question

CEO-8149 avatar image
0 Votes"
CEO-8149 asked ErlandSommarskog commented

Must declare the scalar variable @P160R

I noticed sometimes, when I try to retrieve data using the sql format of:

self.cursor.execute('SELECT FROM tablename WHERE column1 LIKE ? OR column2 LIKE ? OR column3 LIKE ?' , (searchbox, searchbox, searchbox) )

It usually gives me an error message: ODBC SQL server (SQL Server) Must declare the scalar variable "P160R". [SQL Server statement could not be prepared]

What sort of shit is this?

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hm, I don't exactly what is going on here, but I get the feeling that you are using the very old ODBC driver that comes with the OS. What does your connection string look like?

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 commented

cursor.execute is a Python command. This is a Microsoft SQL Server forum, not a Python forum. Please post your question on a Python forum.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The error message very clearly indicates that it comes from SQL Server. cursor.execute is obviously a client-side thing, so it can be different engines.

0 Votes 0 ·

Sorry, I said MySQL and meant Python. I corrected my post.

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @CEO-8149,

It seems that you use Python to retrieve data .

Please have a try like below, replace apostrophe with double quotes and make three searchboxes different.

 cursor.execute("SELECT FROM tablename WHERE column1 LIKE ? OR column2 LIKE ? OR column3 LIKE ?" , (searchbox1, searchbox2, searchbox3) )

ODBC SQL server (SQL Server) Must declare the scalar variable "P160R". [SQL Server statement could not be prepared]

You could find out where is "P160R" from. It is from one procedure,function or other.

Then you could troubleshoot the relationship between the select statement and variable "P160R".

You could also use a new verison of ODBC driver and have another try.

Please provide more details then we could check further.

Thank you for understanding!

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ErlandSommarskog commented

My guess is one of the values you are passing is "P160R", and you probably need something like:

self.cursor.execute('SELECT FROM tablename WHERE column1 LIKE ''?'' OR column2 LIKE ''?'' OR column3 LIKE ''?''' , (searchbox, searchbox, searchbox) )

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I would expect that to produce three instances of the error "Invalid column name '?'." The parameter placeholders should definitely not be quoted.

0 Votes 0 ·