ERROR: No results. Previous SQL was not a query.

[ShreeshaAck]

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.9
• pyodbc: 4.0.26
• OS: <AWS Lambda environment>
• DB: SQL Server
• driver: ODBC Driver 17 for SQL Server

Issue

We have multiple users in the SQL database and we want to reset the password for some users by using master user login, but when we try to reset the password using the lambda function, it is showing the error as "ERROR: No results. The previous SQL was not a query." but other SQL statements(Select or Insert) are working as expected rather "ALTER"

Here is a code sample:

import json
import pyodbc

def lambda_handler(event, context):
    try:
        pyodbc.pooling = False
        conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};Server=amazon-rd-endpoint,1433;Database=database_name;Uid=supper_user;Pwd=password;CENSORED;Trusted_Connection=yes;',autocommit=True)
        cursor = conn.cursor()

        cursor.execute("ALTER LOGIN second_user WITH PASSWORD = 'newPassword'")
        conn.commit()

        return {
            'statusCode': 200,
        }
    except Exception as e:
        print('ERROR: ', e)

[v-chojas]

I notice you have autocommit enabled in the connect options, but then you try to do an explicit commit. The latter is not necessary in autocommit mode. Do you see the same error without the explicit commit?

[ShreeshaAck]

@v-chojas I have added autocommit=True after reading some of the articles which are posted under issue. Yes without explicit commit also getting the same error.

Even I tried with a lower version of Python, i.e 3.7 and 3.8 but the same error

[v-chojas]

Could you post an ODBC trace? https://github.com/mkleehammer/pyodbc/wiki/Troubleshooting-%E2%80%93-Generating-an-ODBC-trace-log

[ShreeshaAck]

@v-chojas As we are using AWS RDS, we are unable to produce ODBC trace log, but we tried tracing data using SQL profile, when we try to execute ALTER LOGIN user_name WITH PASSWORD = new_password from AWS Lambda, it is changing the password but pyodbc is exit from try block and return Exception as "No results. Previous SQL was not a query."

[v-chojas]

AWS RDS is a service, but tracing is done on the client.

[ShreeshaAck]

I followed the steps, which mentioned in https://github.com/mkleehammer/pyodbc/wiki/Troubleshooting-%E2%80%93-Generating-an-ODBC-trace-log but I couldn't get any tracing log, instead of Tracing I tried with SQL Profile log.

The query is working as expected, but instead of return success, it is going to Exception Here is the sample SQL Profile query trigger : https://picd.in/qzqSnUbu

[v-chojas]

What version of unixODBC are you using? Some of the earlier versions had some problems with tracing (among other bugs).

[ShreeshaAck]

I am using AWS RDS service with bellow details, Engine: SQL Server Web Edition Engine version: 15.00.4073.23.v1

I am using AWS lambda to trigger a SQL query to make some operation, and I have added a lambda layer (pyodbc) to communicate between lambda and SQL database, basically, it is a kind of serverless architecture

pyodbc Version: 4.0.30 Classifier: Programming Language :: Python :: 3.6 Python Version: Python 3.7

[gordthompson]

Are you sure that your function does not try to do a .fetchone(), .fetchall() or similar? "No results. The previous SQL was not a query." does not happen at the "execute" phase, it happens at the "fetch" phase.

Also, FWIW, I cannot reproduce your issue unless I try to do .fetchall() after the .execute().

[ShreeshaAck]

@gordthompson I am trying to make DDL command via pyodbc, hope .fetchone(), .fetchall() will work proper with DML command

anyhow I tried cursor.fetchall() or cursor.fetchone() after the cursor.execute() but getting same error "No results. Previous SQL was not a query."

[v-chojas]

I am not sure what you are trying to say. In any case, neither me nor Gord are able to reproduce your issue. What did you expect? The previous SQL was indeed not a query.

[ShreeshaAck]

Whenever I perform ALTER queries by using pyodbc I am getting the error "No results. Previous SQL was not a query." so I tried with a different Python module pymssql, I don't think so pyodbc will work with Lambda env, so I continue using pymssql instead pyodbc

I am very sad that you are not able to reproduce your issue. here is the screen-shot which I am getting the error in Lambda: https://picd.in/rJGM6jSw

Also, you can see the status of the lambda function and error message which pyodbc is returning in try-catch block

[v4ld3rr4m4]

I get this error using the DML comand delete and trying some as mention @ShreeshaAck fetching the result . Look my wrong code :-1: `for msg in cursor.execute("DELETE FROM MYTABLE... "):

using msg here

` I found that in core inside execute method exist this block:

if (ret == SQL_NO_DATA) { // Example: A delete statement that did not delete anything. cur->rowcount = 0; Py_INCREF(cur); return (PyObject*)cur; } and when you try to use it thinkind DATA EXIST then fire this another code in Cursor_validate method if (IsSet(flags, CURSOR_REQUIRE_RESULTS) && cursor->colinfos == 0) { if (flags & CURSOR_RAISE_ERROR) PyErr_SetString(ProgrammingError, "No results. Previous SQL was not a query."); return 0; }

So later understand this I just make execute and work with rowcount var of Class Cursor :+1: `cursor.execute("DELETE FROM MYTABLE... "):

using cursor.rowcount that could be 0 or more

`

[ShreeshaAck]

@v4ld3rr4m4 How this will help me to rectify the error? my problem statement I different, and the thing is if I try to make an ALTER statement, pyodbc is throwing the error, unfortunately, the database level query is working but pyodbc is showing an error as "No results. Previous SQL was not a query." also you can refer screenshot which is attached in the previous reply.

[v-chojas]

The error comes from line 100 here:

https://github.com/mkleehammer/pyodbc/blob/24e0ccadaec091ce6b1b81e3197a345b895773fc/src/cursor.cpp

...so Cursor_Validate needs to be called with at least CURSOR_REQUIRE_RESULTS and CURSOR_RAISE_ERROR for that to occur. Where does that happen?

• Cursor_iternext
• Cursor_fetchval
• Cursor_fetchone
• Cursor_fetchall
• Cursor_fetchmany
• Cursor_skip

...none of which are called from Cursor_execute, so the error you are getting in this issue is practically impossible given the code you claim to be executing.