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
