Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

timestamp vs. Timestamp

2 posts in JDBC Connect (product renamed to JConnect) Last posting was on 1997-07-23 16:36:53.0Z
David Thiede Posted on 1997-07-22 23:30:08.0Z
Message-ID: <33D5427F.41C6@pnl.gov>
Date: Tue, 22 Jul 1997 16:30:08 -0700
From: David Thiede <David.Thiede@pnl.gov>
Organization: Battelle, PNNL
X-Mailer: Mozilla 3.01Gold (X11; I; OSF1 V4.0 alpha)
MIME-Version: 1.0
CC: david.thiede@pnl.gov
Subject: timestamp vs. Timestamp
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 31
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:481
Article PK: 252235

I have been trying to retrieve a SQL Server timestamp value using a
stored procedure call and keep getting datatype conversion errors. I
remember some discussion quite a while back about date and timestamp
types but can't find any message archive;)

The pertinent part of the sp argument is..

@times timestamp=NULL output

I register it as an output parameter as below but it seems to allow
any type without complaint.

stmt.registerOutParameter(8,Types.TIMESTAMP);

This statement gets an exception...

System.out.println( stmt.getTimestamp(8));

*** java.sql.SQLException: Domain error during implicit conversion of
VARBINARY value '' to a DATETIME field. ***

I think that the exception is talking about a null value as the returned
timestamp but I could be wrong. In either case I don't know what is
happening. The rest of the arguments are returned correctly (they are
all VARCHAR) and I KNOW that I am getting a return value in the @times
argument. Debuging should be on but I don't get any extra output
compared with building with the normal classes.

I am using the July 16 jConnect, a SQL server 11.0.2 and Digital Unix
port of JDK 1.1.1
--
David.Thiede@pnl.gov


Lance Andersen Posted on 1997-07-23 16:36:53.0Z
Message-ID: <33D63325.5E6D@sybase.com>
Date: Wed, 23 Jul 1997 12:36:53 -0400
From: Lance Andersen <lancea@sybase.com>
X-Mailer: Mozilla 3.01Gold (X11; I; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: David Thiede <David.Thiede@pnl.gov>
Subject: Re: timestamp vs. Timestamp
References: <33D5427F.41C6@pnl.gov>
Content-Type: multipart/mixed; boundary="------------3E12611E4BED"
Newsgroups: sybase.public.jdbcconnect
Lines: 568
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:478
Article PK: 252230

David,

the timestamp datatype within the SQL Server is a varbinary(8)
which has special meaning to the SQL Server. It does not
bear any relationship to the java datatype Timestamp (which some
RDBMS vendors have).

Within the SQLServer, use a datetime datatype as the equivalent
of your java Timestamp.

I have attached two sample programs:

1. Retrieves a Sybase Timestamp as a String and Varbinary.

2. Uses a java Timestamp with a SQL Server datetime datatime.

enjoy!

-Lance

David Thiede wrote:
>
> I have been trying to retrieve a SQL Server timestamp value using a
> stored procedure call and keep getting datatype conversion errors. I
> remember some discussion quite a while back about date and timestamp
> types but can't find any message archive;)
>
> The pertinent part of the sp argument is..
>
> @times timestamp=NULL output
>
> I register it as an output parameter as below but it seems to allow
> any type without complaint.
>
> stmt.registerOutParameter(8,Types.TIMESTAMP);
>
> This statement gets an exception...
>
> System.out.println( stmt.getTimestamp(8));
>
> *** java.sql.SQLException: Domain error during implicit conversion of
> VARBINARY value '' to a DATETIME field. ***
>
> I think that the exception is talking about a null value as the returned
> timestamp but I could be wrong. In either case I don't know what is
> happening. The rest of the arguments are returned correctly (they are
> all VARCHAR) and I KNOW that I am getting a return value in the @times
> argument. Debuging should be on but I don't get any extra output
> compared with building with the normal classes.
>
> I am using the July 16 jConnect, a SQL server 11.0.2 and Digital Unix
> port of JDK 1.1.1
> --
> David.Thiede@pnl.gov

--
===============================================================================
Lance J. Andersen Email: lancea@sybase.com
Sybase Technical Support Phone:(617) 564-6336
77 South Bedford Street Fax: (617) 564-6148
Burlington, MA 01803

The Dark Knight Returns!!! Let's Go Penguins!!!
===============================================================================

/* SybTimestamp.java Sybase Product Support group, 06/01/97
* Copyright (c) 1997, Sybase., Emeryville, CA 94608
* All Rights Reserved
*
* TITLE: SybTimestamp.java
*
* START-HISTORY:
*
* 21 Jul 97 edit 0 - Lance Andersen.
* Initial coding.
*
* END-HISTORY
*
* START-DESCRIPTION:
*
* SybTimestamp class demonstrates how to process a Sybase timestamp
* datatype via an Output Parameter of a CallableStatement
*
* A Sybase timestamp datatype is a structure which is stored as a
* VARBINARY(8). The Structure looks like:
*
* fieldname size Description
* ts_high 2 High portion of timestamp
* pad 2 Bytes for alignment
* ts_low 4 Low portion of timestamp
*
* Note: Sybase timestamp is a unique datatype to Sybase
*
* SybTimestamp may be invoked with the optional parameters:
* -U username
* -P password
* -D debuglibraries
* -S server
*
*
* END-DESCRIPTION
*/

import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;
import com.sybase.utils.Debug;

class SybTimestamp {

static String _user = "sa";
static String _password = "";
static String _url = "jdbc:sybase:Tds:alder:6689/tempdb";
static public final int SYBTIMESTAMPSIZE = 8;


public static void main (String args[]) {

// Variables for simple test case
String createQuery1 =
"create table #foo1(f1 int, f2 char(5), f3 timestamp )";
String insertQuery1 =
"insert #foo1(f1,f2) values(1, 'Hello')";

// Sample Stored Procedure

String procname = "foobar";
String dropProc = "drop proc " + procname;
String createProc =
"create proc " + procname +
"(@p1 int, @p2 timestamp out)" +
" as " +
"select 'p1=' + convert(varchar(10),@p1) " +
"select @p2 = f3 from #foo1 where f1=1" +
"select * from #foo1 " +
"return 21";
String sproc = "{? = call foobar(?,?)}";



// Parse the command line

if (!processCommandline(args))
{
System.out.println(
"Syntax:\n" +
"\tSybTimestamp [-U <username>] [-P <password>] " +
" [-S <servername>]\n\t\t [-D <debug-class-list>]\n");
System.exit(1);
}


try {

// Load the Sybase Driver

Class.forName("com.sybase.jdbc.SybDriver");


// Attempt to connect to a driver.

Connection con = DriverManager.getConnection(_url,
_user, _password);

// If we were unable to connect, an exception
// would have been thrown. So, if we get here,
// we are successfully connected to the URL

// Check for, and display and warnings generated
// by the connect.

checkForWarning (con.getWarnings ());


// Create our table
execDDL(con, createQuery1);

// Insert our row
execDDL(con, insertQuery1);

// Now create the Proc
execDDL(con, createProc);


// Now execute our Sproc

CallableStatement cstmt = con.prepareCall(sproc);
System.out.println("Executing: " + sproc);

// Declare the IN Params. Note, you must skip the Return Status
cstmt.setInt(2, 1961);

// Now declare our OUT Params
cstmt.registerOutParameter(1, Types.INTEGER);
cstmt.registerOutParameter(3, Types.VARBINARY);

boolean results = cstmt.execute ();
processASproc(results, cstmt);
String s = cstmt.getString(1);
String s2 = cstmt.getString(3);

// Now grab the same output parameter as VARBINARY
byte[] ts = new byte[SYBTIMESTAMPSIZE];
ts = cstmt.getBytes(3);

// Display the Output Parameters
System.out.println("OUT Param1=" + s);
System.out.println("OUT Param2 as String=" + s2);
System.out.println("OUT Param2 as byte[]=" + toHexString(ts ));

cstmt.close();

// Drop our sproc
execDDL(con, dropProc);

// Close the connection

con.close();

}
catch (SQLException ex) {

// A SQLException was generated. Catch it and
// display the error information. Note that there
// could be multiple error objects chained
// together

System.out.println ("\n*** SQLException caught ***\n");

while (ex != null) {
System.out.println ("SQLState: " + ex.getSQLState ());
System.out.println ("Message: " + ex.getMessage ());
System.out.println ("Vendor: " + ex.getErrorCode ());
ex.printStackTrace ();
ex = ex.getNextException ();
System.out.println ("");
}
}
catch (java.lang.Exception ex) {

// Got some other type of exception. Dump it.

ex.printStackTrace ();
}
}

/*
* toHexString
* Convert a byte array to a hex string
*/
private static String toHexString(byte[] bytes )
{
final int SIZE = 2;
final String ZEROS = "00000000";
StringBuffer result = new StringBuffer("0x");

for(int i= 0; i < bytes.length; i++)
{
Byte aByte = new Byte(bytes[i]);
Integer anInt = new Integer(aByte.intValue());
String hexVal = Integer.toHexString(anInt.intValue());

if(hexVal.length() > SIZE)
hexVal = hexVal.substring(hexVal.length() - SIZE);

result.append( (SIZE > hexVal.length() ?
ZEROS.substring(0,SIZE - hexVal.length()) : "") + hexVal);
}
return(result.toString());

}
/*
* checkForWarning
* Checks for and displays warnings. Returns true if a warning
* existed
*/

private static boolean checkForWarning (SQLWarning warn) throws SQLException
{
boolean rc = false;

// If a SQLWarning object was given, display the
// warning messages. Note that there could be
// multiple warnings chained together

if (warn != null) {
System.out.println ("\n *** Warning ***\n");
rc = true;
while (warn != null) {
System.out.println ("SQLState: " + warn.getSQLState ());
System.out.println ("Message: " + warn.getMessage ());
System.out.println ("Vendor: " + warn.getErrorCode ());
System.out.println ("");
warn = warn.getNextWarning ();
}
}
return rc;
}
/*
* execDDL
* Execute a DDL or a DML statement that does not return a ResultSet
*/

private static void execDDL( Connection con, String cmd)
throws SQLException
{

Statement stmt = con.createStatement();
System.out.println("Executing: " + cmd);
stmt.executeUpdate(cmd);
stmt.close();
}

/*
* processASproc
* Execute CallableStatement and then call dispResultSet to
* display the rows and columns
* This method is able to process multiple ResultSets
*/


private static void processASproc( boolean results, CallableStatement cstmt)
throws SQLException
{
int rsnum = 0; // Number of Result Sets processed
int rowsAffected = 0;

do
{
if(results)
{
ResultSet rs = cstmt.getResultSet();
System.out.println("\n\nDisplaying ResultSet: " + rsnum);
dispResultSet(rs);
rsnum++;
rs.close();
}
else
{
rowsAffected = cstmt.getUpdateCount();
if (rowsAffected >= 0)
System.out.println(rowsAffected + " rows Affected.");
}
try
{
results = cstmt.getMoreResults();
}
catch(SQLException ex)
{
processSQLException( ex);
}
}
while (results || rowsAffected != -1);

}


/*
* processCommandline
* Parse the Command Line and set the appropriate options
*/

static private boolean processCommandline(String args[])
{
//* DONE
String arg;
int errorCount = 0;
for (int i = 0; i < args.length; i++)
{
arg = args[i];
if (arg.regionMatches(0, "-", 0, 1))
{
try
{
switch(arg.charAt(1))
{
case 'D':
i++;
try
{
Debug.debug(true, args[i]);
}
catch (IOException ioe)
{
System.out.println("Error turning on debugging " +
ioe);
}
break;
case 'U':
i++;
_user = args[i];
break;
case 'P':
i++;
_password = args[i];
break;
case 'S':
i++;
_url = args[i];
break;
default:
System.out.println("Invalid command line option: " + arg);
errorCount++;
break;
}
}
catch (ArrayIndexOutOfBoundsException aioobe)
{
System.out.println("missing option argument");
errorCount++;
}
}
else
{
// The syntax has no non "-" arguments
errorCount++;
}
}

return(errorCount == 0);
}

private static void processSQLException(SQLException ex)
{
// A SQLException was generated. Catch it and
// display the error information. Note that there
// could be multiple error objects chained
// together


while (ex != null) {
if(ex.getSQLState()== null && ex.getErrorCode() >= 17000)
{
// The SQLException is due to a raiserror command.

System.out.println("raiserror encountered");
System.out.println ("Error: " + ex.getErrorCode ());
System.out.println ("Message: " + ex.getMessage ());

}
else
{
System.out.println ("\n*** danger danger ***\n");
System.out.println ("SQLState: " + ex.getSQLState ());
System.out.println ("Message: " + ex.getMessage ());
System.out.println ("Vendor: " + ex.getErrorCode ());
}
ex = ex.getNextException ();
System.out.println ("");
}

}

/*
* dispResultSet
* Process a ResultSet displaying all of the rows and columns. Also
* use ResultSetMetaData to obtain the column headers
* This method also has an SQLException handler which is used to check
* for an exception that was the result of the raiserror command. If
* it is caught, a different message is displayed and processing of
* the next ResultSet is started.
*/

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

// 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("\t\t");
System.out.print(rsmd.getColumnLabel(i));
}
System.out.println("");

// Display data, fetching until end of the result set

while (rs.next ()) {

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

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

// Fetch the next result set row

}
}
catch(SQLException ex)
{
processSQLException(ex);

}
}
}

/* GetObject.java Sybase Product Support group, 06/01/97
* Copyright (c) 1997, Sybase., Emeryville, CA 94608
* All Rights Reserved
*
* TITLE: GetObject.java
*
* START-HISTORY:
*
* 01 Jun 97 edit 0 - Lance Andersen.
* Initial coding.
*
* END-HISTORY
*
* START-DESCRIPTION:
*
* GetObject class demonstrates how to use the GetObject method
* In this example we will check to see if we had a TimeStamp DataType returned
*
* GetObject may be invoked with the optional parameters:
* -U username
* -P password
* -D debuglibraries
* -S server
*
* END-DESCRIPTION
*/


import java.io.*;
import java.sql.*;
import java.util.*;
import com.sybase.utils.Debug;

class GetObject {

static public final int MAXROWS = 5;
static String _user = "sa";
static String _password = "";
static String _url = "jdbc:sybase:Tds:alder:6689/pubs2";


public static void main (String args[]) {


String query = "select pub_id, pubdate, pubdate from titles";

// Parse the command line

if (!processCommandline(args))
{
System.out.println(
"Syntax:\n" +
"\tGetObject [-U <username>] [-P <password>] " +
" [-S <servername>]\n\t\t [-D <debug-class-list>]");
System.exit(1);
}


try {

// Load the Sybase Driver

Class.forName("com.sybase.jdbc.SybDriver");


// Attempt to connect to a driver.

Connection con = DriverManager.getConnection(_url, _user, _password);

// If we were unable to connect, an exception
// would have been thrown. So, if we get here,
// we are successfully connected to the URL

// Check for, and display and warnings generated
// by the connect.

checkForWarning (con.getWarnings ());


// Display Rows

displayRows(con, query);


// Close the connection

con.close();

}
catch (SQLException ex) {

// A SQLException was generated. Catch it and
// display the error information. Note that there
// could be multiple error objects chained
// together

System.out.println ("\n*** SQLException caught ***\n");

while (ex != null) {
System.out.println ("SQLState: " + ex.getSQLState ());
System.out.println ("Message: " + ex.getMessage ());
System.out.println ("Vendor: " + ex.getErrorCode ());
ex = ex.getNextException ();
System.out.println ("");
}
}
catch (java.lang.Exception ex) {

// Got some other type of exception. Dump it.

ex.printStackTrace ();
}
}

/*
* checkForWarning
* Checks for and displays warnings. Returns true if a warning
* existed
*/

private static boolean checkForWarning (SQLWarning warn) throws SQLException
{
boolean rc = false;

// If a SQLWarning object was given, display the
// warning messages. Note that there could be
// multiple warnings chained together

if (warn != null) {
System.out.println ("\n *** Warning ***\n");
rc = true;
while (warn != null) {
System.out.println ("SQLState: " + warn.getSQLState ());
System.out.println ("Message: " + warn.getMessage ());
System.out.println ("Vendor: " + warn.getErrorCode ());
System.out.println ("");
warn = warn.getNextWarning ();
}
}
return rc;
}

/*
* displayRows
* Execute the desired DML statement and then call dispResultSet to
* display thre rows and columns
*/

private static void displayRows( Connection con, String query)
throws SQLException
{

Statement stmt = con.createStatement();
System.out.println("Executing: " + query);

stmt.setMaxRows(MAXROWS);
ResultSet rs = stmt.executeQuery(query);
dispResultSet(rs);
rs.close();
stmt.close();
}

/*
* processCommandline
* Parse the Command Line and set the appropriate options
*/

static private boolean processCommandline(String args[])
{
//* DONE
String arg;
int errorCount = 0;
for (int i = 0; i < args.length; i++)
{
arg = args[i];
if (arg.regionMatches(0, "-", 0, 1))
{
try
{
switch(arg.charAt(1))
{
case 'D':
i++;
try
{
Debug.debug(true, args[i]);
}
catch (IOException ioe)
{
System.out.println(
"Error turning on debugging " + ioe);
}
break;
case 'U':
i++;
_user = args[i];
break;
case 'P':
i++;
_password = args[i];
break;
case 'S':
i++;
_url = args[i];
break;
default:
System.out.println("Invalid command line option: " + arg);
errorCount++;
break;
}
}
catch (ArrayIndexOutOfBoundsException aioobe)
{
System.out.println("missing option argument");
errorCount++;
}
}
else
{
// The syntax has no non "-" arguments
errorCount++;
}
}

return(errorCount == 0);
}
/*
* dispResultSet
* Process a ResultSet displaying all of the rows and columns. Also
* use ResultSetMetaData to obtain the column headers
*
* This version of dispResultSet uses getObject() and getTimestamp()
* against 2 datetime columns and then checks to see if they match
* a Timestamp datatype
*/

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("\t\t");
System.out.print(rsmd.getColumnLabel(i));
}
System.out.println("");

// Display data, fetching until end of the result set

while (rs.next ()) {

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


System.out.print(rs.getString(1));
Object a = rs.getObject(2);
System.out.print("\t" + a);
if(a instanceof Timestamp)
System.out.print(" is a timestamp");
Timestamp ts = rs.getTimestamp(3);
System.out.print("\t" + ts);
if(ts instanceof Timestamp)
System.out.print(" is a timestamp");
System.out.println("");

// Fetch the next result set row

}
}
}