BYTEA type compatibility with SQL inputs

[kensystem]

1) Should BYTEA columns accept ASCII input if quoted?
CREATE CACHED TABLE PUBLIC.TEST (VAL BYTEA)
then
INSERT INTO test (val) VALUES ('29/10')
throws
org.h2.jdbc.JdbcSQLException: Hexadecimal string with odd number of
characters: 29/10;

Can it be changed to accept this on presumption that the string is an
ASCII one? I believe this is accepted by PG and some others.

2) I also tried
INSERT INTO registry (val) VALUES ('12345') which is considered hex
would be legal (even if missing the leading 0x) equivalent to 012345
(0x012345)

3) INSERT INTO registry (val) VALUES ('0x012345') throws:
Hexadecimal string contains non-hex character: 0x012345

4) (I don't know if this should be accepted) INSERT INTO registry
(val) VALUES (0x012345) throws:
Hexadecimal string with odd number of characters: 74565;

[bob mcgee]

These questions are really more for Thomas, but since he is (mostly)
on vacation I will try to answer, and he can correct any mistakes.
As you have discovered, the current behavior for entering quoted text
into H2 is to treat it as pairs of hex characters encoding single
bytes, no '0x' prefix needed.

On Jul 22, 10:23 am, kensystem <kensys...@gmail.com> wrote:
> 1) Should BYTEA columns accept ASCII input if quoted? [...]

> Can it be changed to accept this on presumption that the string is an
> ASCII one? I believe this is accepted by PG and some others.

Probably not, for several good reasons.

One: Adding ASCII input opens things up for ambiguity. Does 'FACE'
mean the bytes 0xFACE (2 bytes) or the ASCII bytes (4 bytes)? What
about NULL & unprintable characters? What about foreign languages --
some keyboards may not even have the standard US-ASCII keys on them?

Two: Redundancy. The functions UTF8TOSTRING(bytes) and STRINGTOUTF8
(string) already exist to convert text to raw bytes. See:
http://www.h2database.com/html/functions.html#stringtoutf8 This
should be the preferred way to store text as bytes.

Thomas could add a function to convert bytes to/from arbitrary text
encodings, but it is very easy to do this in java or roll your own
user-defined function to do it.

Three: Performance. Parsing text as hex characters is very, very
fast, and is safe against the complexities of character sets &
encodings. US-ASCII isn't suitable for international use (as noted
above). This is why UTF-8 & UTF-16 are replacing them; the catch is
that they are much, much more complicated to parse, and you don't know
how long the byte string is until you do.

> 2) INSERT INTO registry (val) VALUES ('12345') which is considered hex

> would be legal (even if missing the leading 0x) equivalent to 012345
> (0x012345)

I suspect this can be changed without too much effort, although it
will mean you have to read the entire string in to memory to check
length. This could pose problems for very long byte strings, such as
BLOBs encoded in an SQL script.

> 3) INSERT INTO registry (val) VALUES ('0x012345') throws:
> Hexadecimal string contains non-hex character: 0x012345

This is already in the Roadmap: http://www.h2database.com/html/roadmap.html
If other people request it, it will get bumped up in priority.

> 4) (I don't know if this should be accepted) INSERT INTO registry
> (val) VALUES (0x012345) throws:
> Hexadecimal string with odd number of characters: 74565;

See answer to (2).

Cheers,
Bob McGee

[kensystem]

On Jul 22, 12:40 pm, bob mcgee <buckyba...@gmail.com> wrote:
> On Jul 22, 10:23 am, kensystem <kensys...@gmail.com> wrote:> 1) Should BYTEA columns accept ASCII input if quoted? [...]
> > Can it be changed to accept this on presumption that the string is an
> > ASCII one? I believe this is accepted by PG and some others.
>
> Probably not, for several good reasons.
>
> One:  Adding ASCII input opens things up for ambiguity.  Does 'FACE'
> mean the bytes 0xFACE (2 bytes) or the ASCII bytes (4 bytes)?  What
> about NULL & unprintable characters?  What about foreign languages --
> some keyboards may not even have the standard US-ASCII keys on them?

I agree; since H2 already treats inputs as HEX, it is too late to
change it. It cannot be compatible with PG syntax without modifying
the PG backup SQL.

>
> Two: Redundancy. The functions UTF8TOSTRING(bytes) and STRINGTOUTF8
> (string) already exist to convert text to raw bytes. See:http://www.h2database.com/html/functions.html#stringtoutf8 This
> should be the preferred way to store text as bytes.

I'm not sure here; while a java String can technically hold all 256
octets (including NULL), UTF8 codecs will mangle that. An ASCII or
ISO88591 encoding/decoding would not, though. As I recall PG uses a
binary safe encoding.

I believe SQL considers only apos as special character, and any other
octet, including \0 etc, are all just bytes and legal (for binary
data) inside apos-quoting (I know you know this but others may benefit
from my mentioning it here). Even so, not all DBMSs accept this (their
connectors, or wire-protocol, are confused by \0 and others).

>
> Thomas could add a function to convert bytes to/from arbitrary text
> encodings, but it is very easy to do this in java or roll your own
> user-defined function to do it.
>
> Three: Performance.  Parsing text as hex characters is very, very
> fast

For binary data, I might differ in opinion: hex encoding, due to size
(2x for hex) is half speed, and some additional to decode (vs char-
>byte cast).

>, and is safe against the complexities of character sets &
> encodings.  US-ASCII isn't suitable for international use (as noted
> above).

But in the context of bytea, where the input should already be
'binary' this is not an issue.

>
> Cheers,
> Bob McGee

Thank you,
-Ken

[kensystem]

This problem of how to import data from a PG backup.sql file, is
proving quite challenging, because the export has bytea columns, and
parsing which value-index from the insert stmt needing conversion to
suite H2 is non-trivial; does anyone have suggestion on how to do
handle bytea data from pg dumps?

I've already solved most of the other PG dialect issues (casts as ::,
convert with/out timezone to implicit/none, etc), but tracking the
values and aligning to the DDL-type and ordinal position is another
thing :-)

Maybe, H2 when in the PG-behavior mode, could just treat BYTEA data
like PG does...(?)

[bob mcgee]

On Jul 23, 12:06 am, kensystem <kensys...@gmail.com> wrote:
> does anyone have suggestion on how to do
> handle bytea data from pg dumps?

So, looked at the info for PostgreSQL here:
http://www.postgresql.org/docs/8.0/static/datatype-binary.html
.. and, I've got to say that I feel for you. This could be messy, but
I think H2's functions can make it easier.

Here's what I suggest: make a copy of the script for experimentation,
and open it in a text editor.
Then use search-and-replace to convert PG's escaping for single
apostrophes (slash or unicode) to H2's escaping (doubled
apostrophes).

I *think* H2 will accept the BYTEA items as valid strings then, and
you can use string functions to convert it.
Try STRINGDECODE(STRINGDECODE(input)) and see if this converts it to
the proper string representation (it should).
Now you've got the BYTEA as a string corresponding to the ASCII, but
in Unicode format.

To convert the Unicode string to bytes in US-ASCII (I think that's
what PG is using?), you'll need to use java or a user-defined
function.

Copy the following to a file called H2Utils.java, and compile it to
a .class file:

public class H2Utils {
public static byte[] encodeString(String input, String encoding)
throws UnsupportedEncodingException {
return input.getBytes(encoding);
}
public static String decodeString(byte[] input, String encoding)
throws UnsupportedEncodingException {
return new String(input,encoding);
}
}

Set up the classpath for your H2 application so it includes the
H2Utils.class file.
Create user-defined functions for these methods with:
CREATE ALIAS IF NOT EXISTS string_to_bytes FOR 'H2Utils.decodeString';
CREATE ALIAS IF NOT EXISTS bytes_to_string FOR 'H2Utils.encodeString';

The final expression to produce your BINARY output from PG BYTEA input
(read by H2 as a CLOB or VARCHAR)
will be:

string_to_bytes(STRINGDECODE(STRINGDECODE(pg_bytea_string)),'US-
ASCII').

With some finagling and the above, you should be able to do a 2-stage
build from script, first reading BYTEA as
VARCHAR/CLOB, then using that function to convert to BINARY/BLOB.
Messy, but should work. Proviso:
not tested -- do let me know how well it works.

> tracking the values and aligning to the DDL-type and ordinal position is another
> thing :-)

You might find the functions in org.h2.values.DataType and
java.sql.Types useful here.
They assist with type conversions & type mapping.
(browse source online:
http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/value/DataType.java
)

Cheers,
Bob McGee

[bob mcgee]

CORRECTION: the following

> CREATE ALIAS IF NOT EXISTS string_to_bytes FOR 'H2Utils.decodeString';
> CREATE ALIAS IF NOT EXISTS bytes_to_string FOR 'H2Utils.encodeString';

Should read:
CREATE ALIAS IF NOT EXISTS string_to_bytes FOR 'H2Utils.encodeString';
CREATE ALIAS IF NOT EXISTS bytes_to_string FOR 'H2Utils.decodeString';

Sorry!
Bob McGee

[Thomas Mueller]

Hi,

I will add a feature request for

PostgreSQL compatibility: when in PG mode, treat BYTEA data like PG

Regards,
Thomas