At 01:23 PM 10/02/2009, you wrote:

>Hi!
>
>I am executing the following SQL code:
>
>CREATE DATABASE 'keytest.fdb' user 'sysdba' password 'masterkey';
>
>CREATE TABLE test
>(
> Id VARCHAR(16) CHARACTER SET OCTETS NOT NULL,
> PRIMARY KEY (Id)
>);
>
>COMMIT;
>
>INSERT INTO test VALUES (ASCII_CHAR(1));
>INSERT INTO test VALUES (ASCII_CHAR(1) || ASCII_CHAR(0));
>
>The second INSERT gives me a:
>
>Statement failed, SQLCODE = -803
>violation of PRIMARY or UNIQUE KEY constraint "INTEG_2" on table "TEST"
>
>Why is that? The octet strings 01hex and 0100hex are clearly two
>different keys.

On the face of it, something aint right. One might suspect that the engine doesn't want to concatenate two octets. However, if you do this you get what you expected:

SQL> select cast (ascii_char(01) || ascii_char(02) as varchar(16) character set
octets) from rdb$database;

CAST
================================
0102

OK, let's suspect it is a casting matter...

INSERT INTO test VALUES (
cast (ASCII_CHAR(1) || ASCII_CHAR(0) as varchar(16) character set octets))

Mais non, it does not insert the expected OCTETS string but only the first byte.

But this does what we wanted:

INSERT INTO test VALUES (
cast (ASCII_CHAR(01) as varchar(16) character set octets) ||
cast (ASCII_CHAR(02) as varchar(16) character set octets) )

Is it really a casting matter? Let's try this:

INSERT INTO test VALUES (
(ASCII_CHAR(02)) || (ASCII_CHAR(01)) )

It works too: we didn't need the casting at all. We succeed in both cases simply by complying with the (often overlooked) requirement to add brackets in order to delimit expressions whose results are to be operands for another expression.

However, it doesn't quite explain the anomaly between our case when used as an input argument to an INSERT statement and our case when used to specify a derived output field.

./heLen