Example of using JDBC Timezone

Posted by Steve Racanovic | Posted in | Posted on 11:46 AM

0

Recently I was working on a timezone issue there the values returned on the daylight saving change over where incorrect. Here is the example and steps to reproduce this problem.

There sql script looks like:

connect / as sysdba

drop user timezoneuser cascade;

grant connect,resource to timezoneuser identified by timezoneuser;

connect timezoneuser/timezoneuser

create table jdbc_test (
  sequence_id number(2),
  cbt timestamp,
  cbt_utc timestamp);

alter session set NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
alter session set NLS_TIMESTAMP_TZ_FORMAT ='MM/DD/YYYY HH24:MI:SS';
alter session set NLS_TIMESTAMP_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

select * from nls_session_parameters;

insert into jdbc_test values(1,'04/05/2009 00:00:00','04/04/2009 13:00:00');
insert into jdbc_test values(2,'04/05/2009 01:00:00','04/04/2009 14:00:00');
insert into jdbc_test values(3,'04/05/2009 02:00:00','04/04/2009 15:00:00');
insert into jdbc_test values(4,'04/05/2009 02:00:00','04/04/2009 16:00:00');
insert into jdbc_test values(5,'04/05/2009 03:00:00','04/04/2009 17:00:00');
insert into jdbc_test values(6,'04/05/2009 04:00:00','04/04/2009 18:00:00');
insert into jdbc_test values(7,'04/05/2009 05:00:00','04/04/2009 19:00:00');
insert into jdbc_test values(8,'04/05/2009 06:00:00','04/04/2009 20:00:00');
insert into jdbc_test values(9,'04/05/2009 07:00:00','04/04/2009 21:00:00');
insert into jdbc_test values(10,'04/05/2009 08:00:00','04/04/2009 22:00:00');
insert into jdbc_test values(11,'04/05/2009 09:00:00','04/04/2009 23:00:00');
insert into jdbc_test values(12,'04/05/2009 10:00:00','04/05/2009 00:00:00');
insert into jdbc_test values(13,'04/05/2009 11:00:00','04/05/2009 01:00:00');
insert into jdbc_test values(14,'04/05/2009 12:00:00','04/05/2009 02:00:00');
insert into jdbc_test values(15,'04/05/2009 13:00:00','04/05/2009 03:00:00');
insert into jdbc_test values(16,'04/05/2009 14:00:00','04/05/2009 04:00:00');
insert into jdbc_test values(17,'04/05/2009 15:00:00','04/05/2009 05:00:00');
insert into jdbc_test values(18,'04/05/2009 16:00:00','04/05/2009 06:00:00');
insert into jdbc_test values(19,'04/05/2009 17:00:00','04/05/2009 07:00:00');

column seq format 999
column cbt format a20
column cbt_utc format a20
column difference format a30

select sequence_id as seq,cbt ,cbt_utc , cbt - cbt_utc as difference from jdbc_test;
and the java looks like:
package example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import java.util.Date;
import java.util.Properties;

public class JDBCTimeStamp {

  private void queryDatabase() {
      Connection conn = null;

      String DRIVER = "oracle.jdbc.driver.OracleDriver";
      String connectionName = "jdbc:comp:env:sys";
      String URL_NAME = "jdbc:oracle:thin:@celcsol1.us.oracle.com:1521:tarsr3";
      String userName = new String("timezoneuser");
      String password = new String("timezoneuser");
      Properties prop = new Properties();
      prop.put("user", userName);
      prop.put("password", password);
      prop.put("connectionName", connectionName);
      try {
          try {
              Class.forName(DRIVER);
          } catch (Exception e) {
              System.out.println("Exception in Class.forName : " + e);
          }
          try {
              conn = DriverManager.getConnection(URL_NAME, prop);
          } catch (Exception e) {
              System.out.println("Exception in creating the connection : " + e);
          }

          String sqlString = " SELECT SEQUENCE_ID,CBT,CBT_UTC" + " FROM JDBC_TEST ";

          PreparedStatement ps = conn.prepareStatement(sqlString.toString());
          ResultSet rs = null;
          try {
              ps = conn.prepareStatement(sqlString);
              rs = ps.executeQuery();
              String sequenceId = null;
              Timestamp cbtTimeStamp = null;
              Timestamp cbtUtcTimeStamp = null;
              long longValueOfUtc;
              long longValueOfCbt;
              Date cbtDateValueFromLong;
              Date utcDateValueFromLong;

              System.out.println("ID \tCBT(local time) \t\t\tCBT_UTC(GMT) \t\t\tTime Difference");
              while (rs.next()) {
                  sequenceId = rs.getString(1);
                  cbtTimeStamp = rs.getTimestamp(2);
                  cbtUtcTimeStamp = rs.getTimestamp(3);
                  longValueOfUtc = cbtUtcTimeStamp.getTime();
                  longValueOfCbt = cbtTimeStamp.getTime();
                  cbtDateValueFromLong = new Date(longValueOfCbt);
                  utcDateValueFromLong = new Date(longValueOfUtc);
                  System.out.println(sequenceId + "\t" + cbtDateValueFromLong + "\t\t" + utcDateValueFromLong + "\t\t" + ((longValueOfCbt - longValueOfUtc) / 3600000));
              }
          } finally {
              if (ps != null)
                  ps.close();
              if (rs != null)
                  rs.close();
          }
      } catch (Exception e) {
          System.out.println("Exception :::" + e);
          e.printStackTrace();
      }
  }

  public static void main(String[] args) {
      JDBCTimeStamp main = new JDBCTimeStamp();
      main.queryDatabase();
  }
}
Upon running the java code using JDK 1.6 and 11g JDBC drivers (ojdbc6.jar), the following results are show:
ID   CBT(local time)                     CBT_UTC(GMT)                        Time Difference
1    Sun Apr 05 00:00:00 EST 2009        Sat Apr 04 13:00:00 EST 2009        11
2    Sun Apr 05 01:00:00 EST 2009        Sat Apr 04 14:00:00 EST 2009        11
3    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 15:00:00 EST 2009        12
4    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 16:00:00 EST 2009        11
5    Sun Apr 05 03:00:00 EST 2009        Sat Apr 04 17:00:00 EST 2009        11
6    Sun Apr 05 04:00:00 EST 2009        Sat Apr 04 18:00:00 EST 2009        11
7    Sun Apr 05 05:00:00 EST 2009        Sat Apr 04 19:00:00 EST 2009        11
8    Sun Apr 05 06:00:00 EST 2009        Sat Apr 04 20:00:00 EST 2009        11
9    Sun Apr 05 07:00:00 EST 2009        Sat Apr 04 21:00:00 EST 2009        11
10    Sun Apr 05 08:00:00 EST 2009        Sat Apr 04 22:00:00 EST 2009        11
11    Sun Apr 05 09:00:00 EST 2009        Sat Apr 04 23:00:00 EST 2009        11
12    Sun Apr 05 10:00:00 EST 2009        Sun Apr 05 00:00:00 EST 2009        11
13    Sun Apr 05 11:00:00 EST 2009        Sun Apr 05 01:00:00 EST 2009        11
14    Sun Apr 05 12:00:00 EST 2009        Sun Apr 05 02:00:00 EST 2009        10
15    Sun Apr 05 13:00:00 EST 2009        Sun Apr 05 03:00:00 EST 2009        10
16    Sun Apr 05 14:00:00 EST 2009        Sun Apr 05 04:00:00 EST 2009        10
17    Sun Apr 05 15:00:00 EST 2009        Sun Apr 05 05:00:00 EST 2009        10
18    Sun Apr 05 16:00:00 EST 2009        Sun Apr 05 06:00:00 EST 2009        10
19    Sun Apr 05 17:00:00 EST 2009        Sun Apr 05 07:00:00 EST 2009        10
This because the table was not created with local timezone. The column in the database is TIMESTAMP - which holds a non timezone specified value.

When jdbc extracts values from a TIMESTAMP column into a java.sql.Timestamp via the getTimestamp method, it ensures that the value, when printed, shows the same DATE/TIME as that stored in the database.

The correct way to create the table would be:
create table jdbc_test (
  sequence_id number(2),
  cbt TIMESTAMP WITH LOCAL TIME ZONE,
  cbt_utc TIMESTAMP WITH TIME ZONE);
Now that I am using 'WITH LOCAL TIME ZONE', I need change my java code to include the timezone in the connection like '((OracleConnection)conn).setSessionTimeZone("Australia/Victoria");'

So the java code would now look like:
...
try {
 conn = DriverManager.getConnection(URL_NAME, prop);
} catch (Exception e) {
 System.out.println("Exception in creating the connection : " + e);
}

((OracleConnection)conn).setSessionTimeZone("Australia/Victoria");
String sqlString = " SELECT SEQUENCE_ID,CBT,CBT_UTC" + " FROM JDBC_TEST ";

PreparedStatement ps = conn.prepareStatement(sqlString.toString());
ResultSet rs = null;
...
Now when I run the java class, the correct results are shown:
ID   CBT(local time)                     CBT_UTC(GMT)                        Time Difference
1    Sun Apr 05 01:00:00 EST 2009        Sat Apr 04 14:00:00 EST 2009        11
2    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 15:00:00 EST 2009        11
3    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 16:00:00 EST 2009        11
4    Sun Apr 05 02:00:00 EST 2009        Sat Apr 04 17:00:00 EST 2009        10
5    Sun Apr 05 03:00:00 EST 2009        Sat Apr 04 18:00:00 EST 2009        10
6    Sun Apr 05 04:00:00 EST 2009        Sat Apr 04 19:00:00 EST 2009        10
7    Sun Apr 05 05:00:00 EST 2009        Sat Apr 04 20:00:00 EST 2009        10
8    Sun Apr 05 06:00:00 EST 2009        Sat Apr 04 21:00:00 EST 2009        10
9    Sun Apr 05 07:00:00 EST 2009        Sat Apr 04 22:00:00 EST 2009        10
10    Sun Apr 05 08:00:00 EST 2009        Sat Apr 04 23:00:00 EST 2009        10
11    Sun Apr 05 09:00:00 EST 2009        Sun Apr 05 00:00:00 EST 2009        10
12    Sun Apr 05 10:00:00 EST 2009        Sun Apr 05 01:00:00 EST 2009        10
13    Sun Apr 05 11:00:00 EST 2009        Sun Apr 05 02:00:00 EST 2009        10
14    Sun Apr 05 12:00:00 EST 2009        Sun Apr 05 02:00:00 EST 2009        10
15    Sun Apr 05 13:00:00 EST 2009        Sun Apr 05 03:00:00 EST 2009        10
16    Sun Apr 05 14:00:00 EST 2009        Sun Apr 05 04:00:00 EST 2009        10
17    Sun Apr 05 15:00:00 EST 2009        Sun Apr 05 05:00:00 EST 2009        10
18    Sun Apr 05 16:00:00 EST 2009        Sun Apr 05 06:00:00 EST 2009        10
19    Sun Apr 05 17:00:00 EST 2009        Sun Apr 05 07:00:00 EST 2009        10

Example of OracleDatabaseMetaData using OAS datasources to display table and view data

Posted by Steve Racanovic | Posted in , | Posted on 3:10 PM

0

A simple example of using oracle database metadata (from JDBC) with Oracle Application Server 1013x datasources to display the data.

1. My data-sources.xml looks like:

<managed-data-source connection-pool-name="scottPool2" jndi-name="jdbc/ScottDS2"
name="jdbc/ScottDS2"/>
<connection-pool name="scottPool2">
   <connection-factory factory-class="oracle.jdbc.pool.OracleDataSource"
user="scott" password="tiger" url="jdbc:oracle:thin:@//sracanov-au2.au.oracle.com:1522/orcl"/>
</connection-pool>

2. My java code in jdev looks like:

package examples;

import java.sql.Connection;

import java.sql.ResultSet;

import java.util.Properties;

import javax.naming.Context;
import javax.naming.InitialContext;

import javax.sql.DataSource;

import oracle.jdbc.OracleDatabaseMetaData;

public class TestOracleDatabaseMetaDataDataSource {
public static void main(String[] args) throws Exception {
   Properties props = new Properties();
   props.put(Context.PROVIDER_URL,
             "opmn:ormi://sracanov-au2.au.oracle.com:6005:OC4J_Apps/default");
   props.put(Context.INITIAL_CONTEXT_FACTORY,
             "oracle.j2ee.rmi.RMIInitialContextFactory");
   props.put(Context.SECURITY_PRINCIPAL, "oc4jadmin");
   props.put(Context.SECURITY_CREDENTIALS, "welcome1");
   Context ctx = new InitialContext(props);
   DataSource ds = (DataSource)ctx.lookup("jdbc/ScottDS2");
   Connection conn = ds.getConnection();
   OracleDatabaseMetaData dbmd =
       (OracleDatabaseMetaData)conn.getMetaData();
   ResultSet rs =
       dbmd.getTables(null, "%", "%", new String[] { "TABLE", "VIEW" });
   while (rs.next()) {
       System.out.println("   " + rs.getString(3) + " : " +
                          rs.getString(4));
   }
}

}
3. Ensure the following libraries are in the project:

* JDBC driver
* Apache Ant
* JSP Runtime

4. Run the class and the results are displayed:
      DR$NUMBER_SEQUENCE : TABLE
  DR$OBJECT_ATTRIBUTE : TABLE
  DR$POLICY_TAB : TABLE
  RLM$PARSEDCOND : TABLE
  OGIS_GEOMETRY_COLUMNS : TABLE
  OGIS_SPATIAL_REFERENCE_SYSTEMS : TABLE
  ....
  ....
  ....
  WM$REPLICATION_INFO : VIEW
  WM$TABLE_NEXTVERS_VIEW : VIEW
  WM$TABLE_PARVERS_VIEW : VIEW
  WM$TABLE_VERSIONS_IN_LIVE_VIEW : VIEW
  WM$TABLE_WS_PARVERS_VIEW : VIEW
  WM$VERSIONS_IN_LIVE_VIEW : VIEW
  WM$VER_BEF_INST_NEXTVERS_VIEW : VIEW
  WM$VER_BEF_INST_PARVERS_VIEW : VIEW
  WM_EVENTS_INFO : VIEW
  PATH_VIEW : VIEW
  RESOURCE_VIEW : VIEW
Results of the output have been truncated.

Subscribe to: Posts (Atom)