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
|
| 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
...