Storing Objects in DB2

[Rhino]

Is it possible to store Java objects in DB2 V8.2 for Windows/Unix/Linux via
JDBC?

Specifically, if I have a 4-dimensional boolean array, i.e. boolean[][][][],
can I store it directly in a column of a DB2 table? If so, how do I do it?

It would be VERY convenient if I could store this boolean array directly in
a column of a DB2 table but I'm not at all clear on whether this is
possible, even after reading the documentation in the Information Center. I
was intrigued by the setObject() and getObject() methods in JDBC but I'm not
at all sure if I can use them for the purpose I described. I'm especially
unlclear about what datatype the column containing the Object could/should
be.

If I can't store a multidimensiona l boolean array directly in a table
column, I can always convert it into a representation of the array that uses
more traditional datatypes. For instance, I could convert:

boolean[] myArray = {true, false, true};

into this String:

String myString = "TFT"; //T=true; F=false

then store the String representation of the array in one of the CHAR column
types. But it would be much more convenient if I could simply store the
boolean[][][][] array directly in a column of the table. Does anyone know if
that is possible? If it is, a brief code snippet showing how to insert the
value would be VERY helpful!

--
Rhino

[Gregor Kova�]

Rhino wrote:

>
"Gregor Kovac" <gr**********@m ikropis.siwrote in message
news:2X******** ************@ne ws.siol.net...

Thanks Gregor, I think you've just answered my question. It sounds like I
always need to convert all but the simplest of Objects to byte arrays for
storage in BLOBs or VARCHAR FOR BIT DATA columns. The only things I don't
need to convert to byte arrays are integers, doubles, floats,
dates/times/timestamps, and Strings: those can be stored directly in the
corresponding DB2 datatypes.

--
Rhino

What you could also do is to persist Java object fields into table columns,
like:
class Person{
String name;
String address;
}

You don't have to serialize the Person class in order to store it into the
database, you can map fields (name and address) into a table PERSON that
has NAME and ADDRESS columns.
This way you can even work with collections, ....

This process is called object/relational persistence and there are numberous
product available. One of the better ones is Hibernate
(http://www.hibernate.org/). Others can be found at
http://java-source.net/open-source/persistence

Tell us what you come up with :)

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mi kropis.si |
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[gimme_this_gimme_that]

Hey Rhino,

(Does that mean republican in name only?)

Why not write a text representation of the object into a LOB column?

You could use Java 1.4's java.beans.* package which provides tools for
the encoding and decoding:

This will get you started. This code encodes and decodes an object
representation from a file on a disk.

You'll have to modify the implementation to writing object to a LOB
column using JDBC.

import java.io.*;
import java.beans.*;
public class ENXML
{
public static void encode( String FileName , A a )
throws FileNotFoundExc eption
{
XMLEncoder encoder = new XMLEncoder(
new BufferedOutputS tream(
new FileOutputStrea m( FileName )));
encoder.writeOb ject( a );
encoder.close() ;
}
public static A decode( String FileName )
throws FileNotFoundExc eption
{
XMLDecoder decoder = new XMLDecoder(
new BufferedInputSt ream(
new FileInputStream ( FileName )));
A a = (A)decoder.read Object();
decoder.close() ;
return a;
}

[Purple-D]

Rhino,

Iam currently migrating a database which has something similar to java
objects as primary keys. However the db2 datatype for that is char(16)
in our tables, so am guessing the java application merely converts the
object data into char(16) format when inserting or accessing records
from the relational tables. Ofcourse lot of this data is represented in
hexa and would not be visible with a mere select colname from
tablename.

I had a lot of problem migrating this to windows so am guessing there
is some platform dependence.

Cheers
PD
Rhino wrote:

"Serge Rielau" <sr*****@ca.ibm .comwrote in message
news:4h******** *****@individua l.net...

Rhino wrote:

By the way, do you have any idea when/if DB2 will support the ARRAY
datatype? Is it in Version 9 by any chance?

Not in DB2 9. And if you want to know what's in Python you'll need an NDA
and sign your name in blood.

It's okay, I wasn't trying to find out anything that hadn't already been
revealed :-) I just thought there might have been some kind of announcement
about a general strategic direction with respect to datatype support for
coming versons of DB2.

Can you define what you mean by an object? If you can pass DB2 the java
object in binary format it'll gladly store that.

Virtually everything in Java is an Object. In fact, the only things that
aren't Objects are the primitives: short, int, long, boolean, char, and one
or two others. And even the primitives have wrapper classes that can turn
them into Objects. So pretty much anything you can imagine is an Object: a
File, a URL, a ResultSet, an Image, GUI components, etc. etc. etc.

I suppose I was just dreaming the impossible dream but when I saw methods
setObject() and getObject() it occurred to me it might mean that _any_ Java
Object might be storable in and retrievable from DB2 _DIRECTLY_. Then,
instead of having to linearize the data into a byte stream for the insert
and then convert it back when I read the data, I could just store it in its
original form and get it back that same way.

Naturally, I looked at the Java API and the DB2 documentation on setObject()
and getObject() but I found them very vague and they didn't answer the
question of whether they worked the way that I would have liked them to
work. But it now seems pretty clear that they DON'T work the way I wanted.

It struck me that my impossible dream might be something that DB2 will be
doing somewhere down the road and might even have been announced at some
point; I could have easily missed such an announcement. That's why I asked
whether it was coming in Viper or further down the road.

I'm going to hold on to a vague hope that direct storage of any Object in a
DB2 table will be possible some day but I'm going to make sure that I don't
expect "some day" to be any time soon :-)

In the meantime, I will convert Objects to byte arrays as I currently do
with JPGs or audio files. This is not really a problem. It would just be
that much more convenient if I didn't have to convert to or from byte arrays
to store and use my Objects.

--
Rhino

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Purple-D]

Rhino,

Iam currently migrating a database which has something similar to java
objects as primary keys. However the db2 datatype for that is char(16)
in our tables, so am guessing the java application merely converts the
object data into char(16) format when inserting or accessing records
from the relational tables. Ofcourse lot of this data is represented in
hexa and would not be visible with a mere select colname from
tablename.

I had a lot of problem migrating this to windows so am guessing there
is some platform dependence.

Cheers
PD
Rhino wrote:

"Serge Rielau" <sr*****@ca.ibm .comwrote in message
news:4h******** *****@individua l.net...

Rhino wrote:

By the way, do you have any idea when/if DB2 will support the ARRAY
datatype? Is it in Version 9 by any chance?

Not in DB2 9. And if you want to know what's in Python you'll need an NDA
and sign your name in blood.

It's okay, I wasn't trying to find out anything that hadn't already been
revealed :-) I just thought there might have been some kind of announcement
about a general strategic direction with respect to datatype support for
coming versons of DB2.

Can you define what you mean by an object? If you can pass DB2 the java
object in binary format it'll gladly store that.

Virtually everything in Java is an Object. In fact, the only things that
aren't Objects are the primitives: short, int, long, boolean, char, and one
or two others. And even the primitives have wrapper classes that can turn
them into Objects. So pretty much anything you can imagine is an Object: a
File, a URL, a ResultSet, an Image, GUI components, etc. etc. etc.

I suppose I was just dreaming the impossible dream but when I saw methods
setObject() and getObject() it occurred to me it might mean that _any_ Java
Object might be storable in and retrievable from DB2 _DIRECTLY_. Then,
instead of having to linearize the data into a byte stream for the insert
and then convert it back when I read the data, I could just store it in its
original form and get it back that same way.

Naturally, I looked at the Java API and the DB2 documentation on setObject()
and getObject() but I found them very vague and they didn't answer the
question of whether they worked the way that I would have liked them to
work. But it now seems pretty clear that they DON'T work the way I wanted.

It struck me that my impossible dream might be something that DB2 will be
doing somewhere down the road and might even have been announced at some
point; I could have easily missed such an announcement. That's why I asked
whether it was coming in Viper or further down the road.

I'm going to hold on to a vague hope that direct storage of any Object in a
DB2 table will be possible some day but I'm going to make sure that I don't
expect "some day" to be any time soon :-)

In the meantime, I will convert Objects to byte arrays as I currently do
with JPGs or audio files. This is not really a problem. It would just be
that much more convenient if I didn't have to convert to or from byte arrays
to store and use my Objects.

--
Rhino

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/