Re: [firebird-support] New auto-increment column, existing data
On 15/11/2016 19:58, 'Some One' anotherpersonsomewh...@mail.com [firebird-support] wrote: > I am trying to add new auto-increment primary keys for existing table. > > This is what I have: > == > set term ~ ; > execute block > as > declare dbkey char(8); > begin > for > select rdb$db_key > from mytable > into :dbkey > do > begin > ... > end > end~ > set term ; ~ > == > This fails with error "malformed string", it looks like either > rdb$db_key does not match char(8) or maybe it is related to same > character set settings. Any suggestions? This is totally the wrong way to go about achieving an auto-incrementing key. The "field" rdb$db_key should never be used as a basis for anything persistent, as it is not stable. It is internally maintained by the engine according to some algorithm that I forget, related to the offset position of the record on disk and a few other things that cannot be assumed to be persistent. Read rdb$db_key within a transaction, if you have a reason to, for that is its only guaranteed "lifespan". Never try to write to it nor use it as as a base for anything that is relied on for integrity. Use the method Alan suggested or, if you are using Firebird 3, you can define your PK field using the IDENTITY data type (which can be any of the supported integer types and is maintained by an internal, non-user-acessible generator). > > It also seems that when new entries are added the auto-incremented > values will start from 0, causing conflicts. Is it ok to simply right > click the generator in FlameRobin, select "set value" and enter a value > higher than the highest existing or will that cause any trouble? > Using your method, it will invite a world of trouble. Using a proper method, you can set a generator (or sequence) or an IDENTITY to a specific value that will be treated as the most recently-generated value. How you do it depends on the Firebird version, i.e., pre-Firebird 3 or post-Firebird 3. Helen
Re: [firebird-support] New auto-increment column, existing data
Den 15.11.2016 20:58, skrev 'Some One' anotherpersonsomewh...@mail.com [firebird-support]: > I am trying to add new auto-increment primary keys for existing table. > > > This is what I have: > == > set term ~ ; > execute block > as > declare dbkey char(8); > begin > for >select rdb$db_key >from mytable >into :dbkey >do >begin > ... >end > end~ > set term ; ~ > == > This fails with error "malformed string", it looks like either rdb$db_key > does not match char(8) or maybe it is related to same character set settings. > Any suggestions? > > It also seems that when new entries are added the auto-incremented values > will start from 0, causing conflicts. Is it ok to simply right click the > generator in FlameRobin, select "set value" and enter a value higher than the > highest existing or will that cause any trouble? Page 9 and 10 of http://www.firebirdsql.org/file/community/conference-2014/pdf/22_tips_firebird_system_tables.pdf has a few interesting tips, one of them involving db_key. So maybe declare dbkey char(8) CHARACTER SET OCTETS; is the simplest answer to your question. SELECT GEN_ID(, ) FROM RDB$DATABASE should be a safe way to alter a sequence. I thought ALTER SEQUENCE also was fine, but looking at http://www.firebirdsql.org/refdocs/langrefupd20-alter-seq.html, there is a serious warning with no explanation. HTH, Set
Re: [firebird-support] New auto-increment column, existing data
Re: [firebird-support] New auto-increment column, existing data
There are a few things missing from your setup.
Try this; there may be other ideas for you too, but this works.
create a new table
CREATE TABLE A_AUTO_TEST (
NEW_FIELD INTEGER NOT NULL,
SOME_DATA CHAR(10)
);
ensure the field you want is the PK
ALTER TABLE A_AUTO_TEST ADD CONSTRAINT PK_A_AUTO_TEST PRIMARY KEY
(NEW_FIELD);
create a generator - that does the auto-increment for you
CREATE SEQUENCE GEN_A_AUTO_TEST_ID;
change it to whatever you want
ALTER SEQUENCE GEN_A_AUTO_TEST_ID RESTART WITH 3;
create a trigger, I've used a before insert
SET TERM ^ ;
/* Trigger: A_AUTO_TEST_BI0 */
CREATE OR ALTER TRIGGER A_AUTO_TEST_BI0 FOR A_AUTO_TEST
ACTIVE BEFORE INSERT POSITION 0
AS
begin
new.new_field=gen_id(gen_a_auto_test_id,1);
end
^
SET TERM ; ^
now go ahead and insert data
insert into a_auto_test (some_data)
values('a2')
Hope this helps.
Alan J Davies
Aldis
On 15/11/2016 19:58, 'Some One' anotherpersonsomewh...@mail.com
[firebird-support] wrote:
>
>
> I am trying to add new auto-increment primary keys for existing table.
>
> This is what I have:
> ==
> set term ~ ;
> execute block
> as
> declare dbkey char(8);
> begin
> for
> select rdb$db_key
> from mytable
> into :dbkey
> do
> begin
> ...
> end
> end~
> set term ; ~
> ==
> This fails with error "malformed string", it looks like either
> rdb$db_key does not match char(8) or maybe it is related to same
> character set settings. Any suggestions?
>
> It also seems that when new entries are added the auto-incremented
> values will start from 0, causing conflicts. Is it ok to simply right
> click the generator in FlameRobin, select "set value" and enter a value
> higher than the highest existing or will that cause any trouble?
>
>
[firebird-support] New auto-increment column, existing data
I am trying to add new auto-increment primary keys for existing table. This is what I have: == set term ~ ; execute block as declare dbkey char(8); begin for select rdb$db_key from mytable into :dbkey do begin ... end end~ set term ; ~ == This fails with error "malformed string", it looks like either rdb$db_key does not match char(8) or maybe it is related to same character set settings. Any suggestions? It also seems that when new entries are added the auto-incremented values will start from 0, causing conflicts. Is it ok to simply right click the generator in FlameRobin, select "set value" and enter a value higher than the highest existing or will that cause any trouble?
