using BLOB objects and ...

[ali]

Hi there well i am having a problem in one of my current projects and
it is that

i have to insert an 4 dimention array into a database and the best
thing i was adviced until now is to use a BLOB, but BLOB is just an
interface in java.sql.* so how can i create an object of Blob and
again how i can insert my 4d array to it.

 

[Rhino]

Hi there well i am having a problem in one of my current projects and
it is that

i have to insert an 4 dimention array into a database and the best
thing i was adviced until now is to use a BLOB, but BLOB is just an
interface in java.sql.* so how can i create an object of Blob and
again how i can insert my 4d array to it.

What kind of data is in your 4 dimensional array? Integers? Strings? JPEGs?

Is it vital that your 4 dimensional array be stored as a single object?

How big is your array? Even BLOBs have size limits. In some cases, it might
be necessary to break your array into smaller pieces. And in some cases,
BLOBs might not even be necessary if the array is small enough.

Also, in many cases database analysts will store data outside of the
database and only store a link to the location of that data within the
database itself. Is that permissible in your case or is it essential that
the array is stored within the database?

It's very hard to suggest an answer to your requirement until you answer
these questions.

 

[vijjus4u]

you can insert any kind of file to database by using following code
i hope this program will usefull for you

Example on storing images into BLOB data type.
- Managing SQL 92 data types (BLOB, CLOB and Ref)
BLOB (Binary Large OBject)
Used to store binary informations like images, audio files etc
-For each byte one byte of memory will be allocated
-Size can vary up to 4GB for each entry
CLOB (Character LOB)
-Used to store character information like plain, word documents
-For each character two bytes of memory is allocated
-Size up to 4GB
Ref (Reference):
-The column declared with this data type instead of storing a file into
DB contains
a pointer pointing to the file located in the hard disk
-Size up to 4GB
*/
// ImageStore.java
import java.sql.*;
import java.io.*;
public class ImageStore
{
public static void main(String rags[]) thro

ws Exception
{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbcdbc:mysqlDSN","root",
"active");
PreparedStatement pstmt=con.prepareStatement("insert into temp
values(?,?)");
File f=new File("xml-pic.jpg");
FileInputStream fis=new FileInputStream(f);
pstmt.setInt(1, 1);
pstmt.setBinaryStream(2, fis, (int)f.length());
int i=pstmt.executeUpdate();
System.out.println(i+" record inserted");
pstmt.close();
con.close();
}// main()
}// class

 

[ali]

What kind of data is in your 4 dimensional array? Integers? Strings? JPEGs?

Is it vital that your 4 dimensional array be stored as a single object?

How big is your array? Even BLOBs have size limits. In some cases, it might
be necessary to break your array into smaller pieces. And in some cases,
BLOBs might not even be necessary if the array is small enough.

Also, in many cases database analysts will store data outside of the
database and only store a link to the location of that data within the
database itself. Is that permissible in your case or is it essential that
the array is stored within the database?

It's very hard to suggest an answer to your requirement until you answer
these questions.

thanks for your reply and intrest

the array is a boolean array which contain data of about 53000 elements
all of these elements of the 4 dimention array reprisents one thing,
and i have to store and retrieve more than 200,000 of those things <--
hope my way of explaination is cleare

well that why i think it should be in the database but if it should be
a BLOB or not and how to be able to converte it to BLOB
i hope you and the others here are going to help me by telling me what
i should do

thanks in advance

 

[Rhino]

you can insert any kind of file to database by using following code
i hope this program will usefull for you

Example on storing images into BLOB data type.
- Managing SQL 92 data types (BLOB, CLOB and Ref)
BLOB (Binary Large OBject)
Used to store binary informations like images, audio files etc
-For each byte one byte of memory will be allocated
-Size can vary up to 4GB for each entry
CLOB (Character LOB)
-Used to store character information like plain, word documents
-For each character two bytes of memory is allocated
-Size up to 4GB
Ref (Reference):
-The column declared with this data type instead of storing a file into
DB contains
a pointer pointing to the file located in the hard disk
-Size up to 4GB
*/
// ImageStore.java
import java.sql.*;
import java.io.*;
public class ImageStore
{
public static void main(String rags[]) thro

ws Exception
{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbcdbc:mysqlDSN","root",
"active");
PreparedStatement pstmt=con.prepareStatement("insert into temp
values(?,?)");
File f=new File("xml-pic.jpg");
FileInputStream fis=new FileInputStream(f);
pstmt.setInt(1, 1);
pstmt.setBinaryStream(2, fis, (int)f.length());
int i=pstmt.executeUpdate();
System.out.println(i+" record inserted");
pstmt.close();
con.close();
}// main()
}// class

Your code seems to be assuming that the original poster is storing a single
JPEG. But he is actually storing a 4 dimensional array containing thousands
of booleans. I'm not sure your code will work for an Object of the kind the
poster wants to store.../

 

[Rhino]

thanks for your reply and intrest

the array is a boolean array which contain data of about 53000 elements
all of these elements of the 4 dimention array reprisents one thing,
and i have to store and retrieve more than 200,000 of those things <--
hope my way of explaination is cleare

Do you mean that your database will have 200,000+ rows and that each row
will have an array of booleans containing 53000 elements in four dimensions?
Or am I misunderstanding you?

well that why i think it should be in the database but if it should be
a BLOB or not and how to be able to converte it to BLOB
i hope you and the others here are going to help me by telling me what
i should do

What database are you using and what version is that database? For example,
MySQL Version 5.0 or DB2 for Linux/Unix/Windows Version 8.2. I should have
asked that in my previous note. Support for BLOBs and other "exotic"
datatypes like BLOBs depends very heavily on which database you are using
and what version it is. For example, DB2 has supported BLOBs for quite a
while but only the newer versions of MySQL support them.

 

[Matt Humphrey]

you can insert any kind of file to database by using following code
i hope this program will usefull for you

Example on storing images into BLOB data type.
- Managing SQL 92 data types (BLOB, CLOB and Ref)
BLOB (Binary Large OBject)
Used to store binary informations like images, audio files etc
-For each byte one byte of memory will be allocated
-Size can vary up to 4GB for each entry
CLOB (Character LOB)
-Used to store character information like plain, word documents
-For each character two bytes of memory is allocated
-Size up to 4GB
Ref (Reference):
-The column declared with this data type instead of storing a file into
DB contains
a pointer pointing to the file located in the hard disk
-Size up to 4GB
*/
// ImageStore.java
import java.sql.*;
import java.io.*;
public class ImageStore
{
public static void main(String rags[]) thro

ws Exception
{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbcdbc:mysqlDSN","root",
"active");
PreparedStatement pstmt=con.prepareStatement("insert into temp
values(?,?)");
File f=new File("xml-pic.jpg");
FileInputStream fis=new FileInputStream(f);
pstmt.setInt(1, 1);
pstmt.setBinaryStream(2, fis, (int)f.length());
int i=pstmt.executeUpdate();
System.out.println(i+" record inserted");
pstmt.close();
con.close();
}// main()
}// class

Your code seems to be assuming that the original poster is storing a
single JPEG. But he is actually storing a 4 dimensional array containing
thousands of booleans. I'm not sure your code will work for an Object of
the kind the poster wants to store.../

Actually, this code simply copies an arbitrary byte stream (which happens to
come from a jpb) into a BLOB parameter of a prepared statement. The OP can
apply this by serializing the original 4-dimensional array to a byte stream,
something along the lines of this:

Object object = ? // arbitrary serializable object
ByteArrayOutputStream bos = new ByteArrayOutputStream ();
ObjectOutputStream oos = new ObjectOutputStream (bos);
oos.writeObject (object); oos.flush ();
bos.close ();
byte [] byteArray = bos.toByteArray ();

pstmt.setBytes (2, byteArray);

Cheers,
Matt Humphrey (e-mail address removed) http://www.iviz.com/

 

[Rhino]

you can insert any kind of file to database by using following code
i hope this program will usefull for you

Example on storing images into BLOB data type.
- Managing SQL 92 data types (BLOB, CLOB and Ref)
BLOB (Binary Large OBject)
Used to store binary informations like images, audio files etc
-For each byte one byte of memory will be allocated
-Size can vary up to 4GB for each entry
CLOB (Character LOB)
-Used to store character information like plain, word documents
-For each character two bytes of memory is allocated
-Size up to 4GB
Ref (Reference):
-The column declared with this data type instead of storing a file into
DB contains
a pointer pointing to the file located in the hard disk
-Size up to 4GB
*/
// ImageStore.java
import java.sql.*;
import java.io.*;
public class ImageStore
{
public static void main(String rags[]) thro

ws Exception
{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbcdbc:mysqlDSN","root",
"active");
PreparedStatement pstmt=con.prepareStatement("insert into temp
values(?,?)");
File f=new File("xml-pic.jpg");
FileInputStream fis=new FileInputStream(f);
pstmt.setInt(1, 1);
pstmt.setBinaryStream(2, fis, (int)f.length());
int i=pstmt.executeUpdate();
System.out.println(i+" record inserted");
pstmt.close();
con.close();
}// main()
}// class

Your code seems to be assuming that the original poster is storing a
single JPEG. But he is actually storing a 4 dimensional array containing
thousands of booleans. I'm not sure your code will work for an Object of
the kind the poster wants to store.../

Actually, this code simply copies an arbitrary byte stream (which happens
to come from a jpb) into a BLOB parameter of a prepared statement. The OP
can apply this by serializing the original 4-dimensional array to a byte
stream, something along the lines of this:

Object object = ? // arbitrary serializable object
ByteArrayOutputStream bos = new ByteArrayOutputStream ();
ObjectOutputStream oos = new ObjectOutputStream (bos);
oos.writeObject (object); oos.flush ();
bos.close ();
byte [] byteArray = bos.toByteArray ();

pstmt.setBytes (2, byteArray);

Okay, I get in now

Thanks!

 

[steve]

thanks for your reply and intrest

the array is a boolean array which contain data of about 53000 elements
all of these elements of the 4 dimention array reprisents one thing,
and i have to store and retrieve more than 200,000 of those things <--
hope my way of explaination is cleare

well that why i think it should be in the database but if it should be
a BLOB or not and how to be able to converte it to BLOB
i hope you and the others here are going to help me by telling me what
i should do

thanks in advance

how many will you have in memory at one time?
or is it for some sort of long term processing, and is speed needed for the
application.

Steve

 

[ali]

Hi

Rhino
you are right that how the database will be

about which dbms i am going to use it is still open choice for me
(still my superviser would like to have MS Access ) but i dont think MS
Access can handle this

so what is the best DBMS to use

steve
yes it is a long time processing thing

 

[Rhino]

Hi

Rhino
you are right that how the database will be

about which dbms i am going to use it is still open choice for me
(still my superviser would like to have MS Access ) but i dont think MS
Access can handle this

I'm not 100% sure if MS Access can handle this. I haven't used Access a lot
but I just looked at the datatypes described in the Help and there are three
possible datatypes that _MIGHT_ work for your situation:
- You could use Memo if you converted your boolean array into a text string.
Then, instead of storing actual booleans, you could store String
representations of booleans, like TFTF for true-false-true-false. You said
that your array will never have more than 53000 values in it so each boolean
could be turned into a single character representing true or false and still
fit in a Memo field. But you'd have to convert the array into a long string
before inserting it into the table and you'd have to convert it back to a
boolean array when you fetch it from the database. If each dimension of your
array always had the exact same number of entries, this might be fairly easy
but, if not, you might find that it was complicated or impossible to convert
your String back to an array reliably.
- You might be able to put the whole array in an OLE Object, assuming that
the object was less than 1 GB in size. I'd be very surprised if your array
was anywhere near that big! I've never worked with OLE Objects in any
language though so I'm not sure what sort of code you'd have to write.
- You could use a Hyperlink and then store something similar to a URL in the
database. The URL would point to a file that contained some sort of
representation of your array. I'm not sure if you'd find this easier or more
complex though! It's easier in some ways - your database only needs a small
field for the URL instead of a big field for the array - but you also have
to write your array to a file and read it from the file again to get the
data back. Again, I've never really worked with hyperlinks in Access so I'm
not sure how ugly this would be.

But I don't guarantee that _any_ of these datatypes would work for your
data.

so what is the best DBMS to use

That's an _excellent_ question but I'm not sure I can answer it. I don't
know all of the different databases. I've used DB2 a lot and MySQL quite a
bit. I've also dabbled with Access a little. But I've never used Oracle or
Sybase or Informix or Cloudbase or several of the other databases at all.

My main database is DB2 and I spent a bit of time yesterday trying to see if
it could do the job but I'm not sure yet. There are a few ways that _might_
work but I don't know yet if they will definitely work. I _THINK_ that I
should be able to get it to work if I can convert the boolean array to a
byte array: then I'll probably be able to store it in a BLOB. It might even
be possible to store the array directly as an Object, which would be my
preference. But I need to do a bit more research before I can answer that
for sure.

Since this is an interesting issue that I've been curious about for some
time, let me do a bit of research and see if I can suggest some answers. I'm
not sure how long it will take to find out what I want to know so check back
every few hours.

One other thing: what operating system(s) will be used for this system? Most
databases are limited to only some of the operating systems that are
available.

 

[ali]

thanks alot Rhino for your help you were really really so much helpful

thanks alot for suggesting all of these options well

One other thing: what operating system(s) will be used for this system? Most
databases are limited to only some of the operating systems that are
available.

we are using Windows XP

for the options you suggested about MS Access well ok they do not fet
our needs because
1- saving them in the file will consume more time for getting them back
specially when we will develop a searching program that will search
them from the database
2- converting them to string of TFTF will need a convertion function
while saving the array object to the database which is ok but while
searching the database we will have to get each string of TFTF and
convert it back to object of 4 dimention boolean array which will be
overhead on the search

3- OLE may be usefull but still dont know any one have tried it before
so prefere not to use it



any how we have to submit a running programe the do this and save it in
any kind of dbms by saturday
since i just have experiance in MS Access , Oracle and MYSQL
so i think i will download MYSQL dbms and its driver and try this thing



thanks a lot for your help i just dont know how to thank you enough

 

[Rhino]

thanks alot Rhino for your help you were really really so much helpful

thanks alot for suggesting all of these options well


we are using Windows XP

for the options you suggested about MS Access well ok they do not fet
our needs because
1- saving them in the file will consume more time for getting them back
specially when we will develop a searching program that will search
them from the database
2- converting them to string of TFTF will need a convertion function
while saving the array object to the database which is ok but while
searching the database we will have to get each string of TFTF and
convert it back to object of 4 dimention boolean array which will be
overhead on the search

3- OLE may be usefull but still dont know any one have tried it before
so prefere not to use it

Yes, I understand your concerns about each of these possible approaches to
storing the data in Access.

I've done a little more research on this issue and have some further
information for you.

I posted to comp.lang.java.databases to see if there is ANY database that
will let you store your 4 dimensional boolean array in it. No one was able
to suggest any such database. Now, that doesn't mean that a database with
this capability doesn't exist! Perhaps several databases can do what you
want but no one who is familiar with those databases saw the question. Or
maybe those people were too busy to answer the question. So I can't say with
any certainty that there is no database that can directly store an array.

I also did some searches in the DB2 manuals since I am primarily a DB2 user.
I found some information but it wasn't very clear so I posted to
comp.databases.ibm-db2 to get clarifications. I was told there by one of the
people from the IBM lab where they develop DB2 (for Windows/Unix/Linux) that
DB2 Version 8 does not support direct storage of arrays. Also, it will not
support direct storage of arrays in Version 9, which will be out soon. They
suggested that best approach was to convert the boolean array into a byte
array; then it could be stored in a BLOB datatype (a VARCHAR FOR BIT DATA
would also be possible if the array were smaller than it is). Converting the
data to a byte array is a very standard approach and has been used for
several years for many kinds of data. For example, if you wanted to store a
JPEG in a DB2 database, you would first convert it to a byte array and then
store the byte array in a BLOB datatype.

In the case of MySQL, I think you will need to convert your boolean array to
a ByteArrayInputStream, then use setByteStream() to store the
ByteArrayInputStream in a BLOB datatype. When you retrieve the data later,
you will need to use getBlob() on the result set for your BLOB column. Then
you will need to convert the blob value back to a boolean array; I think you
will probably need to convert the blob to a byte array first, then convert
the byte array to a boolean array. Please note that I have never tried
exactly what you are doing and my suggestion is only that: I _think_ it is
the right approach but it may not work. You may have to modify my suggestion
a bit and use different intermediate datatypes to accomplish your goal.

I _have_ stored and retrieved JPEGs and audio files in MySQL with code
similar to what I suggested for your problem so if it would help you, I
could post some fragments of that code. But my code isn't converting a
boolean array to a byte array input stream or a byte array to a boolean
array so I suggest you do some Google Group searches to see if you can find
some existing examples of the techniques you'll need.

any how we have to submit a running programe the do this and save it in
any kind of dbms by saturday
since i just have experiance in MS Access , Oracle and MYSQL
so i think i will download MYSQL dbms and its driver and try this thing



thanks a lot for your help i just dont know how to thank you enough

You're very welcome! Good luck with your project!

 

[ali]

Hi
Rhino

well i have done a test programe to do what i need in my project and it
worked very well thanks to god and to you and the others who helped me
in this post

if you are intrested of the code or for any one who may have the same
problem in the future here is the code to insert a 2 dimention boolean
array to blob in mysql

boolean barray[][]={{true,false,true},{false,true,true,true}};
Class.forName("com.mysql.jdbc.Driver");

con=
DriverManager.getConnection("jdbc:mysql://localhost/db1?user=root&password=899160");
stp= con.prepareStatement("insert into img values (?,?,?)");
stp.setInt(1,20);
stp.setString(2,"Hassan");
ByteArrayOutputStream bos = new ByteArrayOutputStream ();
ObjectOutputStream oos = new ObjectOutputStream (bos);
oos.writeObject (barray);
oos.flush ();
bos.close ();
byte [] byteArray = bos.toByteArray ();

System.out.println(byteArray.length);

stp.setBytes(3,byteArray);

stp.execute();

=======================================
and here is how to read it

Class.forName("com.mysql.jdbc.Driver");

con=
DriverManager.getConnection("jdbc:mysql://localhost/db1?user=root&password=899160");
st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs = st.executeQuery("SELECT * FROM img");

rs.next();

System.out.println(rs.getInt(1));
System.out.println(rs.getString(2));

boolean result[][];


byte a[] = rs.getBytes(3);



ByteArrayInputStream bis = new ByteArrayInputStream(a);
ObjectInputStream ois = new ObjectInputStream(bis);
result=(boolean[][])ois.readObject();

=======================================================

well i think my next task will be to get the speed or the needed space
to be reduced but that will be another problem that i have to deal with
on my own

well thank thanks thanks very much for your greate help

good bye and tack care

 

[Rhino]

Hi
Rhino

well i have done a test programe to do what i need in my project and it
worked very well thanks to god and to you and the others who helped me
in this post

if you are intrested of the code or for any one who may have the same
problem in the future here is the code to insert a 2 dimention boolean
array to blob in mysql

boolean barray[][]={{true,false,true},{false,true,true,true}};
Class.forName("com.mysql.jdbc.Driver");

con=
DriverManager.getConnection("jdbc:mysql://localhost/db1?user=root&password=899160");
stp= con.prepareStatement("insert into img values (?,?,?)");
stp.setInt(1,20);
stp.setString(2,"Hassan");
ByteArrayOutputStream bos = new ByteArrayOutputStream ();
ObjectOutputStream oos = new ObjectOutputStream (bos);
oos.writeObject (barray);
oos.flush ();
bos.close ();
byte [] byteArray = bos.toByteArray ();

System.out.println(byteArray.length);

stp.setBytes(3,byteArray);

stp.execute();

=======================================
and here is how to read it

Class.forName("com.mysql.jdbc.Driver");

con=
DriverManager.getConnection("jdbc:mysql://localhost/db1?user=root&password=899160");
st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs = st.executeQuery("SELECT * FROM img");

rs.next();

System.out.println(rs.getInt(1));
System.out.println(rs.getString(2));

boolean result[][];


byte a[] = rs.getBytes(3);



ByteArrayInputStream bis = new ByteArrayInputStream(a);
ObjectInputStream ois = new ObjectInputStream(bis);
result=(boolean[][])ois.readObject();

=======================================================

well i think my next task will be to get the speed or the needed space
to be reduced but that will be another problem that i have to deal with
on my own

well thank thanks thanks very much for your greate help

good bye and tack care

I'm glad to hear that your program is doing what is required now!

It was a pleasure to help