Message-Id: <200011230805.eAN85FE17139@mail11.jump.net>
Subject: switching database  form oracle to postgress on a jdbc app
From: Jean-Pierre Loui <jplouis@coral-tech.com>
To: pgsql-interfaces@postgresql.org
Content-Type: text/plain
Date: 23 Nov 2000 14:05:14 +0600
Mime-Version: 1.0

I am currently trying to determine how easily I could swithch out the
database on an existing application.  I know some coding will have to be
done but need to find out how much and how difficult it will be.  

Currently the code uses the javax.sql.DataSource  to get connections to
the database, I don't believe this is supported in the postgres jdbc
implementation but I could probably write  a DataSource.  

My main concern is that the oracle database uses many user defined
datatypes and I didn't really see anything too similar. To use these
user
defined types in the java apps, many object have been written that
extend/implemnt java.sql.SQLData.  These objects are then used directly
in PreparedStatements

eg.
in oracle the following exist:

create type profile_obj  as object(
name       varchar2(56),
age          integer,
dob          date
)

create table profiles(
profile_id    int,
profile        profile_obj
)

In Java I would have an object:
public class Profile implements SQLData{

private sqlTypeName = "profile_obj";
private String name;
private int age;
private dob  java.sql.Date;

public Profile(){super();}

public void writeSQL(SQLOutput stream) 
  stream.writeString(name);
  stream.writeInt(age);
  stream.writeDate(dob);
}

public void readSQL(SQLInputStream stream){
sqlTypeName = stream.readString();
name = stream.readString();
age = stream.readInt();
dob = stream.readDate();
}

the code using this class would look like this


String query ="insert into profiles (profile_id, profile) values(?,?)";
PreparedStatement stmt = conn.prepareStatement(query);
//do conn.setTypeMap() stuff here

Profile profile = new Profile();//assume profile has some values set

stmt.setInt(1,100);
stmt.setObject(2,profile);
stmt.executeUpdate();

Statement stmt2 = conn.createStatement();
query = "select profile from profiles where profile_id = 100";
ResultSet rs = stmt2.execute(query);

Profile profile2;
if(rs.next())
  profile2 = (Profile)rs.getObject("profile");


So I guess my question is, does postgres support defined data types this
way, and if so will the jdbc driver support using SQLData objects  as in
the examples above and
Connection.setTypeMap()

Thanks.

ps. excuse my code if there are any blatant errors, it is late as I
write this.

Jean-Pierre Louis
Developer 
Coral Technologies  Austin, TX