sqlanywhere / sqlanydb Goto Github PK

@gperrow-SAP
It would be useful have a method to import data from a cvs file with INPUT statement instead parse row by row with INSERT and execute method. I think this could be more efficient in DataBase side for large amount of data.
Just like postgres interface psycopg2 "copy_from"

Hello! Would you kindly upload the latest release 1.0.9 to pypi?

Looks like fetching large (>1000) result sets over the network is slow when latency is couple of microseconds. Maybe prefetching is disabled ?

see

http://sqlanywhere-forum.sap.com/questions/27823/why-is-python-sqlanydb-so-slow-when-fetching-resultsets-with-sap-iq-and-wan

I'm having library issues with sqlanydb on CentOS 7.4.1708 running Python 2.7

What I've done:

• Installed SAP SQL Anywhere 17.0
• Created a simple test script:

#!/usr/bin/env python

import sqlanydb

conn = sqlanydb.connect(uid='', pwd='', host='***', ServerName='***')

The required *.so files could not be found after that, so I tried this (symlinks would've probably been the better choice here):

• Copied libraries from /opt/sqlanywhere17/lib32 to /usr/lib
• Copied libraries from /opt/sqlanywhere17/lib64 to /usr/lib64

Now I receive the following exception:

Traceback (most recent call last):
  File "./anydb-test.py", line 5, in <module>
    conn = sqlanydb.connect(uid='', pwd='', host='***', ServerName='***')
  File "/usr/lib/python2.7/site-packages/sqlanydb.py", line 522, in connect
    return Connection(args, kwargs)
  File "/usr/lib/python2.7/site-packages/sqlanydb.py", line 538, in __init__
    parent = Connection.cls_parent = Root("PYTHON")
  File "/usr/lib/python2.7/site-packages/sqlanydb.py", line 478, in __init__
    API_VERSION_EX)
sqlanydb.InterfaceError: ('Failed to initalize dbcapi context, dbcapi version 2 required. Perhaps you are missing some sqlanywhere libaries?', 0)

The dbcapi lib was copied from /opt/sqlanywhere17/lib64, so it should be the correct version.
Any ideas?

I am attempting to use this to connect to a SQL Anywhere 17 database, but unfortunately I can't complete the initial test upon installing the application. What am I doing wrong? Here is the traceback:

`InterfaceError                            Traceback (most recent call last)
Cell In[3], line 2
      1 import sqlanydb
----> 2 conn = sqlanydb.connect(uid='dba', pwd='sql', eng='demo', dbn='demo' )
      3 curs = conn.cursor()
      4 curs.execute("select 'Hello, world!'")

File /Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlanydb.py:566, in connect(*args, **kwargs)
    564 def connect(*args, **kwargs):
    565     """Constructor for creating a connection to a database."""
--> 566     return Connection(args, kwargs)

File /Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlanydb.py:582, in Connection.__init__(self, args, kwargs, parent)
    578 if parent == None:
    579 
    580     # cache the Root() object so we don't load it every time
    581     if Connection.cls_parent == None:
--> 582         parent = Connection.cls_parent = Root("PYTHON")
    583     else:
    584         parent = Connection.cls_parent

File /Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlanydb.py:496, in Root.__init__(self, name)
    493 def __init__(self, name):
    495     lg.debug("Attempting to load dbcapi library")
--> 496     self.api = load_library(os.getenv( 'SQLANY_API_DLL', None ), 'dbcapi.dll', 'libdbcapi_r.so',
    497                             'libdbcapi_r.dylib')
    498     ver = c_uint(0)
    499     try:

File /Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/sqlanydb.py:488, in load_library(*names)
    486     except OSError as ose:
    487         continue
--> 488 raise InterfaceError("Could not load dbcapi.  Tried: " + ','.join(map(str, names)))

InterfaceError: ('Could not load dbcapi.  Tried: /Applications/SQLAnywhere17/System/lib64/libdbcapi_r.dylib,dbcapi.dll,libdbcapi_r.so,libdbcapi_r.dylib', 0)`

When using this module on OSX one must tweak the search paths so that the dependent modules can be loaded.

I use this environment variable to specify where to load the dylib:
declare -x SQLANY_API_DLL="/Applications/SQLAnywhere17/System/lib64/libdbcapi_r.dylib"

However, this will still fail unless you modify the locations of the dynamic modules.

jamess-mbp-2016:lib64 jamesrusso$ otool -L libdbcapi_r.dylib 
libdbcapi_r.dylib:
	libdbcapi_r.dylib (compatibility version 0.0.0, current version 0.0.0)
	**/Applications/SQLAnywhere17/System/lib64/**libdblib17_r.dylib (compatibility version 0.0.0, current version 0.0.0)
	**/Applications/SQLAnywhere17/System/lib64/**libdbtasks17_r.dylib (compatibility version 0.0.0, current version 0.0.0)
	/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1213.0.0)
	/usr/lib/libstdc++.6.dylib (compatibility version 7.0.0, current version 104.1.0)

Then do the same for libdblib17_r and libdbtasks17_r

After all this, I made progress but am now getting this error...

sqlanydb.OperationalError: ('Connection error: Missing or invalid encryption DLL "/Applications/SQLAnywhere17/System/', -832)

I know its supposed to be loading libdbrsa17_r.dylib, but I don't see that as a dependency?

This all works if I put my python script in the lib64 directory.

Any ideas?

Hello, I am writing a client that connects to a SQL Anywhere 9 server (which comes with dblib9.dll). But SQL Anywhere 9 does not come with dbcapi.dll. Is there a way I can get the required dbcapi.dll which works with dblib9? The earliest version of SQL Anywhere that I have found that comes with dbcapi.dll is SQL Anywhere 12.

In general, does sqlanydb support connecting to multiple versions of the server?

Thanks,
David

@DanCummins-SAP so I've noticed that sqlanydb has been developed in private lately, given there's been PyPI releases but no activity here. What is the state of this repo? Is there a way to access history? Where should be report issues?

trying to use a Numeric(11) the package fail when trying to pack

sqlanydb.py line 430

        size = param.value.buffer_size
        buffer = create_string_buffer(value)
    else:
        buffer = **create_string_buffer(pack(fmt, value))**
        size = length = calcsize(fmt)
    param.value.buffer = cast(buffer, POINTER(c_char))

i am using flask and sqlalchemy

sqlanydb needs some other lib to run?

Traceback (most recent call last):
File "/home/<user_home>/test.py", line 1, in
import sqlanydb
File "build/bdist.linux-x86_64/egg/sqlanydb.py", line 469, in
File "build/bdist.linux-x86_64/egg/sqlanydb.py", line 471, in Connection
File "build/bdist.linux-x86_64/egg/sqlanydb.py", line 422, in init
File "build/bdist.linux-x86_64/egg/sqlanydb.py", line 416, in load_library
sqlanydb.InterfaceError: Could not load dbcapi. Tried: dbcapi.dll,libdbcapi_r.so,libdbcapi_r.dylib
Exception AttributeError: "'Root' object has no attribute 'api'" in <bound method Root.del of <sqlanydb.Root object at 0x7f9a633ea6d0>> ignored

according to SQL Anywhere 12.0.1 > SQL Anywhere Server - Database Administration > Database configuration > International languages and character sets > Locales,there is a default charset iso_1 , and it is not in the codecs .so it will cause error in the charset check.

/opt/conda/lib/python3.6/site-packages/sqlanydb.py in __init__(self, args, kwargs, parent)
    572                 if isinstance(char_set, bytes):
    573                     char_set = char_set.decode()
--> 574                 if codecs.lookup(char_set):
    575                     self.valueof = mk_valueof((A_BINARY,), char_set)
    576                     self.assign = mk_assign(char_set)

LookupError: unknown encoding: iso_1

Thanks

Hello,

I am experiencing an error when trying to start a Python application that uses the sqlanydb library to connect to an SQL Anywhere database. The error message is:

sqlanydb.InterfaceError: ('Could not load dbcapi. Tried: C:\Program Files\SQL Anywhere 16\bin64,dbcapi.dll,libdbcapi_r.so,libdbcapi_r.dylib', 0)

This error indicates that the sqlanydb library is unable to load the dbcapi library, which is required to connect to SQL Anywhere databases. I have installed SQL Anywhere 16 and have verified that the dbcapi.dll file is located in the C:\Program Files\SQL Anywhere 16\bin64 directory.

I have also added the SQLANY_API_DLL environment variable with the full path to the dbcapi.dll file, but the problem persists.

Could you please advise on how to resolve this error?

Environment:

Operating system: Windows 11
Python: 3.11
sqlanydb: 1.0.13
SQL Anywhere 16

I would appreciate your help in resolving this issue so that I can use the Python application to its full potential.

Thank you for your time and attention to this matter.

Sincerely,
Pietro

Hi Team,

I am trying to use sqlanydb Python API to invoke a stored procedure deployed in Sybase IQ. I am calling this stored procedure successfully in any of the DB Tool like DBArtison and DBVisualizer (possibly using JDBC driver). I can always return results.

Unfortunately I am using given sample to execute this stored procedure but getting funny error saying failed to insert into a temp table. This actually happened right after a inner stored procedure call of my stored procedure.

I remember I encountered similar situation using pyodbc before but issue cleared after adding "SET ANSINULL OFF" in the top. I tried here but it doesn't turn out to have the same happy ending.

I tried couple tweaking over past few days 12 hours per day but this i still not working.

Do you have any suggestion?

Thanks.

Fortitude

When I connect explicitly, conn = sqlanydb.connect(), type(conn) is <class 'sqlanydb.Connection'>.

If I have the connect in a with statement, with sqlanydb.connect() as conn:
type(conn) is <class 'sqlanydb.Cursor'>.

Is this the intended effect? I can live with this.
testsqlanywith.pdf

Thank you,
Jeffrey Sue

In python 3 print became a function instead of statement, if you execute the test_sqlany.py example you get a compilation error ->

If you just add a parenthesis to the example in this line then it compiles in python 3

print("SQL Anywhere says: %s" % curs.fetchone())

Want me to change it? Or add a note?

When i try to insert blob sqlanydb treat bytes as A_STRING and try to decode bynary data.
In fact in
mk_assign() string elif isinstance(value, Binary) on type bytes never give True so it's fallback to A_STRING.
I've changes it to elif isinstance(value, bytes) and al works perfect.

goblin:dbanywhere $ source dbanwhere17/.../sa_config.sh

goblin:dbanywhere $ python manage.py migrate

[...]
django.core.exceptions.ImproperlyConfigured: 'sqlany_django' isn't an available database backend.
Try using 'django.db.backends.XXX', where XXX is one of:
    u'base', u'mysql', u'oracle', u'postgresql_psycopg2', u'sqlite3'
Error was: cannot import name BaseDatabaseClient

goblin:dbanywhere $ more test_sqlany.py

  import sqlanydb
  conn = sqlanydb.connect(uid='dba', pwd='sql123', eng='django', dbn='django')
  curs = conn.cursor()
  curs.execute("select 'Hello, world!'")
  print "SQL Anywhere says: %s" % curs.fetchone()
  curs.close()
  conn.close()

goblin:dbanywhere $ python test_sqlany.py

  SQL Anywhere says: Hello, world!

I use Django 1.8.5, the test file works just fine.
Please, advice.

The exceptions in the sqlanydb.py source file contain a bug where each class definition misapplies the "super" keyword.

class Error(Exception):
    def __init__(self,err,sqlcode=0):
        self._errortext = err
        self._errorcode = sqlcode
    @property
    def errortext(self): return self._errortext
    @property
    def errorcode(self): return self._errorcode
...
class InterfaceError(Error):
    """Raise for interface, not database, related errors."""
    def __init__(self, *args):
        super(Error,self).__init__(*args)

In the above code, super(Error,self) will point to the parent class of Error in the MRO (e.g. Exception), not the parent class of Interface error as intended. This means that inspecting sqlanydb exceptions fails with an attribute error, e.g. AttributeError: 'DatabaseError' object has no attribute '_errortext'.

It would also be nice to add repr and str overloads, so error print outs are more informative (see below).

Reproducible test case below:

import unittest
import sqlanydb

class CorrectDatabaseError(sqlanydb.Error):
    def __init__(self, err, sqlcode=0):
        super(CorrectDatabaseError, self).__init__(err, sqlcode)
    def __repr__(self):
        return "%s(%s, %s)" % (self.__class__.__name__, repr(self.errortext),
                               repr(self.errorcode))
    def __str__(self):
        return repr((self.errortext, self.errorcode))

class SqlAnyDBTest(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        cls.errortext = "Connection timeout"
        cls.errorcode = -42

    def test_error_fields(self):
        e = sqlanydb.DatabaseError(self.errortext, self.errorcode)
        self.assertEqual(self.errortext, e.errortext)
        self.assertEqual(self.errorcode, e.errorcode)

    def test_correct_fields(self):
        e = CorrectDatabaseError("Connection timeout", -42)
        self.assertEqual(self.errortext, e.errortext)
        self.assertEqual(self.errorcode, e.errorcode)


if __name__ == "__main__":
    unittest.main()```

Could not load dbcapi.dll... on Python 3.8.5 32bit doesnt work, but work on Python 3.7.8 32bit

After SQL Anywhere server was updated to 17.0.10.5963 version, python application get error about wrong user id or password. Tried from MacOS, linux, Windows. The same error on the all platforms.

But SQL Central(Windows version) with the same credentials works perfect, connects to server without problem.

SQL Anywhere client version is 17.0.11.7058
sqlanydb 1.0.11

I have a Django Script that uses sqlanydb, that gives MemoryErorr. I cannot figure out what's wrong. I have another Windows Server running the exact same version of SQL Anywhere and that works fine.

Please help!

Performing system checks...

Unhandled exception in thread started by <function check_errors.<locals>.wrapper at 0x0000000003DFC048>
Traceback (most recent call last):
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\django\utils\autoreload.py", line 225, in wrapper
    fn(*args, **kwargs)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\django\core\management\commands\runserver.py", line 121, in inner_run
    self.check(display_num_errors=True)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\django\core\management\base.py", line 364, in check
    include_deployment_checks=include_deployment_checks,
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\django\core\management\base.py", line 351, in _run_checks
    return checks.run_checks(**kwargs)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\django\core\checks\registry.py", line 73, in run_checks
    new_errors = check(app_configs=app_configs)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\django\core\checks\urls.py", line 40, in check_url_namespaces_unique
    all_namespaces = _load_all_namespaces(resolver)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\django\core\checks\urls.py", line 57, in _load_all_namespaces
    url_patterns = getattr(resolver, 'url_patterns', [])
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\django\utils\functional.py", line 36, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\django\urls\resolvers.py", line 536, in url_patterns
    patterns = getattr(self.urlconf_module, "urlpatterns", self.urlconf_module)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\django\utils\functional.py", line 36, in __get__
    res = instance.__dict__[self.name] = self.func(instance)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\django\urls\resolvers.py", line 529, in urlconf_module
    return import_module(self.urlconf_name)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\importlib\__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 994, in _gcd_import
  File "<frozen importlib._bootstrap>", line 971, in _find_and_load
  File "<frozen importlib._bootstrap>", line 955, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 665, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "C:\SumoPinRisk\Sumo\urls.py", line 18, in <module>
    from PinRisk import views
  File "C:\SumoPinRisk\PinRisk\views.py", line 4, in <module>
    from . import forms
  File "C:\SumoPinRisk\PinRisk\forms.py", line 6, in <module>
    class RiskParamForm(forms.Form):
  File "C:\SumoPinRisk\PinRisk\forms.py", line 9, in RiskParamForm
    choices=[(tdr,tdr) for tdr in Trader.all()]
  File "C:\SumoPinRisk\PinRisk\models.py", line 86, in all
    conn = sqlanydb.connect(uid='dba', pwd='dba', eng='AQTOR')
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\sqlanydb.py", line 522, in connect
    return Connection(args, kwargs)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\sqlanydb.py", line 571, in __init__
    char_set = cur.fetchone()[0]
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\sqlanydb.py", line 820, in fetchone
    rows = self.fetchmany(size=1)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\sqlanydb.py", line 817, in fetchmany
    return [row for i,row in zip(range(size), self.rows())]
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\sqlanydb.py", line 817, in <listcomp>
    return [row for i,row in zip(range(size), self.rows())]
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\sqlanydb.py", line 811, in rows
    yield tuple(self.converter.gen(v3list(list(self.values()))))
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\sqlanydb.py", line 803, in values
    yield (self.valueof)(value)
  File "C:\ProgramData\Anaconda3\envs\pinrisk\lib\site-packages\sqlanydb.py", line 390, in valueof
    return data.buffer[:data.length.contents.value]
MemoryError

It could be useful to implement a method returning query result like this:
[{'field_name': 'value_1', 'another_field_name': 'value_2'}]

The method close() is throwing an OSError exception

Exception ignored in: <function Connection.del at 0x0000024ADCA2BB80>
Traceback (most recent call last):
File ".venv\lib\site-packages\sqlanydb.py", line 596, in del
self.close()
File ".venv\lib\site-packages\sqlanydb.py", line 651, in close
self.api.sqlany_disconnect(c)
OSError: exception: access violation writing 0x0000000000000024

This seems to be related to dbcapi.dll.
It has been tested on several computers, using both python 3.9 and python 3.10, on Windows 10 computers and SQL Anywhere 17 with the same error every time.

After updating to sqla17_client_macosx.17010_6178 from https://wiki.scn.sap.com/wiki/display/SQLANY/SAP+SQL+Anywhere+Database+Client+Download the driver stopped working. At least NULL interpolation handling is broken now.

Here's an SSCCE:

create table new_driver_test (col1 timestamp with time zone null);

In [1]: import  sqlanydb; conn = sqlanydb.connect(links="tcpip(host=localhost;DoBroadcast=None)", ServerName="XXX", uid="DBA", pwd=""); c = conn.cursor()
In [3]: c.execute("insert into new_driver_test values (?)", (None,))
---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-3-954ca3385b83> in <module>
----> 1 c.execute("insert into new_driver_test values (?)", (None,))

~/Work/customer-api/.direnv/python-3.8.6/lib/python3.8/site-packages/sqlanydb.py in execute(self, operation, parameters)
    792
    793     def execute(self, operation, parameters = ()):
--> 794         self.executemany(operation, [parameters])
    795
    796     def callproc(self, procname, parameters = ()):

~/Work/customer-api/.direnv/python-3.8.6/lib/python3.8/site-packages/sqlanydb.py in executemany(self, operation, seq_of_parameters)
    769                          for k, col in enumerate(parameters[:bind_count])]
    770                 if not self.api.sqlany_execute(self.stmt):
--> 771                     self.handleerror(*self.parent.error())
    772
    773                 try:

~/Work/customer-api/.direnv/python-3.8.6/lib/python3.8/site-packages/sqlanydb.py in handleerror(self, errorclass, errorvalue, sqlcode)
    691         if errorclass:
    692             eh = self.errorhandler or standardErrorHandler
--> 693             eh(self.parent, self, errorclass, errorvalue, sqlcode)
    694
    695     def __stmt_get(self):

~/Work/customer-api/.direnv/python-3.8.6/lib/python3.8/site-packages/sqlanydb.py in standardErrorHandler(connection, cursor, errorclass, errorvalue, sqlcode)
    377             cursor.messages.append(error)
    378     if errorclass != Warning:
--> 379         raise errorclass(errorvalue,sqlcode)
    380
    381

OperationalError: (b'Cannot convert integer to timestamp with time zone', -157)

Without interpolation it works fine:

In [4]: c.execute("insert into new_driver_test values (NULL)")

In [5]: c.execute("insert into new_driver_test values (NULL)", ())

This is a big deal because it means we're stuck with old drivers potentially forever. Given that there hasn't been any activity since 2018, is there any chance this will be remediated? What are the options to paying customers? Who can we talk to for SAP to pick this up again?

I am attempting to setup sqlanydb in a Docker container, so I need to install via script.
The sqlanydb is no problem as it is just a pip install.

However, that has a dependency on the SqlAnywhere client install. Which needs to also be a silent install.

If I do a UI install you go through several screens and it all works. (never asked for a Key in UI install.)

following the docs on silent setup. (see example from site) I cant install sqlany_client64 without a Key. And I don't have a correct key?

Any pointers to the process/examples.

setup -k NEEDA-REAL0-KEY12-34567-89012 -install sqlany64,sqlany_client64 -ss
-I_accept_the_license_agreement -name "Joe Jones" -company "SAP AG"

the charset config(pass by user) in connect should hava higher priority than other