Board index » delphi » Record number field

Record number field


2005-10-06 08:26:11 AM
delphi79
I am using Delphi 7 with Interbase and DB2, using TSqlConnection for
most commands and TSqlDataSet for retrieving records. In particular, I
am using an INSERT INTO to add records to a table. Most of my database
programming prior to now has been with Paradox and the BDE (which
always worked fine for me, but that is another story). Please bear with
me if the following is a bit SQL-naive.
No problem with SQL until now, except for a few tables where the
records are identical (essentially tests done on the same day with the
same results, where the only unique item is the time of the test, but
that is unknown). I don't want to use a field for the number of tests
to avoid this problem because of the way the table is updated.
I dealt with it in Paradox by having a "record number" field which was
the primary index, and when adding records, filled in that field by
calling the TTable.RecordCount. From what I read, this is not the best
approach for SQL (as I understand, if I effectively implement it on the
client side, the client could end up retrieving all the records across
the network, and if I implement it on the server side through a
metadata call, presumably the server will end up reading the whole
table, plus there is the issue of multiple clients simultaneously
adding records).
Is there an acceptable method that will give me the same effect in SQL?
I know little about triggers, stored procedures, and other features
that I did not have to use with BDE, but if i can be pointed in the
right direction, I will learn about them.
Ciaran
 
 

Re:Record number field

Both InterBase and DB2 provide a multi-user safe way to generate a
unique id. For InterBase the method is a trigger and a database object
called a generator. Look up generators in the Data Definition Guide and
you will find an explanation and examples.
With DB2, IIRC, there is an Identity property you can add to an integer
field.
If you are going to work with SQL database servers learning about
stored procedures and triggers is critical. They provide numerous
advantages including better performance and easier maintenance.
--
Bill Todd (TeamB)
 

Re:Record number field

Bill Todd writes:
Quote
Both InterBase and DB2 provide a multi-user safe way to generate a
unique id. For InterBase the method is a trigger and a database object
called a generator. Look up generators in the Data Definition Guide
and you will find an explanation and examples.

With DB2, IIRC, there is an Identity property you can add to an
integer field.

If you are going to work with SQL database servers learning about
stored procedures and triggers is critical. They provide numerous
advantages including better performance and easier maintenance.
Thank you very much, Bill. I will start reading right away :-)
Ciaran
 

Re:Record number field

Bill Todd writes:
Quote
Both InterBase and DB2 provide a multi-user safe way to generate a
unique id. For InterBase the method is a trigger and a database object
called a generator. Look up generators in the Data Definition Guide
and you will find an explanation and examples.

With DB2, IIRC, there is an Identity property you can add to an
integer field.

If you are going to work with SQL database servers learning about
stored procedures and triggers is critical. They provide numerous
advantages including better performance and easier maintenance.
It seems to get database-specific very quickly, and goes beyond
standard SQL. Performance is not going to be an issue for me in this
case whereas cross-database ability is, so I think I will just put the
record count in record 0.
Ciaran