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]>> > > 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]>> >> >> 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] Behalf Of >>> *Indika Kumara >>> *Sent:* October 1, 2008 1:21 PM >>> *To:* [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]> >>>> 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 >>> >>> ------------------------------------------------------------------------ >>> >>> >>> 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] >> <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
