Thread: Clob or Blob in JDBC
Greetings everyone, I have for the past week been exposed to large amounts of frustration due to the fact that I keep getting the message "not yet implemented" with what every I try in the latest Postgresql JDBC. I have read the jdbc docs at sun, and I have read all of the postgresql docs for jdbc (which even that example shows the "not yet implemented"). I have a few questions questions that I just could not answer. My questions: 1. How can I store a String into the database? (can be up to 4000 characters, so I do not want to use varchar) I have been told that if I were to take that string and convert it into a byte[] that I could store it like that. How can I convert a byte[] into a Blob or Clob? And when I do get a blob or a clob do I just use setBlob? 2. Do I have to manually set the OID number? 3. Is there a place on the internet where I can download the source for the most current jdbc? I downloaded the postgresql in Debian format so I did not get the source. I just have the source for the older versions. If someone could help me, possible show me part of some code that would allow me to store my large Strings in the database. Anything would be greatly appreciated, I am running out of options. Thank you all in advance, Matt Fair
On Mon, 17 Jul 2000, Matt Fair wrote: > > 1. How can I store a String into the database? (can be up to 4000 > characters, so I do not want to use varchar) I have been told that if > I were to take that string and convert it into a byte[] that I could > store it like that. A BLOB can be stored using the setBytes() method of PreparedStatement, it can be retrieved using the getBytes() method of ResultSet. > 2. Do I have to manually set the OID number? No, the setBytes() method will do this for you. Note, that BLOB operations must be done inside a transaction. Do NOT use the autocommit setting when manipulating BLOBS. > 3. Is there a place on the internet where I can download the source for > the most current jdbc? The most current would be in CVS. Released versions of Postgresql including the JDBC driver are available as compressed tar files. See http://www.postgresql.org (click on Software) for the links. Joachim
Thank you for your help and quick response. Matt Fair Joachim Achtzehnter wrote: > On Mon, 17 Jul 2000, Matt Fair wrote: > > > > 1. How can I store a String into the database? (can be up to 4000 > > characters, so I do not want to use varchar) I have been told that if > > I were to take that string and convert it into a byte[] that I could > > store it like that. > > A BLOB can be stored using the setBytes() method of PreparedStatement, it > can be retrieved using the getBytes() method of ResultSet. > > > 2. Do I have to manually set the OID number? > > No, the setBytes() method will do this for you. > > Note, that BLOB operations must be done inside a transaction. Do NOT use > the autocommit setting when manipulating BLOBS. > > > 3. Is there a place on the internet where I can download the source for > > the most current jdbc? > > The most current would be in CVS. Released versions of Postgresql > including the JDBC driver are available as compressed tar files. See > http://www.postgresql.org (click on Software) for the links. > > Joachim
Comments prefixed with PM:
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council
-----Original Message-----
From: Matt Fair [mailto:matt@netasol.com]
Sent: Tuesday, July 18, 2000 12:46 AM
To: pgsql-interfaces@postgresql.org
Subject: [INTERFACES] Clob or Blob in JDBC
Greetings everyone,
I have for the past week been exposed to large amounts of frustration
due to the fact that I keep getting the message "not yet implemented"
with what every I try in the latest Postgresql JDBC. I have read the
jdbc docs at sun, and I have read all of the postgresql docs for jdbc
(which even that example shows the "not yet implemented"). I have a few
questions questions that I just could not answer.
My questions:
1. How can I store a String into the database? (can be up to 4000
characters, so I do not want to use varchar)
PM: Text is an alternative to varchar.
I have been told that if I were to take that string and convert it into
a byte[] that I could store it like that. How can I convert a byte[]
into a Blob or Clob? And when I do get a blob or a clob do I just use
setBlob?
PM: You don't. You use the PreparedStatement method setBytes(), ie:
in PSQL: create table mytable (id serial,str oid);String myLongString = "....4000 bytes...";PreparedStatement ps =
conn.createPreparedStatement("insertinto
mytable (str) values (?)");ps.setBytes(1,myLongString.getBytes());ps.execute();ps.close();
2. Do I have to manually set the OID number?
PM: No, OID's are generated by the backend. When setBytes() is used it
stores the returned BLOB's oid in the given field (normally of type oid).
3. Is there a place on the internet where I can download the source for
the most current jdbc? I downloaded the postgresql in Debian format so
I did not get the source. I just have the source for the older
versions.
PM: As always, the most up to date version is in the postgresql CVS tree.
Because of my recent move (& my development machine is still not connected
up yet), I've done nothing real since 7.0.1 so the CVS is current.
If someone could help me, possible show me part of some code that would
allow me to store my large Strings in the database.
Anything would be greatly appreciated, I am running out of options.
PM: Again, the best example is the ImageViewer example application which is
included in the source.
Thank you all in advance,
Matt Fair