Our application keeps adding/updating/deleting fields based on the business requirements.  So, we have a way to manage that.  There is no problem
with that.  What happened since build 5 is every time the application deletes the table and reinserts the values again the values of the new integer
field that was created by code gets incremented!!


The field was created using this statement:

ALTER TABLE "TB_Users" ADD COLUMN SkinIndex Integer DEFAULT 0 AT 0;

Adbulaziz,

<< Our application keeps adding/updating/deleting fields based on the
business requirements.  So, we have a way to manage that.  There is no
problem with that.  What happened since build 5 is every time the
application deletes the table and reinserts the values again the values of
the new integer field that was created by code gets incremented!! >>

I'm not quite sure what you're saying.  Are you dropping the table
completely at some point, or just altering the table and adding/removing
columns ?  Are there any GENERATED/IDENTITY columns involved ?

--
Tim Young
Elevate Software
www.elevatesoft.com

<<I'm not quite sure what you're saying.  Are you dropping the table
completely at some point, or just altering the table and adding/removing
columns ?  Are there any GENERATED/IDENTITY columns involved ?>>

The case is simpler than the way I posted.  From time to time we keep adding some fields.  So our application has a version protocol where we keep
adding more fields to the db.  Now comes the heart of the question:  We have a test environment were we have some pre-created test cases and
during those tests our application failed.  The problem could be on our side, but after some test it appears that after adding one field to a table which
is an integer field every time the application delete all records and add them again the this new integer field increment the values.  This filed is
created as mentioned in the original post.  This happened only after EDB 1.04 build 4.  Earlier versions were ok.  As I said before this could be on
our side.  But there is no way to prove it until we test it with build 4.

Abdulaziz,

<< The problem could be on our side, but after some test it appears that
after adding one field to a table which
is an integer field every time the application delete all records and add
them again the this new integer field increment the values.  This filed is
created as mentioned in the original post.  This happened only after EDB
1.04 build 4.  Earlier versions were ok.  As I said before this could be on
our side.  But there is no way to prove it until we test it with build 4. >>

Is the integer column defined as a GENERATED..AS IDENTITY column ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<Is the integer column defined as a GENERATED..AS IDENTITY column ?>>

No..It is a normal integer field.

Tim,

It is a bug for sure.  I dropped the field and recreate it using the below statement and every time I add a new record using the DB Manager the field
get an incremented value.  At act like AutoInc field.

ALTER TABLE "TB_Users" ADD COLUMN SkinIndex Integer DEFAULT 0 AT 0;

OK..I got more news.  If I use the clause "AT" then the problem appears.  In other words, using the below statement to create the field prevent the
problem form happening.

ALTER TABLE "TB_Users" ADD COLUMN SkinIndex Integer DEFAULT 0;

Abdulaziz,

<< It is a bug for sure.  I dropped the field and recreate it using the
below statement and every time I add a new record using the DB Manager the
field get an incremented value.  At act like AutoInc field.

ALTER TABLE "TB_Users" ADD COLUMN SkinIndex Integer DEFAULT 0 AT 0; >>

You're using the wrong column index and EDB isn't stopping you.  The column
indexes are 1-based, not 0-based, so you're basically picking up the
internal row ID column.

This is the correct statement:

ALTER TABLE "TB_Users" ADD COLUMN SkinIndex Integer DEFAULT 0 AT 1;

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<You're using the wrong column index and EDB isn't stopping you.  The column
indexes are 1-based, not 0-based, so you're basically picking up the
internal row ID column.

This is the correct statement:

ALTER TABLE "TB_Users" ADD COLUMN SkinIndex Integer DEFAULT 0 AT 1;>>

Never thought of this because simply it was working with DBISAM3 and EDB 1.04 build < 5.  Any way thanks...