I’m new in Firebird but I’d like to write a small script in PHP that reads a CSV file and fills an existing Firebird db with its data. The problem is I don’t really know how to use the autoincrement generator. I’ve googled a lot but it’s still a mistery for me. There is a gen_main generator defined in the db and I can use it in the IBExpert’s query builder but cannot in PHP… I saw a function named ibase_gen_id, what is the “PDO-way” of it? What is the process of inserting a row that has an autoincremented field with PDO? Thanks in advance!
Advertisement
Answer
NOTE: I have never used PDO, so I can’t comment on PDO specifics.
Depending on your exact needs you can use: NEXT VALUE FOR
NEXT VALUE FOR <sequence-name>
or GEN_ID
GEN_ID(<sequence-name>, 1)
To get the next value of the sequence/generator.
You can either use these directly in your INSERT statement, or first issue a SELECT query against RDB$DATABASE to retrieve the value yourself before inserting: in Firebird you need to use a SELECT to retrieve values, and you always need to select against a table. RDB$DATABASE is guaranteed to contain only one row (like Oracle’s DUAL).
So you need SELECT NEXT VALUE FOR GEN_MAIN FROM RDB$DATABASE or SELECT GEN_ID(GEN_MAIN, 1) FROM RDB$DATABASE to get the next sequence value.
In general however I would advise you to add a trigger to do the auto-increment for you, see Firebird Generator Guide for details. You can then use INSERT ... RETURNING <column-list> to retrieve the generated id.