How to insert a file in oracle blob object

[vinoth prabakaran]

Hi,
can any body help me to insert a file into oracle table containing blob object. The file preferrably msword document.

i tried a program , but it is getting an error
The program i tried is
import java.io.*;
import java.sql.*;
public class InsertFile
{
public static void main(String args[])
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc dbc racledsn","scott","tiger");
PreparedStatement pstmt=con.prepareStatement("insert into myform (id,name,resume) values (?,?,?)");
File file=new File(args[0]);
InputStream is=new FileInputStream(args[0]);
pstmt.setBinaryStream(3,is,(int)file.length());
pstmt.setString(1,"abcde12345");
pstmt.setString(2,"mydoc");
System.out.println("abcde12345"+"mydoc"+args[0]);
if(pstmt.execute())
System.out.println("Inserted the record");
else
System.out.println("Inserted the record");
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
for msword file i am getting an error
java.sql.SQLException: [Oracle][ODBC][Ora]ORA-24307: invalid length for piece
for other file like txt,xml,jsp
java.sql.SQLException: [Oracle][ODBC]No data at execution values pending.
Thanks in advance
bye
Vinoth

[vinoth prabakaran]

Hi,
i found the solution to insert and retrive file using blob object of oracle.
Table Description
Name Null? Type
----------------------------------------- -------- ----------------------------
FILEID NOT NULL VARCHAR2(255)
NAME NOT NULL VARCHAR2(255)
RESUME NOT NULL BLOB
here is the code
(InsertFile.java)
import java.io.*;
import java.sql.*;
public class InsertFile
{
public static void main(String args[])
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc dbc racledsn","scott","tiger");
Statement st=con.createStatement();
String initstr="insert into myform values ('"+args[0]+"','"+args[1]+"',EMPTY_BLOB())";
st.execute(initstr);
st.close();
con.commit();
PreparedStatement pstmt=con.prepareStatement("update myform set resume = (?) where fileid ='"+args[1]+"'");
File file=new File(args[2]);
InputStream is=new FileInputStream(args[2]);
pstmt.setAsciiStream(1,is,(int)file.length());
System.out.println("Length of the file "+file.length());
System.out.println("Length of the file "+(int)file.length());
if(pstmt.execute())
System.out.println("Inserted the record");
else
System.out.println("Inserted the record");
con.commit();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}

(Retrieve.java)
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*; //for oracle.sql.BLOB

public class Retrieve
{
public static void main(String args[])
{
try
{
DriverManager.registerDriver((new oracle.jdbc.driver.OracleDriver()));
Connection cn =DriverManager.getConnection("jdbc racle:thin:@192.168.3.2:1521:blue","scott", "tiger");
InputStream is=null;
oracle.sql.BLOB blob=null;
OutputStream os=new FileOutputStream(args[1]);
Statement st = cn.createStatement();
ResultSet rs = st.executeQuery("Select resume from myform"+" where name='"+args[0]+"'");

while (rs.next()){
blob=((OracleResultSet)rs).getBLOB(1);
is=blob.getBinaryStream();
}
int pos=0;
int length=0;
byte[] b = new byte[blob.getChunkSize()];
while((length=is.read(b))!= -1){
pos+=length;
os.write(b);
}
is.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
Bye
Vinoth