search

ibmdb / python-ibmdbsa

Automatically exported from code.google.com/p/ibm-db.ibm-db-sa
Apache License 2.0
41 stars 59 forks source link

db2+pyodbc reflection fails on default value for column #81

Closed mdobrzanski closed 2 years ago

mdobrzanski commented 4 years ago

I'm getting error

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('ODBC SQL type -99 is not yet supported. column-index=2 type=-99', 'HY106') (Background on this error at: http://sqlalche.me/e/f405)

when making a reflection of Chinook database on DB2 10.5 server due column DEFAULT being of type CLOB and pyodbc does not support it. Is that default value necessary, since it's server side default?

My current walk around is by selecting None for default values when collecting column information https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/ibm_db_sa/reflection.py#L240. Any drawbacks doing such?

Below more detail:

import urllib
from sqlalchemy import create_engine
odbcstr = 'DRIVER=DB2; HOSTNAME=127.0.0.1; DATABASE=Chinook; PROTOCOL=TCPIP; PORT=50000; UID=chinook; PWD=p4ssw0rd'
odbcstr = urllib.quote(odbcstr)
connstr = 'db2+pyodbc:///?odbc_connect={}'.format(odbcstr)
engine = create_engine(connstr)

Base = automap_base()
Base.prepare(engine, reflect=True)

Full traceback 

Traceback (most recent call last):
  File "/home/mike/projects/slashdb/experiments/db2_over_pyodbc.py", line 69, in <module>
    Base.prepare(engine, reflect=True)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/ext/automap.py", line 761, in prepare
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 4159, in reflect
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 469, in __new__
  File "build/bdist.linux-x86_64/egg/sqlalchemy/util/langhelpers.py", line 67, in __exit__
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 464, in __new__
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 552, in _init
  File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/schema.py", line 574, in _autoload
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1596, in run_callable
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 415, in reflecttable
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py", line 626, in reflecttable
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py", line 370, in get_columns
  File "/home/mike/envs/slashdb/local/lib/python2.7/site-packages/ibm_db_sa/base.py", line 734, in get_columns
    connection, table_name, schema=schema, **kw)
  File "<string>", line 2, in get_columns
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/reflection.py", line 55, in cache
  File "/home/mike/envs/slashdb/local/lib/python2.7/site-packages/ibm_db_sa/reflection.py", line 250, in get_columns
    for r in connection.execute(query):
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/result.py", line 946, in __iter__
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/result.py", line 1276, in fetchone
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1458, in _handle_dbapi_exception
  File "build/bdist.linux-x86_64/egg/sqlalchemy/util/compat.py", line 296, in raise_from_cause
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/result.py", line 1268, in fetchone
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/result.py", line 1148, in _fetchone_impl
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('ODBC SQL type -99 is not yet supported.  column-index=2  type=-99', 'HY106') (Background on this error at: http://sqlalche.me/e/f405)

The error is raised when executing sql for column information of tables in this reflection code https://github.com/ibmdb/python-ibmdbsa/blob/master/ibm_db_sa/ibm_db_sa/reflection.py#L240

The executed sql is like

SELECT 
  "SYSCAT"."COLUMNS"."COLNAME", 
  "SYSCAT"."COLUMNS"."TYPENAME", 
  "SYSCAT"."COLUMNS"."DEFAULT", 
  "SYSCAT"."COLUMNS"."NULLS", 
  "SYSCAT"."COLUMNS"."LENGTH", 
  "SYSCAT"."COLUMNS"."SCALE", 
  "SYSCAT"."COLUMNS"."IDENTITY", 
  "SYSCAT"."COLUMNS"."GENERATED" 
FROM 
  "SYSCAT"."COLUMNS" 
WHERE "SYSCAT"."COLUMNS"."TABSCHEMA" = 'CHINOOK' 
      AND "SYSCAT"."COLUMNS"."TABNAME" = 'Album' 
ORDER BY "SYSCAT"."COLUMNS"."COLNO"

According to below the column "SYSCAT"."COLUMNS"."DEFAULT" is CLOB.

SELECT NAME, COLTYPE, TBNAME FROM SYSIBM.SYSCOLUMNS WHERE NAME = 'DEFAULT';
NAME,COLTYPE,TBNAME
DEFAULT,"CLOB    ",SYSCOLUMNS
DEFAULT,"CLOB    ",SYSROUTINEPARMS
DEFAULT,"CLOB    ",SYSATTRIBUTES
DEFAULT,"CLOB    ",SYSVARIABLES
DEFAULT,"CLOB    ",COLUMNS
DEFAULT,"CLOB    ",ROUTINEPARMS
DEFAULT,"CLOB    ",ROWFIELDS
DEFAULT,"CLOB    ",VARIABLES
DEFAULT,"VARCHAR ",ADMINTEMPCOLUMNS

Pyodbc does not support CLOB or BLOB and there we get an error.

imavo commented 4 years ago

I am curious because I cannot recreate this symptom with environment Using this mix:

ibm_db_sa 0.3.5
sqlalchemy 1.3.16 chinook db 1.4 ddl from github Db2 v11.1.4.4a with local databases unixODBC 2.3.1-4.1 ubuntu 16.04.06. python 3.6.8 in virtualenv

and my sample fragment of code is below. There is no exception thrown, not sure why I don't see your symptom.

import urllib
from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base

# authenticating via kerberos, all details  in ~/.odbc.ini and db2dsdriver.cfg
CONNECTION_STRING=("DSN=CHINOOK;")

engine = create_engine('db2+pyodbc:///?odbc_connect={}'.format(CONNECTION_STRING))

engine.connect()

Base = automap_base()
Base.prepare(engine, reflect=True)
mdobrzanski commented 4 years ago

That is interesting, below s a better description of the environment. Still fails.

What is the UnixODBC version that you used?

DB2 v11.5 on docker

docker pull ibmcom/db2
docker run -itd --name mydb2 --privileged=true -p 50000:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=p4ssw0rd -e DBNAME=testdb -e SAMPLEDB=true  ibmcom/db2

Python 3.6.8 with

pyodbc==4.0.30
ibm-db==3.0.1
ibm-db-sa==0.3.5
SQLAlchemy==1.3.16

UnixODBC 2.3.7 /etc/odbcinst.ini

[DB2]
Driver = /home/mike/envs/mypy3/lib/python3.6/site-packages/clidriver/lib/libdb2.so
FileUsage = 1
DontDLClose = 1

Linux uname -a

Linux pure-power 4.15.0-99-generic #100-Ubuntu SMP Wed Apr 22 20:32:56 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

Python script

from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base
connstr = 'db2+pyodbc://db2inst1:p4ssw0rd@127.0.0.1:50000/SAMPLE?driver=DB2'
engine = create_engine(connstr)

Base = automap_base()
Base.prepare(engine, reflect=True)

and the same error

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('ODBC SQL type -99 is not yet supported.  column-index=2  type=-99', 'HY106')
(Background on this error at: http://sqlalche.me/e/f405)
imavo commented 4 years ago

OK found that my database had oracle-compatibility ENABLED. This allows it to work. By this i mean that the Db2-instance had registry setting DB2_COMPATIBILITY_VECTOR=ORA defined and effective before creating the chinook database.

When I connect to a database that has oracle-compatibility DISABLED (which is the default), then I can recreate your symptom exactly.

amukherjee28 commented 3 years ago

Hi @mdobrzanski

Can this issue be closed, or do you still have more queries related to this.

Thanks

mdobrzanski commented 3 years ago

@amukherjee28 that is a way to patch it, but shouldn't reflect work even if oracle compatibility is disabled?

amukherjee28 commented 2 years ago

@mdobrzanski This is a database setting you are talking about and I would like not to patch it in the existing adapter code. This is something best left untouched from the adaptor code and can be controlled from the database end whenever needed.

The Db2 setting vary from user to user based on everyone requirement and hence modifying these would be in the best of interest.

mdobrzanski commented 2 years ago

@amukherjee28 it looks like some changes fd86d6a have been made. From the code it looks like the default is set to None for pyodbc. I wanted to verify how it works now. I pulled the latest from master branch and tried the example again but I'm getting error regarding creating connection 

  File "/home/mike/envs/sandbox/lib/python3.6/site-packages/ibm_db_sa/base.py", line 725, in initialize
    elif "DB2/" in self.dbms_name:
TypeError: argument of type 'NoneType' is not iterable

Is there something wrong with the connection string db2+pyodbc://db2inst1:p4ssw0rd@127.0.0.1:50000/SAMPLE?driver=SandboxDB2 ?

amukherjee28 commented 2 years ago

@mdobrzanski Thanks for pointing this out.

The code change got removed because of the PR merge via commit https://github.com/ibmdb/python-ibmdbsa/commit/1e797163baab7c024df9d34e932b0da76ddb3832

I have restored the code and now this should work fine.

Thanks

mdobrzanski commented 2 years ago

@amukherjee28 error is gone. Run the script using ibm-db-sa latest code from master branch.

/etc/odbcinst.ini

[SandboxDB2]
Driver = /home/mike/envs/sandbox/lib/python3.6/site-packages/clidriver/lib/libdb2.so
FileUsage = 1
DontDLClose = 1

docker with db2 with sample database

docker run -itd --name mydb2 --privileged=true -p 50000:50000 -e LICENSE=accept -e DB2INST1_PASSWORD=p4ssw0rd -e DBNAME=testdb -e SAMPLEDB=true  ibmcom/db2

for switching on/off oracle compatibility in db2 in docker container

su db2inst1
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2set DB2_COMPATIBILITY_VECTOR=
db2stop
db2start

sandbox.py

from sqlalchemy import create_engine
from sqlalchemy.ext.automap import automap_base

# connstr = 'db2://db2inst1:p4ssw0rd@127.0.0.1:50000/SAMPLE'
connstr = 'db2+pyodbc://db2inst1:p4ssw0rd@127.0.0.1:50000/SAMPLE?driver=SandboxDB2'
engine = create_engine(connstr)

Base = automap_base()
Base.prepare(engine, reflect=True)
print(list(Base.classes))