What is talxy?
stored function, multiple queries, best practices
Hello Guest		   
  • Login
• Register…
• Start blog		   • Who, Where, When
• What can I do?
• What to Read?		   • Polls
• Avatars
• Interests		   • Cities and Countries
• Random blog
• Users search		   • Search
• Games
• Tests
• QAIX		   • Сообщества
• Talxy Chat
• Horoscope
• Online		  
Зарегистрируйся!
QAIX > PostgreSQL database development > stored function, multiple queries, best practices 19 April 2006 00:19:16
  Recent blog posts: 
Free iPod nano raffle for…
Aimersoft 2008 Christmas promotion: "Free iPod nano raffle for Chrismas" and "Buy 1 Get…

How to convert Rhapsody to…
Music purchased from Rhapsody is under DRM protection, which stops customers from playing the downloaded&helli­p;

PS3 firmware update adds full…
Eric Lempel, Sony's Director of PlayStation Network Operations, has posted on the official PlayStation 3…

Nintendo makes $6 profit on…
According to Macquarie Securities analyst David Gibson, Nintendo makes $6 USD operating profit on each…

Maintenance in winter
Skin for winter very easy to do, if does not supplement the moisture in time, like persimmons, flat and…

'Jasper' Xbox 360s finally…
'Jasper' Xbox 360s finally available According to a flooding of posts over at xbox-scene, it appears…

How to transfer Napster music…
Music purchased from Napster is under DRM protection, which stops customers from playing the downloaded&helli­p;

The song my brother taught me
Don't let your heart be hardened Don't let your love grow cold May it always stay so childlike May it…

My brother taught me this…
God grant me the serenity to accept the things I cannot change; courage to change the things I can;…

baz-baz-baz-baz pam-parabaram…
baz-baz-baz-baz pam-parabaram baz-baz-baz-baz pam-parabaram baz-baz-baz-baz pam-parabaram-baz ba-ra-ba…

  They have birthday today: 
xiongchong
henna diah
  Forums:   
    topic feeds

• QAIX  all topics, blogs and comments

• PHP web-programming

• Perl web-programming

• ASP web-programming

• Visual Basic Programming

• .Net Development

• C/C++ Programming

• Java Programming

• Javascript web-programming

• Apache HTTP Server

• Web-design

• Web-hosting, dedicated, colocation

• HTML web-programming

• XML development

• General Web-programming

• Content Management Systems

• ColdFusion

• Search Engine Optimization

• Hardware

• Software, Computer Help

• MySQL database development

• PostgreSQL database development

• MS Access database development

• Oracle database development

• MS SQL database development

• Informix database development

• Buy / Sell

• Open discussion

• Anonymous
  Discuss: 
plpgsql - Insert from a record…
Dear Postgresql experts, I'm writing a simple plpgsql function that selects a row from a table…

calling a pl/pgsql function…
Hello, i am tryin to pass an array like argument to a pl/pgsql function but i can not find the correct…

PgDatabase, on heap…
I am having a problem constructing a PgDatabase object. If I create the object on the stack (as a var…

Backup and restore through JDBC
Hello List I am trying to develop, a API to carry through backup and restore through JDBC. I think that…
  Recent forum topics: 
why infix:<div>:(Int, Int…
HaloO, I realized from the typed literal thread that S03 now explicitly states that div on two Ints…

[rt.cpan.org #41329] VMS…
<URL: http://rt.cpan.org/­Ticket/Display.html?­id=41329 > Thanks, applied

how to write literals of some…
Hello, I'm wondering how to write anonymous value literals of some Perl 6 basic types, and I didn't…
  Recent forum comments:
RE: Defining classpath for…
Your WSExample.java file has to be placed under the WAS.HOME/bin folder. Since your java2Wsdl is…

RE: access denied
IUSR account doesn't have access to cmd.exe by default (assuming you are using anonymous…

RE: access denied
Hey guys, I'm totally out of my league here, I don't even know how I got stuck with this problem, it's…

RE: [patch@34995] vms.c - Memory…
On Wed, Dec 3, 2008 at 7:38 PM, John E. Malmberg <wb8tyw@qsl.net> wrote: Thanks, applied as…

RE: how to write literals of some…
HaloO, David Green wrote: And how about 'Num 1.0 === Complex(1,0) === Int 1'? Should all these be…

RE: how to write literals of some…
HaloO, David Green wrote: IIRC, === is defined to compare only values from the same type domain. For…

RE: Support for ensuring invariants…
* David Green <david.green@telus.­net> [2008-12-03 22:00]: Not with a closure trait and…

RE: Support for ensuring invariants…
* Mark J. Reed <markjreed@gmail.co­m> [2008-12-03 20:30]: Yes. Exactly. It’s not a…

RE: Files, Directories, Resources…
* Aristotle Pagaltzis (pagaltzis@gmx.de) [081204 14:38]: So, they start when - you have users pick…

RE: Support for ensuring invariants…
* mark.a.biggar@comca­st.net <mark.a.biggar@comc­ast.net> [2008-12-03 21:45]: I…
  Moderators:
 Administrator
 Protorioris

stored function, multiple queries, best practices

Ash Grove 19 April 2006 00:19:16
 Hello,

I'd like to learn how other people approach this kind
of situation:

Say you want to do an insert on three tables and all
of your data is coming into the db from your
application. You do the first insert and a primary key
is generated. You grab that primary key and insert it
(along with some other data) into the two other
tables. Finally, you return the primary key to the
application. If something goes wrong, you want to
everything to rollback.

I currently do this by building a delimited string in
the application containing all data to be inserted and
then sending it, via callablestatement, to a stored
function. The function parses the string, does the
inserts and returns the primary key via a registered
out parameter.

Are there glaring errors with this approach? How would
you do this?

Thanks in advance,
Ash

___________________­____________________­___________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.c­om

-------------------­--------(end of broadcast)---------­------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Add comment
Oliver Jowett 14 April 2006 18:18:58 [ permanent link ]
 Ash Grove wrote:
I currently do this by building a delimited string in> the application containing all data to be inserted and> then sending it, via callablestatement, to a stored> function. The function parses the string, does the> inserts and returns the primary key via a registered> out parameter.

Why assemble a string and parse it? Why not just use multiple parameters
directly?

-O

-------------------­--------(end of broadcast)---------­------------------
TIP 5: don't forget to increase your free space map settings

Add comment
Ash Grove 14 April 2006 18:37:20 [ permanent link ]
 
Why assemble a string and parse it? Why not just use>multiple parameters >directly?>-O


So, Oliver, you're suggesting something like "call
myfunction('query1'­,'query2','query3')"­?
I currently do this by building a delimited string> in> the application containing all data to be inserted> and> then sending it, via callablestatement, to a> stored> function. The function parses the string, does the> inserts and returns the primary key via a> registered> out parameter.>
Why assemble a string and parse it? Why not just use
multiple parameters
directly?

-O


___________________­____________________­___________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.c­om

-------------------­--------(end of broadcast)---------­------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgres­ql.org/docs/faq

Add comment
Oliver Jowett 16 April 2006 05:07:34 [ permanent link ]
 Ash Grove wrote:>
Why assemble a string and parse it? Why not just use>>multiple parameters >>directly?>>-O>
So, Oliver, you're suggesting something like "call> myfunction('query1'­,'query2','query3')"­?

No, I'm suggesting:

call myfunction('data value 1', 42, 1.234, 'data value 4')

I am assuming you know the combinations of updates you will need to run
ahead of time. If you want to run arbitary queries then you are probably
stuck with doing some parsing in the function .. but that is pretty nasty.

-O

-------------------­--------(end of broadcast)---------­------------------
TIP 4: Have you searched our list archives?

http://archives.pos­tgresql.org

Add comment
Ash Grove 18 April 2006 22:53:51 [ permanent link ]
 

--- Oliver Jowett <oliver@opencloud.c­om> wrote:
Ash Grove wrote:> >
Why assemble a string and parse it? Why not just> use> >>multiple parameters > >>directly?> >>-O> >
So, Oliver, you're suggesting something like> "call> > myfunction('query1'­,'query2','query3')"­?>
No, I'm suggesting:>
call myfunction('data value 1', 42, 1.234, 'data> value 4')>
I am assuming you know the combinations of updates> you will need to run > ahead of time. If you want to run arbitary queries> then you are probably > stuck with doing some parsing in the function .. but> that is pretty nasty.>
-O>

I know the combination of queries, but whether a
parameter has a value is unknown. Also unknow is
whether all relevant tables will get updated. If I
simply send all parameters in a defined order, the
parameter list will be kinda long and many values will
be nulls.

I can only call one function (from the application)
per transaction, right?

I don't mind doing the parsing, I was just wondering
if I was considering all options.

___________________­____________________­___________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.c­om

-------------------­--------(end of broadcast)---------­------------------
TIP 4: Have you searched our list archives?

http://archives.pos­tgresql.org

Add comment
Guy Rouillier 19 April 2006 00:19:16 [ permanent link ]
 Ash Grove wrote:
I can only call one function (from the application)> per transaction, right?

Incorrect. Transactions are delimited by commits or rollbacks, not by
function calls or SQL statements (more correctly, DML.) If you want to
control your commit points, tell JDBC to "conn.setAutocommit­(false)".

--
Guy Rouillier


-------------------­--------(end of broadcast)---------­------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresq­l.org so that your
message can get through to the mailing list cleanly

Add comment
 

Add new comment

As:
Login:  Password:  
 
 
  
 
Пожалуйста, относитесь к собеседникам уважительно, не используйте нецензурные слова, не злоупотребляйте заглавными буквами, не публикуйте рекламу и объявления о купле/продаже, а также материалы нарушающие сетевой этикет или УК РФ.

QAIX > PostgreSQL database development > stored function, multiple queries, best practices 19 April 2006 00:19:16
see also:
inserting works fine but I cant update.
Foreign Key on text field
reversion? Recursion question
пройди тесты:
Do you really know yourself?
see also:
Posting Lookup Values
hallo , j don t know what to write...
mahalo transaction manager
  Copyright © 2001—2008 QAIX
Idea: Miсhael Monashev
Помощь и задать вопросы можно в сообществе support.qaix.com.
Сообщения об ошибках оставляем в сообществе bugs.qaix.com.
Предложения и комментарии пишем в сообществе suggest.qaix.com.
Информация для родителей.
Write us at:
If you would like to report an abuse of our service, such as a spam message, please .