Thread: timestamp parameter is null

Hello List!

I have the following problem:

I'm trying to execute a simple SQL query with JDBC and I get an
exception, which I don't understand why. Here's the code:

    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection conn =
DriverManager.getConnection("jdbc:postgresql://localhost:5434/dbname",
"user", "pass");
        PreparedStatement stm = conn.prepareStatement("select * from
tablename where ? is not null");
        stm.setTimestamp(1, new Timestamp(new Date().getTime()));
        stm.executeQuery();
    }

The exception is:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
could not determine data type of parameter $1
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:250)
    at Test6.main(Test6.java:15)

The server version is 8.1.0. The Jdbc driver is
postgresql-8.1-404.jdbc3.jar.

The same code runs fine with the 7.4 jdbc driver. It's also okay, if I'm
using any other type than timestamp. Actually, I tried "string" and it
worked.

I wonder what the problem is.

Marci

Papp Márton schrieb:
> Hello List!
>
> I have the following problem:
>
> I'm trying to execute a simple SQL query with JDBC and I get an
> exception, which I don't understand why. Here's the code:
>
>    public static void main(String[] args) throws Exception {
>        Class.forName("org.postgresql.Driver");
>        Connection conn =
> DriverManager.getConnection("jdbc:postgresql://localhost:5434/dbname",
> "user", "pass");
>        PreparedStatement stm = conn.prepareStatement("select * from
> tablename where ? is not null");
>        stm.setTimestamp(1, new Timestamp(new Date().getTime()));
>        stm.executeQuery();
>    }
>
> The exception is:
>
> Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
> could not determine data type of parameter $1
>    at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)
>
>    at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)
>
>    at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
>
>    at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)
>
>    at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346)
>
>    at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:250)
>
>    at Test6.main(Test6.java:15)
>
> The server version is 8.1.0. The Jdbc driver is
> postgresql-8.1-404.jdbc3.jar.
>
> The same code runs fine with the 7.4 jdbc driver. It's also okay, if
> I'm using any other type than timestamp. Actually, I tried "string"
> and it worked.
>
You cannot set a table or column name with a prepared statement with the
protocol in the version 3.
This might only work if you set the protocol to version 2 in the
connection url:

"jdbc:postgresql://localhost:5434/dbname?protocolVersion=2"

But this may have disadvantages, as for example the index search for
columns with BIGINT values
will not work with a standard sql-statement. You would have to write
something like:

SELECT * FROM table WHERE column=(? CAST BIGINT);

--
Roland Walter                   mailto: rwa (at) mosaic-ag (dot) com
MOSAIC SOFTWARE AG               phone: +49 (0) 22 25 / 88 2-44 9
Am Pannacker 3                     fax: +49 (0) 22 25 / 88 2-20 1
D-53340 Meckenheim                http://www.mosaic-ag.com


Die in dieser E-Mail enthaltenen Nachrichten und Anhaenge sind ausschliesslich
fuer den bezeichneten Adressaten bestimmt. Sie koennen rechtlich geschuetzte,
vertrauliche Informationen enthalten. Falls Sie nicht der bezeichnete Empfaenger
oder zum Empfang dieser E-Mail nicht berechtigt sind, ist die Verwendung,
Vervielfaeltigung oder Weitergabe von Nachrichten und Anhaengen untersagt.
Falls Sie diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte
unverzueglich den Absender und vernichten Sie die E-Mail.

This e-mail message and any attachment are intended exclusively for the named
addressee. They may contain confidential information which may also be protected
by professional secrecy. Unless you are the named addressee (or authorised to
receive for the addressee) you may not copy or use this message or any attachment
or disclose the contents to anyone else. If this e-mail was sent to you by mistake
please notify the sender immediately and delete this e-mail.

Hi, Papp,

Papp Márton wrote:

> I'm trying to execute a simple SQL query with JDBC and I get an
> exception, which I don't understand why. Here's the code:
>
>    public static void main(String[] args) throws Exception {
>        Class.forName("org.postgresql.Driver");
>        Connection conn =
> DriverManager.getConnection("jdbc:postgresql://localhost:5434/dbname",
> "user", "pass");
>        PreparedStatement stm = conn.prepareStatement("select * from
> tablename where ? is not null");
>        stm.setTimestamp(1, new Timestamp(new Date().getTime()));
>        stm.executeQuery();
>    }

Are you shure that your query is correct?

This would return no rows if you set parameter 1 to null, and the whole
table if you set your parameter to anything not-null.

If you really want to do this, use "select * from tablename where
?::timestamp with time zone is not null"

hth,
schabi


--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org