save image into database blob

[Honour Cooker]

Hi everyone,

I have a upload.jsp page which saves image into database blob. It seems working "fine" (no error message) when I click upload button after choosing a file from the disk. But I could not retrieve the image saved in the database. I suspect that I did not save the image correctly into database. The following is the jsp file saving image into database. Please help me!
-------------------------------------------------------
<!-- upload.jsp -->
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>

<%
String contentType = request.getContentType();
if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)) {

DataInputStream in = new DataInputStream(request.getInputStream());
int formDataLength = request.getContentLength();

byte dataBytes[] = new byte[formDataLength];
int byteRead = 0;
int totalBytesRead = 0;
while (totalBytesRead < formDataLength) {
byteRead = in.read(dataBytes, totalBytesRead, formDataLength);
totalBytesRead += byteRead;
}

try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
out.println("Driver Loaded!");
} catch (Exception E) {
out.println("Unable to load driver.");
}

try {
String connectionURL = "jdbc:mysql://localhost:3306/meg?user=;password=";
Connection C = DriverManager.getConnection(connectionURL,"","");
C.setAutoCommit(false);

String insert = "insert into tblContent (content) values (?)";

PreparedStatement pstmt = C.prepareStatement(insert);
pstmt.setObject(1, dataBytes);
pstmt.executeUpdate();

pstmt.close();
C.close();
out.println("Inserted row " );

} catch (SQLException E) {
out.print( "SQLException: " + E.getMessage());
} catch (Exception E) {
out.print("Error " + E.toString());
}



}
%>
-----------------------------------------------------
Thanks!

[Bear Bibeault]

But I could not retrieve the image saved in the database.

Some detail on what this means would be helpful.

[Honour Cooker]

Thanks for replying.

What I mean was the image data seemed saved in the database as I could see the binary data in the table field via phpAdmin. But I tried to implement a servlet to save the binary data to an image file, this file wont be opened by photoshop. So the data might not be save into database correctly.

[satish sathineni]

hi,

I think the basic problem is the way u save image object into the mysql database...
Becoz i tried the same with oracle database i was able to do it...

One thing u r missing is BLOB term which should be used to store the images in the database...

clarify u r self regarding BLOB in MYSQL then i think that should solve u r problem


satish.....

[Bear Bibeault]

satish,

JavaRanch is a community of people from all over the world, many of who are not native English speakers. While using abbreviations like "u" instead of spelling out "you" is convenient when text messaging your friends on a cell phone or in a chat room, it presents an extra challenge to those that are already struggling with English. Additionally, such shortcuts may confound automated translation tools that patrons of the Ranch may be making use of.

I would like to ask for your help in making the content of JavaRanch a little easier to read for everybody that visits here by not using such abbreviations.

thanks,
bear
JSP Forum Bartender

[JulianInactive KennedyInactive]

Hi Honour,

Have you tried usin pstmt.setBytes(1, dataBytes); instead of pstmt.setObject(1, dataBytes);?

If that doesn't work and/or you decide to go the setBlob() route then I'd be interested to see how you create the Blob. If it's not too much trouble, please post your solution when you reach it.

Jules

[Honour Cooker]

Hi Julian,

I have changed the code as below and it worked!!!
-------------------------- saving image to database -----------------------
private void savePayload(String filename,BufferedInputStream is) throws java.io.IOException{
int c;
PushbackInputStream input = new PushbackInputStream(is,128);
ByteArrayOutputStream blob = new ByteArrayOutputStream();
while ( (c=read(input,boundary)) >= 0 )blob.write( c );
saveBlob(filename,blob.toByteArray());
blob.close();
}

private void saveBlob(String filename,byte[] out){
Connection con = null;
PreparedStatement pstmt = null;
String sqlCmd = "INSERT INTO tblContent (FileName,BinaryData) VALUES(?,?)";
try {
Class.forName(jdbcDriver);
con = DriverManager.getConnection(dbUrl);

pstmt = con.prepareStatement(sqlCmd);
pstmt.setString(1, filename);
pstmt.setBytes(2, out);
pstmt.executeUpdate();

con.close();
}
catch(ClassNotFoundException e){
e.printStackTrace();
}
catch(SQLException e){
e.printStackTrace();
}
}

--------------------retrieving from database ---------------------------
public byte[] getBlob(int id){
String sqlQuery = "SELECT BINARYDATA FROM tblContent WHERE ContentID = ?;";
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Blob blob = null;
byte[] bytes = null;
String description = "";
try {
Class.forName(jdbcDriver);
con = DriverManager.getConnection(dbUrl);
pstmt = con.prepareStatement(sqlQuery);
pstmt.setInt(1,id);

rs = pstmt.executeQuery();
ResultSetMetaData md = rs.getMetaData();
while (rs.next()) {
blob = rs.getBlob(1);
}
bytes = blob.getBytes( 1, (int)(blob.length()));
con.close();
}
catch(ClassNotFoundException e){
e.printStackTrace();
}
catch(SQLException e){
e.printStackTrace();
}
return bytes;
}
-----------------------------------------------------------------
You are right. I did not save image properly. Thank you very much!

Honour

[Nikka Missia]

hi..
I search for single image uploading but i found out multiple image uploading which is working nice
the site is also providing fully working code to download with jars.

click here to go to link
they are using struts2 and JDBC

very helpful