Does PG's JDBC support prepared statements at all?

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>

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

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

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

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:

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