I am trying to use kinterbasdb and Python 2.7 to write data to a Firebird database on a server, and print the key value of the added data. It works perfectly when I write to a local copy of the database.
insert = """INSERT INTO myTable (myID,DT_TIMESTAMP)
VALUES (?,?)
RETURNING myTable_KEY"""
data = ("idTest", datetime.now())
conAdd = kinterbasdb.connect(dsn=nm, user=dbUser, password=dbPass)
cur = conAdd.cursor()
cur.execute(insert, data)
return_key = cur.fetchone()[0]
conAdd.commit()
cur.close()
But the word returning causes a problem when accessing the database on the network:
ProgrammingError: (-104, 'isc_dsql_prepare: \n Dynamic SQL Error\n SQL error code = -104\n Token unknown - line 3, column 13\n RETURNING'
Looking at the version of Firebird, the local version is 2.0 and the server version is 1.5. I don't understand this, as I've just copied the server version to my local drive for testing. I'm using kinterbasdb, which I thought would be the interface to the database whether it was local or on the server. It turns out that v2.0 has the 'returning' word but v1.5 doesn't. And I need my Python code to work on the server version.
I have two questions: Why are the versions different? And how can I get the key value returned in v1.5 (multiple simultaneous users will be entering data)?
The RETURNING clause was introduced in Firebird 2.0. It can't be used on earlier Firebird versions. See also RETURNING Clause for Insert Statements in the Firebird 2 release notes.
So to answer your question "Why are the versions different?", because that is why they are different versions: new versions introduce new features. If you mean, 'why do I have a different version locally', then probably because you installed that version locally. Note that (before Firebird 3), newer Firebird versions can access older database files. So if you copied the database file from the server to local, then you can access it with the newer Firebird version installed on your machine.
You should really consider upgrading to a newer Firebird version. The last Firebird 1.5 version was released 8 years ago, while even 2.0 has not been updated for 5 years. Since that time several new features have been introduced, and bugs (including security bugs) have been fixed. The current version is Firebird 3.0, with Firebird 2.5 still being supported with bug fixes.
Also note that kinterbasdb is an abandoned driver (after the death of its maintainer in 2007), instead there are two other drivers that are actively maintained: FDB and pyfirebirdsql.
To answer your second question "And how can I get the key value returned in v1.5 (multiple simultaneous users will be entering data)?": You will need to manually select a new value from the generator and use that explicitly in your insert statement instead of relying on a trigger to generate it for you.
See also How to create an autoincrement column?:
If you use an older version of Firebird, you should first get the value using GEN_ID and then use it in INSERT statement. To read in the generator value, you can use some single-record table like RDB$DATABASE:
select GEN_ID(GEN_T1_ID, 1) from RDB$DATABASE;