Re: ODBC driver and JDBC Escape functions

[Army <qo...@sbcglobal.net>]

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(
		"jdbc:default:connection");

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

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

	ResultSet rs = pSt.executeQuery();
	rs.next();
	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,
Army

Re: ODBC driver and JDBC Escape functions

[Nicolas Dufour <ni...@neometsys.fr>]

Thank you very much !

It helps a lot and open me a lot of possibilities with this database, 
specially by adding java procedures.

Nicolas

Army wrote:

> 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(
>         "jdbc:default:connection");
>
>     PreparedStatement pSt = conn.prepareStatement(
>         "values { fn timestampdiff (SQL_TSI_DAY, ?, ?) }");
>
>     pSt.setTimestamp(1, ts1);
>     pSt.setTimestamp(2, ts2);
>
>     ResultSet rs = pSt.executeQuery();
>     rs.next();
>     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,
> Army
>
>
>