TimesTen内存数据库的配置与OCI调用 最近初步接触了下TimesTen,对使用作了些记录 一。安装及使用 1. 安装TimesTen服务器 使用root用户创建目录及用户 bi0001:~ # mkdir /etc/TimesTen bi0001:~ # touch /etc/TimesTen/instance_info bi0001:~ # groupadd timesten bi0001:~ # groupadd ttadmin bi0001:~ # bi0001:~ # useradd -g ttadmin -m -d /ttadm -s /bin/ksh ttadm bi0001:~ # bi0001:~ # passwd ttadm Changing password for ttadm. New Password: bi0001:~ # chgrp -R ttadmin /etc/TimesTen bi0001:~ # chmod 775 /etc/TimesTen bi0001:~ # chmod 664 /etc/TimesTen/* bi0001:~ # bi0001:~ # chown -R ttadm:timesten /ttadm 使用新建的用户进入安装目录运行./setup.sh ,根据提示进行设置 选择 quickstart 获得示例代码 这个例子中的安装路径为/ttadm/TimesTen/tt1121 在info/sys.odbc.ini 中可以看到新建数据库的配置 [TT_1121] Driver=/ttadm/TimesTen/tt1121/lib/libtten.so DataStore=/ttadm/TimesTen/tt1121/info/TT_1121 DatabaseCharacterSet=US7ASCII 环境变量PATH添加/ttadm/TimesTen/tt1121/bin 后,就可以使用命令行进行操作了 重启数据库 ttDaemonAdmin –restart ttisql "dsn=tt_1121;" 创建用户 Command> create user test identified by test; User created. Command> grant create session, create any table to test; 接着就可以使用新的用户进行登录操作了 ttisql "DSN=TT_1121;uid=test;pwd=test" 2. 在另一台机器上安装客户端 过程和安装服务端类似 连接服务器配置 在info/sys.odbc.ini 中添加 [ODBC Data Sources] client0=TimesTen 11.2.1 Client Driver [client0] TTC_SERVER=tthost0 TTC_SERVER_DSN=TT_1121 在sys.ttconnect.ini 中添加 [tthost0] Description=TimesTen Server Network_Address=192.168.111.102 TCP_PORT=53389 Network_Address 为服务端ip TCP_PORT 为服务端端口,可在服务端使用ttstatus进行查看 设置好后,就可以在客户端进行连接 ttisqlcs "dsn=client0;uid=test" Command> tables; TEST.NAMEID TEST.STUDENT 2 tables found. Command> select * from student; < 1, HanMm > 1 row found. 二。ODBC连接例子 在客户端进行连接 #include <sql.h> #include <sqlext.h> #include <string> #include <iostream> using namespace std; #ifndef NULL #define NULL 0 #endif #define MAX_NAME_LEN 50 #define MAX_STMT_LEN 100 #define MAX_MSG_LNG 512 class TTODBCTest { private: HENV henv; HDBC hdbc; HSTMT hstmt; SDWORD id; char name[MAX_NAME_LEN + 1]; char create[MAX_STMT_LEN]; char insert[MAX_STMT_LEN]; char select[MAX_STMT_LEN]; SQLLEN namelen; RETCODE rc; SQLCHAR szErrorMsg[MAX_MSG_LNG]; SQLSMALLINT pcbErrorMsg; SQLCHAR szSqlState[MAX_MSG_LNG]; SQLINTEGER pfNativeError; public: int print_err( HSTMT stmt ); int example1( const char* server, const char * uid, const char * pwd); }; int TTODBCTest::print_err( HSTMT stmt ) { SQLError( this->henv, this->hdbc, stmt, szSqlState, &pfNativeError, szErrorMsg, MAX_MSG_LNG, &pcbErrorMsg ); cout << szErrorMsg << endl; return 0; } int TTODBCTest::example1( const char* server, const char * uid, const char* pwd) { /* EXEC SQL CONNECT TO :server USER :uid USING :pwd; */ /* Allocate an environment handle. */ /* Allocate a connection handle. */ /* Connect to a data source. */ /* Allocate a statement handle. */ SQLAllocEnv(&henv); SQLAllocConnect(henv, &hdbc); rc = SQLConnect( hdbc, (SQLCHAR*)server, SQL_NTS, (SQLCHAR*)uid, SQL_NTS, (SQLCHAR*)pwd, SQL_NTS); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(print_err( SQL_NULL_HSTMT)); SQLAllocStmt(hdbc, &hstmt); /* EXEC SQL CREATE TABLE NAMEID (ID integer, NAME varchar(50)); */ /* Execute the SQL statement. */ strcpy( (char*)create, "CREATE TABLE NAMEID (ID INTEGER, NAME VARCHAR(50))"); rc = SQLExecDirect(hstmt, (SQLCHAR*)create, SQL_NTS); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) return(print_err( hstmt)); /* EXEC SQL COMMIT WORK; */ /* Commit the table creation. */ /* Note that the default transaction mode for drivers that support */ /* SQLSetConnectOption is auto-commit and SQLTransact has no effect SQLTransact(hdbc, SQL_COMMIT); /* EXEC SQL INSERT INTO NAMEID VALUES ( :id, :name ); */ /* Show the use of the SQLPrepare/SQLExecute method: */ /* Prepare the insertion and bind parameters. */ /* Assign parameter values. */ /* Execute the insertion. */ strcpy( (char*)insert, "INSERT INTO NAMEID VALUES (?, ?)"); if (SQLPrepare(hstmt, (SQLCHAR*)insert, SQL_NTS) != SQL_SUCCESS) return(print_err( hstmt)); SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &id, 0, NULL); SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, MAX_NAME_LEN, 0, name, 0, NULL); id=500; strcpy( (char*)name, "Babbage"); if (SQLExecute(hstmt) != SQL_SUCCESS) return(print_err( hstmt)); /* EXEC SQL COMMIT WORK; */ /* Commit the insertion. */ SQLTransact(hdbc, SQL_COMMIT, SQL_COMMIT ); /* EXEC SQL DECLARE c1 CURSOR FOR SELECT ID, NAME FROM NAMEID; * /* EXEC SQL OPEN c1; */ /* Show the use of the SQLExecDirect method. */ /* Execute the selection. */ /* Note that the application does not declare a cursor. */ strcpy( (char*)select, "SELECT ID, NAME FROM NAMEID"); if (SQLExecDirect(hstmt, (SQLCHAR*)select, SQL_NTS) != SQL_SUCCESS) return(print_err( hstmt)); /* EXEC SQL FETCH c1 INTO :id, :name; */ /* Bind the columns of the result set with SQLBindCol. */ /* Fetch the first row. */ SQLBindCol(hstmt, 1, SQL_C_SLONG, &id, 0, NULL); SQLBindCol(hstmt, 2, SQL_C_CHAR, name, (SQLLEN)sizeof(name), &namelen); SQLFetch(hstmt); /* EXEC SQL COMMIT WORK; */ /* Commit the transaction. */ SQLTransact(hdbc, SQL_COMMIT, SQL_COMMIT ); /* EXEC SQL CLOSE c1; */ /* Free the statement handle. */ SQLFreeStmt(hstmt, SQL_DROP); /* EXEC SQL DISCONNECT; */ /* Disconnect from the data source. */ /* Free the connection handle. */ /* Free the environment handle. */ SQLDisconnect(hdbc); SQLFreeConnect(hdbc); SQLFreeEnv(henv); return(0); } int main() { TTODBCTest tt; tt.example1( "client0", "test", "test" ); return 0; } 编译 g++ -I/ttadm/TimesTen/tt1121/include -L/ttadm/TimesTen/tt1121/lib -lttclient -lgcc_s ins.cpp 如果在服务端进行连接 tt.example1( "client0", "test", "test" ); 中client0 修改为服务端的DSN 编译稍有不同 g++ -I/ttadm/TimesTen/tt1121/include -L/ttadm/TimesTen/tt1121/lib -ltten -lgcc_s ins.cpp 三。 OCI连接 TimsTen 11.2 版本中支持Oci的方法调用 Oci的连接中,程序到$ORACLE_HOME/network/admin 中根据给定的tnsname 在文件tnsnames.ora中查找相应的配置,与sqlplus 连接相似 TimesTen兼容Oci的部分常用方法,也使用了类似的方式进行连接处理 这里需要指定一个环境变量$TNS_ADMIN ,TimesTen 到该路径中的tnsnames.ora中查找相应的配置,可以将该变量指定到默认的路径 /ttadm/TimesTen/tt1121/network/admin/samples 中 配置格式为 # The Timesten Client entries follow the pattern of: # <NAME> =(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = <NAME> )(SERVER = timesten_client))) #服务端的配置 sampledb_1121 =(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = sampledb_1121)(SERVER = timesten_direct))) #客户端的配置 testct=(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = client0)(SERVER = timesten_client))) SERVICE_NAME 即为在sys.odbc.ini 文件中的DSN配置 可以使用/ttadm/TimesTen/tt1121/quickstart/sample_code/oci 中自带的程序进行测试 查看 README_oci.txt 说明,进行相应的设置 设置环境 ttadm@bi0001:/ttadm/TimesTen/tt1121/quickstart> . ./ttquickstartenv.sh NOTE: TNS_ADMIN is already set in environment - /ttadm/TimesTen/tt1121/network/admin/samples LD_LIBRARY_PATH set to /ttadm/TimesTen/tt1121/lib:/ttadm/TimesTen/tt1121/ttoracle_home/instantclient_11_1:/oracle/app/oracle/product/11.1.0/db_1/lib make 编译后,检查LD_LIBRARY_PATH 这个环境变量的设置,动态库应该指向最新的版本,因为安装低版本Oracle数据库自带的动态库版本也会较低, 在使用的时候会出错。 配置好后,就会发现使用oci的程序连接TimesTen和连接Oracle没有多少区别,同一个程序连接TimesTen和连接Oracle完全一致,不用作任何修改。