1

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)?

lemmyBarnet
  • 59
  • 1
  • 8

2 Answers2

2

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;
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Thanks for the comments. I can't change the database version on the server, it is a commercial product. I still don't understand why the versions of Firebird appear different, though. I'm accessing both database files using the same kinterbasd driver (and using the same FBCLIENT.DLL). I don't have any other Firebird drivers on my PC. Is kinterbasdb connecting to the Firebird service on the server, rather than directly connecting to the file? – lemmyBarnet Aug 16 '17 at 08:01
  • @lemmyBarnet Yes, Firebird databases are always accessed through the server (although sometimes that server is an in-process 'embedded server', but you'd usually know it if you were using that). kinterbasdb itself doesn't know how to read a Firebird database, that is what the server is for. – Mark Rotteveel Aug 16 '17 at 08:03
  • "I'm accessing both database files using the same kinterbasd driver " - then show BOTH `connection strings` to those two databases. – Arioch 'The Aug 16 '17 at 08:36
  • @Arioch 'The It's the same connection string, just different file paths and passwords. If I take out the 'RETURNING myTable_KEY' part, I can write successfully to the database on the server. – lemmyBarnet Aug 16 '17 at 09:11
  • @Mark Rotteveel Thanks, that's good to know. I thought I was connecting using using the FBCLIENT.DLL to act as an 'embedded server', thereby allowing me to use my software on any PC without installing any ODBC Firebird drivers. So I expected the Firebird version to be the same because I was only using one driver. Clearly I don't understand how this is working! FBCLIENT is being used on my local machine to act as the Firebird server when accessing the local database file, but when accessing the server-side database file, my code is using the server's embedded Firebird server? – lemmyBarnet Aug 16 '17 at 09:18
  • "It's the same connection string, just different" - nonsense. It is either the same or it is different, it cannot be differently the same. But whatever.... – Arioch 'The Aug 16 '17 at 10:56
  • The normal FBCLient dll does not include embedded server. It is merely a cross-connections library. Though you can rename firebird embedded DLL into anything you want, including FBCLient.DLL but we do not know if you did it or not. – Arioch 'The Aug 16 '17 at 10:58
  • @Arioch 'The My point about the connection string is that it works, regardless of whether it is the server-side FB client or not. The problem is that one version of Firebird allows the 'RETURNING' token and the other does not. I don't know why the local and server FB clients are different. – lemmyBarnet Aug 16 '17 at 14:48
  • @lemmyBarnet They are different, because on the server you have Firebird 1.5 installed, and on your local machine you have Firebird 2.0 (or maybe even a newer version?) installed. – Mark Rotteveel Aug 16 '17 at 16:25
  • @lemmyBarnet by attentively reading connection strings you can deduce which different servers (or same) you do connect to. And then you could examine those servers, versions and what not. If FB2 you can also issue `select rdb$get_context('SYSTEM', 'ENGINE_VERSION') from rdb$database`. Will be an error in FB1 // also using Embedded FB as a client library for remote DB is possible and is a simple case of undocumented feature of proxy-chaining FB-servers together :-D – Arioch 'The Aug 17 '17 at 08:32
0

The answer to why there are different versions of Firebird locally and on the server is (from Mark Rotteveel) that kinterbasd isn't acting as an embedded server, but there is a Firebird service on the host computer. At some point, someone has put a different version of the Firebird service on my desktop/local PC. I did not realise this, so I thought the the RETURNING statment would work on the server-side database because it works locally.

Each table I am writing to has a datetimestamp without milliseconds, and I need to get the key from each entry because it is used as a foreign key in other tables. Rather than look into generators for autonumbers (which I'm sure is the 'correct' way of approaching the problem), I'm using a select SQL statement to return the key:

select="select " + key + " from " + table + " where cast (myTIMESTAMP as timestamp) = '" + myTIMESTAMP.strftime('%m/%d/%Y %H:%M:%S') + "'"

where myTIMESTAMP = datetime.now().replace(microsecond=0)

is set before the insert SQL statement that adds the data.

lemmyBarnet
  • 59
  • 1
  • 8