
平台论坛博客文库

› 论坛 › 程序设计 › C/C++ › linux下读取sqlserver2000的方法和程序

linux下读取sqlserver2000的方法和程序 [复制链接]

ukalpa

稍有积蓄

论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2004-02-27 17:21 |只看该作者 |倒序浏览

哎～　弄了几天，在　菜青虫　朋友的帮助下，终于搞定了，并写了个类～

主要参考了freeTDS中的 tsql.c　文件，朋友们去看那个，比我做的好的多，也就是改了改.

但请不要学我的代码！　我代码很不规范～我的Ｃ也很差　，期望高手能帮我把代码重写一下

请下载安装:freeTDS

/*
author: ukalpa@sohu.com
date : 2004-02-26
*/
-----------------------------------------
//mssql.h
// header
#include <stdio.h>;
#include <string.h>;
#include <stdlib.h>;
#include <unistd.h>;
#include <locale.h>;
#include <sys/time.h>;
#include <time.h>;
#include <assert.h>;
#include <tds.h>;
#include <tdsconvert.h>;
#define PERLENGTH 512
/* define function */
// 转换成小写
int tolower(int ch)
{
if (ch >; 64 && ch < 91)
{
return (ch + 32);
}
}
char *strlwr(char *str)
{
for (int i = 0; i < strlen(str); i++)
{
tolower(str[i]);
}
return str;
}
int tsql_handle_message(TDSCONTEXT * context, TDSSOCKET * tds, TDSMSGINFO * msg)
{
if (msg->;msg_number == 0) {
fprintf(stderr, "%s\n", msg->;message);
return 0;
}
if (msg->;msg_number != 5701 && msg->;msg_number != 20018) {
fprintf(stderr, "Msg %d, Level %d, State %d, Server %s, Line %d\n%s\n",
msg->;msg_number, msg->;msg_level, msg->;msg_state, msg->;server, msg->;line_number, msg->;message);
}
return 0;
}
//本版块需要用到的自定义函数
/*
* 函数功能：创建2维数组
* x : 数组参数名
* rows : 行数
* cols : 列数
*/
template <class T>;
void Make2DArray(T*** &x, int rows, int cols)
{
//创建行指针
x = new T** [rows];
//为每行分配空间
for(int i = 0; i < rows; i++)
{
x[i] = new T* [cols];
for(int j = 0; j < cols; j++)
{
x[i][j] = new char [255];
}
}
}
/*
* 函数功能：释放2维数组
* x : 数组参数名
* rows : 行数
*/
template <class T>;
void Delete2DArray(T*** &x, int rows, int cols)
{
//释放为每行所分配的空间
for(int i = 0; i < rows; i++)
{
for(int j = 0; j < cols; j++)
{
delete [] x[i][j];
}
delete[] x[i];
}
//删除行指针
delete[] x;
x = NULL;
}
//结束本版块需要用到的自定义函数
/* end define function */
class mssql{
public:
mssql(const char *host, int port, const char *user, const char *pass);
~mssql();
bool ms_connect(const char *host, int port, const char *user, const char *pass); //连接
void selectdb(const char *db); //选择数据库
void errormsg(const char *msg); //错误信息
void query(const char *query); //执行query语句
void freem(); //释放
char **fetch_array();
int numrows();
int numcols();
private:
int current;
int get_rows;
int get_cols;
char ***my_res;
const char *locale;
char *charset;
TDSSOCKET *tds;
TDSLOGIN *login;
TDSCONTEXT *context;
TDSCONNECTINFO *connect_info;
/* used in query () */
int rc, i;
int ctype;
unsigned char *src;
char message[128];
CONV_RESULT dres;
TDSCOLINFO *col;
TDS_INT srclen;
TDS_INT rowtype;
TDS_INT resulttype;
TDS_INT computeid;
struct timeval start, stop;
//char **cols_name; // 列名称
//char **cols_values; // 列描述
/* end used in query() */
};
mssql::mssql(const char *host, int port, const char *user, const char *pass)
{
// init
this->;locale = NULL;
this->;charset = NULL;
this->;get_rows = 0;
this->;get_cols = 0;
this->;current = 0;
//this->;cols_name = NULL;
//this->;cols_values = NULL;
this->;ms_connect(host, port, user, pass);
}
bool mssql::ms_connect(const char *host, int port, const char *user, const char *pass)
{
//step 1 : init
this->;login = tds_alloc_login(); //初始化login结构，分配内存
this->;context = tds_alloc_context();//初始化context,获得当前信息
if (this->;context->;locale && !this->;context->;locale->;date_fmt)
{
/* set default in case there`s no locale file */
this->;context->;locale->;date_fmt = strdup("%b %e %Y %I:%M%p");
}
this->;context->;msg_handler = tsql_handle_message;
this->;context->;err_handler = tsql_handle_message;
//step 2 : connect mssql
setlocale(LC_ALL, "");
this->;locale = setlocale(LC_ALL, NULL);
#if HAVE_LOCALE_CHARSET
this->;charset = locale_charset(); //charset
#endif
#if HAVE_NL_LANGINFO && defined(CODESET)
if (!this->;charset)
this->;charset = nl_langinfo(CODESET); //language
#endif
//
tds_set_user(this->;login, user);
tds_set_app(this->;login, "TSQL");
tds_set_library(this->;login, "TDS_Library");
tds_set_server(this->;login, host);
tds_set_port(this->;login, port);
if (!this->;charset) {
this->;charset = "ISO-8859-1";
}
tds_set_client_charset(this->;login, this->;charset);
tds_set_language(this->;login, "us_english");
tds_set_passwd(this->;login, pass);
/* Try to open a connection */
this->;tds = tds_alloc_socket(this->;context, 512); //初始化socket
tds_set_parent(this->;tds, NULL);
this->;connect_info = tds_read_config_info(NULL, this->;login, this->;context->;locale);
if (!this->;connect_info || tds_connect(this->;tds, this->;connect_info) == TDS_FAIL)
{
tds_free_connect(this->;connect_info);
//fprintf(stderr, "There was a problem connecting to the server\n");
//exit(1);
this->;errormsg("There was a problem connecting to the server\n");
}
tds_free_connect(this->;connect_info);
}
mssql::~mssql()
{
this->;freem();
}
void mssql::errormsg(const char *msg)
{
printf("Content-Type: text/html \n\n");
printf("ErrorMsg: %s<br>;\n", msg);
this->;freem();
exit(1);
}
//
void mssql::query(const char *query)
{
//先判断是否get_rows为空,如果未空，则清空资源
if (this->;get_rows >; 0)
{
Delete2DArray(this->;my_res, this->;get_rows, this->;get_cols);
this->;get_rows = 0;
}
if (NULL == query || 0 == strcmp(query, ""))
{
this->;errormsg("查询语句不能为空");
}
char prev[7];
memset(prev, 0, 7);
prev[0] = query[0];
prev[1] = query[1];
prev[2] = query[2];
prev[3] = query[3];
prev[4] = query[4];
prev[5] = query[5];
prev[6] = 0;
strlwr(prev);
//先分析query语句,如果是select语句
if (strstr(prev, "select"))
{
//
char *tmp_query1, *tmp_query2 = NULL;
tmp_query1 = new char [strlen(query) + 1];
strcpy(tmp_query1, query);
strlwr(tmp_query1); //小写
tmp_query2 = strstr(tmp_query1, "from");
tmp_query2 = (char *)(query + (tmp_query2 - tmp_query1));
sprintf(tmp_query1, "select count(*) %s", tmp_query2);
this->;rc = tds_submit_query(this->;tds, tmp_query1);
if (this->;rc != TDS_SUCCEED)
{
delete [] tmp_query1;
tmp_query1 = NULL;
this->;errormsg("query语句执行失败\n");
}
while ((this->;rc = tds_process_result_tokens(this->;tds, &this->;resulttype, NULL)) == TDS_SUCCEED) {
if (this->;resulttype == TDS_ROW_RESULT)
{
this->;get_rows = 0;
while ((this->;rc = tds_process_row_tokens(this->;tds, &this->;rowtype, &this->;computeid)) == TDS_SUCCEED) {
this->;get_rows++;
if (!this->;tds->;res_info)
continue;
for (i = 0; i < this->;tds->;res_info->;num_cols; i++) {
if (tds_get_null(this->;tds->;res_info->;current_row, i)) {
continue;
}
this->;col = this->;tds->;res_info->;columns[i];
this->;ctype = tds_get_conversion_type(this->;col->;column_type, this->;col->;column_size);
this->;src = &(this->;tds->;res_info->;current_row[this->;col->;column_offset]);
if (is_blob_type(this->;col->;column_type))
this->;src = (unsigned char *) ((TDSBLOBINFO *) this->;src)->;textvalue;
this->;srclen = this->;col->;column_cur_size;
if (tds_convert(this->;tds->;tds_ctx, this->;ctype, (TDS_CHAR *) this->;src, this->;srclen, SYBVARCHAR, &this->;dres) < 0)
continue;
//fprintf(stdout, "%s\t", this->;dres.c);
this->;get_rows = atoi(this->;dres.c);
free(this->;dres.c);
}
}
}
}
delete [] tmp_query1;
tmp_query1 = NULL;
}
//{
this->;rc = tds_submit_query(this->;tds, query);
if (this->;rc != TDS_SUCCEED)
{
this->;errormsg("query语句执行失败\n");
}
int i = 0, j = 0, t = 0;
//先将字段名保存到数组中
while ((this->;rc = tds_process_result_tokens(this->;tds, &this->;resulttype, NULL)) == TDS_SUCCEED) {
switch (this->;resulttype) {
case TDS_ROWFMT_RESULT: //打印字段名
//
this->;get_cols = this->;tds->;res_info->;num_cols;
// 开始进行数据读取工作
if (this->;get_rows >; 0)
{
// 行 + 1 ， 0，0 表示列
this->;get_rows++;
Make2DArray(this->;my_res, this->;get_rows, this->;get_cols);
}
if (this->;tds->;res_info) {
for (i = 0; i < this->;tds->;res_info->;num_cols; i++) {
//fprintf(stdout, "%s\t", this->;tds->;res_info->;columns[i]->;column_name);
t = strlen(this->;my_res[j][i]);
while (t < strlen(this->;tds->;res_info->;columns[i]->;column_name))
{
t += PERLENGTH;
}
this->;my_res[j][i] = (char *)realloc(this->;my_res[j][i], t * sizeof(char));
if (!this->;my_res[j][i])
{
this->;errormsg("内存分配失败");
}
memset(this->;my_res[j][i], 0, t);
strcpy(this->;my_res[j][i], this->;tds->;res_info->;columns[i]->;column_name);
}
}
break;
case TDS_ROW_RESULT:
while ((this->;rc = tds_process_row_tokens(this->;tds, &this->;rowtype, &this->;computeid)) == TDS_SUCCEED) {
j++; // go tag
if (!this->;tds->;res_info)
continue;
//fprintf(stdout, "%d, %s\n", this->;tds->;res_info->;null_info_size, this->;tds->;res_info->;current_row);
for (i = 0; i < this->;get_cols; i++)
{
if (tds_get_null(this->;tds->;res_info->;current_row, i))
{
continue;
}
this->;col = this->;tds->;res_info->;columns[i];
this->;ctype = tds_get_conversion_type(this->;col->;column_type, this->;col->;column_size);
this->;src = &(this->;tds->;res_info->;current_row[col->;column_offset]);
if (is_blob_type(this->;col->;column_type))
this->;src = (unsigned char *) ((TDSBLOBINFO *) this->;src)->;textvalue;
this->;srclen = this->;col->;column_cur_size;
if (tds_convert(this->;tds->;tds_ctx, this->;ctype, (TDS_CHAR *) this->;src, this->;srclen, SYBVARCHAR, &this->;dres) < 0)
continue;
t = strlen(this->;my_res[j][i]);
while (t < strlen(this->;tds->;res_info->;columns[i]->;column_name))
{
t += PERLENGTH;
}
this->;my_res[j][i] = (char *)realloc(this->;my_res[j][i], t * sizeof(char));
if (!this->;my_res[j][i])
{
this->;errormsg("内存分配失败");
}
memset(this->;my_res[j][i], 0, t);
strcpy(this->;my_res[j][i], this->;dres.c);
free(this->;dres.c);
}
}
break;
case TDS_STATUS_RESULT:
printf("(return status = %d)\n", this->;tds->;ret_status);
break;
default:
break;
}
}
//}
}
//释放内存
void mssql::freem()
{
if (this->;get_rows >; 0)
{
//Delete2DArray(this->;my_res, this->;get_rows, this->;get_cols);
this->;get_rows = 0;
}
tds_free_socket(this->;tds);
tds_free_login(this->;login);
tds_free_context(this->;context);
}
//选择数据库
void mssql::selectdb(const char *db)
{
if (NULL == db || 0 == strcmp(db, ""))
{
this->;errormsg("数据库未指定为空");
}
char tmp_str[100];
memset(tmp_str, 0, 100);
sprintf(tmp_str, "use %s", db);
this->;rc = tds_submit_query(this->;tds, tmp_str);
if (this->;rc != TDS_SUCCEED)
{
this->;errormsg("数据库无法连接");
}
int i = 0;
tds_process_result_tokens(this->;tds, &this->;resulttype, NULL);
/*
while ((this->;rc = tds_process_result_tokens(this->;tds, &this->;resulttype, NULL)) == TDS_SUCCEED)
{
//
i++;
printf("%d\n", i);
}
*/
}
//取行
char ** mssql::fetch_array()
{
if (this->;current >; (this->;get_rows - 2))
{
return NULL;
}
else
{
this->;current++; //偏移
return (this->;my_res[this->;current]);
}
}
//获得行数
int mssql::numrows()
{
if (this->;get_rows >; 1)
{
return (this->;get_rows - 1);
}
else
{
return 0;
}
}
//获得列数
int mssql::numcols()
{
return this->;get_cols;
}