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.