Informix -> Oracle conversions
Changes in DDL, DML, DQL
DDL:
create table
some column types are different
Informix dates TO DAY or above -> DATE
treat as java.sql.Date for JDBC
Dates to minute -> DATE
treat as java.sql.Timestamp
SERIAL -> number(x,Y), not auto-assigned
adapt with "create sequence"
integer -> number(38) (very large)
no "HTML" type
Supports clob types clob, blob
varchars are the same
oracle varchars limited to 4000 bytes (not 4Kb)
CONSTRAINTS
Oracle supports named constraints
constraints can be dropped/added to table by name
foreign key constraint syntax is different
primary key constraint syntax is compatible
also has a different form that's oracle specific
default constraints
additional storage specifications
can optionally specific space usage as part of create table
create index
seems to be the same
EXAMPLE:
Oracle:
create table media (
media_id number(9,0) CONSTRAINT pk_media PRIMARY KEY,
media_type_id number(9,0) CONSTRAINT fk_media_media_type REFERENCES media_type(media_type_id),
height number(5,0) ,
width number(5,0),
filename varchar(50) CONSTRAINT nn_media_filename NOT NULL,
caption varchar(255),
credit varchar(100),
alt varchar(100),
filesize number(7,0) CONSTRAINT nn_media_filesize NOT NULL,
created_on date DEFAULT sysdate)
PCTFREE 5 PCTUSED 75;
Informix:
create table media (
media_id INTEGER , PRIMARY KEY(media_id),
media_type_id INTEGER, FOREIGN KEY (media_type_id )
REFERENCES media_type(media_type_id),
height INTEGER ,
width INTEGER,
filename varchar(50) NOT NULL ,
caption varchar(255),
credit varchar(100),
alt varchar(100),
filesize INTEGER NOT NULL ,
created_on DATETIME YEAR TO MINUTE DEFAULT CURRENT YEAR TO MINUTE);
DML/DQL Differences
UPDATE syntax is different,
UPDATE
set (column1, column2) = (value1, value2)
NOT ALLOWED
UPDATE set =, column2=
NOTE: no parens
Remember to check for NULLs,
select max(column)+1 from table
returns a null for an empty database
CLOB handling
to be handled a little later
::date --> nothing
::html --> clob stuff
OUTER joins
OUTER joins in oracle are specified in the WHERE clause
OUTER keyword not supported
JDBC 2.0 also has an OUTER JOIN apis but ARE NOT SUPPORTED by Oracle
use oracle syntax: (+)
e.g., RIGHT outer join
Informix:
select *
from table1 t1, OUTER table2 t2
where t1.PK = t2.PK
Oracle:
select *
from table1 t1, table2 t2
where t1.PK = t2.PK(+)
Default date format on Oracle is YYYY-MM-DD
use JDBC to handle this
Informix "CURRENT" keyword is "sysdate"
use java to handle this if possible
e.g., to get tomorrow's date in YYYY-MM-DD
// NOTE: case sensitive date format
java.text.SimpleDateFormat xDateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd");
java.util.GregorianCalendar cal = new java.util.GregorianCalendar();
cal.add( Calendar.DAY_OF_MONTH, + 1 );
// this is locale=dependent
String today = xDateFormat.format(cal.getTime());
e.g., to get today's date
// NOTE: case sensitive date format
SimpleDateFormat xDateFormat = new SimpleDateFormat("yyyy-MM-dd");
java.util.GregorianCalendar cal = new java.util.GregorianCalendar();
// this is locale=dependent
today = xDateFormat.format(cal.getTime());
Oracle functions TO_DATE and TO_CHAR used for date conversions
useful in WHERE clauses
JDBC Issues
Informix JDBC driver DOES NOT support read only connections
Oracle does, less overhead
date handling is not standard in SQL, but IS in JDBC
JDBC date format is YYYY-MM-DD
(not the same as java's date format masks)
use JDBC date/time escape syntax for inserts, updates and comparisons
Dates:
syntax: {d 'YYYY-MM-DD'}
JDBC examples:
"insert into date_test values (sysdate-1, {d '2000-09-02'})"
"select end_date from date_test where end_date > {d '2000-05-04'}"
also works in PreparedStatement, e.g.,
"select * from foo where datecol = {d ?}"
"setString(1, java.lang.String);"
Not needed for setDate
i.e., do not use JDBC escape sequence when using setDate(x, java.sql.Date)
Timestamps:
syntax: {ts 'YYYY-MM-DD hh:mm:ss:mmm'} hours:minutes:seconds:microseconds
e.g., {ts '2000-05-04 13:20:01.123'}
does NOT work in prepared statement as:
"{ts ?}"
setString(X, java.lang.String)
DOES work as setTimestamp
insert into timestamp_test values ( ? , ? )
setTimestamp(1, new java.sql.Timestamp(new java.util.Date().getTime())
setTimestamp(2, new java.sql.Timestamp(new java.util.Date().getTime())
Time:
{t 'hh:mm:ss')
for selects: convert dates to Strings
use: java.sql.ResultSet.getDate(x)
e.g., in default JDBC date format:
use java.sql.ResultSet.getDate(x).toString()
e.g.,
String publish_date = rs.getDate(8).toString();
CLOB Handlings:
Informix seems better
Will likely be improved in future versions of JDBC
Clob Creation in Oracle:
Oracle CLOBS must be written in 5 steps
Step1: turn autocommit off in the database connection
e.g./i.e.,
java.sql.Connection.setAutoCommit(false);
Step2: create clob in "insert" using "EMPTY_CLOB()"
e.g.,
xSQLStatement = fxDbConnection.createStatement();
// the 1st column is the row PK
sQuery = new String("insert into clobtest values (1, EMPTY_CLOB()) ");
xSQLStatement.execute(sQuery);
Step3: read the clob back out, and convert it to an ORACLE clob
e.g.,
xSQLStatement.executeQuery("select data from clobtest where clobid = 1 FOR UPDATE");
NOTE: "FOR UPDATE" is mandatory to lock the CLOB
ResultSet xRS = xSQLStatement.getResultSet();
xRS.next();
oracle.sql.CLOB xClob = (oracle.sql.CLOB)xRS.getClob(1);
Step4: write data to the clob's output stream
e.g.,
java.io.OutputStream os = xClob.getAsciiOutputStream();
// sClobData is the stuff you want to store in the CLOB
String sData = sClobData.toString();
os.write(sData.getBytes());
os.flush();
Step5: close the streams and commit the data
e.g.,
if (xRS != null)
xRS.close();
if (xSQLStatement != null)
xSQLStatement.close();
fxDbConnection.commit();
Reading Clobs in Oracle:
Read in 2 steps
Step1: open the clob
e.g.,
xSQLStatement.executeQuery("select data from clobtest where clobid = 1");
ResultSet xRS = xSQLStatement.getResultSet();
xRS.next();
oracle.sql.CLOB xClob = (oracle.sql.CLOB)xRS.getClob(1);
Step2: read like any other buffered input stream
e.g.,
StringBuffer sOutputClob = new StringBuffer();
java.io.InputStream is = xClob.getAsciiStream();
byte[] buf = new byte[2048]; // reading in 2k chunks
int iRead = 1;
while (iRead > 0)
{
buf[0] = 0;
iRead = is.read(buf, 0, 80);
if (iRead < 0)
break;
buf[iRead] = 0;
sOutputClob.append(new String(buf, 0, iRead));
}
Updating Clobs
Done like insert + select
usually not part of an "update" statement
e.g.,
// autoCommmit is OFF
xSQLStatement.executeQuery("select data from clobtest where clobid = 1 FOR UPDATE");
xRS = xSQLStatement.getResultSet();
xRS.next();
oracle.sql.CLOB xClob = (oracle.sql.CLOB)xRS.getClob(1);
java.io.OutputStream os = xClob.getAsciiOutputStream();
// sClobData is the stuff you want to store in the CLOB
os.write(sClobData.getBytes());
os.flush();
os.close();
connection.commit();
Misc Oracle CLOB stuff:
oracle uses the DBMS_LOB package to manipulate LOBs,
e.g., cannot check for clobcolumn "IS NULL/IS NOT NULL" if use EMPTY_CLOB(),
need to use DBMS_LOB.getlength(clobcolumn) != 0
Data Conversion (sample code)
Easy to do if code to JDBC
CLOB tables are different though
Step 1: Create tables on Oracle that Match the IFMX tables
Step 2: create java app/applet whatever
Create informix and oracle database connections
example:
(for all tables)
{
// get the Informix stuff
xIfmxState.execute("select * from " + sTable);
ResultSet xIRs = xIfmxState.getResultSet();
ResultSetMetaData xMDRS = xIRs.getMetaData();
int iColumnCount = xMDRS.getColumnCount();
// create the oracle statement on the fly
StringBuffer sOracleInsert = new StringBuffer(" insert into " + sTable + " values ( ");
for (int iTer = 1; iTer < iColumnCount+1; iTer++) // getColumn(xx) is 1 initial
{
sOracleInsert.append("?");
if (iTer+1 < iColumnCount+1)
sOracleInsert.append(",");
}
sOracleInsert.append(")");
// do as a prepared statement
xOraclePrep = xOrclConn.prepareStatement(sOracleInsert.toString());
int iInformixCount = 0, iOracleCount = 0;
while (xIRs.next())
{
try
{
iInformixCount++;
// getColumn(xx) is 1 initial
for (int iTer = 1; iTer < iColumnCount+1; iTer++)
{
Object xObtained = xIRs.getObject(iTer);
String sColumnName = xMDRS.getColumnName(iTer);
int iJDBCType = xMDRS.getColumnType(iTer);
// expand it for debugging purposes
if (xObtained instanceof java.sql.Date)
xOraclePrep.setObject(iTer, xObtained);
else if (xObtained instanceof java.sql.Timestamp)
xOraclePrep.setTimestamp(iTer, (Timestamp)xObtained);
else if (xObtained instanceof java.sql.Time)
xOraclePrep.setTime(iTer, (Time)xObtained);
else if (xObtained instanceof java.lang.Integer) // IFMX does not support
xOraclePrep.setObject(iTer, xObtained);
else if (xObtained instanceof java.lang.String)
xOraclePrep.setObject(iTer, xObtained);
else
{
if (xObtained == null) // no value...
{
xOraclePrep.setNull(iTer, iJDBCType);
}
else
{
System.out.println("Unknown Object Type Id: " + iPkId + " :Column: " + sColumnName + " :JDBC Type: " + iJDBCType);
switch (iJDBCType)
{
case java.sql.Types.CLOB:
// NOT HANDLED HERE, left as an exercise for the reader
System.out.println("CLOB Object Type Id: " + iPkId + " :Column: " + sColumnName );
break;
case java.sql.Types.JAVA_OBJECT: // informix specific b.s.
System.out.println("Generic Object Type, attempting ");
xOraclePrep.setObject(iTer, xObtained);
break;
case java.sql.Types.INTEGER:
int iValue = xIRs.getInt(iTer);
xOraclePrep.setInt(iTer, iValue);
break;
default:
System.out.println("Not a Handled Type ");
break;
}
}
}
}
xOraclePrep.executeUpdate();
iOracleCount++;
}
}
END --------------------------------------