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

boolean datatype returned as nulltype #82

Closed singhshraddha closed 4 years ago

singhshraddha commented 4 years ago

Using version ibm-db | 3.0.1 ibm-db-sa | 0.3.3

I have a Db2 table with a BOOLEAN column Boolean column in table ddl

CREATE TABLE "SCHEMA."TABLE"  (
          ...
          "VALUE_B" BOOLEAN , 
          ...

I get the following warning every time I read the table:

python3.7/site-packages/ibm_db_sa/reflection.py:255: SAWarning: Did not recognize type 'BOOLEAN' of column 'VALUE_B'

and the column gets assigned sqlalchemy.types.NULLTYPE by default in bm_db_sa/reflection/DB2Reflector/get_columns

Is it possible to add support for BOOLEAN columns so that they are not returned as nulltype?

amukherjee28 commented 4 years ago

Hi @singhshraddha

I was trying to reproduce the problem you were facing. Could you please help me with a few more details.

  1. The OS details where your application is running (MAC/Linux/Windows) It will be good to try out in the same OS.

Thanks.

amukherjee28 commented 4 years ago

Hi @singhshraddha

As per my understanding it looks like that the Boolean support issue has been fixed in the new version of ibm_db. Could you try and update the version of ibm_db from 3.0.1 to 3.0.2 and try.

Thanks.

singhshraddha commented 4 years ago

Hi @singhshraddha

I was trying to reproduce the problem you were facing. Could you please help me with a few more details.

1. The OS details where your application is running (MAC/Linux/Windows)
   It will be good to try out in the same OS.

Thanks.

Hi @amukherjee28

my production environment is RHEL 8 and I haven't solved the issue there my dev environment is macOS Catalina and I successfully bypassed this issue using several ways (casting, adding boolean in ischema_names)

I will try upgrading the ibm-db version as you suggested

singhshraddha commented 4 years ago

After upgrading ibm-db==3.0.2 and ibm_db_sa==0.3.3, it doesn't work on RHEl 8, but works on MacOS Catalina

Still have the same issue in RHEL8 - when a boolean column with nullable=True is read in, it reads in all values as None

amukherjee28 commented 4 years ago

Thanks @singhshraddha for the update. Will have a look at RHEL 8 from our end and update.

Thanks.

amukherjee28 commented 4 years ago

@singhshraddha

I investigated on RHEL8 (AMD64) and here is what my observations are :

WITH THE BELOW CONFIGURATION

ibm-db (3.0.1) ibm-db-sa (0.3.3)

CREATE TABLE "boolTab1" ( "ID" INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1),  "Name" VARCHAR(30),  "Home Student" BOOLEAN,  PRIMARY KEY ("ID"),  CHECK ("Home Student" IN (0, 1)) )

2020-06-25 04:53:57,387 INFO sqlalchemy.engine.base.Engine SELECT "boolTab1"."ID", "boolTab1"."Name", "boolTab1"."Home Student"  FROM "boolTab1" 2020-06-25 04:53:57,387 INFO sqlalchemy.engine.base.Engine () (1, 'Arnab', None) (2, 'Bibek', None) (3, 'Pathak', None)

AFTER UPGRADING IBM-DB to 3.0.2

ibm-db (3.0.2) ibm-db-sa (0.3.3)

2020-06-25 04:56:41,979 INFO sqlalchemy.engine.base.Engine SELECT "boolTab1"."ID", "boolTab1"."Name", "boolTab1"."Home Student"  FROM "boolTab1" 2020-06-25 04:56:41,979 INFO sqlalchemy.engine.base.Engine () (1, 'Arnab', True) (2, 'Bibek', False) (3, 'Pathak', False)

Boolean datatype seems to return proper results.

If You continue to see the wrong results then I would require a few more details form your end.

  1. Are you using the default clidriver that comes with ibm_db or are you using your own version of clidriver. If so can you please specify the version of the clidriver used. (using db2level command)
  2. Can you please provide us with the trace file. You can have the trace generated using the below steps : (in the RHEL8 Machine)

a. set the LD_LIBRARY_PATH environment variable to the clidriver/lib path b. set the PATH environment variable to clidriver/bin and clidriver/adm path c. once the above path is set run : db2trc on -f trc.dmp d. Run your application e. Next run : db2trc off f. then generate the cli trace file using : db2trc fmt -cli trc.dmp trc.cli g. db2trc fmt trc.dmp trc.fmt h. db2trc flw trc.dmp trc.flw

Once you have the above trace file can you pass the trc.cli trc.fmt and trc.flw file so that we can have a deeper look.

Thanks.

SabaKauser commented 4 years ago

Also confirm if server level is same. As well, make sure when you reproduce the error, its using 3.0.2 package and not 3.0.1 package. This issue in fixed in 3.0.2 in ibm_db driver. With older versions of Db2 server(database) i.e v11.1 ones, you will see 0/1s and with newer versions(v11.5 database server) you sill see True/False if inserted as True/False.

singhshraddha commented 4 years ago

@SabaKauser @amukherjee28 Thank you for your analysis. I am running Db2 on Cloud, and this is working as intended now. I am unsure as to what fixed it

amukherjee28 commented 4 years ago

@singhshraddha thank you for the update. So I assume that the upgrade worked fine for you and now things are working fine. Can you please close the issue in that case.

Thanks