Jack,

 

Once the generator produces an increment in the series of numbers it has no further concern as to what is done with that number. So, if there isn't a commit involving an insert using that sequence number you will have gaps in your sequencing.

 

If you need an auditable sequential series of ID's I highly recommend that you dedicate a specialized process to generate those numbers. This should be handled by a separate process running on a service application that assures this is serialized for all users.

 

For example, instead of trying to generate an invoice right from scratch, generate and order and have an order number that can tolerate gaps. Then, have a process that takes the order and converts it into an actual invoice under a more careful process that makes sure you end up with an auditable series of invoice numbers.

 

Hope this helps,

Jason

---

From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com] On Behalf Of Jack Cane
Sent: Sunday, March 16, 2014 6:34 AM
To: IBObjects ListServer
Subject: [IBO] Anomalous behavior of sql server

Good morning,

I use a Delphi app to scan image files for metadata and post data (not images) to fb table using an instance of TIB_dSql. This has been airtight for months. 

I just found that a select query fails to return all of the last dozen records recently posted. Although the sql management app is still under development, methods to form the dynamic query and post to FB have not been touched recently (as far as I know).

Select requests are passed to a web application on local machine for display of results on Chrome browser. Results (repeated in attached graphic):

PKs 1-204 are OK.

PKs 205, 208, 209, 211 and 215 are not assigned at all.

PKs 206, 207, 212-214 and 216 (last record on table) do not show up.

Questions:

1. Will Firebird periodically clean the trash and realign PK values so that there are no gaps?
2. What is my best approach to find reason for non-display of some records?

Best,

jwc