调用OCI连接Oracle
Oracle Call Interface简称OCI,是Oracle数据库访问的基础接口。
下面的例子依次执行了:
初始化OCI环境
连接服务器连接数据库
创建会话执行查询
执行新增断开会话
断开服务器释放资源
示例代码:
#include <oci.h>
#include <iostream>
#include <string>
#include <string.h>
#include <stdlib.h>
using namespace std;
//存放查询数据的结构体
struct result
{
char ename[20];
char cname[20];
result()
{
memset(ename, '\0', sizeof(ename));
memset(cname, '\0', sizeof(cname));
}
};
int main()
{
// 初始化 OCI 环境句柄指针
OCIEnv *envhpp = NULL;
// 初始化服务器句柄
OCIServer *servhpp = NULL;
// 用于捕获 OCI 错误信息
OCIError *errhpp = NULL;
// 初始化会话句柄
OCISession *usrhpp = NULL;
// 初始化服务上下文句柄
OCISvcCtx *svchpp = NULL;
// 初始化表达式句柄
OCIStmt *stmthpp = NULL;
string server="mydb";
// 创建 OCI 环境 , 并设置环境句柄。
sword swResult = OCIEnvCreate(&envhpp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);
if (swResult != OCI_SUCCESS && swResult != OCI_SUCCESS_WITH_INFO)
{
cout << "Oracle environment initialization error!" << endl;
exit(1);
}
cout << "Oracle environment initialization success!" << endl;
// 创建错误句柄
OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
// 创建服务句柄
OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&servhpp, OCI_HTYPE_SERVER, (size_t)0, (dvoid **)0);
// 连接服务器,如果失败则获取错误码
if (OCIServerAttach(servhpp, errhpp, (text *)server.c_str(), strlen(server.c_str()), 0) != OCI_SUCCESS)
{
int errcno;
char errbuf[512] = "";
sb4 errcode;
// 获取错误指针和 OCI 错误代码
OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
errcno = errcode;
cout << "Oracle server attach error:" << errbuf << endl;
OCIHandleFree((dvoid *)envhpp,OCI_HTYPE_ENV);
OCIHandleFree((dvoid *)servhpp,OCI_HTYPE_SERVER);
OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR);
exit(1);
}
cout << "Oracle server attach success!"<< endl;
/***************** 连接数据库 ****************/
string user = "user";
string pas = "passwd";
errhpp = NULL;
// 创建错误句柄
(void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
// 创建服务上下文句柄
(void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&svchpp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **)0);
// 设置属性
(void) OCIAttrSet((dvoid *)svchpp, OCI_HTYPE_SVCCTX, (dvoid *)servhpp, (ub4)0, OCI_ATTR_SERVER, (OCIError *)errhpp);
// 创建用户连接句柄
(void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&usrhpp, (ub4)OCI_HTYPE_SESSION, (size_t) 0, (dvoid **)0);
// 设置用户名、密码
(void) OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)user.c_str(), (ub4)strlen(user.c_str()), (ub4)OCI_ATTR_USERNAME, errhpp);
(void) OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)pas.c_str(), (ub4)strlen(pas.c_str()), (ub4)OCI_ATTR_PASSWORD, errhpp);
// 创建会话连接
if(OCISessionBegin(svchpp, errhpp, usrhpp, OCI_CRED_RDBMS, (ub4)OCI_DEFAULT) != OCI_SUCCESS)
{
int errcno;
char errbuf[512]={'\0'};
sb4 errcode;
// 获取错误指针和 OCI 错误代码
OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
errcno = errcode;
cout << "User session error:" << errbuf << endl;
OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR);
OCIHandleFree((dvoid *)usrhpp,OCI_HTYPE_SESSION);
OCIHandleFree((dvoid *)svchpp,OCI_HTYPE_SVCCTX);
exit(1);
}
cout << "user session success!" << endl;
(void) OCIAttrSet((dvoid *)svchpp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)usrhpp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhpp);
/*************** 执行 查询SQL 语句 ******************/
errhpp = NULL;
// 创建一个表达式句柄
if (OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&stmthpp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0) != OCI_SUCCESS)
{
cout << "Create STMT error !" << endl;
exit(1);
}
cout << "Create stmt success !" << endl;
// 创建错误句柄
OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
// Select语句
char sql[255] = "select col1, col2 from table1 ";
if (OCIStmtPrepare(stmthpp, errhpp, (text *)sql, (ub4)strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT) != OCI_SUCCESS)
{
cout << "Create prepare error!" << sql << endl;
exit(1);
}
cout << "Create prepare success!" << endl;
/********* 绑定参数 ***********/
// 申请绑定字段的句柄
OCIDefine *bhp1 = NULL;
OCIDefine *bhp2 = NULL;
// 存放数据的结构
struct result rst;
// 指定提取数据长度
ub2 datalen = 0;
// 定义指示器变量 , 用于取可能存在空值的字段
char isnul[6] = "";
// 定义输出变量 ,
OCIDefineByPos(stmthpp, &bhp1, errhpp, 1, (dvoid *)&rst.ename, sizeof(rst.ename), SQLT_CHR, NULL, &datalen, NULL, OCI_DEFAULT);
OCIDefineByPos(stmthpp, &bhp2, errhpp, 2, (dvoid *)&rst.cname, sizeof(rst.cname), SQLT_STR, NULL, &datalen, NULL, OCI_DEFAULT);
// 获取 SQL 语句类型
ub2 stmt_type;
OCIAttrGet ((dvoid *)stmthpp, (ub4)OCI_HTYPE_STMT, (dvoid *)&stmt_type, (ub4 *)0, (ub4)OCI_ATTR_STMT_TYPE, errhpp);
// 执行 SQL 语句
OCIStmtExecute(svchpp, stmthpp, errhpp, (ub4)0, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);
// 获取查询信息
int rows_fetched;
do
{
cerr << rst.ename<< " ";
cerr << rst.cname<< " \n";
}
while(OCIStmtFetch2(stmthpp, errhpp, 1, OCI_FETCH_NEXT, 1, OCI_DEFAULT) != OCI_NO_DATA);
// 获得记录条数
OCIAttrGet((CONST void *)stmthpp, OCI_HTYPE_STMT, (void *)&rows_fetched, (ub4 *)sizeof(rows_fetched), OCI_ATTR_ROW_COUNT, errhpp);
cout << " rows :" << rows_fetched << endl;
/*************** 执行 新增SQL 语句 ******************/
if (OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&stmthpp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0) != OCI_SUCCESS)
{
cout << "Create STMT error !" << endl;
exit(1);
}
cout << "Create stmt success !" << endl;
OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
// Insert语句
char sql2[255] = "insert into table1 (col1, col2) values('testoci', 'testoci')";
// 准备Sql语句
if (OCIStmtPrepare(stmthpp, errhpp, (text *)sql2, (ub4)strlen(sql2), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT) != OCI_SUCCESS)
{
cout << "Create prepare error!" << sql2 << endl;
exit(1);
}
cout << "Create prepare success!" << endl;
// 执行SQL 语句
OCIStmtExecute(svchpp, stmthpp, errhpp, (ub4)1, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);
// 断开用户会话
OCILogoff(svchpp, errhpp);
// 断开服务器连接
OCIServerDetach(servhpp, errhpp, OCI_DEFAULT);
// 释放资源
OCIHandleFree((dvoid *) stmthpp, OCI_HTYPE_STMT);
OCIHandleFree((dvoid *) svchpp, OCI_HTYPE_SVCCTX);
OCIHandleFree((dvoid *) servhpp, OCI_HTYPE_SERVER);
OCIHandleFree((dvoid *) errhpp, OCI_HTYPE_ERROR);
return 0;
}
在AIX 5.0环境下编译指令:
xlC -q64 -I/ora10g/app/oracle/product/10.2.0/rdbms/public -L/ora10g/app/oracle/product/10.2.0/lib -lclntsh -o ociTest ociTest.cpp
./ociTest
Oracle environment initialization success!
Oracle server attach success!
user session success!
Create stmt success !
Create prepare success!
col1 col2
rows :1
Create stmt success !
Create prepare success!
调用CLI连接DB2
DB2 Call Level Interface简称CLI ,是DB2数据库访问的基础接口。
下面例子依次执行了:
连接DB2数据库
创建操作句柄
查询操作
新增操作
断开连接
释放资源
示例代码:
#include <stdio.h>
#include <stdlib.h>
#include <sqlcli.h>
#include <sqlcli1.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <sqlca.h>
#include <string.h>
using namespace std;
struct result
{
char ename[50];
char cname[100];
int ename_len;
int cname_len;
result()
{
memset(ename, '\0', sizeof(ename));
memset(cname, '\0', sizeof(cname));
}
};
int main()
{
SQLRETURN cliRC = SQL_SUCCESS;
SQLHANDLE henv = SQL_NULL_HENV;
SQLHANDLE hdbc = SQL_NULL_HDBC;
SQLHANDLE hstmt = SQL_NULL_HSTMT;
//获取环境句柄
cliRC = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
//获取连接句柄
cliRC = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
cliRC = SQLSetConnectAttr(hdbc,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_OFF,
SQL_IS_INTEGER);
cliRC = SQLConnect(hdbc,
(SQLCHAR *)"mydb",
SQL_NTS,
(SQLCHAR *)"user",
SQL_NTS,
(SQLCHAR *)"passwd",
SQL_NTS);
if (cliRC != SQL_SUCCESS)
{
cout<<"connect fail"<<endl;
}
//设置连接
cliRC = SQLSetConnection(hdbc);
//获取操作句柄
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
cliRC = SQLSetStmtAttr(hstmt,
SQL_ATTR_DEFERRED_PREPARE,
(SQLPOINTER)SQL_DEFERRED_PREPARE_ON,
SQL_IS_INTEGER);
cliRC = SQLSetStmtAttr(hstmt, SQL_ATTR_BLOCK_FOR_NROWS, (SQLPOINTER)100, SQL_IS_INTEGER);
if (cliRC == SQL_SUCCESS)
{
cout<<"setStmtAttr"<<endl;
}
//查询语句
const char * strSQL = "select col1, col2 from table1 ";
cliRC = SQLExecDirect(hstmt, (SQLCHAR*)strSQL, SQL_NTS);
if (cliRC == SQL_SUCCESS)
{
cout<<"SQL execute successfully"<<endl;
}
SQLSMALLINT ulColumns = 0;
//获取列数
cliRC = SQLNumResultCols(hstmt, &ulColumns);
if (cliRC == SQL_SUCCESS)
{
cout<<"SQLNumResultCols num="<< ulColumns<<endl;
}
// SQLCHAR szName[50] = "";
// SQLSMALLINT cbNameLen = 0;
// SQLSMALLINT iType = 0;
// SQLUINTEGER ulColumnSize = 0;
// SQLSMALLINT iScale = 0;
// cliRC = SQLDescribeCol(hstmt, (SQLSMALLINT)(0 + 1), szName, 32, &cbNameLen, &iType, &ulColumnSize, &iScale, NULL);
// if (cliRC == SQL_SUCCESS)
// {
// printf("SQLDescribeCol ulColumnSize=[%d]\n", ulColumnSize);
// }
//获取查询结果
SQLPOINTER rgbValue;
SQLINTEGER aaa = 0;
SQLINTEGER *pcbValue = &aaa;
char temp[100] = "";
rgbValue = temp;
struct result rst;
cliRC = SQLBindCol(hstmt, 1, SQL_C_CHAR, rst.ename, sizeof(rst.ename), &rst.ename_len);
cliRC = SQLBindCol(hstmt, 2, SQL_C_CHAR, rst.cname, sizeof(rst.cname), &rst.cname_len);
while(SQLFetch(hstmt) != SQL_NO_DATA_FOUND)
{
cout<<"ename="<<rst.ename<<", cname="<< rst.cname<<endl;
}
//************************************INSERT 操作 ***************************************
const char *insert = " insert into table1 (col1, col2) values('testcli', 'cli')";
cliRC = SQLExecDirect(hstmt, (SQLCHAR*)insert, SQL_NTS);
if (cliRC != SQL_SUCCESS)
{
printf("exec fail \n");
}
//成功操作行数
SQLLEN ulRowEffected = 0;
cliRC = SQLRowCount(hstmt, &ulRowEffected);
cout<<"ulRowEffected = "<<ulRowEffected<<endl;
//结束会话
cliRC = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
//释放资源
cliRC = SQLFreeStmt(hstmt, SQL_UNBIND);
cliRC = SQLFreeStmt(hstmt, SQL_CLOSE);
return 0;
}
在AIX5.0环境下编译:
xlc -q64 -I${DB2_HOME}/include -L${DB2_HOME}/lib -ldb2 -lpthread -o cliTest cliTest.cpp
使用OleDB连接SQL Server
下面例子依次调用了:
初始化环境
连接SQL Server
执行查询
关闭连接
示例代码:
#include "stdafx.h"
#include <windows.h>
#include <sys\timeb.h>
#include <time.h>
#include <stdio.h>
#include <iostream>
#include <map>
#include <atldbcli.h>
#include <atlconv.h>
using namespace std;
typedef struct result
{
char ename[19];
char cname[100];
DBSTATUS dwEnameStatus;
DBSTATUS dwCnameStatus;
} RESULT;
int _tmain(int argc, _TCHAR* argv[])
{
HRESULT hr;
CDataSource m_ds;
CSession m_session;
//连接串指定数据库,用户名,密码
char szConnectionString[512] = "Provider=SQLNCLI10.1;Data Source=10.2.0.10;Initial Catalog=MYDB;User ID=user;Password=passwd;Application Name=SQLSERVER;MARS Connection=True";
::CoInitialize(NULL);
//连接数据库
hr = m_ds.OpenFromInitializationString(CA2W(szConnectionString));
//打开会话
hr = m_session.Open(m_ds);
DBORDINAL ulColumns = 0;
CDBPropSet dbPropSet(DBPROPSET_ROWSET);
CCommand<CManualAccessor, CRowset, CNoMultipleResults> m_command;
hr = m_command.CreateCommand(m_session);
CComPtr<ICommandText> spCommandText;
hr = m_command.m_spCommand->QueryInterface(&spCommandText);
USES_CONVERSION;
//查询语句
const TCHAR *cmd = _T(" select col1, col2 from table1 ");
hr = spCommandText->SetCommandText(DBGUID_SQL, T2COLE(cmd));
dbPropSet.AddProperty(DBPROP_ISequentialStream, true);
hr = m_command.Open(&dbPropSet, NULL, false);
DBCOLUMNINFO *m_pColumnInfo;
LPOLESTR m_pStrings;
//获取列信息
hr = m_command.GetColumnInfo(&ulColumns
, &m_pColumnInfo
, &m_pStrings);
BYTE * m_pRowInfoBuff;
//初始化列绑定字段
DBCOUNTITEM cbRowSize = (m_pColumnInfo[0].ulColumnSize + 1) * sizeof(CHAR);
DBCOUNTITEM cbRowSize2 = (m_pColumnInfo[1].ulColumnSize + 1) * sizeof(CHAR);
RESULT rst;
int bufferSize = sizeof(rst.ename) + sizeof(rst.cname) +1;
m_pRowInfoBuff = new BYTE[bufferSize];
hr = m_command.CreateAccessor(ulColumns, m_pRowInfoBuff, (DBLENGTH)bufferSize);
//DBORDINAL* pLength = (DBORDINAL*)(m_pRowInfoBuff + cbRowSize);
//DBSTATUS* pStatus = (DBSTATUS*)(m_pRowInfoBuff + cbRowSize);
DBTYPE dbType=DBTYPE_STR;
DBLENGTH dbLength = (m_pColumnInfo[0].ulColumnSize + 1) * sizeof(TCHAR);
DBLENGTH dbLength2 = (m_pColumnInfo[1].ulColumnSize + 1) * sizeof(TCHAR);
//m_command.AddBindEntry(1, dbType, dbLength, pValue, pLength, pStatus);
m_command.AddBindEntry(1, dbType, dbLength, rst.ename, NULL, &rst.dwEnameStatus);
m_command.AddBindEntry(2, dbType, dbLength2, rst.cname, NULL, &rst.dwCnameStatus);
hr = m_command.Bind();
//获取查询信息
while(S_OK == m_command.MoveNext())
{
printf(" ename = [%s] ,cname = [%s]\n", rst.ename, rst.cname);
}
//关闭连接
m_command.Close();
m_session.Close();
m_ds.Close();
::CoUninitialize();
::getchar();
}