another question

How i can update generated value for value ID - generated ?

like......  tblTable.FieldByName('ID').UPDATEVALUE


this is for not to have a KeyViolation error.

Mauro,

I'm not sure if I understood...
You want to change the existing values of the ID column, when you change the seed value ?

--
Fernando Dias
[Team Elevate]

> I'm not sure if I understood...
> You want to change the existing values of the ID column, when you change
> the seed value ?



( this record have 5000 in ID Value )

tblTable.CopyOnAppend := True;
tblTable.Append;

this new record don't have 50001 !!!!
but have 5000 in ID Value


Here ID - Generated is in key violation when post is called.

how i can update Generated value ?

Mauro,

The value for generated columns is generated at the moment of the Post, not
when the new row is Appended, so although the value for ID is being copied
when a new row is appended, it's going to be replaced by the GENERATED
value, as long as you define the generation option as ALWAYS AS IDENTITY and
not BY DEFAULT AS IDENTITY, as I think you did. So, you only have to change
the generation options:

ALTER TABLE "MyTable"
 ALTER COLUMN "ID" AS INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0,
INCREMENT BY 1)

--
Fernando Dias
[Team Elevate]

Mauro


What's the column definition? That determines wether or not you can alter the value.

eg

"_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,

or

"_ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1)

The first can be altered the second not.

I don't know of any way to then use SQL to alter the actual numbers used to the new sequence so it would have to be addressed programmatically in two passes.

Pass 1:

a) select a number greater than the last one that would be used with your new seed value and also greater than the last number already used.
b) make sure the table index is set to the column you are changing
c) go to the last row
d) in a while not table bof loop alter the column value start with the number you first thought of and decrement each loop

Pass 2:
a) determine what the new number for the last row should be using your new seed start value
b) make sure the table index is set to the column you are changing
c) go to the last row
d) in a while not table bof loop alter the column value start with the new number and decrement each loop

Finally use an ALTER TABLE to reset the seed value to the new one

I hope there is a simple sql command to do the above but I don't know it if there is.


Roy Lambert [Team Elevate]

> The value for generated columns is generated at the moment of the Post,
> not when the new row is Appended, so although the value for ID is being
> copied when a new row is appended, it's going to be replaced by the
> GENERATED value, as long as you define the generation option as ALWAYS AS
> IDENTITY and not BY DEFAULT AS IDENTITY, as I think you did. So, you only
> have to change the generation options:


i have in a table a ID Field GENERATED

COLUMN "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1,
INCREMENT BY 1) NOT NULL


i don't want use : ALWAYS AS IDENTITY , it a READ ONLY field !!

i need change it anytime...

Mauro,

Then, if you want to be able to change the ID values, but still want a new value to be generated, define it as GENERATED BY DEFAULT AS IDENTITY and then, just clear the value of ID after the Append:

MyTbl.Append;
MyTbl.FieldByName('Id').Clear;
....
....
MyTbl.Post;

--
Fernando Dias
[Team Elevate]