FreeTDS: cannot execute with params

[dirkjonker]

Using FreeTDS I cannot execute an INSERT statement with parameters. I get the following error:

DatabaseError: ODBC error
state: IM001
native error code: 0
message: [unixODBC][Driver Manager]Driver does not support this function

From the unixODBC trace:

[ODBC][58773][1487672064.768219][SQLDescribeParam.c][185]
		Entry:
			Statement = 0x7fee40014400
			Parameter Number = 1
			SQL Type = 0x7fff5e9343be
			Param Def = 0x7fff5e9343b0
			Scale = 0x7fff5e9343ae
			Nullable = 0x7fff5e9343ac
[ODBC][58773][1487672064.768731][SQLDescribeParam.c][292]Error: IM001

To reproduce:

conn = turbodbc.connect(dsn='mssql', uid='test', pwd='test')
cursor = conn.cursor()
cursor.execute("CREATE TABLE test_insert (val INT);")
cursor.execute("INSERT INTO test_insert (val) VALUES (?);", (0,))

Doing an insert without parameterization works fine, e.g.
cursor.execute("INSERT INTO test_insert (val) VALUES (42);")
works fine.

Full specs: using FreeTDS 1.00.26, unixODBC 2.3.4, Python 3.5.2, macOS Sierra, SQL Server 2016.

[MathMagique]

Hi Dirk! Thanks for the report. I have not yet played with FreeTDS, but I know that Microsoft's official ODBC driver has no problems with parameters. Unfortunately, it seems to be available for Linux and Windows only.

If FreeTDS does not support parameters, performance of inserts will be pretty slow. But I'll have to check in more detail.

[dirkjonker]

Hi Michael, thanks for the quick reply! Parameterization works fine if I use pyodbc + FreeTDS, so turbodbc must be calling some kind of ODBC function that FreeTDS does not support. I'll see if I can find something.

[MathMagique]

That would be great. As far as I know, pyodbc does not transfer parameters in batches, so any calls required to enable batches might be harmful.

Btw: @keitherskine does not seem to have the issue you report with MSSQL, but he uses Microsoft's driver to connect (see his comments in the otherwise unrelated #39).

[dirkjonker]

@MathMagique the problem seems to be that FreeTDS has not implemented SQLDescribeParam: https://github.com/FreeTDS/freetds/blob/Branch-1_00/doc/api_status.txt

I updated the title and initial post with some more information.

[MathMagique]

Then there may be some hope left ;-). I use SQLDescribeParam for the initial guess of what a parameter is supposed to be. Many databases don't know that anyway and I have a mechanism in place that can handle differing parameter types.

It should be easy to implement a fallback to something like an integer type when SQLDescribeParam is not available. The remaining code should already be able to handle Python parameters that deviate from this integer type, then.

[MathMagique]

The default parameter type mentioned in the commit message is automatically replaced by more appropriate types once non-Null values are passed.

[dirkjonker]

Awesome, just tested the master branch and it works great!

[MathMagique]

Thanks for helping identifying the issue!