#ifdef WIN32
# include
#else
# include
#endif
# include
#include
#include
#define FAIL 1
#define PRINT_TABLE 1
/*conn string*/
char* connStr = "DRIVER=GBase 8a ODBC 8.3
Driver;UID=gbase;PWD=gbase20110531;PORT=5258;"
"SERVER={192.168.9.173};"
"DATABASE=test;";
GBase 8a 程序员手册ODBC 篇
南大通用数据技术股份有限公司
- 47 -
char* table_name = "t_tttt";
/*sql*/
char* sql_ddl
= "create table t_tttt(a int, b
varchar(10))";
char* sql_insert
= "insert into t_tttt values (1,
\'aaaa\')";
char* sql_select
= "select * from t_tttt";
char* sql_delete
= "delete from t_tttt where a = 1";
char* sql_update
=
"update
t_tttt
set
b=\'update\'
where
a =1";
char* sql_create_proce = "create procedure demo_p(in a
varchar(100), out b varchar(100)) "
"begin "
"set b = CONCAT('InOutParam
',a,' works!'); "
"end;";
char* sql_drop_proce
= "drop procedure demo_p";
char* sql_call_proce
= "CALL demo_p(?, @out)";
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";
int 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);
GBase 8a 程序员手册ODBC 篇
- 48 -
南大通用数据技术股份有限公司
#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 { \
GBase 8a 程序员手册ODBC 篇
南大通用数据技术股份有限公司
- 49 -
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)
int demo_select()
{
SQLHSTMT hstmt;
printf("exec sql:\n\t%s\n", sql_select);
ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
printf("select result:\n");
print_select(hstmt, sql_select);
return 0;
}
int demo_delete()
{
SQLHSTMT hstmt;
printf("exec sql:\n\t%s\n", sql_delete);
ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
ok_stmt(hstmt, SQLExecDirect(hstmt, sql_delete, SQL_NTS));
print_table(hstmt, table_name);
return 0;
}
int demo_update()
{
SQLHSTMT hstmt;
printf("exec sql:\n\t%s\n", sql_update);
ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
ok_stmt(hstmt, SQLExecDirect(hstmt, sql_update, SQL_NTS));
GBase 8a 程序员手册ODBC 篇
- 50 -
南大通用数据技术股份有限公司
print_table(hstmt, table_name);
return 0;
}
int demo_insert()
{
SQLHSTMT hstmt;
printf("exec sql:\n\t%s\n", sql_insert);
ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
ok_stmt(hstmt, SQLExecDirect(hstmt, sql_insert, SQL_NTS));
print_table(hstmt, table_name);
return 0;
}
int demo_create_proce()
{
SQLHSTMT hstmt;
printf("create proc:\n\t%s\n", sql_create_proce);
ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
ok_stmt(hstmt, SQLExecDirect(hstmt, sql_create_proce,
SQL_NTS));
printf("create proc success.\n");
return 0;
}
int demo_drop_proce()
{
SQLHSTMT hstmt;
printf("drop proc:\n\t%s\n", sql_drop_proce);
ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
ok_stmt(hstmt,
SQLExecDirect(hstmt,
sql_drop_proce,
SQL_NTS));
printf("drop proc success\n");
return 0;
}
GBase 8a 程序员手册ODBC 篇
南大通用数据技术股份有限公司
- 51 -
int demo_call_proce()
{
SQLHSTMT hstmt;
char* str1 = "input";
SQLINTEGER str1_cb = SQL_NTS;
char str2[100] = {'\0'};
SQLINTEGER str2_cb;
printf("exec proc:\n\t%s\n", sql_call_proce);
ok_con(hdbc, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));
ok_stmt(hstmt, SQLPrepare(hstmt, sql_call_proce, SQL_NTS));
ok_stmt(hstmt, SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
SQL_C_CHAR, SQL_VARCHAR, 100, 0, str1, 6, NULL));
ok_stmt(hstmt, SQLExecute(hstmt));
printf("proc result:\n");
print_select(hstmt, sql_select_out);
return 0;
}
int demo_ddl()
{
SQLHSTMT hstmt;
printf("exec:\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("exec success.\n");
return 0;
}
int main(int argc, char** argv)
{
SQLRETURN rc = 0;
if(argc < 2)
{
printf(usage, argv[0]);
GBase 8a 程序员手册ODBC 篇
- 52 -
南大通用数据技术股份有限公司
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 'D':
demo_ddl();
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]);
GBase 8a 程序员手册ODBC 篇
南大通用数据技术股份有限公司
- 53 -
}
ok_con(hdbc, SQLDisconnect(hdbc));
ok_con(henv, SQLFreeHandle(SQL_HANDLE_DBC, hdbc));
ok_env(henv, SQLFreeEnv(henv));
return 0;
}
int print_select(SQLHSTMT hstmt, char* sql)
{
int num_cols = 0;
int i = 0;
int buff_len = 1024;
int val_len = 0;
SQLSMALLINT col_type, tnum = 0;
unsigned char* buff = (unsigned char*)malloc(buff_len);
SQLExecDirect(hstmt, sql, SQL_NTS);
ok_stmt(hstmt, SQLNumResultCols(hstmt, &tnum));
num_cols = tnum;
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);
GBase 8a 程序员手册ODBC 篇
- 54 -
南大通用数据技术股份有限公司
val_len = 0;
ok_stmt(hstmt, SQLGetData(hstmt, i, SQL_C_CHAR, buff,
buff_len, &val_len));
printf("|%s\t", buff);
}
printf("\n");
}
free(buff);
return 0;
}
void print_table(SQLHSTMT hstmt, char* table_name)
{
char sql_select[500] = "select * from ";
int i=0;
if(0 == PRINT_TABLE)
{
return;
}
printf("print table %s.\n", table_name);
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;
GBase 8a 程序员手册ODBC 篇
南大通用数据技术股份有限公司
- 55 -
/** @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);
}
}