I found a solution.
fix:
use a mysql store procedure that creates a new entry and returns the
LAST_INSERT_ID().
Then from a dblookup() mediator call this procudure and store the result.
*mysql example code:*
DELIMITER $$
DROP PROCEDURE IF EXISTS create_transaction $$
CREATE PROCEDURE create_transaction (
IN token VARCHAR(36),
IN ip VARCHAR(15),
IN transaction_type INT)
BEGIN
insert into transaction(token,ip,transaction_type) values (token,
ip,transaction_type );
select LAST_INSERT_ID();
END $$
DELIMITER ;
*dblookup example code*
<syn:dblookup>
<syn:connection>
<syn:pool>
<syn:property name="validationquery" value="select 1"/>
<syn:driver>com.mysql.jdbc.Driver</syn:driver>
<syn:property name="testonborrow" value="true"/>
<syn:property name="testwhileidle" value="true"/>
<syn:password>esb</syn:password>
<syn:user>esb</syn:user>
<syn:url>jdbc:mysql://localhost:3306/esb</syn:url>
</syn:pool>
</syn:connection>
<syn:statement>
<syn:sql><![CDATA[call
create_transaction(?,?,?)]]></syn:sql>
<syn:parameter expression="get-property('token')"
type="VARCHAR"/>
<syn:parameter
expression="get-property('axis2','REMOTE_ADDR')" type="VARCHAR"/>
<syn:parameter expression="get-property('transaction_type')"
type="INTEGER"/>
<syn:result name="transaction_id" column="1"/>
</syn:statement>
</syn:dblookup>
*access the id with*
get-property('transaction_id')
On Tue, Oct 7, 2008 at 3:15 PM, Harm Verhagen <[EMAIL PROTECTED]>wrote:
> The problem is a bit worse than I thought.
> I cant find _any_ reliable way to find the id of a newly created row. (I
> want to know it so I can update the row in the 'out' sequence).
>
> The (but-ugly) workaround I thought I had... only works when issuing single
> request.
> Whenever I stress the bus (10 threads simultaniously) the workaround fails.
> The workaround is described in [1]
>
> problem: sql statement 1 and 2 in the workaround are _not_ always done by
> the same connections. sometimes the 2nd one uses a new mysql connection from
> the pool resulting in a pretty subtle problem. (LAST_INSERT_ID() gives the
> _wrong_ number, causing me to update the incorrect row !!
>
> Anyone can think of a solution, workaround ?
>
> Regards,
> Harm
>
>
> - [1] http://wso2.org/mailarchive/esb-java-user/2008-October/000935.html
>
>
> Regards,
> Harm
>
> 2008/10/2 Indika Kumara <[EMAIL PROTECTED]>
>
>> Hi Paul
>>
>> No... current DB report is only do add/update operation. It seems it is
>> helpful to provide lasted inserted record identity.
>> So we need new abstraction as an extension to the existing one- DB report
>> with return value.
>> Return value will be another statement so that it can be configured for
>> database specific ways.
>> Thanks
>> Indika
>>
>> Paul Fremantle wrote:
>>
>> Indika
>>
>> Shouldn't we always grab the return from the report into a fixed property?
>>
>> Paul
>>
>> Harm Verhagen wrote:
>>
>>
>> filed as:https://issues.apache.org/jira/browse/SYNAPSE-457
>>
>> 2008/10/2 Indika Kumara <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
>> <[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] <mailto:[EMAIL PROTECTED]>
>> <[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";
>> <http://org.apache.synapse/xsd>
>> xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/";
>> <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]>
>> [mailto:[EMAIL PROTECTED] <[EMAIL PROTECTED]>]*On Behalf Of
>> *Indika Kumara
>> *Sent:* October 1, 2008 1:21 PM
>> *To:* [email protected] <mailto:[email protected]>
>> <[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]>
>> <[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]> <[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]>
>> <[email protected]>
>> http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
>>
>>
>> ------------------------------------------------------------------------
>> _______________________________________________ Esb-java-user
>> mailing list [email protected]
>> <mailto:[email protected]> <[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]>
>> <[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
