ODBC 드라이버 연결

tbodbc_driver_installer_7_xx.exe -i <driver path>

  C:\Tibero\tibero7\bin>tbodbc_driver_installer_7_64.exe -i 
TiberoODBC Driver is being installed to [ C:\Tibero\tibero7\bin\ ]
<Caution!!> If you changed the driver path, you should remove all Tibero DSNs and 
add them again.

Success to INSTALL Tibero 7 ODBC Driver

DRIVER={Tibero 7 ODBC Driver};SERVER=192.168.17.21;PORT=1111;DB=tibero;
UID=tibero;PWD=tmax;

$ tar -xvzf libiodbc-3.52.16.tar.gz 

$ ls -al libiodbc-3.52.16
합계 884
drwxrwxr-x 14 tibero7 tibero7   4096  5월  2 20:25 . 
drwx------ 22 tibero7 tibero7   4096  6월 29 16:08 ..
-rw-rw-r--  1 tibero7 tibero7    991  2월 17  2021 AUTHORS
-rw-rw-r--  1 tibero7 tibero7    729  5월  2 20:20 COPYING
-rw-rw-r--  1 tibero7 tibero7  81151  5월  2 20:20 ChangeLog
-rw-rw-r--  1 tibero7 tibero7   1481  2월 17  2021 IAFA-PACKAGE
-rw-rw-r--  1 tibero7 tibero7   9236  2월 17  2021 INSTALL
-rw-rw-r--  1 tibero7 tibero7    729  5월  2 20:20 LICENSE
-rw-rw-r--  1 tibero7 tibero7   1588  5월  2 20:20 LICENSE.BSD
-rw-rw-r--  1 tibero7 tibero7  25277  2월 17  2021 LICENSE.LGPL
-rw-rw-r--  1 tibero7 tibero7  11613  5월  2 20:20 Makefile.am
-rw-rw-r--  1 tibero7 tibero7  37645  5월  2 20:20 Makefile.in
-rw-rw-r--  1 tibero7 tibero7  19293  5월  2 20:20 NEWS
-rwxrwxr-x  1 tibero7 tibero7  11118  5월  2 20:20 PORT.OpenLink
-rw-rw-r--  1 tibero7 tibero7  17076  5월  2 20:20 README
-rw-rw-r--  1 tibero7 tibero7  13828  5월  2 20:20 README.md
-rw-rw-r--  1 tibero7 tibero7   3918  5월  2 20:20 README_GIT
-rw-rw-r--  1 tibero7 tibero7   4030  5월  2 20:20 README_GIT.md
-rw-rw-r--  1 tibero7 tibero7   6110  5월  2 20:20 README_MACOSX
-rw-rw-r--  1 tibero7 tibero7   6110  5월  2 20:20 README_MACOSX.md
-rw-rw-r--  1 tibero7 tibero7   3971  5월  2 20:20 acinclude.m4
-rw-rw-r--  1 tibero7 tibero7  44084  5월  2 20:20 aclocal.m4 
drwxrwxr-x  2 tibero7 tibero7   4096  5월  2 20:25 admin
-rwxrwxr-x  1 tibero7 tibero7   6045  5월  2 20:20 autogen.sh 
drwxrwxr-x  2 tibero7 tibero7     67  5월  2 20:25 bin 
-rwxrwxr-x  1 tibero7 tibero7 496554  5월  2 20:20 configure 
-rw-rw-r--  1 tibero7 tibero7  21726  5월  2 20:20 configure.ac
drwxrwxr-x  2 tibero7 tibero7   4096  5월  2 20:25 debian
drwxrwxr-x  4 tibero7 tibero7    186  5월  2 20:25 drvproxy
drwxrwxr-x  2 tibero7 tibero7     94  5월  2 20:25 etc
drwxrwxr-x  2 tibero7 tibero7    289  5월  2 20:25 include
drwxrwxr-x  3 tibero7 tibero7   4096  5월  2 20:25 iodbc
drwxrwxr-x  4 tibero7 tibero7    163  5월  2 20:25 iodbcadm
drwxrwxr-x  2 tibero7 tibero7   4096  5월  2 20:25 iodbcinst
drwxrwxr-x 11 tibero7 tibero7   4096  5월  2 20:25 mac
drwxrwxr-x  2 tibero7 tibero7    139  5월  2 20:25 man
drwxrwxr-x  2 tibero7 tibero7     86  5월  2 20:25 samples

$ mkdir iodbc
$ cd libiodbc-3.52.7/

./configure --prefix=$HOME/iodbc --disable-gui
.
.
.
iODBC Driver Manager 3.52.16 configuration summary 
==================================================

Installation variables
layout                default
prefix                /home/tibero7/iodbc 
exec_prefix           ${prefix}

Installation paths
programs              ${exec_prefix}/bin 
include files         ${prefix}/include 
libraries             ${exec_prefix}/lib 
manual pages          ${datarootdir}/man

Configuration files
odbc.ini              /etc/odbc.ini 
odbcinst.ini          /etc/odbcinst.ini 
default FILEDSN path  /etc/ODBCDataSources

Extensions
ODBC Version          3 
GUI Extensions        false 
ThreadSafe            true 
Install libodbc.so    true

make

make install

$ cd $HOME/iodbc/bin
$ file iodbctest
iodbctest: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked 
(uses shared libs), for GNU/Linux 2.6.32, 
BuildID[sha1]=b292e7409d349220796e2a5e7649f05504b7c8e6, not stripped

# iodbc setting
export IODBC_HOME=$HOME/iodbc
export LD_LIBRARY_PATH=$IODBC_HOME/lib:$LD_LIBRARY_PATH 
export PATH=$IODBC_HOME/bin:$PATH

[ODBC Data Source]
Tibero7     = Tibero 7 ODBC Driver 
[ODBC]
Trace       = 1
TraceFile   = /tmp/odbc.trace 

[<ODBC Data Sources 세부설정>]
Driver      = /home/tibero7/client/lib/libtbodbc.so 
Description = Tibero7 ODBC Datasource
SID         = <tbdsn.tbr 파일에 설정한 alias 정보> 
User        = tibero
Password    = tmax

$ cat .odbc.ini
[ODBC Data Sources]
tibero7 = Tibero7 ODBC driver

[ODBC]
Trace = 1
TraceFile = /home/tibero7/iodbc/tmp/odbc.trace

[Tibero7]
Server = 192.168.17.21
Port = 11111 
Database = tibero
Driver = /home/tibero7/tibero7/client/lib/libtbodbc.so
Description = Tibero7 ODBC Datasource 
SID = tibero
User = tibero
Password = tmax

iodbctest "DSN=<dsn>;UID=<user>;PWD=<pwd>"

$ iodbctest "DSN=Tibero7;UID=tibero;PWD=tmax" 
iODBC Demonstration program
This program shows an interactive SQL processor 
Driver Manager: 03.52.1623.0502
Driver: 07.00.0218 (libtbodbc.so)

SQL>select * from lim_test;

C1
-------------------------------
100

result set 1 returned 1 rows.

include <windows.h>
include <stdio.h>
include <stdlib.h>
include <sql.h>
include <sqlext.h>

/* #include "stdafx.h" */ 
define ROWSET_SIZE 20 
define DIRECT
int main(int argc, char* argv[])

{

   SQLRETURN rc = SQL_SUCCESS;
   SQLUINTEGER len;
   SQLHANDLE henv, hdbc, hstmt;
   SQLCHAR *sql = (SQLCHAR *)"SELECT TO_CHAR(SYSDATE,'YYYYMMDD') FROM DUAL";
   char buf[128]; 

/* Env Handle */

   SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
   SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
   SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

/* Tibero Connect */

ifdef DIRECT 

// IP, PORT

rc = SQLDriverConnect(hdbc, 

   (SQLHWND)NULL,
   //(SQLCHAR *) "DRIVER={Tibero 4 ODBC Driver};\
                  SERVER=192.168.70.185;PORT=9629;UID=tibero;PWD=tmax;",
   (SQLCHAR *) "DRIVER={Tibero 5 ODBC Driver};\
       SERVER=192.168.70.185;PORT=9629;DB=t5;UID=dbtech;PWD=dbtech;", 
   SQL_NTS,
   NULL, 
   0, 
   NULL,
   SQL_DRIVER_NOPROMPT);

else

// Data Source Name 

rc = SQLConnect(hdbc,
 
                   (SQLCHAR *)"t5", SQL_NTS, /* Data Source Name or DB NAME */ 
                   (SQLCHAR *)"dbtech", SQL_NTS, /* User */
                   (SQLCHAR *)"dbtech", SQL_NTS); /* Password */
                   
endif
if (rc != SQL_SUCCESS) {
   fprintfstderr, "Connection failed!!!");
   exit(1); 
}
/* Statements Handle */
    SQLAllocHandle(SQL_HANDLE_TMT, hdbc, &hstmt); 
    printf("Query: %s\n", sql);
/* Execute Query */
    rc = SQLExecDirect(hstm, sql, SQL_NTS); 
    if (rc != SQL_SUCCESS) {
fprintf(stderr, "SQLExecDirect failed!!!"); 
    exit(1);
   }
/* Bind Result */
    SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLCHAR *)buf, 128, (long *)&len); 
    printf("Result: ", buf);
/* Fetch Result */
   while(SQLFetch(hstmt) != SQL_NO_DATA) { 
    printf("%s\n", buf);
   }
/* Release Handle and Close Connection */
SQLFreeStmt(hstmt, SQL_DROP);
SQLDisconnect(hdbc);
SQLFreeConnect(hdbc);
SQLFreeEnv(henv); 
return 0;
}

include <windows.h> 
include <sql.h> 
include <sqlext.h>

include <sqlcli.h>

cc -m64 -O -ITB_HOME/client/include -LTB_HOME/client/lib -c test.c
cc -m64 -O -ITB_HOME/client/include -o test.bin -LTB_HOME/client/lib -ltbcli \
-lclialloc test.o

-rw-r--r--   1 tibero7 tibero7     5040 6월 29 17:03 test.c 
-rw-rw-r--   1 tibero7 tibero7     3352 6월 29 17:03 test.o

include <windows.h>
include <stdlib.h>
include <stdio.h>
include <string.h>
include <sql.h>
include <sqlext.h> 
//#include<sqlcli.h>

int main() 
{
   SQLRETURN rc;
SQLHANDLE henv, hdbc, hstmt;
   SQLCHAR sRegNo[10], sRegName[25];
   SQLINTEGER sqlnts=SQL_NTS;

/* Env Handle */
   SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
   SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
   SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);

// Set Connection Attribute
   //SQLSetConnectAttr(hdbc,5,(void*)SQL_LOGIN_TIMEOUT,0); 

/* Tibero Connect */

rc = SQLConnect(hdbc,

               (SQLCHAR *)"t5", SQL_NTS, /* Data Source Name or DB NAME */ 
               (SQLCHAR *)"dbtech", SQL_NTS, /* User */
               (SQLCHAR *)"dbtech", SQL_NTS); /* Password */

if (rc != SQL_SUCCESS) {
    printf("Connection failed!!! : %d", rc);
        exit(1); 
    }

/* Set AutoCommit ON/OFF */
rc = SQLSetConnectAttr(hdbc,SQL_ATTR_AUTOCOMMIT,(SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0); 

//rc = SQLSetConnectAttr(hdbc,SQL_ATTR_AUTOCOMMIT,(SQLPOINTER)SQL_AUTOCOMMIT_ON, 0);
   if (rc != 0) return NULL; 
   /* Statements Handle */
   SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);

/* Execute Query - INSERT */
rc = SQLExecDirect(hstmt,(SQLCHAR *)"INSERT INTO reg VALUES(100,'Seoul')",SQL_NTS); 
    if (rc != SQL_SUCCESS) {
        printf("Insert Failed!!!"); 
        exit(1);
    }

/* Commit */
// rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT); 

    /* Execute Query - SELECT */
    rc = SQLExecDirect(hstmt,(SQLCHAR*)"SELECT * FROM reg",SQL_NTS); 
    if (rc != SQL_SUCCESS) {

printf(stderr, "Select Failed!!!"); 
    exit(1);
    }

// Fetch
printf("%10s%25s\n","REGNO","REGNAME");
printf("%10s%25s\n","=====","========================"); 
while( SQLFetch(hstmt) != SQL_NO_DATA ) {
  SQLGetData(hstmt,1,SQL_C_CHAR,sRegNo,10,&sqlnts); 
  SQLGetData(hstmt,2,SQL_C_CHAR,sRegName,25,&sqlnts); 
  printf("%10s%25s\n", sRegNo,sRegName);
} // while end
printf("%10s%25s\n","=====","========================"); 
/* Release Handle and Close Connection */
SQLFreeStmt(hstmt, SQL_DROP);
SQLDisconnect(hdbc);
SQLFreeConnect(hdbc);
SQLFreeEnv(henv);
  return 0;
}

// gcc test_clob.c -ITB_HOME/client/include -L$TB_HOME/client/lib -ltbcli 
// a.out DSN USERNAME PASSWORD

include <stdio.h>
include <assert.h>
include "sqlcli.h"
SQLRETURN _rc = SQL_SUCCESS;
SQLHANDLE _henv = SQL_NULL_HANDLE;
SQLHANDLE _hdbc = SQL_NULL_HANDLE;
SQLHANDLE _hstmt = SQL_NULL_HANDLE;
SQLCHAR *DSN = NULL;
SQLCHAR *UID = NULL;
SQLCHAR *PWD = NULL;
int
main(int argc, char **argv)

{
 SQLINTEGER lob_loc = 0;
 SQLLEN ind = 0;
 SQLLEN lob_ind = 0;
 SQLINTEGER byte_len = 0;
 SQLBIGINT lob_len = 0;
 SQLCHAR lob_char[10+1]; // 버퍼크기(10)+널텀(1)
 
 // 읽어들일 글자 수,버퍼크기가 10이므로 5로 설정 
 SQLINTEGER read_char_len = 5;
 SQLINTEGER sqlnts=SQL_NTS;

 int offset = 1; // 여러번 나누어 가져올때의 offset 
 int lob_length;

 rc = SQLAllocEnv(&henv);
 rc = SQLAllocConnect(henv, &_hdbc); 
 DSN = (SQLCHAR *)argv[1];
 UID = (SQLCHAR *)argv[2]; 
 PWD = (SQLCHAR *)argv[3];

 rc = SQLConnect(hdbc, DSN, SQL_NTS, UID, SQL_NTS, PWD, SQL_NTS); 
 rc = SQLAllocStmt(hdbc, &_hstmt);
 rc = SQLExecDirect(hstmt, (SQLCHAR *)"DROP TABLE T", SQL_NTS);
 rc = SQLExecDirect(hstmt, (SQLCHAR *)"CREATE TABLE T(C1 CLOB)", SQL_NTS); 
 rc = SQLExecDirect(hstmt, (SQLCHAR *)"DELETE FROM T", SQL_NTS);
 rc = SQLExecDirect(hstmt, (SQLCHAR *)"INSERT INTO T VALUES (NULL)",
                    SQL_NTS);

_rc = SQLEndTran(SQL_HANDLE_DBC, _hdbc, SQL_COMMIT); 

rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);

/* Case 1 */

rc = SQLExecDirect(hstmt, (SQLCHAR *)"SELECT * FROM T", SQL_NTS); 

rc = SQLFetch(hstmt);

/* must be return SQL_SUCCESS with SQL_NULL_DATA indicator value

- at first invocation */
  rc = SQLGetData(hstmt, 1, SQL_C_CHAR, NULL, 0, &ind);

/* c1컬럼(clob)이 null인지 확인. null이면 indicator가 SQL_NULL_DATA */
  
if (ind == SQL_NULL_DATA) {

   printf("CASE1: lob : null\n"); 
}

/* must be return SQL_NO_DATA at second invocation*/ 

rc = SQLGetData(hstmt, 1, SQL_C_CHAR, NULL, 0, &ind);
assert(_rc == SQL_NO_DATA);
rc = SQLCloseCursor(hstmt);
rc = SQLFreeStmt(hstmt, SQL_DROP); 
/* Case 2 : lob locator사용 */ 
ind = 0;
rc = SQLAllocStmt(hdbc, &_hstmt);
rc = SQLExecDirect(hstmt, (SQLCHAR *)"DELETE FROM T", SQL_NTS); 
rc = SQLExecDirect(hstmt, (SQLCHAR *)"INSERT INTO T \
VALUES ('티맥스소프트abc티베로입니다.')", SQL_NTS);
rc = SQLExecDirect(hstmt, (SQLCHAR *)"SELECT * FROM T", SQL_NTS); 
rc = SQLBindCol(hstmt, 1, SQL_C_CLOB_LOCATOR, \
                          (SQLPOINTER)&lob_loc, 0, &ind);
rc = SQLFetch(hstmt); // c1이 null이 아니면 lob_loc이 여기서 채워짐 
/* 해당 lob 데이터가 NULL인지 확인 */
if (ind == SQL_NULL_DATA) {
  printf("CASE2 : lob : null\n");
} 
else {

   rc = SQLLobGetLength(hstmt, lob_loc, &lob_len, NULL); 
   printf("CASE2(lob_len) : lob len : %lld\n", lob_len);
/*

SQLRETURN SQL_API

SQLLobGetData2(SQLHSTMT StatementHandle,

       SQLINTEGER Locator,

// 1부터 시작, 문자 수 의미의 offset, 여러번 호출 시 값 변경 필요
        SQLBIGINT Offset,
        SQLINTEGER *ReadLength, // 읽어들일 문자 수
        SQLSMALLINT TargetCType,
        SQLPOINTER DataPtr,
        SQLINTEGER BufferLength, // 버퍼크기
        SQLLEN *Indicator)
*/

while (1) {
      if( read_char_len > lob_len )
              read_char_len = lob_len;
              
      rc = SQLLobGetData2(hstmt, lob_loc, offset, &read_char_len \ 
      , SQL_C_CHAR,lob_char, sizeof(lob_char),&lob_ind);
      if (!SQL_SUCCEEDED(_rc) || lob_ind <= 0)
          break;
      offset += read_char_len; 
      lob_len -= read_char_len;
      printf("length of writing buffer : %lld\n", lob_ind); 
      printf("lob data : %s\n", lob_char);
   }// while end 
 }

 //rc = SQLExecDirect(hstmt, (SQLCHAR *)"DROP TABLE T", SQL_NTS); 
 rc = SQLDisconnect(hdbc);
 rc = SQLFreeConnect(hdbc); 
 rc = SQLFreeEnv(henv); 
 return 0;
}