Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.1
  HXTT Cobol v5.0
  HXTT DBF v7.1
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v6.1
  HXTT Json v1.0
  HXTT Paradox v7.1
  HXTT PDF v2.0
  HXTT Text(CSV) v7.1
  HXTT Word v1.1
  HXTT XML v4.0
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT DBF
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!

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Refund | Sitemap