[Tibero] Tibero <=> ODBC 연동 (CentOS 7.9)

 

패키지, python package 설치

#---- pip 설치
yum install python3-pip -y
yum install python3 -y

#---- unixODBC 설치
yum install unixODBC-devel -y

#---- ODBC 패키지의 의존성 패키지
#---- Red Hat: glibc, e2fsprogs, krb5-libs, openssl, unixODBC
#---- [참조링크](<https://learn.microsoft.com/ko-kr/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16&tabs=alpine18-install%2Credhat17-install%2Credhat7-install%2Credhat7-13-install%2Crhel7-offline>)

gcc 버전 확인

$ gcc -v
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-redhat-linux/4.8.5/lto-wrapper
Target: x86_64-redhat-linux
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-bootstrap --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu-unique-object --enable-linker-build-id --with-linker-hash-style=gnu --enable-languages=c,c++,objc,obj-c++,java,fortran,ada,go,lto --enable-plugin --enable-initfini-array --disable-libgcj --with-isl=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/isl-install --with-cloog=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/cloog-install --enable-gnu-indirect-function --with-tune=generic --with-arch_32=x86-64 --build=x86_64-redhat-linux
Thread model: posix
	gcc version 4.8.5 20150623 (Red Hat 4.8.5-44) (GCC)

pyodbc 설치

#---- python 
pip3 install pyodbc==3.0.7

#---- 확인
$ python3
Python 3.6.8 (default, Nov 16 2020, 16:55:22) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux
Type "help", "copyright", "credits" or "license" for more information.
**>>> import pyodbc**
>>> quit()

TIBERO Client 캐릭터 셋 설정

#---- TIBERO의 DB_CHARSET은 UTF8 이다.
#---- TIBERO의 CHARSET과 맞춰주기 위해서
#---- TIBERO CLIENT의 TB_NLS_LANG 설정 필요
vi $TB_HOME/client/config/tbdsn.tbr

	TB_NLS_LANG=UTF8

#---- locale 설정
# locale -a | grep ko
export LANG=ko_KR.utf8

DSN 설정

• /etc/odbc.ini 혹은 $HOME/odbc.ini 에 **** DSN 설정
• sample: /etc/odbcinst.ini

#---- odbc 작성
vi /etc/odbc.ini 

[ODBC Data Sources]
tibero6 = Tibero6 ODBC driver
[ODBC]
Trace = 1
Tracefile = /tmp/odbc.trace

[tibero6]
Driver = /db/tibero6/client/lib/libtbodbc.so
Description = Tibero6 ODBC Datasource
SID = tibero
User = humaninfo
Password = humaninfo

설명

[ODBC Data Sources]
tibero6 = Tibero6 ODBC driver
[ODBC]
Trace = 1                     #--- trace level
Tracefile = /tmp/odbc.trace   #--- trace file 경로

[tibero6]
Driver = /db/tibero6/client/lib/libtbodbc.so #--- library 경로
Description = Tibero6 ODBC Datasource        #--- 
SID = tibero
User = humaninfo
Password = humaninfo

 

환경변수 설정

#---- TIBERO ENV
export TB_HOME=/db/tibero6
export TB_SID=tibero
export PATH=.:$TB_HOME/bin:$TB_HOME/client/bin:$JAVA_HOME/bin:$PATH
export LD_LIBRARY_PATH=$TB_HOME/lib:$TB_HOME/client/lib:$LD_LIBRARY_PATH

DSN 접속 TEST

• isql 명령어는 unixODBC 패키지에 포함된 명령어

isql -v tibero6

SQL> select * from dual;
SQL> select * from sample_view;
SQL> select * from user_objects;
SQL> quit

python 코드 (ODBC를 통해)

#!/bin/python3

import pyodbc

try:
    db = pyodbc.connect('DSN=tibero6')#;UID=humaninfo;PWD=humaninfo')
    sql = 'select * from sample_view;'
    cursor = db.cursor()

    cursor.execute(sql)
    data = cursor.fetchall()

    for x in data:
        print (x)

    cursor.close()
    db.close()

except Exception as ex:
    print(ex)

#---- pyodbc.Error: ('ERRPS', '[ERRPS]  PSM compilation error. (-15146) (SQLExecDirectW)') 에러 발생시
#---- 환경변수 설정해야 함
export TBCLI_WCHAR_TYPE=UCS2

#---- 아래의 경우로 해결 가능한 경우도 있다고 함
#---- 내가 사용한 pyodbc.connect에선 해당 함수가 존재하지 않더라..
#------ [참조링크](<https://somjang.tistory.com/entry/Python-pyodbc%EB%A5%BC-%ED%99%9C%EC%9A%A9%ED%95%98%EC%97%AC-Tibero-%EC%97%B0%EA%B2%B0-%EC%8B%9C-%EB%B0%9C%EC%83%9D%ED%95%98%EB%8A%94-TBR-15146-PSM-compilation-error-%ED%95%B4%EA%B2%B0-%EB%B0%A9%EB%B2%95>)