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
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.

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