Hi,

Please do the following in EDB Manager. It creates a "test" table and
alters it, so you can use any database you may have and drop the "test
"table afterwards. Create new script and insert the follwoing:

EXECUTE IMMEDIATE 'CREATE TABLE "test"(
"RecRef" INTEGER NOT NULL,
"Name" VARCHAR(24) COLLATE "UNI_CI" NOT NULL)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'INSERT INTO "test" VALUES (10, ''TEST'')';

EXECUTE IMMEDIATE 'ALTER TABLE "test" ALTER COLUMN RecRef AS INTEGER
GENERATED ALWAYS AS IDENTITY (START WITH 100, INCREMENT BY 1) NOT NULL';

EXECUTE IMMEDIATE 'ALTER TABLE "test" ADD CONSTRAINT "PKey" PRIMARY KEY
("RecRef")';

Execute the script and when done, open the test "table" and insert a
new record, edit the field Name and post the record. The field RecRef
takes value 1 which is NOT what I would expect after the column was
altered as above.

Am I doing something wrong?

--
regards
Lucian

Lucian


Just tried in b14 non-unicode and it works fine.

Roy Lambert

I'm doing Unicode but than ... crap, I'm still on b8
I'll install the latest, thanks
Lucian

>Just tried in b14 non-unicode and it works fine.

I tried b15 unicode. What I get in manager is this error, after I click Execute:
"ElevateDB Error #1103 An invalid or unknown request was sent to the server"
At this point EDB Manager won't close and have to kill it from task manager.

The table test is however created. But the issue remains. The new record has RecRef 1 instead of 100 what I expected as a result of

ALTER TABLE "test" ALTER COLUMN RecRef AS INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 100, INCREMENT BY 1) NOT NULL

regards
Lucian

Lucian


Sorry I've been doing some more experimenting and your observations are correct. It seems as though whatever seed value is set initially (or 1 if not set as a generated column) is there for eternity. Looks like a bug for Tim.

Roy Lambert [Team Elevate]

Lucian,

<< Execute the script and when done, open the test "table" and insert a new
record, edit the field Name and post the record. The field RecRef takes
value 1 which is NOT what I would expect after the column was altered as
above. ? >>

The START WITH seed value for the CREATE/ALTER TABLE statements, is only for
the catalog metadata that is used when creating the table for the first
time.  If you want to change an existing seed value, then you should use
this:

ALTER TABLE "test" ALTER COLUMN RecRef  RESTART WITH 100

This gets asked a lot, so I added a FAQ on this:

http://www.elevatesoft.com/supportfaq?action=view&category=edb&question=altering_identity_seed_value

--
Tim Young
Elevate Software
www.elevatesoft.com

Lucian,

<< I tried b15 unicode. What I get in manager is this error, after I click
Execute: >>

There is an issue with B15 when running scripts in the EDB Manager script
debugger, and there will be a fix shortly.  The entire server API had to be
changed around recently to accommodate the appearance of a .NET managed-code
version of the EDB Server/engine, and this bug showed up in B15 due to these
changes.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Any chance of sneaking in times for statements like CREATE TABLE AS in EDBManager when you sort this one out?

Roy Lambert

>ALTER TABLE "test" ALTER COLUMN RecRef  RESTART WITH 100

Hi Tim,

The thing is I am importing some DBISAM tables where RecRef is autoinc. It is supposed to be autoinc also in EDB, however for the import, I create the table(s) with RecRef INTEGER and only when I'm done with the import I am restructuring the tables. What exactly am I supposed to do if I want to obtain an autoinc column with a new lastautoinc value? From your answer I understand I have to call twice ALTER TABLE, once to get an autoinc column and second to reset the seed? If this is true, I understand it's a bug and it will get fixed, right?

thanks
Lucian

Lucian


What happens if you create the table with generate by default which should mean that existing values are transferred over and then alter to generated always after the transfer. That way you can use the correct seed value when creating the table.

Roy Lambert [Team Elevate]