Question Re: pyodbc issues (Python) in Technology Q&A

pyodbc issues (Python)

Baron — Wed, 20 Apr 2022 10:09:06 GMT

I am trying to connect to a SQL Anywhere 17 Database from Python using pyodbc Module.

The connect looks like: mydb=pyodbc.connect('DSN=mydsn;UID=dba;pwd=mypwd;Company=mycompany;Application=myapp;Signature=mysignature')

The database runs as a service (dbsvc), and the connection works only if the the database doesnt have other connections open. i.e. if I connect to the database using dbisql then has my Python script no any chance to connect to the DB and I get the following error:

Unable to initialize performance monitor data area; server startup continuing
SQL Anywhere Personal Server Version 17.0.9.4803
Developer edition, not licensed for deployment.
Copyright © 2018 SAP SE or an SAP affiliate company.
All rights reserved.
Use of this software is governed by the SAP Software Use Rights Agreement.
Refer to http://www.sap.com/about/agreements.html.
Connection limit (Personal Server): 10
Processors detected: 8 logical processor(s) on 4 core(s) on 1 physical processor(s)
Processor license restriction (Personal Server): all logical processors on at most 4 core(s) on at most 1 physical processor(s)
This server is licensed to use: all logical processors on at most 4 core(s) on at most 1 physical processor(s)
Processors in use by server: 8 logical processor(s) on 4 core(s) on 1 physical processor(s)
This server is licensed to:
    Developer Edition
    Restricted Use
Running Windows 10 Build 19043  on X86_64
Server built for X86_64 processor architecture
51464K of memory used for caching
Minimum cache size: 51452K, maximum cache size: 7453104K
Using a maximum page size of 2048 bytes
Multiprogramming level: 20
Database server shutdown due to startup error
Database server stopped at Wed Apr 20 2022 16:08

Re: pyodbc issues (Python)

VolkerBarth — Wed, 20 Apr 2022 11:01:42 GMT

If you specify a DSN, do the DSN's connection parameters conflict with the running database, say, you are trying to explicitly start a database file that is already running - possibly with a different name? You might use a DSN-less connection string, see your older question here.

To add: Do you use the -oe server option to log startup errors in a different message log? If so, what does that tell?

Re: pyodbc issues (Python)

Baron — Thu, 21 Apr 2022 03:15:01 GMT

I can't use DSN-less connection, since the servername varies among devices, and only the DSN name is fix.

BTW, the other connection (over dbisql) uses also same DSN, so there might not be any chance for a conflict.

Re: pyodbc issues (Python)

VolkerBarth — Thu, 21 Apr 2022 03:27:37 GMT

Well, so what's in the DSN? And what kind of DSN is this - system or user?

Note that system DSNs with the same name are not shared between 32-bit and 64-bit. May this have an effect here (in case you are using a 64-bit DBISQL and a 32-bit Python process or vice versa)?

Re: pyodbc issues (Python)

chris_keating — Thu, 21 Apr 2022 08:18:59 GMT

Are you connecting to an already running database server? The engine log posted appears to be suggesting that the database server is being autostarted. It is attempting to start a personal server (dbengX). And, there is already an engine running as indicated by the first line of the engine log.

Adding LOG= to the connection string will log connection diagnostic info and include the actual connection string that is used for connecting (logged as "Attempting to connect using:". Please note that the following "Company=mycompany;Application=myapp;Signature=mysignature" is not valid connection parms.

Here is an example connection with LOG:

mydb=pyodbc.connect('DSN=mydsn;UID=dba;pwd=mypwd;log=<path>\\filename' )

I would also run the server with -z to collect server side communication logging i.e., it will log connection attempts.

My guess is that the database server name and database name defined in the DSN do not match the running database server and then it attempts to start a database server and is unable. The client and server communication diagnostic logs will collect the information that will identify the cause.

Re: pyodbc issues (Python)

jack_schueler — Thu, 21 Apr 2022 14:10:50 GMT

I also recommend that you include Server=xxx in your connection string or data source specifying the server name. I find that omitting it prevents connecting to a personal server running as a service.

Re: pyodbc issues (Python)

Baron — Fri, 22 Apr 2022 05:59:53 GMT

Is there any documentation how the connection string should look like?

Please note that the following "Company=mycompany;Application=myapp;Signature=mysignature" is not valid connection parms.

where should I then define my authentication string?

Re: pyodbc issues (Python)

VolkerBarth — Fri, 22 Apr 2022 06:56:39 GMT

If you cannot set the connection_authentication option via code, it has to be specified via the InitString connection parameter, see the OLEDB sample here.

Re: pyodbc issues (Python)

chris_keating — Fri, 22 Apr 2022 08:11:00 GMT

You should start with the link textSQL Anywhere Database Administration | Database Connections.