uploading image into oracle database through servlet

[michael dizon]

I am trying to upload an image file into an oracle database but I do not know where to begin, and then I will need to get it out!! Can someone help me out?

[Scott Duclos]

Originally posted by michael dizon:
I am trying to upload an image file into an oracle database but I do not know where to begin, and then I will need to get it out!! Can someone help me out?

Michael,
Try this out.
Create a table for your images. The actual image will be stored in a blob type field.
--- Images --------------
image_id NUMBER( 14 )
image_name VARCHAR2( 50 )
image BLOB
-------------------------
--- Code to insert image ----------
In your upload servlet grab the image and the mime type for the
uploaded image.
I will assume that the image is stored in a byte array.

import oracle.sql.*;
import oracle.jdbc.driver.*;
....

byte[] image = getImage(); //However you're getting your image.
conn.setAutoCommit( false ); //Very Important!
CallableStatement cs = conn.prepareCall (
"BEGIN " +
" insert into images ( image_id, image_name, image, " +
" ) values ( ?, ?, empty_blob() ) " +
" returning image into ?; " +
"END;"
);
cs.setInt( 1, 1 );
cs.setString( 2, "test image" );
cs.registerOutParameter( 3, OracleTypes.BLOB );
cs.executeUpdate();
BLOB blob = ((OracleCallableStatement)cs).getBLOB( 3 );
//Write the image to the blob
PrintWriter out = new PrintWriter( blob.getBinaryOutputStream() );
ByteArrayInputStream in = new ByteArrayInputStream( image );
//get the chunk size to read
int chunk = blob.getChunkSize();
char[] buffer = new char[chunk];
int len = -1;
while( (len = in.read( buffer ) ) != -1 ) {
out.write( buffer, 0, len );
}
out.close();
conn.commit();
--- Code to retrieve your image ---------
BLOB blob = null;
PreparedStatement ps = conn.prepareStatement( "select image from " +
" images where image_id = ? " );
ps.setInt( 1, 1 );
ResultSet rs = ps.executeQuery();
if( rs.next() ) {
blob = ((OracleResultSet)rs).getBLOB( 1 );
}
/*
* Whatever your mime type for the image is. Its
* probably a good idea to insert this with the image
* in the upload servlet.
*/
response.setContentType( mime_type );

int len = -1;
int chunk = blob.getChunkSize();
byte[] buffer = new byte[chunk];
InputStream is = blob.getBinaryStream();
OutputStream os = response.getOutputStream();

while( ( len = is.read( buffer ) ) != -1 )
os.write( buffer, 0, length );
os.flush();
os.close();

I would also check out the Intermedia api from Oracle. It has a few classes for inserting
images, audio and for uploading images.
http://otn.oracle.com/software/products/intermedia/htdocs/descriptions/servlets_jsp.html
I hope this helps. If something isn't clear here, feel free to email me at [e-mail].
Scott Duclos

[michael dizon]

Wow, very nice. What about MySQL?