/***************************************************************
 * 链接freetds-devel库： /usr/lib64/libsybdb.so.5.1.0
 * $ gcc sample2.c -L/usr/lib64 -lsybdb -o sample2
 * Windows Server 防火墙需要创建mssql入站规则：打开默认的1433端口
 ***************************************************************/
#include <sybfront.h>  
#include <sybdb.h> 
#include <stdio.h>
#include <string.h>
#include <stdlib.h> 

typedef struct __mssql_conn
{
    char SQL_SERVER_IP[16];
    char SQL_SERVER_PORT[8];
    char SQL_SERVER[32];
    char user[32];
    char pass[64];
    char db[16];
    char table[32];

    DBINT result_code;  //定义保存数据库交易结果的类型
    DBPROCESS *dbprocess;   //数据库连接结果的句柄 
}MSSQL_CONN;

void mssql_conn_construct(MSSQL_CONN* conn, char* SQL_SERVER_IP, int SQL_SERVER_PORT,
                            char* user, char* pass,
                            char* db )
{
    //char s_port[25];
    //itoa(SQL_SERVER_PORT, s_port, 10);     //here 10 means decimal
    strcpy(conn->SQL_SERVER_IP, SQL_SERVER_IP);
   sprintf(conn->SQL_SERVER_PORT, "%d", SQL_SERVER_PORT);
   sprintf(conn->SQL_SERVER, "%s:%d", SQL_SERVER_IP, SQL_SERVER_PORT);
    strcpy(conn->user, user);
    strcpy(conn->pass, pass);
    strcpy(conn->db, db);
    //strcpy(conn->table, table);
}

int mssql_conn_open(MSSQL_CONN* conn)
{
    //建立到MSSQL数据库的连接------------------------   
    dbinit();                         //使用dbinit()函数, 进行freetds操作之前的初始化操作  
    LOGINREC *loginrec = dblogin();   //建立一个到数据库的连接的句柄  
    DBSETLUSER(loginrec, conn->user);  //向句柄中添加连接数据库的用户  
    DBSETLPWD(loginrec,  conn->pass);                     //向数据库连接句柄中添加密码  
    conn->dbprocess = dbopen(loginrec, conn->SQL_SERVER);   //连接数据库,并返回数据库连接结果的句柄  
          
    if(conn->dbprocess == FAIL)                                   //如果连接失败  
    {   
        fprintf(stderr, "Connect Fail\n");                  //在标准错误中输出信息  
        //exit(EXIT_FAILURE);                                 //进程异常退出  
        return 1;
    }  
    else                                                    //如果连接成功  
    {  
        printf("Connect success\n");  
    }  
           
    if(dbuse(conn->dbprocess, conn->db) == FAIL)                 //使用某个数据库，如果使用失败  
    {  
        dbclose(conn->dbprocess);                                 //关闭数据库连接句柄, 并且回收相关资源  
        //exit(EXIT_FAILURE);                                 //进程异常退出  
        return 2;
    }  
    return 0;
}

int main(int argc, char* argv[])
{
    MSSQL_CONN conn;
    mssql_conn_construct(&conn, "172.24.1.122", 1433,
                            "sa", "passw0rd",
                            "testDB");
    //建立到MSSQL数据库的连接------------------------   
    mssql_conn_open(&conn);
    //开始进行数据库中数据查询的工作------------------  
    char mssqlbuf[1024];                                    //定义保存数据库查询语句的字符串  
    memset(mssqlbuf, 0x00, sizeof(mssqlbuf));               //开始初始化字符串  
    char sql[1024]; memset(sql, 0, 1024);
    sprintf(mssqlbuf, "SELECT name, age FROM testTable");   //组装操作sql的语句, 将sql语句保存到mssqlbuf  
    dbcmd(conn.dbprocess, mssqlbuf);                             //将刚刚组装好的sql命令, 使用dbcmd命令保存到数据库连接句柄的缓存中  
                              
    if(dbsqlexec(conn.dbprocess) == FAIL)                        //如果执行的命令失败  
    {   
        dbclose(conn.dbprocess);                                 //关闭数据库操作进程  
        exit(EXIT_FAILURE);                                 //程序异常退出  
    }  
    
    RETCODE erc;
    while((erc = dbresults(conn.dbprocess)) != NO_MORE_RESULTS) {
        int ncols; int row_code; 
        struct col {        //保存列的所有信息
            char *name;     //列名字
            char *buffer;   //存放列数据指针
            int type, size, status;
        } *columns, *pcol;
        
        if(erc == FAIL) {
            fprintf(stderr, "dbresults failed\n");
            exit(1);
        } 
        ncols = dbnumcols(conn.dbprocess);//返回执行结果的列数目 
        if((columns = calloc(ncols, sizeof(struct col))) == NULL) {
            perror(NULL);
            exit(1);
        }
        /* read metadata and bind. */
        for(pcol = columns; pcol - columns < ncols; pcol++) {
            int c = pcol - columns + 1;
            pcol->name = dbcolname(conn.dbprocess, c); //返回指定列的列名
            pcol->type = dbcoltype(conn.dbprocess, c);
            pcol->size = dbcollen(conn.dbprocess, c); 
            printf("%*s(%d)", 20, pcol->name, pcol->size);
            if((pcol->buffer = calloc(1, 20)) == NULL) {
                perror(NULL);
                exit(1);
            }

            if((erc = dbbind(conn.dbprocess, c, NTBSTRINGBIND, 20, (BYTE*)pcol->buffer)) == FAIL) {
                fprintf(stderr, "dbbind(%d) failed\n", c);
                exit(1);
            }  
            if(( erc = dbnullbind(conn.dbprocess, c, &pcol->status)) == FAIL) {
                fprintf(stderr, "dbnullbind(%d) failed\n", c);
                exit(1);
            }
        }
    
        printf("\n");
        /* 打印数据 */
        while((row_code = dbnextrow(conn.dbprocess)) != NO_MORE_ROWS) {//读取行数据
            switch(row_code) {
                case REG_ROW:
                    for(pcol=columns; pcol - columns < ncols; pcol++) {
                        char *buffer = pcol->status == -1 ? "null" : pcol->buffer;
                        printf("%*s ", 20, buffer);
                    }
                    printf("\n"); 
                    break;
                case BUF_FULL: 
                    break;
                case FAIL:
                    fprintf(stderr, "dbresults failed\n");
                    exit(1); 
                    break;
                default:
                    printf("data for computeid %d ignored\n", row_code);
            }
        }
        /* free metadata and data buffers */
        for(pcol=columns; pcol - columns < ncols; pcol++) {
            free(pcol->buffer);
        }
        free(columns);
        if(DBCOUNT(conn.dbprocess) > -1) /* 得到SQL语句影响的行数 */
            fprintf(stderr, "%d rows affected\n", DBCOUNT(conn.dbprocess));
    }

    dbclose(conn.dbprocess);                                            //关闭数据库连接   
    dbexit();

    return 0;
}