Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v5.2
  HXTT Cobol v2.1
  HXTT DBF v5.2
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Excel v4.2
  HXTT Paradox v5.2
  HXTT Text(CSV) v5.2
  HXTT XML v1.2
Offshore Outsourcing
Oracle Data Import/Export
DB2 Data Import/Export
Sybase Data Import/Export
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
   
   
   
Hongxin Technology & Trade Ltd. of Xiangtan City (abbr, HXTT)

HXTT DBF
PreparedStatement.setTimestamp whit Calendar
Fernando Hartmann
2009-05-22 13:49:13.0
We found a regression in the last versions, in the following issue, I reported a problem that was resolved by you, see the following link

http://www.hxtt.com/support_view_issue.jsp?product=dbf&id=1219426696

In that time the problem was solved, but now it doesn't work any more. The same problem started to happen again.
Please, can you fix it again ?
Re:PreparedStatement.setTimestamp whit Calendar
Fernando Hartmann
2009-05-27 04:50:02.0
Hi, there are a estimate of when this will be fixed ?
Thanks.
Re:Re:PreparedStatement.setTimestamp whit Calendar
Fernando Hartmann
2009-05-29 10:33:42.0
Some news here ?
Re:Re:Re:PreparedStatement.setTimestamp whit Calendar
HXTT Support
2009-06-03 08:11:12.0
Sorry for missed that thread.

>We found a regression in the last versions
More customers think that regression is better, so that we roll back that modification. It seems that we need a solution to let both sides feel their code can work normally:(
Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar
Fernando Hartmann
2009-06-03 19:12:44.0
I don't now exactly what are the others concerns, but in the following code You can see that is a conceptual problem with the current behavior. The code does exactly the same queries against, HXTT, PostGres, Oracle and SQL Server, and the only different "wrong" results are from HXTT, the others tree DBs returns the "right" response.
In my opinion this is to be enough to show that the current behavior need to be corrected.
Bellow I attach the code and the results.

THE CODE
=======================

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package tests;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.TimeZone;

/**
*
* @author fh
*/
public class WhereDataNovo {

public static void main(String[] args) throws Exception {

String connectionUrl;
Connection con;




// HXTT
connectionUrl = "jdbc:dbf:/C:/TEMP/";
Class.forName("com.hxtt.sql.dbf.DBFDriver");
con = DriverManager.getConnection(connectionUrl);

printResults("HXTT", con);
con.close();

// PostGreSQL
connectionUrl = "jdbc:postgresql://est/sa?user=sa&password=sa";
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(connectionUrl);
printResults("PostGreSQL", con);
con.close();

// Oracle
connectionUrl = "jdbc:oracle:thin:sa/sa@est:1521:XE";
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection(connectionUrl);
printResults("Oracle", con);
con.close();

// SQL Server
connectionUrl = "jdbc:sqlserver://est\\sql2005;databaseName=sa;user=sa;password=sa;";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl);
printResults("SQL Server", con);
con.close();

}
public static void printResults(String DriverName, Connection con) throws SQLException, ParseException {

String sqlAll = "select * from tst ";
String sql = "SELECT datez as datez FROM tst where datez = ? ";
String sqlOracle = "SELECT datez as datez FROM tst where datez = to_date(?,'YYYY-MM-DD') ";
String date = "2007-01-02";
TimeZone tz = TimeZone.getTimeZone("gmt");
Calendar cal = Calendar.getInstance(tz);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd zzz");
sdf.setTimeZone(tz);

System.out.println("** Testing " + DriverName + " **************");


/*
* 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("datez", 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("datez", 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("datez", cal)));
}
resp.close();
ps.close();
System.out.println("");

/*
* Step 4
* Execute the query no preparedstatement
* The response is right
*/
Statement stmt = con.createStatement();
if (DriverName.equalsIgnoreCase("oracle"))
sql = sqlOracle;
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("datez", cal)));
}
resp.close();
stmt.close();
con.close();

}
}


THE RESULTS
==========================

** Testing HXTT **************
** Step 1 List all records from table **************
2007-01-01 GMT+00:00
2007-01-01 GMT+00:00
2007-01-02 GMT+00:00

** Step 2 DateTime Test **************
2007-01-01 GMT+00:00
2007-01-01 GMT+00:00

** Step 3 Date Test **************
2007-01-01 GMT+00:00
2007-01-01 GMT+00:00

** Step 4 Without prepared statement **************
2007-01-02 GMT+00:00
** Testing PostGreSQL **************
** Step 1 List all records from table **************
2007-01-01 GMT+00:00
2007-01-01 GMT+00:00
2007-01-02 GMT+00:00

** Step 2 DateTime Test **************
2007-01-02 GMT+00:00

** Step 3 Date Test **************
2007-01-02 GMT+00:00

** Step 4 Without prepared statement **************
2007-01-02 GMT+00:00
** Testing Oracle **************
** Step 1 List all records from table **************
2007-01-02 GMT+00:00
2007-01-01 GMT+00:00
2007-01-01 GMT+00:00

** Step 2 DateTime Test **************
2007-01-02 GMT+00:00

** Step 3 Date Test **************
2007-01-02 GMT+00:00

** Step 4 Without prepared statement **************
2007-01-02 GMT+00:00
** Testing SQL Server **************
** Step 1 List all records from table **************
2007-01-01 GMT+00:00
2007-01-01 GMT+00:00
2007-01-02 GMT+00:00

** Step 2 DateTime Test **************
2007-01-02 GMT+00:00

** Step 3 Date Test **************
2007-01-02 GMT+00:00

** Step 4 Without prepared statement **************
2007-01-02 GMT+00:00



Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar
Fernando Hartmann
2009-06-09 04:49:36.0
Six days after the last response, and no more any answer !
And we have a support contract !
What that means ? Means that the incorrect behavior of the driver is permanent ?
This is unacceptable, the current behavior is wrong !
If there are some one that need this behavior, I sugest a creation of a parameter for the driver, my be like JDBCDateFullCompatible=yes|no , with that this people that want no correct date behavior can set it to no, and their can steal work.
What do you think, about this ?
Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar
HXTT Support
2009-06-16 08:45:38.0
DBF's database is based on local calendar, not GMT calendar. More customers think they see the normal date format with default local calendar, so that we rolled back from GMT calendar. We're still thinking how to do the final solution. Thanks for your suggestion.
Re:Re:Re:Re:Re:Re:Re:PreparedStatement.setTimestamp whit Calendar
HXTT Support
2009-06-20 02:50:28.0
Thanks for your patience waiting. The v4.2.066 do the final fix. It'll be release in about 8 hours.

Search Key   Search by Last 50 Questions




Google
 

Address: 9 Station Rd., Xiangtan City, Hunan Province, P.R. China
Postcode: 411100
Phone: (86)731-58225727
Fax: (86)731-58225727
Email: webmaster@hxtt.com
Copyright © 1999-2011 Hongxin Technology & Trade Ltd. | All Rights Reserved. | Privacy | Legal | Sitemap