Re: blob load errors - Mailing list pgsql-jdbc

Barry Lind wrote:

> I am assuming from the error message you are getting your table
> definition has imgoid as type oid and thus you are trying to use the
> large object api to store the blobs instead of storing them as type
> bytea.
>
> The 7.2 and 7.3 drivers default to assuming binary data is stored as
> bytea.  So looking at the error message you are getting and the output
> from the log it really looks like the problem is that you are not
> using the 7.1 driver but are really using a 7.2 or 7.3 driver (are you
> sure that you don't have the newer driver somewhere in your classpath?).
>
> You should also read the section in the postgres jdbc driver
> documentation on binary data support.  You can actually use the 7.2 or
> 7.3 driver to work with the oid data type, by turning on the backward
> compatibility mode.  This is documented in the documentation.
>
> thanks,
> --Barry
>
>
> jonerf1 wrote:
>
>> Thanks for your reply.
>> I have tried both the 73b1 and the devpgjdbc3 drivers; neither
>> machine works with any driver but the 7.1-1.3.jar
>> this is the setup for the prepared statement; a file name string and
>> a fileinputstream
>> this works with jdbc7.1-1.3 only, and on machine A only
>> //images has int serial in 1st fld, names in #2, oid in *3
>> pst.setString(1,fn1.getName());
>>    pst.setBinaryStream(2,fis,(int)fn1.length());
>>    pst.executeUpdate();
>>    fis.close();
>>
>> Is the pg_atoi error an indicator of a library problem on machine B?
>> is there a utility to check what pg_atoi
>> is calling so I can compare the two machines' underlying libraries?
>>
>> TIA
>> rfjones
>> Barry Lind wrote:
>>
>>> First thing I would suggest is trying the latest driver from
>>> jdbc.postgresql.org (either the latest dev build or the 7.3b1
>>> build). Also your example below is missing the code that is setting
>>> the values for the prepared statement.  So I am unsure as to what
>>> your code is trying to do.  Finally why are you running a 7.1 driver
>>> against a 7.2 database?
>>>
>>> thanks,
>>> --Barry
>>>
>>>
>>> jonerf1 wrote:
>>>
>>>> I have two machines running the same small java program; one
>>>> machine can load files as blobs,
>>>> the other gets the oidin error. This happens even when the same
>>>> postgresql server is acccessed;
>>>> the local upload works on machine A, the local upload fails on
>>>> machine B, and upload to machine Aover tcpip fails as well. Both
>>>> machines are running pgsql7.2.3, using jdbc7.1-1.3.jar, jdk1.4.0x,
>>>> kernel 2.4.18 on redhat 7.3
>>>>
>>>> this is the insert statement
>>>> try
>>>> {
>>>> //ist fld is serial id, 2nd is name, 3rd is oid
>>>> String sqlstr =  "INSERT INTO images(imgname, imgoid) VALUES(?,?)";
>>>> pst = conn.prepareStatement(sqlstr);
>>>> System.out.println("pst = "+pst);
>>>> }//end try
>>>>
>>>> below is output from posmaster on machine A from machine B
>>>>
>>>> DEBUG:  query: begin;
>>>> DEBUG:  parse tree: { QUERY :command 5  :utility ?  :resultRelation
>>>> 0 :into <> :isPortal false :isBinary false :isTemp false :hasAggs
>>>> false :hasSubLinks false :rtable <> :jointree <> :rowMarks ()
>>>> :targetList <> :groupClause <> :havingQual <> :distinctClause <>
>>>> :sortClause <> :limitOffset <> :limitCount <> :setOperations <>
>>>> :resultRelations ()}
>>>> DEBUG:  ProcessUtility: begin;
>>>> DEBUG:  CommitTransactionCommand
>>>> DEBUG:  StartTransactionCommand
>>>> DEBUG:  query: INSERT INTO images
>>>> HERE NEXT LINE SHORTENED UP
>>>> VALUES('doss','\\377WPC^\\012\\000\\000\\001\\012\\002\\001\\000\\000\\000\\002\\005\\0\\000\\324')
>>>>
>>>> ERROR:  pg_atoi: error in "doss": can't parse "doss"
>>>> DEBUG:  AbortCurrentTransaction
>>>> DEBUG:  StartTransactionCommand
>>>> DEBUG:  query: commit;begin;
>>>>
>>>> Any help greatly appreciated.
>>>> TIA
>>>> rfjones
>>>>
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 5: Have you checked our extensive FAQ?
>>>>
>>>> http://www.postgresql.org/users-lounge/docs/faq.html
>>>>
>>>
>>>
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 2: you can get off all lists at once with the unregister command
>>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>>
>>>
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/users-lounge/docs/faq.html
>>
>
>
>
>
You are absolutely right. I recreated the table as bytea; I now get an
error trying to compile the sample code below.

>CREATE TABLE images (imgname text, img bytea);
>
> To insert an image, you would use:
>
>File file = new File("myimage.gif");
>FileInputStream fis = new FileInputStream(file);
>PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES (?, ?)");
>ps.setString(1, file.getName());
>ps.setBinaryStream(2, fis, file.length());
>ps.executeUpdate();
>ps.close();
>
pgf.java:84: setBinaryStream(int,java.io.InputStream,int) in
java.sql.PreparedStatement cannot be applied to
(int,java.io.FileInputStream,long)
    pst.setBinaryStream(2, fis,fn1.length());
           ^
I had been using (2,fis,(int)fn1.length()); and it worked with the oid
now it gives me  the following error when I run the program

at pst getName pst = INSERT INTO images(imgname, imgid)
VALUES('abc-lmi.sxw',16726)
SQLException java.sql.SQLException: ERROR:  column "imgid" is of type
'bytea' but expression is of type 'integer'
    You will need to rewrite or cast the expression

How should I proceed?
TIA
rfjones