64977

Why does my query break when it is parameterized?

<h3>Question</h3>

I have 2 tables - Sales and Product. Sales can store the product as Idn or Name (legacy design) and the Type column specifies the actual <em>type</em> associated to it. Product etc. is a subset table that is joined <em>into</em> this table to get the real data. (In this example, Product is a table that stores <em>Idn</em>'s to demonstrate the issue.)

Sales |------------|--------------------|----------------| | Idn | Product Idn/Name | Type | |------------|--------------------|----------------| | 1 | 1 | Number | |------------|--------------------|----- ----------| | 2 | Colgate | Word | |------------|--------------------|----------------| Product (Idn) |------------|------------------| | Idn | Some Info | |------------|------------------| | 1 | ... | |------------|------------------|

Normally, you should not join these tables on Product Idn because it has mixed data; but if you select the rows where LHS matches RHS, it works fine <sup>(1)</sup>. For example, if Product is a table that stores <em>Idn</em>s, the following query fails:

<pre class="lang-sql prettyprint-override">SELECT * from sales JOIN product on sales.pid = product.idn

but the following query works:

<pre class="lang-sql prettyprint-override">SELECT * from sales JOIN product on sales.pid = product.idn WHERE type = 'Number'

This also works as expected in Python 2 + SQLAlchemy + PyODBC as well. However, when I try this in Python 3 + SQLAlchemy + PyODBC, it gives me a datatype conversion error and it only happens when the query is parameterized!

Now if I make it u'number' in Python 2, it breaks there as well; and b'number' works in Python 3! I am guessing there is some issue with Unicode conversion. Is it trying to guess encoding and doing something wrong? Can I fix this by being more explicit?

The error received is:

Traceback (most recent call last): File "reproduce.py", line 59, in <module> print(cursor.execute(select_parametrized, ('number', 1)).fetchall()) pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error converting data type varchar to numeric. (8114) (SQLFetch)

What could be the issue here and is there any good ways to side-step the problem without doing things like convert (because it worked in a previous version)?

Here is a query that can be used to reproduce this issue with no side-effects (needs SQLAlchemy and PyODBC):

<pre class="lang-py prettyprint-override">import sqlalchemy import sqlalchemy.orm create_tables = """ CREATE TABLE products( idn NUMERIC(9) PRIMARY KEY ); CREATE TABLE sales( idn NUMERIC(9) PRIMARY KEY, pid VARCHAR(50) NOT NULL, type VARCHAR(10) NOT NULL ); """ check_tables_exist = """ SELECT * FROM products; SELECT * FROM sales; """ insert_values = """ INSERT INTO products (idn) values (1); INSERT INTO sales (idn, pid, type) values (1, 1, 'number'); INSERT INTO sales (idn, pid, type) values (2, 'Colgate', 'word'); """ select_adhoc = """ SELECT * FROM products JOIN sales ON products.idn = sales.pid AND sales.type = 'number' WHERE products.idn in (1); """ select_parametrized = """ SELECT * FROM products JOIN sales ON products.idn = sales.pid AND sales.type = ? WHERE products.idn in (?); """ delete_tables = """ DROP TABLE products; DROP TABLE sales; """ engine = sqlalchemy.create_engine('mssql+pyodbc://user:password@dsn') connection = engine.connect() cursor = engine.raw_connection().cursor() Session = sqlalchemy.orm.sessionmaker(bind=connection) session = Session() session.execute(create_tables) try: session.execute(check_tables_exist) session.execute(insert_values) session.commit() print(cursor.execute(select_adhoc).fetchall()) print(cursor.execute(select_parametrized, ('number', 1)).fetchall()) finally: session.execute(delete_tables) session.commit()

1. This was a wrong assumption. It worked by chance - SQL's execution plan gave priority to this condition as explained here. It didn't do that when it became NVARCHAR.


<h3>Answer1:</h3>

SQLAlchemy generates this SQL script with your non-parameterized query (select_adhoc):

SELECT * FROM products JOIN sales ON products.idn = sales.pid AND sales.type = 'number' WHERE products.idn in (1);

But with the parameterized query (select_parametrized), it generates this: (I checked from SQL Server Profiler.)

declare @p1 int set @p1=NULL exec sp_prepexec @p1 output,N'@P1 nvarchar(12),@P2 int',N' SELECT * FROM products INNER JOIN sales ON products.idn = sales.pid AND sales.type = @P1 WHERE products.idn in (@P2); ',N'number',1 select @p1

If you try this on SQL Server you will get the same exception:

<blockquote>

Msg 8114, Level 16, State 5, Line 32 Error converting data type varchar to numeric.

</blockquote>

The problem is at the @P1 parameter declaration -- it makes an implicit conversion to varchar (the type of sales.type) and that causes this problem. Probably Python 2 generates varchar?

If you change your query like this it will work correctly; or you need to change the type of sales.type to nvarchar.

select_parametrized = """ SELECT * FROM products INNER JOIN sales ON products.idn = sales.pid AND sales.type = CAST(? AS VARCHAR(50)) WHERE products.idn in (?); """

来源:https://stackoverflow.com/questions/61936620/why-does-my-query-break-when-it-is-parameterized

Recommend

  • Write data to a csv file Python
  • Using CompositeItemWriter the writer or classify method is not getting called
  • Update Stripe Credit Card with Coffeescript
  • How to detect a memory leak?
  • Get a list of who has what access to git repositories
  • Decompress string in java from compressed string in C#
  • Auto-incrementation with HSQLDB (2.2.8) + DDLUtils
  • Preventing Internet-accessing-method from delaying a toast popup
  • How to use OPENXML to load XML data into existing SQL Table?
  • Texture streaming in DirectX11, Immutable vs Dynamic
  • pass sessionid through jquery ajax call to php
  • Repeatable job for Laravel json api
  • How to use mixed C++ & .Net dll in node.js? (Error: abort() has been called)
  • Oracle BI Publisher - How to format numbers as text so that leading zeroes don't dissappear
  • How to convert SVG to jVectorMap format
  • Bulk loading into PostgreSQL from a remote client
  • integrity constraint violation: NOT NULL check constraint
  • create circular Auto Horizontal Scroll View?
  • How to put an object in the air?
  • Please update your Node runtime to version >=0.12.x
  • Sample deviceQuery cuda program
  • Android studio import problems. (Apktool)
  • Find all parks for a given zipcode with google maps
  • How to control xtics in gnuplot
  • AWS RDS Parameter Group not changing MySQL encoding
  • Run a form (insert/update/delete) from within a div using jquery
  • Connect to a local database from phpmyadmin with R
  • Building JavaFX 2.0 App on Mac, deploying on Windows
  • Create an Office365 mailbox from within C# Web API method
  • Julia 1.0 UndefVarError - Scope of Variable
  • How to resolve this in PHPUnit where it is asking me to set KERNEL_DIR in my phpunit.xml?
  • How to merge objects within array based on attribute
  • how to get the location(lat/lng) on google maps v3 from the location(x,y)
  • How to turn off notice reporting in xampp?
  • Drag and drop unicode TText in DelphiXe4
  • Debug `Unexpected end of JSON input Error` on content script
  • Bad automatic Triangulation with Mayavi for coloring a surface known only by its corner
  • Using Service Component Runtime
  • How to use FirstOrDefault inside Include
  • How to handle a codeigniter PDF generator