DATE field problem |
Wolfgang |
2008-11-18 11:41:31 |
Hi
we have a problem with DATE fields in your new driver
DBF_JDBC30.jar (previous version worked fine):
Using
java.sql.Date sqlDate = rs.getDate(1);
we get the sql.Date *with* hours representing the offset
of our local timezone from UTC. Reagrding to specs, this should
be all set to 0 (i.e. midnight in local timezone).
This could be handled by setting this to 0 after getting it
from the ResultSet, but saving is worse:
Using PreparedStatement stmt.setDate(1, sqlDate);
where sqlDate has hours, minutes, seconds set to 0, your
driver changes the date back one day and stores this in de database.
We are using DBF tables (XBase). Same code works fine with older versions
of your driver and e.g. MySQL driver.
Thanks for any help!
|
Re:DATE field problem |
HXTT Support |
2008-11-19 02:08:20 |
>Using PreparedStatement stmt.setDate(1, sqlDate);
You should use
PreparedStatement stmt.setTimestamp(1, sqlDate);
|
Re:DATE field problem |
HXTT Support |
2008-11-19 02:11:18 |
>java.sql.Date sqlDate = rs.getDate(1);
If it's a timestamp, you should use
java.sql.Timestamp sqlDate = rs.getTimestamp(1);
PreparedStatement stmt.setTimestamp(1, sqlDate);
or
Object sqlDate = rs.getObject(1);
PreparedStatement stmt.setObject(1, sqlDate);
|
Re:Re:Re:DATE field problem |
Wolfgang |
2008-11-19 02:24:30 |
OK, thanks for your answer.
So you mean that stmt.setDate(1, sqlDate) does not work
(we have a DATE column without time information and
in previous version 4.1 and other databases this works)?
Or is stmt.setTimestamp() just a temporary workaround?
Thanks in advance.
|
Re:Re:Re:Re:DATE field problem |
HXTT Support |
2008-11-19 02:36:03 |
>So you mean that stmt.setDate(1, sqlDate) does not work
It can work.
>(we have a DATE column without time information and
>in previous version 4.1 and other databases this works)?
If your date is a java.sql.Date type, of course you should use getDate(). If you meet issue, please email us a dbf sample, and a piece of your code. Then we will check why.
|
Re:Re:Re:Re:Re:DATE field problem |
Wolfgang |
2008-11-19 03:41:17 |
OK, mail to webmaster is on its way. Thanks.
|
Re:Re:Re:Re:Re:Re:DATE field problem |
HXTT Support |
2008-11-19 04:07:10 |
Checked. All date field in your dbf sample is java.sql.Date type, not java.sql.Timestamp type. So that you will store only date value, no matter whether you're using g(s)etDate or g(s)etTimestamp.
|
Re:Re:Re:Re:Re:Re:DATE field problem |
HXTT Support |
2008-11-19 04:09:14 |
If you wish to store time information, you need to "alter table modify AUSTRITT timestamp,..." at ALTER TABLE table_name alter_specification [,...]
|
Re:Re:Re:Re:Re:Re:Re:Re:DATE field problem |
Wolfgang |
2008-11-19 04:15:42 |
I just want a consistent behaviour using DATE, not time
information. Please have a look at my first post:
It seems that the driver changes the date during save operation
one day back, at least in out timezone GMT+0100
Thanks in advance.
|
Re:Re:Re:Re:Re:Re:Re:Re:DATE field problem |
HXTT Support |
2008-11-19 04:25:16 |
Failed to recur your issue.
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package test.jdbc.dbf;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.TimeZone;
/**
*
* @author fh
*/
public class testCalendar4 {
public static void main(String[] args) throws Exception {
String connectionUrl;
Connection con;
String sColumnName="EINTRITT";
connectionUrl = "jdbc:dbf:/d:/dbffiles/";
Class.forName("com.hxtt.sql.dbf.DBFDriver");
con = DriverManager.getConnection(connectionUrl);
TimeZone tz = TimeZone.getTimeZone("gmt");
Calendar cal = Calendar.getInstance(tz);
SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy zzz");
sdf.setTimeZone(tz);
System.out.println("** Step 1 Date Test **************");
PreparedStatement ps = con.prepareStatement("SELECT * FROM ZEPARA where recno()=1");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
java.sql.Date sqlDate = rs.getDate(sColumnName);
System.out.println(sqlDate);
System.out.println(sdf.format(sqlDate));
}
rs.close();
ps.close();
System.out.println("** Step 2 Date Test **************");
PreparedStatement prepStmt = con.prepareStatement("update ZEPARA set AUSTRITT=? where recno()=1");
java.util.Date dt=new java.util.Date(System.currentTimeMillis());
long lMillis = dt.getTime();
java.sql.Date sqlDate = new java.sql.Date(lMillis);
int iColumnIndex=1;
prepStmt.setDate(iColumnIndex, sqlDate);
prepStmt.execute();
prepStmt.close();
System.out.println(sqlDate);
System.out.println(sdf.format(sqlDate));
System.out.println("** Step 3 Date Test **************");
ps = con.prepareStatement("SELECT * FROM ZEPARA where recno()=1");
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(sdf.format(rs.getDate("AUSTRITT")));
System.out.println(sdf.format(rs.getDate("AUSTRITT", cal)));
}
rs.close();
ps.close();
con.close();
}
}
|
Re:Re:Re:Re:Re:Re:Re:Re:DATE field problem |
HXTT Support |
2008-11-20 00:00:21 |
Recurred and supported. That's resulted by which you're using System.currentTimeMillis() to construct a date.
For instance,
11/20/2008 12:54:50 GMT+01:00
is
11/19/2008 11:54:50 GMT
in fact.
The latest package will detect it, and choose the suitable default time zone(local or GMT). It will be availabe after about 3 hours.
|
Re:Re:Re:Re:Re:Re:Re:Re:DATE field problem |
Wolfgang |
2008-11-20 01:52:03 |
OK, tried it --> worked.
Many thanks for your quick reaction!
Very good support!
|