unixODBC C/C++ Programming

 What is ODBC(Open Database Connectivity)?

In computing, Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems.[citation needed] An application written using ODBC can be ported to other platforms, both on the client and server side, with few changes to the data access code.

ODBC accomplishes DBMS independence by using an ODBC driver as a translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS. An ODBC driver can be thought of as analogous to a printer driver or other driver, providing a standard set of functions for the application to use, and implementing DBMS-specific functionality. An application that can use ODBC is referred to as "ODBC-compliant". Any ODBC-compliant application can access any DBMS for which a driver is installed. Drivers exist for all major DBMSs, many other data sources like address book systems and Microsoft Excel, and even for text or comma-separated values (CSV) files.

ODBC was originally developed by Microsoft and Simba Technologies during the early 1990s, and became the basis for the Call Level Interface (CLI) standardized by SQL Access Group in the Unix and mainframe field. ODBC retained several features that were removed as part of the CLI effort. Full ODBC was later ported back to those platforms, and became a de facto standard considerably better known than CLI. The CLI remains similar to ODBC, and applications can be ported from one platform to the other with few changes.

-- from wikipedia


ODBC is an API that Microsoft has made available various types of databases using a single API a long time ago. Therefore, developers using the ODBC API have the advantage of being able to control databases such as Oracle, MS SQL, DB2, and MySQL using one source code.

If you look at the ODBC API, you can see that it uses a very Microsoft-like nomenclature.

I will be testing on Modern Linux (64bit OS). The OS I used is CentOS 8 and the database is MySQL.


The ODBC connection will have the structure shown in the following figure.




prerequisite

For unixODBC programming, the unixODBC-devel package must be installed in advance. If you are using Ubuntu, install unixODBC-dev.

yum install -y unixODBC-devel


I won't explain MySQL installation here. Let's assume that MySQL is installed on another server. And we will install the client tools to connect to this server.

Download mysql-connector-odbc-8.0.31-1.el8.x86_64.rpm from the https://dev.mysql.com/downloads/connector/odbc/ page as follows.


After downloading, install it as follows.

sudo rpm -Uvh mysql-connector-odbc-8.0.31-1.el8.x86_64.rpm


odbcinst.ini and odbc.ini

Like the API created by Microsoft, ODBC configuration is performed using the ini files.

If you have installed mqsql odbc connector 8.x, you will see MySQL ODBC 8.0 Unicode Driver entry in the odbcinst.ini file as follows. I will use this driver

[root@localhost ~]# cat /etc/odbcinst.ini
[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[FreeTDS]
Description=Free Sybase & MS SQL Driver
Driver=/usr/lib/libtdsodbc.so
Setup=/usr/lib/libtdsS.so
Driver64=/usr/lib64/libtdsodbc.so
Setup64=/usr/lib64/libtdsS.so
Port=1433

[MariaDB]
Description=ODBC for MariaDB
Driver=/usr/lib/libmaodbc.so
Driver64=/usr/lib64/libmaodbc.so
FileUsage=1

[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib64/libmyodbc8w.so
UsageCount=1

[MySQL ODBC 8.0 ANSI Driver]
Driver=/usr/lib64/libmyodbc8a.so
UsageCount=1


And the odbc.ini file creates the [myodbc] section as follows.  We call this section DSN (Data Source Name), and we will use this DSN value to connect to the database.

[root@localhost ~]# cat /etc/odbc.ini
[myodbc]
Driver=MySQL ODBC 8.0 Unicode Driver
SERVER=192.168.150.128
UID=study_user
PWD=study
DATABASE=study_db
PORT=3306

Modify the SERVER address, DATABASE, UID, and PWD according to your environment. UID and PWD can be omitted.


Connection Test

If your MySQL database is running normally and you have configured it properly, you can check the connection as follows.

[root@localhost ~]# isql -v myodbc
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

The isql program can be used by installing unixODBC. And unixODBC is installed together when unixODBC-devel is installed.


Simple ODBC C/C++example

As you can guess from the odbc.ini file I created, I made the study_db schema in advance for the test and created the professor and student tables.


This is a sample ODBC program that searches the contents of the professor table.

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

using namespace std;

void debug_err(SQLRETURN rc, SQLHSTMT stmt){
    SQLLEN numRecs = 0;
    SQLSMALLINT   i, MsgLen;
    SQLRETURN     rc2; 
    SQLCHAR       SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER    NativeError;  
    
    SQLGetDiagField(SQL_HANDLE_STMT, stmt, 0, SQL_DIAG_NUMBER, &numRecs, 0, 0);
    i = 1;  
    while (i <= numRecs && (rc2 = SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i, SqlState, &NativeError,  
            Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {  
        printf("SQLSTATE[%s] NATIVE ERROR[%d] Msg[%s]\n",SqlState,NativeError,Msg);
        i++;  
    }         
}

int main( int argc, char** argv ) {
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN ret; /* ODBC API return status */
    SQLSMALLINT columns; /* number of columns in result-set */
    int row = 0;
    char sql[1024] = "";
    SQLRETURN rc1;
    string conn("DSN=myodbc");

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

    rc1 = SQLDriverConnect(dbc, NULL, (SQLCHAR*)conn.c_str(), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    if(SQL_SUCCESS != rc1){
        printf("SQLDriverConnect return [%d]\n", rc1);
    }
    /* Allocate a statement handle */
    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    /* execute a query */
    sprintf(sql, "select belong, phone from professor");
    rc1 = SQLExecDirect(stmt, (SQLCHAR*)sql, SQL_NTS);
    if(SQL_SUCCESS != rc1 && SQL_SUCCESS_WITH_INFO != rc1){
        debug_err(rc1, stmt);
        exit(0);
    }
    char belong[16], phone[16];
    SQLBindCol(stmt, 1, SQL_C_CHAR,  belong, 16, NULL);
    SQLBindCol(stmt, 2, SQL_C_CHAR,  phone, 16, NULL);


    for ( int i = 0; ; i++ ) {
        rc1 = SQLFetch( stmt );
        if ( rc1 == SQL_ERROR || rc1 != SQL_SUCCESS_WITH_INFO )
            debug_err(rc1, stmt);
        if ( rc1 == SQL_SUCCESS || rc1 == SQL_SUCCESS_WITH_INFO ) {
            cout << "Record [" << i + 1 << "]  ";
            cout << " belong : " << belong;
            cout << " \tphone : " << phone;
            cout << "\n";
        }
        else
            break;
    }
   
    rc1 = SQLFreeHandle( SQL_HANDLE_STMT, stmt );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_STMT)err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLDisconnect( dbc );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLDisconnect err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLFreeHandle( SQL_HANDLE_DBC, dbc );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_DBC)err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLFreeHandle( SQL_HANDLE_ENV, env );    
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_ENV)err:"<< rc1 << endl;
        exit(0);
    } 
    cout << "basic odbc test end" << endl;    
    return 0;
}

<basic_odbc.cpp>


Let's compile and run it.

[root@localhost odbc]# g++ basic_odbc.cpp -lodbc -o basic_odbc
[root@localhost odbc]# ./basic_odbc
Record [1]   belong : IDE       phone : 01112345678
Record [2]   belong : MSE       phone : 01121342443
Record [3]   belong : ESE       phone : 01123424343
Record [4]   belong : IME       phone : 08200003333
Record [5]   belong : IME       phone : 01134343222
Record [6]   belong : IDE       phone : 01123432432

It works fine.


mysql odbc connector and utf-8 problem

However, I omitted the name among the table columns. Let's add a name column this time. Here is just the modified code.

    /* execute a query */
    sprintf(sql, "select name, belong, phone from professor");
    rc1 = SQLExecDirect(stmt, (SQLCHAR*)sql, SQL_NTS);
    if(SQL_SUCCESS != rc1 && SQL_SUCCESS_WITH_INFO != rc1){
        debug_err(rc1, stmt);
        exit(0);
    }
    char name[36], belong[16], phone[16];
    SQLBindCol(stmt, 1, SQL_C_CHAR,  name, 32, NULL);
    SQLBindCol(stmt, 2, SQL_C_CHAR,  belong, 16, NULL);
    SQLBindCol(stmt, 3, SQL_C_CHAR,  phone, 16, NULL);


    for ( int i = 0; ; i++ ) {
        rc1 = SQLFetch( stmt );
        if ( rc1 == SQL_ERROR )
            debug_err(rc1, stmt);
        if ( rc1 == SQL_SUCCESS || rc1 == SQL_SUCCESS_WITH_INFO ) {
            cout << "Record [" << i + 1 << "]  ";
            cout << " name : " << name;
            cout << " belong : " << belong;
            cout << " \tphone : " << phone;
            cout << "\n";
        }
        else
            break;
    }

<basic_odbc2.cpp>


Let's compile and run it.

[root@localhost odbc]# g++  basic_odbc2.cpp -lodbc -o basic_odbc2
[root@localhost odbc]# ./basic_odbc2
Record [1]   name : ??? belong : IDE    phone : 01112345678
Record [2]   name : ??? belong : MSE    phone : 01121342443
Record [3]   name : ??? belong : ESE    phone : 01123424343
Record [4]   name : ??? belong : IME    phone : 08200003333
Record [5]   name : ??? belong : IME    phone : 01134343222
Record [6]   name : ??? belong : IDE    phone : 01123432432
basic odbc test end

No error occurred, but the value of the name column could not be displayed properly. This means that there is a part to be supplemented in relation to unixODBC's character set handling. In the next, I will look at the mutual conversion between utf-8, a character set commonly used in Linux systems, and SQLCHAR and SQLWCHAR used for character processing in unixODBC.

This problem also occurs when using isql.

[spypiggy@localhost mariadb]$ isql -v myodbc
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from professor;
+-----------+---------------------------------+-------------+-------------+
| _id       | name                            | belong      | phone       |
+-----------+---------------------------------+-------------+-------------+
| 1         | ???                          ...| IDE         | 01112345678 |
| 2         | ???                          ...| MSE         | 01121342443 |
| 3         | ???                          ...| ESE         | 01123424343 |
| 4         | ???                          ...| IME         | 08200003333 |
| 5         | Jordan                          | NBA         | 01023452222 |
| 256       | ???                          ...| IME         | 01134343222 |
| 257       | ???                          ...| IDE         | 01123432432 |
+-----------+---------------------------------+-------------+-------------+
SQLRowCount returns 7
7 rows fetched


mariadb odbc connector and utf-8

However, this phenomenon is also related to the mysql odbc connector. I also installed mariadb odbc conector and tested it. The mariadb odbc connector does not have this problem. It seems to convert to utf-8 characters inside the connector.

[spypiggy@localhost mariadb]$ isql -v mariaodbc
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from professor;
+------------+---------------------------------+-------------+-------------+
| _id        | name                            | belong      | phone       |
+------------+---------------------------------+-------------+-------------+
| 1          | 유재석                       | IDE         | 01112345678 |
| 2          | 황영조                       | MSE         | 01121342443 |
| 3          | 케이멀                       | ESE         | 01123424343 |
| 4          | 김연경                       | IME         | 08200003333 |
| 5          | Jordan                          | NBA         | 01023452222 |
| 256        | 호날두                       | IME         | 01134343222 |
| 257        | 리오넬                       | IDE         | 01123432432 |
+------------+---------------------------------+-------------+-------------+
SQLRowCount returns 7
7 rows fetched


The contents from now on are related to the processing of utf-8 strings that occur when using the mysql odbc connector. If you use the mariadb odbc connector, you will probably be able to process utf-8 strings without any problems even if you do not apply the contents below.


character set

In the English-speaking world, character set may not be a big problem, but in countries that use multi-byte characters such as Korea, Japan, and China, character set is a very important issue.

Since unixODBC is made by Microsoft, the supported character set is tailored to ansi and 2-byte Unicode characters.

However, in Linux systems, utf-8 is mainly used as a character set. Programming languages like Python also use utf-8 as their default character set.

Let's make the above code in Python and run it.

import pyodbc
import sys

server = "192.168.150.128"
user = "study_user"
password = "study"
db = "study_db"

try:
    cnxn = pyodbc.connect('DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=' + server + ';DATABASE='+db+';UID='+user+';PWD=' + password)
except pyodbc.Error as ex:
    sqlstate = ex.args[1]
    sqlstate = sqlstate.split(".")
    print("SQL Connect Error")
    sys.exit(0)
cursor = cnxn.cursor()

sql =  '''select name, belong, phone from professor'''
res =  cursor.execute(sql)
for r in res:
    print(r[0], " ", r[1], " ", r[2])

cnxn.close()

<basic_odbc.py>

Let's run the program. You probably need to install pyodbc with the pip command.

[root@localhost odbc]# python3 myodbc.py
유재석   IDE   01112345678
황영조   MSE   01121342443
케이멀   ESE   01123424343
김연경   IME   08200003333
호날두   IME   01134343222
리오넬   IDE   01123432432

The Python program is displaying Korean characters(유재석, 황영조, 케이멀, 김연경, 호날두, 리오넬) well without any problems. This means that the pyodbc package converts the values of the name column to utf-8 and displays them.

However, the unixODBC c/c++ API does not automatically do this conversion, so the developer needs to do a additional work.

Characters used in unixODBC SDK are as follows.

  • SQLCHAR: It is 1 byte in size and is the same as char.
  • SQLWCHAR: It is 2 byte in size and is the same as char16_t.


Columns stored in utf-8 character set are read as SQLWCHAR. However, since SQLWCHAR (char16_t) is different from the utf-8 character set, you need to convert it once again.


    char16_t  name[36];
    char belong[16], phone[16];
    SQLBindCol(stmt, 1, SQL_C_WCHAR,  name, 32, NULL);
    SQLBindCol(stmt, 2, SQL_C_CHAR,  belong, 16, NULL);
    SQLBindCol(stmt, 3, SQL_C_CHAR,  phone, 16, NULL);

<binding SQLWCHAR>


Method1 - Using wstring_convert 

Now we need to convert the value stored in the char16_t variable (name) to utf-8 which we can easily process. 

This process can be easily done by using wstring_convert.

wstring_convert<std::codecvt_utf8_utf16<char16_t>,char16_t> conversion;

......

string mbs = conversion.to_bytes( name);

If you want to convert utf-8 to char16_t backwards, use the conversion.from_bytes function.

The following code is modified to display Korean properly by adding char16_t and utf-8 conversions.

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

#include <wchar.h>
#include <locale>
#include <codecvt>

using namespace std;
wstring_convert<std::codecvt_utf8_utf16<char16_t>,char16_t> conversion;

void debug_err(SQLRETURN rc, SQLHSTMT stmt){
    SQLLEN numRecs = 0;
    SQLSMALLINT   i, MsgLen;
    SQLRETURN     rc2; 
    SQLCHAR       SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER    NativeError;  
    
    SQLGetDiagField(SQL_HANDLE_STMT, stmt, 0, SQL_DIAG_NUMBER, &numRecs, 0, 0);
    i = 1;  
    while (i <= numRecs && (rc2 = SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i, SqlState, &NativeError,  
            Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {  
        printf("SQLSTATE[%s] NATIVE ERROR[%d] Msg[%s]\n",SqlState,NativeError,Msg);
        i++;  
    }         
}

int main( int argc, char** argv ) {
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN ret; /* ODBC API return status */
    SQLSMALLINT columns; /* number of columns in result-set */
    int row = 0;
    char sql[1024] = "";
    SQLRETURN rc1;
    string conn("DSN=myodbc");

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

    rc1 = SQLDriverConnect(dbc, NULL, (SQLCHAR*)conn.c_str(), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    if(SQL_SUCCESS != rc1){
        printf("SQLDriverConnect return [%d]\n", rc1);
    }
    /* Allocate a statement handle */
    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    /* execute a query */
    sprintf(sql, "select name, belong, phone from professor");
    rc1 = SQLExecDirect(stmt, (SQLCHAR*)sql, SQL_NTS);
    if(SQL_SUCCESS != rc1 && SQL_SUCCESS_WITH_INFO != rc1){
        debug_err(rc1, stmt);
        exit(0);
    }

    char16_t  name[36];
    char belong[16], phone[16];
    SQLBindCol(stmt, 1, SQL_C_WCHAR,  name, 32, NULL);
    SQLBindCol(stmt, 2, SQL_C_CHAR,  belong, 16, NULL);
    SQLBindCol(stmt, 3, SQL_C_CHAR,  phone, 16, NULL);


    for ( int i = 0; ; i++ ) {
        rc1 = SQLFetch( stmt );
        if ( rc1 == SQL_ERROR  )
            debug_err(rc1, stmt);
        if ( rc1 == SQL_SUCCESS || rc1 == SQL_SUCCESS_WITH_INFO ) {
            string mbs = conversion.to_bytes( name);
            cout << "Record [" << i + 1 << "]  ";
            cout << " name : " << mbs.c_str();
            cout << " belong : " << belong;
            cout << " \tphone : " << phone;
            cout << "\n";
        }
        else
            break;
    }
   
    rc1 = SQLFreeHandle( SQL_HANDLE_STMT, stmt );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_STMT)err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLDisconnect( dbc );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLDisconnect err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLFreeHandle( SQL_HANDLE_DBC, dbc );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_DBC)err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLFreeHandle( SQL_HANDLE_ENV, env );    
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_ENV)err:"<< rc1 << endl;
        exit(0);
    } 
    cout << "basic odbcw test end" << endl;    
    return 0;
}

<basic_odbcw.cpp>


Let's compile and run it. Don't forget to add the -std=c++14 compile option.

[root@localhost odbc]# g++ -std=c++14 basic_odbcw.cpp -lodbc -o basic_odbcw
[root@localhost odbc]# ./basic_odbcw
Record [1]   name : 유재석 belong : IDE         phone : 01112345678
Record [2]   name : 황영조 belong : MSE         phone : 01121342443
Record [3]   name : 케이멀 belong : ESE         phone : 01123424343
Record [4]   name : 김연경 belong : IME         phone : 08200003333
Record [5]   name : 호날두 belong : IME         phone : 01134343222
Record [6]   name : 리오넬 belong : IDE         phone : 01123432432

As with the Python program, it can be seen that Hangul (Korean) processing is complete.

Sometimes you need to put utf-8 characters in the SQLExecDirect function rather than SQLFetch like this.


    const char *insert = "INSERT INTO professor (_id, name, belong, phone) VALUES ( '4','김연경','IME', '08200003333')";
    std::u16string uinsert;

    uinsert = conversion.from_bytes( insert);
    SQLRETURN sret = SQLExecDirectW(stmt, (SQLWCHAR*)uinsert.c_str(), SQL_NTS);

You can convert const char * to u16string using conversion.from_bytes.


Method2 - Using std::c16rtomb

Another way is to use the std::c16rtomb function, which has been supported since C++11.

However, this function works for one character, not a char16_t string. Therefore, you have to create your own functions that target strings. But it's not difficult.


int c16str_2_str(char16_t *c16str, char *str, int str_size)
{
    bzero(str, str_size);
    int index = 0;
    mbstate_t st{};
    while(1){
        size_t t = c16rtomb(str, c16str[index], &st);
        if(0x00 == (int)c16str[index]) break;
        if(t == (size_t)-1) break;
        str += t;
        index++;
    }
    return index;
}


To use this function, the locale must be specified in advance using the std::setlocale function. You can check the current system's locale as follows:


[root@localhost odbc]# echo $LANG
en_US.UTF-8


    char *locale = setlocale(LC_ALL, "en_US.UTF-8");
    if(locale) cout <<"locale: "<< locale << endl;
    else cout << "locale set failed" <<endl;


The following is the entire source code that processes utf-8 characters using c16rtomb.


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

#include <wchar.h>
#include <locale>
#include <codecvt>
#include <uchar.h>
using namespace std;


void debug_err(SQLRETURN rc, SQLHSTMT stmt){
    SQLLEN numRecs = 0;
    SQLSMALLINT   i, MsgLen;
    SQLRETURN     rc2; 
    SQLCHAR       SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER    NativeError;  
    
    SQLGetDiagField(SQL_HANDLE_STMT, stmt, 0, SQL_DIAG_NUMBER, &numRecs, 0, 0);
    i = 1;  
    while (i <= numRecs && (rc2 = SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i, SqlState, &NativeError,  
            Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {  
        printf("SQLSTATE[%s] NATIVE ERROR[%d] Msg[%s]\n",SqlState,NativeError,Msg);
        i++;  
    }         
}

int c16str_2_str(char16_t *c16str, char *str, int str_size)
{
    bzero(str, str_size);
    int index = 0;
    mbstate_t st{};
    while(1){
        size_t t = c16rtomb(str, c16str[index], &st);
        if(0x00 == (int)c16str[index]) break;
        if(t == (size_t)-1) break;
        str += t;
        index++;
    }
    return index;
}

int main( int argc, char** argv ) {
    SQLHENV env;
    SQLHDBC dbc;
    SQLHSTMT stmt;
    SQLRETURN ret; /* ODBC API return status */
    SQLSMALLINT columns; /* number of columns in result-set */
    int row = 0;
    char sql[1024] = "";
    SQLRETURN rc1;
    string conn("DSN=myodbc");

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

    rc1 = SQLDriverConnect(dbc, NULL, (SQLCHAR*)conn.c_str(), SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    if(SQL_SUCCESS != rc1){
        printf("SQLDriverConnect return [%d]\n", rc1);
    }
    /* Allocate a statement handle */
    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    /* execute a query */
    sprintf(sql, "select name, belong, phone from professor");
    rc1 = SQLExecDirect(stmt, (SQLCHAR*)sql, SQL_NTS);
    if(SQL_SUCCESS != rc1 && SQL_SUCCESS_WITH_INFO != rc1){
        debug_err(rc1, stmt);
        exit(0);
    }

    char16_t  name[36];
    char belong[16], phone[16];
    SQLBindCol(stmt, 1, SQL_C_WCHAR,  name, 32, NULL);
    SQLBindCol(stmt, 2, SQL_C_CHAR,  belong, 16, NULL);
    SQLBindCol(stmt, 3, SQL_C_CHAR,  phone, 16, NULL);

    char *locale = setlocale(LC_ALL, "en_US.UTF-8");
    if(locale) cout <<"locale: "<< locale << endl;
    else cout << "locale set failed" <<endl;

    for ( int i = 0; ; i++ ) {
        rc1 = SQLFetch( stmt );
        if ( rc1 == SQL_ERROR  )
            debug_err(rc1, stmt);
        if ( rc1 == SQL_SUCCESS || rc1 == SQL_SUCCESS_WITH_INFO ) {
            char szname[36];
            c16str_2_str(name, szname, sizeof(szname));
            cout << "Record [" << i + 1 << "]  ";
            cout << " name : " << szname;
            cout << " belong : " << belong;
            cout << " \tphone : " << phone;
            cout << "\n";
        }
        else
            break;
    }
   
    rc1 = SQLFreeHandle( SQL_HANDLE_STMT, stmt );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_STMT)err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLDisconnect( dbc );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLDisconnect err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLFreeHandle( SQL_HANDLE_DBC, dbc );
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_DBC)err:"<< rc1 << endl;
        exit(0);
    } 
    rc1 = SQLFreeHandle( SQL_HANDLE_ENV, env );    
    if(rc1 != SQL_SUCCESS){
        cout << "SQLFreeHandle (SQL_HANDLE_ENV)err:"<< rc1 << endl;
        exit(0);
    } 
    cout << "basic odbcw test end" << endl;    
    return 0;
}

<basic_odbcw2.cpp>


You can download source codes at C-Python-Cooking .


댓글

댓글 쓰기

이 블로그의 인기 게시물

MQTT - C/C++ Client

이미지
  This article records how to implement the client directly using c/c++ , which is linked to the Mosquito MQTT broker set-up on the Ubuntu 20.04, introduced in MQTTT - Mosquito MQT broker set-up on the Ubuntu 20.04 .   Install Paho MQTT C Client  To use MQTT on c/c++, first download the required module from the eclipse foundation. You can download files for your operating system from https://www.eclipse.org/paho/index.php?page=clients/c/index.php . <Paho MQTT C Client download page> Of course I use Linux, so I'll refer to the build contents for Linux in the middle of the document for "Building from Source." apt-get install openssl libssl-dev  cd / usr / local / src git clone https :// github . com / eclipse / paho . mqtt . c . git cd paho . mqtt . c . git make && sudo make install  Now we are ready to develop mqtt client using c/c++.   Build Example  We will proceed with the example of mqttt as described in MQTT - Mosquito MQTT broke...
자세한 내용 보기

RabbitMQ - C++ Client #1 : Installing C/C++ Libraries

이미지
 This article documents how to connect to RabbitMQ using c/c++ to publish and subscribe to messages. I'm working on Ubuntu 20.04. RabbitMQ supports various development languages. Java, Python, C/C++, C#, Ruby, Javascript, as well as recently released languages such as Go and Rust are supported. How to use various development languages is introduced in detail at https://www.rabbitmq.com/devtools.html . Those who use C/C++ can also get download information for some Client tools on this page. <Development tool introduction page> Install SimpleAmqpClient In the middle of this page there is an introduction to tools for C/C++ developers. C and C++ RabbitMQ C client SimpleAmqpClient , a C++ wrapper around rabbitmq-c amqpcpp , a C++ message library for RabbitMQ AMQP-CPP , a C++ RabbitMQ client I'll use SimpleAmqpClient here. The reason for using SimpleAmqpClient is that the API is very concise. RabbitMQ has the advantage of being easy to port when porting Python code becaus...
자세한 내용 보기

C/C++ - Everything about time, date

 Important data types  time_t Although not defined by the C standard, this is almost always an integral value holding the number of seconds (not counting leap seconds) since 00:00, Jan 1 1970 UTC, corresponding to POSIX time.  Unix and POSIX systems define time_t as an integer (usually a 32-bit or 64-bit integer) or a floating-point type. Note : Time_t can only be handled in seconds, but not in milliseconds or microsec.  struct tm The tm structure has the following structure. struct tm { int tm_sec; int tm_min; int tm_hour; int tm_mday; int tm_mon; int tm_year; int tm_wday; int tm_yday; int tm_isdst; }; time_t is a variable that stores seconds that have elapsed since 1970. It is often used to convert from this variable to the easy-to-use year, month, day, hour, minute, and second formats. struct timeval The time_t discussed above has only a value in seconds. Time_t cannot be used if ms (0.001sec) and us (0.000001sec) value...
자세한 내용 보기