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]>
> 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] <[EMAIL PROTECTED]>]*On
> Behalf Of *Indika Kumara
> *Sent:* October 1, 2008 1:21 PM
> *To:* [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 [EMAIL
> PROTECTED]://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 [EMAIL
> PROTECTED]://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]
> 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
