Hello

My app uses integer fields for most primary indexes. In Interbase I use Generators to contain an integer value, and a StoredProc to increment the
value. What is the best way to handle a similar situation in EDB? I could have multiple single-record tables, but I figure that there is a better way.

I would also be grateful if I could see an example of a StoredProc that incremented a field value, but would also be capable of being rolled back if
the transaction failed.

The proc's code would look something like

GetNewIDFromTable(IN "TableName", "FieldName" VARCHAR VAR COLLATE, OUT "NewID")
BEGIN
--Get the existing ID number
DECLARE GetStmt STATEMENT;
--save the incremented number
DECLARE UpdateStmt STATEMENT;

PREPARE GetStmt FROM 'SELECT MAX(:FieldName) from :TableName';
PREPARE UpdateStmt FROM 'Update :TableName set :FieldName = ? + 1';

--then use the result of the GetStmt to increment
Execute UpdateStmt using GetStmt ;

NextID = beats me, but I have to know the new value somehow

END

Let me know if this is the way to go - be unkind if you wish; I can take it.

Regards

Peter

Peter


If you can set the column definition to

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

If you can't do that the next best approach is to use a trigger. BeforeInsert I think but it might be AfterInsert.

Since I do it the column definition way I don't have any code examples. However, from my DBISAM days in a system which came from the BDE what I did was a single table with four fields: table - string, field - string, value - integer, ascending - boolean. The latter allowing me to have descending field numbers. I used table components to manage it all so no useful code for you there either - sorry.

Roy Lambert [Team Elevate]

Peter,

<< My app uses integer fields for most primary indexes. In Interbase I use
Generators to contain an integer value, and a StoredProc to increment the
value. What is the best way to handle a similar situation in EDB? I could
have multiple single-record tables, but I figure that there is a better way.
>>

The best (and easiest) way is to do as Roy suggests, and define your primary
key column as an IDENTITY column.  You can return IDENTITY columns as output
parameters from INSERT statements, so you can always easily retrieve the
value generated during the INSERT:

http://www.elevatesoft.com/newsgrp?action=openmsg&group=16&msg=8697&page=1#msg8697

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

Do you remember these links

>http://www.elevatesoft.com/newsgrp?action=openmsg&group=16&msg=8697&page=1#msg8697

or are you just better and deciding what search terms to use?

Whichever its damned impressive.

Roy Lambert

Tim & Roy
<<<<<
The best (and easiest) way is to do as Roy suggests, and define your primary
key column as an IDENTITY column.  You can return IDENTITY columns as output
parameters from INSERT statements, so you can always easily retrieve the
value generated during the INSERT:

http://www.elevatesoft.com/newsgrp?action=openmsg&group=16&msg=8697&page=1#msg8697
>>>>>

That looks like the goods - I have dozens of tables that would require converting, and I have hundreds of different databases to migrate from
DBISAM, in the field. With regard to the migrating tables, I would need to find the MAX of the column, and use that as the seed value, then use that
in a script to ALTER the table.

Is that correct?

Regards

Peter

Tim & Roy

I was alerted to the problem in another thread - what if my heritage DBISAM tables do not have contiguous ID values? If a record has been archived
out to a separate table, then will the conversion to an IDENTITY type respect the missing records?

Regards

Peter

Roy,

<< Do you remember these links

or are you just better and deciding what search terms to use? >>

I just remember (approximately) which search terms I used when created the
message.  In this case, it was "insert param out".

--
Tim Young
Elevate Software
www.elevatesoft.com

Peter,

<< That looks like the goods - I have dozens of tables that would require
converting, and I have hundreds of different databases to migrate from
DBISAM, in the field. With regard to the migrating tables, I would need to
find the MAX of the column, and use that as the seed value, then use that in
a script to ALTER the table.

Is that correct? >>

Are the source table fields defined as plain integers, or autoincs ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Peter,

<< I was alerted to the problem in another thread - what if my heritage
DBISAM tables do not have contiguous ID values? If a record has been
archived out to a separate table, then will the conversion to an IDENTITY
type respect the missing records? >>

Yes.  By default, the migration will convert autoinc fields to IDENTITY
columns that are generated BY DEFAULT, and so will use existing values if
they are present.  If the source fields are plain integer fields, then you
can just use an ALTER TABLE on the table after the migration, and just
specify that the column should be GENERATED as an IDENTITY column BY
DEFAULT, and that will also preserve the existing values.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I'm still impressed.

Roy Lambert