Thomas,
I think the issue here is that H2 is treating literals as hex *even
when* there is not an explicit hex encoding indicator (the 'x').
Hex encoding is needed if the data is stored in raw txt files (or
other form which will mangle control chars etc), but not needed at the
API layer. So if there is not encoding hint 'x' then I believe the API
layer should treat literals as (char)byte (ignoring string encoded),
*if* the value applies to a blob type column (matching PG and Mysql).
Answers to your questions:
I don't use a prepared statement because the middleware depends on a
internal conversions for dynamic queries (which would almost never re-
use a prepared statement so in theory would be much, much slower),
and, because the middleware outputs literal values that are supposed
to already be sql compliant.
Also I was not suggesting this applied to UTF-8 -- the *user
application* is responsible for generating byte[] form from strings if
applicable, e.g: insert into tbl (foo) values ('"+escapeSql(new
String(bytea,"iso-8859-1"))+"') where iso-8859-1 is equiv to ascii,
e.g (char)byte.
Again, both Mysql and PG *do* accept (char)byte form (when you don't
hint hex encoding):
-Mysql if you use SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES,ANSI';
-PG does by default in newer version which no longer treat '\' as
escape char).
Since I no longer have access to sql specs, here are some links that
hopefully describe the two different modes, explicit hex (hinted with
'x') encoding, vs (char)byte:
This shows PG's old encoding ( it is (char)byte, but adds-in the
extraneous 92 which I don think is needed in newer PG, and also 0
which is needed just for the wire protocol as I understand):
http://www.sql.org/sql-database/postgresql/manual/datatype-binary.html
http://www.herongyang.com/jdbc/Oracle-BLOB-SQL-INSERT.html (it does
not say what would happen for each server if using the (char)byte
method (escaping only 0x39)
Oracle, is different and unique becasue it defaults to hex mode
(Oracle is)... H2 is mimicking this but I don't believe it should.
Notably Sun's docs don't seem to mention "binary" and "literal" in the
same (raw sql) context:
http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html
Again I appreciate your suggestion that converting to hex and hint
with 'x' would work, but this is also very inefficient (double hex enc/
dec and memcopy).
So, can the server be changed to accept literals which are not hinted
with 'x', as (char)byte, to behave like PG and Mysql?
Thank you,
Ken
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
