6

I am trying to create an 'upsert' function for pypyodbc SQL Server. I have validated that the query built up will run in SSMS with the desired outcome, but when trying to execute and commit with pypyodbc I receive the following error: 'HY007', '[HY007] [Microsoft][ODBC SQL Server Driver]Associated statement is not prepared'.

Here is the upsert function:

def sql_upsert(sql_connection, table, key_field, key_value, **kwargs):
    keys = ["{key}".format(key=k) for k in kwargs]
    values = ["'{value}'".format(value=v) for v in kwargs.values()]
    update_columns = ["{key} = '{value}'".format(key=k, value=v) for k, v in kwargs.items()]
    sql = list()

    #update
    sql.append("UPDATE {table} SET ".format(table=table))
    sql.append(", ".join(update_columns))
    sql.append(" WHERE {} = '{}'".format(key_field, key_value))
    sql.append(" IF @@ROWCOUNT=0 BEGIN ")

    # insert
    sql.append("INSERT INTO {table} (".format(table=table))
    sql.append(", ".join(keys))
    sql.append(") VALUES (")
    sql.append(", ".join(values))
    sql.append(")")

    sql.append(" END")
    query = "".join(sql)
    print(query)

The function builds up a query string in a format based on this other thread How to insert or update using single query?

Here is an example of the output:

UPDATE test SET name='john' WHERE id=3012

IF @@ROWCOUNT=0 BEGIN

INSERT INTO test(name) VALUES('john')

END

Alec Thomas
  • 176
  • 1
  • 2
  • 12

3 Answers3

6

The error message you cited is produced by the ancient "SQL Server" ODBC driver that ships as part of Windows. A more up-to-date driver version like "ODBC Driver 17 for SQL Server" should produce a meaningful error message.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 2
    Thanks Gord. I updated to the 2017 ODBC driver and received a more meaningful SQL Server error message. Turned out to be a simple permissions error. – Alec Thomas Jan 26 '19 at 00:51
  • just adding a comment, if you are using pypyodbc just have this line for your driver: `"Driver={ODBC Driver 17 for SQL Server};"` – sniperd Jul 01 '22 at 14:40
1

If you look here or here you'll see people complaining about this over a decade ago.

Apparently SQL Server's ODBC driver returns that error when you're executing two statements that fail due to a field value being too long, or perhaps due to foreign key violations.

Using SSMS to see which statement causes this problem, or better - stop using ODBC and use pymssql

zmbq
  • 38,013
  • 14
  • 101
  • 171
  • Thanks for the info, I will consider pymssql. I did take the query generated from my function and insert it directly into SSMS and it executed fine. I also ran a SQL trace to verify the query being executed will run in SQL Server. – Alec Thomas Jan 25 '19 at 22:56
0

This error may also come when you don't give correct permissions to stored procedure Go the SQL server --> Right click on your sp-->properties-->permissions Add required users and roles which are going to execute this sp

This may help resolving the issue

Sumit Paroliya
  • 1