Store an ArrayList in MySql as a Blob

I want to store an ArrayList intact in a db field - a Blob - How do I cast an ArrayList to a Blob so I can use the PreparedStatement setBlob. If there is a better way I'm open to that too.
mmouerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

objectsCommented:
you need to serialize the list using ObjectOutputStream
0
objectsCommented:
ByteArrayOuputStream bout = new ByteArrayOutputStream();
ObjectOutputStream oout = new ObjectOutputStream(bout);
oout.writeObject(list);
oout.close();
InputStream in = new ByteArrayInputStream(bout.toByteArray());
// save your stream to blob field
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

CEHJCommented:
You need to use a PreparedStatement with a binary column:

ByteArrayOutputStream baos = new ByteArrayOutputStream();
ObjectOutputStream out = new ObjectOutputStream(baos);
out.writeObject(yourArrayList);
out.close();
PreparedStatement ps = conn.prepareStatement("INSERT INTO sometable (someblob) VALUES (?)");
byte[] bytes = baos.toByteArray();
ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
ps.setBinaryStream(1, bais, bytes.length);
0
objectsCommented:
> You need to use a PreparedStatement with a binary column:

Thats already been posted.
0
mmouerAuthor Commented:
Thanks to all
0
objectsCommented:
Can you please explain why you accepted that answer, it merely duplicates what was already stated.
0
CEHJCommented:
>>it merely duplicates what was already stated

Where?
0
objectsCommented:
I'm not going to argue with you about it, I'll get a mod to sort it out.
0
objectsCommented:
mmouer,

Can we get some feedback from you please
0
CEHJCommented:
Seems pretty clear to me:

a. petmagdy's is a good example, but unfortunately of doing something different
b. your example is OK, but leaves too much out

0
objectsCommented:
No ones asking you :)
0
CEHJCommented:
8-)
0
petmagdyCommented:
sorry just a little Q CEHJ.

what is 8-) ? a laught or showing teath?

 ;-)
0
CEHJCommented:
LOL - a thank you!
0
mmouerAuthor Commented:
Using the answer given by Objects an error was generated.
The answer given by CEHJ worked.
That's the only reason I awarded the points the way I did.

Thanks,

Michael
0
objectsCommented:
> Using the answer given by Objects an error was generated.

Then you should post the error so I could help you fix it, otherwise it is hard for me to help you.
The code from CEHJ uses the same code as I posted anyways.
0
mmouerAuthor Commented:
No it's not the same code. You use InputStream. CEHJ used ByteArrayInputStream.
I didn't post the error because CEHJ had already posted his response.
Anyway, I apologize for creating a brouhaha and I appreciate all who commeted.
It's still not working for while CEHJ's solution compiled I get a MySQL syntax error.
0
objectsCommented:
> You use InputStream. CEHJ used ByteArrayInputStream.

No I use a ByteArrayInputStream. The type of the var is irrelevant (InputStream is a superclass of ByteArrayInputStream)

InputStream in = new ByteArrayInputStream(bout.toByteArray());

0
objectsCommented:
> It's still not working for while CEHJ's solution compiled

code i posted also compiled :)
0
mmouerAuthor Commented:
Be that as it may your code generated the following and wouldn't compile:
C:\JavaProjects\ColorDifferences\src\com\sherwin\color\colordifferences\IlluminateObserverMySQL.java:88: setBlob(int,java.sql.Blob) in java.sql.PreparedStatement cannot be applied to (java.io.InputStream)
The reason I' m asking an expert is because I'm new at Java - so cut me some slack - I Just  need help not a cat fight.

0
objectsCommented:
the code I posted didn't have a call to setBlob() nor did I suggest using it. I didn't post how to use the stream because petmagdy already had so didn't want to repeat his/her comment.
CEHJ just repeated what had already been posted.

0
CEHJCommented:
>>It's still not working for while CEHJ's solution compiled I get a MySQL syntax error.

Let me know and i'll see if i can help
0
mmouerAuthor Commented:
Venabili,

Yes, please.

objects,

Sometimes it's better to have the whole thing explained in one note otherwise a newbie may not be able to put it together.
Once again, I apologize for stepping on your toes.

CEHJ,

My code - assume all variables and objects are defined:
class to make a stream:
 public boolean buildStream ()
    {
        try
        {
            oout = new ObjectOutputStream (baos);
            oout.writeObject (inList);
            oout.close ();
            bytes = baos.toByteArray ();
            bais = new ByteArrayInputStream (bytes);
        }

main class:
public void setSQL ()
    {
        try
        {
            ByteArrayInputStreamBuilder blobBuilder = new ByteArrayInputStreamBuilder ();
           
            sqlInsertNew.setString (1, Illuminant);
            sqlInsertNew.setInt (2, Observer);
           
            blobBuilder.setInList (xBarList);
            if (blobBuilder.buildStream () == false)
            {
                System.out.println ("Error returned by ByteArrayInputStreamBuilder.buildStream");
            }
            sqlInsertNew.setBinaryStream (3, blobBuilder.getBais (), blobBuilder.getBytesLength ());
           
            blobBuilder.setInList (yBarList);
            if (blobBuilder.buildStream () == false)
            {
                System.out.println ("Error returned by ByteArrayInputStreamBuilder.buildStream");
            }
            sqlInsertNew.setBinaryStream (4, blobBuilder.getBais (), blobBuilder.getBytesLength ());
           
            blobBuilder.setInList (zBarList);
            if (blobBuilder.buildStream () == false)
            {
                System.out.println ("Error returned by ByteArrayInputStreamBuilder.buildStream");
            }
            sqlInsertNew.setBinaryStream (5, blobBuilder.getBais (), blobBuilder.getBytesLength ());
           
            sqlInsertNew.setDouble (6, WhitePointX);
            sqlInsertNew.setDouble (7, WhitePointY);
            sqlInsertNew.setDouble (8, WhitePointZ);
        }
Error:
"You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ''A  ',10,'¬í\0sr\0java.util.ArrayListx?Ò™Ça?\0I\0sizexp\0"

SQL:
Insert Into Ill_Obs_31_Point('A  ',10,** STREAM DATA **,** STREAM DATA **,** STREAM DATA **,111.14399719238281,100.0,35.20000076293945

Thanks for your help,

Michael
0
mmouerAuthor Commented:
I didn't describe the MySQL Data Definition.

Illuminant char(3)
Observer int(11)
xBarList blob
yBarList blob
zBarList blob
WhitePoint1 double
WhitePoint2 double
WhitePoint3 double
0
VenabiliCommented:
Question reopened.

Venabili
0
CEHJCommented:
Maybe this time objects can post code that *does* work ;-)

0
mmouerAuthor Commented:
The latest was directed to you - you haven't given up have you? :-)
0
CEHJCommented:
>>Insert Into Ill_Obs_31_Point('A  ',10,** STREAM DATA **,** STREAM DATA **,** STREAM DATA **,111.14399719238281,100.0,35.20000076293945


Please post the full sql
0
mmouerAuthor Commented:
I don't understand the question - that is the full sql generated by the PreparedStatement - The code listing above shows how I build the PrrparedStatement.
0
CEHJCommented:
There's no close bracket then
0
mmouerAuthor Commented:
Insert Into Ill_Obs_31_Point('A  ',10,** STREAM DATA **,** STREAM DATA **,** STREAM DATA **,111.14399719238281,100.0,35.20000076293945)
0
CEHJCommented:
Have you tried the columns defined as binary?
0
mmouerAuthor Commented:
Isn't Blob binary?
0
objectsCommented:
> Sometimes it's better to have the whole thing explained in one note otherwise a newbie may not be able to put it together.

yes but that does not mean they deserve the points as explained by Venabili

> Once again, I apologize for stepping on your toes.

Thats ok, apology accepted :)

CEHJ > Maybe this time objects can post code that *does* work ;-)

already did and you copied it :-D
0
objectsCommented:
try changing your insert staement to include the values keyword:

Insert Into Ill_Obs_31_Point values ('A  ',10,** STREAM DATA **,** STREAM DATA **,** STREAM DATA **,111.14399719238281,100.0,35.20000076293945)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
objectsCommented:
also adviseable to explicitly include the column names

Insert Into Ill_Obs_31_Point (Illuminant, Observer, xBarList, yBarList, zBarList, WhitePoint1, WhitePoint2, WhitePoint3) values (?, ?, ?, ?, ?, ?, ?, ?)
0
CEHJCommented:
>>already did

That's what mmouer told you - maybe you don't believe him?

0
CEHJCommented:
>>also adviseable

Not 'adviseable' but essential if you're including the values keyword
0
CEHJCommented:
LOL I see my answer is now 'assisted' and the 'accepted' one is actually wrong ;-)
0
mmouerAuthor Commented:
OK. I split the points - I don't know how I missed the Values - I've only been doing SQL for longer than I care to remember.

CEHJ - You got the code right - thanks!

objects - You found the oversight - thanks.

Venabili

Please close - thanks

Michael
0
CEHJCommented:
>>I don't know how I missed the Values

Using VALUES (even when it *is* used correctly) won't make any difference, unless the full table definition differs from the one given under here

>>I didn't describe the MySQL Data Definition.


0
objectsCommented:
Thanks mmouer, let me know if you have any questions in the future :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.