pyodbc-ifx-knight 3
DB API Module for ODBC with Ifx utf8 chs Error Msg Fix
License: MIT

#### RELEASE NOTE
+ 2021.7.12  BIGSERIAL,JSON column supported
    + read bigserial type as long int type
    + read json type as char from informix (restrict by db) and convert to dict by output converter but can only input as char from json.dumps (restrict by db)

#### INSTALL

pip install pyodbc-ifx

####How To Work
____
environment:
```
export INFORMIXDIR=/opt/INFORMIX/
export INFORMIXCLIENTDIR=/opt/INFORMIX/
export INFORMIXSQLHOSTS=$INFORMIXDIR/etc/sqlhosts.ol_dbt18
export INFORMIXSERVER=ol_dbt18
export DB_LOCALE=zh_cn.utf8
export CLIENT_LOCALE=zh_cn.utf8
export PSORT_DBTEMP=/tmp
export ODBCINI=/etc/odbc.ini
export GL_USEGLU=1
export IFX_DISABLE_IPV6=1
export ONCONFIG=onconfig.$INFORMIXSERVER
export LD_LIBRARY_PATH=/usr/local/openssl/lib:${INFORMIXDIR}/lib:${INFORMIXCLIENTDIR}/lib:${INFORMIXCLIENTDIR}/lib/esql:${INFORMIXCLIENTDIR}/lib/cli:${INFORMIXCLIENTDIR}/lib/client:${INFORMIXCLIENTDIR}/lib/c++:${INFORMIXCLIENTDIR}:${INFORMIXCLIENTDIR}/lib/dmi
export PATH=$INFORMIXDIR/bin:$INFORMIXCLIENTDIR/bin:$INFORMIXDIR/extend/krakatoa/jre/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/python3/bin:$PATH
export PYTHONPATH=$PYTHONPATH:/usr/local/python3
```
odbc.ini
```
;---------------------------------------------------------------------------
; IBM INFORMIX ODBC Sample File
;
; File:         odbc.ini
;
;---------------------------------------------------------------------------
[ODBC Data Sources]
Infdrv1=jsvftestdbs
;
; Define ODBC Database Driver's Below - Driver Configuration Section
;
[jsvftestdbs]
Driver=/opt/INFORMIX/lib/cli/iclit09b.so
Description=Informix Server ODBC DRIVER
Database=testdb
UID=
PWD=
Servername=ol_ids121018
ISOLATIONLEVEL=1
CLIENT_LOCALE=zh_cn.utf8
DB_LOCALE=zh_cn.utf8
USESERVERDBLOCALE=1
LENGTHINCHARFORDIAGRECW=1
LEAVE_TRAILING_SPACES=0
REPORTCHARCOLASWIDECHARCOL=1
FETCHBUFFERSIZE=1024
TRANSLATIONDLL=/opt/INFORMIX/lib/esql/igo4a304.so

;
; UNICODE connection Section
;
[ODBC]
;uncomment the below line for UNICODE connection
;UNICODE=UCS-4
UNICODE=UCS-2

;
; Trace file Section
;
Trace=1
TraceFile=/tmp/odbctrace.out
TraceAutoStop=0
InstallDir=/opt/INFORMIX
TRACEDLL=idmrs09a.so
```
Demo Script:
```
# -*- coding: utf-8 -*-
# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.
import io
import os
import sys
import json
import datetime

coding = 'utf-8'

def string_to_json(unicode_string):
    return json.loads(unicode_string)

def test():
    import sys
    import pyodbc_ifx as pyodbc
    conn = pyodbc.connect("DSN=jsvftestdbs")
    conn.setdecoding(pyodbc.SQL_CHAR,encoding=coding)
    conn.setdecoding(pyodbc.SQL_WCHAR, encoding=coding)
    conn.setdecoding(pyodbc.SQL_WMETADATA, encoding=coding)
    conn.setencoding(encoding=coding)
    conn.add_output_converter(pyodbc.SQL_JSON, string_to_json)
    id = 1
    name = '张三三三'
    sex =  json.dumps({1:1,'测试':'结果','数组': [1,2,3]});
    content =  pyodbc.Binary("adfadsf",coding)
    dt = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f")
    with open('/tmp/core.img', 'rb') as f:
        bindata = f.read()
    pic =  pyodbc.Binary(bindata)
    mycursor = conn.cursor()
    mycursor.execute("drop table if exists tpyodbc")
    mycursor.execute("create table tpyodbc(id serial, name varchar(60), sex json,dt datetime year to fraction(5) ,content text,pic byte)")
    mycursor.execute("insert into tpyodbc(id,name,sex,dt,content,pic) values(?,?,?,?,?,?)", id,name,sex,dt, content ,pic)
    conn.commit()
    cursor1 = conn.cursor()
    cursor1.execute('select id,name,sex,dt,content,pic from tpyodbc')
    rows = cursor1.fetchall()
    for i, (id, name, sex, dt, content, pic) in enumerate(rows):
        print(type(id),type(name),type(sex),type(dt),type(content),type(pic))
        print(id,str(name),sex,dt,content,pic.hex())
    conn.close()
    sys.exit(0)

if __name__ == '__main__':
    sys.stdout = io.TextIOWrapper(sys.stdout.detach(), encoding='utf-8')
    test()
```

Sample Output
```
-bash-4.2$ echo "select * from tpyodbc"|dbaccess testdb

id       1
name     张三三三
sex      {"1": 1, "\u6d4b\u8bd5": "\u7ed3\u679c", "\u6570\u7ec4": [1, 2, 3]} 
dt       2021-07-15 10:01:58
content  
adfadsf
pic      <BYTE值>


-bash-4.2$ python3.9 main.py 
<class 'int'> <class 'str'> <class 'dict'> <class 'datetime.datetime'> <class 'str'> <class 'bytes'>
1 张三三三 {'1': 1, 'user': 'test', 'num': [1, 2, 3]} 2021-07-13 09:38:08.160300 adfadsf 52bff481668b2d837d08000f84e200807cff007446668b1d668b4d046631c0b07f3945087f038b45082945086601056683550400c704100089440266895c0866894c0cc74406007050c744040000b442cd130f82af00bb0070eb66668b45046609c00f859700668b056631d266f73488540a6631d266f7740488540b89440c3b44087d798b042a440a3945087f038b450829450866010566835504008a540dc0e2068a4c0afec108d18a6c0c5a528a740b50bb00708ec331dbb402cd1372468cc38e450a58c1e00501450a601ec1e00389c131ff31f68edbfcf3a51fbe1b81e8570061837d08000f8524ff83ef0ce916ffbe1d81e842005aea00820000be2081e83600eb06be2581e82e00be2a81e82800ebfe6c6f6164696e67002e000d0a0047656f6d005265616400204572726f7200bb0100b40ecd10468a043c0075f2c3000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
```