ResultSet.getObject() of a DATE or TIME column returns a java.sql.Timestamp object on ASA 6.0
John Chiu
This problem is on Adaptive Sql Anywhere 6.0 using jConnect 5.
---------------------------------------------------------------------------
I ran into this in jConnect 4.x and posted a message, but never got a
satisfactory answer. I was hoping that jConnect 5.x will fix it, but
I am still running into it. The problem is that if I use
ResultSet.getObject()
on a DATE or TIME column, the driver returns a java.sql.Timestamp
object instead of a java.sql.Date or java.sql.Time object. I have
enclosed
a sample program that shows this with the executed output.
Thanks in advance for any help.
John Chiu
SilverStream Software, Inc.
jc...@silverstream.com
vvvvvvvvvvvvvvvv test program to follow vvvvvvvvvvvvvvvvvv
import java.sql.*;
import java.util.*;
/*
// Show that with jConnect 5.0 that columns of DATE, TIME, and TIMESTAMP
// are all returned as java.sql.Timestamp objects when
ResultSet.getObject()
// is used.
//
// For help, run "java asatimestamp5 -?"
//
// This is sample output running on NT server, using ASA 6.0.1.1165 with
// jConnect 5.0.
// ---------------------------------------------------------------------
C:\Dev\bin>java asatimestamp5
Sybase jConnect (TM) for JDBC (TM) 5.0 Development and Unsupported
Version
It is an unsupported product and is not intended for deployment. It
does not in
clude free technical support. You can purchase technical support on an
annual o
r pay-per-issue basis. A Discussion Newsgroup is available on
news://forums.syba
se.com/sybase.public.jconnect50. For additional information on services
or run-t
ime license, please check the Sybase website (www.sybase.com) or call
1-800-8-SY
BASE or +1-510-922-3500 outside of the USA and Canada.
Copyright 1998-1999 Sybase, Inc.
All Rights Reserved
==> dropping table tabtimestamp
==> creating table tabtimestamp
SQL = CREATE TABLE tabtimestamp (xtime TIME, xtimestamp TIMESTAMP,
xdate DATE)
==> CREATE completed
==> attempting insert of...
Time : 01:01:01
Timestamp : 1999-04-15 04:03:01.0
Date : 1999-04-15
...executing INSERT
++> SELECTING ...
--> Retrieving values from database using ResultSet.getObject() ...
time : object type=java.sql.Timestamp, val=1999-03-15 01:01:01.0
timestamp : object type=java.sql.Timestamp, val=1999-04-15 04:03:01.0
date : object type=java.sql.Timestamp, val=1999-04-15 00:00:00.0
C:\Dev\bin>
//
--------------------------------------------------------------------------
//
//
*/
public class asatimestamp5
{
static void dp(String s) { System.out.println(s); }
static final String kDROP = "DROP TABLE tabtimestamp";
static final String kCREATE =
"CREATE TABLE tabtimestamp (xtime TIME, xtimestamp TIMESTAMP, xdate
DATE)";
static final String kJConnect50 = "com.sybase.jdbc2.jdbc.SybDriver";
private static String g_url =
"jdbc:sybase:Tds:madonna:2638/?SERVICENAME=silvermasterasa";
private static String g_user = "dba";
private static String g_pwd = "sql";
static void usage()
{
dp("");
dp("======================================================");
dp("Usage :");
dp("\t-L <JDBC URL> - JDBC Driver Specific URL");
dp("\t-U <user> - user account to login");
dp("\t-P <password> - pass word for user account");
dp("======================================================");
}
static public void main(String[] args)
{
Connection con = null;
int len = args.length;
for (int i=0; i<len; i++) {
if (args[i].equals("-L")) {
// specify driver specific URL
g_url = args[++i];
dp("-L="+g_url);
}
else if (args[i].equals("-U")) {
// specify new User
g_user = args[++i];
dp("-U="+g_user);
}
else if (args[i].equals("-P")) {
// specify new Password
g_pwd = args[++i];
dp("-P=******");
}
else if (args[i].equals("-?")) {
usage();
System.exit(0);
}
}
try {
// connect with default id/password
// load the driver
Driver jdbcDriver = (Driver)Class.forName(kJConnect50).newInstance();
Properties info = new Properties();
info.put("user", g_user);
info.put("password", g_pwd);
// attempt to get jdbc connection
con = jdbcDriver.connect(g_url, info);
// drop table
Statement stmt = con.createStatement();
try {
dp("==> dropping table tabtimestamp");
stmt.execute(kDROP );
}
catch (SQLException x) {
// must likely because no such table exists...ignore
}
// create table
dp("==> creating table tabtimestamp\n SQL = "+kCREATE);
stmt.execute(kCREATE);
dp("==> CREATE completed");
stmt.close();
dp("\n\n");
}
catch (Exception x) {
dp("*** Exception trying to create table : "+x.toString());
dp("... Exiting test");
System.exit(101);
}
try {
// create Time, Date, and Timestamp objects
java.sql.Time xtime = new java.sql.Time(1, 1, 1);
java.sql.Timestamp xtimestamp = new java.sql.Timestamp(99, 3, 15, 4,
3, 1, 0);
java.sql.Date xdate = new java.sql.Date(99, 3, 15);
dp("==> attempting insert of...");
dp(" Time : "+xtime.toString());
dp(" Timestamp : "+xtimestamp.toString());
dp(" Date : "+xdate.toString());
// insert
PreparedStatement dbstmt =
con.prepareStatement("INSERT INTO tabtimestamp(xtime, xtimestamp,
xdate) VALUES (?, ?, ?)");
// bind all the values
dbstmt.setTime(1, xtime);
dbstmt.setTimestamp(2, xtimestamp);
dbstmt.setDate(3, xdate);
dp("...executing INSERT");
int status = dbstmt.executeUpdate();
dbstmt.close();
// try to read it!
dp("\n++> SELECTING ...");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT xtime, xtimestamp, xdate
FROM tabtimestamp");
dp("--> Retrieving values from database using ResultSet.getObject()
...");
while (rs.next()) {
// Object o = rs.getTime(1);
Object otime = rs.getObject(1);
Object otimestamp = rs.getObject(2);
Object odate = rs.getObject(3);
if (otime!=null) {
dp(" time : object type="+otime.getClass().getName()+",
val="+otime.toString());
}
if (otimestamp!=null) {
dp(" timestamp : object type="+otimestamp.getClass().getName()+",
val="+otimestamp.toString());
}
if (odate!=null) {
dp(" date : object type="+odate.getClass().getName()+",
val="+odate.toString());
}
}
con.close();
}
catch (Exception x) {
dp("** Exception : "+x.toString());
}
}
}
Brian McGiverin
John Chiu wrote:
>
> Hi -
>
> This problem is on Adaptive Sql Anywhere 6.0 using jConnect 5.
> ---------------------------------------------------------------------------
>
> I ran into this in jConnect 4.x and posted a message, but never got a
> satisfactory answer. I was hoping that jConnect 5.x will fix it, but
> I am still running into it. The problem is that if I use
> ResultSet.getObject()
> on a DATE or TIME column, the driver returns a java.sql.Timestamp
> object instead of a java.sql.Date or java.sql.Time object. I have
> enclosed
> a sample program that shows this with the executed output.
>
Thanks for the sample program. I have entered this as bug #193263.
- Brian