Comments (8)
The square brackets are normal; they are how unixODBC formats the string values in the trace file.
However, the connection string is enclosed in double-quotes for pyodbc but not for isql:
isql
Str In = [DRIVER={ODBC Driver 18 for SQL Server};SERVER=XXXXXX,1433;DATABASE=XXXXXX;UID=XXXXXX...][length = 149 (SQL_NTS)]
pyodbc
Str In = ["DRIVER={ODBC Driver 18 for SQL Server};SERVER=tcp:XXXXXXX,1433;DATABASE=XXXXXXX;UID=XXXXX...][length = 151 (SQL_NTS)]
That's the difference.
from pyodbc.
Is your Python code running in any sort of special environment like Anaconda or similar?
from pyodbc.
Hi @gordthompson, negative. We pip install directly to the global python installation in the container.
For demonstration purposes:
FROM python:3.11
RUN get all the required linux packages, including ms odbc 18, etc, etc
RUN pip install --no-cache-dir --upgrade -r /code/requirements.txt
CMD start app
from pyodbc.
Compare an ODBC trace of the working and non-working case.
I suspect something is different with the connection string (escaping special characters in passwords?)
from pyodbc.
Hi @v-chojas, I will gladly supply said traces. Could you instruct me or point me in the direction of how to get the types of traces that you are looking for?
from pyodbc.
https://github.com/mkleehammer/pyodbc/wiki/Troubleshooting-%E2%80%93-Generating-an-ODBC-trace-log
from pyodbc.
Morning from Europe @gordthompson and @v-chojas ☀️ Thanks for sharing the troubleshooting page.
isql
[ODBC][26][1715065290.944892][__handles.c][499] Exit:[SQL_SUCCESS] Environment = 0xaaaae2575470 [ODBC][26][1715065290.944967][SQLAllocHandle.c][395] Entry: Handle Type = 2 Input Handle = 0xaaaae2575470 UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'[ODBC][26][1715065290.945041][SQLAllocHandle.c][531]
Exit:[SQL_SUCCESS]
Output Handle = 0xaaaae2576180
[ODBC][26][1715065290.945150][SQLDriverConnect.c][751]
Entry:
Connection = 0xaaaae2576180
Window Hdl = (nil)
Str In = [DRIVER={ODBC Driver 18 for SQL Server};SERVER=XXXXXX,1433;DATABASE=XXXXXX;UID=XXXXXX...][length = 149 (SQL_NTS)]
Str Out = 0xffffcaa1de18
Str Out Max = 2048
Str Out Ptr = (nil)
Completion = 0
[ODBC][26][1715065291.385037][__handles.c][499]
Exit:[SQL_SUCCESS]
Environment = 0xaaaae25c6470
[ODBC][26][1715065291.385086][SQLGetEnvAttr.c][157]
Entry:
Environment = 0xaaaae25c6470
Attribute = -534
Value = 0xffffcaa1b648
Buffer Len = 128
StrLen = 0xffffcaa1b5e4
[ODBC][26][1715065291.385095][SQLGetEnvAttr.c][254]Error: HY092
[ODBC][26][1715065291.385108][SQLFreeHandle.c][220]
Entry:
Handle Type = 1
Input Handle = 0xaaaae25c6470
DIAG [01000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Changed database context to 'XXXXXXXX'.[ODBC][26][1715065291.385144][__handles.c][499]
Exit:[SQL_SUCCESS]
Environment = 0xaaaae2719370
[ODBC][26][1715065291.385155][SQLGetEnvAttr.c][157]
Entry:
Environment = 0xaaaae2719370
Attribute = -534
Value = 0xffffcaa1b648
Buffer Len = 128
StrLen = 0xffffcaa1b5e4
[ODBC][26][1715065291.385168][SQLGetEnvAttr.c][254]Error: HY092
[ODBC][26][1715065291.385180][SQLFreeHandle.c][220]
Entry:
Handle Type = 1
Input Handle = 0xaaaae2719370
DIAG [01000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Changed language setting to us_english.[ODBC][26][1715065291.385268][__handles.c][499]
Exit:[SQL_SUCCESS]
Environment = 0xaaaae27197c0
[ODBC][26][1715065291.385280][SQLGetEnvAttr.c][157]
Entry:
Environment = 0xaaaae27197c0
Attribute = -534
Value = 0xffffcaa1b648
Buffer Len = 128
StrLen = 0xffffcaa1b5e4
[ODBC][26][1715065291.385289][SQLGetEnvAttr.c][254]Error: HY092
[ODBC][26][1715065291.385299][SQLFreeHandle.c][220]
Entry:
Handle Type = 1
Input Handle = 0xaaaae27197c0
[ODBC][26][1715065291.385313][SQLDriverConnect.c][1809]
Exit:[SQL_SUCCESS_WITH_INFO]
Connection Out [DRIVER=ODBC Driver 18 for SQL Server;SERVER=tcp:XXXXXXXX....]
[ODBC][26][1715065291.385323][SQLGetFunctions.c][151]
Entry:
Connection = 0xaaaae2576180
Id = SQLMoreResults
Supported = 0xaaaabc910014
[ODBC][26][1715065291.385332][SQLGetFunctions.c][209]
Exit:[SQL_SUCCESS]
Supported = 0xaaaabc910014 -> 1
pyodbc
# cat odbctrace.log [ODBC][36][1715065803.747851][__handles.c][499] Exit:[SQL_SUCCESS] Environment = 0xaaab01c42a00 [ODBC][36][1715065803.747898][SQLSetEnvAttr.c][189] Entry: Environment = 0xaaab01c42a00 Attribute = SQL_ATTR_ODBC_VERSION Value = 0x3 StrLen = 4 [ODBC][36][1715065803.747906][SQLSetEnvAttr.c][381] Exit:[SQL_SUCCESS] [ODBC][36][1715065803.748002][SQLDrivers.c][235] Entry: Environment = 0xaaab01c42a00 Direction = 2 [ODBC][36][1715065803.748034][SQLDrivers.c][510] Exit:[SQL_SUCCESS] [ODBC][36][1715065803.748041][SQLDrivers.c][235] Entry: Environment = 0xaaab01c42a00 Direction = 1 [ODBC][36][1715065803.748054][SQLDrivers.c][510] Exit:[SQL_NO_DATA] [ODBC][36][1715065803.748086][SQLAllocHandle.c][395] Entry: Handle Type = 2 Input Handle = 0xaaab01c42a00 UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'[ODBC][36][1715065803.748116][SQLAllocHandle.c][531]
Exit:[SQL_SUCCESS]
Output Handle = 0xaaab01c45ea0
[ODBC][36][1715065803.749377][SQLDriverConnectW.c][298]
Entry:
Connection = 0xaaab01c45ea0
Window Hdl = (nil)
Str In = ["DRIVER={ODBC Driver 18 for SQL Server};SERVER=tcp:XXXXXXX,1433;DATABASE=XXXXXXX;UID=XXXXX...][length = 151 (SQL_NTS)]
Str Out = (nil)
Str Out Max = 0
Str Out Ptr = (nil)
Completion = 0
[ODBC][36][1715065803.749392][SQLDriverConnectW.c][658]Error: IM002
[ODBC][36][1715065803.749427][SQLGetDiagRecW.c][535]
Entry:
Connection = 0xaaab01c45ea0
Rec Number = 1
SQLState = 0xffffda46b660
Native = 0xffffda46b63c
Message Text = 0xaaab01c585e0
Buffer Length = 1023
Text Len Ptr = 0xffffda46b63a
[ODBC][36][1715065803.749439][SQLGetDiagRecW.c][596]
Exit:[SQL_SUCCESS]
SQLState = [IM002]
Native = 0xffffda46b63c -> 0 (32 bits)
Message Text = [[unixODBC][Driver Manager]Data source name not found and no default driver specified]
[ODBC][36][1715065803.749451][SQLFreeHandle.c][290]
Entry:
Handle Type = 2
Input Handle = 0xaaab01c45ea0
[ODBC][36][1715065803.749458][SQLFreeHandle.c][339]
Exit:[SQL_SUCCESS]
Some notable differences are the length of the connection string does seem to differ for some reason (which it shouldn't).
Notably, the connection string seems to be enclosed with []
. I have printed the connection string the row prior to connecting, such as:
print(config["db_connection_string"])
self.conn = pyodbc.connect(config["db_connection_string"])
The connection string is then printed in the expected format without the []
.
Could this be it?
from pyodbc.
I sincerely apologise for the oversight on my part. It was a foolish error that I should not have missed. Thank you for your patience and support 🙏🏽
from pyodbc.
Related Issues (20)
- sqlalchemy.exc.OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000102:SSL routines::unsupported protocol] (-1) (SQLDriverConnect)') HOT 1
- Connection is busy with results for another command (0) (SQLExecDirectW) HOT 3
- Transaction is not getting rolled back HOT 7
- MACOS 系统使用 HOT 4
- Memory leak when passing strings within table value parameter to stored proc HOT 9
- Add support for SQLTablePrivileges
- Login Timeout Error when connecting to Synapse database using pyodbc python library in Azure synapse notebook via Managed Identity HOT 1
- reference count encoding in the connect function HOT 5
- PyToCType uses internal function removed from Python 3.13 HOT 3
- I am trying to install pyodbc in MacBook Pro M3 chip - arm64. The drivers are installed succesfully for odbc 13,17 and 18. But pyodbc is not able to detect the drivers.:
- I am trying to install pyodbc in MacBook Pro M3 chip - arm64. The drivers are installed succesfully for odbc 13,17 and 18. But pyodbc is not able to detect the drivers. ERROR: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)") HOT 5
- Executing multiple queries HOT 1
- pyodbc.connect() gives an error when executing it with pytest HOT 1
- Multiple threads querying same table takes a long time HOT 19
- Timeout not being enforced in SQL query HOT 7
- How to use Federated credentials with service principal to auth the Azure resources HOT 7
- Ansi to Unicode conversion error (noob) HOT 9
- How to surface azure active directory service principal related errors HOT 3
- Do we need to close cursor in this case? HOT 9
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pyodbc.