Mise à jour dBulletin N°12
Copié ici afin de sauvegarde du fil au cas ou le lien disparaitrait.
Groupes de discussion : dbase.programming
De : Jean-Pierre Martel <nos...@nospam.com>
Date : Sun, 5 Aug 2001 11:26:28 -0400
Date/heure locale : Dim 5 août 2001 17:26
Objet : Update of the article "The dBASE tables"
This in an update on the article "The dBASE tables" published in the
Beginner's Corner series, in our 12th issue.
"Append from..." vs AutoIncrement fields
When a table has an autoIncrement field, there is a flag in the table's
header that keeps track of the autoInc number (it stores the hexadecimal
value of the next AutoInc number). If we do a simple append (using xDML
Append command, not the Append from command), that flag is updated.
On the contrary, when records are added in lot (with the Append from
command), that flag is not updated. The target table inherits all the
autoInc values from the source table. The records are copied as such. The
autoInc flag in the table's header is not used nor updated. It is simply
ignored. Actually, the Append from command doesn't_usually_ update the
autoInc flag. Why "usually"? Because if we add the clause for true to the
Append from command (Append from... for true), then the flag will be used
and updated. Complicated? Yes, but there is more.
When all the records in a table are deleted (when a table is zapped), the
autoInc flag is not reset, nor is it decreased when the last saved record
is deleted. Let's see some practical examples, just to see if everything
is well understood.
use Source_Table // Source_Table has an autoIncrement field
copy structure to Target_Table
use Target_Table // which is empty for now
append from Source_Table
In that example, the autoInc numbers in the Target_Table are those found
in the Source_Table. Thereafter, the autoInc number given to the next
record added to the table with a simple Append command will be 1, all the
autoInc numbers already in the table being ignored since they were
written without changing the table's header autoInc flag. Moreover, if
the target table is appended in lot once again, but this time from
another table that has some identical autoInc value, we would end up with
duplicate values. This is where the for true clause is valuable. If we
had use the following code:
use Source_Table // Source_Table has an autoIncrement field
copy structure to Target_Table
use Target_Table // which is empty for now
append from Source_Table for true
In this case, the BDE will increment the autoInc number correctly in
Target_Table, starting from number one. Any record added individually
will bear the next autoInc number available.
Does it mean that the for true clause should always be used? Not
necessarily. There might be situations where we would want the autoInc
number in the source table be retained in the target table. Our problem
is that the first new record added thereafter will not get the following
autoInc number: how can this problem be avoided? Prior to the Append from
command, we just have to create (then delete) the number of records
needed to set properly the autoInc number in the table's header. For
example, if the autoInc number in the source table stops at 500 and thus
we want the next autoInc number to be 501 when a simple Append command
will be issued, we would use the following code:
use Source_Table // Source_Table has an autoIncrement field
copy structure to Target_Table
use Target_Table exclusive
// the following line of code will increment
// the autoInc flag in the table's header
generate 500
zap // this command needs exclusive use of the table
append from Source_Table
If we use a source table that doesn't have an autoIncrement field, to
append records in a target table that has such a field type, what will
happen? In that case, the autoIncrement field will be empty for all the
records added in lot. Thereafter, the first record to be added to the
target table with a simple Append command will bear the next autoInc
umber, as if the records added in lot didn't existed. That problem
would have been avoided with the for true clause.
If one of your tables is at risk of duplicate values because you did some
of the mistakes we spoke about, you could renumber the values in your
autoIncrement field. If you don't mind about the starting autoIncrement
value, the easiest solution is to use SQL:
Alter table Target_Table drop Field_Name
Alter table Target_Table add Field_Name AutoInc
In the dUFLP, is a file called DBF7File.cc which has the ability to
change the value of the autoincrement field. For example, the code needed
to set the next autoInc number to 325 in a table called "Target_Table",
would be:
set procedure to DBF7File.cc additive
oFile = new DBF7File("Target_Table.dbf")
oFile.setNextAutoIncValue("Field_Name", 325)
oFile = null
Jean-Pierre Martel, editor
The dBASE Developers Bulletin
Ivar B. Jessen
Autres options 6 août 2001, 15:47
Groupes de discussion : dbase.programming
De : bergisha...@it.dk (Ivar B. Jessen)
Date : Mon, 06 Aug 2001 13:45:52 GMT
Date/heure locale : Lun 6 août 2001 15:45
Objet : Re: Update of the article "The dBASE tables"
On Sun, 5 Aug 2001 11:26:28 -0400 , in dbase.programming , Update
of the article "The dBASE tables" , Jean-Pierre Martel
>"Append from..." vs AutoIncrement fields
>When a table has an autoIncrement field, there is a flag in the table's
>header that keeps track of the autoInc number (it stores the hexadecimal
>value of the next AutoInc number). If we do a simple append (using xDML
>Append command, not the Append from command), that flag is updated.
>On the contrary, when records are added in lot (with the Append from
>command), that flag is not updated. The target table inherits all the
>autoInc values from the source table. The records are copied as such. The
>autoInc flag in the table's header is not used nor updated. It is simply
>ignored. Actually, the Append from command doesn't_usually_ update the
>autoInc flag. Why "usually"? Because if we add the clause for true to the
>Append from command (Append from... for true), then the flag will be used
>and updated. Complicated? Yes, but there is more.
Jean-Pierre,
Looking at this with a beginners eyes I believe a small correction is
needed. When the target table has an AUTOINC field and one of the
remaining fields has the DEFAULT property set, the APPEND FROM will
result in _updated_ autoinc values in the target table.
>When all the records in a table are deleted (when a table is zapped), the
>autoInc flag is not reset, nor is it decreased when the last saved record
>is deleted. Let's see some practical examples, just to see if everything
>is well understood.
> use Source_Table // Source_Table has an autoIncrement field
> copy structure to Target_Table
> use Target_Table // which is empty for now
> append from Source_Table
>In that example, the autoInc numbers in the Target_Table are those found
>in the Source_Table.
If the source table has a field with the DEFAULT property set your
example still works correctly, but that is only because COPY STRUCTURE
TO (or for that matter COPY TO) doesn't copy the default property to
the target table. If a beginner creates the taget table by the command
'COPY TABLE sourceTable TO targetTable' he will find that the autoinc
values in the target table will be updated.
Notice that when starting out with the target table and creating the
source table by a COPY command, it doesn't matter which of the COPY
commands is used.
All this very confusing to us beginners and should maybe be mentioned
in your text :-)
[snip]
>If we use a source table that doesn't have an autoIncrement field, to
>append records in a target table that has such a field type, what will
>happen?
As a beginner I had a hard time trying to find a command which would
allow me to USE a source table and APPEND into a target table :-)
Did you mean something like this?
"If we use a target table that has an autoIncrement field and append
records to the table from a source table that doesn't have an
autoIncrement field, what will happen?".
Ivar B. Jessen
Chris Stoeckert Afficher le profil Traduire en FrançaisTraduit (Afficher
l'original)
Autres options 6 août 2001, 21:39
Groupes de discussion : dbase.programming
De : "Chris Stoeckert" <cstoeck...@email.com>
Date : Sun, 5 Aug 2001 21:46:54 -0700
Date/heure locale : Lun 6 août 2001 06:46
Objet : Re: Update of the article "The dBASE tables"
Hi Jean-Pierre,
Thanks for the "The dBase tables" article update. It is very timely as I am
trying to accomplish something with a table and autoinc field -- that I
really could use help with.
I have an application with that uses a parent and child tables. The parent
has an Autoinc field and the child a matching Long field. Everything works
fine until I try to backup and restore the tables.
Not being aware of a better method, I use "CopyTable" to backup both tables
(e.g. to a floppy drive). Then I use "EmptyTable" and "Append from" to
restore the backed up records to their original location. This works OK too,
except that the Autoinc field in the parent table does not accept the data
stored in the backed up table, but increments from the last number it stored
prior to being emptied. Obviously the new Autoinc field no longer matches
the Long field in the child table and the relationship is broken.
I hope this explanation makes sense <g>. If so, do you have any suggestions
for keeping the parent table's Autoinc and Child table's Long field in sync?
Or, can you suggest a better method for achieving my objective here?
TIA, Chris
Jean-Pierre Martel Afficher le profil Traduire en FrançaisTraduit
(Afficher l'original)
Autres options 7 août 2001, 05:22
Groupes de discussion : dbase.programming
De : Jean-Pierre Martel <nos...@nospam.com>
Date : Mon, 6 Aug 2001 23:18:32 -0400
Date/heure locale : Mar 7 août 2001 05:18
Objet : Re: Update of the article "The dBASE tables"
> When the target table has an AUTOINC field and one of the
> remaining fields has the DEFAULT property set, the APPEND
> FROM will result in _updated_ autoinc values in the target
> table.
Thanks Ivar. I was not aware of this tip. So I made some experiments.
When one of remaining fields has its Default property set _while_ that
table is not empty, the values in the autoIncrement field are reset
(starting from a value of 1) as soon as we quit the Table designer, as if
we had deleted, then created a new autoIncrement field. Thereafter, as
you said, the Default property protects the integrity of the
autoIncrement field. When we modify the structure again in order to take
off the default value in that field, the protection is lifted.
> If a beginner creates the taget table by the command
> 'COPY TABLE sourceTable TO targetTable' he will find
> that the autoinc values in the target table will be updated.
Yes, because with 'Copy Table', the target table's header is a perfect
copy of the source table's header (that includes the autoInc flag).
> All this very confusing to us beginners and should maybe be
> mentioned in your text :-)
Ivar, your very long career as a dBASE beginners begins to look
suspicious: are you sure you are not a pro by any chance?<g>
> As a beginner I had a hard time trying to find a command
> which would allow me to USE a source table and APPEND into
> a target table :-)
Old beginners like you and me had this problems because we started by
using xDML. People learning dBL these days don't have this problem
because OODML is free from those complications.
> Did you mean something like this?
> "If we use a target table that has an autoIncrement field and append
> records to the table from a source table that doesn't have an
> autoIncrement field, what will happen?".
Translated by my proof-reader (Flip Young), the same sentence has become:
"If we start with a source table that doesn't have an autoIncrement field
and append its records into a target table that does have such a field
type, what will happen?"
Jean-Pierre Martel, editor
The dBASE Developers Bulletin
Jean-Pierre Martel Afficher le profil Traduire en FrançaisTraduit
(Afficher l'original)
Autres options 7 août 2001, 05:49
Groupes de discussion : dbase.programming
De : Jean-Pierre Martel <nos...@nospam.com>
Date : Mon, 6 Aug 2001 23:41:35 -0400
Date/heure locale : Mar 7 août 2001 05:41
Objet : Re: Update of the article "The dBASE tables"
> I hope this explanation makes sense <g>.
Yes it does.
> If so, do you have any suggestions for keeping the parent
> table's Autoinc and Child table's Long field in sync?
> Or, can you suggest a better method for achieving my objective here?
A first suggestion would be to backup often but to restore when needed
(when there is table corruption for example). If you restore using
"Append from" without the "for true" clause, you should have a perfect
restoration, except that you have to reset the autoInc flag with
DBF7File.cc
The code could be something like:
local r, next_autoInc_No
r = form.rowset
r.last()
next_autoInc_No = r.fields["Field_name"].value + 1
set procedure to DBF7File.cc additive
oFile = new DBF7File("Target_Table.dbf")
oFile.setNextAutoIncValue("Field_Name", next_autoInc_No)
oFile = null
Jean-Pierre Martel, editor
The dBASE Developers Bulletin
Ivar B. Jessen
Jean-Pierre,
Looking at this with a beginners eyes I believe a small correction is
needed. When the target table has an AUTOINC field and one of the
remaining fields has the DEFAULT property set, the APPEND FROM will
result in _updated_ autoinc values in the target table.
>When all the records in a table are deleted (when a table is zapped), the
>autoInc flag is not reset, nor is it decreased when the last saved record
>is deleted. Let's see some practical examples, just to see if everything
>is well understood.
> use Source_Table // Source_Table has an autoIncrement field
> copy structure to Target_Table
> use Target_Table // which is empty for now
> append from Source_Table
>In that example, the autoInc numbers in the Target_Table are those found
>in the Source_Table.
If the source table has a field with the DEFAULT property set your
example still works correctly, but that is only because COPY STRUCTURE
TO (or for that matter COPY TO) doesn't copy the default property to
the target table. If a beginner creates the taget table by the command
'COPY TABLE sourceTable TO targetTable' he will find that the autoinc
values in the target table will be updated.
Notice that when starting out with the target table and creating the
source table by a COPY command, it doesn't matter which of the COPY
commands is used.
All this very confusing to us beginners and should maybe be mentioned
in your text :-)
[snip]
>If we use a source table that doesn't have an autoIncrement field, to
>append records in a target table that has such a field type, what will
>happen?
As a beginner I had a hard time trying to find a command which would
allow me to USE a source table and APPEND into a target table :-)
Did you mean something like this?
"If we use a target table that has an autoIncrement field and append
records to the table from a source table that doesn't have an
autoIncrement field, what will happen?".
Ivar B. Jessen
Chris Stoeckert
Hi Jean-Pierre,
Thanks for the "The dBase tables" article update. It is very timely as I am
trying to accomplish something with a table and autoinc field -- that I
really could use help with.
I have an application with that uses a parent and child tables. The parent
has an Autoinc field and the child a matching Long field. Everything works
fine until I try to backup and restore the tables.
Not being aware of a better method, I use "CopyTable" to backup both tables
(e.g. to a floppy drive). Then I use "EmptyTable" and "Append from" to
restore the backed up records to their original location. This works OK too,
except that the Autoinc field in the parent table does not accept the data
stored in the backed up table, but increments from the last number it stored
prior to being emptied. Obviously the new Autoinc field no longer matches
the Long field in the child table and the relationship is broken.
I hope this explanation makes sense <g>. If so, do you have any suggestions
for keeping the parent table's Autoinc and Child table's Long field in sync?
Or, can you suggest a better method for achieving my objective here?
TIA, Chris
Jean-Pierre Martel
Groupes de discussion : dbase.programming
De : Jean-Pierre Martel <nos...@nospam.com>
Date : Mon, 6 Aug 2001 23:18:32 -0400
Date/heure locale : Mar 7 août 2001 05:18
Objet : Re: Update of the article "The dBASE tables"
> When the target table has an AUTOINC field and one of the
> remaining fields has the DEFAULT property set, the APPEND
> FROM will result in _updated_ autoinc values in the target
> table.
Thanks Ivar. I was not aware of this tip. So I made some experiments.
When one of remaining fields has its Default property set _while_ that
table is not empty, the values in the autoIncrement field are reset
(starting from a value of 1) as soon as we quit the Table designer, as if
we had deleted, then created a new autoIncrement field. Thereafter, as
you said, the Default property protects the integrity of the
autoIncrement field. When we modify the structure again in order to take
off the default value in that field, the protection is lifted.
> If a beginner creates the taget table by the command
> 'COPY TABLE sourceTable TO targetTable' he will find
> that the autoinc values in the target table will be updated.
Yes, because with 'Copy Table', the target table's header is a perfect
copy of the source table's header (that includes the autoInc flag).
> All this very confusing to us beginners and should maybe be
> mentioned in your text :-)
Ivar, your very long career as a dBASE beginners begins to look
suspicious: are you sure you are not a pro by any chance?<g>
> As a beginner I had a hard time trying to find a command
> which would allow me to USE a source table and APPEND into
> a target table :-)
Old beginners like you and me had this problems because we started by
using xDML. People learning dBL these days don't have this problem
because OODML is free from those complications.
> Did you mean something like this?
> "If we use a target table that has an autoIncrement field and append
> records to the table from a source table that doesn't have an
> autoIncrement field, what will happen?".
Translated by my proof-reader (Flip Young), the same sentence has become:
"If we start with a source table that doesn't have an autoIncrement field
and append its records into a target table that does have such a field
type, what will happen?"
Jean-Pierre Martel, editor
The dBASE Developers Bulletin
> I hope this explanation makes sense <g>.
Yes it does.
> If so, do you have any suggestions for keeping the parent
> table's Autoinc and Child table's Long field in sync?
> Or, can you suggest a better method for achieving my objective here?
A first suggestion would be to backup often but to restore when needed
(when there is table corruption for example). If you restore using
"Append from" without the "for true" clause, you should have a perfect
restoration, except that you have to reset the autoInc flag with
DBF7File.cc
The code could be something like:
local r, next_autoInc_No
r = form.rowset
r.last()
next_autoInc_No = r.fields["Field_name"].value + 1
set procedure to DBF7File.cc additive
oFile = new DBF7File("Target_Table.dbf")
oFile.setNextAutoIncValue("Field_Name", next_autoInc_No)
oFile = null
Jean-Pierre Martel, editor
The dBASE Developers Bulletin
Ivar B. Jessen
Groupes de discussion : dbase.programming
De : bergisha...@it.dk (Ivar B. Jessen)
Date : Tue, 07 Aug 2001 17:13:25 GMT
Date/heure locale : Mar 7 août 2001 19:13
Objet : Re: Update of the article "The dBASE tables"
>Thanks Ivar. I was not aware of this tip. So I made some experiments.
>When one of remaining fields has its Default property set _while_ that
>table is not empty, the values in the autoIncrement field are reset
>(starting from a value of 1) as soon as we quit the Table designer, as if
>we had deleted, then created a new autoIncrement field. Thereafter, as
>you said, the Default property protects the integrity of the
>autoIncrement field. When we modify the structure again in order to take
>off the default value in that field, the protection is lifted.
Aha, an interesting variation I didn't know about.
[snip]
>Ivar, your very long career as a dBASE beginners begins to look
>suspicious: are you sure you are not a pro by any chance?<g>
A pro-beginner?
Ivar B. Jessen
Jean-Pierre Martel
Groupes de discussion : dbase.programming
De : Jean-Pierre Martel <nos...@nospam.com>
Date : Tue, 7 Aug 2001 15:36:37 -0400
Date/heure locale : Mar 7 août 2001 21:36
Objet : Re: Update of the article "The dBASE tables"
> > Ivar, your very long career as a dBASE beginners begins
> > to look suspicious: are you sure you are not a pro
> > by any chance?<g>
> A pro-beginner?
LOL. There is always something else to learn about dBL. That's what keeps
us young, I guess. <g>
Jean-Pierre Martel, editor
The dBASE Developers Bulletin
Chris Stoeckert
> A first suggestion would be to backup often but to restore when needed
> (when there is table corruption for example). If you restore using
> "Append from" without the "for true" clause, you should have a
perfect
> restoration, except that you have to reset the autoInc flag with
> DBF7File.cc
Good Idea! My current method is use a DOS Command (e.g. run copy *.dbf ,
etc.) -- for emergencies only <g>.
> The code could be something like:
> local r, next_autoInc_No
> r = form.rowset
> r.last()
> next_autoInc_No = r.fields["Field_name"].value + 1
> set procedure to DBF7File.cc additive
> oFile = new DBF7File("Target_Table.dbf")
> oFile.setNextAutoIncValue("Field_Name", next_autoInc_No)
> oFile = null
The above code looks like it should do the trick. I'll try it soon..
Thanks again, Chris
Jean-Pierre Martel
Groupes de discussion : dbase.programming
De : Jean-Pierre Martel <nos...@nospam.com>
Date : Wed, 8 Aug 2001 23:03:53 -0400
Date/heure locale : Jeu 9 août 2001 05:03
Objet : Re: Update of the article "The dBASE tables"
> The above code looks like it should do the trick. I'll try
> it soon.. Thanks again, Chris
You are welcome. Good luck.
Jean-Pierre Martel, editor
The dBASE Developers Bulletin