Store byte[] array in MS Access - Help!

[Emile Ghazzawi]

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:

public String addValue(String valueType, String valueTitle, String 
description, double amount, double price, String ID, byte[] imageBytes) 
throws SQLException, ClassNotFoundException, java.rmi.RemoteException
{
database();
statement.executeUpdate("INSERT INTO ITEMS (valueType, 
valueTitle, Description, Amount, Price, ID, Picture) VALUES 
('"+valueType+"', '"+valueTitle+"', '"+description+"', "+amount+", 
"+price+", '"+ID+"', "+imageBytes+")");
 
success="Item Successfully Created!";
 
link.close();
 
return auctionSuccess;
}

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]

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]

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]

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.

public ImageIcon retrieveImage(int ChosenNum) throws SQLException, ClassNotFoundException
{
database();
 
results = statement.executeQuery("SELECT * FROM DATA WHERE ID = " + ChosenNum);
 
while (results.next())
{
long imageSize = results.getLong("ImageSize");
byte[] image = new byte[(int)imageSize];
 
image = results.getBytes("Image");
 
ImageIcon ii = new ImageIcon(image);
}
 
results.close();
statement.close();
link.close();
 
return ii;
}

Thank you in advance for your help!

[Jeanne Boyarsky]

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]

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]

Yes. Two lines are unneeded, but don't harm anything:

long imageSize = results.getLong("ImageSize");
byte[] image = new byte[(int)imageSize];

[Emile Ghazzawi]

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.