db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <qoz...@sbcglobal.net>
Subject Re: ODBC driver and JDBC Escape functions
Date Sat, 01 Oct 2005 01:34:48 GMT
Nicolas Dufour wrote:
> Hi
> I access to my derby database thru a DB2 ODBC driver and I have seen
> that the JDBC Escape functions are not working, of course, in particular
> the one called TIMESTAMPDIFF. Then How can I compute this difference
> between two dates from ODBC with my derby database ??

Hi Nicolas,

First things first: can you be more specific about what error you're seeing, 
just so we can make sure we're on the same page?

The TIMESTAMPDIFF function is a relatively new addition to Derby and thus the 
DB2 ODBC driver doesn't yet reflect the fact that Derby supports it.  Until a 
corresponding change can be made to the ODBC driver, you'll probably have to 
find another way to compute the difference between two dates.

As one alternative, if your environment supports Java you could create a Java 
stored procedure that does the date-diff operation and then call that procedure 
from ODBC.  The procedure could either use Java classes/methods to do the diff, 
or it could get the current connection and then call the JDBC escaped 
TIMESTAMPDIFF function.  For example, if you want the diff in days then you 
could do something like:

public static int tsDiffDays (Timestamp ts1, Timestamp ts2)
	throws SQLException
	Connection conn = DriverManager.getConnection(

	PreparedStatement pSt = conn.prepareStatement(
		"values { fn timestampdiff (SQL_TSI_DAY, ?, ?) }");

	pSt.setTimestamp(1, ts1);
	pSt.setTimestamp(2, ts2);

	ResultSet rs = pSt.executeQuery();
	return rs.getInt(1);


(Of course, you'd have to add better error-checking than I what I've shown here 
;)  After that, you can create a function like the following:

create function tsdiff (tsone timestamp, tstwo timestamp) returns int language 
java parameter style java external name 'MyDateFunctions.tsDiffDays';

Then, from your ODBC program, instead of trying to call the timestamp escape 
function, you'd just use "values tsdiff (current_timestamp, timestamp('...'))".

I realize it's a bit of a hassle, but that might allow you to do what you want 
even though the ODBC escape function isn't working...

Hope that helps, but let me know if you have any other questions,

View raw message