[Lazarus] newbie question: tsqlquery; primarykey; insert.

[Reinier Olislagers]

On 26/11/2013 16:52, Antonio Fortuny wrote:
> Le 26/11/2013 16:02, Kovács László a écrit :
>> So, I created my database, that wasn't too hard with IBExpert personal
>> (I had to look for something to substitute Database desktop).
>> I also managed to create my autoinc fields, using Firebirds triggers
>> and generators. Each of my tables has its own counters.

FYI:
1. IBExprt is a good tool, but FlameRobin is also nice (and open
source). Multiplatform as well.
2. There's a Lazarus data desktop application included in (IIRC) the
tools directory of your Lazarus install.

>> But I'm stuck with getting the newly inserted rows ID.
>> How do you get it?
> Create as many sequences (new name for generators) as you need
Probably IBExpert created these for you already...

> In the application when you need a new value for a sequence (an insert
> for instance) I execute the next query just before:
> */select next value for my_generator_name as column_name_i_want from
> rdb$database/*
> and I get the value with the query which executed the query (some TQuery
> component, using FieldByName('column_name_i_want).AsInteger or better
> AsLargeInt)
> The unique ID has been definitely reserved and the I can use it into any
> other update or insert with other tables.
> This is my way but it not the only one but because I want to control
> everything which reports to database, i'm used to this method

Yep. You could also let the trigger do the job and open a query with
INSERT INTO...RETURNING <your primary key>, which will give you the just
inserted primary key.

>> Or if you generate the ID on the client side, how do you make
>> absolutely sure, no other user can get the same ID.
> That's easy: it is guaranteed by Firebirdwhatever way you choose
Yep. Generators/sequences are outside of transactions, i.e. it will
never give out the same number twice. Of course this leads to possible
holes in your primary key numbers if you rollback transactions etc but
that isn't relevant for most people (if you want absolutely sequential
numbers for other purposes, IIRC firebirdfaq.org has the solution).

>> Is there a way to ask Firebird about the next value of a generator?
>> And then I could use that ID upon an insert, and would deactivate the
>> triggers.
> yes, read above, no need to deactivate or reactivate triggers
Yes, correct. At least FlameRobin, and IIRC IBExpert, too, will create a
before insert trigger for your table that checks if you supplied a
non-null primary key. If so, it will use that.
If not, it will run the select next value for my_generator_name... etc
to get the required primary key value itself.

>> If I'm on a wrong way with this ID thing, any other "Best practices"
>> are welcome. ;)
I think you're on the right track.
You could also have a look at sqldb_tutorial0 through... let's say 2 on
the wiki. It also uses generators to use primary keys.
Actually, updates & improvements to thoses articles are very welcome.