Thread: Does PG's JDBC support prepared statements at all?
There is a bunch of documentation for prepared statements in PG's
JDBC, it seems that the only thing prepared statements do is throw
exceptions.
Here's some code I'm trying:
String newvalue = "This is a new value";
int accountnumber = 54;
String qstring = "UPDATE foo SET message = '?' WHERE number = ?";
PreparedStatement st = db.prepareStatement(qstring);
st.setString(1, newvalue);
st.setInt(2, accountnumber);
st.execute();
st.clearParameters();
st.close();
and I always get a Parameter index out of range error, which seems
impossible. Any idea what's going on?
On Sat, Oct 20, 2001 at 11:14:22PM -0000, Dr. Evil wrote: > > There is a bunch of documentation for prepared statements in PG's > JDBC, it seems that the only thing prepared statements do is throw > exceptions. > > Here's some code I'm trying: > > String newvalue = "This is a new value"; > int accountnumber = 54; > String qstring = "UPDATE foo SET message = '?' WHERE number = ?"; > PreparedStatement st = db.prepareStatement(qstring); > st.setString(1, newvalue); > st.setInt(2, accountnumber); > st.execute(); > st.clearParameters(); > st.close(); > > and I always get a Parameter index out of range error, which seems > impossible. Any idea what's going on? You shouldn't quote the ? for the string. By calling the setString() method, it will add the quotes for you. So I guess what is happening is that the preparedstatement parser ignores quoted question marks and just finds 1 variable, when you call setstring on 1 it sets the number= part, and then when you call setInt(2) you are getting the index out of range. The proper qstring should be: "UPDATE foo SET message = ? WHERE number = ?" Cheers, Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com tom@nooper.com - Testing - http://nooper.co.jp/labs
On Sun, 21 Oct 2001 11:37:29 +0900, you wrote: >So I guess what is happening is that the preparedstatement parser >ignores quoted question marks I hope not. I hope it sets the field to a literal '?' :-) Regards, René Pijlman <rene@lab.applinet.nl>
Remove the single quotes from '?' there is no need for them.
Dave
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dr. Evil
Sent: October 20, 2001 7:14 PM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Does PG's JDBC support prepared statements at all?
There is a bunch of documentation for prepared statements in PG's JDBC,
it seems that the only thing prepared statements do is throw exceptions.
Here's some code I'm trying:
String newvalue = "This is a new value";
int accountnumber = 54;
String qstring = "UPDATE foo SET message = '?' WHERE
number = ?";
PreparedStatement st = db.prepareStatement(qstring);
st.setString(1, newvalue);
st.setInt(2, accountnumber);
st.execute();
st.clearParameters();
st.close();
and I always get a Parameter index out of range error, which seems
impossible. Any idea what's going on?
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On 20 Oct 2001 23:14:22 -0000, you wrote: >There is a bunch of documentation for prepared statements in PG's >JDBC, it seems that the only thing prepared statements do is throw >exceptions. I suggest you read some basic JDBC documentation. This is really not PostgreSQL specific. Checkout http://www.javaskyline.com/learnjdbc.html Regards, René Pijlman <rene@lab.applinet.nl>
On Sun, Oct 21, 2001 at 02:06:21PM +0200, Rene Pijlman wrote: > On Sun, 21 Oct 2001 11:37:29 +0900, you wrote: > >So I guess what is happening is that the preparedstatement parser > >ignores quoted question marks > > I hope not. I hope it sets the field to a literal '?' :-) I meant ignores them in terms of not handling them as normal variable placement holders or whatever the correct terminology is :) But I guess that wasn't crystal clear in the way that I phrased it. Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com tom@nooper.com - Testing - http://nooper.co.jp/labs
Hi folks I'd like to make the JDBC driver comple with JDK1.4. I've got the following list of errors and potential remedies. Does anyone have any objections if I start work on this? The following are the methods defined in JDK1.4 which are not currently implemented in the JDBC driver: javax.transaction.RollbackException is not defined in 1.4. -- this appears to be renamed to TransationRolledbackException Statement.getMoreResults(int) -- defines the behaviour of the current ResultSet when the next one is retrieved. Appears that we need to simply perform appropriate operations on the result instance field of jdbc2.Statement. PreparedStatement.setURL(java.net.URL) -- Presumably convert the URL to a string and set it like everything else? CallableStatement.registerOutParameter(String, int) -- There are a bunch of these actually, javac is only listing one. They all take parameter names rather than indexes. Should be straightforward to fix? Connection.setHoldability(int) -- Not too sure about this. It defines the "holdability" of result sets between commits. Need to investigate further (tips?) DataBaseMetaData.supportsSavepoints() -- Presumably: "return false;" ResultSet.getURL(int) -- Presumably, "return new URL(getParameter...));" PGblob.setBytes(long, byte[]) PGclob.setString(long, String) -- This stuff looks harder, there are setBinaryStream() etc methods. Not sure how to go about this at this stage. Maybe an Unsupported exception? ClientConnection.setHoldability(int) -- presumably, pass the call to Connection.setHoldability()? Feedback/comments welcome. Cheers Mark
Hi Attached is a unified diff to DatabaseMetadata from the jdbc2 interface. It fixes a bug where the metadata does not include the scale/precision of the database columns. We have been using this patch successfully for about 2 years, it would be great to finally get it into the sources. Suggestions/comments welcome. Cheers Mark
Attachment
> Attached is a unified diff to DatabaseMetadata from the jdbc2 interface. > It fixes a bug where the metadata does not include the scale/precision > of the database columns. > > We have been using this patch successfully for about 2 years, it would > be great to finally get it into the sources. > > Suggestions/comments welcome. There is no patch attached. Also, have you checkes our current CVS/snapshots to make sure this is still a problem? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Mark, Any help on moving the jdbc code base forward is more than welcome. In briefly looking over your suggestions below they all seem fine to me. (Of course the devil is in the details). I would also suggest looking at http://lab.applinet.nl/postgresql-jdbc for a list of where we are lacking in jdbc2 support. thanks, --Barry Mark Lillywhite (pg-jdbc) wrote: > Hi folks > > I'd like to make the JDBC driver comple with JDK1.4. I've got the > following list of errors and potential remedies. Does anyone have any > objections if I start work on this? > > The following are the methods defined in JDK1.4 which are not currently > implemented in the JDBC driver: > > javax.transaction.RollbackException is not defined in 1.4. > -- this appears to be renamed to TransationRolledbackException > > Statement.getMoreResults(int) > -- defines the behaviour of the current ResultSet when the next one is > retrieved. Appears that we need to simply perform appropriate operations > on the result instance field of jdbc2.Statement. > > PreparedStatement.setURL(java.net.URL) > -- Presumably convert the URL to a string and set it like everything > else? > > CallableStatement.registerOutParameter(String, int) > -- There are a bunch of these actually, javac is only listing one. They > all take parameter names rather than indexes. Should be straightforward > to fix? > > Connection.setHoldability(int) > -- Not too sure about this. It defines the "holdability" of result sets > between commits. Need to investigate further (tips?) > > DataBaseMetaData.supportsSavepoints() > -- Presumably: "return false;" > > ResultSet.getURL(int) > -- Presumably, "return new URL(getParameter...));" > > PGblob.setBytes(long, byte[]) > PGclob.setString(long, String) > -- This stuff looks harder, there are setBinaryStream() etc methods. Not > sure how to go about this at this stage. Maybe an Unsupported exception? > > ClientConnection.setHoldability(int) > -- presumably, pass the call to Connection.setHoldability()? > > Feedback/comments welcome. > > Cheers > Mark > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >