Binding parameters using pyodbc with connection to Firebird

Question

I've got an application which uses pyodbc to connect to database. It can by run with different db engines. Now I try to adapt it to Firebird.

For some db engines (tested with sqlite, Sybase) I can use native pyodbc way to handle binding parametres:

cnxn = pyodbc.connect(connect_string, autocommit=True)
cur = cnxn.cursor()
cur.execute("insert into files (file_type_id, path, md5) values (?, ?, ?)", file_type_id, path, md5)

For Oracle I had to prepare some special SQL to be run:

cnxn = pyodbc.connect(connect_string, autocommit=True)
cur = cnxn.cursor()
sql = """DECLARE v_file_type_id INT; v_path varchar2(1024); v_md5 varchar2(32);
         BEGIN
         v_file_type_id := %d;
         v_path := '%s';
         v_md5 := '%s';
         insert into files (file_type_id, path, md5) values (v_file_type_id, v_path, v_md5);
         END;""" % (file_type_id, path, md5)
cur.execute(sql)

First way doesn't work with Firebird. I receive error: pyodbc.IntegrityError: ('23000', '[23000] [ODBC Firebird Driver][Firebird]validation error for column FILE_TYPE_ID, value "*** null ***" (-625) (SQLExecDirectW)').

I tried to prepare dedicated SQL, just like for Oracle, but I've got some problems with it.

cnxn = pyodbc.connect(connect_string, autocommit=True)
cur = cnxn.cursor()
sql = """set term ^ ;
         execute block
         as
         declare v_file_type_id int = %d;
         declare v_path varchar(1024) = '%s';
         declare v_md5 varchar(32) = '%s';
         begin
         insert into files (file_type_id, path, md5) values (:v_file_type_id, :v_path, :v_md5);
         end
         ^
         set term ; ^""" % (file_type_id, path, md5)
cur.execute(sql)

It doesn't work. The rror this time is: pyodbc.Error: ('HY000', '[HY000] [ODBC Firebird Driver][Firebird]Dynamic SQL Error\nSQL error code = -104\nToken unknown - line 1, column 5\nterm (-104) (SQLExecDirectW)').

I'm looking for any sollution, so I'll be able to run it in any of that 2 ways. Performance aspect is also very important - application isn't run very often, but each time that part of code makes about 50k inserts to files table.

Seeing the actual SQL that Firebird is trying to execute would be helpful here. There's no way to see the actual SQL being executed from `pyodbc` (and shouldn't be) as it passes the SQL and parameters. Could you turn on query logging in Firebird and include the SQL causing the error? — FlipperPA, Dec 17 '17 at 01:39

Mark Rotteveel · Accepted Answer · 2017-12-18T11:56:54.900

The first error is caused by the fact you are inserting a null value into a not null column called FILE_TYPE_ID, in other words your variable file_type_id is null, and this is not allowed by the constraints on the column, or something else is going wrong.

It has been a while since I did anything with the Python or its DB-API, but as far as I recall, the way you execute is wrong:

cur.execute("insert into files (file_type_id, path, md5) values (?, ?, ?)", file_type_id, path, md5)

Should be (note the use of a tuple)

cur.execute("insert into files (file_type_id, path, md5) values (?, ?, ?)", (file_type_id, path, md5))

The second error is caused by the fact that set term is not a valid Firebird SQL statement, it is a statement specific to client tools to switch statement terminators, see also firebird procedural query throwing "token unknown" error at "SET TERM #;"

Execute block is an anonymous 'stored' procedure, and is not normally used to execute prepared statements this way. Your use of execute block is particularly unsafe as you are opening yourself to SQL injection because of your use of string formatting, and because of execute block this form is more dangerous than executing 'normal' statement.

There are several Firebird database drivers for Python, FDB and pyfirebirdsql, that way you don't need to use ODBC.

Actually, pyodbc does not require parameter values to be passed as a tuple (ref: [here](https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API#passing-parameters)). — Gord Thompson, Dec 17 '17 at 09:52
Yes, the first error says about trying to put the `null` value into column which doesn't allow it. But believe me, `file_type_id` isn't `null`, I can print it's value just before `cur.execute(...)`. Also, the same code works when I change `connect_string` to sqlite or Sybase database. I tried both ways to pass parameters - direct (like I wrote above), and in tumple. Both doesn't work in that case. I removed `set term` part from the beginning and from the end, and it works now. I understand potential dangerous. Thanks for your help. — Maciej Szymonowicz, Dec 17 '17 at 11:55
@Yama Hmm, that would suggest a bug in the ODBC driver, are you using Firebird ODBC driver 2.0.5? — Mark Rotteveel, Dec 18 '17 at 11:58

Binding parameters using pyodbc with connection to Firebird

1 Answers1