Sagewire Logo

INSERTINg a Clob in a portable way

9 Message(s) by 4 Author(s) originally posted in java databases


From: Thomas Kellerer Date:   Tuesday, August 14, 2007
Hello,

I'm trying to insert a values for a CLOB column in a portable way. So far using
PreparedStatement.setCharacterStream() together with a Reader to read the CLOB
contents from a local file was working quite well (with various JDBC driver s).

Now I stumbled across a bug in my code that raised it ugly head when using
Apache Derby.

When reading the contents of a file with a multi-byte encoding I can not seem to
find a way to pass the correct length of the data into the setCharacterStream()
method.

What I did so far:

File f = new File("sourcfile.txt");
Reader r = new Input StreamReader(new FileInputStream(f), "UTF-8");
preparedStatement.setCharacterStream(1, r, f.length());

which worked fine for most JDBC drivers except for Derby as Derby (rightfully)
complains that the value supplied (f.length()) doesn't match the number of
characters read from the stream.

I can not image a way where I can supply the correct length when using a Reader to
supply the Clob content. If I was able to "create" Clob instance in a generic
way, I think this could solve the problem as the Clob interface contains a
setCharacterStream() which doesn't require a length parameter.
But how'd I create the Clob instance if I do not know which driver will be
used at runtime?

Any input appreciated.

Thanks in advance
Thomas


From: joeNOSPAM Date:   Tuesday, August 14, 2007
On Aug 14, 10:29 am, Thomas Kellerer <FJIFALSDG...@xxxxxxxxxxx>
wrote in message :
Hello,
I'm trying to insert a values for a CLOB column in a portable way. So far using
PreparedStatement.setCharacterStream() together with a Reader to read the CLOB
contents from a local file was working quite well (with various JDBC drivers).
Now I stumbled across a bug in my code that raised it ugly head when using
Apache Derby.
When reading the contents of a file with a multi-byte encoding I can not seem to
find a way to pass the correct length of the data into the setCharacterStream()
method.
What I did so far:
File f = new File("sourcfile.txt");
Reader r = new InputStreamReader(new FileInputStream(f), "UTF-8");
preparedStatement.setCharacterStream(1, r, f.length());
which worked fine for most JDBC drivers except for Derby as Derby (rightfully)
complains that the value supplied (f.length()) doesn't match the number of
characters read from the stream.
I can not image a way where I can supply the correct length when using a Reader to
supply the Clob content. If I was able to "create" Clob instance in a generic
way, I think this could solve the problem as the Clob interface contains a
setCharacterStream() which doesn't require a length parameter.
But how'd I create the Clob instance if I do not know which driver will be
used at runtime?
Any input appreciated.
Thanks in advance
Thomas



Hi. How about trying setBinaryStream() and send the data as raw bytes?
Joe


From: Thomas Kellerer Date:   Tuesday, August 14, 2007
wrote in message on 14.08.2007 21:26:
What I did so far:

File f = new File("sourcfile.txt");
Reader r = new InputStreamReader(new FileInputStream(f), "UTF-8");
preparedStatement.setCharacterStream(1, r, f.length());

Hi. How about trying setBinaryStream() and send the data as raw bytes?
Joe
Hi Joe,



thanks for the answer, but this'd only work if the file's encoding was the
same as the database's encoding.

Thomas


From: joeNOSPAM Date:   Tuesday, August 14, 2007
On Aug 14, 12:39 pm, Thomas Kellerer <FJIFALSDG...@xxxxxxxxxxx>
wrote in message:
wrote in message on 14.08.2007 21:26: What I did so far:
File f = new File("sourcfile.txt");
Reader r = new InputStreamReader(new FileInputStream(f), "UTF-8");
preparedStatement.setCharacterStream(1, r, f.length());
> Hi. How about trying setBinaryStream() and send the data as raw bytes?
> Joe
Hi Joe,
thanks for the answer, but this'd only work if the file's encoding was the
same as the database's encoding.
Thomas



Well, yes, but I'd separate the issues. If you've an encoding
that needs
conversion, do it explicitly at the client , and then store and send
the resulting
data as bytes.


From: Thomas Kellerer Date:   Tuesday, August 14, 2007
wrote in message on 14.08.2007 23:40:
On Aug 14, 12:39 pm, Thomas Kellerer <FJIFALSDG...@xxxxxxxxxxx>
wrote in message:
wrote in message on 14.08.2007 21:26: What I did so far:

File f = new File("sourcfile.txt");
Reader r = new InputStreamReader(new FileInputStream(f), "UTF-8");
preparedStatement.setCharacterStream(1, r, f.length());
Hi. How about trying setBinaryStream() and send the data as raw bytes?
Joe
Hi Joe,

thanks for the answer, but this'd only work if the file's encoding was the
same as the database's encoding.

Thomas
Well, yes, but I'd separate the issues. If you've an encoding
that needs
conversion, do it explicitly at the client, and then store and send
the resulting
data as bytes.


Which is exactly what the driver should be doing in setCharacterStream() :)

The problem is, as this is a generic routine, I'd first need to find out the
encoding of the database (or the table, or the column). Not an easy task if you
do not know which DBMS and driver will be used.

But even if I could get that to work, I'd still need to encode the full
file, because the number of bytes (in the binary representation) of the target
encoding might be different than those in the original encoding.

So I guess my best bet is to test-read the file to get the real length in
characters (using e.g. BufferedReader.skip()) before calling
setCharacterStream(). It does work, but there is obviously an overhead involved
(especially on large files).

Thomas


From: Frank Meyer Date:   Wednesday, August 15, 2007
wrote in message:
File f = new File("sourcfile.txt");
Reader r = new InputStreamReader(new FileInputStream(f), "UTF-8");
preparedStatement.setCharacterStream(1, r, f.length());



Hi Thomas,

have a look at
http://JAVA.sun.com/mailers/techtips/coreJAVA/2007/tt0207.html#1
where this issue may be clarified.Frank


From: Thomas Kellerer Date:   Wednesday, August 15, 2007
wrote in message on 15.08.2007 14:04:
wrote in message:
File f = new File("sourcfile.txt");
Reader r = new InputStreamReader(new FileInputStream(f), "UTF-8");
preparedStatement.setCharacterStream(1, r, f.length());
Hi Thomas,
have a look at
http://JAVA.sun.com/mailers/techtips/coreJAVA/2007/tt0207.html#1
where this issue may be clarified.


Hi Frank

thanks for the answer, but I do not understand the relation to my question.

If I understand the article correctly, it describes how I can remove (normalize)
problematic characters from a String. But in my case I need to store the
contents of a text file un-altered into a database CLOB field . But to do that I
need to know the number of characters according to a given encoding in a file,
without reading the file into memory.
I can not seem to get the relation to normalizing a text input here...Thomas


From: Philipp Taprogge Date:   Wednesday, August 15, 2007
Hi!

Thus spake Thomas Kellerer on 08/15/2007 02:37 PM:
But in my case I need to store the contents of a text file un-altered into a database CLOB
field. But to do that I need to know the number of characters according
to a given encoding in a file, without reading the file into memory.



Hmm... I'd say, you do not want to store "text" at all. What you are trying to
do is store arbitrary data, regardless of it's encoding, in the database and
let the client worry about producing a readable representation.
I'd say that's exactly what a BLOB is for. You should store the file as binary
data, possibly detecting and storing it's encoding alongside and then when
reading it from the DB again, produce appropriate output from it.
A CLOB'd only make sense if you wanted the database to do anything "texty"
with that data. But if you can not choose and stick to a certain encoding
beforehand, that'd be extremely difficult.

Am I missing something...?

Regards,

Phil


From: Thomas Kellerer Date:   Thursday, August 16, 2007
Hello Philipp,

wrote in message:
Hi!
Thus spake Thomas Kellerer on 08/15/2007 02:37 PM:
But in my case I need to store the contents of a text file un-altered into a database CLOB
field. But to do that I need to know the number of characters according
to a given encoding in a file, without reading the file into memory.
Hmm... I'd say, you do not want to store "text" at all. What you are trying to
do is store arbitrary data, regardless of it's encoding, in the database and
let the client worry about producing a readable representation.
I'd say that's exactly what a BLOB is for. You should store the file as binary
data, possibly detecting and storing it's encoding alongside and then when
reading it from the DB again, produce appropriate output from it.
A CLOB'd only make sense if you wanted the database to do anything "texty"
with that data. But if you can not choose and stick to a certain encoding
beforehand, that'd be extremely difficult.
Am I missing something...?



Yes and no :)

This is a generic SQL GUI where I support uploading of text files into a
CLOB field (BLOBs aren't problem at all). To give you an idea, the
syntax is:

INSERT INTO some_table (col1, clob_col)
VALUES
(1, {$clobfile='test.txt' encoding='UTF-8'});

This is an "extension " so that the end-user can handle file "uploads"
from the client into LOB fields (a similar extension without encoding is
available using {$blobfile=}

So I have no control whatsoever about the data model or the JDBC driver
used in this context. As I said in my initial posting, all (major)
drivers except Derby seem to be able to handle setCharacterStream() with
a length that is bigger than the actual number of characters.

"Counting" the number of characters in the input file using a Reader
does work, and the overhead for a single INSERT is not that problematic
(not even for larger files). But when it comes to bulk uploads this
might be a problem. And as my current implementation doesn't comply
with the JDBC definition, I'm trying to find a portable and correct way
to implement it.

Regards
Thomas



Next Message: jvm crash when using ojdbc1.4


Blogs related to INSERTINg a Clob in a portable way

The official Magazine Website features news and tests.
Day care centers columbus georiga tichey stryder new release blockbusters video store stuffed cannelotny recipes german to english translation maxtor c408frpc africanbootyhunter.com hd.com stan the java man largethumb ...

breach
auto code scanner. grafton school file taxes taxes insaneplanet.com nachtbus naar ibiza stad juli een dinsdag rosa moon dance Insert vagus nerve stimulator by cyberonics to treat epilepsy adventure age pornstars using rabbit vibrators ...

Annotations: the good, the bad and the ugly
18 months ago, Java SE5 introduced a new declarative way of expressing Java metadata. It has taken a while to digest, but finally we are seeing some uses for them emerging. Some of these I like, some I don‘t, and some I think are awful. ...

ingredients in levall cough medicine methadone yel...
... bag lambskin snort xanax affect tiganello one touch backpack bb dakota and summer collection www easy way to call usa aylar pornvideo filipina webcam beauty tizanidine skelaxin interaction authentic versace handbags verification wav ...

24 ctu razr ringtone doxylamine succinate child do...
ringtone using java card image editing tips photoinpact certification coursexgxda threelac bad reviews plab 2 courseleeds funai television singer houston com travel italy wine gator hunts inventor of the outhouse bushwacker ...

versace couture bag new model thumbplay spring rin...
... krzr add wallpaper blackberry from pc les theme de portable sumsang funny tagalog ringing tones using others tradelines first us goverment building bueno of california knock off handbags runescape money torrent taking hydrocodone ...


Programming | Sports | Autos

copyright 2006
Valid XHTML 1.0 Transitional