Hi,
I am seeing a similar problem when using mysql as db backend.
/How to get the ID of a NEWLY INSERTED ROW ?/
*problem:*
- I insert a new fow in a table with the dbreport module. It gets a
new id. (it is an AUTO_INCREMENT variable).
- further in my sequence, and in the 'out' sequence I need to update
that row in the table, using the id.
I couldn't find a clean (and threadsafe!!!) way to get the newly
created id with wso2.
The wso2 samples don't address this problem.
mysql provides a function LAST_INSERT_ID() to get the newly inserted
ID. However that can only be used in the *_same_* transaction.
So I can use LAST_INSERT_ID() in the _same_ dbreport module (as 2nd
or 3rd sql statement). However dbreport mediator CANNOT save this to a
variable. (only dblookup can).
A following dblookup mediator can however NOT use LAST_INSERT_ID()
(it always returns zero, as this is a new db transaction).
dblookup could do some MAX like magic... but thatis not threadsafe.
*workaround*
I found a (but-ugly) workaround:
dbreport:
sql1: insert into ..... (store my new row... creates a
new ID).
sql2: insert into transaction_uuid(id, uuid)
values(LAST_INSERT_ID(),?)
param:
get-property('MessageID') (store the newly created
ID in a new temp table, using the uuid of the message)
dblookup: select id from transaction_uuid where uuid = ?
param: get-property('MessageID')
result:
transaction_id ( read the newly created
id from the temp table, store it in a variable).
dbreport: delete from transaction_uuid where id = ?
param: get-property('transaction_id')
(delete the temp table).
From here on I can use get-property('transaction_id') to update the
row in the same sequence, (and in the answer sequence).
I would expect however that I can do the above with _one_ single
dbreport mediator. Insert the new field, and store the id in
set-property('transaction_id').
I didn't measure my workaround but it must be slow. (the hole temp
table should be unneeded, the above contains 3 unneeded db accesses
and 2 unneeded mediators).
Do you see any way how wso2 could cleanly support this ? (Maybe an
extension to the dbreport module ?)
Regards,
Harm
2008/10/2 Indika Kumara <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>
Here, you had done two wrongs
1) The IDENTITY_VAL_LOCAL function has no input
parameters. But, you have tried to set parameters -- That is why
you get exception.
2) You have called it in db report mediator. DB report
mediator use to add /update record ...simply uses executeUpdate().
But values IDENTITY_VAL_LOCAL() is for retrieving latest inserted
record identity and will throws errors.
Please use values IDENTITY_VAL_LOCAL() with DB LookUp mediator
Thanks
Indika
Schmilinsky, Remsy wrote:
Thanks. I am trying to get the generated id after insert, I'm
using derby, with 'values IDENTITY_VAL_LOCAL()' in a second sql
but doesn't work, are these incompatible functions ?
2008-10-01 13:47:14,790 [169.254.7.221-WH24CU6052] [jms-Worker-1]
ERROR DBReportMediator Error execuring insert statement : values
IDENTITY_VAL_LOCAL() against DataSource :
jdbc:derby://localhost:1527/esbdb;create=false
java.sql.SQLException: The column position '1' is out of range.
The number of columns for this ResultSet is '0'.
at
org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown
Source)
at
org.apache.derby.client.am.SqlException.getSQLException(Unknown
Source)
at
org.apache.derby.client.am.ColumnMetaData.getColumnType(Unknown
Source)
the insert statement alone works though.
here dbreport config, for the moment I assign value 1 to test it out:
<syn:dbreport>
<syn:connection>
<syn:pool>
<syn:driver>org.apache.derby.jdbc.ClientDriver</syn:driver>
<syn:password>esb</syn:password>
<syn:user>esb</syn:user>
<syn:url>jdbc:derby://localhost:1527/esbdb;create=false</syn:url>
</syn:pool>
</syn:connection>
<syn:statement>
<syn:sql><![CDATA[insert into sample values
(DEFAULT, ?)]]></syn:sql>
<syn:parameter
xmlns:ns1="http://org.apache.synapse/xsd";
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/";
expression="//*/*" type="VARCHAR"/>
</syn:statement>
<syn:statement>
<syn:sql><![CDATA[values
IDENTITY_VAL_LOCAL()]]></syn:sql>
<syn:parameter value="1" type="INTEGER"/>
</syn:statement>
</syn:dbreport>
remsy
-----Original Message-----
*From:* [EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>
[mailto:[EMAIL PROTECTED] Behalf Of *Indika
Kumara
*Sent:* October 1, 2008 1:21 PM
*To:* [email protected] <mailto:[email protected]>
*Subject:* Re: [esb-java-user] dbreport question
Hi Remsy
All the samples in [1] show a scenario somewhat similar to
your scenario (I believe). In those samples, get an ID from
DB and set it as message context property
and then perform conditional switching based on the value of
that property. On the out path, some result form response is
stored in the DB too.
By going through [2], it will be easy to understand the
current behavior of the DB -mediators
[1]
http://wso2.org/project/esb/java/1.7.1/docs/ESB_Samples.html#DBMediators
[2]
http://wso2.org/project/esb/java/1.7.1/docs/ESB_Configuration_Language.html#dblookup
Thanks
Indika
Schmilinsky, Remsy wrote:
I need ro propagate the result of a query (id number) from dbreport to the mediators along with the message.
The idea is that the mediators will perform some actions on the message
and then log results in the db, so we need the id to reference the original
data.
is this possible ? how ?
thanks
Remsy
_______________________________________________
Esb-java-user mailing list
[email protected] <mailto:[email protected]>
http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.173 / Virus Database: 270.7.5/1698 - Release Date: 9/29/2008 7:25 PM
------------------------------------------------------------------------
_______________________________________________ Esb-java-user
mailing list [email protected]
<mailto:[email protected]>
http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.173 / Virus Database: 270.7.5/1698 - Release Date: 9/29/2008 7:25 PM
_______________________________________________
Esb-java-user mailing list
[email protected] <mailto:[email protected]>
http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
------------------------------------------------------------------------
_______________________________________________
Esb-java-user mailing list
[email protected]
http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.173 / Virus Database: 270.7.5/1698 - Release Date: 9/29/2008 7:25 PM
