Thread: JDBC PostgreSQhelL

I'm a getting a bit flustered with my old friend Postgresql.  Everything
was fine and dandy until I decided I needed to use large objects.

At first, after following the documentation, I was getting InputStream not
supported as a parameter.  I started looking around and found some things
in the archives that said streams aren't supported until version 7.1. (the
fact that the documentation gives an example using streams to use large
objects going at least back to 6.5 is lame, why not just add a note that
this isn't implemented?  Wouldn't that save the most headaches?) Ok, no
problem, I'm running 7.0, I can upgrade to 7.1.  After wrestling the RPMs
into submission, finally got 7.1.3 up and running.  Ok, need new drivers
now, ok, no problems, add them to the classpath and. . . now I'm getting
some new funk:

java.sql.SQLException: ERROR:  oidin: error in ". . . a big long string
representing the file. . . ": can't parse " . . .same big long string,
again. . ."

at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:88)
at org.postgresql.Connection.ExecSQL(Connection.java:356)
. . .

Here is the gist of the code (should look pretty familiar, it's straight
out of the documentation):

File file = new File("mytest.blob");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps = con.prepareStatement("insert into blobtest
values (?,?)");
ps.setString(1, file.getName());
ps.setUnicodeStream(2, fis, (int)file.length());
ps.executeUpdate();


Can I get a little direction?  Any idea why I'm getting this error or how
to make it go away?

I would appreciate if anyone could supply an example or two of working
code, inserting and recalling files from the db using JDBC.  I would
prefer to avoid the LargeObjectManager if possible, but if any one would
care to supply an example of code doing the same thing the
org.postgresql.largeobject package, I'd like to look through that also.

Also, Is there a javadoc of all the packages in the .jar file for
postgres?

Sincerely,

Andrew

The problem is that you are using current development code with
instructions that are written for 7.1.

You can either get the 7.1 jdbc drivers which should work according to
the documentation you are using, or you can use the 'compatible'
parameter to set the behavior in the 7.2 driver back to what it was in 7.1.

I haven't yet updated the docs for the changes put in place in the 7.2 code.


thanks,
--Barry



acshafer@moon.hec.utah.edu wrote:

>
> I'm a getting a bit flustered with my old friend Postgresql.  Everything
> was fine and dandy until I decided I needed to use large objects.
>
> At first, after following the documentation, I was getting InputStream not
> supported as a parameter.  I started looking around and found some things
> in the archives that said streams aren't supported until version 7.1. (the
> fact that the documentation gives an example using streams to use large
> objects going at least back to 6.5 is lame, why not just add a note that
> this isn't implemented?  Wouldn't that save the most headaches?) Ok, no
> problem, I'm running 7.0, I can upgrade to 7.1.  After wrestling the RPMs
> into submission, finally got 7.1.3 up and running.  Ok, need new drivers
> now, ok, no problems, add them to the classpath and. . . now I'm getting
> some new funk:
>
> java.sql.SQLException: ERROR:  oidin: error in ". . . a big long string
> representing the file. . . ": can't parse " . . .same big long string,
> again. . ."
>
> at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:88)
> at org.postgresql.Connection.ExecSQL(Connection.java:356)
> . . .
>
> Here is the gist of the code (should look pretty familiar, it's straight
> out of the documentation):
>
> File file = new File("mytest.blob");
> FileInputStream fis = new FileInputStream(file);
> PreparedStatement ps = con.prepareStatement("insert into blobtest
> values (?,?)");
> ps.setString(1, file.getName());
> ps.setUnicodeStream(2, fis, (int)file.length());
> ps.executeUpdate();
>
>
> Can I get a little direction?  Any idea why I'm getting this error or how
> to make it go away?
>
> I would appreciate if anyone could supply an example or two of working
> code, inserting and recalling files from the db using JDBC.  I would
> prefer to avoid the LargeObjectManager if possible, but if any one would
> care to supply an example of code doing the same thing the
> org.postgresql.largeobject package, I'd like to look through that also.
>
> Also, Is there a javadoc of all the packages in the .jar file for
> postgres?
>
> Sincerely,
>
> Andrew
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

Dear Barry,

> The problem is that you are using current development code with
> instructions that are written for 7.1.

I'm a bit confused with your response, if you check the postgres
documentation, these intructions have been there for a long time.  I'm
looking 7.1.3 and the documentation for Large Ojects is the same as many
previous versions.  What exactly am I using that is "current development
code"?

> You can either get the 7.1 jdbc drivers which should work according to
> the documentation you are using, or you can use the 'compatible'
> parameter to set the behavior in the 7.2 driver back to what it was in 7.1.

Throw me a bone here, the driver I'm using is from jdbc7.1-1.3.jar, is
this a 7.2 driver?  If so, how do I go about setting the 'compatible'
parameter.  Just a line or two of code would be nice.  I don't care if I
have the latest and greatest driver, as long as I can get it to work.

> I haven't yet updated the docs for the changes put in place in the 7.2 code.

Understandable, but I'm not sure where 7.2 came into the picture.

regards,

Andrew

The error stack you had in your original email indicates you are running
a 7.2development version of the driver.  Since QueryExecutor didn't
exist in 7.1.  (Sorry I didn't explain how I came up with the conclusion
you were running 7.2development code).

 >>at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:88)
 >>at org.postgresql.Connection.ExecSQL(Connection.java:356)

Where did you get the drivers you are running.  I just verified that the
  7.1 drivers off of jdbc.postgresql.org do not contain this class.

thanks,
--Barry


acshafer@moon.hec.utah.edu wrote:

>
> Dear Barry,
>
>
>>The problem is that you are using current development code with
>>instructions that are written for 7.1.
>>
>
> I'm a bit confused with your response, if you check the postgres
> documentation, these intructions have been there for a long time.  I'm
> looking 7.1.3 and the documentation for Large Ojects is the same as many
> previous versions.  What exactly am I using that is "current development
> code"?
>
>
>>You can either get the 7.1 jdbc drivers which should work according to
>>the documentation you are using, or you can use the 'compatible'
>>parameter to set the behavior in the 7.2 driver back to what it was in 7.1.
>>
>
> Throw me a bone here, the driver I'm using is from jdbc7.1-1.3.jar, is
> this a 7.2 driver?  If so, how do I go about setting the 'compatible'
> parameter.  Just a line or two of code would be nice.  I don't care if I
> have the latest and greatest driver, as long as I can get it to work.
>
>
>>I haven't yet updated the docs for the changes put in place in the 7.2 code.
>>
>
> Understandable, but I'm not sure where 7.2 came into the picture.
>
> regards,
>
> Andrew
>
>

> The error stack you had in your original email indicates you are running
> a 7.2development version of the driver.  Since QueryExecutor didn't
> exist in 7.1.  (Sorry I didn't explain how I came up with the conclusion
> you were running 7.2development code).
>
>  >>at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:88)
>  >>at org.postgresql.Connection.ExecSQL(Connection.java:356)
>
> Where did you get the drivers you are running.  I just verified that the
>   7.1 drivers off of jdbc.postgresql.org do not contain this class.


I got the .jar file from a link supplied by Bruce in one of the pqsql-jdbc
archives.  I assumed since the jar was named 7.1-1.3 that it would be
drivers for 7.1.  Tell me what drivers you suggest and I'll try them.

I would suggest starting with the 7.1 drivers off of
jdbc.postgresql.org.  The functionality they provide for
Blobs/LargeObjects matches the documentation you are using.

You can also try the 7.2 drivers that you can also download from
jdbc.postgresql.org.  In these drivers the implementation of getBytes()
and getBinaryStream() in ResultSet and setBytes()/setBinaryStream() in
PreparedStatement is different than is documented in the 7.1
documentation.  These methods now work off of the bytea datatype for a
column instead of the oid datatype.  However the getBlob()/setBlob() and
LargeObject API methods should all work the same as they did in 7.1.

thanks,
--Barry


acshafer@moon.hec.utah.edu wrote:

>
>>The error stack you had in your original email indicates you are running
>>a 7.2development version of the driver.  Since QueryExecutor didn't
>>exist in 7.1.  (Sorry I didn't explain how I came up with the conclusion
>>you were running 7.2development code).
>>
>> >>at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:88)
>> >>at org.postgresql.Connection.ExecSQL(Connection.java:356)
>>
>>Where did you get the drivers you are running.  I just verified that the
>>  7.1 drivers off of jdbc.postgresql.org do not contain this class.
>>
>
>
> I got the .jar file from a link supplied by Bruce in one of the pqsql-jdbc
> archives.  I assumed since the jar was named 7.1-1.3 that it would be
> drivers for 7.1.  Tell me what drivers you suggest and I'll try them.
>
>
>