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

Question

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)")

Answer 1

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?

Answer 2

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.