Cool. Nice solution!
Paul
2008/10/7 Harm Verhagen <[EMAIL PROTECTED]>
> 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
>
>
_______________________________________________
Esb-java-user mailing list
[email protected]
http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
