Hi dear list members.

I'm new here. Approximately 17 years ago I worked as a sysadmin, gave every day support for 70 peoples how to use their computers. I also made numerous small apps, to help the work of the firm. That time I did it with Delphi 1, used DBase and Paradox tables.
I did this job for a few years, then the life pushed me away from programming, but not too far away from computers.
Now I'm here, and seem to come back.
My friend wants me to create a database app, because the task is just too complicated to do by himself in MS Access.

I put my cents on Lazarus, as I can't afford any Delphi (and like) available today.
I choosed Firebird (a very cost effective solution as well) as database engine, started with embedded, but later when as development progresses need move to real client-server.

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.

But I'm stuck with getting the newly inserted rows ID. How do you get it?

Or if you generate the ID on the client side, how do you make absolutely sure, no other user can get the same ID.

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.

If I'm on a wrong way with this ID thing, any other "Best practices" are welcome. ;)

I highly appreciate every help word!

Thank you very much in advance

Laszlo Kovacs
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20131126/79dbe6dd/attachment.html>

Le 26/11/2013 16:02, Kov?cs L?szl? a ?crit :

Create as many sequences (new name for generators) as you need 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

Antonio.

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

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20131126/67bc6421/attachment-0001.html>

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

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.

First let me say thank you very much Antonio and Reinier!

------ Az eredeti ?zenet ------
Felad?: "Reinier Olislagers"
C?mzett: "Lazarus mailing list"
Elk?ld?tt elemek: 2013.11.26. 17:05:23
T?rgy Re: [Lazarus] newbie question: tsqlquery; primarykey; insert.

Thanks for the hint on FlameRobin, I'll check it.
IBExpert was really a big help for me.
After you mentioned, looked for but didn't find that data desktop in the tools dir.
I'm running on Windows, and just installed the latest Lazarus I could download.
Should it really be there?

------ Az eredeti ?zenet ------
Felad?: "Antonio Fortuny"
C?mzett:
Elk?ld?tt elemek: 2013.11.26. 16:52:06
T?rgy Re: [Lazarus] newbie question: tsqlquery; primarykey; insert.

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

Antonio.
-------------------

Antonio, thanks!
That rocks!
I dropped my triggers,because they are useless now. I added another query with the sql you mentioned (adapted the fields names of course :))
This is GenQuery.
My original query is already open, and bound to a dbgrid, and is now just a select * from a table, I'm just testing the basics. So, this is TestQuery.
I have put in TestQuery afterinsert event handler
Genquery.Open; TestQuery.FieldbyName ('ID').asLargeint := GenQuery.Fields[0].asLargeint; GenQuery.Close;
So if the dbgrid adds a row, I can see the newly generated ID in the column.
So far, so good, one basic thing is working, the new ID is under my control, cause I can have it in a variable, if I want to, and that's vital for further processing.
I think TestQuery.Post is called automatically by the DBGrid, but seems, that I have to call upplyupdates and commit.

Thank you very much!

Best regards
Laszlo Kovacs

The directory is %lazarus%\tools\lazdatadesktop\. There you'll find a lazdatadesktop.lpi which you'll need to compile (I don't know whether it's shipped precompiled as well).

Regards,
Sven