GBase 8a ODBC

安装ODBC驱动

https://pkgs.org/download/unixODBC-devel

[root@localhost opt]$rpm -ivh unixODBC-2.3.7-1.el8.x86_64.rpm
[root@localhost opt]$rpm -ivh unixODBC-devel-2.3.7-1.el8.x86_64.rpm
[root@localhost opt]# odbcinst -j
unixODBC 2.3.11
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

配置驱动

[root@bogon opt]# tar xfj GBaseODBC-9.5.0.3-centos8-x86_64.tar.bz2
[root@bogon opt]# cd package/
[root@bogon package]# ll
-rwxrwxr-x. 1 gbase gbase 2031504 Mar 10 02:03 gsodbc-installer
-rwxrwxr-x. 1 gbase gbase 4091928 Mar 10 02:03 libgsodbc8-9.5.0.3.so
-rwxrwxr-x. 1 gbase gbase 4091928 Mar 10 02:03 libgsodbc8.so
[root@bogon package]# cp libgsodbc8* /usr/lib64/
[root@bogon ~]# vi /usr/local/etc/odbcinst.ini 
[root@bogon ~]# cat /usr/local/etc/odbcinst.ini 
[GBase ODBC 8.4 Driver]
Driver =/usr/lib64/libgsodbc8.so 
UsageCount = 1
DontDLClose = 1
Threading = 0

注册驱动

[root@bogon package]# ./gsodbc-installer --help
[root@bogon package]# ./gsodbc-installer -d -a -n "GBase ODBC 8.3 Driver" -t "DRIVER=/usr/lib64/libgsodbc8.so;"
Success: Usage count is 1
[root@bogon package]# ./gsodbc-installer -d -l
GBase ODBC 8.3 Driver

创建ODBC数据源

[root@bogon package]# ./gsodbc-installer -s -a -c2 -n "test" -t "DRIVER=GBase ODBC 8.3 Driver;UID=gbase;PWD=gbase20110531; SERVER={192.168.61.106};"
[root@bogon package]# ./gsodbc-installer -s -l -c2 -n "test"
Name:                test
Driver:              /usr/lib64/libgsodbc8.so
Server:              192.168.61.106
Uid:                 gbase
Pwd:                 gbase20110531
Port:                5258
Options:

编译

#  include <stdio.h>


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

#define FAIL 1
#define PRINT_TABLE 1

/*集群信息*/
const char* connStr = "DRIVER=GBase ODBC 8.3 Driver;UID=gbase;PWD=gbase20110531;SERVER={192.168.61.106};DATABASE=test;CONNECTION_BALANCE=1;GCLUSTER_ID=gcluster1;CHECK_INTERVAL=60;";

const char* table_name = "t_tttt";

/*sql文信息*/
const char* sql_ddl				= "create table t_tttt(a int, b varchar(10))";
const char* sql_insert			= "insert into t_tttt values (1, \'aaaa\')";
const char* sql_select			= "select * from t_tttt";
const char* sql_delete			= "delete from t_tttt where a = 1";
const char* sql_update			= "update t_tttt set b=\'update\' where a =1";
const char* sql_drop			= "drop table t_tttt";
const char* sql_create_proce	= "create procedure demo_p(in a varchar(100), out b varchar(100)) \
								   begin \
								   set b = CONCAT('InOutParam ',a,'  works!'); \
								   end;";
const char* sql_drop_proce		= "drop procedure demo_p";
const char* sql_call_proce		= "CALL demo_p(?, @out)";
const char* sql_select_out		= "select @out";


SQLHENV henv;
SQLHDBC hdbc;
const char* usage = "%s s[D(DDL), d(delete), u(update), i(insert), p(proce), P(drop proce), c(call proce)]\n";

void print_select(SQLHSTMT hstmt, char* sql);
void print_table(SQLHSTMT hstmt, char* table_name);
void print_diag(SQLRETURN rc, SQLSMALLINT htype, SQLHANDLE handle,
		       const char *text, const char *file, int line);

#define ok_env(environ, call) \
do { \
  SQLRETURN rc= (call); \
  print_diag(rc, SQL_HANDLE_ENV, (environ), #call, __FILE__, __LINE__); \
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) \
    return FAIL; \
} while (0)

#define ok_con(con, call) \
do { \
  SQLRETURN rc= (call); \
  print_diag(rc, SQL_HANDLE_DBC, (con), #call, __FILE__, __LINE__); \
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) \
    return FAIL; \
} while (0)

#define ok_sql(statement, query) \
do { \
  SQLRETURN rc= SQLExecDirect((statement), (SQLCHAR *)(query), SQL_NTS); \
  print_diag(rc, SQL_HANDLE_STMT, (statement), \
             "SQLExecDirect(" #statement ", \"" query "\", SQL_NTS)", \
             __FILE__, __LINE__); \
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) \
    return FAIL; \
} while (0)

#define ok_stmt(statement, call) \
do { \
  SQLRETURN rc= (call); \
  print_diag(rc, SQL_HANDLE_STMT, (statement), #call, __FILE__, __LINE__); \
  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) \
    return FAIL; \
} while (0)


void demo_select()
{
	SQLHSTMT hstmt;
	printf("执行查询sql:\n\t%s\n", sql_select);
	ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
	printf("查询结果:\n");
	print_select(hstmt, sql_select);
	
}

void demo_create()
{
	SQLHSTMT hstmt;
	printf("执行创建sql:\n\t%s\n", sql_ddl);
	ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
	ok_con(hstmt, SQLExecDirect(hstmt, sql_ddl, SQL_NTS));
	print_table(hstmt, table_name);
}
void demo_drop()
{
	SQLHSTMT hstmt;
	printf("执行创建sql:\n\t%s\n", sql_drop);
	ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
	ok_con(hstmt, SQLExecDirect(hstmt, sql_drop, SQL_NTS));
}

void demo_delete()
{
	SQLHSTMT hstmt;
	printf("执行删除sql:\n\t%s\n", sql_delete);
	ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
	ok_con(hstmt, SQLExecDirect(hstmt, sql_delete, SQL_NTS));
	print_table(hstmt, table_name);
}

void demo_update()
{
	SQLHSTMT hstmt;
	printf("执行更新sql:\n\t%s\n", sql_update);
	ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
	ok_con(hstmt, SQLExecDirect(hstmt, sql_update, SQL_NTS));
	print_table(hstmt, table_name);
}

void demo_insert()
{
	SQLHSTMT hstmt;
	printf("执行插入sql:\n\t%s\n", sql_insert);
	ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
	ok_con(hstmt, SQLExecDirect(hstmt, sql_insert, SQL_NTS));
	print_table(hstmt, table_name);
}

void demo_create_proce()
{
	SQLHSTMT hstmt;
	printf("创建存储过程:\n\t%s\n", sql_create_proce);
	ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
	ok_con(hstmt, SQLExecDirect(hstmt, sql_create_proce, SQL_NTS));
	printf("创建成功\n");
}

void demo_drop_proce()
{
	SQLHSTMT hstmt;
	printf("删除存储过程:\n\t%s\n", sql_drop_proce);
	ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
	ok_con(hstmt, SQLExecDirect(hstmt, sql_drop_proce, SQL_NTS));
	printf("删除成功\n");	
}

void demo_call_proce()
{
	SQLHSTMT hstmt;
	char* str1 = "input";
	SQLINTEGER str1_cb = SQL_NTS;
	char str2[100] = {'\0'};
	SQLINTEGER str2_cb;
	printf("执行存储过程:\n\t%s\n", sql_call_proce);
	ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));

	ok_stmt(hstmt, SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,  SQL_C_CHAR, SQL_VARCHAR, 100, 0, str1, 6, &str1_cb));
	ok_stmt(hstmt, SQLExecDirect(hstmt, sql_call_proce, SQL_NTS));
	ok_stmt(hstmt, SQLExecDirect(hstmt, sql_select_out, SQL_NTS));
	ok_stmt(hstmt, SQLFetch(hstmt));
	ok_stmt(hstmt, SQLGetData(hstmt, 1, SQL_C_CHAR, str2, 100, &str2_cb));
	printf("执行结果\n");
	printf("\t%s\n", str2);
}

void demo_ddl()
{
	SQLHSTMT hstmt;
	printf("执行ddl语句:\n\t%s\n", sql_ddl);
	ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
	ok_stmt(hstmt, SQLExecDirect(hstmt, sql_ddl, SQL_NTS));
	printf("执行成功\n");
}

void main(int argc, char** argv)
{
	SQLRETURN rc = 0;
	if(argc < 2)
	{
		printf(usage, argv[0]);
		exit(0);
	}
	ok_env(henv, SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv));
	ok_env(henv, SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0));
	ok_env(henv, SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc));
	ok_con(hdbc, SQLDriverConnect(hdbc, NULL, connStr, SQL_NTS,NULL, 0, NULL, 0));
	switch(argv[1][0])
	{
	case 'C':
		demo_create();
		break;
	case 'D':
		demo_drop();
		break;
	case 's':
		demo_select();
		break;
	case 'd':
		demo_delete();
		break;
	case 'u':
		demo_update();
		break;
	case 'i':
		demo_insert();
		break;
	case 'p':
		demo_create_proce();
		break;
	case 'P':
		demo_drop_proce();
		break;
	case 'c':
		demo_call_proce();
		break;
	default:
		printf(usage, argv[0]);
	}	
	ok_con(hdbc, SQLDisconnect(hdbc));
	ok_con(henv, SQLFreeHandle(SQL_HANDLE_DBC, hdbc));
	ok_env(henv, SQLFreeEnv(henv));

}

void print_select(SQLHSTMT hstmt, char* sql)
{
	int num_cols = 0;
	int i = 0;
	int buff_len = 1024;
	int val_len = 0;
	SQLSMALLINT col_type;
	unsigned char* buff = (unsigned char*)malloc(buff_len);
	SQLExecDirect(hstmt, sql, SQL_NTS);
	ok_stmt(hstmt, SQLNumResultCols(hstmt, &num_cols));
	for(i=1; i<=num_cols; i++)
	{
		ok_stmt(hstmt, SQLDescribeCol(hstmt, i, buff, buff_len, NULL, NULL, NULL, NULL, NULL));
		printf("|%s\t", buff);
	}
	printf("\n");
	for(i=1; i<=num_cols; i++)
	{
		printf("--------");
	}
	printf("\n");
	while(SQL_SUCCESS == SQLFetch(hstmt))
	{
		for(i=1; i<=num_cols; i++)
		{
			col_type = 0;
			memset(buff, 0, buff_len);
			val_len = 0;
			ok_stmt(hstmt, SQLGetData(hstmt, i, SQL_C_CHAR, buff, buff_len, &val_len));
			printf("|%s\t", buff);
		}
		printf("\n");
	}
}

void print_table(SQLHSTMT hstmt, char* table_name)
{
	char sql_select[500] = "select * from ";
	int i=0;

	if(0 == PRINT_TABLE)
	{
		return;
	}
	printf("打印表中数据\n");
	
	while(table_name[i]){sql_select[14+i] = table_name[i]; i++;}
	sql_select[14+i] = '\0';
	print_select(hstmt, sql_select);
}

void print_diag(SQLRETURN rc, SQLSMALLINT htype, SQLHANDLE handle,
		       const char *text, const char *file, int line)
{
  if (rc != SQL_SUCCESS)
  {
    SQLCHAR     sqlstate[6], message[SQL_MAX_MESSAGE_LENGTH];
    SQLINTEGER  native_error;
    SQLSMALLINT length;
    SQLRETURN   drc;

    /** @todo map rc to SQL_SUCCESS_WITH_INFO, etc */
    printf("# %s = %d\n", text, rc);

    /** @todo Handle multiple diagnostic records. */
    drc= SQLGetDiagRec(htype, handle, 1, sqlstate, &native_error,
                       message, SQL_MAX_MESSAGE_LENGTH - 1, &length);

    if (SQL_SUCCEEDED(drc))
      printf("# [%6s] %*s in %s on line %d\n",
             sqlstate, length, message, file, line);
    else
      printf("# Did not get expected diagnostics from SQLGetDiagRec() = %d"
             " in file %s on line %d\n", drc, file, line);
  }
}

[root@localhost opt]# gcc -g gbase_test_odbc.c -o gbase_test_odbc -l gsodbc8

调用ODBC

[root@localhost opt]# ./gbase_test_odbc C
执行创建sql:
	create table t_tttt(a int, b varchar(10))
打印表中数据
|a	|b	
----------------
[root@localhost opt]# ./gbase_test_odbc s
执行查询sql:
	select * from t_tttt
查询结果:
|a	|b	
----------------
[root@localhost opt]# ./gbase_test_odbc i
执行插入sql:
	insert into t_tttt values (1, 'aaaa')
打印表中数据
|a	|b	
----------------
|1	
[root@localhost opt]# ./gbase_test_odbc u
执行更新sql:
	update t_tttt set b='update' where a =1
打印表中数据
|a	|b	
----------------
|1	
[root@localhost opt]# ./gbase_test_odbc d
执行删除sql:
	delete from t_tttt where a = 1
打印表中数据
|a	|b	
----------------
[root@localhost opt]# ./gbase_test_odbc D
执行创建sql:
	drop table t_tttt