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]
<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
