0

In Delphi the TFDQuery component has a property "aggregates" which represents a lightweight version of client side aggregate fields to Data Sets.

If you insert "MAX(ID)" you get a number that is one less than the next free number of the ID.

If this could be used to calculate the ID-Field it would be a very easy method to have an autogenerator for the ID-Field for every kind of database.

But I doubt because it is called "client side". I don't know exactly what that means. Doesn't it calculate the aggregate for the whole table but only for a local partition of the records? In the last case it could perhaps be used for an auto generator once it is correctly initialized.

user1473001
  • 19
  • 2
  • I don't know what you're after, but I would recommend you to read [Auto-Incremental Fields](http://docwiki.embarcadero.com/RADStudio/en/Auto-Incremental_Fields_(FireDAC)). – Victoria Jul 28 '18 at 09:31
  • I know this page " Depending on the DBMS, the auto-incrementing fields may be implemented either using a special IDENTITY (or similar) column data type, or using a generator (or a sequence) and a table trigger: " That are little bit to many alternatives to me. A unified solution would be nicer. – user1473001 Jul 28 '18 at 09:57
  • Doing what is described there is a safe way. If you insert identity value based just on `MAX(ID) + 1`, you cannot guarantee that another user inserts the same. – Victoria Jul 28 '18 at 09:59
  • Besides, there is a unified way to create table with autoincremental identity column. Yet, it's not clear what you want to do. If you elaborate your aim, you can receive a proper answer. – Victoria Jul 28 '18 at 10:17

1 Answers1

1

If this could be used to calculate the ID-Field it would be a very easy method to have an autogenerator for the ID-Field for every kind of database.

I am afraid not, at any rate, not for "every kind of database". The signifcance of "client-side" is that a client-side method of deriving a row id is no good for a shared database where different users might be attempting to add rows to the same table at the same time.

For a multi-user database, it as always best you use whatever facility the server-side RDMS provides to provide ID-fields values. Some RDMSs like Interbase have so-called generators to do this reliably for you.

MartynA
  • 30,454
  • 4
  • 32
  • 73
  • That is what I was afraid of. Thank you for the clear answer. And yes I think too that there is no general solution for this problem but every RDMS has its own way. It was only a hope. – user1473001 Jul 28 '18 at 10:21
  • There is a unified way to generate `CREATE` command for autoincremental identity columns, if that is what you want to get (FireDAC can detect them, so what else could be the aim). Hard to say. This is the answer for what you asked. – Victoria Jul 28 '18 at 10:26
  • Thank you Victoria for your very useful replies. I use Stackoverflow very seldom for asking questions. I don't know how I can thank you. But I don't want to pay a coffee for the moment. I will have a closer look at Identity colums and the possibilities of generalization. I use only little databases for applications and mobile devices. I've seen that you are also interested in SQLite. That works very well with Delphi. I will migrate to Interbase ToGo. But there are many other DB-systems out there that Delphi can work with and so I am searching for generalization even for multiuser DBs. – user1473001 Jul 28 '18 at 11:17
  • You're welcome! But I'm not tied to SQLite (for RDBMS I prefer PostgreSQL). I'm interested in FireDAC in general. And I know that in FireDAC there's a way to define table and generate SQL command to create it. Or actually create it. If you were interested how, ask another question and I (or someone else) can show you how ;-) – Victoria Jul 28 '18 at 11:22