CODEWITHSUNDEEP
pythonsql-serverazure-sql-databasepyodbc
Dushan Dissanayake
Dushan Dissanayake

Reputation: 31

Column, parameter, or variable #5: Cannot find data type

This is a script written to analyse the reputation of files by the file hash. File hash is taken from Azure SQL database and analysed by calling APIs and the result is entered again to the database. I am using ODBC Driver 17 for SQL Server.

I am getting an error while re-entering the data to the database.


# Scanning files
def fileScan():

    select_sql = "SELECT source_hash FROM [Downloads]"
    crsr.execute(select_sql)

    rows = crsr.fetchall()

            
    # Read all rows
    for row in rows:
        print(''.join(row))
        response = vt.get_file_report(''.join(row))
        time.sleep(15)


        source_hash = row
        md5 = json.dumps(response['results']['md5'])
        sha1 = json.dumps(response['results']['sha1'])
        sha256 = json.dumps(response['results']['sha256'])
        detections = json.dumps(response['results']['positives'])
  

        query = (
            "UPDATE Downloads SET md5=(?), sha1=(?), sha256=(?), detections=(?)"
            "WHERE source_hash=(?)")
       

        crsr.execute(query, (md5,sha1,sha256,detections,source_hash))

        crsr.commit()

Below is the error.

461a26ad4219ea5e504fcdd1a61beb80b1a7fba8ff760e6696aa8cc3a3937887 is the hash value retrieved from the database to analyse.

Data type of the source_hash in the database is VARCHAR(1024).

pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
Column, parameter, or variable #5: Cannot find data type 461a26ad4219ea5e504fcdd1a61beb80b1a7fba8ff760e6696aa8cc3a3937887. (2715)
(SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180);
[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parameter or variable '@P5' has an invalid data type. (2724)")

Although similar errors are posted in many sources, no exact solution was mentioned.

Upvotes: 2

Views: 2362

Answers (1)

Dilumika Nawodya
Dilumika Nawodya

Reputation: 93

According to the error shown,

Parameter or variable '@P5' has an invalid data type. (2724)")

source_hash in SQL query (Near to WHERE clause) is expecting something (either string or int) but your source_hash variable is not giving the exact data type and in the code you used ''.join(raw) and source_hash = raw. This means raw variable could be a list or tuple if your code compiled successfully for that part.

For example,

..... WHERE source_hash = "452dferasd5481asd5"

this is what SQL query is expecting but source_hash variable could be a list or tuple,

['452dferasd5481asd5','',...] or ('452dferasd5481asd5', '',...)

So, try out printing your source_hash variable to check its data type. if it appears as in the example given, you can use source_hash[0] to get it.

Upvotes: 1

Related Questions