Hi,
We are using the latest access-jdbc jars (i.e. 3.2.127). The issue which we are facing is related to timezone .We are reading the data from access table the code is as follows
Properties props = new Properties();
props.setProperty("timezone", "local");
connection = DriverManager.getConnection(connectionURL,props);
statement = connection.createStatement();
and expecting it to read the data as it is seen in the file i.e. in GMT format. But it doesnt happen.
|
The latest package is v4.0.005. Please download it.
If you cann't see with
props.setProperty("timezone", "local");
you can try
props.setProperty("timezone", "GMT");
|
The interesting thing I have found with the same jars which I was using (i.e. 3.2.127) , Currently we use
calendar1 = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
resultSet.getDate(i, calendar1);
reading like this is a problem. If i dont use the calendar instance in resultSet.GetDate() it reads up the date correctly.
This piece of code we were using with old access-jdbc jars. After upgrade this issue started coming in. For reproducing I am setting my machine's timezone to EST and reading it.
|
>If i dont use the calendar instance in resultSet.GetDate() it reads up the
>date correctly.
1. Without timezone connection property setting, ResultSet.getDate(i) should return Date in local timezone,and ResultSet.getDate(i,gmdCalendar) return Date in GMT timezone.
2. With timezone=GMT connection property setting, ResultSet.getDate(i) and ResultSet.getDate(i,gmdCalendar) should return Date in GMT timezone.
|
|
Yes this is the ideal behavior but its not happening in this way. Only if i ues getDate() without calendar instance it works as expected. Else in all other scenarios it doesn't
|
Passed test with the following code:
package test.jdbc.access;
import java.sql.*;
import java.util.Properties;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.TimeZone;
public class testCalendar5 {
public static void main(String[] args) throws Exception {
try{
test(null);
test("GMT");
test("local");
}
catch( SQLException sqle )
{
do
{
System.out.println(sqle.getMessage());
System.out.println("Error Code:"+sqle.getErrorCode());
System.out.println("SQL State:"+sqle.getSQLState());
sqle.printStackTrace();
}while((sqle=sqle.getNextException())!=null);
}
catch( Exception e )
{
System.out.println(e.getMessage());
e.printStackTrace();
}
}
public static void test(String timeZone) throws Exception {
TimeZone newZone;
if(timeZone==null || "local".equals(timeZone)){
newZone=TimeZone.getDefault();
}else{
newZone=TimeZone.getTimeZone(timeZone);
}
TimeZone.setDefault(newZone);
Calendar calendar = Calendar.getInstance();
Class.forName("com.hxtt.sql.access.AccessDriver");
String jdbcConnection = "jdbc:access:/d:/test/access";
Properties props = new Properties();
props.setProperty("versionNumber", "JET4");
if(timeZone!=null){
props.setProperty("timezone", timeZone);
}
Connection conn = DriverManager.getConnection(jdbcConnection, props);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
boolean createflage=stmt.executeUpdate("create database if not exists testcalendar;")>0;
conn.setCatalog("testcalendar");
if(createflage){
stmt.execute("create table if not exists test (date1 date);");
stmt.execute("insert into test values('2010-05-10');");
stmt.execute("insert into test values(now());");
}
System.out.println("timeZone:"+timeZone);
ResultSet rs = stmt.executeQuery("select * from test");
while(rs.next()) {
System.out.println(rs.getDate("date1"));
System.out.println(rs.getDate("date1",calendar));
System.out.println(rs.getTimestamp("date1"));
System.out.println(rs.getTimestamp("date1",calendar));
}
rs.close();
stmt.close();
conn.close();
}
}
BTW, MS Access is stored timestamp based on GMT, and different calendar setting will return different date value.
|
Main
webmaster@hxtt.com