search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.95k stars 563 forks source link

ODBC SQL type -151 is not supported #1308

Closed narminaziz closed 11 months ago

narminaziz commented 1 year ago

Hi, everyone. I am migrating data from MSSQL to Postgresql. There is one table with the column named SHAPE and this column contains geometry values and the data type is GEOMETRY. When I run my Python code, with pyodbc I join MSSQL and make Select statement from this table where SHAPE column included but it gives me "ODBC SQL type -151 is not supported yet column-index=27 type=-151, 'HY106' ". I am going to share scripts so you can see much more detailed information. I believe it is very rare problem and don't know precise workaround. We are running this code on Linux/Ubuntu 22.04 version. Drivers for SQL server are Microsoft ODBC 18 and 17 on Ubuntu. It is working on Windows OS without having any problem. But it throws errors on Ubuntu. What I am worried about is the maybe eligibility of the drivers on Windows that supports that datatype but Ubuntu drivers don't support.

Drivers on my Windows machine ['SQL Server', 'SQL Server Native Client RDA 11.0', 'PostgreSQL ODBC Driver(ANSI)', 'PostgreSQL ODBC Driver(UNICODE)', 'ODBC Driver 17 for SQL Server', 'Microsoft Access Driver (.mdb, .accdb)', 'Microsoft Excel Driver (.xls, .xlsx, .xlsm, .xlsb)', 'Microsoft Access Text Driver (.txt, .csv)', 'MySQL ODBC 8.2 ANSI Driver', 'MySQL ODBC 8.2 Unicode Driver']. In this list we are using 'SQL Server'

Drivers on my Ubuntu machine ['ODBC Driver 18 for SQL Server', 'ODBC Driver 17 for SQL Server'] are these.

issue1 issue2

Thanks for your time and consideration . I am looking forward to receiving feedback from you.

v-chojas commented 1 year ago

Are you using same version of pyODBC (and the Python script) on both machines?

gordthompson commented 1 year ago

As for the difference between Windows and Linux:

Drivers on my Windows machine ['SQL Server', … ]. In this list we are using 'SQL Server'

The ancient "SQL Server" driver on Windows was almost certainly released before MSSQL started supporting GEOMETRY type(s) and is probably returning a string representation of the data. The newer "ODBC Driver 18/17 for SQL Server" drivers on Linux are returning ODBC SQL type -151.

As for handling type -151:

Duplicate of #404.