aspose file tools*
The moose likes JDBC and the fly likes store and retreive the image from oracle Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "store and retreive the image from oracle" Watch "store and retreive the image from oracle" New topic
Author

store and retreive the image from oracle

shahul hameed
Greenhorn

Joined: Jul 14, 2007
Posts: 9
Hi,
When i try to retreive the image from oracle, i am facing one problem.
I retreive the image from oracle and stored in xyz.gif. When i click this image, its telling that cannot determine. Can anyone help me, how to retreive the image from oracle. I have attached the coding, could you tell me what is the mistake in this coding?

Thanks

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Blob;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.*;

class MySqlInsertBlob {
FileOutputStream image;
Connection conn = null;
PreparedStatement pstmt = null;
Statement stmt= null;
ResultSet res = null;
StringBuffer query=null;
String filename ="c:/backblue.gif";

public MySqlInsertBlob(){

try{

query=new StringBuffer("insert into blobs(filename,binarydata,name) values (?,?,?)");
File file= new File(filename);

Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql","root","raghu123");
stmt=conn.createStatement();
pstmt=conn.prepareStatement(query.toString());
pstmt.setString(1, filename);
pstmt.setBinaryStream(2, new FileInputStream(filename),(int)file.length());
pstmt.setString(3,"silviya");
pstmt.executeUpdate();
System.out.println("Successfully inserted into BLOBS .....");

ResultSet rs=stmt.executeQuery("select * from blobs where name='silviya'");
if (rs.next()) {
Blob test=rs.getBlob("binarydata");
InputStream x=test.getBinaryStream();
int size=x.available();
OutputStream out=new FileOutputStream("c:/xyz.gif");
byte b[]= new byte[size];
x.read(b);
out.write(b);


}
}catch(ClassNotFoundException cnfe){

System.out.println("ClassNotFoundException occured :"+cnfe);
}catch(SQLException se){

System.out.println("SQLException occured :"+se);
}catch(FileNotFoundException fe){

System.out.println("File Not Found Exception occured :"+fe);
}catch(Exception e){

System.out.println("Exception occured :"+e);
}finally{
try{
stmt.close();
conn.close();
}catch(Exception e){}
}
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8895
    
    8

You should start with our FAQ entry AvailableDoesntDoWhatYouThinkItDoes


"blabbing like a narcissistic fool with a superiority complex" ~ N.A.
[How To Ask Questions On JavaRanch]
shahul hameed
Greenhorn

Joined: Jul 14, 2007
Posts: 9
Hi,

Thanks for your reply. Now i can able to insert and retreive a image in oracle through jdbc only if the image size less than 4000 bytes. When i try to store the image size is more than 4000 bytes, i cant able to store the image. It throws one exception like ORA-01460: unimplemented or unreasonable conversion requested. Could you check and correct the code that i have attached at below please.

String filename = "d:/shanarn.gif";
File file= new File(filename);
pstmt=con.prepareStatement("insert into picture(id,picture1) values(?,?)");
pstmt.setString(1,"image1");
pstmt.setBinaryStream(2,new FileInputStream(filename),(int)file.length());
pstmt.executeUpdate();


Thank you
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8895
    
    8

Sounds like a database problem. How is the field declared?
shahul hameed
Greenhorn

Joined: Jul 14, 2007
Posts: 9
Hi

I have declared the field as BLOB data type. Herewith i have attached script for that table. Could you help me please.


CREATE TABLE PICTURE
(
ID VARCHAR2(25 BYTE),
PICTURE1 BLOB
)
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8895
    
    8

I did a search for that error and it sounds like a character conversion problem. Are you certain the ID is small enough to fit and of the same charset as the database?
I'm going to move this to our JDBC forum to see if they have any input.
shahul hameed
Greenhorn

Joined: Jul 14, 2007
Posts: 9
Hi,

When i try to store the image, iam getting one exception like
java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested. In the table, i declared the image field as BLOB data type.
Herewith i have attached my code snippet. just go through this code and say me what is the problem. Iam working in windows - oracle client. but the oracle server in linux. Admin has set the NLS_LANG also. He set the NLS_LANG like AMERICAN_AMERICA.ALUTF8 -something. Database admin is telling that some content is missing in database. I dont know what the content is missing. What i need to check. i raised this question before, they informed, the problem is in database conversion. Could you help me please.


String filename = "d:/commercial.gif";
if (!filename.equals("")) {
PreparedStatement ps = con.prepareStatement("insert into picture (id,picture1) values (?,?)");
ps.setString(1, "imageId");
FileInputStream fis = new FileInputStream(filename);
ps.setBinaryStream(2, fis, fis.available());
ps.execute();
ps.close();
} else {
PreparedStatement ps = con.prepareStatement("insert into picture (id,picture1) values (?, empty_blob())");
ps.setString(1, "imageId");
ps.execute();
ps.close();
}



thanks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: store and retreive the image from oracle