why does this
select * from event where room_name =
'A Test Room ';

return the same as this

select * from subevent where room_name =
'A Test Room';

in firebird 1.0? is something trimming the right side? if so why? this
actually caused an sql statement to return nothing when it should have when
i did

select * from childtable where name not in (Select name from parenttable)

if the childtable had a name with a trailing space and the parent table
didn't it was not reported.

any info?

thanks

-----Original Message-----
From: Alan McDonald [mailto:alan@...]
Sent: Wednesday, February 11, 2004 1:36 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Re: Auto Generate nos. but trigger won't
work

> Hi Alan
>
> Thanks for responding to my silly queries
>
> At 03:27 pm 11/02/2004, you wrote:
> >You don't request a new record from the server, you create a
> record insert
> >statement at the client. You can ask the server for a new record
> PK though,
> >first, then insert this new record you are constructing at the
> client with
> >the requested PK value. You know after asking the server for
> this PK value
> >that it will never be refused insertion to the DB since it has an
> >unequivocal PK value. And it will never be handed out to anyone else.
>
> Okay, I was under the impression that a new record place holder
> is there so
> it should be having a PK filled in, my bad, sorry.
>
>
> >generator values are generated outside all transaction control.
> You ask for
> >and it's gone whether you use it or not, it's gone.. never to be
> returned to
> >anyone else who askks for a generated value. This needs to be imutable in
> >your design, don't go down the track of being tempted to reset generator
> >values at your leisure or when you think you'd like to. That's the main
> >reason why PK values should be meaningless to your app. Gaps in
> the running
> >order of PK values are of absolutely no conseqence.
>
> Yes that I am clear with, I expect gaps in generated IDs
>
> >You would need to show us the trigger you are using. What you
> are describing
> >is possible only if you are sending the server a value - not
> NULL - which is
> >therefore not requesting a generated value since you have a not null if
> >cluse on your trigger.
> >let's see it.
>
>
> The dependency tab for the table MACCOUNTS has the following:
> Dependent: TRG_MACCOUNTS_IID_BI
> Fieldl: IID
>
>
> the following is the tables DDL:
> create table MACCOUNTS(
> IID integer not null,
> IPID integer,
> CNAME char(35) not null);
>
> /* Primary Key */
> alter table MACCOUNTS add constraint CON_MACCOUNTS_IID primary key (IID);
>
> /* Foreign Key */
>
> /* Indexes */
>
> /* Triggers */
> create trigger TRG_MACCOUNTS_IID_BI for MACCOUNTS active before insert
> position 0
> as
> begin
> IF (NEW.IID IS NULL) THEN
> NEW.IID = GEN_ID(GEN_MACCOUNTS_IID,1);
> end
>
> /* Grants */
>

If you send this table an
INERT INTO TABLE (iid) VALUES (NULL)
you will advance the generator without exception.
Alan






Yahoo! Groups Links