1

I'm using Firebird as my database and I wish to know how can I select the next generator value of my table when there's no data? Currently I'm using this script: 

SELECT GEN_ID(" + sGEN_NAME + ", 1) FROM sGEN_TABLE

This works fine if there's records on my table, otherwise it won't work! I was thinking about make a "helper INSERT", then make the select to get the GEN_VALUE, then delete the record. But it look so messy, is there another way?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Diego Bittencourt
  • 595
  • 10
  • 28
  • 1
    `Works fine if there's records on my table` - no it did not! it only works correctly when single row, otherwise it skips MANY values. Unless you had (but you did not tell) used *Select **First(1)** GEN+ID....* – Arioch 'The Dec 11 '19 at 09:50

2 Answers2

3

Using RDB$DATABASE single-row system table is the most simple and idiomatic way.

However, just for the completeness, there is one more way.

https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-execblock.html

EXECUTE BLOCK 
  RETURNS (next_val integer /* BigInt in Firebird 3 */ )
AS
BEGIN
  next_val = GEN_ID ( gen_name, +1 ); 
END

Also, more SQL-ish way would be replacing flexible, but IB/FB-specific GEN_ID ( xxx, +1 ) with standard NEXT VALUE FOR xxx.

https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-commons-expressions.html#fblangref25-commons-conditional-nxtvlufor

Also, the very task to "select the next generator value of my table" sounds weird. Why would you ever want to do it? In modern Firebird that should be some very special niche case, when you actually need to waste a stand-alone select query for so simple a task. Usually you just fetch form generator immediately when inserting, by one and the same command for example

INSERT INTO Table1( ID, Column1, Column2 ...)
VALUES ( GEN_ID(gen_name, +1), ? /* Param 1 */, ? /* Param 2 */ ...)
RETURNING ID

Or, if you would put ID assignment into an BEFORE UPDATE OR INSERT SQL trigger, just

INSERT INTO Table1( Column1, Column2 ...)
VALUES ( ? /* Param 1 */, ? /* Param 2 */ ...)
RETURNING ID

The less queries you send to server - the faster it works (see: network latency, roundtrip) and the less code you have to make mistakes about.

Arioch 'The
  • 15,799
  • 35
  • 62
1

So I found the answer, just needed to replace table's name by rdb$database

Diego Bittencourt
  • 595
  • 10
  • 28