Delphi, How to add the Auto-Increment to DB Field?
Steven
I would like to know how to add the auto increment in db field? In
Delphi.
I know that Visual Dbase 5.5 for Win has this feature.
Thank you very much for any input.
Best regards,
Steven.
If you don't mind would you please also send email too.
Steve Koterski
: I would like to know how to add the auto increment in db field? In
The CreateTable method of the TTable only supports those features that are
common to all table types Delphi/BDE natively supports. The Auto-Incre-
ment field sub-type is specific to just Paradox tables, and so is not
supported by the general TTable component (in the CreateTable method,
however, if one is already present it will be used).
Barring creating a custom component based on the stock TTable component
that supports Paradox-specific features such as this, the only way to
create a Paradox table with an Auto-Increment field is through the Data-
base Desktop utility.
--
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/ Steve Koterski _/ The opinions expressed here are _/
_/ kote...@borland.com _/ exclusively my own _/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steven
>The CreateTable method of the TTable only supports those features that are
>common to all table types Delphi/BDE natively supports. The Auto-Incre-
>ment field sub-type is specific to just Paradox tables, and so is not
>supported by the general TTable component (in the CreateTable method,
>however, if one is already present it will be used).
>Barring creating a custom component based on the stock TTable component
>that supports Paradox-specific features such as this, the only way to
>create a Paradox table with an Auto-Increment field is through the Data-
>base Desktop utility.
Thank you Koterski for your reply,
I don't have Paradox in any version. I used to run Approach 3.0 and
use DBF files.
I just bought the Delphi, I was looking for auto-increment feature
but fail to find it.
Do you have any idea how to get the DBF or DB files include the
Auto-increment fields. How Borland lack out this feature?
Or how can I create the DBF or DB files in Delphi, without any Dbase
or Paradox.
Best regards,
Steven.
Peter Lipp
: Do you have any idea how to get the DBF or DB files include the
: Auto-increment fields.
You will have to do it yourself. Use the after-insert event to calculate
your value. Is (about) the same except you have to find out about the
value yourself. DBF-Files don't do that automagically. I use an INI-File to
store the value in.
: Or how can I create the DBF or DB files in Delphi, without any Dbase
: or Paradox.
With the Database Desktop it's no problem.
--
Dipl.Ing. Dr. Peter Lipp - Institute for Applied Information Processing
Technische Universitaet Graz, Austria (University of Technology, Graz, Austria)
pl...@iaik.tu-graz.ac.at
tno...@digital.net
>use DBF files.
>I just bought the Delphi, I was looking for auto-increment feature
>but fail to find it.
>or Paradox.
Well, this may sound like a kludge, but I have a solution to auto-increment
fields. What I do is simply create a float field in my table, and set its
value to Now. Now is an encoding of the Date and Time in a double value.
If the table is shared, you can place the post in a try-except loop which
re-updates the field and attempts the post until it is successful:
with theTable do
begin
success := false;
attempts := 0;
repeat
try
inc(attempts);
theField.Value := Now;
post;
success := true;
except
application.processMessages;
end;
until ( (success) or (attempts = 15) );
I don't know if the syntax on the repeat-until is exactly correct, but
something like this.
************************************
* The Nomad *
* tno...@digital.net *
************************************
Steve Koterski
[...]
: Thank you Koterski for your reply,
: I don't have Paradox in any version. I used to run Approach 3.0 and
: use DBF files.
: I just bought the Delphi, I was looking for auto-increment feature
: but fail to find it.
: Do you have any idea how to get the DBF or DB files include the
: Auto-increment fields. How Borland lack out this feature?
: Or how can I create the DBF or DB files in Delphi, without any Dbase
: or Paradox.
Delphi does support the Paradox AutoIncrement field. The dBASE file speci-
fication includes no similar field.
In Delphi, you cannot create a table with this type field programmatically,
but you can using the Database Desktop utility that comes with Delphi.
You do not have to have Paradox to use or create Paradox tables in Delphi.
Mike Frisch
: Delphi does support the Paradox AutoIncrement field. The dBASE file speci-
: fication includes no similar field.
What about Delphi support for auto-incrementing fields using the
local InterBase server? I have a trigger and generator set-up to create
the effect, but it doesn't seem to work well with Delphi. In my
application, I need the value of the key generated, but doing a
TTable.Refresh when the new record is posted to the database doesn't
update the TTable.Fields record. Any ideas? (I am using the SQL-Links
driver as well as the local InterBase server bundled with Delphi).
I have called your pay-for-tech-support line and he told me that
this should work, but it doesn't. My application is very simple...
table.append;
table.fields[0].asinteger := 0; { Since field needs a dummy value }
...
table.post;
table.refresh; { At this point, table.fields[0] is 1 when it should be
something like 30 in my database }
Thanks.
Mike.
--
====================================================================
Mike Frisch Email: mfr...@saturn.tlug.org
Northstar Technologies Compuserve: 76620,2534
Newmarket, Ontario, CANADA
Frank Korpershoek
mfr...@saturn.tlug.org (Mike Frisch) wrote:
>Steve Koterski (koterski@genghis) wrote:
>: Delphi does support the Paradox AutoIncrement field. The dBASE file speci-
>: fication includes no similar field.
>
> What about Delphi support for auto-incrementing fields using the
>local InterBase server? I have a trigger and generator set-up to create
>the effect, but it doesn't seem to work well with Delphi. In my
>application, I need the value of the key generated, but doing a
>TTable.Refresh when the new record is posted to the database doesn't
>update the TTable.Fields record. Any ideas? (I am using the SQL-Links
>driver as well as the local InterBase server bundled with Delphi).
> I have called your pay-for-tech-support line and he told me that
>this should work, but it doesn't. My application is very simple...
>
>table.append;
>table.fields[0].asinteger := 0; { Since field needs a dummy value }
>...
>table.post;
>table.refresh; { At this point, table.fields[0] is 1 when it should be
> something like 30 in my database }
Yep, it is possible to make it work, at least I have! If you check your
database with your database desktop, is the count value correct?
By the way, I didn't need to give it a dummy value. Also got some troubles
with refreshing. I had to put an afterPost event on my table which refreshes
the table...
greetz -frank korpershoek
Steve Koterski
: Steve Koterski (koterski@genghis) wrote:
: : Delphi does support the Paradox AutoIncrement field. The dBASE file speci-
: : fication includes no similar field.
: What about Delphi support for auto-incrementing fields using the
: local InterBase server? I have a trigger and generator set-up to create
: the effect, but it doesn't seem to work well with Delphi. In my
: application, I need the value of the key generated, but doing a
: TTable.Refresh when the new record is posted to the database doesn't
: update the TTable.Fields record. Any ideas? (I am using the SQL-Links
: driver as well as the local InterBase server bundled with Delphi).
By "doesn't work well" I assume you mean that you do not see the incre-
mented field value until going to another record and back? This effect
is caused by the different meanings of the term "insert" between Delphi
and InterBase. For Delphi, the act of inserting only meaning making
memory space and a blank row in a grid for the new data. It does not
in any way affect the underlying table. Only after you elect to post the
new row (either explicitly with a call to the Post method or implicitly
by changing the record pointer) does Delphi issue the INSERT command to
cause the newly added row to be added to the underlying table.
It is only when the INSERT command is sent from Delphi that your trigger
is actually being triggered. Thus, you do not see the value until after
the record has been officially posted.
The reason for this manner that Delphi effects an "insert" is to allow
for an easy way to account for the user backing out of the insert by
pressing Escape. If it was not handled in this manner and the "insert"
in Delphi immediately attempted an insert in the table, some transaction
processing would be needed, greatly complicating the process of the
end-user backing out of the insert process.
Instead of using a trigger for this auto-incrementing, you should instead
consider using a stored procedure. This stored procedure could then be
queried for the next value in the TTable/TQuery BeforeInsert event, and
you would plug this manually obtained value into the new row.
[...]
Mike Frisch
: By "doesn't work well" I assume you mean that you do not see the incre-
: mented field value until going to another record and back? This effect
Exactly.
: in any way affect the underlying table. Only after you elect to post the
: new row (either explicitly with a call to the Post method or implicitly
: by changing the record pointer) does Delphi issue the INSERT command to
: cause the newly added row to be added to the underlying table.
Shouldn't I be able to execute the TTable.Refresh method to
refresh the data currently in RAM? I would've expected this to re-read
the current (newly inserted) record and therefore update the
TTable.Fields structure. This would certainly make my life easier.
: Instead of using a trigger for this auto-incrementing, you should instead
: consider using a stored procedure. This stored procedure could then be
: queried for the next value in the TTable/TQuery BeforeInsert event, and
: you would plug this manually obtained value into the new row.
How do you propose I keep the next valid primary key value?
(Sorry if this may be obvious, but I am not at all familiar with stored
procedures). My previous method of keeping key values was simply keeping
a keys table which stores the table name and last used key value. My
only problem with this is that I (one day) might want to use the same
database with other applications and this is obviously not portable.
Thank you very kindly for the detailed response!
Steve Koterski
: : in any way affect the underlying table. Only after you elect to post the
: : new row (either explicitly with a call to the Post method or implicitly
: : by changing the record pointer) does Delphi issue the INSERT command to
: : cause the newly added row to be added to the underlying table.
: Shouldn't I be able to execute the TTable.Refresh method to
: refresh the data currently in RAM? I would've expected this to re-read
: the current (newly inserted) record and therefore update the
: TTable.Fields structure. This would certainly make my life easier.
The Refresh method of the TTable, TQuery, and TStoredProc components is
indeed designed to re-read records from the current data set. But if
you have yet to post a record, i.e., it is still in Insert mode, it will
not be part of the data set and thus will not be included in the refreshed
data. Only after a new record has been posted will it be subject to the
Refresh method. So this would still not help in trying to see incremented
values before the record is posted.
: : Instead of using a trigger for this auto-incrementing, you should instead
: : consider using a stored procedure. This stored procedure could then be
: : queried for the next value in the TTable/TQuery BeforeInsert event, and
: : you would plug this manually obtained value into the new row.
: How do you propose I keep the next valid primary key value?
: (Sorry if this may be obvious, but I am not at all familiar with stored
: procedures). My previous method of keeping key values was simply keeping
: a keys table which stores the table name and last used key value. My
: only problem with this is that I (one day) might want to use the same
: database with other applications and this is obviously not portable.
A stored procedure would execute a SELECT command, retrieving the max-
imum current value for the field. That maximum value would be incremented
in the stored procedure and then be returned as if it were an actual
field. The Delphi application would then plug that value into the field
for the incrementing value.
But there is a faster way to get this maximum value -- on the Delphi
side. Assuming that there is a primary key or an index on the increment
field, a second instance of the table could be contained in another
TTable component on the form. This TTable would not be connected to
a TDataSource, it would be there just to perform this search. The Last
method of the TTable would move the record pointer to the row with the
highest value, thereupon to be incremented and plugged into the field.
This use of the Last method would be considerably faster than issuing
an SQL statement to do the same thing.