Name: mc57594 Date: 03/12/97


In java.sql.Types, DATE, TIME, and TIMESTAMP are declared as
having the values 91, 92, and 93, respectively. When I access
Oracle 7.3 on Windows NT and Microsoft SQL Server 6.5 on NT via
the JdbcOdbcDriver jdbc-odbc bridge provided with JDK 1.1, I see
the ANSI SQL DATE data type given as 11, which is the corresponding
ODBC type, both with Intersolv's and Visigenic's ODBC drivers.
This is not a valid type according to java.sql.Types.

Below are the various metadata dumps for Oracle and Sybase with the
JdbcObdcDriver and Weblogic drivers. I've also included the source
code used to produce the output.

===================================================

++++Oracle 7.3, Windows NT, Intersolv Driver:
TYPE_NAME,DATA_TYPE,COLUMN_SIZE,LITERAL_PREFIX,LITERAL_SUFFIX,CREATE_PARAMS,NULL
ABLE,CASE_SENSITIVE,SEARCHABLE,UNSIGNED_ATTRIBUTE,FIXED_PREC_SCALE,AUTO_UNIQUE,L
OCAL_TYPE_NAME,MINIMUM_SCALE,MAXIMUM_SCALE,SQL_DATA_TYPE,SQL_DATETIME_SUB,NUM_PR
EC_RADIX,INTERVAL_PRECISION
LONG RAW,-4,2147483647,',',null,1,0,0,null,0,null,LONG RAW,null,null,-4,null,nul
l,null
RAW,-3,255,',',max length,1,0,3,null,0,null,RAW,null,null,-3,null,null,null
LONG,-1,2147483647,',',null,1,1,0,null,0,null,LONG,null,null,-1,null,null,null
CHAR,1,255,',',length,1,1,3,null,0,null,CHAR,null,null,1,null,null,null
NUMBER,3,38,null,null,precision,scale,1,0,3,0,0,0,NUMBER,-84,127,3,null,10,null
NUMBER,8,15,null,null,null,1,0,3,0,0,0,NUMBER,null,null,8,null,10,null
DATE,11,19,null,null,null,1,0,3,null,0,null,DATE,0,0,9,3,null,null
VARCHAR2,12,2000,',',max length,1,1,3,null,0,null,VARCHAR2,null,null,12,null,nul
l,null

===================================================
++++Oracle 7.3, Windows NT, VisigenicDriver:
TYPE_NAME,DATA_TYPE,PRECISION,LITERAL_PREFIX,LITERAL_SUFFIX,CREATE_PARAMS,NULLAB
LE,CASE_SENSITIVE,SEARCHABLE,UNSIGNED_ATTRIBUTE,MONEY,AUTO_INCREMENT,LOCAL_TYPE_
NAME,MINIMUM_SCALE,MAXIMUM_SCALE
LONG RAW,-4,2147483647,',',null,1,0,0,null,0,null,LONG RAW,null,null
RAW,-3,255,',',max length,1,0,3,null,0,null,RAW,null,null
LONG,-1,2147483647,',',null,1,0,0,null,0,null,LONG,null,null
CHAR,1,255,',',max length,1,1,3,null,0,null,CHAR,null,null
NUMBER,2,38,null,null,precision,scale,1,0,3,0,0,0,NUMBER,0,38
NUMBER,3,38,null,null,precision,scale,1,0,3,0,0,0,NUMBER,0,38
INTEGER,4,38,null,null,null,1,0,3,0,0,0,NUMBER,0,0
FLOAT,6,15,null,null,null,1,0,3,0,0,0,FLOAT,null,null
DATE,11,19,{ ts ','},null,1,0,3,null,0,null,DATE,0,0
VARCHAR2,12,2000,',',max length,1,1,3,null,0,null,VARCHAR2,null,null
===================================================
++++Oracle 7.3, Windows NT, Weblogic non-odbc Driver:
TYPE_NAME,DATA_TYPE,PRECISION,LITERAL_PREFIX,LITERAL_SUFFIX,CREATE_PARAMS,NULLABLE,CASE_SENSITIVE,SEARCHABLE,UNSIGNED_ATTRIBUTE,MONEY,AUTO_INCREMENT,LOCAL_TYPE_NAME,MINIMUM_SCALE,MAXIMUM_SCALE,SQL_DATA_TYPE,SQL_DATATIME_SUB,NUM_PREC_RADIX
LONG RAW,-4,2147483647,',',null,1,0,0,0,0,0,LONG RAW,0,0,null,null,null
RAW,-3,255,',',maxlength,1,0,3,0,0,0,RAW,0,0,null,null,null
LONG,-1,2147483647,',',null,1,1,0,0,0,0,LONG,0,0,null,null,null
CHAR,1,255,',',maxlength,1,1,3,0,0,0,CHAR,0,0,null,null,null
NUMBER,3,38,null,null,precision scale,1,0,3,0,0,0,NUMBER,-84,127,null,null,null
VARCHAR2,12,2000,',',maxlength,1,1,3,0,0,0,VARCHAR2,0,0,null,null,null
DATE,93,19,null,null,null,1,0,3,0,0,0,DATE,0,0,null,null,null

===================================================
++++Microsoft SQL Server 6.5, Windows NT, Microsoft ODBC Driver:
TYPE_NAME,DATA_TYPE,PRECISION,LITERAL_PREFIX,LITERAL_SUFFIX,CREATE_PARAMS,NULLABLE,CASE_SENSITIVE,SEARCHABLE,UNSIGNED_ATTRIBUTE,MONEY,AUTO_INCREMENT,LOCAL_TYPE_NAME,MINIMUM_SCALE,MAXIMUM_SCALE,SQL_DATA_TYPE,SQL_DATETIME_SUB,NUM_PREC_RADIX,INTERVAL_PRECISION,USERTYPE
bit,-7,1,null,null,null,0,0,2,null,0,null,bit,0,0,-7,null,2,null,16
tinyint,-6,3,null,null,null,1,0,2,1,0,0,tinyint,0,0,-6,null,10,null,5
tinyint identity,-6,3,null,null,null,0,0,2,1,0,1,tinyint identity,0,0,-6,null,10,null,5
image,-4,2147483647,0x,null,null,1,0,0,null,0,null,image,null,null,-4,null,null,null,20
varbinary,-3,255,0x,null,max length,1,0,2,null,0,null,varbinary,null,null,-3,null,null,null,4
binary,-2,255,0x,null,length,1,0,2,null,0,null,binary,null,null,-2,null,null,null,3
timestamp,-2,8,0x,null,null,0,0,2,null,0,null,timestamp,null,null,-2,null,null,null,80
text,-1,2147483647,',',null,1,0,1,null,0,null,text,null,null,-1,null,null,null,19
char,1,255,',',length,1,0,3,null,0,null,char,null,null,1,null,null,null,1
empid,1,9,',',null,0,0,3,null,0,null,empid,null,null,1,null,null,null,103
numeric,2,28,null,null,precision,scale,1,0,2,0,0,0,numeric,0,28,2,null,10,null,25
numeric() identity,2,28,null,null,precision,0,0,2,0,0,1,numeric() identity,0,0,2,null,10,null,25
decimal,3,28,null,null,precision,scale,1,0,2,0,0,0,decimal,0,28,3,null,10,null,26
money,3,19,$,null,null,1,0,2,0,1,0,money,4,4,3,null,10,null,11
smallmoney,3,10,$,null,null,1,0,2,0,1,0,smallmoney,4,4,3,null,10,null,21
decimal() identity,3,28,null,null,precision,0,0,2,0,0,1,decimal() identity,0,0,3,null,10,null,26
int,4,10,null,null,null,1,0,2,0,0,0,int,0,0,4,null,10,null,7
int identity,4,10,null,null,null,0,0,2,0,0,1,int identity,0,0,4,null,10,null,7
smallint,5,5,null,null,null,1,0,2,0,0,0,smallint,0,0,5,null,10,null,6
smallint identity,5,5,null,null,null,0,0,2,0,0,1,smallint identity,0,0,5,null,10,null,6
float,6,15,null,null,null,1,0,2,0,0,0,float,null,null,6,null,10,null,8
real,7,7,null,null,null,1,0,2,0,0,0,real,null,null,7,null,10,null,23
datetime,11,23,',',null,1,0,3,null,0,null,datetime,3,3,9,3,10,null,12
smalldatetime,11,16,',',null,1,0,3,null,0,null,smalldatetime,0,0,9,3,10,null,22
varchar,12,255,',',max length,1,0,3,null,0,null,varchar,null,null,12,null,null,null,2
sysname,12,30,',',null,0,0,3,null,0,null,sysname,null,null,12,null,null,null,18
id,12,11,',',null,0,0,3,null,0,null,id,null,null,12,null,null,null,101
tid,12,6,',',null,0,0,3,null,0,null,tid,null,null,12,null,null,null,102


============================================================
Program source:
import java.net.URL;
import java.sql.*;


class test2 {
    public static void main(String args[]) {
     System.out.println("Is my jdk1.1 installed correctly?");

try
{
// Load the jdbc-odbc bridge driver
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
//Class.forName ("weblogic.jdbc.oci.Driver");
System.out.println( "Loaded driver..." );

////////////////NOTE: Uncomment one of the following to test a specific connection
//Microsoft drvier against Microsoft SQL Server 6.5
//Connection con = DriverManager.getConnection (
// "jdbc:odbc:LocalServer", "pubs", "pubs" );

           
//Intersolv driver against Oracle 7.3
//Connection con = DriverManager.getConnection (
            // "jdbc:odbc:intersolv", "SCOTT", "TIGER" );

//Visigenic driver driver against Oracle 7.3
Connection con = DriverManager.getConnection (
"jdbc:odbc:visigenic", "SCOTT", "TIGER" );

//Weblogic driver driver against Oracle 7.3
//Connection con = DriverManager.getConnection (
// "jdbc:weblogic:oracle:regatta", "SCOTT", "TIGER" );
///////////////////////////////////////////////////////////////////////////////////////


DatabaseMetaData meta = con.getMetaData();

System.out.println( "Established connection..." );
            
//Get the result set
ResultSet rs = meta.getTypeInfo();

//Display the results set
dispResultSet (rs);

             // Close the result set

             rs.close();

// Close the connection

             con.close();
             
}
catch(ClassNotFoundException e)
{
System.out.println("Can't load the freaking odbc bridge driver: "
+ e.getMessage() );
}
catch(SQLException e)
{
System.out.println("Caught exception: " + e.getMessage() );
}
    }

//-------------------------------------------------------------------
     // dispResultSet
     // Displays all columns and rows in the given result set
     //-------------------------------------------------------------------

     private static void dispResultSet (ResultSet rs)
             throws SQLException
     {
             int i;

             // Get the ResultSetMetaData. This will be used for
             // the column headings

             ResultSetMetaData rsmd = rs.getMetaData ();

             // Get the number of columns in the result set

             int numCols = rsmd.getColumnCount ();

             // Display column headings

             for (i=1; i<=numCols; i++) {
                     if (i > 1) System.out.print(",");
                     System.out.print(rsmd.getColumnLabel(i));
             }
             System.out.println("");
             
             // Display data, fetching until end of the result set

             boolean more = rs.next ();
             while (more) {

                     // Loop through each column, getting the
                     // column data and displaying

                     for (i=1; i<=numCols; i++) {
                             if (i > 1) System.out.print(",");
                             System.out.print(rs.getString(i));
                     }
                     System.out.println("");

                     // Fetch the next result set row

                     more = rs.next ();
             }
     }
}
company - Fidelity Investments , email - ###@###.###
======================================================================

DatabeseMetaData.getTypeInfo() returns following
in JDBC-ODBC bridge using MS Access 97 driver.

TYPE_NAME = DATETIME
DATA_TYPE = 11
PRECISION = 19
LITERAL_PREFIX = #
LITERAL_SUFFIX = #
CREATE_PARAMS = null
NULLABLE = 1
CASE_SENSITIVE = 0
SEARCHABLE = 2
UNSIGNED_ATTRIBUTE = null
MONEY = 0
AUTO_INCREMENT = null
LOCAL_TYPE_NAME = null
MINIMUM_SCALE = 0
MAXIMUM_SCALE = 0

Note that DATA_TYPE = 11. But there are no such
constant in java.sql.Types. I think that
DATA_TYPE sould be 93 (TIMESTAMP). BTW in
ODBC headers for Visual C++ 5.0 there are
three type constants that could be mapped to
TIMESTAMP in JDBC. Maybe there is more, but
I do not know.

sql.h
#if (ODBCVER >= 0x0300)
#define SQL_DATETIME 9
#endif

/* One-parameter shortcuts for date/time data types */
#if (ODBCVER >= 0x0300)
/* ... */
#define SQL_TYPE_TIMESTAMP 93
#endif

sqlext.h
#define SQL_TIMESTAMP 11