aspose file tools*
The moose likes JDBC and the fly likes Error with user defined object datatypes Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error with user defined object datatypes" Watch "Error with user defined object datatypes" New topic
Author

Error with user defined object datatypes

clyde melly
Ranch Hand

Joined: Sep 04, 2003
Posts: 152
Trying to use user defined Object datatypes in oracle.Then using java
to insert values.I get an error
java.sql.SQLEXception : dataUnknown SQL type for
PreparedStatement.setObject(SQL type=1111)
Code :
import java.io.*;
import java.sql.*;
public class Person implements SQLData,Serializable
{
private int person_id;
private String last_name;
private String first_name;
private java.sql.Date birth_date;
private String gender;
public Person()
{ }
//SqlData interface
public String getSQLTypeName() throws SQLException
{
return "SCOTT.PERSON_TYPE";
}
public void readSQL(SQLInput stream,String type) throws SQLException
{
person_id = stream.readInt();
last_name = stream.readString();
first_name = stream.readString();
birth_date = stream.readDate();
gender = stream.readString();
}

public void writeSQL(SQLOutput stream) throws SQLException
{
stream.writeInt(person_id);
stream.writeString(last_name);
stream.writeString(first_name);
stream.writeDate(birth_date);
stream.writeString(gender);
}
//Accessors
public int getPersonId()
{
return person_id;
}
public String getLastName()
{
return last_name;
}
public String getFirstName()
{
return first_name;
}
public java.sql.Date getBirthDate()
{
return birth_date;
}
public String getGender()
{
return gender;
}
//Mutators

public void setPersonId(int person_id)
{
this.person_id = person_id;
}
public void setLastName(String last_name)
{
this.last_name = last_name;
}
public void setFirstName(String first_name)
{
this.first_name = first_name;
}
public void setBirthDate(java.sql.Date birth_date)
{
this.birth_date = birth_date;
}
public void setGender(String gender)
{
this.gender = gender;
}

public static void main(String g[])
{

Statement stmt=null;
PreparedStatement pstmt =null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection
con=DriverManager.getConnection("jdbc dbc:calvin","scott","tiger");


stmt = con.createStatement ();

stmt.execute ("CREATE TYPE o AS OBJECT(pno number(4),lname
varchar2(10),fname varchar2(10),birth_date date,sex varchar2(1))");

stmt.execute ("CREATE TABLE PERSOBJECT(pobj o)");

// Create a new instance for person class
Person p = new Person();
p.setPersonId(1);
p.setLastName("melwyn");
p.setFirstName("barretto");
p.setBirthDate(java.sql.Date.valueOf("1976-06-12"));
p.setGender("M");
// Insert into an object table
pstmt=con.prepareStatement("insert into PERS_OBJECT values(?)");

pstmt.setObject(1,p);

int rows = pstmt.executeUpdate();

System.out.println("Rows : " + rows);

pstmt.close();
pstmt = null;
con.commit();
stmt.close();
}
catch(Exception e){System.out.println("ex : " +e);}
}
}
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8713
    
    6

My experience with Oracle objects is scant, but it looks like you are playing fast and loose with the names you are using. Your Java type returns "SCOTT.PERSON_TYPE" for its sql type, you create a database type called "o", create a table named "PERSOBJECT", then try to insert to a table named "PERS_OBJECT". I'm pretty sure all these names should be the identical.
The example that I have seen also makes a Hashmap to relate the Java type's SQL name to the Java type's class:
hmap.put("SOMEDBNAME.SOMETYPENAME", SomeClass.class);
This map is used to tell the database know how to relate its database type to the Java class (called right after obtaining the Connection):
connection.setTypeMap(hmap);
There's some great documentation at the Oracle Technology Network. I'm sure they have a tutorial there.


"blabbing like a narcissistic fool with a superiority complex" ~ N.A.
[How To Ask Questions On JavaRanch]
 
Consider Paul's rocket mass heater.
 
subject: Error with user defined object datatypes
 
Similar Threads
Character Set Unknown issue using datasource in JBOSS server
java.sql.SQLData - Oracle object mapping problem
Passing Nested Type from Java to DB
Character Set Unknown issue using datasource in JBOSS server
syntax error