[firebird-support] New auto-increment column, existing data

Discussion:

'Some One' anotherpersonsomewhere@mail.com [firebird-support]

2016-11-15 19:58:40 UTC

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?

Alan J Davies Alan.Davies@aldis-systems.co.uk [firebird-support]

2016-11-15 21:30:42 UTC

Permalink

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' ***@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?
>
>

'Some One' anotherpersonsomewhere@mail.com [firebird-support]

2016-11-15 21:44:10 UTC

Permalink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
</head>

<body style="background-color: #fff;">
<span style="display:none"> </span>


<div id="ygrp-mlmsg" style="position:relative;">
<div id="ygrp-msg" style="z-index: 1;">


<div id="ygrp-text" >

<p><div style="font-family: Verdana;font-size: 12.0px;"><div>
<div>Thanks, but table/generator/trigger already exists and seems to be ok, my problem is to update the field for existing data. (I used FlameRobin to add the field). It is not set to primary key yet however because I need to insert unique values for the existing data first.</div>

<div>Sorry for being unclear about that.</div>

<div> 
<div name="quote" style="margin:10px 5px 5px 10px;padding: 10px 0 10px 10px;border-left:2px solid #C3D9E5;">
<div style="margin:0 0 10px 0;"><b>Sent:</b> Tuesday, November 15, 2016 at 10:30 PM<br>
<b>From:</b> "Alan J Davies ***@aldis-systems.co.uk [firebird-support]" <firebird-***@yahoogroups.com><br>
<b>To:</b> firebird-***@yahoogroups.com<br>
<b>Subject:</b> Re: [firebird-support] New auto-increment column, existing data</div>

<div name="quoted-content">
<div style="background-color: rgb(255,255,255);"><span> </span>

<div id="ygrp-mlmsg">
<div id="ygrp-msg">
<div id="ygrp-text">
<p>There are a few things missing from your setup.<br>
Try this; there may be other ideas for you too, but this works.<br>
create a new table<br>
<br>
CREATE TABLE A_AUTO_TEST (<br>
NEW_FIELD INTEGER NOT NULL,<br>
SOME_DATA CHAR(10)<br>
);<br>
<br>
ensure the field you want is the PK<br>
<br>
ALTER TABLE A_AUTO_TEST ADD CONSTRAINT PK_A_AUTO_TEST PRIMARY KEY<br>
(NEW_FIELD);<br>
<br>
create a generator - that does the auto-increment for you<br>
<br>
CREATE SEQUENCE GEN_A_AUTO_TEST_ID;<br>
change it to whatever you want<br>
ALTER SEQUENCE GEN_A_AUTO_TEST_ID RESTART WITH 3;<br>
<br>
create a trigger, I've used a before insert<br>
<br>
SET TERM ^ ;<br>
<br>
/* Trigger: A_AUTO_TEST_BI0 */<br>
CREATE OR ALTER TRIGGER A_AUTO_TEST_BI0 FOR A_AUTO_TEST<br>
ACTIVE BEFORE INSERT POSITION 0<br>
AS<br>
begin<br>
new.new_field=gen_id(gen_a_auto_test_id,1);<br>
end<br>
^<br>
<br>
SET TERM ; ^<br>
<br>
now go ahead and insert data<br>
<br>
insert into a_auto_test (some_data)<br>
values('a2')<br>
<br>
Hope this helps.<br>
<br>
Alan J Davies<br>
Aldis<br>
<br>
On 15/11/2016 19:58, 'Some One' ***@mail.com<br>
[firebird-support] wrote:<br>
><br>
><br>
> I am trying to add new auto-increment primary keys for existing table.<br>
><br>
> This is what I have:<br>
> ======================================<br>
> set term ~ ;<br>
> execute block<br>
> as<br>
> declare dbkey char(8);<br>
> begin<br>
> for<br>
> select rdb$db_key<br>
> from mytable<br>
> into :dbkey<br>
> do<br>
> begin<br>
> ...<br>
> end<br>
> end~<br>
> set term ; ~<br>
> ======================================<br>
> This fails with error "malformed string", it looks like either<br>
> rdb$db_key does not match char(8) or maybe it is related to same<br>
> character set settings. Any suggestions?<br>
><br>
> It also seems that when new entries are added the auto-incremented<br>
> values will start from 0, causing conflicts. Is it ok to simply right<br>
> click the generator in FlameRobin, select "set value" and enter a value<br>
> higher than the highest existing or will that cause any trouble?<br>
><br>
></p>
</div>

<div style="color: rgb(255,255,255);height: 0;"></div>

</div>
</div>
</div>
</div>
</div></div>
</div></div></p>

</div>


<div style="color: #fff; height: 0;">__._,_.___</div>

<div style="clear:both"> </div>

<div id="fromDMARC" style="margin-top: 10px;">
<hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
Posted by: "Some One" <***@mail.com> <hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
</div>
<div style="clear:both"> </div>

<table cellspacing=4px style="margin-top: 10px; margin-bottom: 10px; color: #2D50FD;">
<tbody>
<tr>
<td style="font-size: 12px; font-family: arial; font-weight: bold; padding: 7px 5px 5px;" >
<a style="text-decoration: none; color: #2D50FD" href="https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/129986;_ylc=X3oDMTJydmZ0ZmlzBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzEyOTk4NgRzZWMDZnRyBHNsawNycGx5BHN0aW1lAzE0NzkyNDYyNTE-?act=reply&messageNum=129986">Reply via web post</a>
</td>
<td>•</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;" >
<a href="mailto:***@mail.com?subject=Re%3A%20%5Bfirebird-support%5D%20New%20auto-increment%20column%2C%20existing%20data" style="text-decoration: none; color: #2D50FD;">
Reply to sender </a>
</td>
<td>•</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;">
<a href="mailto:firebird-***@yahoogroups.com?subject=Re%3A%20%5Bfirebird-support%5D%20New%20auto-increment%20column%2C%20existing%20data" style="text-decoration: none; color: #2D50FD">
Reply to group </a>
</td>
<td>•</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;" >
<a href="https://groups.yahoo.com/neo/groups/firebird-support/conversations/newtopic;_ylc=X3oDMTJlOTh0NTQzBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA250cGMEc3RpbWUDMTQ3OTI0NjI1MQ--" style="text-decoration: none; color: #2D50FD">Start a New Topic</a>
</td>
<td>•</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;color: #2D50FD;" >
<a href="https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/129984;_ylc=X3oDMTM4ZXZpbjhiBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzEyOTk4NgRzZWMDZnRyBHNsawN2dHBjBHN0aW1lAzE0NzkyNDYyNTEEdHBjSWQDMTI5OTg0" style="text-decoration: none; color: #2D50FD;">Messages in this topic</a>
(3)
</td>
</tr>
</tbody>
</table>

<div id="megaphoneModule">
<hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
<div>
<div class="stream" style="margin-bottom:10px;">
<div style="background-color:white;">
<div class="sn-img" style="display:inline;"><img name="tn_file" style="padding:0px 10px;vertical-align:top;margin-top:5px;" src="https://s.yimg.com/ru/static/images/yg/img/megaphone/1464031581_phpFA8bON" height="82" width="82"></div>
<div class="mod-txt" style="display:inline-block;">
<a rel="nofollow" name="sub_url" target="_blank" href="https://yho.com/1wwmgg" style="color:#0000FF;display:block;margin-left:5px;text-decoration:none;"><span style="font-size:15px;">Have you tried the highest rated email app?</span></a>
<div style="max-width:530px;padding:2px 5px;">With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.</div>
</div>
</div>
</div> </div>

<hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
</div>





<div id="ygrp-grfd" style="font-family: Verdana; font-size: 12px; padding: 15px 0;">



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++<BR>
<BR>
Visit <a href="http://www.firebirdsql.org">http://www.firebirdsql.org</a> and click the Documentation item<BR>
on the main (top) menu.  Try FAQ and other links from the left-side menu there.<BR>
<BR>
Also search the knowledgebases at <a href="http://www.ibphoenix.com/resources/documents/">http://www.ibphoenix.com/resources/documents/</a> <BR>
<BR>
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


</div>


<div id="ygrp-vital" style="background-color: #f2f2f2; font-family: Verdana; font-size: 10px; margin-bottom: 10px; padding: 10px;">

<span id="vithd" style="font-weight: bold; color: #333; text-transform: uppercase; "><a href="https://groups.yahoo.com/neo/groups/firebird-support/info;_ylc=X3oDMTJlYWI3Y2g2BF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTQ3OTI0NjI1MQ--" style="text-decoration: none;">Visit Your Group</a></span>

<ul style="list-style-type: none; margin: 0; padding: 0; display: inline;">
<li style="border-right: 1px solid #000; font-weight: 700; display: inline; padding: 0 5px; margin-left: 0;">
<span class="cat"><a href="https://groups.yahoo.com/neo/groups/firebird-support/members/all;_ylc=X3oDMTJmcnVvYmI3BF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzE0NzkyNDYyNTE-" style="text-decoration: none;">New Members</a></span>
<span class="ct" style="color: #ff7900;">6</span>
</li>
</ul>
</div>

<div id="ft" style="font-family: Arial; font-size: 11px; margin-top: 5px; padding: 0 2px 0 0; clear: both;">
<a href="https://groups.yahoo.com/neo;_ylc=X3oDMTJkbDM5NWphBF9TAzk3NDc2NTkwBGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA2dmcARzdGltZQMxNDc5MjQ2MjUx" style="float: left;"><img src="http://l.yimg.com/ru/static/images/yg/img/email/new_logo/logo-groups-137x15.png" height="15" width="137" alt="Yahoo! Groups" style="border: 0;"/></a>
<div style="color: #747575; float: right;"> • <a href="https://info.yahoo.com/privacy/us/yahoo/groups/details.html" style="text-decoration: none;">Privacy</a> • <a href="mailto:firebird-support-***@yahoogroups.com?subject=Unsubscribe" style="text-decoration: none;">Unsubscribe</a> • <a href="https://info.yahoo.com/legal/us/yahoo/utos/terms/" style="text-decoration: none;">Terms of Use</a> </div>
</div>
<br>



</div> 



<div id="ygrp-sponsor" style="width:160px; float:right; clear:none; margin:0 0 25px 0; background: #fff;">


<div id="ygrp-reco">
</div>


</div> 

<div style="clear:both; color: #FFF; font-size:1px;">.</div>
</div>

<img src="http://geo.yahoo.com/serv?s=97359714/grpId=2442406/grpspId=1705115386/msgId=129986/stime=1479246251" width="1" height="1"> <br>

<img src="http://y.analytics.yahoo.com/fpc.pl?ywarid=515FB27823A7407E&a=10001310322279&js=no&resp=img&cf12=CP" width="1" height="1">

<div style="color: #fff; height: 0;">__,_._,___</div>


</body>


<head>
<style type="text/css">

</style>
</head>


</html>

Helen Borrie helebor@iinet.net.au [firebird-support]

2016-11-15 23:22:11 UTC

Permalink

On 15/11/2016 19:58, 'Some One' ***@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

setysvar setysvar@gmail.com [firebird-support]

2016-11-15 22:26:45 UTC

Permalink

Den 15.11.2016 20:58, skrev 'Some One' ***@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(<generator_name>, <a value greater than or equal to 0>)
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

Alain Bastien alainbastien@gmail.com [firebird-support]

2016-11-16 10:28:03 UTC

Permalink

Use generators

*Kind Regards*

*Alain Bastien*

*34 Dr Ross AvenueRose Hill 72102MauritiusMobile Tel: +230 5 719 30 30*
*Skype:alainbastien*
*Viber: 7320143*

On Tue, Nov 15, 2016 at 11:58 PM, 'Some One' ***@mail.com
[firebird-support] <firebird-***@yahoogroups.com> 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?
>
>

Ann Harrison aharrison@ibphoenix.com [firebird-support]

2016-11-16 11:26:12 UTC

Permalink

> On Nov 15, 2016, at 3:58 PM, 'Some One' ***@mail.com [firebird-support] <firebird-***@yahoogroups.com> wrote:
>
> I am trying to add new auto-increment primary keys for existing table.
> .....
> select rdb$db_key
> from mytable
> into :dbkey
> .....

As several people have written generators or sequences are the right way to create unique values for primary keys. They can be started at a value of your choice. The syntax depends on the Firebird version but checking the release notes or on-line documentation for Generator or Sequence will get you the right answer.

The rdb$db_key is a unique identifier for a record at a particular time, but is not stable over time or across backup/restore cycles. If you store the rdb$db_key as your primary key, eventually you will will get duplicate key errors when you try to store new records. For the hard core, the rdb$db_key uniquely identifies a record in a table - different records in different tables will have the same rdb$db_key. It's a three part value. The first part is the sequence of the pointer page for the table that holds the actual database page number that holds the record. Pointer pages are pages that hold an array of page numbers - pretty simple. The second part is the offset on the pointer page of the actual data page number. The third part is the identity of the index on that data page that describes the location and length of the record. (Records are stored with run-length compression, so their length varies.)

When a record is deleted, its rdb$db_key will be reused. When a database is backed up with gbak and restored, gbak creates a new empty database and stores all the backed up record into it. Unless the database is very stable, records will be stored in different places, resulting in different rdb$db_key values.

In short, do not store rdb$db_key values in your database!

> This fails with error "malformed string", it looks like either rdb$db_key does not match char(8)
>
Right. The rdb$db_key is eight bytes of binary some of which will not be valid characters in any normal character set. If you want to use the rdb$db_key as a short-term locator value, use character set Octets which is value agnostic.

Good luck,

Ann
>
>
>
>

'Some One' anotherpersonsomewhere@mail.com [firebird-support]

2016-11-16 21:06:12 UTC

Permalink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
</head>

<body style="background-color: #fff;">
<span style="display:none"> </span>


<div id="ygrp-mlmsg" style="position:relative;">
<div id="ygrp-msg" style="z-index: 1;">


<div id="ygrp-text" >

<p><div style="font-family: Verdana;font-size: 12.0px;"><div>As said I was a little unclear in my first post, my problem is not to create the autoincrementing field which most of your replies is about but to handle existing table entries. (Neither will I use the rdb$db_key for something persistent.)</div>

<div>However the "character set octets" solved my problem and I also found out how to update the generator value regarding the existing items. Here is my working version:</div>

<div> </div>

<div>set term ~ ;<br>
execute block<br>
as<br>
 declare dbkey char(8) character set octets;<br>
begin<br>
 for<br>
 select rdb$db_key<br>
 from mytable<br>
 into :dbkey<br>
 do<br>
 begin<br>
  update mytable set id = next value for mygenerator<br>
  where rdb$db_key = :dbkey;<br>
 end<br>
end~<br>
set term ; ~</div></div>
</p>

</div>


<div style="color: #fff; height: 0;">__._,_.___</div>

<div style="clear:both"> </div>

<div id="fromDMARC" style="margin-top: 10px;">
<hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
Posted by: "Some One" <***@mail.com> <hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
</div>
<div style="clear:both"> </div>

<table cellspacing=4px style="margin-top: 10px; margin-bottom: 10px; color: #2D50FD;">
<tbody>
<tr>
<td style="font-size: 12px; font-family: arial; font-weight: bold; padding: 7px 5px 5px;" >
<a style="text-decoration: none; color: #2D50FD" href="https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/129993;_ylc=X3oDMTJycjYydmI5BF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzEyOTk5MwRzZWMDZnRyBHNsawNycGx5BHN0aW1lAzE0NzkzNjQ0OTY-?act=reply&messageNum=129993">Reply via web post</a>
</td>
<td>•</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;" >
<a href="mailto:***@mail.com?subject=Re%3A%20%5Bfirebird-support%5D%20New%20auto-increment%20column%2C%20existing%20data" style="text-decoration: none; color: #2D50FD;">
Reply to sender </a>
</td>
<td>•</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;">
<a href="mailto:firebird-***@yahoogroups.com?subject=Re%3A%20%5Bfirebird-support%5D%20New%20auto-increment%20column%2C%20existing%20data" style="text-decoration: none; color: #2D50FD">
Reply to group </a>
</td>
<td>•</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;" >
<a href="https://groups.yahoo.com/neo/groups/firebird-support/conversations/newtopic;_ylc=X3oDMTJldXEyM21sBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA250cGMEc3RpbWUDMTQ3OTM2NDQ5Ng--" style="text-decoration: none; color: #2D50FD">Start a New Topic</a>
</td>
<td>•</td>
<td style="font-size: 12px; font-family: arial; padding: 7px 5px 5px;color: #2D50FD;" >
<a href="https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/129984;_ylc=X3oDMTM4Zzk2MGZrBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzEyOTk5MwRzZWMDZnRyBHNsawN2dHBjBHN0aW1lAzE0NzkzNjQ0OTYEdHBjSWQDMTI5OTg0" style="text-decoration: none; color: #2D50FD;">Messages in this topic</a>
(8)
</td>
</tr>
</tbody>
</table>

<div id="megaphoneModule">
<hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
<div>
<div class="stream" style="margin-bottom:10px;">
<div style="background-color:white;">
<div class="sn-img" style="display:inline;"><img name="tn_file" style="padding:0px 10px;vertical-align:top;margin-top:5px;" src="https://s.yimg.com/ru/static/images/yg/img/megaphone/1464031581_phpFA8bON" height="82" width="82"></div>
<div class="mod-txt" style="display:inline-block;">
<a rel="nofollow" name="sub_url" target="_blank" href="https://yho.com/1wwmgg" style="color:#0000FF;display:block;margin-left:5px;text-decoration:none;"><span style="font-size:15px;">Have you tried the highest rated email app?</span></a>
<div style="max-width:530px;padding:2px 5px;">With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.</div>
</div>
</div>
</div> </div>

<hr style="height:2px ; border-width:0; color:#E3E3E3; background-color:#E3E3E3;">
</div>





<div id="ygrp-grfd" style="font-family: Verdana; font-size: 12px; padding: 15px 0;">



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++<BR>
<BR>
Visit <a href="http://www.firebirdsql.org">http://www.firebirdsql.org</a> and click the Documentation item<BR>
on the main (top) menu.  Try FAQ and other links from the left-side menu there.<BR>
<BR>
Also search the knowledgebases at <a href="http://www.ibphoenix.com/resources/documents/">http://www.ibphoenix.com/resources/documents/</a> <BR>
<BR>
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


</div>


<div id="ygrp-vital" style="background-color: #f2f2f2; font-family: Verdana; font-size: 10px; margin-bottom: 10px; padding: 10px;">

<span id="vithd" style="font-weight: bold; color: #333; text-transform: uppercase; "><a href="https://groups.yahoo.com/neo/groups/firebird-support/info;_ylc=X3oDMTJlOTdkcm5iBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTQ3OTM2NDQ5Ng--" style="text-decoration: none;">Visit Your Group</a></span>

<ul style="list-style-type: none; margin: 0; padding: 0; display: inline;">
<li style="border-right: 1px solid #000; font-weight: 700; display: inline; padding: 0 5px; margin-left: 0;">
<span class="cat"><a href="https://groups.yahoo.com/neo/groups/firebird-support/members/all;_ylc=X3oDMTJmamtuMnA1BF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzE0NzkzNjQ0OTY-" style="text-decoration: none;">New Members</a></span>
<span class="ct" style="color: #ff7900;">5</span>
</li>
</ul>
</div>

<div id="ft" style="font-family: Arial; font-size: 11px; margin-top: 5px; padding: 0 2px 0 0; clear: both;">
<a href="https://groups.yahoo.com/neo;_ylc=X3oDMTJkb2VoNzk0BF9TAzk3NDc2NTkwBGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA2dmcARzdGltZQMxNDc5MzY0NDk2" style="float: left;"><img src="http://l.yimg.com/ru/static/images/yg/img/email/new_logo/logo-groups-137x15.png" height="15" width="137" alt="Yahoo! Groups" style="border: 0;"/></a>
<div style="color: #747575; float: right;"> • <a href="https://info.yahoo.com/privacy/us/yahoo/groups/details.html" style="text-decoration: none;">Privacy</a> • <a href="mailto:firebird-support-***@yahoogroups.com?subject=Unsubscribe" style="text-decoration: none;">Unsubscribe</a> • <a href="https://info.yahoo.com/legal/us/yahoo/utos/terms/" style="text-decoration: none;">Terms of Use</a> </div>
</div>
<br>



</div> 



<div id="ygrp-sponsor" style="width:160px; float:right; clear:none; margin:0 0 25px 0; background: #fff;">


<div id="ygrp-reco">
</div>


</div> 

<div style="clear:both; color: #FFF; font-size:1px;">.</div>
</div>

<img src="http://geo.yahoo.com/serv?s=97359714/grpId=2442406/grpspId=1705115386/msgId=129993/stime=1479364496" width="1" height="1"> <br>

<img src="http://y.analytics.yahoo.com/fpc.pl?ywarid=515FB27823A7407E&a=10001310322279&js=no&resp=img&cf12=CP" width="1" height="1">

<div style="color: #fff; height: 0;">__,_._,___</div>


</body>


<head>
<style type="text/css">

</style>
</head>


</html>

hvlad@users.sourceforge.net [firebird-support]

2016-11-17 11:33:31 UTC

Permalink

---In firebird-***@yahoogroups.com, <***@...> wrote :

As said I was a little unclear in my first post, my problem is not to create the autoincrementing field which most of your replies is about but to handle existing table entries. (Neither will I use the rdb$db_key for something persistent.)
However the "character set octets" solved my problem and I also found out how to update the generator value regarding the existing items. Here is my working version:

set term ~ ;
execute block
as
declare dbkey char(8) character set octets;
begin
for
select rdb$db_key
from mytable
into :dbkey
do
begin
update mytable set id = next value for mygenerator
where rdb$db_key = :dbkey;
end
end~
set term ; ~

This is full (and less efficient) equivalent of the

update mytable set id = next value for mygenerator

Regards,
Vlad