switching database form oracle to postgress on a jdbc app

From: Jean-Pierre Loui <jplouis(at)coral-tech(dot)com>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: switching database form oracle to postgress on a jdbc app
Date: 2000-11-23 08:05:14
Message-ID: 200011230805.eAN85FE17139@mail11.jump.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces

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

Browse pgsql-interfaces by date

  From Date Subject
Next Message sathya priya 2000-11-23 14:31:00 PQendcopy: resetting connection error
Previous Message Tom Lane 2000-11-23 04:34:09 Re: Pg7.1-beta and CVS psqlodbc.dll: No views