[firebird-support] Create Autoincrement

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[firebird-support] Create Autoincrement

Clyde Eisenbeis
Using http://www.firebirdfaq.org/faq29/ as a reference.

1) If I understand this website, the CREATE GENERATOR is initiated only once?

2) Likewise the CREATE TRIGGER is initiated only once?

3) When is the T1_BI used (CREATE TRIGGER T1_BI FOR T1)?

4) I see the 'id' field in table t1.  I see upper case 'ID' in the
TRIGGER.  Are these two the same?

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [firebird-support] Create Autoincrement

Scott Morgan
On 03/24/2017 03:09 PM, Clyde Eisenbeis wrote:
> Using http://www.firebirdfaq.org/faq29/ as a reference.
>
> 1) If I understand this website, the CREATE GENERATOR is initiated only once?

Yes. It's a global item like a table, so you just need to create it once
(and commit that change to the DB,. Changes to tables and other global
items like that all need to be committed before using them). It'll have
an initial value of 0 and calls to GEN_ID will increment it by the
specified value (usually 1)

> 2) Likewise the CREATE TRIGGER is initiated only once?

Yes. It's just associated with the table, ready to run whenever the
specific event happens, so...

> 3) When is the T1_BI used (CREATE TRIGGER T1_BI FOR T1)?

The "ACTIVE BEFORE INSERT POSITION 0" specifies that. In this case
'BEFORE INSERT', so whenever a new item is inserted into the table, that
trigger is run.

> 4) I see the 'id' field in table t1.  I see upper case 'ID' in the
> TRIGGER.  Are these two the same?

Yes. (Bit of bad form in that FAQ there I'd say, mixing case) Unquoted
identifiers (names of tables, columns, etc.) are stored in upper case.
So id, ID, iD, and Id are all the same (ID).


You'll get more detailed answers about this stuff on the main Firebird
mailing list, and I highly recommend the Firebird Book by Helen Borrie,
which covers all this clearly and in depth. It's things like triggers
and foreign keys that make a proper DB so powerful, but it can take a
bit of getting used to.

Scott


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [firebird-support] Create Autoincrement

Clyde Eisenbeis
I should have started this email with ... C# ... Firebird ADO.NET ...
FirebirdClient.5.8.0 ... Visual Studio 2013.

I tried entering

    CREATE GENERATOR gen_t1_id;
    SET GENERATOR gen_t1_id TO 0;

    set term !! ;
    CREATE TRIGGER T1_BI FOR T1

in my C# code ... the compiler complains.  This appears to not be
relevant for C#.

What I need is the ability to add data and retrieve the primary key
which needs to be autoincremented ... see
http://stackoverflow.com/questions/42981872/why-the-executenonquery-catch-exception-validation-error-for-column-orgtable
for the current code.

On Fri, Mar 24, 2017 at 10:57 AM, Scott Morgan <[hidden email]> wrote:

> On 03/24/2017 03:09 PM, Clyde Eisenbeis wrote:
>> Using http://www.firebirdfaq.org/faq29/ as a reference.
>>
>> 1) If I understand this website, the CREATE GENERATOR is initiated only once?
>
> Yes. It's a global item like a table, so you just need to create it once
> (and commit that change to the DB,. Changes to tables and other global
> items like that all need to be committed before using them). It'll have
> an initial value of 0 and calls to GEN_ID will increment it by the
> specified value (usually 1)
>
>> 2) Likewise the CREATE TRIGGER is initiated only once?
>
> Yes. It's just associated with the table, ready to run whenever the
> specific event happens, so...
>
>> 3) When is the T1_BI used (CREATE TRIGGER T1_BI FOR T1)?
>
> The "ACTIVE BEFORE INSERT POSITION 0" specifies that. In this case
> 'BEFORE INSERT', so whenever a new item is inserted into the table, that
> trigger is run.
>
>> 4) I see the 'id' field in table t1.  I see upper case 'ID' in the
>> TRIGGER.  Are these two the same?
>
> Yes. (Bit of bad form in that FAQ there I'd say, mixing case) Unquoted
> identifiers (names of tables, columns, etc.) are stored in upper case.
> So id, ID, iD, and Id are all the same (ID).
>
>
> You'll get more detailed answers about this stuff on the main Firebird
> mailing list, and I highly recommend the Firebird Book by Helen Borrie,
> which covers all this clearly and in depth. It's things like triggers
> and foreign keys that make a proper DB so powerful, but it can take a
> bit of getting used to.
>
> Scott
>
>
> ------------------------------------------------------------------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> Firebird-net-provider mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/firebird-net-provider

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [firebird-support] Create Autoincrement

Mark Rotteveel-2
On 25-3-2017 15:09, Clyde Eisenbeis wrote:

> I should have started this email with ... C# ... Firebird ADO.NET ...
> FirebirdClient.5.8.0 ... Visual Studio 2013.
>
> I tried entering
>
>     CREATE GENERATOR gen_t1_id;
>     SET GENERATOR gen_t1_id TO 0;
>
>     set term !! ;
>     CREATE TRIGGER T1_BI FOR T1
>
> in my C# code ... the compiler complains.  This appears to not be
> relevant for C#.

You need to show how you execute this. For example `set term` is unknown
in Firebird itself, it is just client-side functionality in a number of
Firebird tools (like isql, flame robin, and in the Firebird ADO.net
provider FbScript tool.

If you are not using FbScript, but executing these statements like
normal commands, then you must execute 1) leave out `SET TERM`, and
execute the statements individually.

> What I need is the ability to add data and retrieve the primary key
> which needs to be autoincremented ... see
> http://stackoverflow.com/questions/42981872/why-the-executenonquery-catch-exception-validation-error-for-column-orgtable
> for the current code.

Use `INSERT .... RETURNING ID`.

Mark
--
Mark Rotteveel

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Firebird-net-provider mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/firebird-net-provider
Loading...