Harm The only real solution is for us to extend the dbreport mediator.
Otherwise you need to look up the new row based on data you have in Synapse. In other words, don't just look up the "last" insert but uniquely identify the right row, preferably by primary key. However, I will work with Indika on how to extend the mediator as I think this is a useful addition. Paul Harm Verhagen 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] <mailto:[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]> >>> <mailto:[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]> <mailto:[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]> >>>>> <mailto:[EMAIL PROTECTED]> >>>>> [mailto:[EMAIL PROTECTED] Behalf Of >>>>> *Indika Kumara >>>>> *Sent:* October 1, 2008 1:21 PM >>>>> *To:* [email protected] >>>>> <mailto:[email protected]> <mailto:[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]> >>>>>> <mailto:[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]> >>>>> <mailto:[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]> >>>> <mailto:[email protected]> >>>> http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user >>>> >>>> >>>> >>>> ------------------------------------------------------------------------ >>>> _______________________________________________ Esb-java-user >>>> mailing list [email protected] <mailto:[email protected]> >>>> <mailto:[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]> >>> <mailto:[email protected]> >>> http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user >>> >>> >>> >>> ------------------------------------------------------------------------ >>> >>> _______________________________________________ >>> Esb-java-user mailing list >>> [email protected] <mailto:[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]> > 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 -- Paul Fremantle CTO and Co-Founder, WSO2 OASIS WS-RX TC Co-chair VP, Apache Synapse Office: +44 844 484 8143 Cell: +44 798 447 4618 blog: http://pzf.fremantle.org [EMAIL PROTECTED] "Oxygenating the Web Service Platform", www.wso2.com _______________________________________________ Esb-java-user mailing list [email protected] http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
