db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nicolas Dufour <nicolas.duf...@neometsys.fr>
Subject Re: ODBC driver and JDBC Escape functions
Date Sun, 02 Oct 2005 13:20:58 GMT
Thank you very much !

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


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

View raw message