[prev in list] [next in list] [prev in thread] [next in thread]
List: hsqldb-user
Subject: Re: [Hsqldb-user] 1.8 prepared statement with autoGeneratedKeys
From: Allen Riddle <allenriddle () gmail ! com>
Date: 2011-06-17 15:28:33
Message-ID: BANLkTim6h_+roDJXZ7s_-7mGhEAB7WKQeQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
I solved my problem described in my previous message. We were forgetting to
invoke next() on the Result.
On Fri, Jun 17, 2011 at 9:35 AM, Allen Riddle <allenriddle@gmail.com> wrote:
> I'm not able to get CALL IDENTITY() to work with 1.8.1.3. I've got a small
> test application that just has a main that simply creates a JDBC connection,
> does an insert and then does a CALL IDENTITY(). However, when I try to get
> the id off the ResultSet, I get an exception saying that no data is
> available. I've tried this against the in memory and stand alone (file).
> Is this call not supported in those modes, does it only work with a stand
> alone server? Below is my sample, any help would be appreciated. Thanks.
>
> static Connection conn;
>
> /**
>
> * @param args
>
> */
>
> public static void main(String[] args) {
>
> // TODO Auto-generated method stub
>
> try
>
> {
>
> Class.forName("org.hsqldb.jdbcDriver").newInstance();
>
> String url = "jdbc:hsqldb:file:/Users/ariddle/Desktop/testdb";
>
> conn = DriverManager.getConnection(url, "SA", "");
>
> doTests();
>
> conn.close();
>
> }
>
> catch (ClassNotFoundException ex) {System.err
> .println(ex.getMessage());}
>
> catch (IllegalAccessException ex) {System.err
> .println(ex.getMessage());}
>
> catch (InstantiationException ex) {System.err
> .println(ex.getMessage());}
>
> catch (SQLException ex) {System.err
> .println(ex.getMessage());}
>
>
> }
>
> public static void doTests() {
>
> try
>
> {
>
> Statement st = conn.createStatement();
>
>
>
> st.executeUpdate("CREATE TABLE campaign (id INTEGER generated by
> default as identity (start with 1), campaign_id INTEGER NULL);");
>
> st.executeUpdate("INSERT INTO campaign (campaign_id) VALUES (100)");
>
> PreparedStatement ps = conn.prepareStatement("CALL IDENTITY()");
>
> ResultSet result = ps.executeQuery();
>
> int identity = result.getInt(1);
>
> result.close();
>
> }
>
> catch (SQLException ex)
>
> {
>
> System.err.println(ex.getMessage());
>
> }
>
> }
>
> On Thu, Jun 16, 2011 at 4:31 PM, Allen Riddle <allenriddle@gmail.com>wrote:
>
> > Thanks Fred. Yesterday, I went down the path of using Retrotranslator,
> > but was running into issues. I'm not sure if I was sending in the correct
> > switches on the command. It would be cool to get that vetted out in order
> > to use the HSQDB 2.x features, maybe I'll take another shot at it. Thanks.
> >
> >
> > On Thu, Jun 16, 2011 at 4:06 PM, Fred Toussi <fredt@users.sourceforge.net
> > > wrote:
> >
> > > This wasn't supported back then.
> > >
> > > The old method for getting the value of an identity column is by using
> > > CALL IDENTITY() after an insert. You might be able to put this call in the
> > > getGeneratedKeys() method of PareparedStatement and return the result set
> > > that it returns.
> > >
> > > Alternatively, you might try the "retro" tools listed here
> > > http://retrotranslator.sourceforge.net/ on a Java 5 compiled HSQLDB jar
> > > and let us know if you get any usable results
> > >
> > > Fred
> > >
> > >
> > > On Thu, 16 Jun 2011 15:30 -0500, "Allen Riddle" <allenriddle@gmail.com>
> > > wrote:
> > >
> > > Hello,
> > >
> > > Now that we've backed our version of HSQLDB down to 1.8.1.3, we are
> > > getting a function not supported exception when trying to execute prepared
> > > statements using generated key holders. If I understand the 1.8.x doc
> > > correctly, this is supported. However, when I look at the source code, I
> > > see the following defined:
> > >
> > > public synchronized PreparedStatement prepareStatement(String sql,
> > > int autoGeneratedKeys) throws SQLException {
> > >
> > > checkClosed();
> > >
> > > throw Util.notSupported();
> > > }
> > >
> > >
> > > I'm running on Java 1.4 against HSQLDB 1.8.1.3, what is the best way to
> > > get at the auto generated key? Any help is appreciated. Thanks.
> > >
> > > ------------------------------------------------------------------------------
> > > EditLive Enterprise is the world's most technically advanced content
> > > authoring tool. Experience the power of Track Changes, Inline Image
> > > Editing and ensure content is compliant with Accessibility \
> > > Checking.http://p.sf.net/sfu/ephox-dev2dev
> > > _______________________________________________
> > > Hsqldb-user mailing \
> > > listHsqldb-user@lists.sourceforge.nethttps://lists.sourceforge.net/lists/listinfo/hsqldb-user
> > >
> > >
> > >
> > >
> > > ------------------------------------------------------------------------------
> > > EditLive Enterprise is the world's most technically advanced content
> > > authoring tool. Experience the power of Track Changes, Inline Image
> > > Editing and ensure content is compliant with Accessibility Checking.
> > > http://p.sf.net/sfu/ephox-dev2dev
> > > _______________________________________________
> > > Hsqldb-user mailing list
> > > Hsqldb-user@lists.sourceforge.net
> > > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> > >
> > >
> >
>
[Attachment #5 (text/html)]
I solved my problem described in my previous message. We were forgetting to invoke \
next() on the Result.<br><br><div class="gmail_quote">On Fri, Jun 17, 2011 at 9:35 \
AM, Allen Riddle <span dir="ltr"><<a \
href="mailto:allenriddle@gmail.com">allenriddle@gmail.com</a>></span> wrote:<br> \
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex;">I'm not able to get CALL IDENTITY() to work with \
1.8.1.3. I've got a small test application that just has a main that simply \
creates a JDBC connection, does an insert and then does a CALL IDENTITY(). However, \
when I try to get the id off the ResultSet, I get an exception saying that no data is \
available. I've tried this against the in memory and stand alone (file). Is \
this call not supported in those modes, does it only work with a stand alone server? \
Below is my sample, any help would be appreciated. Thanks.<div>
<br></div><div><p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span><span style="color:#941166">static</span> \
Connection <span style="color:#2024c8">conn</span>;</p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco;color:#5476c8"><span \
style="color:#000000"><span style="white-space:pre-wrap"> </span></span>/**</p> <p \
style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco;color:#5476c8"><span \
style="white-space:pre-wrap"> </span> * <span style="color:#91afc9">@param</span> \
args</p> <p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px \
Monaco;color:#5476c8"><span style="white-space:pre-wrap"> </span> */</p> <p \
style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span><span style="color:#941166">public</span> <span \
style="color:#941166">static</span> <span style="color:#941166">void</span> \
main(String[] args) {</p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco;color:#4c9073"><span \
style="color:#000000"><span style="white-space:pre-wrap"> </span></span>// <span \
style="color:#91afc9">TODO</span> Auto-generated method stub</p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco;min-height:15.0px"><span \
style="white-space:pre-wrap"> </span></p> <p style="margin:0.0px 0.0px 0.0px \
0.0px;font:11.0px Monaco"><span style="white-space:pre-wrap"> </span> <span \
style="color:#941166">try</span></p> <p style="margin:0.0px 0.0px 0.0px \
0.0px;font:11.0px Monaco"><span style="white-space:pre-wrap"> </span> {</p> <p \
style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> Class.forName(<span \
style="color:#4830fa">"org.hsqldb.jdbcDriver"</span>).newInstance();</p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco;color:#4830fa"><span \
style="color:#000000"><span style="white-space:pre-wrap"> </span> String url = \
</span>"jdbc:hsqldb:file:/Users/ariddle/Desktop/testdb"<span \
style="color:#000000">;</span></p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> <span style="color:#2024c8">conn</span> = \
DriverManager.getConnection(url, <span style="color:#4830fa">"SA"</span>, \
<span style="color:#4830fa">""</span>);</p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> doTests();</p> <p style="margin:0.0px \
0.0px 0.0px 0.0px;font:11.0px Monaco"><span style="white-space:pre-wrap"> </span> \
<span style="color:#2024c8">conn</span>.close();</p> <p style="margin:0.0px 0.0px \
0.0px 0.0px;font:11.0px Monaco"><span style="white-space:pre-wrap"> </span> }</p> \
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> <span style="color:#941166">catch</span> \
(ClassNotFoundException ex) {System.<span \
style="color:#2024c8">err</span>.println(ex.getMessage());}</p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> <span style="color:#941166">catch</span> \
(IllegalAccessException ex) {System.<span \
style="color:#2024c8">err</span>.println(ex.getMessage());}</p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> <span style="color:#941166">catch</span> \
(InstantiationException ex) {System.<span \
style="color:#2024c8">err</span>.println(ex.getMessage());}</p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> <span style="color:#941166">catch</span> \
(SQLException ex) {System.<span \
style="color:#2024c8">err</span>.println(ex.getMessage());}</p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px \
Monaco;min-height:15.0px"><br></p> <p style="margin:0.0px 0.0px 0.0px \
0.0px;font:11.0px Monaco"><span style="white-space:pre-wrap"> </span>}</p> <p \
style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco;min-height:15.0px"><span \
style="white-space:pre-wrap"> </span></p> <p style="margin:0.0px 0.0px 0.0px \
0.0px;font:11.0px Monaco;color:#941166"><span style="color:#000000"><span \
style="white-space:pre-wrap"> </span></span>public<span style="color:#000000"> \
</span>static<span style="color:#000000"> </span>void<span style="color:#000000"> \
doTests() {</span></p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> <span style="color:#941166">try</span></p> \
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> {</p> <p style="margin:0.0px 0.0px 0.0px \
0.0px;font:11.0px Monaco"><span style="white-space:pre-wrap"> </span> Statement \
st = <span style="color:#2024c8">conn</span>.createStatement();</p> <p \
style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco;min-height:15.0px"><span \
style="white-space:pre-wrap"> </span> </p> <p style="margin:0.0px 0.0px 0.0px \
0.0px;font:11.0px Monaco;color:#4830fa"><span style="color:#000000"><span \
style="white-space:pre-wrap"> </span> st.executeUpdate(</span>"CREATE TABLE \
campaign (id INTEGER generated by default as identity (start with 1), campaign_id \
INTEGER NULL);"<span style="color:#000000">);</span></p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco;color:#4830fa"><span \
style="color:#000000"><span style="white-space:pre-wrap"> </span> \
st.executeUpdate(</span>"INSERT INTO campaign (campaign_id) VALUES \
(100)"<span style="color:#000000">);</span></p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> PreparedStatement ps = <span \
style="color:#2024c8">conn</span>.prepareStatement(<span \
style="color:#4830fa">"CALL IDENTITY()"</span>);</p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> ResultSet result = ps.executeQuery();</p> \
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> <span style="color:#941166">int</span> \
<span style="text-decoration:underline">identity</span> = result.getInt(1);</p>
<p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> result.close();</p> <p style="margin:0.0px \
0.0px 0.0px 0.0px;font:11.0px Monaco"><span style="white-space:pre-wrap"> </span> \
}</p> <p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> <span style="color:#941166">catch</span> \
(SQLException ex)</p> <p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px \
Monaco"><span style="white-space:pre-wrap"> </span> {</p> <p style="margin:0.0px \
0.0px 0.0px 0.0px;font:11.0px Monaco"><span style="white-space:pre-wrap"> </span> \
System.<span style="color:#2024c8">err</span>.println(ex.getMessage());</p><div><div></div><div \
class="h5"> <p style="margin:0.0px 0.0px 0.0px 0.0px;font:11.0px Monaco"><span \
style="white-space:pre-wrap"> </span> }</p> <p style="margin:0.0px 0.0px 0.0px \
0.0px;font:11.0px Monaco"><span style="white-space:pre-wrap"> </span>}</p><br><div \
class="gmail_quote">On Thu, Jun 16, 2011 at 4:31 PM, Allen Riddle <span \
dir="ltr"><<a href="mailto:allenriddle@gmail.com" \
target="_blank">allenriddle@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">Thanks Fred. Yesterday, I went down the path of using \
Retrotranslator, but was running into issues. I'm not sure if I was sending in \
the correct switches on the command. It would be cool to get that vetted out in \
order to use the HSQDB 2.x features, maybe I'll take another shot at it. \
Thanks.<div>
<div></div><div><br>
<br><div class="gmail_quote">On Thu, Jun 16, 2011 at 4:06 PM, Fred Toussi <span \
dir="ltr"><<a href="mailto:fredt@users.sourceforge.net" \
target="_blank">fredt@users.sourceforge.net</a>></span> wrote:<br><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">
<div><div style="font-family:Arial;font-size:medium" dir="ltr"><div>
This wasn't supported back then.</div>
<div>
</div>
<div>
The old method for getting the value of an identity column is by using CALL \
IDENTITY() after an insert. You might be able to put this call in the \
getGeneratedKeys() method of PareparedStatement and return the result set that it \
returns.</div>
<div>
</div>
<div>
Alternatively, you might try the "retro" tools listed here <a \
href="http://retrotranslator.sourceforge.net/" \
target="_blank">http://retrotranslator.sourceforge.net/</a> on a Java 5 compiled \
HSQLDB jar and let us know if you get any usable results</div>
<div>
</div>
<div>
Fred</div>
<div>
</div>
<div>
</div>
<div>
<div><div><div></div><div>
<div>
On Thu, 16 Jun 2011 15:30 -0500, "Allen Riddle" <<a \
href="mailto:allenriddle@gmail.com" target="_blank">allenriddle@gmail.com</a>> \
wrote:</div> </div></div><blockquote type="cite"><div><div></div><div>
Hello,
<div>
</div>
<div>
Now that we've backed our version of HSQLDB down to 1.8.1.3, we are getting a \
function not supported exception when trying to execute prepared statements using \
generated key holders. If I understand the 1.8.x doc correctly, this is supported. \
However, when I look at the source code, I see the following defined:</div>
<div>
</div>
<div>
<div>
public synchronized PreparedStatement prepareStatement(String sql,</div>
<div>
int autoGeneratedKeys) throws SQLException {</div>
<div>
</div>
<div>
checkClosed();</div>
<div>
</div>
<div>
throw Util.notSupported();</div>
<div>
}</div>
<div>
</div>
<div>
</div>
<div>
I'm running on Java 1.4 against HSQLDB 1.8.1.3, what is the best way to get \
at the auto generated key? Any help is appreciated. Thanks.</div> </div>
</div></div><pre>------------------------------------------------------------------------------
EditLive Enterprise is the world's most technically advanced content
authoring tool. Experience the power of Track Changes, Inline Image
Editing and ensure content is compliant with Accessibility Checking.
<a href="http://p.sf.net/sfu/ephox-dev2dev" \
target="_blank">http://p.sf.net/sfu/ephox-dev2dev</a> </pre>
<pre>_______________________________________________
Hsqldb-user mailing list
<a href="mailto:Hsqldb-user@lists.sourceforge.net" \
target="_blank">Hsqldb-user@lists.sourceforge.net</a> <a \
href="https://lists.sourceforge.net/lists/listinfo/hsqldb-user" \
target="_blank">https://lists.sourceforge.net/lists/listinfo/hsqldb-user</a>
</pre>
</blockquote>
</div>
</div>
<div>
</div>
</div></div><br>------------------------------------------------------------------------------<br>
EditLive Enterprise is the world's most technically advanced content<br>
authoring tool. Experience the power of Track Changes, Inline Image<br>
Editing and ensure content is compliant with Accessibility Checking.<br>
<a href="http://p.sf.net/sfu/ephox-dev2dev" \
target="_blank">http://p.sf.net/sfu/ephox-dev2dev</a><br>_______________________________________________<br>
Hsqldb-user mailing list<br>
<a href="mailto:Hsqldb-user@lists.sourceforge.net" \
target="_blank">Hsqldb-user@lists.sourceforge.net</a><br> <a \
href="https://lists.sourceforge.net/lists/listinfo/hsqldb-user" \
target="_blank">https://lists.sourceforge.net/lists/listinfo/hsqldb-user</a><br> \
<br></blockquote></div><br> </div></div></blockquote></div><br></div></div></div>
</blockquote></div><br>
------------------------------------------------------------------------------
EditLive Enterprise is the world's most technically advanced content
authoring tool. Experience the power of Track Changes, Inline Image
Editing and ensure content is compliant with Accessibility Checking.
http://p.sf.net/sfu/ephox-dev2dev
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic