SYBASE + FreeTDS: Issues getting insert row ID passing in parameters

[snowman2]

Environment

• Python: 3.7.5
• pyodbc: 4.0.30
• OS: Linux-4.15.0-99-generic-x86_64-with-Ubuntu-18.04-bionic
• DB: sybase (Adaptive Server Enterprise/16.0 SP02)
• driver: FreeTDS

Issue

Forwarded from: https://github.com/sqlalchemy/sqlalchemy/issues/5311

import pyodbc

connect_args = {"autoconnect": True}
connector = pyodbc.connect(
    database=database,
    user=username,
    password=password,
    driver="FreeTDS",
    host=host,
    port=port,
    **connect_args,
)

This works:

cnxn = connector.connect()
cursor = cnxn.cursor()
cursor.execute(
    "INSERT INTO mytable (type, description, create_date) "
    f"VALUES ('TEST', 'TEST', '{datetime.utcnow().isoformat()}')
)
cursor.execute("SELECT @@identity AS lastrowid")
print(cursor.fetchone()[0])

large number

But this fails (returns 0):

cnxn = connector.connect()
cursor = cnxn.cursor()
cursor.execute("INSERT INTO mytable (type, description, create_date) VALUES (?, ?, ?)", 'TEST', 'TEST', '2020-05-06T13:22:43.923404')
cursor.execute("SELECT @@identity AS lastrowid")
print(cursor.fetchone()[0])

0

[keitherskine]

SQL queries with parameters run kinda like they are stored procedures (sort of). You could try adding the select @@identity to the end of the query:

cnxn = connector.connect()
cursor = cnxn.cursor()
cursor.execute("INSERT INTO mytable (type, description, create_date) VALUES (?, ?, ?);SELECT @@identity AS lastrowid", 'TEST', 'TEST', '2020-05-06T13:22:43.923404')
if cursor.nextset()
    print(cursor.fetchone()[0])

[snowman2]

Thanks for the tip, but it doesn't seem to like that syntax:

ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near ';'.\n (102) (SQLPrepare)")

[keitherskine]

Hmm. That syntax works on SQL Server. Looks like it used to work on Sybase but no more: https://answers.sap.com/questions/84778/-incorrect-syntax-near-%27%27----sybaseadonet2aseclien.html http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00729.1500/html/errMessageAdvRes/CHDGFCCJ.htm

You could try replacing the semicolon with a newline character \n. I'm afraid I can't check it myself because I don't have access to a Sybase database.

[snowman2]

That's unfortunate. I tried out \n with no luck.

[keitherskine]

Indeed. You could perhaps try using just a space between the statements, but I'm guessing here. You could also try wrapping the whole SQL in BEGIN END, or putting GO between the statements, but it looks like the SQL has to be just one statement. If all else fails, you could create a stored procedure that runs the SQL and returns the identity value. A bit long-winded but it should do the trick.

[snowman2]

I was able to run it with a space and a \n but it didn't return anything. It seemed like the second statement was ignored.

SQL queries with parameters run kinda like they are stored procedures (sort of)

Is this something that could be changed or is it expected behavior and a wontfix?

[keitherskine]

Unfortunately, there's not much pyodbc can do about this, even if it wanted to. pyodbc is simply a conduit between your python code and the database (via the driver manager and driver). pyodbc does not parse the SQL statement, it simply passes it on to the database and manages any return messages. If the database does not execute part of the SQL or does not return any messages, that's not something pyodbc can somehow "force" to happen.

The behavior in this scenario is specific to the RDBMS (and drivers). The Python code and multi-statement SQL query I described above does work on SQL Server with the "ODBC Driver 17 for SQL Server" driver, and does return the identity value. I'm afraid your best bet is to raise this with Sybase and/or FreeTDS.

[snowman2]

Alright, that makes sense. Thanks for your assistance, much appreciated :+1:

[snowman2]

@keitherskine, you were definitely on the right track. I should've followed up on that. This solution seems to work: https://github.com/FreeTDS/freetds/issues/337#issuecomment-640070962