Hi!

Thanks for the answer. BTW I meant zero postfix, not prefix.

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
> >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.
> 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.

On my Firebird 2.1.1 installation, using FlameRobin to send the
commands and show the results, is does insert the octets as I would
expect: 0100hex

> 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.

Yes, this all seems to work, with the same result. But the primary key
constraint violation still occurs. Any ideas?

Cheers,
Christof