Column, parameter, or variable #1: Cannot find data type READONLY

Samir Hamid S Aljuaid 21 Reputation points
2020-10-22T15:57:03.403+00:00

I had hard time to try insert into database, I am using pyodbc for tk interface
This what I have created in MSSMS :

CREATE TABLE mytest
( id_number int NULL,
name_t VARCHAR (25) NULL,
);

and

conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
'Server=xxxx\SQLEXPRESS;'
'Database=test1;'
'UID=xxxx;'
'PWD=xxx;')
cnxn = conn.cursor()
cnxn.execute( 'INSERT INTO mytest (id_number, name_t) VALUES (?,?)', number, name_t)
cnxn .commit()
cnxn .close()

ODBC Driver 17 for SQL Server: works good

I got this message and any explanation will help me

Thanks

('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #1: Cannot find data type READONLY. (2715) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Column, parameter, or variable #2: Cannot find data type READONLY. (2715); [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 '@P1' has an invalid data type. (2724); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Parameter or variable '@P2' has an invalid data type. (2724)")

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,515 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 98,726 Reputation points
    2020-10-23T15:35:01.053+00:00

    This script runs for me:

    import pyodbc
    
    conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
    'Server=.\SJUTTON;'
    'Database=tempdb;'
    'Trusted_connection=yes;')
    number = 14
    name_t = "This is the test"
    cnxn = conn.cursor()
    cnxn.execute( 'INSERT INTO mytest (id_number, name_t) VALUES (?,?)', number, name_t)
    cnxn .commit()
    cnxn .close()
    

    As Yithzak points out, we don't see you set number and name_t. One can note one thing about this snippet: we never declare the data types of the parameters passed. That is of course a very Python way of doing it, but T-SQL is not dynamically typed like Python. I guess what happens under the covers that Pyodbc sets the data type from the current type of the Python variable. But if the variable is something more complex, like an array what happens in that case?

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-10-23T01:56:32.067+00:00

    Hi @Samir Hamid S Aljuaid ,

    Thank you so much for posting here.

    Your issue mostly seems to be related with Python or pyodbc. We recommend you to post your issue in related Python forum.

    I also did some searching and found one small example of how to update in MS Access, from Python. You could try to adapt this to your specific scenario.

    #import pypyodbc  
    import pyodbc  
      
    # MS ACCESS DB CONNECTION  
    pyodbc.lowercase = False  
    conn = pyodbc.connect(  
        r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +  
        r"Dbq=C:\\path_here\\Northwind.mdb;")  
      
    # OPEN CURSOR AND EXECUTE SQL  
    cur = conn.cursor()  
      
    # Option 1 - no error and no update  
    cur.execute("UPDATE dbo_test SET Location = 'New York' Where Status = 'Scheduled'");  
    conn.commit()  
      
    cur.close()  
    conn.close()  
    

    If above is not working and you created one table valued parameter (TVP) ,then you could refer below example:

    CREATE TYPE [dbo].[MappingType] AS TABLE  
    (  
      [t_entityid] UNIQUEIDENTIFIER NOT NULL,  
      [t_threshold] FLOAT DEFAULT 0   
    )  
    GO  
      
    # import, connect, etc. ...  
      
    # -- The stored procedure definition is:  
    #  
    # CREATE PROCEDURE [dbo].[ExecuteMapping](  
    #     @id uniqueidentifier,  
    #     @values MappingType READONLY,  
    #     @threshold float  
    # )  
    # AS  
    # BEGIN  
    #  SET NOCOUNT ON;  
    #  SELECT @id AS thing;  
    # END  
      
    mappingvalues = []  
    mappingvalues.append(["8e4d7360-9a46-ea11-a812-000d3a54419d",0.6])  
    mappingvalues.append(["ed0ef314-9a46-ea11-a812-000d3a8d88aa",0.6])  
      
    params = ("1a1d243c-a587-4d10-995e-cbbf6cad4dce", mappingvalues, 0.5)  
    sqlCmd = "execute [dbo].[ExecuteMapping] @id=?, @values=?, @threshold=?"  
      
    sqlCursor = cnxn.cursor()  
    result = sqlCursor.execute(sqlCmd, params)  
    print(result.fetchall())  # [('1A1D243C-A587-4D10-995E-CBBF6CAD4DCE', )]  
    

    You could also refer more details from below links:
    Failure in executing stored procedure with table valued parameter #732
    How to create a Minimal, Reproducible Example

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments