filed as:
https://issues.apache.org/jira/browse/SYNAPSE-457
2008/10/2 Indika Kumara <[EMAIL PROTECTED]>
> Hi Harm
>
> Yes, Harm, you are correct. With using current DB report , DB lookup
> combination , there is no thread safe manner (clear) to retrieve latest
> insert record's identity other than workarounds such as you did.
> It may need a new abstraction, a DB report with return value (Currently, DB
> report has void behavior), where return value can be explicitly specify by a
> separate statement.
>
> It is great, if you could create a JIRA for this as an improvement. :-)
>
> Thanks
> Indika
> Harm Verhagen wrote:
>
> 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 [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
