SingleStore数据库（mysql分布式）-ODBC接口源码编译及ODBC接口Demo

一、测试环境

名称	版本
操作系统	Red Hat Enterprise Linux Server release 7.9 (Maipo)
CPU	Intel® Core™ i7-7700HQ CPU @ 2.80GHz
内存	5G
cmake	3.23.1
singlestore-odbc-connector	1.0.8

二、下载地址
（1）百度云盘
包含内容：cmake源码、mysql源码、mysql-odbc源码、SingleStore-odbc源码、SingleStore-odbc驱动

链接：https://pan.baidu.com/s/17j1sJFxG6os3_3qkmlnICw 
提取码：vm12

（2）官网链接

Cmake下载链接

SingleStore Connector/ODBC 1.0.8下载链接

三、安装Cmake

（1）安装依赖

yum install gcc gcc-c++ make automake

（2）编译安装

tar -xvf cmake-3.23.1.tar

cd /opt/ODBC/pkg/cmake-3.23.1

./bootstrap

gmake

gmake install

四、编译SingleStore-ODBC驱动

unzip singlestore-odbc-connector-1.0.8.zip

cd /opt/ODBC/pkg/singlestore-odbc-connector-1.0.8

./build-local.sh

cd build

[root@node0 build]# ll
总用量 3576
-rw-r--r-- 1 root root   26076 4月  27 16:50 CMakeCache.txt
drwxr-xr-x 8 root root    4096 4月  27 16:50 CMakeFiles
-rw-r--r-- 1 root root    3332 4月  27 16:50 cmake_install.cmake
-rw-r--r-- 1 root root    3970 4月  27 16:50 CPackConfig.cmake
-rw-r--r-- 1 root root    4534 4月  27 16:50 CPackSourceConfig.cmake
drwxr-xr-x 6 root root     251 4月  27 16:50 libmariadb
-rwxr-xr-x 1 root root 1778280 4月  27 16:50 libssodbca.so
-rwxr-xr-x 1 root root 1785480 4月  27 16:50 libssodbcw.so
-rw-r--r-- 1 root root   44406 4月  27 16:50 Makefile
[root@node0 build]# 

五、配置ODBC数据源

UnixOdbc的安装包请看之前写的一篇链接postgresql-配置ODBC数据源。
其中odbcinst.ini和odbc.ini配置方式不同

（1）/etc/odbcinst.ini

[root@node0 cmake-3.23.1]# cat /etc/odbcinst.ini
[SingleStore ODBC Driver]
Description = SingleStore ODBC Driver
Driver = /opt/ODBC/pkg/singlestore-odbc-connector-1.0.8/build/libssodbca.so

（2）/etc/odbc.ini

[root@node0 cmake-3.23.1]# cat /etc/odbc.ini 
[SingleStore-server]
Description=SingleStore server
Driver=SingleStore ODBC Driver
SERVER=node0
USER=root
PASSWORD=root
DATABASE=rpdsql
PORT=13306

（3）验证odbc数据源是否配置成功

[root@node0 cmake-3.23.1]# isql SingleStore-server -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> show databases;
+-------------------------------------------+
| Database                                  |
+-------------------------------------------+
| cluster                                   |
| czg                                       |
| information_schema                        |
| rpdsql                                    |
+-------------------------------------------+
SQLRowCount returns 4
4 rows fetched
SQL> 

六、ODBC_Singlestore_demo.c
（后续继续学习完善，尝试添加批量插入代码）

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <locale.h>

#define PRINT_ARR_SIZE 256
#define Column_Name_Len 50
#define bufferSize 1024

struct DataBinding {  
   SQLSMALLINT TargetType;  
   SQLPOINTER TargetValuePtr;  
   SQLINTEGER BufferLength;  
   SQLLEN StrLen_or_Ind;  
}; 

SQLHENV    V_OD_Env;    // Handle ODBC environment
long       V_OD_erg;    // result of functions
SQLHDBC    V_OD_hdbc;                      // Handle connection
SQLHSTMT   V_OD_hstmt;
char       V_OD_stat[10];    // Status SQL
SQLINTEGER      V_OD_err,V_OD_rowanz,V_OD_id;
SQLSMALLINT     V_OD_mlen,V_OD_colanz;
char            V_OD_msg[200],V_OD_buffer[200];

void errorInfo(SQLCHAR * test_sql);
void FreeResource();
void SelectAllData(SQLHANDLE Hstmt, SQLCHAR * test_sql);

void main(){
   // Enable UTF8
   setlocale(LC_ALL, "");

   // Allocate environment handle
   SQLHANDLE Henv;
   SQLAllocHandle(SQL_HANDLE_ENV, NULL, &Henv);

   // Set odbc version
   SQLSetEnvAttr(Henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);

   // Allocate connection handle
   SQLHANDLE Hdbc;
   SQLAllocHandle(SQL_HANDLE_DBC, Henv, &Hdbc);
   // Connect to the data source
   // NO_CACHE option and Forward-Only cursor are enabled by default
   V_OD_erg = SQLDriverConnect(Hdbc,
				NULL,
				(SQLCHAR *)"DSN=SingleStore-server;UID=root;PWD=root;TCP_PORT=13306;DATABASE=czg;LANGUAGE=CHINESE",
				SQL_NTS,
				NULL,
				0,
				NULL,
				SQL_DRIVER_NOPROMPT);
   errorInfo("connect db!");
  
   // Allocate statement handle
   SQLHANDLE Hstmt;
   SQLAllocHandle(SQL_HANDLE_STMT, Hdbc, &Hstmt);

   // Drop table
   SQLCHAR * sql_drop_tab = "drop table if exists big_table";
   V_OD_erg = SQLExecDirect(Hstmt, sql_drop_tab, SQL_NTS);
   errorInfo(sql_drop_tab);
   
   // Create big table
   SQLCHAR * sql_create_tab = "CREATE TABLE big_table(id INT,name varchar(20),key(id) using clustered columnstore)";
   V_OD_erg = SQLExecDirect(Hstmt, sql_create_tab, SQL_NTS);
   errorInfo(sql_create_tab);
   
   // Insert table
   SQLCHAR * sql_insert_tab = "INSERT INTO big_table VALUES(1,'czg')";
   V_OD_erg = SQLExecDirect(Hstmt, sql_insert_tab, SQL_NTS);
   errorInfo(sql_insert_tab);

   SQLCHAR * sql_insert_tab_1 = "INSERT INTO big_table VALUES(2,'zxj')";
   V_OD_erg = SQLExecDirect(Hstmt, sql_insert_tab_1, SQL_NTS);
   errorInfo(sql_insert_tab_1);

   // Select all data from big table
   SQLCHAR * sql_select_tab = "SELECT * FROM big_table";
   SelectAllData(Hstmt, sql_select_tab);
   FreeResource();
}

void SelectAllData(SQLHANDLE Hstmt, SQLCHAR * test_sql)
{	
   V_OD_erg = SQLExecDirect(Hstmt, test_sql, SQL_NTS);
   errorInfo(test_sql);
   
   //column num
   SQLSMALLINT cols;
   SQLNumResultCols(Hstmt, &cols);
   printf("column num : %d\n",cols);
   
   register int column_num = cols;
   //column name
   register int i;
   //char TmpStr[Column_Name_Len];
   char** TmpStr = (char**)malloc(column_num * sizeof(char*));
   SQLSMALLINT bufferLenUsed;
   for(i = 0; i< column_num; i++)
   {
	   TmpStr[i] = (char*)malloc(bufferSize * sizeof(char));
       SQLColAttribute(Hstmt, i+1, SQL_DESC_LABEL, TmpStr[i], bufferSize, &bufferLenUsed, NULL);
       printf("Column Name : %s  %d\n",TmpStr[i],bufferLenUsed);
   }
   
   // Bind column
   //SQLINTEGER id;
   //SQLCHAR name[10];
   //SQLLEN retBuffer[10];
   
   int row = 0;   
   char coldata[PRINT_ARR_SIZE];
   //SQLBindCol(Hstmt, 1, SQL_C_LONG, &id, 0, NULL);
   //SQLBindCol(Hstmt, 2, SQL_C_CHAR, name, sizeof(name), retBuffer);

   // Fetch everything from the resultset
   // Data is not cached on the driver side after calling SQLExecDirect
   // and each row is retrieved during SQLFetch
   while(SQLFetch(Hstmt) != SQL_NO_DATA)
   {
	  row++;
      printf("%d row: ", row);
	  for(i=1; i<=column_num; i++)
      {
          SQLGetData(Hstmt, i, SQL_C_CHAR, coldata, PRINT_ARR_SIZE, NULL);
          printf("%s ", coldata);
      }
	  printf("\n");
   }	
   free(TmpStr);
}

void errorInfo(SQLCHAR * test_sql)
{
   if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))
   {
	  printf("Error desc : %s\n",test_sql);
      printf("Error code : %d  ",V_OD_erg);
      SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, V_OD_stat,&V_OD_err,V_OD_msg,100,&V_OD_mlen);
      printf("%s (%d)\n",V_OD_msg,V_OD_err);
      FreeResource();
      exit(0);
   }
   else
   {
	   printf("successful desc : %s\n",test_sql);
   }	   
}

void FreeResource()
{
      SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
      SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
      SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);	
}

七、运行截图

[root@node0 src]# gcc -g ODBC_Singlestore_demo.c -o Singlestore_conn -L /opt/ODBC/pkg/singlestore-odbc-connector-1.0.8/build -lssodbca -O3
[root@node0 src]# 
[root@node0 src]# 
[root@node0 src]# ./Singlestore_conn 
successful desc : connect db!
successful desc : drop table if exists big_table
successful desc : CREATE TABLE big_table(id INT,name varchar(20),key(id) using clustered columnstore)
successful desc : INSERT INTO big_table VALUES(1,'czg')
successful desc : INSERT INTO big_table VALUES(2,'zxj')
successful desc : SELECT * FROM big_table
column num : 2
Column Name : id  2
Column Name : name  4
1 row: 1 czg 
2 row: 2 zxj