At 08:06 AM 1/05/2007, you wrote:

>Is there a way to encode binary data in a text format for submitting
>to Firebird?
>
>I'm experimenting with a CHAR(16) column with OCTETS, and it seems to
>work just jolly well for retrieving binary data and for updating
>binary data with parameters, but I can't seem to find a way to
>represent the raw binary data such that I can, say, put it into a SQL
>statement in a tool. A number of other databases represent the raw
>data with a hex prefix (e.g. 0x) or have a RAW type that takes
>hexadecimal values directly (Oracle does this).

The OCTETS character set is the right way to represent such
data. The engine then makes no assumptions about the contained data,
either when storing it or retrieving it. The difference between
OCTETS and NONE is in the way that trailing blanks are stored for
CHAR. For OCTETS, trailing blanks are 0x00, while for NONE (and
other charsets) they are 0x20.

>I've been up and down the source on SourceForge and as far as I can
>tell, if a value is not quoted then it better be a number or
>identifier, and if it is quoted, the contents are basically left
>untouched.

Correct. Remember, single-quoted....double quotes have quite a
different usage in SQL.

>You can prefix a value with an underscored version of the character
>set used, e.g. _LATIN1 'My Text' instead of just 'My Text', but I
>cannot see whether or even if any translation is performed on that value.

Translation is always performed on non-binary strings, in either
direction, on the basis of the client character set and the character
set that is defined on the database side for the stored data. The
purpose of the introducer syntax is to override this automatic
translation and coerce it to the character set named in the
introducer. One problem with introducer syntax is that it can only
be applied to literals - it doesn't work with replaceable parameters.

With OCTETS there is an additional problem: it is literally raw
data, so you can't specify it as the client character set. At the
client side, therefore, the introducer syntax _OCTETS has no
effect. Another way to look at this is that you can't "bend" the
input to an OCTETS field on the way through the client
interface. Therefore, it is the absolute responsibility of the
client application to ensure that it stores a value into the OCTETS
receiver that will be valid for whatever is to be its intended use.

>Is there a way of representing raw binary (which could include zeroes)
>as text to send to Firebird?

Absolutely. You can store anything in an OCTETS field. Not sure
what you mean by "could include zeroes", though. If you send the
character '0' (ascii 48) in your string, it is not the same as
sending the character 'NUL' (ascii 0).

Just don't lose sight of the fact that if your string is a hex
representation like e.g. 0xA56B99, the engine sees a string of 8
bytes, not a number or a meaningful byte-order or whatever). This
*is* a character set attribute of a text type, not a separate data
type with built-in assumptions about the format of the content.

OCTETS has no character encoding or well-formedness criteria: it
blindly accepts the raw string of bytes. The only checking done by
Firebird on OCTETS strings is for overflow, which is always a byte
count. Provided there is no overflow, it just drops the bytes it
gets into the field as-is.

You do need to be on guard that your driver interface doesn't
automatically transform your strings into something other than your
raw input. Some drivers provide the ability to specify "this is raw
data: send it to the API without munging it" or to recognise the
special implications of an XSQLVAR with the OCTETS charset, but many
don't. If you have doubts about what your driver is doing, or can be
instructed to do, ask on the relevant support list.

./heLen