Thread: stored function, multiple queries, best practices

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.com

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

>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.com

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

--- Oliver Jowett <oliver@opencloud.com> 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.com

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