Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Store byte[] array in MS Access - Help!

 
Emile Ghazzawi
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have successfully converted an image into a byte[] array which i am then trying to store in an MS Access database in an OLE Object Field.

I am using a statement.executeUpdate SQL statement to insert my data along with teh byte[] array into the database. I do not know if this is the correct way of doing it, but i cannot find any helpful examples or documentation for my problem.

It keeps returning the error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Missing ), ], or Item in query expression '[B@1667df0)'

My code is below:



Thanks in advance for any help!

[added line breaks so screen doesn't scroll right]
[ March 31, 2007: Message edited by: Jeanne Boyarsky ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Emile,
You need to use a PreparedStatement rather than a regular statement since Java and SQL won't let you write out a list of bytes.

"INSERT INTO ITEMS (valueType, valueTitle, Description, Amount, Price, ID, Picture) VALUES (?, ?, ?, ?, ?, ?, ?)"

stmt.setString(1, valueType);
// .. other sets here
stmt.setBytes(7, imageBytes);

Your driver will take care of all the conversions for you.
 
Emile Ghazzawi
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you, your solution worked perfectly!

The only problem i am having now is when i retrieve the byte array from access and pass it into an ImageIcon i get a null pointer exception. I have output the contents of the array and it has been populated with data.

Any ideas???

Thanks once again!
 
Emile Ghazzawi
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have successfully placed an image into MS Access with Jeanne's kind help, but i am having problems getting it back out again.

Is this something anybody can help me with? The code i am using is below.



Thank you in advance for your help!
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Emile,
Now you're one step closer to having a solution!

Two things to confirm:
1) Is the byte[] null before you set it? Can you build an ImageIcon out of it before it goes into the database?
2) If the byte[] null after you get it back? ImageIcon could be throwing a null pointer because the bytes are null or because they don't form a valid image.

I recommend troubleshooting this by using a simpler string. For example "hello world".getBytes(). That way you can do new String(bytes) on what you get back to make sure it is the same.
 
Emile Ghazzawi
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I can build the ImageIcon before it goes to the database and i can successfully write the image to a file from Access using a FIleOutputStream, to confirm it is stored correctly.

When i print the byte[] array it seems to be filled with random numbers so i am assuming that it contains the correct contents. This is something that really has me puzzled.

Is the code i posted the right way to go about retrieving the bytes from Access and converting them to an ImageIcon?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes. Two lines are unneeded, but don't harm anything:

long imageSize = results.getLong("ImageSize");
byte[] image = new byte[(int)imageSize];
 
Emile Ghazzawi
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jeanne, i got it sorted in the end after much tinkering around.

I'm not quite sure what was wrong or how i fixed it, but for now all that matters is it works! :-D

Thanks again for your help.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic