• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Error with user defined object datatypes

 
Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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);}
}
}
 
Bartender
Posts: 9626
16
Mac OS X Linux Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
We cannot change unless we survive, but we will not survive unless we change. Evolving tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic