|
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)
HXTT DBF
|
PreparedStatement.setTimestamp whit Calendar |
Fernando Hartmann |
2008-08-22 10:38:16 |
Hi,
I have one issue with the PreparedStatement.setTimestamp, using one Calendar object as a parameter, don't change the date wrote to the table.
The following code try to write a date in to a table, this date in particular is the start of summer day light of my locale.
When I run the code with HXTT driver and open a table with VFP the date in the wrote to the table is 10/11/2008 09:00:00 PM but it should by 10/12/2008 00:00:00 AM, this same code works, with SQL Server, Oracle and PostGreSQL
The objective of this code is to write date in GMT timezone to the table.
Question, is the Calendar parameter supported by HXTT driver ?
Here follows the code:
SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy zzz");
TimeZone tz = TimeZone.getTimeZone("gmt");
Date data = sdf.parse("12/10/2008 gmt");
Calendar cal = Calendar.getInstance(tz);
java.sql.Timestamp ts = new java.sql.Timestamp(data.getTime());
String connectionUrl = "jdbc:DBF:/c:/test/";
Class.forName("com.hxtt.sql.dbf.DBFDriver");
Connection con = DriverManager.getConnection(connectionUrl);
PreparedStatement ps = con.prepareStatement(
" insert into tst_date " +
" (date) " +
" values (?)");
ps.setTimestamp(1, ts, cal);
ps.executeUpdate();
con.commit();
ps.close();
con.close();
Thanks for your support !
|
Re:PreparedStatement.setTimestamp whit Calendar |
Fernando Hartmann |
2008-09-12 06:00:01 |
Hello again,
I need some help here, I'm got no answers until now, this problem is important to my application, hour HXTT support contract has expired but,if this problem can be solved we will enjoy in renew the contract.
If the description of the problem is poor, please, let me now, I will try to make it better.
|
Re:Re:PreparedStatement.setTimestamp whit Calendar |
HXTT Support |
2008-09-12 06:07:30 |
Sorry for missing that thread. We will check soon.
|
Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
HXTT Support |
2008-09-22 01:03:43 |
Supported. BTW, the latest package has supported JDBC4.0 .
|
Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
Fernando Hartmann |
2008-09-22 11:18:36 |
Thanks for your answer, is the JDBC 4.0 available for download to test ?
I looked and the only package in the trial version is the JDBC 3.0 package.
|
Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
HXTT Support |
2008-09-22 19:35:49 |
You can login and download. BTW, the latest JDBC3.0 package support that feature too.
|
Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
Fernando Hartmann |
2008-09-24 14:19:12 |
I tried the new trial version, but it's seems to have a problem. I wrote a sample program that test some drivers.
In this code I test the HXTT, PostGreSQL, Oracle, SQL Server Jdbc drivers.
The program uses a table with one field named data type datetime, it has three steps, clear the table , insert a row and read the row back and print.
Below the code I attached the program result, in there you can see that HXTT driver prints a wrong response, the other drivers implementations seems to me that are right.
THE SAMPLE TEST PROGRAM
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package javaapplication1;
import java.security.Timestamp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.Locale;
import java.util.TimeZone;
import javax.naming.spi.DirStateFactory.Result;
import org.apache.commons.lang.time.DateUtils;
/**
*
* @author fh
*/
public class ListaDatas {
/**
* Insert the date value in the table
* @param con
* @param ts
* @param cal
* @throws java.sql.SQLException
* @throws java.sql.SQLException
*/
public static void insertDate(Connection con, java.sql.Timestamp ts, Calendar cal) throws SQLException, SQLException {
PreparedStatement ps = con.prepareStatement(" insert into fh_tst_data (data) values (?)");
ps.setTimestamp(1, ts, cal);
ps.executeUpdate();
con.commit();
ps.close();
}
/**
* List dthe results in the Output
* @param sdfs
* @param cal
* @param DriverName
* @param con
* @throws java.sql.SQLException
*/
public static void printResults(SimpleDateFormat sdfs, Calendar cal, String DriverName, Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement(
" select * from fh_tst_data ");
ResultSet resp = ps.executeQuery();
System.out.println("** " + DriverName);
while (resp.next()) {
System.out.println(sdfs.format(resp.getTimestamp("data", cal)));
}
}
/**
* Main Procedure
* @param args the command line arguments
*/
public static void main(String[] args) throws Exception {
SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy zzz");
TimeZone tz = TimeZone.getTimeZone("gmt");
sdf.setTimeZone(tz);
/*
Set the date I use this specific date because this is the first date in the summer time
in my contry ( Locale = "pt-BR")
*/
Date data = sdf.parse("12/10/2008 gmt");
Calendar cal = Calendar.getInstance(tz);
java.sql.Timestamp ts = new java.sql.Timestamp(data.getTime());
SimpleDateFormat sdfo = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss zzz");
sdfo.setTimeZone(tz);
String connectionUrl;
Connection con;
// Date Time Before DB
System.out.println("** Before DB");
System.out.println(sdfo.format(ts.getTime()));
// HXTT
connectionUrl = "jdbc:DBF:/c:/xxx/";
Class.forName("com.hxtt.sql.dbf.DBFDriver");
con = DriverManager.getConnection(connectionUrl);
truncateTable(con);
insertDate(con, ts, cal);
printResults(sdfo, cal, "HXTT", con);
con.close();
// PostGreSQL
connectionUrl = "jdbc:postgresql://everest/xxx?user=xxx&password=xxx";
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(connectionUrl);
truncateTable(con);
insertDate(con, ts, cal);
printResults(sdfo, cal, "PostGreSQL", con);
con.close();
// Oracle
connectionUrl = "jdbc:oracle:thin:xxx/xxx@10.1.1.189:1521:XE";
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(connectionUrl);
truncateTable(con);
insertDate(con, ts, cal);
printResults(sdfo, cal, "Oracle", con);
con.close();
// SQL Server
connectionUrl = "jdbc:sqlserver://everest\\sql2005;databaseName=xxxweb;user=xxxsql;password=xxxsql;";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl);
truncateTable(con);
insertDate(con, ts, cal);
printResults(sdfo, cal, "SQL Server", con);
con.close();
}
/**
* Clear the table content
* @param con
* @throws java.sql.SQLException
*/
public static void truncateTable(Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement(" truncate table fh_tst_data");
ps.executeUpdate();
con.commit();
ps.close();
return;
}
}
THE PROGRAM RESULT
** Before DB
12/10/2008 00:00:00 GMT+00:00
** HXTT
12/10/2008 06:00:00 GMT+00:00
** PostGreSQL
12/10/2008 00:00:00 GMT+00:00
** Oracle
12/10/2008 00:00:00 GMT+00:00
** SQL Server
12/10/2008 00:00:00 GMT+00:00
|
Re:Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
Fernando Hartmann |
2008-09-30 05:54:49 |
We renewed the support contract and tried the JDBC 4.0 version, and the problem happens in this version too.
|
Re:Re:Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
HXTT Support |
2008-10-01 02:31:22 |
System.out.println(resp.getTimestamp("data")); will show the correct time.
Changed according to your sample, now
System.out.println(sdfs.format(resp.getTimestamp("data", cal))); will show correct, but
System.out.println(resp.getTimestamp("data")) will fail now.
The latest package will be here in 2 hours.
|
Re:Re:Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
Fernando Hartmann |
2008-10-07 12:10:02 |
It's seems to be ok now, thanks for your help.
|
Re:Re:Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
Fernando Hartmann |
2008-10-17 11:15:03 |
In sequence of the use/test of your patch, I found the following problem.
The problem happens when a I use the PreparedStatement setTimestamp or setDate, setting the where clause of the query, they appears to not use correctly the calendar parameter, because the wrong record that are retrieved in the query, but the same query running without preparedstatement works ok .
The program bellow generate the following output:
** Step 1 List all records from table **************
01/01/2007 GMT+00:00
01/01/2007 GMT+00:00
01/02/2007 GMT+00:00
** Step 2 DateTime Test **************
01/01/2007 GMT+00:00
01/01/2007 GMT+00:00
** Step 3 Date Test **************
01/01/2007 GMT+00:00
01/01/2007 GMT+00:00
** Step 4 Without prepared statement **************
01/02/2007 GMT+00:00
Program description:
It access a table (tst) with 3 records following this steps:
* Step 1
* List all table records
* Step 2
* Execute the query with setTimeStamp
* The response is wrong because the record returned are from a previous day 01/01/2007 and not the required 01/02/2007
* Step 3
* Execute the query with setDate
* The response is wrong because the record returned are from a previous day 01/01/2007 and not the required 01/02/2007
* the of the previous step
* Step 4
* Execute the query no preparedstatement
* The response is right
Thanks for your help.
The program
public static void main(String[] args) throws Exception {
String connectionUrl;
Connection con;
String sqlAll = "select * from tst ";
String sql = "SELECT date as date FROM tst where date = ? ";
String date = "01/02/2007";
connectionUrl = "jdbc:dbf:/C:/TEMP/";
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);
/*
* Step 1
* List all table records
*/
PreparedStatement ps = con.prepareStatement(sqlAll);
ResultSet resp = ps.executeQuery();
System.out.println("** Step 1 List all records from table **************");
while (resp.next()) {
System.out.println(sdf.format(resp.getTimestamp("date", cal)));
}
resp.close();
ps.close();
System.out.println("");
/*
* Step 2
* Execute the query with setTimeStamp
* The response is wrong because the record returned are from a previous day 01/01/2007 and not the required 01/02/2007
*/
ps = con.prepareStatement(sql);
Timestamp ts = new Timestamp(sdf.parse(date + " gmt").getTime());
ps.setTimestamp(1, ts, cal);
resp = ps.executeQuery();
System.out.println("** Step 2 DateTime Test **************");
while (resp.next()) {
System.out.println(sdf.format(resp.getTimestamp("date", cal)) );
}
resp.close();
ps.close();
System.out.println("");
/*
* Step 3
* Execute the query with setDate
* The response is wrong because the record returned are from a previous day 01/01/2007 and not the required 01/02/2007
* the of the previous step
*/
ps = con.prepareStatement(sql);
java.sql.Date dt = new java.sql.Date(sdf.parse(date + " gmt").getTime());
ps.setDate(1, dt, cal);
resp = ps.executeQuery();
System.out.println("** Step 3 Date Test **************");
while (resp.next()) {
System.out.println(sdf.format(resp.getDate("date", cal)));
}
resp.close();
ps.close();
System.out.println("");
/*
* Step 4
* Execute the query no preparedstatement
* The response is right
*/
Statement stmt = con.createStatement();
String sql2 = sql.replaceAll("\\?", "\'" + date + "\'");
resp = stmt.executeQuery(sql2);
System.out.println("** Step 4 Without prepared statement **************");
while (resp.next()) {
System.out.println(sdf.format(resp.getTimestamp("date", cal)));
}
resp.close();
stmt.close();
con.close();
}
|
Re:Re:Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
HXTT Support |
2008-10-18 02:32:22 |
Failed to recur that issue. Maybe it's resulted by a timestamp index issue which HXTT DBF won't calculate timzone offset for java.sql.date type. Please download the latest package. If your issue exists still, please email us your tst.dbf sample with its relative index files.
|
Re:Re:Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
Fernando Hartmann |
2008-10-20 05:25:30 |
I downloaded the latest version (2008-10-18 02:36) and the problem happens too.
There is no index in this table, I created the table with no index just used :
create table c:\temp\tst (date date)
Now I tried with one index, executed the following command
index on date tag date additive
And the problem persist
I can't attach the tst.dbf in ths thread, there is no option to attach a file, how can I sent it to you ?
|
Re:Re:Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
Fernando Hartmann |
2008-10-20 05:33:39 |
I found the following address webmaster@hxtt.com in other thread and sent the sample dbf file attached to it.
|
Re:Re:Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
HXTT Support |
2008-10-20 23:19:09 |
Checked and supported. Please download the latest package. BTW, the data type of your dbf sample is java.sql.Date, not java.sql.Timestamp. You can use ResultSet.getDate() or ResultSet.getObject() to get a java.sql.Date ojbect.
|
Re:Re:Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar |
Fernando Hartmann |
2008-10-22 05:09:56 |
It worked, thanks a lot.
|
|
|