Re: JavaMail/MimeBodyPart question
Martin Gregorie wrote:
It looks as if I can get the complete message, which is what I want, by
extracting the content with getContent() and storing in in a BYTEA
(Postgres BLOB) via a Blob object. That process can easily be reversed
to make it into a MimeBodyPart attachment with setContent().However, I
also need to search the message body when its in the database. Can a
BYTEA be searched in SQL, e.g. with a LIKE or ILIKE search term? The
Postgres manual implies that you can't.
If by "implies" you mean "states that LIKE operates on strings", then yes, it
does, since BLOBs are not strings, as explicitly stated by the pg manual:
<http://www.postgresql.org/docs/8.2/interactive/datatype-binary.html>
Binary strings are distinguished from character strings by two characteristics:
First, binary strings specifically allow storing octets of value zero and other
"non-printable" octets (usually, octets outside the range 32 to 126).
Character strings disallow zero octets, and also disallow any other octet values
and sequences of octet values that are invalid according to the database's
selected character set encoding. Second, operations on binary strings process
the actual bytes, whereas the processing of character strings depends on locale
settings.
Continuing:
If I used a Clob (as suggested by Peter Forneau in a slightly different
context) I could map it to a TEXT, which would then be searchable, BUT
there seems to be no easy way to store a byte array, which is what
MimeMessage.content is, other than first converting it to a String.
Have I missed anything here?
You've missed that a String is not just a sequence of bytes, it's a sequence
of /encoded/ bytes. You have to play games with character encoding to make a
String properly hold binary data. It's not recommended.
Since LIKE is a character operation, it won't work on BLOBs in any SQL dialect.
--
Lew