Using JDBC and CodeGen 'lite'

This page describes a streamlined approach to accessing Oracle data using JDBC and java generated from perl scripts and simple input files.

Lets assume you have an existing Oracle database schema which contains a table named FILESET:

SQL> desc FILESET;

 Name                            Null?    Type
 ------------------------------- -------- ----
 FILESET_NAME                             VARCHAR2(128)
 DS_NAME_ID                               NUMBER(38)
 TAPE_LABEL                               VARCHAR2(128)
 TAPE_PARTITION                           FLOAT(126)
 CREATE_TIME                              FLOAT(126)
 FILE_COUNT                               NUMBER(38)


SQL> 

• Define a c++ structure like FilesetStruct.dat which will be used as input to the code generation step. Right now the code generator only accepts simple types such as (strings, longs, doubles, ints, and floats), which correspond to the most common database datatypes of (varchar, int, int, float, float) or if you've been using Oracle too long to remember standard SQL, (varchar2, number,number,number,number).

• Use the perl script makeJavaObj.pl to generate a java class FilesetStruct.java which uses JDBC/java methods doOneInsert() and fetchOneFromRslt() to do I/O from an oracle database.
  From the command line:
  makeJavaObj.pl FilesetStruct.dat > FilesetStruct.java

• Use the perl script makeJavaHarness.pl to generate a java test harness program FilesetStructHarness.java which excercises the above class.
  From the command line:
  makeJavaHarness.pl FilesetStruct.dat > FilesetStructHarness.java

• Compiling and Running:

  In this example, the table described above already exists..

  fndapl.fnal.gov:/home/dbox/javaGen% setup oracle_client
  fndapl.fnal.gov:/home/dbox/javaGen% setup jdbc
  fndapl.fnal.gov:/home/dbox/javaGen% setup java
  fndapl.fnal.gov:/home/dbox/javaGen% setenv CLASSPATH .:${CLASSPATH}
  fndapl.fnal.gov:/home/dbox/javaGen% ls *.java
  FilesetStruct.java         SimpleQuery.java
  FilesetStructHarness.java  foo.java
  fndapl.fnal.gov:/home/dbox/javaGen% javac FilesetStruct.java
  fndapl.fnal.gov:/home/dbox/javaGen% javac FilesetStructHarness.java
  fndapl.fnal.gov:/home/dbox/javaGen% java FilesetStructHarness
  -------------------------------------------
  Simple jdbc ORACLE query demonstration program
  you will be prompted for an oracle username,
  password, server node name, SID name, 
  and query.  Examples of server node names 
  that worked as of 12/18/98 at fnal: 
  Server Node            SID 
  --------------------------------------------
  fncduh                 dev1 
  fndauk                 dev2 
  b0dau30                ordb 
  cdfora                 cdfoff1 
  ---------------------------------------------
  enter a node:b0dau30
  enter a SID:ordb
  oracle userid:newcalib
  oracle password:XXXX
  preparing to execute: INSERT INTO FILESET ( FILESET_NAME, DS_NAME_ID, TAPE_LABEL, TAPE_PARTITION, CREATE_TIME, FILE_COUNT )VALUES(?, ?, ?, ?, ?, ?)
  inserting datum: test 0  |  0  |  test 0  |  0.0  |  0.0  |  0
  inserting datum: test 1  |  1  |  test 1  |  1.0  |  1.0  |  1
  inserting datum: test 2  |  2  |  test 2  |  2.0  |  2.0  |  2
  inserting datum: test 3  |  3  |  test 3  |  3.0  |  3.0  |  3
  inserting datum: test 4  |  4  |  test 4  |  4.0  |  4.0  |  4
  inserting datum: test 5  |  5  |  test 5  |  5.0  |  5.0  |  5
  inserting datum: test 6  |  6  |  test 6  |  6.0  |  6.0  |  6
  inserting datum: test 7  |  7  |  test 7  |  7.0  |  7.0  |  7
  inserting datum: test 8  |  8  |  test 8  |  8.0  |  8.0  |  8
  inserting datum: test 9  |  9  |  test 9  |  9.0  |  9.0  |  9
  executing query: SELECT FILESET_NAME, DS_NAME_ID, TAPE_LABEL, TAPE_PARTITION, CREATE_TIME, FILE_COUNT FROM FILESET
  -------------------------------------------
  FILESET_NAME  |  DS_NAME_ID  |  TAPE_LABEL  |  TAPE_PARTITION  |  CREATE_TIME  |  FILE_COUNT
  -----------------------------------
  test 0  |  0  |  test 0  |  0.0  |  0.0  |  0
  test 1  |  1  |  test 1  |  1.0  |  1.0  |  1
  test 2  |  2  |  test 2  |  2.0  |  2.0  |  2
  test 3  |  3  |  test 3  |  3.0  |  3.0  |  3
  test 4  |  4  |  test 4  |  4.0  |  4.0  |  4
  test 5  |  5  |  test 5  |  5.0  |  5.0  |  5
  test 6  |  6  |  test 6  |  6.0  |  6.0  |  6
  test 7  |  7  |  test 7  |  7.0  |  7.0  |  7
  test 8  |  8  |  test 8  |  8.0  |  8.0  |  8
  test 9  |  9  |  test 9  |  9.0  |  9.0  |  9
  
  

• To Do:
  • this approach should handle BLOBs with only minor mods to the generating scripts....
  • a script that takes oracle 'desc' output and generates the .dat structure would be nice
  • Translate the above scripts into python....Why??? do you ask???