直接上代码
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <memory.h>
#include <sqlcli1.h>
#ifndef _countof
#define _countof(x) (sizeof(x)/sizeof(x[0]))
#endif
#define hints printf
#define errors printf
#define SQL_STATE_LEN 256
#define SQL_ERROR_MSG 256
#define SQL_DBNAME_LEN 256
#define SQL_USER_NAME_LEN 64
#define SQL_USER_PASS_LEN 64
#define SQL_STATEMENT_LEN 1024
typedef struct sql_client_information_tag
{
SQLHENV env_handle;
SQLHDBC dbc_handle;
}sql_client_information,*sql_client_handle;
typedef int (*fetch_row)(sql_client_handle h,SQLHSTMT stmt,char* buffer,int len);
typedef struct sql_fetch_row_info_tag
{
char* buffer;
int length;
fetch_row fetch;
}sql_fetch_row_info,*sql_fetch_row_handle;
static int fetch_sql(sql_client_handle h,SQLHSTMT stmt,char* buffer,int len);
static void sql_error(sql_client_handle h,SQLHSTMT stm_handle);
static int clean_statement(sql_client_handle h,SQLHSTMT stmt,SQLRETURN frc);
static int sql_excute(sql_client_handle h,const char*const sql,sql_fetch_row_handle fh);
void destroy_sql_handle(sql_client_handle h);
sql_client_handle create_sql_handle(const char*const db_name,const char*const u_name,const char*const u_pass){
SQLCHAR DBName[SQL_MAX_DSN_LENGTH + 1] = {0};
SQLCHAR USRName[SQL_USER_NAME_LEN] = {0};
SQLCHAR USRPass[SQL_USER_PASS_LEN] = {0};
sql_client_handle ret = malloc(sizeof(sql_client_information));
if(NULL == ret){
return NULL;
}
memset(ret , 0 , sizeof(sql_client_information));
do {
SQLRETURN rc = SQLAllocEnv(&ret->env_handle);
if(rc != SQL_SUCCESS) break;
rc = SQLAllocConnect(ret->env_handle,&ret->dbc_handle);
if(rc != SQL_SUCCESS) break;
strncpy((char*)DBName,db_name,sizeof(DBName) - 1);
strncpy((char*)USRName,u_name,sizeof(USRName) - 1);
strncpy((char*)USRPass,u_pass,sizeof(USRPass) - 1);
rc = SQLConnect(ret->dbc_handle,DBName,SQL_NTS,USRName,SQL_NTS,USRPass,SQL_NTS);
if(rc != SQL_SUCCESS) break;
return ret;
} while (0);
sql_error(ret,NULL);
destroy_sql_handle(ret);
return NULL;
}
void destroy_sql_handle(sql_client_handle h){
do
{
if(NULL != h->dbc_handle){
if(SQL_SUCCESS != SQLDisconnect(h->dbc_handle)) break;
if(SQL_SUCCESS != SQLFreeConnect(h->dbc_handle)) break;
h->dbc_handle = SQL_NULL_HDBC;
}
if (NULL != h->env_handle)
{
if(SQL_SUCCESS != SQLFreeEnv(h->env_handle)) break;
h->env_handle = SQL_NULL_HENV;
}
free(h);
return;
} while (0);
sql_error(h,NULL);
free(h);
}
int sql_excute_no_result(sql_client_handle h, const char*const sql_statement){
return sql_excute(h,sql_statement,NULL);
}
int sql_excute_with_result(sql_client_handle h ,char* sql_statment,char*const result,int len){
sql_fetch_row_info fetch_row = {result,len,fetch_sql};
return sql_excute(h,sql_statment,&fetch_row);
}
static void sql_error(sql_client_handle h,SQLHSTMT stm_handle){
SQLCHAR sql_state[SQL_SQLSTATE_SIZE + 1] = {0};
SQLCHAR sql_error[SQL_MAX_MESSAGE_LENGTH + 1] = {0};
SQLINTEGER native_error = 0;
SQLSMALLINT pcb_error = 0;
SQLRETURN rc = SQLError(h->env_handle,h->dbc_handle,stm_handle,
sql_state,&native_error,sql_error,sizeof(sql_error),&pcb_error);
if(rc == SQL_NO_DATA_FOUND){
return;
}
errors("sql state : %s\n",sql_state);
errors("sql error : %s\n",sql_error);
}
static int clean_statement(sql_client_handle h,SQLHSTMT stmt,SQLRETURN frc){
sql_error(h,stmt);
switch(frc){
case SQL_SUCCESS:
case SQL_SUCCESS_WITH_INFO:break;
case SQL_ERROR:
case SQL_INVALID_HANDLE:
default:
SQLTransact(h->env_handle,h->dbc_handle,SQL_ROLLBACK);
return frc;
}
return 0;
}
static int sql_excute(sql_client_handle h,const char*const sql,sql_fetch_row_handle fh){
SQLCHAR SQLState[SQL_STATEMENT_LEN] = {0};
SQLHSTMT stmt = SQL_NULL_HSTMT;
SQLRETURN rc = SQLAllocStmt(h->dbc_handle,&stmt);
do
{
if(SQL_SUCCESS != rc) break;
strncpy((char*)SQLState,sql,sizeof(SQLState) - 1);
rc = SQLExecDirect(stmt,SQLState,SQL_NTS);
if(SQL_SUCCESS != rc) break;
if(NULL != fh){
if(SQL_SUCCESS != fh->fetch(h,stmt,fh->buffer,fh->length)) break;
}
rc = SQLTransact(h->env_handle,h->dbc_handle,SQL_COMMIT);
if(SQL_SUCCESS != rc) break;
SQLFreeStmt(stmt,SQL_DROP);
return 0;
} while (0);
if(SQL_NULL_HSTMT != stmt){
SQLFreeStmt(stmt,SQL_DROP);
stmt = SQL_NULL_HSTMT;
}
return clean_statement(h,stmt,rc);
}
static int fetch_data(SQLHSTMT stmt,int col_index,int type,const char*const col_name,char*const buffer,int len){
SQLCHAR char_buffer[SQL_MAX_MESSAGE_LENGTH] = {0};
SQLINTEGER buffer_len = 0;
SQLRETURN rc = SQLGetData(stmt,col_index,SQL_CHAR,char_buffer,sizeof(char_buffer),&buffer_len);
if(SQL_SUCCESS != rc){
errors("%s : SQLGetData = %d\n",__FUNCTION__,rc);
return rc;
}
switch(type){
case SQL_NUMERIC:
case SQL_DECIMAL:
case SQL_INTEGER:
case SQL_SMALLINT:
case SQL_FLOAT:
case SQL_REAL:
case SQL_DOUBLE:
case SQL_DATETIME:
return snprintf(buffer,len,"%s:%s,",col_name,char_buffer);
default:
return snprintf(buffer,len,"%s:\"%s\",",col_name,char_buffer);
}
}
typedef struct record_set_header_tag{
SQLCHAR Name[SQL_MAX_COLUMN_NAME_LEN];
SQLUINTEGER Define;
SQLSMALLINT Type;
SQLSMALLINT Scale;
SQLSMALLINT IsNUll;
}record_set_header,*record_set_header_handle;
static int fetch_sql_row(SQLHSTMT stmt,int col_counts,record_set_header headers[SQL_MAX_COLUMNS_IN_TABLE],char* buffer,int len){
int sum_len = snprintf(buffer,len,"{");
buffer += sum_len;len -= sum_len;
for (int i = 0; i < col_counts; ++i){
int ret = fetch_data(stmt,i + 1,headers[i].Type,headers[i].Name,buffer,len);
if(ret < 0){
errors("%s : ret = %d",__FUNCTION__,ret);
return ret;
}
buffer += ret;
len -= ret;
sum_len += ret;
}
len = snprintf(buffer - 1,len + 1,"},\n");
return sum_len + len - 1;
}
static int fetch_record_set_header(SQLHSTMT stmt,record_set_header headers[SQL_MAX_COLUMNS_IN_TABLE]){
SQLSMALLINT SQLColCount = 0,i = 0,NameLen = 0;
SQLRETURN rc = SQLNumResultCols(stmt,&SQLColCount);
memset(headers , 0 , sizeof(record_set_header)*SQL_MAX_COLUMNS_IN_TABLE);
if (SQL_SUCCESS != rc){
errors("%s: SQLNumResultCols = %d\n",__FUNCTION__,rc);
return rc;
}
for (i = 0 ; i < SQLColCount && i < SQL_MAX_COLUMNS_IN_TABLE; ++i)
{
rc = SQLDescribeCol(stmt,i + 1,headers[i].Name,sizeof(headers[i].Name),&NameLen,
&headers[i].Type,&headers[i].Define,&headers[i].Scale,&headers[i].IsNUll);
if(SQL_SUCCESS != rc){
errors("%s : SQLDescribeCol = %d\n",__FUNCTION__,rc);
return rc;
}
}
return i;
}
static int fetch_sql(sql_client_handle h,SQLHSTMT stmt,char* buffer,int len){
record_set_header headers[SQL_MAX_COLUMNS_IN_TABLE] = {0};
int sum_counts = len;
int row_counts = fetch_record_set_header(stmt,headers);
if (row_counts < 0)
{
return row_counts;
}
len -= snprintf(buffer,len,"[\n");
buffer += (sum_counts - len);
for (;SQL_NO_DATA != SQLFetch(stmt);){
int ret = fetch_sql_row(stmt,row_counts,headers,buffer,len);
if(ret < 0){
errors("%s : ret = %d",__FUNCTION__,ret);
return ret;
}
len -= ret;
buffer += ret;
}
len -= snprintf(buffer - 2, len + 2, "\n]\n");
return sum_counts - len + 3;
}
static char result[1024 * 1024 * 4];
int main(int argc, char* argv[])
{
char cmd[1024];
sql_client_handle h = NULL;
if(argc < 4){
errors("usage : \n db2jsion db_name u_name u_pass \n");
return -1;
}
h = create_sql_handle(argv[1],argv[2],argv[3]);
if (NULL == h){
return -1;
}
for (gets(cmd);strcmp(cmd,"q") != 0;gets(cmd)){
memset(result , 0 , sizeof(result));
hints("cmd = %s\n",cmd);
sql_excute_with_result(h,cmd,result,sizeof(result));
hints("%s\n",result);
memset(cmd, 0 , sizeof(cmd));
hints("next command\n");
}
destroy_sql_handle(h);
return 0;
}