Thread: Bug? report : PreparedStatement.setObject(java.util.Date) don't work

Hello.

I've faced problem similar to http://archives.postgresql.org/pgsql-jdbc/2007-06/msg00070.php

Source:
-------------------------------------------------------------------------

package test;

import java.sql.*;
import java.util.*;

public class TestDate{
    public static void main(String[] ags){
        try{
            Class.forName("org.postgresql.Driver");

            Connection con = DriverManager.getConnection(
                "jdbc:postgresql:dbname",
                "user",
                "pass");

            PreparedStatement stmt = 
con.prepareStatement("select * from table_name where column_name = ?");
            stmt.setObject(1, new java.util.Date());
            ResultSet rs = stmt.executeQuery();

            while(rs.next
()){
                System.out.println( rs.getDate(1) );
            }
        }catch(Exception e){
            e.printStackTrace();
        }
    }
}

-------------------------------------------------------------------------


Message:
-------------------------------------------------------------------------
org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.Date. Use setObject() with an explicit Types value to specify the type to use.

        at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1737)
        at test.TestDate.main(TestDate.java:16)
-------------------------------------------------------------------------


It seemed org.postgresql.jdbc2.AbstractJdbc2Statement#setObject(int, Object) can't
map java.util.Date to java.sql.Types#DATE.
So I modified AbstractJdbc2Statement.java to accept java.util.Date,then it worked.


Modification:
-------------------------------------------------------------------------
diff postgresql-jdbc-8.2-506.src/org/postgresql/jdbc2/AbstractJdbc2Statement.java postgresql-jdbc-8.2-506.src_mod/org/postgresql/jdbc2/AbstractJdbc2Statement.java

1717a1718,1719
>         else if (x instanceof java.util.Date)
>             setDate(parameterIndex, new java.sql.Date(((java.util.Date)x).getTime()));
-------------------------------------------------------------------------


Is this good solution?
Please advice.

Gotaro AMENOMORi

First I would like to say I'm new to PostgreSQL, so I may not have a
clue of what I'm taking
about, but I have done some reading in the manual and have been testing
the various data
types in PostgreSQL with MyJSQLView. These comments are just notes about
what I have
found, I don't know if these are bugs, or standard behavior. In the end
it will not matter
because I will find ways around them in the application.

Data Types: I do not consider geometric, network address, other objects
to be standard primary
data types. These objects are not defined in most languages as types and
they are not defined
in the java.sql types. As the gentleman states in is problem PostgreSQL
seems to be strongly
typed and does not appear to allow set'type'() in preparedstatements to
be allowed unless the
argument for data is of same type as set. This seems fine, but the
non-standard types, ex.
geometric, must then be created. Unfortunately these types do not exist
in the standard java
language which seems to me they then must be imported from postgresql or
created via new classes.
This also is not a major problem, but in the first I must document to
users the need for additional
dependencies or in the latter add classes to the program, I would prefer
to keep things as simple
as possible. I have already found a way around this for the the network
address types, and just not
decided on how to handle with geometric types until I've done some more
reading.

The test database tables and main class used with regard to this subject
in MyJSQLView may be
obtained from the CVS repository for  MyJSQLView at
http://myjsqlview.cvs.sourceforge.net/myjsqlview/
Of course the whole app. may also be checked out to run the program on
the test databases tables.
The import SQL feature will work for loading the test tables in the
current development.

Same type of error I believe as posted:

MyJSQLView_Main createGUI() Connection Created
MyJSQLView_Main createGUI() Connection Closed
TableTabPanel actionPerformed() Connection Created
TableTabPanel actionPerformed() Connection Closed
TableEntryForm addUpdateTableEntry() Connection Created
TableEntryForm addUpdateTableEntry()
SQLException: ERROR: column "point_type" is of type point but expression
is of type character varying
SQLState: 42804
VendorError: 0
TableEntryForm addUpdateTableEntry() rollback Connection Closed

files: MyJSQLView/test/postgresqlTypes.sql
         MyJSQLView/test/postgresqlTypes_loadTest.sql
         MyJSQLView/src/Panels/net/danap/myjsqlview/TableEntryForm.java
Class Method addUpdateTableEtnry().

danap

雨森郷太郎 wrote:

> It seemed org.postgresql.jdbc2.AbstractJdbc2Statement#setObject(int, Object) can't
> map java.util.Date to java.sql.Types#DATE.

Try using java.sql.Date instead of java.util.Date, there should be a
mapping for that.

-O

Thank you for reply.

>Try using java.sql.Date instead of java.util.Date, there should be a
>mapping for that.

It should work, I agree.

However, Is there reason to refrain from supporting java.util.Date ?

Originaly, I developed my Java Web application on postgres version7.4.
On the environment, java.util.Date to java.sql.Types#DATE mapping
worked well via JDBC ver7.4, like
http://jdbc.postgresql.org/download/pg74.216.jdbc3.jar
I can't see sources of the version, but I guess it has mapping for
java.util.Date.

Recently, I upgraded my Postgres DBMS to ver.8.2, and replaced
JDBC to "8.2 Build 506" then faced problem.

Now, I'm using source compiled Dirver with modification I reported.
I hope java.util.Date mapping to be original specification.

How's this?

Gotaro AMENOMORi

雨森郷太郎 wrote:

> However, Is there reason to refrain from supporting java.util.Date ?

Despite the name, java.util.Date actually contains both date and time
information, and mapping it to SQL's DATE would silently lose data.
That's why java.sql.Date exists in the first place: it's a deliberately
limited subclass that explicitly only carries date information.

We could map java.util.Date to TIMESTAMP, I suppose, but I think that'll
cause even more confusion since there's also java.sql.Timestamp (that
gives nanosecond precision vs. java.util.Date's millisecond precision).
Most of the time, using java.util.Date with JDBC means you've got an
application bug, and I don't think silently covering it up is a great idea.

-O

>application bug, and I don't think silently covering it up is a great idea.

I understand. I would change my Application.
Thank you for your advice.