Thread: blob load errors
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
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
>
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)
>
>
"jonerf1" <jonesrf1@qwest.net> writes:
> 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"
It'd appear to me that the problem is the lack of a column name list
in this version of the INSERT command. The command is evidently
attempting to insert 'doss' into some integer column. The version
that you said worked called out the two column names explicitly
(and, I infer, skipped at least one integer column).
You should be trying to figure out why the column name list is
missing.
regards, tom lane
Tom Lane wrote:
>"jonerf1" <jonesrf1@qwest.net> writes:
>
>>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"
>>
>
>It'd appear to me that the problem is the lack of a column name list
>in this version of the INSERT command. The command is evidently
>attempting to insert 'doss' into some integer column. The version
>that you said worked called out the two column names explicitly
>(and, I infer, skipped at least one integer column).
>
>You should be trying to figure out why the column name list is
>missing.
>
> regards, tom lane
>
>
Thanks for your reply. The following statement fails as described. It
works on my machine and doesnt work on his. Doss is the file name; it
is in the right place, a text field; the next '\\377wpc is data from
the file which should be an oid processed as a stream.
DEBUG: query: INSERT INTO images(imgname, imgoid)
VALUES('doss','\\377WPC^\\012\\000\\000\
The following line show a successful upload; the second value is the oid
of the image loaded to postgresql
DEBUG: query: INSERT INTO images(imgname, imgoid) VALUES('cap.sxw',16676)
The other machine can load strings into pgsql, just not bytea blobs.
TIA
rfjones
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
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
>