Postgresql ODBC 사용법

postgrsql odbc Programm 관련 사용법을 기록 한다.

 

1. unix odbc, iodbc

odbc로 접속을 하기 위하여 unix odbc 설치 하여야 한다.

 

1-1) yum을 통한 unix odbc 설치

• yum install postgresql13-odbc.x86_64 -y
• yum install unixODBC-devel -y

: unixODBC-devel -y 설치 하지 않으면 (sql.h, sqlext.h, sqltypes.h, sqlucode.h)가  없어 odbc
  compile을 할 수 없다. 

 

1-2) file download 설치

• link : https://www.postgresql.org/ftp/odbc/versions/msi/ download

postgrsql download site에서 odbc 관련 download 하여 설치 한다.

 

 

2. odbc.ini 설정

odbc.ini dns 등록하여 program 에서 해당 환경 정보를 보고 postgresql db에 접속 한다.

[mydb]
Description         = PostgreSQL connection to mydb
Driver              = /usr/pgsql-13/lib/psqlodbc.so
Database            = mydb
Servername          = 10.0.0.16
UserName            = eduup
Password            = eduup
Port                = 5432
Protocol            = 13.8
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ConnSettings        =

 

2-1) 접속 및 테스트

isql 통하여 postgresql에 정상 적으로 접속 되는지 확인한다.

[postgres:/home/postgres] isql mydb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1;
+------------+
| ?column?   |
+------------+
| 1          |
+------------+
SQLRowCount returns 1
1 rows fetched

 

3. odbc program 작성해 본다.

odbc 이용하여 program 작성 후 postgresql db에 접속해 본다.
중요 : postgresql odbc library를 제공 하고 있지 않아 unix odbc library을 사용하여 접속해야 한다.

 

3-1) Compile (compile.sh)

rm -f example_odbc example_odbc.o
gcc -c example_odbc.c -I/user/include/
gcc -o example_odbc example_odbc.o -lodbc

 

3-2) SQL Create

create table employee  ( id int, name char(20), age float, birthday date, create_timestamp timestamp);

 

3-3) example_odbc.c

#include <stdio.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>

SQLHENV  henv=NULL;
SQLHDBC  hdbc=NULL;

int ODBCConnectDB(SQLCHAR* ds, SQLCHAR* user, SQLCHAR* pw)
{
  SQLRETURN  rc;

  hdbc=NULL;
  henv=NULL;

  // Allocate environment handle
  rc=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);  
  if (rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO)
  {
    //  Set the ODBC version environment attribute
    rc=SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

    if (rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO)
    {
      // Allocate connection handle
      rc=SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

      if (rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO)
      {
        // Set login timeout to 5 seconds.
        SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

        // Connect to data source
        rc=SQLConnect(hdbc, ds, SQL_NTS, user, SQL_NTS, pw, SQL_NTS);
    
        if (rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO)
        {
          printf(" connect success !!! \n");
          return 1;
        }

        SQLDisconnect(hdbc);
      }
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }
  }

    //  Set the ODBC version environment attribute
  SQLFreeHandle(SQL_HANDLE_ENV, henv);

  hdbc=NULL;
  henv=NULL;

  return 0;
}


int ODBCDisconnectDB()
{
  if(hdbc)
  {
    SQLDisconnect(hdbc);    
    SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
  }
  if(henv) SQLFreeHandle(SQL_HANDLE_ENV, henv);
  hdbc=NULL;
  henv=NULL;
  return 1;
}

int db_exec_stmt(char* stmt_str)
{
  SQLHSTMT hstmt;
  SQLRETURN  rc;

  if(henv==NULL || hdbc==NULL) return 0;

  /* Allocate statement handle */
  rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  if(!(rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO) ) return 0;
    
  rc = SQLExecDirect(hstmt, stmt_str, SQL_NTS);
  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
  if(rc==SQL_SUCCESS || rc==SQL_SUCCESS_WITH_INFO) return 1;
  if(rc==SQL_NO_DATA) return 1;

  printf("sqlerr: %d\n", rc);
  return 0;
}

int db_fetch()
{
  SQLRETURN r;
  SQLHSTMT hstmt;
  SQLCHAR sql[256];
  SQLLEN n;
  SQLINTEGER id;
  SQLCHAR name[20];
  SQLREAL age;
  SQL_DATE_STRUCT birthday;
  SQL_TIMESTAMP_STRUCT create_timestamp;

  if(hdbc==NULL || henv==NULL) return 0;

  r=SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  if(!(r==SQL_SUCCESS||r==SQL_SUCCESS_WITH_INFO)) return 0;

  strcpy(sql,"select id,name,age,birthday,create_timestamp from employee");
  r=SQLExecDirect(hstmt, sql, SQL_NTS);
  if(r!=SQL_SUCCESS)
  {
    printf("cannot access [%s]\n", sql);
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    return 0;
  }

  while(1)
  {
    r=SQLFetch(hstmt);
    if(r==SQL_SUCCESS||r==SQL_SUCCESS_WITH_INFO)
    {
      r=SQLGetData(hstmt, 1, SQL_C_ULONG, &id, 0, &n);
      r=SQLGetData(hstmt, 2, SQL_C_CHAR, name, 20, &n);
      r=SQLGetData(hstmt, 3, SQL_C_FLOAT, &age, 0, &n);
      r=SQLGetData(hstmt, 4, SQL_C_TYPE_DATE, &birthday, 0, &n);
      r=SQLGetData(hstmt, 5, SQL_C_TYPE_TIMESTAMP,&create_timestamp,0,&n);
      printf("%d, %s\n", id, name);
    }
    else if(SQL_NO_DATA==r) break;
    else
    {
      printf("%s\n", "fail to fetch data");
      break;
    }
  }
  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}

void main()
{
  ODBCConnectDB("mydb", "eduup", "eduup");

  
  db_exec_stmt("insert into employee (id, name,age,birthday,create_timestamp) \
    values (100,\'joy\',20.0,\'2001-1-2\',now())");
  
  db_fetch();
  
  ODBCDisconnectDB();

  printf(" ### end ###  \n");
}

 

3-4) Exuecte

$> ./example_odbc
connect success !!!
0, joy                
100, joy                
100, joy                
### end ###