Getting last id from firebird

[mangakissa]

The database engine must do the work for you, not the other way.

[taazz]

eg: I use GUID's as they are 100% unique ever across the whole database

How do you generate GUIDs in a cross-database way that are guaranteed unique in the database (and thus uniqueness in a system is not enough, since I might restore the database to a second system, or might be part of a cluster)

Think windows GUIDs the algorithm to create them is pseudo unique, well up to now I haven't any conflicts my self from different computers users and what not although I prefer the auto inc my self.

[Zoran]

The following is a trick that works well with PostgreSQL. No need to get the id on the client side or generate a globally unique id. The currval function returns the auto generated id for the current transaction. I'm sure something similar can be done with other database engines.

Code: [Select]

START TRANSACTION;
INSERT INTO mastertbl(fld)VALUES(1);
INSERT INTO detailtbl(foreignKey,fld)VALUES(currval('mastertbl_id_seq'),1);
END TRANSACTION;


Afaik Oracle (where postgresql afaik got most of the inspiration for Sequences) and Firebird (generator) have similar concepts.

Yes, and sequences/generators are not isolated by transactions. When a value is taken from sequence/generator, it is lost forever, no matter if current transaction succeeds or not.
Therefore, the uniqueness (and nothing else but uniqueness, you can get "holes" in your data generated this way) is globally guaranteed.

[Graeme]

How do you generate GUIDs in a cross-database way that are guaranteed unique in the database

Functionality is built into Free Pascal.

Code: [Select]

function tiCreateGUIDString: string;
var
  lGUID: TGUID;
begin
  // Delphi support code omitted for this post, but that is one extra line of code
  CreateGUID(lGUID);
  Result := GUIDToString(lGUID);
end;

we then have a GUID OID generator that strips off the characters from the GUID we don't want....

Code: [Select]

var
  LValue: string;
const
  cGUIDLength = 38;
begin
  Assert(AAssignTo.TestValid(TtiOID), CTIErrorInvalidObject);
  LValue := tiCreateGUIDString;
  //           10        20        30
  // 01234567890123456789012345678901234567
  // {81A9C48C-DEF3-11D6-81C4-0002E31296EB}
  // A GUID will be 38 chars long when created,
  // or 36 chars long when {} are removed.
  if (LValue[1] = '{') and
    (LValue[cGUIDLength] = '}') then
    LValue := Copy(lValue, 2, cGUIDLength - 2);
  AAssignTo.AsString := LValue;
end;


Obviously all this is hidden from the developer if they use tiOPF, as the tiOPF framework takes care of generating the ID values for you. You simply need to tell it which OID generator to use, but the framework defaults to the GUID one.

[Graeme]

The database engine must do the work for you, not the other way.

I treat a database just like a treat other storage (example a INI or XML file). It is simply a place to store data for later retrieval. This way of thinking means my applications are very portable to other platforms and database servers. I don't rely on DB specific features (eg: auto-inc fields) and don't use stored procs.

[marcov]

The problem with auto-increment is what this thread is about. How to retrieve that value the instant you inserted a record. Why? Because say you have a master-detail set of records that must all be inserted in one transaction. You can't insert the detail records until you know the PK (ID field) of the master record.

Yes so

• begin transaction
• insert master
• get masterid
• insert child1 with masterid
• insert child2 with masterid
• commit or rollback here

All within one transaction

As long as there is a way to get the masterid guaranteed for exactly that insert statement, you are good.  Doesn't matter if it is done via generators/sequences or via returning.

And as this thread mentions, not all DB components or database servers/engines return the value of the auto-increment field, or you have to try and retrieve it as best you can after the master record insert, but before the detail records insert. Just a terrible mess!

True. But at least Oracle, interbase/Firebird, postgresql and MSSQL >= 2005 have some way to do it. That basically leaves mysql.

Use GUID's as the ID (PK) value and everything can be done on the client side, all in one transaction, guaranteed no conflicts, works with all databases, and no need for back-and-throw queries.

But that precludes inserts from stored procedures reacting on triggers.

[mse]

How to retrieve that value the instant you inserted a record.

MSEgui has the flags "pf1_refreshinsert" and "pf1_refreshupdate" in "tmsefield.providerflags1" for the purpose. It uses the "returning" option of the DB and updates the field value automatically after record insert or update.  The numeric primary key field will be updated automatically by insert if no field has "pf1_refreshinsert" and the DB suports "lastinsertid".
Another possibility is to use a "tsequencelink" which automatically fetches an ID from DB before inserting a record.

[mangakissa]

MSEgui doesn't use SQLdb for connection databases.

I thing the title is a little bit misplaced. The purpose is to get the last id created by the generator and get it back into the dataset without refreshing the table.
The most simple thing of SQLdb is to refresh the inserted record only, so the created id is known after. Now you have to refresh the entire queryset and you don't like it if it has a lot of records dependent  the packetrecord property set.

The whole problem of applyupdates is more than one records can be inserted / updated. I think 'INSERT INTO table (<fields>) values (<values>) returning <lastid>' can't work with more records at once.

[marcov]

How do you generate GUIDs in a cross-database way that are guaranteed unique in the database

Functionality is built into Free Pascal.

Those are afaik only guaranteed unique for one machine. Not for clusters/migrated db.
 

[otorres]

How do you generate GUIDs in a cross-database way that are guaranteed unique in the database

Functionality is built into Free Pascal.

Those are afaik only guaranteed unique for one machine. Not for clusters/migrated db.

GUID or UUID is guaranteed of a UniqueGUID,
The number of possible UUIDs is 340,282,366,920,938,463,463,374,607,431,768,211,456 (16³² or 2 ¹²⁸), or about 3.4 × 10³⁸

[howardpc]

GUID or UUID is guaranteed of a UniqueGUID,

A chance of 1 in 3.4 × 10³⁸ that a GUID might be duplicated on that machine is indeed a very low probabiblity. However, it is not a guarantee. That would require a duplication probability of zero.

[mse]

MSEgui doesn't use SQLdb for connection databases.

MSEgui uses a modified version of sqldb. I assume if desired the "refresh" functionality could be backported. For the Free Pascal team it is even simpler to implement because they can change db.pas which I couldn't at that time.

The whole problem of applyupdates is more than one records can be inserted / updated. I think 'INSERT INTO table (<fields>) values (<values>) returning <lastid>' can't work with more records at once.

AFAIK there is a separate INSERT statement for every inserted record in applyupdates.

[otorres]

GUID or UUID is guaranteed of a UniqueGUID,

A chance of 1 in 3.4 × 10³⁸ that a GUID might be duplicated on that machine is indeed a very low probabiblity. However, it is not a guarantee. That would require a duplication probability of zero.

not is on machine, is on the world machines, if you use the UUID generator on the Firebird server, is almost impossible to repeat.

[BigChimp]

Could somebody please explain to me the difference between a GUID derived from Firebird, Oracle, MSSQL Server or a GUID gotten from the OS which FPC presumably uses? Both should be equally unique or not unique.

If they're not, that's a bug.

[marcov]

Could somebody please explain to me the difference between a GUID derived from Firebird, Oracle, MSSQL Server or a GUID gotten from the OS which FPC presumably uses? Both should be equally unique or not unique.

Most GUIDs are guaranteed within a certain domain (say a machine), and only improbable outside. If the GUID is not within the domain of the DB, moving the db to a different system (or chaining together different machines) means the result is not guaranteed unique anymore.  IOW if you use GUIDs in your database, you want to do that within the domain of that database, not some unrelated machine it just happens to run on.

If they're not, that's a bug.

There is a difference between intention and practical implementation.

The only way to absolutely make sure that GUIDs are never duplicate is to keep a worldwide centralized list of all issued GUIDs, which I hope I don't have to explain is impractical.