« TimesTen标准文档下载
TimesTen随机自带编程资料索引 »
07 12 2008

TimesTen ODBC 编程范例

由canoe发布于应 用 开 发

    到TimesTen 7.0.5版本为止，官方支持的编程接口为ODBC 和 JDBC。下面是一个在Linux 32位平台上的编程范例。该范例等待学生输入自己的名字和系名，然后插入数据库，并返回目前数据库中所有的学生以及对应的系。

  • 创建一个DataStore，范例中使用的DSN为 ttodbc，下面是在我的平台上的DSN，可以对照你自己的环境进行修改。

[ttodbc]
Driver=/home/tt705/TimesTen/tt705/lib/libtten.so
DataStore=/home/tt705/datastore/ttodbc
LogDir=/home/tt705/log
DatabaseCharacterSet=ZHS16GBK
PermSize=12
TempSize=12

  •  在ttodbc中创建要使用的表以及Sequence

create table student(

  id                    integer  not null primary key,
  name              varchar(80),
  department    varchar(80) not null
);
CREATE SEQUENCE studentID INCREMENT BY 1;

  • Makefile 文件，只用把对应的INSTDIR改为你的安装目录即可。

#
#
# TimesTen ODBC exapmle
#
INSTDIR         = /home/tt705/TimesTen/tt705
COMMDIR         = $(INSTDIR)/demo/common
CC              = gcc
PLATCFLAGS      = -Os -finline-functions
LDFLAGS         =
INCS            = -I$(INSTDIR)/include -I$(COMMDIR) -I$(INSTDIR)/include/ttclasses
CSDEFS          = -DTTCLIENTSERVER
CFLAGS          = $(PLATCFLAGS) $(INCS)
TTLINK           = -L$(INSTDIR)/lib -Wl,-rpath,$(INSTDIR)/lib
DCLIBS          = $(TTLINK) -ltten $(EXTRALIBS)
CSLIBS          = $(TTLINK) -lttclient $(EXTRALIBS)
#BLKINSDC        = ttodbc
#BLKINSDCOBJS    = ttodbc.o
#PROGS           = $(BLKINSDC)
all:  ttodbc

ttodbc: ttodbc.o
  $(CC) -o ttodbc $(LDFLAGS) ttodbc.o $(DCLIBS)
clean:         
  rm -f ttodbc.o

#
# .o files
#
ttodbc.o: ttodbc.c
  $(CC) $(CFLAGS) -c ttodbc.c

 /* TimesTen ODBC example by canoe*/

#ifdef WIN32
    #include <windows.h>
#else
    #include <sqlunix.h>
#endif
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#define SQL_MAXLEN    200     /* Max length of SQL buffer */
#define NAME_MAXLEN   20     /* Max length of Name */
typedef         SQLCHAR *STR;
void CheckReturnCode(SQLRETURN rc, SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt, char *msg, char *filename, int lineno);
int main(void)
{
    /* General return code for the API */
    SQLRETURN rc = SQL_SUCCESS;
   
    /* Environment handle */
    SQLHENV hEnv = SQL_NULL_HENV;
   
    /* Connection handle */
    SQLHDBC hDbc = SQL_NULL_HDBC;
   
    /* Statement handle */
    SQLHSTMT hStmt_ins = SQL_NULL_HSTMT;
    SQLHSTMT hStmt_sel = SQL_NULL_HSTMT;
   
   
    /* Connection attributes */
    SQLCHAR *ConnString = (SQLCHAR*)"DSN=ttcore;";
    /* local variable for insert*/
    SQLCHAR    name_ins[NAME_MAXLEN+1];
    SQLCHAR    department_ins[NAME_MAXLEN+1];
    /* local variable for select*/
    int     id_sel = 0;
    SQLCHAR    name_sel[NAME_MAXLEN+1];
    SQLCHAR    department_sel[NAME_MAXLEN+1];
   
    char     *sqlbuf=’\0′;
   
    SQLINTEGER     cbStatus = SQL_NTS;
    /* Initializtion begin…*/

    /**最好在每个重要的操作之后都要捕捉错误信息或处理返回值，虽然写起来烦一些，
     *但对以后的troubleshooting绝对是物有所值的，trust me!*/

    rc = SQLAllocEnv(&hEnv);
    if ( rc != SQL_SUCCESS&& rc != SQL_SUCCESS_WITH_INFO )
    {
        fprintf(stderr, "*** ERROR in %s, line %d: %s\n",__FILE__, __LINE__, "allocating an environment handle");
        exit(1);
    }
    rc = SQLAllocConnect(hEnv, &hDbc);
    CheckReturnCode(rc, hEnv, SQL_NULL_HDBC, SQL_NULL_HSTMT, "Unable to allocate a connection handle\n", __FILE__, __LINE__);
    rc = SQLDriverConnect(hDbc, NULL, ConnString, SQL_NTS,NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
    CheckReturnCode(rc, hEnv, SQL_NULL_HDBC, SQL_NULL_HSTMT, "Unable to get a connection\n", __FILE__, __LINE__);
   rc = SQLSetConnectOption (hDbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
  CheckReturnCode(rc, hEnv, SQL_NULL_HDBC, SQL_NULL_HSTMT, "Unable to turn off commit\n", __FILE__, __LINE__);
    rc = SQLAllocStmt(hDbc, &hStmt_ins);
    CheckReturnCode(rc, hEnv, hDbc, SQL_NULL_HSTMT, "Unable to allocate a insert statement handle\n", __FILE__, __LINE__);
    rc = SQLAllocStmt(hDbc, &hStmt_sel);
    CheckReturnCode(rc, hEnv, hDbc, SQL_NULL_HSTMT, "Unable to allocate a select statement handle\n", __FILE__, __LINE__);
    printf("Successfully ini environment…\n");
 
    /** 准备完上面的初始化环境之后，下面开始准备该模块中涉及到的所有SQL语句，
     * 针对所有的SQL语句，有且仅有一次prepare，输入输出变量的绑定也有且仅有一次。
     * 这一点对性能非常重要，所以一般放在全局初始化区中进行。*/

   sqlbuf = (char *)malloc(SQL_MAXLEN+1);
   if (sqlbuf == NULL) {
     fprintf(stderr, "*** ERROR in %s, line %d: %s\n",__FILE__, __LINE__, "allocating an SQL buffer");
     exit(1);
   }
   /*下面两种的绑定方式都是可以的，即 ? 或者 :name_inst */
   /*strcat(sqlbuf,"insert into student values(studentID.NEXTVAL,?,?)"); */
   strcat(sqlbuf,"insert into student values(studentID.NEXTVAL,:name_ins,:department_ins)"); 
    rc = SQLPrepare(hStmt_ins, (STR) sqlbuf, SQL_NTS);
    CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to prepare statment\n", __FILE__, __LINE__);
    /* binding inserting column */
    rc = SQLBindParameter(hStmt_ins, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,0, 0,name_ins, sizeof(name_ins), &cbStatus);
    CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to bind insert name\n", __FILE__, __LINE__);
    rc = SQLBindParameter(hStmt_ins, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,0, 0,department_ins,sizeof(department_ins),&cbStatus);
    CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to bind insert department\n", __FILE__, __LINE__);
    /** Prepare select statement     */
   strcpy(sqlbuf,"select id,name,department from student"); 
    rc = SQLPrepare(hStmt_sel, (STR) sqlbuf, SQL_NTS);
    CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to prepare statment\n", __FILE__, __LINE__);
    /* binding output column */
    rc = SQLBindCol(hStmt_sel, 1, SQL_C_ULONG, &id_sel, 0, NULL);
    CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to bind id\n", __FILE__, __LINE__);
   
    rc = SQLBindCol(hStmt_sel, 2, SQL_C_CHAR,name_sel, sizeof(name_sel), NULL);  
    CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to bind name\n", __FILE__, __LINE__);
   
    rc = SQLBindCol(hStmt_sel, 3, SQL_C_CHAR,department_sel, sizeof(department_sel), NULL);
    CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to bind department\n", __FILE__, __LINE__);     
 
  /**在所有语句prepare，bind之后，做一次commit，
   *确保下面处理业务的时候是一个完全干净的环境*/

   rc = SQLTransact(hEnv, hDbc, SQL_COMMIT);
    CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to commit inserting\n", __FILE__, __LINE__);
  /*inserting first*/
    printf("Preparing insert…\n");
    printf("Name  :");
    scanf("%s",name_ins);
    printf("Department :");
    scanf("%s",department_ins);
    printf("Inserting…\n");
 
  /**通过上面的一系列准备工作，下面才是真正的业务处理流程。
   *可以看到每次只用给绑定的变量赋予不同的值，然后直接SQLExecute即可。
   *完全不用再次重新prepare，bind*/  

    rc = SQLExecute(hStmt_ins);
    CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to inserting\n", __FILE__, __LINE__);
   rc = SQLTransact(hEnv, hDbc, SQL_COMMIT);
    CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to commit inserting\n", __FILE__, __LINE__);
  
    if ( hStmt_ins != SQL_NULL_HSTMT ){
        rc = SQLFreeStmt(hStmt_ins, SQL_DROP);
        CheckReturnCode(rc, hEnv, hDbc, hStmt_ins, "Unable to free the insert statement handle\n", __FILE__, __LINE__);
    }
    printf("Inserting done.\n");

    printf("Selecting…\n");
    rc = SQLExecute(hStmt_sel);
    CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to executeSQL\n", __FILE__, __LINE__);
  //fetch data
  while ((rc = SQLFetch(hStmt_sel)) == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO){
     if (rc != SQL_NO_DATA_FOUND){
       printf("Id: %d ",id_sel);
        printf("Name: %s ",name_sel);
       printf("Department: %s \n",department_sel);
     }
  }
   if ( hStmt_sel != SQL_NULL_HSTMT ){
        rc = SQLFreeStmt(hStmt_sel, SQL_CLOSE);
        CheckReturnCode(rc, hEnv, hDbc, hStmt_sel, "Unable to free the select statement handle\n", __FILE__, __LINE__);
    }

    /**在整个应用退出之前再次做一次Commit，以防有漏提交的事务*/
    rc = SQLTransact(hEnv, hDbc, SQL_COMMIT);
    
    /**退出的整个过程也是按照初始化时候的倒序一步一步进行的*/
    if ( hDbc != SQL_NULL_HDBC ){
        rc = SQLDisconnect(hDbc);
        CheckReturnCode(rc, hEnv, hDbc, SQL_NULL_HSTMT, "Unable to close the ""connection\n", __FILE__, __LINE__);
        rc = SQLFreeConnect(hDbc);
        CheckReturnCode(rc, hEnv, hDbc, SQL_NULL_HSTMT, "Unable to free the ""connection handle\n", __FILE__, __LINE__);
    }

    if ( hEnv != SQL_NULL_HENV ){
        rc = SQLFreeEnv(hEnv);
        CheckReturnCode(rc, hEnv, SQL_NULL_HDBC, SQL_NULL_HSTMT, "Unable to free the ""environment handle\n", __FILE__, __LINE__);
    }
   
    free(sqlbuf);
    exit(0);
}
void CheckReturnCode(SQLRETURN rc, SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt, char *msg, char *filename, int lineno)
{
#define MSG_LNG 512
    SQLCHAR szSqlState[MSG_LNG];
    /* SQL state string */
    SQLINTEGER pfNativeError;
    /* Native error code */
    SQLCHAR szErrorMsg[MSG_LNG];
    /* Error msg text buffer pointer */
    SQLSMALLINT pcbErrorMsg;
    /* Error msg text Available bytes */
    SQLRETURN ret = SQL_SUCCESS;
    if ( rc != SQL_SUCCESS && rc != SQL_NO_DATA_FOUND )
    {
        if ( rc != SQL_SUCCESS_WITH_INFO )
        {
            /*
             * It’s not just a warning
             */
            fprintf(stderr, "*** ERROR in %s, line %d:"" %s\n", filename, lineno, msg);
        }
        /*
         * Now see why the error/warning occurred
         */
        while ( ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO )
        {
            ret = SQLError(henv, hdbc, hstmt, szSqlState, &pfNativeError, szErrorMsg, MSG_LNG,  &pcbErrorMsg);
            switch ( ret )
            {
            case SQL_SUCCESS:
                fprintf(stderr, "*** %s\n""*** ODBC Error/Warning = %s, ""TimesTen Error/Warning "" = %d\n", szErrorMsg, szSqlState, pfNativeError);
                break;
            case SQL_SUCCESS_WITH_INFO:
                fprintf(stderr, "*** Call to SQLError"" failed with return code of ""SQL_SUCCESS_WITH_INFO.\n ""*** Need to increase size of"" message buffer.\n");
                break;
            case SQL_INVALID_HANDLE:
                fprintf(stderr, "*** Call to SQLError"" failed with return code of ""SQL_INVALID_HANDLE.\n");
                break;
            case SQL_ERROR:
                fprintf(stderr, "*** Call to SQLError"" failed with return code of ""SQL_ERROR.\n");
                break;
            case SQL_NO_DATA_FOUND:
                break;
            } /* switch */
        } /* while */
    }
}
 
  • 运行结果
 
[tt705@west-mountain demo]$ ./ttodbc
Successfully ini environment…
Preparing insert…
Name            :canoe
Department      :computer   
Inserting…
Inserting done.
Selecting…
Id:     1       Name:   canoe   Department:     computer
[tt705@west-mountain demo]$ ./ttodbc
Successfully ini environment…
Preparing insert…
Name            :john
Department      :EE
Inserting…
Inserting done.
Selecting…
Id:     1       Name:   canoe   Department:     computer
Id:     2       Name:   john    Department:     EE

该文章发布于2008-December-7 5:24 pm，归类为应 用 开 发。 你可以通过RSS 2.0 來追踪该文章的留言。 你可以选择留言，或者从你的网站引用

一个回复 “TimesTen ODBC 编程范例”

  1. lau 说：
    December 29th, 2008于4:01 pm

    管理员您好
    我在vs2005中编译这句代码时出错
    #define DEFAULT_CONNSTR “DSN=Sample_tt70_32;PermSize=20″
    …
    SQLCHAR *connStr = (SQLCHAR*)DEFAULT_CONNSTR;
    …
    出错代码：rc = SQLDriverConnect(hDbc, NULL, ConnString, SQL_NTS,NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
    报错内容：错误 1 error C2664: ‘SQLDriverConnectW’ : cannot convert parameter 3 from ‘SQLCHAR *’ to ‘SQLWCHAR *’ d:\testsolution\testcpp\testcpp.cpp 51

    为什么SQLDriverConnect的第三个参数要求的是SQLCHAR * 类型, 而我编译的时候它却要求该参数为SQLWCHAR * 类型？我试着从SQLCHAR* 向SQLWCHAR*转换，编译通过，但运行错误，因为在转换后字符的改为了unicode字符集。

    我不知该如何解决这个问题。请你帮我看一下，谢谢。

留言