Re: Insert JAVA_OBJECT in embedded Derby DB

[Rick Hillegas]

Hi Kevin,

I think that Kristian and Bernt have given you the workarounds for this 
issue. It is true that Java ADTs were supported in earlier versions of 
Cloudscape. However, those ADTs were declared in a non-standard way. ADT 
support was disabled before the code was open-sourced as Derby--Derby 
attempts to follow the SQL Standard as closely as possible.


The good news is that ADT support was only lightly disabled at the 
parser level. Re-enabling this support with SQL Standard DDL should be 
straightforward. This effort is tracked by DERBY-651. For more context 
and for more advice about serializing objects into Derby, please see the 
following email thread: 
http://www.nabble.com/Storing-Java-Objects-in-a-table-tf454110.html#a1239466.


Hope this helps,
-Rick

kab wrote:

Hi,

I tried to directly insert a Java object from typ mottrow.MItem to the 
table ITEMS in my embedded Derby DB. But I got the following error:


java.sql.SQLException: An attempt was made to get a data value of type 
'BLOB' from a data value of type ' mottrow.MItem'


In the query I used, I had defined the row ITEM to the typ BLOB, 
because with the typ JAVA_OBJECT I got an error. In the derby 
documentation I have read that the a JAVA_OBJECT is stored as BLOB in 
the DB, so I defined it directly as BLOB.


SQL query used to create table:

String query = "create table APP.ITEMS (" +
"ID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS 
IDENTITY (START WITH 1, INCREMENT BY 1)," +

"ITEM BLOB," +
"LEVEL INT NOT NULL," +
"OWNER VARCHAR(30)," +
"LASTACCESS DATE," +
"FORMERLEVEL INTEGER NOT NULL )";

Statement to insert my JAVA_OBJECT:

PreparedStatement stmt = con.prepareStatement("insert into 
APP.ITEMS (ITEM) VALUES (?)");

stmt.setObject(1,item);
stmt.addBatch ( );

Does somebody knows a solution for this problem?


Sincerely

Kevin

Re: Insert JAVA_OBJECT in embedded Derby DB

[Bernt M. Johnsen]

You need to serialize the object. One way of doing it is like this:

ByteArrayOutputStream bos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(bos);
oos.writeObject(item);
oos.close();
ps.setBytes(1, bos.toByteArray());

An vice versa when you retrive the object :-)

-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway


signature.asc
Description: Digital signature

Re: Insert JAVA_OBJECT in embedded Derby DB

[Kristian Waagan]

Kevin Bortis wrote:



On 5/4/07, *Kristian Waagan* <[EMAIL PROTECTED] 
> wrote:


I'm sure someone will give you the full story, but I believe you might
have to serialize your objects yourself before you store them into the
database as BLOB/VARCHAR FOR BIT DATA (depending on the maximum size of
your objects, or your own preferences).


I thought  that when using JAVA_OBJECT as a data type, Java Objects are 
automatically serialized and deserialized in the JDBC layer. Is this false?


From the "JDBC API Tutorial and Reference, Third Edition", 50.4.7 page 
1077:
"The type JAVA_OBJECT is used by a database whose type system has been 
extended so that it can store Java objects directly. The JAVA_OBJECT 
value may be stored as a serialized Java object, or it may be stored in 
some vendor-specific format."


and

"For DBMSs that support them, values of type JAVA_OBJECT are stored in a 
database table using the method PreparedStatement.setObject."


These sentences indicate that a database is not required to support 
JAVA_OBJECTs, but it may do so. I do not know of a meta-data call to 
determine this (anyone?).
Further, I have not consulted the JDBC 4.0 spec or its compliance 
requirements on this issue.



Further, the JAVA SE 6 (JDBC 4.0) API documentation states that the 
setObject(int/String,Object,int) should throw a 
SQLFeatureNotSupportedException if the specified type is not supported. 
I will check if this is true for Derby and file a Jira issue if it is not.




--
Kristian




I think the JAVA_OBJECT type in Derby has been disabled, but could it
have been available in earlier Cloudscape releases?
I see comments in the code suggesting the support for JAVA_OBJECT is
"half-baked", so it would be nice if someone with knowledge of earlier
times could enlighten the rest of us :)


Would be nice to know if the feature is disabled.


sincerely

Kevin

Re: Insert JAVA_OBJECT in embedded Derby DB

[Kevin Bortis]

On 5/4/07, Kristian Waagan <[EMAIL PROTECTED]> wrote:


I'm sure someone will give you the full story, but I believe you might
have to serialize your objects yourself before you store them into the
database as BLOB/VARCHAR FOR BIT DATA (depending on the maximum size of
your objects, or your own preferences).



I thought  that when using JAVA_OBJECT as a data type, Java Objects are
automatically serialized and deserialized in the JDBC layer. Is this false?



I think the JAVA_OBJECT type in Derby has been disabled, but could it
have been available in earlier Cloudscape releases?
I see comments in the code suggesting the support for JAVA_OBJECT is
"half-baked", so it would be nice if someone with knowledge of earlier
times could enlighten the rest of us :)



Would be nice to know if the feature is disabled.


sincerely

Kevin

Re: Insert JAVA_OBJECT in embedded Derby DB

[Kristian Waagan]

kab wrote:

Hi,

I tried to directly insert a Java object from typ mottrow.MItem to the 
table ITEMS in my embedded Derby DB. But I got the following error:


java.sql.SQLException: An attempt was made to get a data value of type 
'BLOB' from a data value of type ' mottrow.MItem'


In the query I used, I had defined the row ITEM to the typ BLOB, because 
with the typ JAVA_OBJECT I got an error. In the derby documentation I 
have read that the a JAVA_OBJECT is stored as BLOB in the DB, so I 
defined it directly as BLOB.


SQL query used to create table:

String query = "create table APP.ITEMS (" +
"ID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS 
IDENTITY (START WITH 1, INCREMENT BY 1)," +

"ITEM BLOB," +
"LEVEL INT NOT NULL," +
"OWNER VARCHAR(30)," +
"LASTACCESS DATE," +
"FORMERLEVEL INTEGER NOT NULL )";

Statement to insert my JAVA_OBJECT:

PreparedStatement stmt = con.prepareStatement("insert into 
APP.ITEMS (ITEM) VALUES (?)");

stmt.setObject(1,item);
stmt.addBatch ( );

Does somebody knows a solution for this problem?


Hello Kevin,

I'm sure someone will give you the full story, but I believe you might 
have to serialize your objects yourself before you store them into the 
database as BLOB/VARCHAR FOR BIT DATA (depending on the maximum size of 
your objects, or your own preferences).


Another way to go is to use the object relational mapping technique, a 
third is to use an object-store instead of a relational database.


I think the JAVA_OBJECT type in Derby has been disabled, but could it 
have been available in earlier Cloudscape releases?
I see comments in the code suggesting the support for JAVA_OBJECT is 
"half-baked", so it would be nice if someone with knowledge of earlier 
times could enlighten the rest of us :)




regards,
--
Kristian




Sincerely

Kevin

Insert JAVA_OBJECT in embedded Derby DB

[kab]

Hi,

I tried to directly insert a Java object from typ mottrow.MItem to the table
ITEMS in my embedded Derby DB. But I got the following error:

java.sql.SQLException: An attempt was made to get a data value of type
'BLOB' from a data value of type 'mottrow.MItem'

In the query I used, I had defined the row ITEM to the typ BLOB, because
with the typ JAVA_OBJECT I got an error. In the derby documentation I have
read that the a JAVA_OBJECT is stored as BLOB in the DB, so I defined it
directly as BLOB.

SQL query used to create table:

String query = "create table APP.ITEMS (" +
   "ID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS
IDENTITY (START WITH 1, INCREMENT BY 1)," +
   "ITEM BLOB," +
   "LEVEL INT NOT NULL," +
   "OWNER VARCHAR(30)," +
   "LASTACCESS DATE," +
   "FORMERLEVEL INTEGER NOT NULL )";

Statement to insert my JAVA_OBJECT:

   PreparedStatement stmt = con.prepareStatement("insert into
APP.ITEMS (ITEM) VALUES (?)");
   stmt.setObject(1,item);
   stmt.addBatch( );

Does somebody knows a solution for this problem?


Sincerely

Kevin