well there isn't always 'just a return' in a report, right ? You have to explicit issue a select and then choose what to use as return.
maybe always grab the LAST_INSERT_ID() in case of an insertion ? (LAST_INSERT_ID() is mysql specific) On Thu, Oct 2, 2008 at 2:22 PM, Paul Fremantle <[EMAIL PROTECTED]> 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]>> > > > > 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 >
_______________________________________________ Esb-java-user mailing list [email protected] http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
