Orbeon Forms community mailing list

Added support for GeneratedKeysResultSet to SQLProcessor (includes patch)

Classic

List

Threaded

6 messages Options

Nick Van den Bleeken

Added support for GeneratedKeysResultSet to SQLProcessor (includes patch)

Hi All,

I just started experimenting with Orbeon Forms, and needed access to the ‘generated keys’ of an SQL INSERT statement. Starting from JDBC 3.0 there is a standard way to access these.

The patch that I attached to this e-mail adds support for accessing the generated keys of update statements using the standard sql:result-set and sql:row-iterator construct. I just wanted to make this patch available to other people that need this feature.

It would be great if this (or similar support) could be added to the standard Orbeon build, but I don’t know what the procedure is to request this (create issue, send to dev-list?), can someone provide me with this information?

Regards,

Nick Van den Bleeken - Research & Development Manager
Inventive Designers
Phone: +32 - 3 - 8210170
Fax: +32 - 3 - 8210171
Email: [hidden email]

PS:I just started working with Orbeon a couple of days ago, if this patch isn’t adding the support to access the generated keys of update statements, please feel free to comment on this, I’ll be happy to update the patch.

Inventive Designers' Email Disclaimer:
http://www.inventivedesigners.com/email-disclaimer

--
This message has been scanned for viruses and
dangerous content, and is believed to be clean.
--

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws

AddGeneratedKeysResultSetPatch.txt (2K) Download Attachment

Erik Bruchez

Re: Added support for GeneratedKeysResultSet to SQLProcessor (includes patch)

Administrator

Nick,

Great. You can just send a patch.

It seems your patch contains only changes to QueryInterpreter.java,
but it seems that ResultSetInterpreter.java was modified as well. Can
you send all the files?

At some point we hope to migrate to Git which should make
contributions easier to live with.

-Erik

On Mar 15, 2009, at 11:11 AM, Nick Van den Bleeken wrote:

> Hi All,
>
> I just started experimenting with Orbeon Forms, and needed access to
> the ‘generated keys’ of an SQL INSERT statement. Starting from JDBC
> 3.0 there is a standard way to access these.
>
> The patch that I attached to this e-mail adds support for accessing
> the generated keys of update statements using the standard
> sql:result-set and sql:row-iterator construct. I just wanted to make
> this patch available to other people that need this feature.
>
> It would be great if this (or similar support) could be added to the
> standard Orbeon build, but I don’t know what the procedure is to
> request this (create issue, send to dev-list?), can someone provide
> me with this information?
>
> Regards,
>
> Nick Van den Bleeken - Research & Development Manager
> Inventive Designers
> Phone: +32 - 3 - 8210170
> Fax: +32 - 3 - 8210171
> Email: [hidden email]
>
> PS:I just started working with Orbeon a couple of days ago, if this
> patch isn’t adding the support to access the generated keys of
> update statements, please feel free to comment on this, I’ll be
> happy to update the patch.
>
>
>
> Inventive Designers' Email Disclaimer:
> http://www.inventivedesigners.com/email-disclaimer
>
> --
> This message has been scanned for viruses and
> dangerous content, and is believed to be clean.
> --
> <AddGeneratedKeysResultSetPatch.txt>
>
>
> --
> You receive this message as a subscriber of the [hidden email]
> mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> OW2 mailing lists service home page: http://www.ow2.org/wws

--
Orbeon Forms - Web Forms for the Enterprise Done the Right Way
http://www.orbeon.com/

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws

Nick Van den Bleeken

RE: Re: Added support for GeneratedKeysResultSet to SQLProcessor (includes patch)

Erik,

Sorry about that, apparently I forgot to select ResultSetInterpreter.java when creating the patch. I attached a new patch that contains both files.

Regards,

Nick Van den Bleeken - Research & Development Manager
Inventive Designers
Phone: +32 - 3 - 8210170
Fax: +32 - 3 - 8210171
Email: [hidden email]

> -----Original Message-----
> From: Erik Bruchez [mailto:[hidden email]]
> Sent: maandag 16 maart 2009 23:54
> To: [hidden email]
> Subject: [ops-users] Re: Added support for GeneratedKeysResultSet to
> SQLProcessor (includes patch)
>
> Nick,
>
> Great. You can just send a patch.
>
> It seems your patch contains only changes to QueryInterpreter.java, but
> it seems that ResultSetInterpreter.java was modified as well. Can you
> send all the files?
>
> At some point we hope to migrate to Git which should make contributions
> easier to live with.
>
> -Erik
>
> On Mar 15, 2009, at 11:11 AM, Nick Van den Bleeken wrote:
>
> > Hi All,
> >
> > I just started experimenting with Orbeon Forms, and needed access to
> > the 'generated keys' of an SQL INSERT statement. Starting from JDBC
> > 3.0 there is a standard way to access these.
> >
> > The patch that I attached to this e-mail adds support for accessing
> > the generated keys of update statements using the standard
> > sql:result-set and sql:row-iterator construct. I just wanted to make
> > this patch available to other people that need this feature.
> >
> > It would be great if this (or similar support) could be added to the
> > standard Orbeon build, but I don't know what the procedure is to
> > request this (create issue, send to dev-list?), can someone provide
> me
> > with this information?
> >
> > Regards,
> >
> > Nick Van den Bleeken - Research & Development Manager Inventive
> > Designers
> > Phone: +32 - 3 - 8210170
> > Fax: +32 - 3 - 8210171
> > Email: [hidden email]
> >
> > PS:I just started working with Orbeon a couple of days ago, if this
> > patch isn't adding the support to access the generated keys of update
> > statements, please feel free to comment on this, I'll be happy to
> > update the patch.
> >
> >
> >
> > Inventive Designers' Email Disclaimer:
> > http://www.inventivedesigners.com/email-disclaimer
> >
> > --
> > This message has been scanned for viruses and dangerous content, and
> > is believed to be clean.
> > --
> > <AddGeneratedKeysResultSetPatch.txt>
> >
> >
> > --
> > You receive this message as a subscriber of the [hidden email]
> > mailing list.
> > To unsubscribe: mailto:[hidden email]
> > For general help: mailto:[hidden email]?subject=help
> > OW2 mailing lists service home page: http://www.ow2.org/wws
>
> --
> Orbeon Forms - Web Forms for the Enterprise Done the Right Way
> http://www.orbeon.com/
>
>
> --
> This message has been scanned for viruses and dangerous content by
> MailScanner, and is believed to be clean.
> --
>

Inventive Designers' Email Disclaimer:
http://www.inventivedesigners.com/email-disclaimer

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
--

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
OW2 mailing lists service home page: http://www.ow2.org/wws

AddGeneratedKeysResultSetPatch.txt (4K) Download Attachment

Erik Bruchez

Re: RE: Re: Added support for GeneratedKeysResultSet to SQLProcessor (includes patch)

Administrator

Nick,

I committed these changes. I hope they won't break anything ;) Do you
have an example you could produce to show how this is used?

-Erik

On Mar 17, 2009, at 2:54 AM, Nick Van den Bleeken wrote:

> Erik,
>
> Sorry about that, apparently I forgot to select
> ResultSetInterpreter.java when creating the patch. I attached a new
> patch that contains both files.
>
> Regards,
>
> Nick Van den Bleeken - Research & Development Manager
> Inventive Designers
> Phone: +32 - 3 - 8210170
> Fax: +32 - 3 - 8210171
> Email: [hidden email]
>
>
>> -----Original Message-----
>> From: Erik Bruchez [mailto:[hidden email]]
>> Sent: maandag 16 maart 2009 23:54
>> To: [hidden email]
>> Subject: [ops-users] Re: Added support for GeneratedKeysResultSet to
>> SQLProcessor (includes patch)
>>
>> Nick,
>>
>> Great. You can just send a patch.
>>
>> It seems your patch contains only changes to QueryInterpreter.java,
>> but
>> it seems that ResultSetInterpreter.java was modified as well. Can you
>> send all the files?
>>
>> At some point we hope to migrate to Git which should make
>> contributions
>> easier to live with.
>>
>> -Erik
>>
>> On Mar 15, 2009, at 11:11 AM, Nick Van den Bleeken wrote:
>>
>>> Hi All,
>>>
>>> I just started experimenting with Orbeon Forms, and needed access to
>>> the 'generated keys' of an SQL INSERT statement. Starting from JDBC
>>> 3.0 there is a standard way to access these.
>>>
>>> The patch that I attached to this e-mail adds support for accessing
>>> the generated keys of update statements using the standard
>>> sql:result-set and sql:row-iterator construct. I just wanted to make
>>> this patch available to other people that need this feature.
>>>
>>> It would be great if this (or similar support) could be added to the
>>> standard Orbeon build, but I don't know what the procedure is to
>>> request this (create issue, send to dev-list?), can someone provide
>> me
>>> with this information?
>>>
>>> Regards,
>>>
>>> Nick Van den Bleeken - Research & Development Manager Inventive
>>> Designers
>>> Phone: +32 - 3 - 8210170
>>> Fax: +32 - 3 - 8210171
>>> Email: [hidden email]
>>>
>>> PS:I just started working with Orbeon a couple of days ago, if this
>>> patch isn't adding the support to access the generated keys of
>>> update
>>> statements, please feel free to comment on this, I'll be happy to
>>> update the patch.
>>>
>>>
>>>
>>> Inventive Designers' Email Disclaimer:
>>> http://www.inventivedesigners.com/email-disclaimer
>>>
>>> --
>>> This message has been scanned for viruses and dangerous content, and
>>> is believed to be clean.
>>> --
>>> <AddGeneratedKeysResultSetPatch.txt>
>>>
>>>
>>> --
>>> You receive this message as a subscriber of the [hidden email]
>>> mailing list.
>>> To unsubscribe: mailto:[hidden email]
>>> For general help: mailto:[hidden email]?subject=help
>>> OW2 mailing lists service home page: http://www.ow2.org/wws
>>
>> --
>> Orbeon Forms - Web Forms for the Enterprise Done the Right Way
>> http://www.orbeon.com/
>>
>>
>> --
>> This message has been scanned for viruses and dangerous content by
>> MailScanner, and is believed to be clean.
>> --
>>
>
>
> Inventive Designers' Email Disclaimer:
> http://www.inventivedesigners.com/email-disclaimer
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
> --
>
>
> <AddGeneratedKeysResultSetPatch.txt>
> --
> You receive this message as a subscriber of the [hidden email]
> mailing list.
> To unsubscribe: mailto:[hidden email]
> For general help: mailto:[hidden email]?subject=help
> OW2 mailing lists service home page: http://www.ow2.org/wws

Erik Bruchez

Re: RE: Re: Added support for GeneratedKeysResultSet to SQLProcessor (includes patch)

Administrator

In reply to this post by Nick Van den Bleeken

Nick,

Actually I found an issue with this: Oracle throws an exception
"operation not supported" on stmt.getGeneratedKeys(). So I put the
code in ResultSetInterpreter.setGeneratedKeysResultSetInfo() within
comments for now as I don't have time to investigate. It would be
great if you could figure this one out ;-)

-Erik

On Mar 17, 2009, at 2:54 AM, Nick Van den Bleeken wrote:

Nick Van den Bleeken

RE: Re: RE: Re: Added support for GeneratedKeysResultSet to SQLProcessor (includes patch)

Erik,

Sorry about providing a patch that fails on certain databases.

I would expect that adding to following code to the beginning of ResultSetInterpreter.setGeneratedKeysResultSetInfo will solve the problem:

if (!stmt.getConnection().getMetaData().supportsGetGeneratedKeys()) {
return false;
}

But after reading http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/jdbcvers.htm#sthref109 I'm not completely sure, and I can't test it because I don't have access to an Oracle database. And on MySQL it just worked because they support it, but it seems that Oracle also supports it but not as failsafe as MySQL.

I need this because when I have a table with an auto increment column, I need to know which id got generated to use it in subsequent INSERT statements. But I'm not sure if the create table statement with auto increment is cross database. But this is an example that works in MySQL:

<p:processor name="oxf:sql">
<p:input name="datasource" href="../datasource-sql.xml"/>
<p:input name="config">
<sql:config>
<result>
<sql:connection>
<sql:execute>
<sql:update>
CREATE TABLE Product
(
ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
Name VARCHAR(40) NOT NULL,
PRIMARY KEY (ID)
);
</sql:update>
</sql:execute>
<sql:execute>
<sql:update>
INSERT INTO correspondences VALUES (
null,
'This is a test')
</sql:update>
<sql:result-set>
<sql:row-iterator>
<id>
<sql:get-columns format="xml"/>
</id>
</sql:row-iterator>
</sql:result-set>
</sql:execute>
</sql:connection>
</result>
</sql:config>
</p:input>
<p:output name="data" id="result-set"/>
</p:processor>

Regards,

Nick Van den Bleeken - Research & Development Manager
Inventive Designers
Phone: +32 - 3 - 8210170
Fax: +32 - 3 - 8210171
Email: [hidden email]

> -----Original Message-----
> From: Erik Bruchez [mailto:[hidden email]]
> Sent: vrijdag 20 maart 2009 4:10
> To: [hidden email]
> Subject: [ops-users] Re: RE: Re: Added support for
> GeneratedKeysResultSet to SQLProcessor (includes patch)
>
> Nick,
>
> Actually I found an issue with this: Oracle throws an exception
> "operation not supported" on stmt.getGeneratedKeys(). So I put the code
> in ResultSetInterpreter.setGeneratedKeysResultSetInfo() within comments
> for now as I don't have time to investigate. It would be great if you
> could figure this one out ;-)
>
> -Erik
>
> On Mar 17, 2009, at 2:54 AM, Nick Van den Bleeken wrote:
>
> > Erik,
> >
> > Sorry about that, apparently I forgot to select
> > ResultSetInterpreter.java when creating the patch. I attached a new
> > patch that contains both files.
> >
> > Regards,
> >
> > Nick Van den Bleeken - Research & Development Manager Inventive
> > Designers
> > Phone: +32 - 3 - 8210170
> > Fax: +32 - 3 - 8210171
> > Email: [hidden email]
> >
> >
> >> -----Original Message-----
> >> From: Erik Bruchez [mailto:[hidden email]]
> >> Sent: maandag 16 maart 2009 23:54
> >> To: [hidden email]
> >> Subject: [ops-users] Re: Added support for GeneratedKeysResultSet to
> >> SQLProcessor (includes patch)
> >>
> >> Nick,
> >>
> >> Great. You can just send a patch.
> >>
> >> It seems your patch contains only changes to QueryInterpreter.java,
> >> but it seems that ResultSetInterpreter.java was modified as well.
> Can
> >> you send all the files?
> >>
> >> At some point we hope to migrate to Git which should make
> >> contributions easier to live with.
> >>
> >> -Erik
> >>
> >> On Mar 15, 2009, at 11:11 AM, Nick Van den Bleeken wrote:
> >>
> >>> Hi All,
> >>>
> >>> I just started experimenting with Orbeon Forms, and needed access
> to
> >>> the 'generated keys' of an SQL INSERT statement. Starting from JDBC
> >>> 3.0 there is a standard way to access these.
> >>>
> >>> The patch that I attached to this e-mail adds support for accessing
> >>> the generated keys of update statements using the standard
> >>> sql:result-set and sql:row-iterator construct. I just wanted to
> make
> >>> this patch available to other people that need this feature.
> >>>
> >>> It would be great if this (or similar support) could be added to
> the
> >>> standard Orbeon build, but I don't know what the procedure is to
> >>> request this (create issue, send to dev-list?), can someone provide
> >> me
> >>> with this information?
> >>>
> >>> Regards,
> >>>
> >>> Nick Van den Bleeken - Research & Development Manager Inventive
> >>> Designers
> >>> Phone: +32 - 3 - 8210170
> >>> Fax: +32 - 3 - 8210171
> >>> Email: [hidden email]
> >>>
> >>> PS:I just started working with Orbeon a couple of days ago, if this
> >>> patch isn't adding the support to access the generated keys of
> >>> update statements, please feel free to comment on this, I'll be
> >>> happy to update the patch.
> >>>
> >>>
> >>>
> >>> Inventive Designers' Email Disclaimer:
> >>> http://www.inventivedesigners.com/email-disclaimer
> >>>
> >>> --
> >>> This message has been scanned for viruses and dangerous content,
> and
> >>> is believed to be clean.
> >>> --
> >>> <AddGeneratedKeysResultSetPatch.txt>
> >>>
> >>>
> >>> --
> >>> You receive this message as a subscriber of the [hidden email]
> >>> mailing list.
> >>> To unsubscribe: mailto:[hidden email]
> >>> For general help: mailto:[hidden email]?subject=help
> >>> OW2 mailing lists service home page: http://www.ow2.org/wws
> >>
> >> --
> >> Orbeon Forms - Web Forms for the Enterprise Done the Right Way
> >> http://www.orbeon.com/
> >>
> >>
> >> --
> >> This message has been scanned for viruses and dangerous content by
> >> MailScanner, and is believed to be clean.
> >> --
> >>
> >
> >
> > Inventive Designers' Email Disclaimer:
> > http://www.inventivedesigners.com/email-disclaimer
> >
> > --
> > This message has been scanned for viruses and dangerous content by
> > MailScanner, and is believed to be clean.
> > --
> >
> >
> > <AddGeneratedKeysResultSetPatch.txt>
> > --
> > You receive this message as a subscriber of the [hidden email]
> > mailing list.
> > To unsubscribe: mailto:[hidden email]
> > For general help: mailto:[hidden email]?subject=help
> > OW2 mailing lists service home page: http://www.ow2.org/wws
>
> --
> Orbeon Forms - Web Forms for the Enterprise Done the Right Way
> http://www.orbeon.com/
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
> --
>