[esb-java-user] dbreport question
Harm Verhagen
harm.verhagen at gmail.com
Thu Oct 2 05:56:54 PDT 2008
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 <paul at wso2.com> 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 <indika at wso2.com <mailto:indika at wso2.com>>
> >
> > 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 <indika at wso2.com <mailto:indika at wso2.com>>
> >>
> >> 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:* esb-java-user-bounces at wso2.org
> >>> <mailto:esb-java-user-bounces at wso2.org>
> >>> [mailto:esb-java-user-bounces at wso2.org]*On Behalf Of
> >>> *Indika Kumara
> >>> *Sent:* October 1, 2008 1:21 PM
> >>> *To:* esb-java-user at wso2.org <mailto:
> esb-java-user at wso2.org>
> >>> *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
> >>>> Esb-java-user at wso2.org <mailto:Esb-java-user at wso2.org>
> >>>>
> 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 Esb-java-user at wso2.org
> >>> <mailto:Esb-java-user at wso2.org>
> >>> 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
> >> Esb-java-user at wso2.org <mailto:Esb-java-user at wso2.org>
> >> http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
> >>
> >>
> >>
> ------------------------------------------------------------------------
> >> _______________________________________________ Esb-java-user
> >> mailing list Esb-java-user at wso2.org
> >> <mailto:Esb-java-user at wso2.org>
> >> 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
> > Esb-java-user at wso2.org <mailto:Esb-java-user at wso2.org>
> > http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
> >
> >
> >
> > ------------------------------------------------------------------------
> >
> > _______________________________________________
> > Esb-java-user mailing list
> > Esb-java-user at wso2.org
> > 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
> paul at wso2.com
>
> "Oxygenating the Web Service Platform", www.wso2.com
>
> _______________________________________________
> Esb-java-user mailing list
> Esb-java-user at wso2.org
> http://mailman.wso2.org/cgi-bin/mailman/listinfo/esb-java-user
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.wso2.org/pipermail/esb-java-user/attachments/20081002/b4f6047b/attachment-0001.htm
More information about the Esb-java-user
mailing list