标题
连接 sql server 的 jdbc 库 jTDS 来自 freetds,php用的也是这个
guest
浏览(0) +
2007-09-05 19:21:18 发表
编辑
关键字:
连接 sql server 的 jdbc 库 jTDS 来自 freetds,php用的也是这个 所以这个库是可以不用任何dll自己就能操作 sql server 的.不过它的例子不太好写,找了半天才说是改 tslq.c 而这个文件是linux下的,用了大量linux下的函数,我花了好大功夫才改成windows的,同时要先将 tds_sysdep_public.h.in 改名为 tds_sysdep_public.h 删除掉其中的 @dblib_define@ (这里应该是有什么含义的,不过俺估计也是与用什么库有关,反正这样能用也就行了) 修改后的 tslq.c 得来不易,贴一个吧. 另外要先编译 freetds 的 lib 后才能链接成功,这个是与编译器有关的,幸好它自带有vc6和devc++的工程,一编译就过这倒比多数开源的要好很多. -------------------------------------------------- /* FreeTDS - Library of routines accessing Sybase and Microsoft databases * Copyright (C) 1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005 Brian Bruns */ #include #include #include #include #include #include #include "tds.h" #include "tdsconvert.h" #include "replacements.h" #include "tds_sysdep_private.h"//clq add #include //clq add #include //clq add #include //clq add //#include //clq add //#include #include #include //这个是 linux 下的函数替代品 int gettimeofday(struct timeval * tp, void * tzp) { struct timeb tb; ftime(&tb); tp->tv_sec = tb.time;/* 距离1970年的总秒数 */ tp->tv_sec = tb.millitm * 1000L; /* 微秒 */ return(0); } static int do_query(TDSSOCKET * tds, char *buf); static void populate_login(TDSLOGIN * login, int argc, char **argv); static int tsql_handle_message(const TDSCONTEXT * context, TDSSOCKET * tds, TDSMESSAGE * msg); static int do_query(TDSSOCKET * tds, char *buf) { int rows = 0; int rc, i; TDSCOLUMN *col; int ctype; CONV_RESULT dres; unsigned char *src; TDS_INT srclen; TDS_INT resulttype; struct timeval start, stop; int print_rows = 1; rc = tds_submit_query(tds, buf); if (rc != TDS_SUCCEED) { fprintf(stderr, "tds_submit_query() failed\n"); return 1; } while ((rc = tds_process_tokens(tds, &resulttype, NULL, TDS_TOKEN_RESULTS)) == TDS_SUCCEED) { //取得初始时间 gettimeofday(&start, NULL); print_rows = 0;//是否打印内容 switch (resulttype) { case TDS_ROWFMT_RESULT: if (tds->current_results) { //各个字段的名称 for (i = 0; i < tds->current_results->num_cols; i++) { fprintf(stdout, "%s\t", tds->current_results->columns[i]->column_name); } fprintf(stdout, "\n"); } break; case TDS_COMPUTE_RESULT: case TDS_ROW_RESULT://各个字段的值 rows = 0; while ((rc = tds_process_tokens(tds, &resulttype, NULL, TDS_STOPAT_ROWFMT|TDS_RETURN_DONE|TDS_RETURN_ROW|TDS_RETURN_COMPUTE)) == TDS_SUCCEED) { if (resulttype != TDS_ROW_RESULT && resulttype != TDS_COMPUTE_RESULT) break; rows++; if (!tds->current_results) continue; for (i = 0; i < tds->current_results->num_cols; i++) { col = tds->current_results->columns[i]; if (col->column_cur_size < 0) { if (print_rows) fprintf(stdout, "NULL\t"); continue; } ctype = tds_get_conversion_type(col->column_type, col->column_size); src = &(tds->current_results->current_row[col->column_offset]); if (is_blob_type(col->column_type)) src = (unsigned char *) ((TDSBLOB *) src)->textvalue; srclen = col->column_cur_size; if (tds_convert(tds->tds_ctx, ctype, (TDS_CHAR *) src, srclen, SYBVARCHAR, &dres) < 0) continue; if (print_rows) fprintf(stdout, "%s\t", dres.c); free(dres.c); } if (print_rows) fprintf(stdout, "\n"); } break; case TDS_STATUS_RESULT: printf("(return status = %d)\n", tds->ret_status); break; default: break; } //比较一下前面的时间 gettimeofday(&stop, NULL); printf("Total time for processing %d rows: %ld msecs\n", rows, (long) ((stop.tv_sec - start.tv_sec) * 1000) + ((stop.tv_usec - start.tv_usec) / 1000)); } return 0; } static void populate_login(TDSLOGIN * login, int argc, char **argv) { char *hostname = NULL; char *servername = NULL; char *username = NULL; char *password = NULL; char *confile = NULL; int port = 0; const char *locale = NULL; char *charset = NULL; setlocale(LC_ALL, ""); locale = setlocale(LC_ALL, NULL); hostname = (char *) malloc(100); strcpy(hostname, "127.0.0.1"); strcpy(hostname, "."); servername = (char *) malloc(100); strcpy(servername, "127.0.0.1"); strcpy(servername, "."); username = (char *) malloc(100); strcpy(username, "wap_dba"); password = (char *) malloc(100); strcpy(password, "wap_dba"); //port = atoi(optarg); port = 1433;//1863; if (locale) printf("locale is \"%s\"\n", locale); if (charset) { printf("locale charset is \"%s\"\n", charset); } else { charset = "ISO-8859-1"; printf("using default charset \"%s\"\n", charset); } //clq del//if (servername) if(0) { tds_set_user(login, username); tds_set_app(login, "TSQL"); tds_set_library(login, "TDS-Library"); tds_set_server(login, servername); tds_set_client_charset(login, charset); tds_set_language(login, "us_english"); tds_set_passwd(login, password); if (confile) { tds_set_interfaces_file_loc(confile); } /* else we specified hostname/port */ } else//默认用这个 { tds_set_user(login, username); tds_set_app(login, "TSQL"); tds_set_library(login, "TDS-Library"); //tds_set_server(login, hostname); tds_set_server(login, "127.0.0.1"); //tds_set_server(login, "."); //tds_set_port(login, port); tds_set_port(login, 1433); tds_set_client_charset(login, charset); tds_set_language(login, "us_english"); tds_set_passwd(login, password); } /* free up all the memory */ if (hostname) free(hostname); if (username) free(username); if (password) free(password); if (servername) free(servername); } static int tsql_handle_message(const TDSCONTEXT * context, TDSSOCKET * tds, TDSMESSAGE * msg) { if (msg->msgno == 0) { printf("%s\n", msg->message); fprintf(stderr, "%s\n", msg->message); return 0; } if (msg->msgno != 5701 && msg->msgno != 5703 && msg->msgno != 20018) { printf("Msg %d, Level %d, State %d, Server %s, Line %d\n%s\n", msg->msgno, msg->severity, msg->state, msg->server, msg->line_number, msg->message); fprintf(stderr, "Msg %d, Level %d, State %d, Server %s, Line %d\n%s\n", msg->msgno, msg->severity, msg->state, msg->server, msg->line_number, msg->message); } return 0; } int main(int argc, char **argv) { char *s = NULL, *s2 = NULL, *cmd = NULL; int line = 0; int bufsz = 4096; TDSSOCKET *tds; TDSLOGIN *login; TDSCONTEXT *context; TDSCONNECTION *connection; int opt_flags = 0; #ifdef WIN32 WSADATA WSAData; if (WSAStartup(MAKEWORD(2, 2), &WSAData) != 0) { // socket初始化失败 return -1; } #endif /* grab a login structure */ login = tds_alloc_login(); context = tds_alloc_context(NULL); if (context->locale && !context->locale->date_fmt) { /* set default in case there's no locale file */ context->locale->date_fmt = strdup("%b %e %Y %I:%M%p"); } context->msg_handler = tsql_handle_message; context->err_handler = tsql_handle_message; /* process all the command line args into the login structure */ populate_login(login, argc, argv); /* Try to open a connection */ tds = tds_alloc_socket(context, 512); tds_set_parent(tds, NULL); connection = tds_read_config_info(NULL, login, context->locale); if (!connection || tds_connect(tds, connection) == TDS_FAIL) {//出错的情况 tds_free_socket(tds); tds_free_connection(connection); fprintf(stderr, "There was a problem connecting to the server\n"); exit(1); } tds_free_connection(connection); //执行一句 sql do_query(tds, "use wap_db");//选择数据库 do_query(tds, "select * from wap_pl where 1=1 "); /* close up shop */ tds_free_socket(tds); tds_free_login(login); tds_free_context(context); return 0; }
guest
官方网址 http://www.freetds.org/ 另一个网友写的例子. http://www.lslnet.com/linux/dosc1/04/linux-127236.htm 蓝森林 http://www.lslnet.com 2006年6月6日 10:18 linux下读取sqlserver2000的方法和程序 哎~ 弄了几天,在 菜青虫 朋友的帮助下,终于搞定了,并写了个类~ 主要参考了freeTDS中的 tsql.c 文件,朋友们去看那个,比我做的好的多,也就是改了改. 但请不要学我的代码! 我代码很不规范~我的C也很差 ,期望高手能帮我把代码重写一下:) 请下载安装:freeTDS [code] /* author: ukalpa@sohu.com date : 2004-02-26 */ ----------------------------------------- //mssql.h // header #include ; #include ; #include ; #include ; #include ; #include ; #include ; #include ; #include ; #include ; #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 ; 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 ; 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 ;\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; } [/code] 执行文件: [code] /* * testsql.cpp * need freetds support * cmd : g++ -o testsql testsql.cpp -ltds -lncurses * 3x :) */ #include "mssql.h" int main(void) { mssql a("127.0.0.1", 1433, "sa", "xxx"); a.selectdb("avatardb"); a.query("select * from test"); char **b; int cols = a.numcols(); int i; while (b = a.fetch_array()) { for (i = 0; i < cols; i++) { printf("%s\t", b[i]); } printf("\n"); } a.query("select * from test2"); cols = a.numcols(); while (b = a.fetch_array()) { for (i = 0; i < cols; i++) { printf("%s\t", b[i]); } printf("\n"); } return 0; } [/code] linux下读取sqlserver2000的方法和程序 类功能不多~主要想快点放出来,给需要的朋友看,应该我刚开始弄的时候,觉得类似的代码太少了 linux下读取sqlserver2000的方法和程序 给你加了原创,希望楼主精益求精, 写出更好的代码。 linux下读取sqlserver2000的方法和程序 谢谢:) linux下读取sqlserver2000的方法和程序 我觉得还不如用微软自己提供的jdbc来做呢 linux下读取sqlserver2000的方法和程序 楼上所言诧异。 软件是一种态度!
NEWBT官方QQ群1: 276678893
可求档连环画,漫画;询问文本处理大师等软件使用技巧;求档softhub软件下载及使用技巧.
但不可"开车",严禁国家敏感话题,不可求档涉及版权的文档软件.
验证问题说明申请入群原因即可.