JDBC prepared statements: actually not server prepared

(moved to pgsql-jdbc, as that's where JDBC development is usually discussed)

Henner Zeller wrote:

Hi,

While tracking down a query with a JDBC prepared statement, I
noticed, that the statement actually wasn't prepared but sent to the
server as 'normal' Statement. In my case, this led to a very slow query
since the normal statement does not propagate the correct type -- in my
case I queried an indexed coloumn of type 'int8', which
was correctly passed to the PreparedStatement with setLong() -- however it
was sent with a different type to the server (the
AbstractJdbc1Statement::m_bindTypes[] are never used in this case).

I believe that 7.5 fixes the int4-vs-int8 issue. I assume you're running
a pre-7.5 server?

The reason for this is, that by default, the PreparedStatement is not
configured to use a server side prepared statement. I assume, this is
unintended.

No, it's intentional.

In your case, it's actually not a PREPARE vs non-PREPARE issue at all;
it's a question of how the parameter values the driver generates are
interpreted by the query parser. Defaulting setUseServerPrepare to true
seems like the wrong solution for this. There are queries where use of
PREPARE will slow things down, and PREPARE against a 7.4 server does not
correctly return INSERT/UPDATE/DELETE rowcounts or resultset metadata
(against 7.5, I believe the rowcounts are correct but you still lose the
metadata).

IIRC, the latter problems should be detected by the driver's regression
tests. Did you rerun the tests with your change applied?

I have patches pending that replace use of PREPARE/EXECUTE with the v3
protocol's Parse/Bind messages (Parse/Bind are used for all queries, and
if server-side preparation is enabled then the queries are bound to
named statements and reused). The Bind message allows us to pass typed
parameters, bypassing the query-level parser (and associated type
inference) for the parameter values entirely. This should solve your
problem without the problems associated with PREPARE/EXECUTE.

-O

Please post this to the jdbc list.

Dave
On Sun, 2004-06-20 at 08:09, Henner Zeller wrote:

Hi,

While tracking down a query with a JDBC prepared statement, I
noticed, that the statement actually wasn't prepared but sent to the
server as 'normal' Statement. In my case, this led to a very slow query
since the normal statement does not propagate the correct type -- in my
case I queried an indexed coloumn of type 'int8', which
was correctly passed to the PreparedStatement with setLong() -- however it
was sent with a different type to the server (the
AbstractJdbc1Statement::m_bindTypes[] are never used in this case).

The reason for this is, that by default, the PreparedStatement is not
configured to use a server side prepared statement. I assume, this is
unintended.

I fixed this by adding this to my local version (current CVS):

------------------
--- org/postgresql/jdbc3/Jdbc3PreparedStatement.java    29 Mar 2004 19:17:12 -0000      1.6
+++ org/postgresql/jdbc3/Jdbc3PreparedStatement.java    20 Jun 2004 11:54:00 -0000
@@ -13,6 +13,7 @@
public Jdbc3PreparedStatement(Jdbc3Connection connection, String
sql) throws SQLException
{
super(connection, sql);
+                setUseServerPrepare(true);
}

public BaseResultSet createResultSet (Field[] fields,
java.util.Vector tuples, String status, int updateCount, long insertOID)
throws SQLException
--------------
(same for Jdbc2PreparedStatement and Jdbc1PreparedStatement).

If this is the right way to do ? And if so .. could someone apply
this patch ?

Thanks,
-hen
B�cher kaufen und Freie Software f�rdern | http://bookzilla.de/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

!DSPAM:40d5815f68141026638008!

--
Dave Cramer
519 939 0336
ICQ # 14675561