----- Original Message -----
From: Helen Borrie
To: firebird-support@yahoogroups.com
Sent: Saturday, September 11, 2004 5:30 PM
Subject: Re: [firebird-support] Can't get Generator to work.


At 04:44 PM 11/09/2004 +1000, you wrote:
>Hope someone can help.
>
>FB 1.5 WinXP Marathon GUI.
>
>I'm using the Marathon program to create all the tables and set settings.
>I'm having trouble with 'auto-increment' type fields. I've just read a
>heap of past messages containing keywork 'generators' but none really
>helped, only confused me as to why I can't get it to work.
>
>My understanding is, in order to create a unique number, a generator is
>created, link or attach that to a trigger and finally at the table end, that
>trigger is called in 'before insert'.
>
>When attempting to insert data using Marathon an error message is returned
>"Trip_ID" field must have a value. "Trip_ID" field is an integer (not
>null) and is suppose to have been populated with an incremented (unique)
>number. This is not occurring.
>
>Is my understanding correct or am I missing something. (I'm coming from a
>MS Access background)
>
>Any help would be appreciated.

You're only encountering a rule encapsulated in Delphi (the back-end of
Marathon) that makes all non-nullable values "required fields". So, that
interface won't be any use to you for inserting rows into any tables that
depend on a trigger to fill in the "required field", since the tool has no
knowledge whatsoever of the trigger.

One way to work around this problem is to omit the PK column from the list
of input columns for the insert statement. Once the statement is posted to
the server, the trigger will take over and do its stuff - provided, of
course, that you wrote a "safe" trigger, of the form:

if (new.MyPK is null) then
new.MyPk = gen_id(MyGenerator, 1);

To use the Marathon interface for inserts "into a dataset", you are usually
working with a method that is operating on a set that includes the primary
key, typically "SELECT * FROM...". For many reasons, this is standard and
desirable in desktop admin tools. You'll have to work around this another
way.
One way would be to change your trigger as follows:

ALTER TRIGGER MyTrigger
as
begin
if (new.MyPK is Null or new.MyPK < 0) then
new.MyPK = gen_id(MyGenerator, 1);
end

Then, when inserting from Marathon's dataset grid, give it a negative value
for MyPK. You then keep Delphi/Marathon happy *and* ensure that the
trigger does its nice job for you.

When your write your own apps, e.g. with Delphi, you will typically have
some mechanism to get that generator value into your app *before* the
Insert is posted. You can write a function that does "select
gen_ID(MyGenerator, 1) from RDB$Database". With IB Objects, it will all be
done for you automagically if you use the GeneratorLinks property, or call
IBO's client-side function Gen_ID().

The important fact here is that generator values are delivered
independently of any transaction so, unlike "auto-increment types" in some
other dbmses, we have absolute control over the timing of the key value
generation.

./heLen

Thanks Helen.

I'm using Marathon (I didn't realize Delphi was in the background) for the purposes of setting up the tables and all table related stuff (relationships etc .....oh and training). Once set up, I'm looking at either VB or RealBasic as a frontend using ODBC. I'm not familiar with the Delphi way of doing things. I can't check just yet, but are you saying that in order to retrieve the next unique number it's just a matter of issuing a 'select' statement in the form you mentioned above. I hate to appear stupid, I'm just trying to make sure that this is not a Delphi only type scenario.

Steven Taylor
Melbourne, Australia.

[Non-text portions of this message have been removed]