It's not a secret anymore!*
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
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: 8836
    
    7

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]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Error with user defined object datatypes
 
Similar Threads
java.sql.SQLData - Oracle object mapping problem
Character Set Unknown issue using datasource in JBOSS server
Character Set Unknown issue using datasource in JBOSS server
syntax error
Passing Nested Type from Java to DB