Disallowed implicit conversion from data type ntext to data type varchar

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create a table with a type of varchar(1024)
2. Insert some data into the table.
3. Use 'update table set longcolumn = ? where id=1' and pass params
with length of 600 for the parameter.

What is the expected output? What do you see instead?
Expected to see the row updated. Instead, get an error:
Disallowed implicit conversion from data type ntext to data type varchar
It seems that it's calling sp_prepexec and setting the parameter type to ntext 
even though it would work as varchar or nvarchar.

What version of the product are you using? On what operating system?
latest version, Windows 2008 Web Server.

Please provide any additional information below.

Original issue reported on code.google.com by sp1d...@gmail.com on 1 May 2013 at 11:31

[GoogleCodeExporter]

It appears to not get the lengths correctly because the pyodbc uses the actual 
size of the input string as the length of the nvarchar. pypyodbc uses the 
default of 255, and when it's larger than that, it switches to ntext. 

Original comment by sp1d...@gmail.com on 1 May 2013 at 11:51

[GoogleCodeExporter]

It appears to be line 1048 that is the cause - My guess is it is assuming MS 
Access. Changing that from uppercase U to lowercase, and returning the true 
length of the field seems to fix it. Same thing with the S on line 1054. Line 
1203 in _bind_params needs to be fixed as well.

Original comment by sp1d...@gmail.com on 2 May 2013 at 12:15

[GoogleCodeExporter]

Accepted. I will look into this issue during weekends.

Original comment by jiangwen...@gmail.com on 2 May 2013 at 1:34

• Changed state: Accepted

[GoogleCodeExporter]

I've attached the modified pypyodbc.py that works. I don't know how to get a 
patch, and it doesn't include the dev features from github, but you can see the 
changes I made. I see that it makes queries to check data types... it's execing 
sp_datatype_info 93, 91, and 92. Checking the datetime types or something. 
Anyway, if you call sp_datatype_info with just the odbcver=3 you get a list of 
the datatypes and their maximum sizes. The maximum size of a varchar in SQL 
2000 (the version that I'm using) is 8000.

Original comment by sp1d...@gmail.com on 2 May 2013 at 5:20

Attachments:

• pypyodbc.py