File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

unable to insert a photo document in to the oracle database through JDBC

 
Vinnibabu singu
Ranch Hand
Posts: 30
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi...I tried to insert a photo into the oracle 10g through JDBC
I created a table named employee with four attributes .I declared the attribute at which the photo is intended to be stored as a BLOB type.

The table is as follows employee(eno Number,ename Varchar2(20),esal Number(7,2),ephoto BLOB)

I wrote the following JDBC code to insert a photo named mahesh.gif which is in the same folder in which the program is saved.


It compiled well and when tried to run it,an exception is raised as follows
java.sql.SQLException:[Microsoft][ODBC driver for Oracle][Oracle]ORA-01460:wrong number of parameters

I could not find the problem.Please help to identify the fault?


 
Scott Selikoff
author
Saloon Keeper
Posts: 3887
15
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As a debugging step, try reducing the number of parameters to 1, aka insert a blob into a single-column table.
 
Vinnibabu singu
Ranch Hand
Posts: 30
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried it (inserting into a single column table ).Its giving the same exception
Can anyone just try to run the code in your system once and tell me whether it is raising an exception or not.
Because i have a feeling that my system never does well with images.
 
Scott Selikoff
author
Saloon Keeper
Posts: 3887
15
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When I search that error code via Google, I get the text "unimplemented or unreasonable conversion requested", which sounds more probable in this situation.

Also, it should be:
ps.setBinaryStream(4,photoStream,photoStream.available());

Although I'm not sure that would actually resolve the issue.
 
Tim McGuire
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
photo.length() should be cast to an int first. Try it. photo.length() returns a long and though there is a
getBinaryStream(int,InputStream,long) method in the java 1.6 API, there is only (int, InputStream, int) in the 1.5 API. So, it depends on the JDBC version. When I saw your question, I looked at my code that I did a while back and it came back to me.


yep. available() will work too since that returns an int.
 
Vinnibabu singu
Ranch Hand
Posts: 30
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your replys guys...

I tried converting photoStream.length() in to int by writing

ps.setBinaryStream(4,photoStream,(int)photo.length());

it gave the exception and the message
"unimplemented or unreasonable conversion requested"

and also wouldnt photo.length() return an int value?
 
Tim McGuire
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
API for java File:

http://java.sun.com/j2se/1.5.0/docs/api/java/io/File.html#length()
says:
public long length()

which version of java are you using?
which version of the JDBC Driver?

 
Scott Selikoff
author
Saloon Keeper
Posts: 3887
15
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there a reason you all are avoiding "photoStream.available()"? You're reading data from the stream so you should use a stream parameter to determine how much to read. Using the photo.length() is not valid to me since there's no guarantee photo.length() and photoStream.available() are actually the same value. Either way, its safer to use a stream parameter to read from the stream.
 
Vinnibabu singu
Ranch Hand
Posts: 30
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks alot for your answers....

@Scott selicoff

using photoStream.available() actually raised the same exception ....so i think the exception may be not due to that parameter.

@ Tim

I am using jdk 1.6 and Microsoft Odbc for oracle
 
Scott Selikoff
author
Saloon Keeper
Posts: 3887
15
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Vinnibabu singu wrote:using photoStream.available() actually raised the same exception ....so i think the exception may be not due to that parameter.


As I said in my earlier post I didn't this would actually solve the problem. Either way photoStream.available() is vastly superior since its guaranteed to be tied to the stream you are reading from, as opposed to length() which could produce runtime errors if stream and file aren't perfectly matched.

One thing I tend to do with blobs in a lot of database systems is to insert them as a 2-part process:
1) Step 1: Insert the record without the blob column data using "INSERT" command (you may need to insert a string for the blob data as a place holder so its not null)
2) Step 2: Perform an update that only updates the blob data using the "SELECT ... FOR UPDATE" command, such as "SELECT image FROM images FOR UPDATE". Then you call rs.getBlob("image") on the result and proceed to write to the object.

I find the 2-step process seems to work better in most systems, but agree its not as clear as I'd prefer. Plus, you can't mark the blob as a required field in the database else the INSERT will fail. In fact, if memory serves me correctly, the last time I had to implement such a process was on an Oracle system.
 
Tim McGuire
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott Selikoff wrote:Is there a reason you all are avoiding "photoStream.available()"? You're reading data from the stream so you should use a stream parameter to determine how much to read. Using the photo.length() is not valid to me since there's no guarantee photo.length() and photoStream.available() are actually the same value. Either way, its safer to use a stream parameter to read from the stream.


photoStream.available() seems good to me. In fact, I'm going to use it from now on.

All the examples I can find online though, use File.length() when doing an operation like this.

http://www.java2s.com/Tutorial/Java/0340__Database/UsePreparedStatementsetBinaryStream.htm

http://www.herongyang.com/jdbc/MySQL-BLOB-setBinaryStream.html

Google code search, on the other hand, shows plenty of both uses:
http://www.google.com/codesearch?hl=en&lr=&q=setBinaryStream+lang%3Ajava&sbtn=Search
http://www.google.com/codesearch?hl=en&lr=&q=setBinaryStream+available&sbtn=Search
 
Tim McGuire
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Vinnibabu singu wrote:Thanks alot for your answers....

@ Tim

I am using jdk 1.6 and Microsoft Odbc for oracle


I would try using the Oracle JDBC driver instead.

I was just reading the Microsoft documentation titled "Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider" about this and it says:
Oracle 8.x-specific data types, such as CLOB, BLOB, BFILE, NCHAR, NCLOB, and NVARCHAR2, are not supported.
 
Vinnibabu singu
Ranch Hand
Posts: 30
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually I never used Oracle driver.
I am using Oracle 11g .I created a System DSN by configuring Oracle driver.But still an exception is being raised that it could nt recoginze the driver.
Do i need to install any additional drivers to use Oracle driver?
 
Tim McGuire
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, do you have the ojdbc jar file in your project? I have ojdbc14.jar

You can get the proper jar from:

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html

can you outline the steps you took when you created a system DSN
 
Balu Sadhasivam
Ranch Hand
Posts: 874
Android Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Vinnibabu,
you can use ojdbc6.jar as you are using 1.6 an Oracle 11 g.. download from the same link that Tim pointed.
 
Vinnibabu singu
Ranch Hand
Posts: 30
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Tim
I opened the control panel-> administrative tools->system dsn->add->
gave name :Ora
and username(of the database):system
@ Balu:
I downloaded the jar file.Where should i place it?
Should i set the classpath to it?
 
Vinnibabu singu
Ranch Hand
Posts: 30
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you guys.I finally got it.Thanks for all your help..

photo.length works without type casting it into integer type.
I just used the type 4 drivers .
 
Kajal Chakole
Greenhorn
Posts: 2
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are using jdbc 4 driver to set the binary Stream then it wont allow data > 4k.
So you need to either update new oracle jdbc driver from the site or you can make little modification by inserting empty_blob() in the table and then updating the same using some Output Stream.
Here is the link which worked for me

http://www.coderanch.com/t/306282/JDBC/databases/Storing-blob-kb-oracle-db
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic