permaculture playing cards*
The moose likes JDBC and the fly likes unable to insert a photo document in to the oracle database through JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "unable to insert a photo document in to the oracle database through JDBC" Watch "unable to insert a photo document in to the oracle database through JDBC" New topic
Author

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

Vinnibabu singu
Ranch Hand

Joined: Aug 19, 2009
Posts: 30
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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

As a debugging step, try reducing the number of parameters to 1, aka insert a blob into a single-column table.


My Blog: Down Home Country Coding with Scott Selikoff
Vinnibabu singu
Ranch Hand

Joined: Aug 19, 2009
Posts: 30
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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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

Joined: Apr 30, 2003
Posts: 820

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

Joined: Aug 19, 2009
Posts: 30
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

Joined: Apr 30, 2003
Posts: 820

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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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

Joined: Aug 19, 2009
Posts: 30
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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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

Joined: Apr 30, 2003
Posts: 820

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

Joined: Apr 30, 2003
Posts: 820

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

Joined: Aug 19, 2009
Posts: 30
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

Joined: Apr 30, 2003
Posts: 820

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

Joined: Jan 01, 2009
Posts: 874

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

Joined: Aug 19, 2009
Posts: 30
@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

Joined: Aug 19, 2009
Posts: 30
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

Joined: Nov 14, 2011
Posts: 2
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


Nothing is impossible.
 
 
subject: unable to insert a photo document in to the oracle database through JDBC