SYBASE: Issues getting insert row ID passing in parameters

[snowman2]

Forwarded from: mkleehammer/pyodbc#757

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: sqlalchemy/sqlalchemy#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

Any thoughts on if this is a FreeTDS issue or a SYBASE issue?

[snowman2]

Also, any debugging tips would be appreciated as well.

[freddy77]

For the debugging see http://www.freetds.org/userguide/logging.htm. For the problem I expect that either the row was not inserted (not sure what Python does in case of errors) or that the type of request done by FreeTDS does not update @@identity.

[snowman2]

Thanks for the debugging tips 👍

I expect that either the row was not inserted

This is not the case. I checked the database and the row is properly inserted.

The type of request done by FreeTDS does not update @@identity

Interesting 🤔. Is this something that could be changed?

[snowman2]

Also, the failing method uses parameters. This comment may provide insight as well: mkleehammer/pyodbc#757 (comment)

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

[snowman2]

Here is the log from the first query that returned the correct row ID:

freetds_single.log

Here is the log from the query that returned the row ID of 0 with parameters:

feetds_parameter.log

I didn't notice anything strange in there. The logs show different methods were called in each scenario. So, one of the methods called in the logs in the one that returned 0 could be suspect.

[uzb-dev]

This is inherent to the way Sybase ASE deals with parameterized statements. These get transformed on the database side to a so called LightWeight Procedure or LWP. They're indeed basically stored procedures with a cached query plan stored entirely in memory (ASE's procedure/statement cache) with no artifacts on disk. If you have access to Sybase/SAP support, there's some more information in SAP Knowledge Base Article 2578627. These types of LWPs have their own context and the session-global @@identity value gets reset to 0 as you've discovered after they've executed.

To make your code work, you can embed the select @@identity in the query text of your insert statement so that it is part of the same LWP and has access to the @@identity value while the LWP is still executing. You need to have a newline between the insert statement and the select @@identity, like so:

import pyodbc

connection = pyodbc.connect('DSN=DST_ST_2;UID=someusr;PWD=somepwd',
                            autocommit = True)
cursor = connection.cursor()
cursor.execute('insert tempdb.dbo.some_table (foo) values (?)' +
               '\nselect @@identity', 'bar')
print(cursor.fetchone()[0])

(atatidentity) [16:08] mathias@vmdb02x:~/projects/atatidentity > python ./atatidentity.py
35
(atatidentity) [16:08] mathias@vmdb02x:~/projects/atatidentity >

The same issue can be encountered in Java (both with jTDS and the official jConnect JDBC driver):

import java.sql.*;
import java.util.Properties;

public abstract class Identity {
    public static void main(String[] args) throws SQLException {
        Properties props = new Properties();
        props.put("user", "someusr");
        props.put("password", "somepwd");
        Connection c =
            DriverManager.getConnection("jdbc:jtds:sybase://dst_st_2:3000",
                                        props);
        PreparedStatement stmt =
            c.prepareStatement("insert tempdb.dbo.some_table (foo) values (?)");
        stmt.setString(1, "bar");
        stmt.executeUpdate();
        //stmt.close();
        Statement stmt2 = c.createStatement();
        ResultSet rs = stmt2.executeQuery("select @@identity");
        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
    }
}

(atatidentity) [16:08] mathias@vmdb02x:~/projects/atatidentity > java -cp jtds-1.3.1.jar:. Identity
0
(atatidentity) [16:11] mathias@vmdb02x:~/projects/atatidentity >

Same fix applies:

import java.sql.*;
import java.util.Properties;

public abstract class Identity2 {
    public static void main(String[] args) throws SQLException {
        Properties props = new Properties();
        props.put("user", "someusr");
        props.put("password", "somepwd");
        Connection c =
            DriverManager.getConnection("jdbc:jtds:sybase://dst_st_2:3000",
                                        props);
        PreparedStatement stmt =
            c.prepareStatement("insert tempdb.dbo.some_table (foo) values (?)"
                               + "\nselect @@identity");
        stmt.setString(1, "bar");
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
    }
}

(atatidentity) [16:11] mathias@vmdb02x:~/projects/atatidentity > java -cp jtds-1.3.1.jar:. Identity2
37
(atatidentity) [16:13] mathias@vmdb02x:~/projects/atatidentity >

The jConnect JDBC driver has the DYNAMIC_PREPARE Connection property (which is True by default in any jConnect release from the last decade), which exhibits the same behavior. Setting it to False makes a subsequent select @@identity work, but each invocation of a PreparedStatement then gets sent to the database (with the query text (with placeholders) and arguments separately) to be parsed and compiled over and over again. LWPs can greatly increase performance as only a small integral identifier needs to be sent upon each invocation on a prepared statement handle together with any requisite parameters. There's also the related LITERAL_PARAMS property, which makes the jConnect driver interpolate any parameters in a PreparedStatement before sending the query text to the server. This sidesteps the issue as well, but with the downside of churning through ASE's statement cache with ad-hoc queries that will never get repeated.

[snowman2]

Thanks @uzb-dev, that was very helpful 👍. Very nice response that will definitely help lead to a resolution.