Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #30441 > unrolled thread

jdbc setBinaryStream

Back to article view | Back to comp.lang.java.programmer

Contents

  jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-29 14:45 +0000
    Re: jdbc setBinaryStream Daniele Futtorovic <da.futt.news@laposte.net> - 2014-11-29 16:57 +0100
      Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-29 16:58 +0000
        Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-29 19:39 +0000
          Re: jdbc setBinaryStream Daniele Futtorovic <da.futt.news@laposte.net> - 2014-11-30 02:37 +0100
            Re: jdbc setBinaryStream Arne Vajhøj <arne@vajhoej.dk> - 2014-11-29 21:26 -0500
              Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-30 11:12 +0000
    Re: jdbc setBinaryStream Martin Gregorie <martin@address-in-sig.invalid> - 2014-11-29 19:59 +0000
      Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-29 20:36 +0000
        Re: jdbc setBinaryStream Martin Gregorie <martin@address-in-sig.invalid> - 2014-11-30 00:07 +0000
          Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-30 14:56 +0000
            Re: jdbc setBinaryStream Martin Gregorie <martin@address-in-sig.invalid> - 2014-12-01 14:06 +0000
              Re: jdbc setBinaryStream Arne Vajhøj <arne@vajhoej.dk> - 2014-12-01 21:36 -0500
                Re: jdbc setBinaryStream Martin Gregorie <martin@address-in-sig.invalid> - 2014-12-02 12:16 +0000
              Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-12-02 17:56 +0000
                Re: jdbc setBinaryStream Martin Gregorie <martin@address-in-sig.invalid> - 2014-12-02 21:37 +0000

#30441 — jdbc setBinaryStream

What I need is like
   insert into sometable values ( 0, ? )
where the relevant column is a varlongbinary (not blob) and
it is fed from an external data source accessed through a 
custom InputStream.

I create a PreparedStatement "prep" for the query and:
   InputStream source = ... (some custom InputStream)
   prep.setBinaryStream(1, source ); // the one with no length


Problem is, it ignores my stream altogether. Eclipse breakpoints on
read methods never trigger. It just silently inserts an empty object.


My custom InputStream implements the no-arg read() and the three-arg
read(arr,off,len). The one-arg read(arr) falls back to the three-args
one by base class's implementation.

The overload of setBinaryStream without a length parameter is new
in jdbc 4.0, and I read from the docs that it should mean: get as many
bytes as possible from that stream (i.e. until EOF).
Obviously that is not always so - at least not with the informix
server I'm targetting or with the ifxjdbc.jar I got from the server's
jdbc/lib folder.

Has anyone else run into that problem and perhaps found a solution?

PS: the only relevant google hit is an unanswered stackoverflow question
  http://stackoverflow.com/questions/20471030/setbinarystream-saves-null-values
and that was even for a different db-system than the one I'm targetting.

[toc] | [next] | [standalone]

#30443

On 2014-11-29 15:45, Andreas Leitgeb wrote:
> What I need is like
>    insert into sometable values ( 0, ? )
> where the relevant column is a varlongbinary (not blob) and
> it is fed from an external data source accessed through a 
> custom InputStream.
> 
> I create a PreparedStatement "prep" for the query and:
>    InputStream source = ... (some custom InputStream)
>    prep.setBinaryStream(1, source ); // the one with no length
> 
> 
> Problem is, it ignores my stream altogether. Eclipse breakpoints on
> read methods never trigger. It just silently inserts an empty object.
> 
> 
> My custom InputStream implements the no-arg read() and the three-arg
> read(arr,off,len). The one-arg read(arr) falls back to the three-args
> one by base class's implementation.
> 
> The overload of setBinaryStream without a length parameter is new
> in jdbc 4.0, and I read from the docs that it should mean: get as many
> bytes as possible from that stream (i.e. until EOF).
> Obviously that is not always so - at least not with the informix
> server I'm targetting or with the ifxjdbc.jar I got from the server's
> jdbc/lib folder.
> 
> Has anyone else run into that problem and perhaps found a solution?
> 
> PS: the only relevant google hit is an unanswered stackoverflow question
>   http://stackoverflow.com/questions/20471030/setbinarystream-saves-null-values
> and that was even for a different db-system than the one I'm targetting.
> 

Have you made sure your InputStream is behaving as you assume it does?

[toc] | [prev] | [next] | [standalone]

#30445

Daniele Futtorovic <da.futt.news@laposte.net> wrote:
> On 2014-11-29 15:45, Andreas Leitgeb wrote:
>> What I need is like
>>    insert into sometable values ( 0, ? )
>> where the relevant column is a varlongbinary (not blob) and
>> it is fed from an external data source accessed through a 
>> custom InputStream.
>> 
>> I create a PreparedStatement "prep" for the query and:
>>    InputStream source = ... (some custom InputStream)
>>    prep.setBinaryStream(1, source ); // the one with no length
>> 
>> 
>> Problem is, it ignores my stream altogether. Eclipse breakpoints on
>> read methods never trigger. It just silently inserts an empty object.
>> 
>> 
>> My custom InputStream implements the no-arg read() and the three-arg
>> read(arr,off,len). The one-arg read(arr) falls back to the three-args
>> one by base class's implementation.
>> 
>> The overload of setBinaryStream without a length parameter is new
>> in jdbc 4.0, and I read from the docs that it should mean: get as many
>> bytes as possible from that stream (i.e. until EOF).
>> Obviously that is not always so - at least not with the informix
>> server I'm targetting or with the ifxjdbc.jar I got from the server's
>> jdbc/lib folder.
>> 
>> Has anyone else run into that problem and perhaps found a solution?
>> 
>> PS: the only relevant google hit is an unanswered stackoverflow question
>>   http://stackoverflow.com/questions/20471030/setbinarystream-saves-null-values
>> and that was even for a different db-system than the one I'm targetting.
>> 
>
> Have you made sure your InputStream is behaving as you assume it does?

I set breakpoints to all my read-methods (even added available() also
with breakpoint), single stepped the part where my stream object is created
and passed to setBinaryStream() then let it continue, and it ran to end
without passing by any breakpoint.

Next step might be, I add some logging, just to eliminate eclipse from
the equation, but honestly I'm *pretty* confident, that my stream-code
just simply isn't called.

Last step will be that I accept the ifxjdbc-driver as being buggy, and see
if a newer one is legally available and likely to help, or  cache all
the stream data into a byte array, then call one of the setBinaryStream-
methods *with* length parameter, and hope that these are less buggy.

[toc] | [prev] | [next] | [standalone]

#30447

Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> wrote:
> I set breakpoints to all my read-methods (even added available() also
> with breakpoint)

I'm quite embarrassed, but still happy to tell, that one of my tests
namely about the breakpoint in available() was unexpectedly flawn.

The gist is, that it *does* ask my stream's "available()" count,
and unlike what the documentation claims about reading till end
of stream, it reads exactly as many bytes as the available() 
returned.  A stream's available() is however NOT defined to return
it's total remaining amount of bytes, but merely the number of bytes
available *without waiting*, so it is - after all - still a bug.

Actually, the available() was even called twice in a row: once from
  setBinaryStream(idx,is)
apparently to be passed on to a call to
  setBinaryStream(idx,is,len)
which again called available(), apparently to verify that the stream
really had enough bytes "available". (These "apparently"s are based on 
experiments playing with fixed constants returned from my available()
or passed to sBS.)

I really hoped that the new method would allow me to just pass it a
stream and have jdbc consume it directly into the Db as I thought to
read from docs, but I'll have to wait for universe version 2 to fix
this (besides a big big lot of arguably more stringent problems).

At least I can now deal with it and work around it. (caching it all
in memory, that is)

I'd be curious, though, if someone with access to a non-Informix and 
of course non-critical DB ideally having already tables with blobs could
try setting breakpoints in his stream class's available() method (even
if its a standard one) and report about how often it was trapped during
a setBinaryStream with length versus without length.
If you don't happen to have such a setup at or near hand, don't bother :)

[toc] | [prev] | [next] | [standalone]

#30453

On 2014-11-29 20:39, Andreas Leitgeb wrote:
> Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> wrote:
>> I set breakpoints to all my read-methods (even added available() also
>> with breakpoint)
> 
> I'm quite embarrassed, but still happy to tell, that one of my tests
> namely about the breakpoint in available() was unexpectedly flawn.
> 
> The gist is, that it *does* ask my stream's "available()" count,
> and unlike what the documentation claims about reading till end
> of stream, it reads exactly as many bytes as the available() 
> returned.  A stream's available() is however NOT defined to return
> it's total remaining amount of bytes, but merely the number of bytes
> available *without waiting*, so it is - after all - still a bug.
> 
> Actually, the available() was even called twice in a row: once from
>   setBinaryStream(idx,is)
> apparently to be passed on to a call to
>   setBinaryStream(idx,is,len)
> which again called available(), apparently to verify that the stream
> really had enough bytes "available". (These "apparently"s are based on 
> experiments playing with fixed constants returned from my available()
> or passed to sBS.)
> 
> I really hoped that the new method would allow me to just pass it a
> stream and have jdbc consume it directly into the Db as I thought to
> read from docs, but I'll have to wait for universe version 2 to fix
> this (besides a big big lot of arguably more stringent problems).
> 
> At least I can now deal with it and work around it. (caching it all
> in memory, that is)
> 
> I'd be curious, though, if someone with access to a non-Informix and 
> of course non-critical DB ideally having already tables with blobs could
> try setting breakpoints in his stream class's available() method (even
> if its a standard one) and report about how often it was trapped during
> a setBinaryStream with length versus without length.
> If you don't happen to have such a setup at or near hand, don't bother :)
> 

How about returning, say, Integer.MAX_VALUE in available()? It should
still stall when read() returns -1, shouldn't it?

[toc] | [prev] | [next] | [standalone]

#30454

On 11/29/2014 8:37 PM, Daniele Futtorovic wrote:
> On 2014-11-29 20:39, Andreas Leitgeb wrote:
>> Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> wrote:
>>> I set breakpoints to all my read-methods (even added available() also
>>> with breakpoint)
>>
>> I'm quite embarrassed, but still happy to tell, that one of my tests
>> namely about the breakpoint in available() was unexpectedly flawn.
>>
>> The gist is, that it *does* ask my stream's "available()" count,
>> and unlike what the documentation claims about reading till end
>> of stream, it reads exactly as many bytes as the available()
>> returned.  A stream's available() is however NOT defined to return
>> it's total remaining amount of bytes, but merely the number of bytes
>> available *without waiting*, so it is - after all - still a bug.
>
> How about returning, say, Integer.MAX_VALUE in available()? It should
> still stall when read() returns -1, shouldn't it?

Hopefully it uses a read with buffer and not the no-arg read.

Arne

[toc] | [prev] | [next] | [standalone]

#30456

These are easy to answer...

Arne Vajhøj <arne@vajhoej.dk> wrote:
> On 11/29/2014 8:37 PM, Daniele Futtorovic wrote:
>> On 2014-11-29 20:39, Andreas Leitgeb wrote:
>>> The gist is, that it *does* ask my stream's "available()" count,
>>> and unlike what the documentation claims about reading till end
>>> of stream, it reads exactly as many bytes as the available()
>>> returned.  A stream's available() is however NOT defined to return
>>> it's total remaining amount of bytes, but merely the number of bytes
>>> available *without waiting*, so it is - after all - still a bug.
>> How about returning, say, Integer.MAX_VALUE in available()? It should
>> still stall when read() returns -1, shouldn't it?

Nope, that wouldn't work.  It uses the first call to available() as
len parameter to the other sBS, then apparently creates a thus-sized
byte[] and has it filled all in one single read.
If it receives less -> *Boom* (aka: SQLException)

> Hopefully it uses a read with buffer and not the no-arg read.

Yes, rest assured it calls a read with buffer. :-)
If some custom IS-subclass *only* implements the abstract one-arg
read, then it will of course still boil down to repeated no-arg
use, but then that wouldn't be the jdbc-driver's fault. ;-)

[toc] | [prev] | [next] | [standalone]

#30448

On Sat, 29 Nov 2014 14:45:42 +0000, Andreas Leitgeb wrote:

> Has anyone else run into that problem and perhaps found a solution?
>
I had a similar problem some time back when I was writing my mail 
archiving system, which stores archived e-mail in a PostgreSQL database 
using text fields to store the message body and a subset of it that 
contains searchable text. Text fields can contain arbitrary amounts of 
text: there is no way to specify a size for a text field. I think other 
databases call them CLOBs (Character Large Objects).

The issue was with moving data into and out of these text text fields: 
the only JDBC interface for this data type is via an input or output 
Stream. I ended using ByteArrayOutputStreams to suck the data out of text 
columns into byte[] arrays and ByteArrayInputStreams to push data into 
them. This took a fair bit of head scratching to get it working since I 
kept thinking that the names of these classes were backward: wouldn't you 
expect a ByteArrayOutputStream to drain the byte array rather than fill 
it?


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#30449

Martin Gregorie <martin@address-in-sig.invalid> wrote:
> The issue was with moving data into and out of these text text fields: 
> the only JDBC interface for this data type is via an input or output 
> Stream. I ended using ByteArrayOutputStreams to suck the data out of text 
> columns into byte[] arrays and ByteArrayInputStreams to push data into 
> them.

In hindsight (now that I've solved my own "puzzle"), the ByteArray*Stream
helped you in two ways: first, to do the rather unexpectedly necessary
text<->bytes conversion, and second, that the ByteArrayInputStream has
conveniently implemented available() as needed at least by informix
jdbc-drivers. (as I just learnt...)

> This took a fair bit of head scratching to get it working since I 
> kept thinking that the names of these classes were backward: wouldn't you 
> expect a ByteArrayOutputStream to drain the byte array rather than fill 
> it?

I've put it into my head, that any *OutputStream has write, and any *Input-
Stream has read().  But even apart from that, when there's an Xxx[In|Out]-
putStream, then typically the Xxx describes where data goes out TO or
comes in FROM, i.e.: the Xxx usually describes the other end of what
you call the methods on.
That said, the methods around ProcessBuilder/Process: redirect* and
get*Stream methods still require me to quadruple check that I got the
right point of view regarding In/Out.

[toc] | [prev] | [next] | [standalone]

#30452

On Sat, 29 Nov 2014 20:36:40 +0000, Andreas Leitgeb wrote:

> In hindsight (now that I've solved my own "puzzle"), the
> ByteArray*Stream helped you in two ways: first, to do the rather
> unexpectedly necessary text<->bytes conversion,
>
I could be wrong, but I don't think any conversion is needed or done: 
AFAIK a text field is simply an array of bytes that's as big as needed to 
contain an arbitrary amount of data and the streams just transfer raw 
bytes. 

I throw complete MIME encoded message bodies into a text fields when 
archiving a message and, when I need to look at an archived message, its 
sucked out, attached to a newly built covering message as a whole e-mail 
message and sent to my normal mail account so I can open it with my usual 
mail reader. I never had problems accessing all the MIME parts in the 
archived message. None of them have ever shown signs of being garbled by 
character code translations. Binary types, e.g. JPG images, open as 
normal.
 
> ByteArrayInputStream has conveniently implemented available() as needed
> at least by informix jdbc-drivers. (as I just learnt...)
>
I've never looked at that because I haven't needed to use it. All I care 
about, which I did check during initial testing, is that the same number 
of bytes are present before and after either transfer direction.
  

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#30457

Martin Gregorie <martin@address-in-sig.invalid> wrote:
> On Sat, 29 Nov 2014 20:36:40 +0000, Andreas Leitgeb wrote:
>> In hindsight (now that I've solved my own "puzzle"), the
>> ByteArray*Stream helped you in two ways: first, to do the rather
>> unexpectedly necessary text<->bytes conversion,
> I could be wrong, but I don't think any conversion is needed or done: 

I didn't yet come across clob or text so that's not an area I really
know about.

I'd however expect certain functionality, such as being able to store
international characters, and have the database really know the actual
characters for stuff like collation etc.

Also, it is clear, that at some point, a stream of single bytes flows
between client and server.

In my naive imagination, I'd have expected, that Jdbc (the J stands
for Java, doesn't it?) would define a text/clob interface in terms
of char, thus Reader/Writer (and internally pick it's own byte
encoding based on best performance for actual transfer).
Well, it ain't so, whatever the reasons, maybe even good ones.

Instead, there is setAsciiStream, which is an awful name, imho, for
referring to the 7-bit encoding "ascii".  Javadoc says:
"  The JDBC driver will do any necessary conversion from ASCII to
"  the database char format.
Wait, what? Isn't ASCII already a text format? How can one convert
from *ascii* into something capable of international scripts?

Probably one had better ignore all one's knowledge about ASCII,
and assume they're writing about some unspecified byte-encoding
instead. Whether that is some iso-8859-*, utf-7/8, cjk*, windows-*
or whatever else is probably defined elsewhere.

So much about text/clobs, now for MIME:

> AFAIK a text field is simply an array of bytes that's as big as needed to 
> contain an arbitrary amount of data [...]

Except, that the Database is expected to see it as characters, with
collating order, etc, and not just as some blob marked as "I'm really
a text - once the user gets me back and decodes me"

> I throw complete MIME encoded message bodies into a text fields when 
> archiving a message

MIME messages are a strange beast. For their "quoted-printable" (iirc)
sections they specify the character-encoding in the section headers,
but as a whole it would probably be best to treat them as a blob on
db side.  No way, the database would analyse each section to actually
get (collatable) characters.

My doubts probably didn't affect you, because all you do on db-side
is write in and read out. Why not just use a blob?

> Binary types, e.g. JPG images, open as normal.
Aren't these already base64'd in the MIME message? So the db sees
them even as 7 bit ascii :-)
But even if your message had some cjk text in it, the db sees 8 bits,
may interpret them as any 8bit encoding e.g. windows-1251, convert to
unicode (e.g. utf-16 like java) and upon converting back for read-out,
it'll produce exactly the same byte stream.

Just for "scientific" curiosity, I'd suggest to create a new table in
your db like the one you have, but with text turned to blob (or long-
varbyte), and measure performance and also check the actual disk-space
consumption of a text/clob versus blob. I'd expect much more overhead
from clob compared to blob, but if the database is simple/old enough and
sees "text" only in 8bit locales, then that difference might not even
show up.

>> ByteArrayInputStream has conveniently implemented available() as needed
>> at least by informix jdbc-drivers. (as I just learnt...)
>>
> I've never looked at that because I haven't needed to use it. All I care 
> about, which I did check during initial testing, is that the same number 
> of bytes are present before and after either transfer direction.

I hope you did also check the contents, not just the number of bytes ;-)

[toc] | [prev] | [next] | [standalone]

#30460

On Sun, 30 Nov 2014 14:56:21 +0000, Andreas Leitgeb wrote:

> I didn't yet come across clob or text so that's not an area I really
> know about.
>
In some databases they can be quite weird, explicitly being stored 
external to the table that defines them. There's not a lot of detail on 
how Postgres stores them: you just declare the column as 'text' type and 
use it as you would a character varying column except that it doesn't 
have size limitations. In fact, Postgres recommends the use of text in 
preference to character(n) or character varying(n) because it doesn't 
have any length checking overheads.

> I'd however expect certain functionality, such as being able to store
> international characters, and have the database really know the actual
> characters for stuff like collation etc.
> 
> Also, it is clear, that at some point, a stream of single bytes flows
> between client and server.
> 
> In my naive imagination, I'd have expected, that Jdbc (the J stands for
> Java, doesn't it?) would define a text/clob interface in terms of char,
> thus Reader/Writer (and internally pick it's own byte encoding based on
> best performance for actual transfer).
> Well, it ain't so, whatever the reasons, maybe even good ones.
> 
Probably due to the potential length of a text field?

> Aren't these already base64'd in the MIME message? So the db sees them
> even as 7 bit ascii :-)
>
Correct.

> But even if your message had some cjk text in it, the db sees 8 bits,
> may interpret them as any 8bit encoding e.g. windows-1251, convert to
> unicode (e.g. utf-16 like java) and upon converting back for read-out,
> it'll produce exactly the same byte stream.
>
I think you'll find that the RFCs for e-mail specify that a message body 
must use 8 bit characters - which is why a MIME part with content other 
than ASCII or UTF-8  will be encoded as base64 or something else that can 
be represented as 8 bit characters.

I used a character type representation rather than blobs because of the 
RFCs requiring that a message body and headers must be 8-bit characters.

> Just for "scientific" curiosity, I'd suggest to create a new table in
> your db like the one you have, but with text turned to blob (or long-
> varbyte), and measure performance and also check the actual disk-space
> consumption of a text/clob versus blob. I'd expect much more overhead
> from clob compared to blob, but if the database is simple/old enough and
> sees "text" only in 8bit locales, then that difference might not even
> show up.
>
I'll bear it in mind next time I design a database - the mail archive is 
far to big to be messed about with at 4.9 GB.

> I hope you did also check the contents, not just the number of bytes ;-)
>
Of course. 


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#30463

On 12/1/2014 9:06 AM, Martin Gregorie wrote:
> I think you'll find that the RFCs for e-mail specify that a message body
> must use 8 bit characters - which is why a MIME part with content other
> than ASCII or UTF-8  will be encoded as base64 or something else that can
> be represented as 8 bit characters.
>
> I used a character type representation rather than blobs because of the
> RFCs requiring that a message body and headers must be 8-bit characters.

Do you mean 7 and not 8?

Arne

[toc] | [prev] | [next] | [standalone]

#30464

On Mon, 01 Dec 2014 21:36:30 -0500, Arne Vajhøj wrote:

> On 12/1/2014 9:06 AM, Martin Gregorie wrote:
>> I think you'll find that the RFCs for e-mail specify that a message
>> body must use 8 bit characters - which is why a MIME part with content
>> other than ASCII or UTF-8  will be encoded as base64 or something else
>> that can be represented as 8 bit characters.
>>
>> I used a character type representation rather than blobs because of the
>> RFCs requiring that a message body and headers must be 8-bit
>> characters.
> 
> Do you mean 7 and not 8?
>
No, that used to be the case when e-mail message bodies used 7-bit ASCII 
or ECMA encoding, but now that UTF-8 is generally accepted in e-mail 
message bodies, 8 bit encoding is accepted, but only UTF-8 is near 
universal. 

There are some Apple mailing systems that insist on setting the encoding 
to MACINTOSH. Some mail handling libraries, JavaMail in combination with 
Mstor is one of them, spit at this. However, AFAICT MACINTOSH is just a 
synonym for UTF-8: changing it to UTF-8 makes the mail parsable by Mstor/
JavaMail and its still readable when displayed by my usual mailreader, 
Evolution.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#30468

Martin Gregorie <martin@address-in-sig.invalid> wrote:
> On Sun, 30 Nov 2014 14:56:21 +0000, Andreas Leitgeb wrote:
>> I didn't yet come across clob or text so that's not an area I really
>> know about.
> In some databases they can be quite weird, explicitly being stored 
> external to the table that defines them. [...]

Whether it's in-table or in an extension or extra reserved space for
smart objects (as in informix) should be transparent to the program 
code inserting, updating or reading information.

>> In my naive imagination, I'd have expected, that Jdbc (the J stands for
>> Java, doesn't it?) would define a text/clob interface in terms of char,
>> thus Reader/Writer (and internally pick it's own byte encoding based on
>> best performance for actual transfer).
>> Well, it ain't so, whatever the reasons, maybe even good ones.
> Probably due to the potential length of a text field?

That, makes no sense to me.  A Reader is to an char, what InputStream
is to byte.  Both naturally "support" dynamic length.

>> But even if your message had some cjk text in it, the db sees 8 bits,
>> may interpret them as any 8bit encoding e.g. windows-1251, convert to
>> unicode (e.g. utf-16 like java) and upon converting back for read-out,
>> it'll produce exactly the same byte stream.
> I think you'll find that the RFCs for e-mail specify that a message body 
> must use 8 bit characters - which is why a MIME part with content other 
> than ASCII or UTF-8  will be encoded as base64 or something else that can 
> be represented as 8 bit characters.

If it really says "must" for 8bit, then this is surely just to define
bytes as bit-octets, while some exotic systems might perhaps take 10
or 12 bit for a "byte".

> I used a character type representation rather than blobs because of the 
> RFCs requiring that a message body and headers must be 8-bit characters.

The decision between db text or blob ought to be, whether it makes sense
for the *database* to treat data as characters and allow certain operations
on it e.g. select a slice(first 100 chars) of it for preview-text, and
ordering result rows by these previews.

Otherwise, it's like using a bus to transport cupboards and couches,
ignoring the capabilities (seats) to transport humans.  Might still
serve the job, but a plain lorry(blob) would be likely more performant
during load and unload, mostly because it lacks the passenger seats.

>> Just for "scientific" curiosity, I'd suggest to create a new table in
>> your db like the one you have, but with text turned to blob (or long-
>> varbyte), and measure performance and also check the actual disk-space
>> consumption of a text/clob versus blob. I'd expect much more overhead
>> from clob compared to blob, but if the database is simple/old enough and
>> sees "text" only in 8bit locales, then that difference might not even
>> show up.
> I'll bear it in mind next time I design a database - the mail archive is 
> far to big to be messed about with at 4.9 GB.

Potential of reducing it by almost 50% does not sound tempting? ;-)

[toc] | [prev] | [next] | [standalone]

#30469

On Tue, 02 Dec 2014 17:56:05 +0000, Andreas Leitgeb wrote:

> Martin Gregorie <martin@address-in-sig.invalid> wrote:
>> On Sun, 30 Nov 2014 14:56:21 +0000, Andreas Leitgeb wrote:
>>> I didn't yet come across clob or text so that's not an area I really
>>> know about.
>> In some databases they can be quite weird, explicitly being stored
>> external to the table that defines them. [...]
> 
> Whether it's in-table or in an extension or extra reserved space for
> smart objects (as in informix) should be transparent to the program code
> inserting, updating or reading information.
>
Yeah, sure, but that doesn't necessarily carry over to the JDBC 
interface. Unfortunately.  I'm certain I was able to do the obvious 
String mapping with character() and character varying() fields, but was 
forced to use a stream to access a text field.
 
>>> In my naive imagination, I'd have expected, that Jdbc (the J stands
>>> for Java, doesn't it?) would define a text/clob interface in terms of
>>> char, thus Reader/Writer (and internally pick it's own byte encoding
>>> based on best performance for actual transfer).
>>> Well, it ain't so, whatever the reasons, maybe even good ones.
>> Probably due to the potential length of a text field?
>
There was a good reason for not going that way, of so it seemed at the 
time. 

Its been quite some years since I wrote the archive and in the meantime 
the archive has just sat there and worked without needing any attention 
except when a message turns up with MIME parts described as using 
MACINTOSH encoding or something else that Mstor and/or JavaMail can't 
parse. As a result, I just don't remember some of that detail - sorry.

I'm fairly certain I looked at using Clobs/Blobs, but couldn't get it to 
play nicely with what I was trying to do, particularly as JavaMail wants 
nothing to do with them.

> That, makes no sense to me.  A Reader is to an char, what InputStream is
> to byte.  Both naturally "support" dynamic length.
>
Yes, but not all JDBC implementations map SQL types onto the Java types 
you might expect.
   
> If it really says "must" for 8bit, then this is surely just to define
> bytes as bit-octets, while some exotic systems might perhaps take 10 or
> 12 bit for a "byte".
>
Yes, thats true. In UTF-8, for instance, it plays nicely with most of the 
common Western characters but some Middle Eastern and Asian characters 
get turned into hex hell when you try to display them..

> The decision between db text or blob ought to be, whether it makes sense
> for the *database* to treat data as characters and allow certain
> operations on it e.g. select a slice(first 100 chars) of it for
> preview-text, and ordering result rows by these previews.
>
Frankly, that isn't and wasn't important. 

My earliest design decision was that I wouldn't attempt to make the 
archive interpret anything in a message except some headers (needed for 
indexing and use as dimensions in the star schema) and plain text MIME 
parts (or the whole of a non-MIME body) which is used to support partial 
whole-body content searches. This gives good enough searching and search 
subsetting that the search performance is considerably faster than 
ferreting through huge local mail folders ever is. To read the message 
content, the whole thing is stuffed into a MIME part that describes it as 
a complete e-mail message, which it is, within a covering e-mail. This  
is addressed to me and sent. Two seconds later its in Evolution's in tray 
in front of me and I can open and read it.

> Otherwise, it's like using a bus to transport cupboards and couches,
> ignoring the capabilities (seats) to transport humans.  Might still
> serve the job, but a plain lorry(blob) would be likely more performant
> during load and unload, mostly because it lacks the passenger seats.
> 
... which is exactly what my, sometimes huge, text field holding the 
entire message does. Parsing the message into its components in the 
archive would serve no useful purpose. The system as it stands is quite 
quick enough. During the day Postfix does a BCC for every incoming and 
outgoing message. These build up in a main queue until the archive's 
overnight archival run, which I've never seen take more than 30 seconds 
to complete (it runs at up to 10 msgs/sec, slower if there are few 
messages to archive, but of course that's because its Java and hasn't run 
long enough to get JITed properly). Searching the archive is typically 
under 25 secs (whole archive), and 5 or less if I limit it to the last 12 
months.
 
> Potential of reducing it by almost 50% does not sound tempting? ;-)
>
Not unless I held the messages in compressed form and I'm not sure that 
would do the runtime much good, especially the search times. Some of the 
dimension keys are bigger than I'd really like too (Subject line) but 
that can't be helped either and don't forget that some of the dimensions 
are M:M (correspondents : messages). 

The one thing that I do occasionally think about is writing a batch 
process that could trawl the archive and flag up any spam it finds. I 
know there's some in there but haven't any idea what proportion it makes 
up. Known spam is, of course, excluded but some "never seen this type 
before" spam may get past Spamassassin and be stored. Later examples will 
get excluded by new SA rules or the various blacklists, but meanwhile the 
early samples are in the archive.
  

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.programmer

csiph-web