Executing several SQL Statements in one DBLookup

[MrStiffler]

On an output card function, i want to update 24 specific filds in an Oracle Database.
For each Update i use the dblookup in the function pannel.
This means that the card have to make a connection to the database for each dblookup, so this are 24 connection which make the map processing performance very very slow !

a part of the rules looks like this:

=DBLOOKUP("UPDATE table SET COUNTER = COUNTER + " + CONTENT:Counter_x1 + " WHERE FELD = 'field1' ", "-DBTYPE ORACLE -CONNECT Hoststring -USER user -PASSWORD pass " )
+
DBLOOKUP("UPDATE table SET COUNTER = COUNTER + " + CONTENT:Counter_x2 + " WHERE FELD = 'field2' ", "-DBTYPE ORACLE -CONNECT Hoststring -USER user -PASSWORD pass " )
+
DBLOOKUP("UPDATE table SET COUNTER = COUNTER + " + CONTENT:Counter_x3 + " WHERE FELD = 'field3' ", "-DBTYPE ORACLE -CONNECT Hoststring -USER user -PASSWORD pass " )

If i could make all 24 updates in one SQL statement, this performance problem will be solved.
Don't care for the full map there will be about 200'000 connection which makes realy no sence !

Do you have some ideas or experiences with this ?
thank you very much.
greez stiffle

 

[BocaBurger]

There should not be a new connection for each statement unless the user name or password are different. You might want to create a stored proceedure and just pass it the data required. Save time & money, let the database do the database work.

BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[shelts]

Can you not just point the output card at the database adapter and use -UPDATE to update the table?

 

[BocaBurger]

Not if he needs the where clause.

BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!