附录C：ACI示例¶
数据库连接¶
单用户连接¶
ACILogon：
#include <iostream>
#include <aci.h>
int main()
{
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
sword r = ACI_SUCCESS;
char *m_dblink = (char *)"localhost:2003/osrdb";
char *m_dbuser = (char *)"sysdba";
char *m_dbpwd = (char *)"szoscar55";
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
r = ACILogoff(m_psvc, m_perr);
}
ACILogon2：
#include <iostream>
#include <aci.h>
#define CPOOL_CONN_MIN 10
#define CPOOL_CONN_MAX 50
#define CPOOL_CONN_INCR 1
char *dblink = (char *) "localhost:2003/osrdb";
char *dbuser = (char *) "SYSDBA";
char *dbpwd = (char *)"szoscar55";
/*本例中CPOOL_CONN_MIN、CPOOL_CONN_MAX和CPOOL_CONN_INCR
* 均为宏定义的最小、最大、连接增长数
*/
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACICPool *m_pcpool = NULL;/*连接池句柄*/
ACIAuthInfo *m_pauthhp = NULL;/*认证信息句柄*/
ACICPool *cpool;
OraText *poolName;
sb4 poolNameLen;
int main()
{
sword r = ACI_SUCCESS;
//初始化环境句柄
r = ACIEnvCreate((ACIEnv **)&m_penv, (ub4)ACI_DEFAULT, 0, 0, 0, 0,
0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0,
0);
r = ACIHandleAlloc(m_penv, (void**)&m_pcpool, ACI_HTYPE_CPOOL, 0,
0);
r = ACIHandleAlloc(m_penv, (void**)& m_pauthhp,
ACI_HTYPE_AUTHINFO, 0, 0);
//建立连接池
r = ACIConnectionPoolCreate(m_penv, m_perr, m_pcpool, (OraText
**)&poolName, &poolNameLen, (text *)dblink,
(sb4)strlen(dblink), CPOOL_CONN_MIN, CPOOL_CONN_MAX,
CPOOL_CONN_INCR, (OraText *)dbuser, (sb4)strlen(dbuser),
(OraText *)dbpwd, (sb4)strlen(dbpwd), ACI_DEFAULT);
//连接数据库
r = ACILogon2(m_penv, m_perr, &m_psvc, (OraText *)dbuser, (sb4)strlen(dbuser),
(OraText *)dbpwd, (sb4)strlen(dbpwd), poolName, poolNameLen, ACI_LOGON2_CPOOL);
//断开连接
r = ACILogoff(m_psvc, m_perr);
//销毁连接池
r = ACIConnectionPoolDestroy(m_pcpool, m_perr, ACI_DEFAULT);
//释放各类句柄
r = ACIHandleFree(m_pauthhp, ACI_HTYPE_AUTHINFO);
r = ACIHandleFree(m_perr, ACI_HTYPE_ERROR);
r = ACIHandleFree(m_pcpool, ACI_HTYPE_CPOOL);
r = ACIHandleFree(m_penv, ACI_HTYPE_ENV);
}
多用户连接¶
#include <iostream>
#include <stdlib.h>
#include"aci.h"
using namespace std;
ACIEnv* env = NULL;
ACIError* err = NULL;
ACIServer* srv = NULL;
ACISvcCtx* svc = NULL;
ACISession* ses = NULL;
char *dblink = (char *) "localhost:2003/osrdb";
char *dbuser = (char *) "sysdba";
char *dbpwd = (char *)"szoscar55";
int main(int args, char *argv[])
{
sword r = 0;
ACIServer *srv1 = NULL;
ACIError *err1 = NULL;
ACISvcCtx *svc1 = NULL;
ACIStmt *stmt1 = NULL;
ACIServer *srv2 = NULL;
ACIError *err2 = NULL;
ACISvcCtx *svc2 = NULL;
ACIStmt *stmt2 = NULL;
ACIEnvCreate((ACIEnv **)&env, (ub4)ACI_DEFAULT, 0, 0, 0, 0, 0, 0);
ACIHandleAlloc(env, (dvoid**)&err, ACI_HTYPE_ERROR, 0, 0);
ACIHandleAlloc(env, (dvoid**)&srv, ACI_HTYPE_SERVER, 0, 0);
ACIHandleAlloc(env, (dvoid**)&svc, ACI_HTYPE_SVCCTX, 0, 0);
ACIHandleAlloc(env, (dvoid**)&ses, ACI_HTYPE_SESSION, 0, 0);
ACIHandleAlloc(env, (dvoid**)&err1, ACI_HTYPE_ERROR, 0, 0);
ACIHandleAlloc(env, (dvoid**)&srv1, ACI_HTYPE_SERVER, 0, 0);
ACIHandleAlloc(env, (dvoid**)&svc1, ACI_HTYPE_SVCCTX, 0, 0);
ACIHandleAlloc(env, (dvoid**)&err2, ACI_HTYPE_ERROR, 0, 0);
ACIHandleAlloc(env, (dvoid**)&srv2, ACI_HTYPE_SERVER, 0, 0);
ACIHandleAlloc(env, (dvoid**)&svc2, ACI_HTYPE_SVCCTX, 0, 0);
ACIServerAttach(srv, err, (text*)dblink, (sb4)strlen(dblink), (ub4)ACI_DEFAULT);
ACIServerAttach(srv1, err, (text*)dblink, (sb4)strlen(dblink), (ub4)ACI_DEFAULT);
ACIServerAttach(srv2, err, (text*)dblink, (sb4)strlen(dblink), (ub4)ACI_DEFAULT);
//! 设置服务上下文的服务器属性
ACIAttrSet(svc, ACI_HTYPE_SVCCTX, srv, sizeof(srv), ACI_ATTR_SERVER, err);
ACIAttrSet(svc1, ACI_HTYPE_SVCCTX, srv1, sizeof(srv1), ACI_ATTR_SERVER, err1);
ACIAttrSet(svc2, ACI_HTYPE_SVCCTX, srv2, sizeof(srv2), ACI_ATTR_SERVER, err2);
//! 设置用户会话的用户名和密码属性
ACIAttrSet(ses, ACI_HTYPE_SESSION, dbuser, (sb4)strlen(dbuser), ACI_ATTR_USERNAME, err);
ACIAttrSet(ses, ACI_HTYPE_SESSION, dbpwd, (sb4)strlen(dbpwd), ACI_ATTR_PASSWORD, err);
r = ACISessionBegin(svc, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
r = ACISessionBegin(svc1, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
r = ACISessionBegin(svc2, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
r = ACISessionEnd(svc, err, ses, ACI_DEFAULT);
r = ACISessionEnd(svc1, err, ses, ACI_DEFAULT);
r = ACISessionEnd(svc2, err, ses, ACI_DEFAULT);
}
连接池连接¶
#include <iostream>
#include <aci.h>
#include <stdlib.h>
#define CPOOL_CONN_MIN 10
#define CPOOL_CONN_MAX 50
#define CPOOL_CONN_INCR 1
using namespace std;
char *dblink = (char *) "localhost:2003/osrdb";
char *dbuser = (char *) "SYSDBA";
char *dbpwd = (char *)"szoscar55";
/*本例中CPOOL_CONN_MIN、CPOOL_CONN_MAX和CPOOL_CONN_INCR
* 均为宏定义的最小、最大、连接增长数
*/
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACICPool *m_pcpool = NULL;/*连接池句柄*/
ACIAuthInfo *m_pauthhp = NULL;/*认证信息句柄*/
ACICPool *cpool;
OraText *poolName;
sb4 poolNameLen;
int main()
{
sword r = ACI_SUCCESS;
//初始化环境句柄
r = ACIEnvCreate((ACIEnv **)&m_penv, (ub4)ACI_DEFAULT, 0, 0, 0, 0,
0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0,
0);
r = ACIHandleAlloc(m_penv, (void**)&m_pcpool, ACI_HTYPE_CPOOL, 0,
0);
r = ACIHandleAlloc(m_penv, (void**)& m_pauthhp,
ACI_HTYPE_AUTHINFO, 0, 0);
//建立连接池
r = ACIConnectionPoolCreate(m_penv, m_perr, m_pcpool, (OraText
**)&poolName, &poolNameLen, (text *)dblink,
(sb4)strlen(dblink), CPOOL_CONN_MIN, CPOOL_CONN_MAX,
CPOOL_CONN_INCR, (OraText *)dbuser, (sb4)strlen(dbuser),
(OraText *)dbpwd, (sb4)strlen(dbpwd), ACI_DEFAULT);
//连接数据库
r = ACIAttrSet((dvoid *)m_pauthhp, ACI_HTYPE_AUTHINFO, dbuser,
strlen(dbuser), ACI_ATTR_USERNAME, m_perr);
r = ACIAttrSet((dvoid *)m_pauthhp, ACI_HTYPE_AUTHINFO, dbpwd,
strlen(dbpwd), ACI_ATTR_PASSWORD, m_perr);
r = ACISessionGet(m_penv, m_perr, &m_psvc, m_pauthhp, poolName,
poolNameLen, NULL, 0, NULL, NULL, NULL, ACI_SESSGET_CPOOL);
//断开连接
r = ACISessionRelease(m_psvc, m_perr, NULL, 0, ACI_DEFAULT);
//销毁连接池
r = ACIConnectionPoolDestroy(m_pcpool, m_perr, ACI_DEFAULT);
//释放各类句柄
r = ACIHandleFree(m_pauthhp, ACI_HTYPE_AUTHINFO);
r = ACIHandleFree(m_perr, ACI_HTYPE_ERROR);
r = ACIHandleFree(m_pcpool, ACI_HTYPE_CPOOL);
r = ACIHandleFree(m_penv, ACI_HTYPE_ENV);
}
服务名访问¶
#include <iostream>
#include <aci.h>
int main()
{
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
sword r = ACI_SUCCESS;
//在ShenTong\admin目录下的tnsnames.aci文件中添加acidb= localhost:2003/osrdb
char *m_dblink = (char *)"acidb";
char *m_dbuser = (char *)"sysdba";
char *m_dbpwd = (char *)"szoscar55";
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
r = ACILogoff(m_psvc, m_perr);
}
空串连接¶
#include <iostream>
#include <aci.h>
int main()
{
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
sword r = ACI_SUCCESS;
char *m_dblink = (char *)"";
char *m_dbuser = (char *)"sysdba";
char *m_dbpwd = (char *)"szoscar55";
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
r = ACILogoff(m_psvc, m_perr);
}
参数的绑定¶
普通绑定:
#include <iostream>
#include <aci.h>
using namespace std;
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACISession *m_pses = NULL;/*会话句柄*/
ACIStmt *m_pstmt = NULL;/*语句句柄*/
ACIBind *m_bnd = NULL;/*绑定句柄*/
ACIDefine *m_def = NULL;/*定义句柄*/
typedef struct { int sID; char sCol1[30]; } Record;
int main()
{
sword r = ACI_SUCCESS;
int id = 0;
//根据位置定义输出变量
ub2 *rlp = NULL, *rcodep = NULL;
Record rec = { 1,2 };
char ssql[] = "select * from testtb where id=:id";/////
char *m_dblink = (char *)"127.0.0.1:2003/OSRDB";
char *m_dbuser = (char *)"SYSDBA";
char *m_dbpwd = (char *)"szoscar55";
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
//准备SQL语句
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)ssql, strlen(ssql), ACI_HTYPE_ERROR, ACI_DEFAULT);
id = 1;//选择id=1的记录
//按位置进行绑定
r = ACIBindByPos(m_pstmt, &m_bnd, m_perr, 1, (void *)&id, sizeof(id),
SQLT_INT, (void*)0, (ub2*)0, (ub2*)0, 0, 0, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &m_def, m_perr, 1, (void*)&rec.sID, sizeof(rec.sID),
SQLT_INT, (void*)0, (ub2*)rlp, (ub2*)rcodep, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &m_def, m_perr, 2, (void *)rec.sCol1,
sizeof(rec.sCol1), SQLT_STR, (void*)0, (ub2*)rlp, (ub2*)rcodep,
ACI_DEFAULT);
//执行SQL语句
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 0, 0, 0, 0, ACI_DEFAULT);
while (((r = ACIStmtFetch(m_pstmt, m_perr, 1, ACI_FETCH_NEXT, ACI_DEFAULT)) != ACI_NO_DATA))
{
printf("%d, %s \n", rec.sID, rec.sCol1);
}
}
占位符有双引号的绑定:
#include <iostream>
#include <aci.h>
using namespace std;
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACISession *m_pses = NULL;/*会话句柄*/
ACIStmt *m_pstmt = NULL;/*语句句柄*/
ACIBind *m_bnd = NULL;/*绑定句柄*/
ACIDefine *m_def = NULL;/*定义句柄*/
int main()
{
sword r = ACI_SUCCESS;
char dsql[] = "drop table testtb";
char csql[] = "create table testtb(a int,b int,c int)";
char isql[] = "insert into testtb values(1,2,:\"c\")";
char *m_dblink = (char *)"127.0.0.1:2003/osrdb";
char *m_dbuser = (char *)"sysdba";
char *m_dbpwd = (char *)"szoscar55";
int c = 5;
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
r = ACIStmtPrepare(m_pstmt, m_perr, (const OraText *)dsql,
(ub4)strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_COMMIT_ON_SUCCESS);
//建表
r = ACIStmtPrepare(m_pstmt, m_perr, (const OraText *)csql,
(ub4)strlen(csql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_COMMIT_ON_SUCCESS);
//准备SQL语句
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)isql, strlen(isql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIBindByName(m_pstmt, &m_bnd, m_perr, (OraText*)":\"c\"", (sb4)strlen(":\"c\""), (void *)&c, sizeof(c), SQLT_INT, NULL, NULL, NULL, NULL, NULL, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACITransCommit(m_psvc, m_perr, ACI_DEFAULT);
}
数据类型读写¶
Text类型¶
#include <iostream>
#include <aci.h>
#include <string.h>
ACIEnv *m_penv = NULL;
ACIError *m_perr = NULL;
ACIServer *m_psrv = NULL;
ACISvcCtx *m_psvc = NULL;
ACISession *m_pses = NULL;
ACIStmt *m_pstmt = NULL;
ACIBind *m_pbnd = NULL;
ACIDefine *m_pdef = NULL;
int main()
{
sword r = ACI_SUCCESS;
ub1 errbuf[64] = { 0 }; //获取错误信息的缓冲区
ub1 sqlstate[64] = { 0 }; //ACIErroGet()的第三个参数变量
sb4 ercodep = 0;
ub2 *rlp = NULL, rcodep = NULL;
char dsql[] = "drop table tt";
char createSql[] = "create table tt(Profession text)";//创建表
char insql[] = "insert into tt values(:1)";//插入数据
char ssql[] = "select * from tt";//查询数据
char *m_dblink = (char *)"127.0.0.1:2003/OSRDB";
char *m_dbuser = (char *) "SYSDBA";
char *m_dbpwd = (char *)"szoscar55";
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类句柄
ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText*)m_dbuser, strlen(m_dbuser),
(const OraText*)m_dbpwd, strlen(m_dbpwd), (const OraText*)m_dblink, strlen(m_dblink));
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
//创建数据库
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)createSql, strlen(createSql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
//插入数据
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)insql, strlen(insql), ACI_HTYPE_ERROR, ACI_DEFAULT);
char profession[5][11] = { "小品演员","歌手","相声演员","演员、歌手","演员、歌手" };
int indp3[] = { 0,0,0,0,0 };
ub2 alenp3[] = { 9,5,9,11,11 };
r = ACIBindByPos(m_pstmt, &m_pbnd, m_perr, 1, (void*)profession[0], sizeof(profession[0]), SQLT_STR, indp3, alenp3, 0, 0, 0, ACI_DEFAULT);
r = ACIBindArrayOfStruct(m_pbnd, m_perr, sizeof(profession[0]), sizeof(int), sizeof(ub2), 0);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 5/*总共有6行*/, 0, 0, 0, ACI_DEFAULT);
ACITransCommit(m_psvc, m_perr, ACI_DEFAULT);
//查询
char profession3[6][12] = { 0 };
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)ssql, strlen(ssql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &m_pdef, m_perr, 1, (void*)profession3[0], sizeof(profession3[0]), SQLT_STR,
(void*)0, (ub2*)rlp, (ub2*)rcodep, ACI_DEFAULT);
r = ACIDefineArrayOfStruct(m_pdef, m_perr, sizeof(profession3[0]), 0, 0, 0);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 0, 0, 0, 0, ACI_DEFAULT);
while ((r = ACIStmtFetch(m_pstmt, m_perr, 5, ACI_FETCH_NEXT, ACI_DEFAULT)) != ACI_NO_DATA)
{
int i;
for (i = 0; i < 5; i++)
printf("%s\n", profession3[i]);
}
return 0;
}
时间戳类型¶
#include <iostream>
#include <aci.h>
#include <string.h>
ACIEnv *m_penv = NULL;
ACIError *m_perr = NULL;
ACIServer *m_psrv = NULL;
ACISvcCtx *m_psvc = NULL;
ACISession *m_pses = NULL;
ACIStmt *m_pstmt = NULL;
ACIBind *m_pbnd = NULL;
ACIDefine *m_pdef = NULL;
void checkerr(ACIError* err, sword status)
{
text errbuf[512];
ub4 buflen = 0;
sb4 errcode;
switch (status)
{
case ACI_SUCCESS:
break;
case ACI_SUCCESS_WITH_INFO:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - ACI_SUCCESS_WITH_INFO:%s\\n", errbuf);
exit(-1);
break;
case ACI_NEED_DATA:
printf("Error - ACI_NEED_DATA\\n");
exit(-1);
break;
case ACI_NO_DATA:
printf("Error - ACI_NO_DATA\\n");
exit(-1);
break;
case ACI_ERROR:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - %d %s\\n", errcode, errbuf);
exit(-1);
break;
case ACI_INVALID_HANDLE:
printf("Error - ACI_INVALID_HANDLE\\n");
exit(-1);
break;
case ACI_STILL_EXECUTING:
printf("Error - ACI_STILL_EXECUTE\\n");
exit(-1);
break;
case ACI_CONTINUE:
printf("Error - ACI_CONTINUE\\n");
exit(-1);
break;
default:
break;
}
}
int main()
{
sword r = ACI_SUCCESS;
ub1 errbuf[64] = { 0 }; //获取错误信息的缓冲区
ub1 sqlstate[64] = { 0 }; //ACIErroGet()的第三个参数变量
sb4 ercodep = 0;
ub2 *rlp = NULL, rcodep = NULL;
char dsql[] = "drop table tt";
char createSql[] = "create table tt(mytime timestamp)";//创建表
char insql[] = "insert into tt values(:1)";//插入数据
char ssql[] = "select * from tt";//查询数据
char *m_dblink = (char *)"127.0.0.1:2003/OSRDB";
char *m_dbuser = (char *) "SYSDBA";
char *m_dbpwd = (char *)"szoscar55";
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类句柄
ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText*)m_dbuser, strlen(m_dbuser),
(const OraText*)m_dbpwd, strlen(m_dbpwd), (const OraText*)m_dblink, strlen(m_dblink));
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
//创建数据库
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)createSql, strlen(createSql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
//插入数据
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)insql, strlen(insql), ACI_HTYPE_ERROR, ACI_DEFAULT);
checkerr(m_perr, r);
char mytime[5][30] = { "2005-05-13 07:15:31.123456789","2003-02-16 02:14:31.1256789","2005-07-14 07:15:31.123456789","2002-08-13 07:15:31.123456789","2012-05-12 07:15:31.123456789" };
int indp3[] = { 0,0,0,0,0 };
ub2 alenp3[] = { 30,30,30,30,30 };
r = ACIBindByPos(m_pstmt, &m_pbnd, m_perr, 1, (void*)mytime[0], sizeof(mytime[0]), SQLT_STR, indp3, alenp3, 0, 0, 0, ACI_DEFAULT);
checkerr(m_perr, r);
r = ACIBindArrayOfStruct(m_pbnd, m_perr, sizeof(mytime[0]), sizeof(int), sizeof(ub2), 0);
checkerr(m_perr, r);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 5/*总共有6行*/, 0, 0, 0, ACI_DEFAULT);
checkerr(m_perr, r);
ACITransCommit(m_psvc, m_perr, ACI_DEFAULT);
//查询
char mytime3[6][30] = { 0 };
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)ssql, strlen(ssql), ACI_HTYPE_ERROR, ACI_DEFAULT);
checkerr(m_perr, r);
r = ACIDefineByPos(m_pstmt, &m_pdef, m_perr, 1, (void*)mytime3[0], sizeof(mytime3[0]), SQLT_STR,
(void*)0, (ub2*)rlp, (ub2*)rcodep, ACI_DEFAULT);
checkerr(m_perr, r);
r = ACIDefineArrayOfStruct(m_pdef, m_perr, sizeof(mytime3[0]), 0, 0, 0);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 0, 0, 0, 0, ACI_DEFAULT);
checkerr(m_perr, r);
while ((r = ACIStmtFetch(m_pstmt, m_perr, 5, ACI_FETCH_NEXT, ACI_DEFAULT)) != ACI_NO_DATA)
{
if (r != ACI_SUCCESS)
{
ACIErrorGet(m_perr, 1, sqlstate, &ercodep, errbuf, sizeof(errbuf), ACI_HTYPE_ERROR);
printf("错误信息为：%s\n", errbuf);
return 0;
}
int i;
for (i = 0; i < 5; i++)
printf("%s\n", mytime3[i]);
}
return 0;
}
大对象类型¶
插入¶
#include <iostream>
#include <aci.h>
#include <stdlib.h>
using namespace std;
#pragma comment(lib, "./aci.lib")
#define LOB_BUFF_LEN 512 /*要操作的大对象buff长度*/
/*要插入的记录*/
typedef struct tagInsRecord
{
int in_int;
char in_varchar[64];
}InsRecord;
ACIEnv* env = NULL;
ACIError* err = NULL;
ACIServer* srv = NULL;
ACISvcCtx* svc = NULL;
ACISession* ses = NULL;
ACIStmt* stmt = NULL;
ACIBind* pBind[10];
ACIDefine* pDef[10];
char *dblink = (char *) "localhost:2003/osrdb";
char *dbuser = (char *) "SYSDBA";
char *dbpwd = (char *)"szoscar55";
void read_lob();//大对象读操作
void write_getlength_lob();//大对象写操作与获取长度
void insertblobtmp();
void insertblob();
void checkerr(ACIError* err, sword status)
{
text errbuf[512];
ub4 buflen = 0;
sb4 errcode;
switch (status)
{
case ACI_SUCCESS:
break;
case ACI_SUCCESS_WITH_INFO:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - ACI_SUCCESS_WITH_INFO:%s\n", errbuf);
exit(-1);
break;
case ACI_NEED_DATA:
printf("Error - ACI_NEED_DATA\n");
exit(-1);
break;
case ACI_NO_DATA:
printf("Error - ACI_NO_DATA\n");
exit(-1);
break;
case ACI_ERROR:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - %d %s\n", errcode, errbuf);
exit(-1);
break;
case ACI_INVALID_HANDLE:
printf("Error - ACI_INVALID_HANDLE\n");
exit(-1);
break;
case ACI_STILL_EXECUTING:
printf("Error - ACI_STILL_EXECUTE\n");
exit(-1);
break;
case ACI_CONTINUE:
printf("Error - ACI_CONTINUE\n");
exit(-1);
break;
default:
break;
}
}
int main(int args, char *argv[])
{
sword r = 0;
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
checkerr(err, r);
r = ACIEnvInit(&env, ACI_DEFAULT, 0, 0);
checkerr(err, r);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(env, (void**)&err, ACI_HTYPE_ERROR, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&srv, ACI_HTYPE_SERVER, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&svc, ACI_HTYPE_SVCCTX, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&ses, ACI_HTYPE_SESSION, 0, 0);
checkerr(err, r);
//连接数据库
r = ACIServerAttach(srv, err, (const OraText *)dblink, (dblink != NULL ? (sb4)strlen(dblink) : 0), ACI_DEFAULT);
checkerr(err, r);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, srv, sizeof(srv), ACI_ATTR_SERVER, err);
checkerr(err, r);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbuser, (ub4)strlen(dbuser), ACI_ATTR_USERNAME, err);
checkerr(err, r);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbpwd, (ub4)strlen(dbpwd), ACI_ATTR_PASSWORD, err);
checkerr(err, r);
r = ACISessionBegin(svc, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
checkerr(err, r);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, ses, sizeof(ses), ACI_ATTR_SESSION, err);
checkerr(err, r);
//分配语句句柄
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
checkerr(err, r);
//读lob
//read_lob();
//写lob，并获取长度
//write_getlength_lob();
//insertblobtmp();
insertblob();
return 0;
}
void read_lob()
{
char *inCLobSql = (char *)"insert into testtb1(c) values('我是中国人')";
char *selCLobSql = (char *) "select c from testtb1";
ACILobLocator *clob;
char szV[50] = { 0 };
long lFileLen = 0;
int readedlen = 0;
sword r = 0;
//插入clob数据
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
checkerr(err, r);
r = ACIStmtPrepare(stmt, err, (const OraText*)inCLobSql, (ub4)strlen(inCLobSql), ACI_NTV_SYNTAX, ACI_DEFAULT);
checkerr(err, r);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_COMMIT_ON_SUCCESS);
checkerr(err, r);
//获取clob数据
r = ACIDescriptorAlloc(env, (void**)&clob, ACI_DTYPE_LOB, 0, NULL); //clob描述符初始化
checkerr(err, r);
r = ACIStmtPrepare(stmt, err, (const OraText*)selCLobSql, (ub4)strlen(selCLobSql), ACI_NTV_SYNTAX, ACI_DEFAULT);
checkerr(err, r);
r = ACIDefineByPos(stmt, &pDef[3], err, 1, &clob, sizeof(clob), SQLT_CLOB, 0, 0, 0, ACI_DEFAULT);
checkerr(err, r);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_DEFAULT);
checkerr(err, r);
//读取clob数据
ACILobRead(svc, err, clob, (ub4*)&lFileLen, 1, szV, sizeof(szV), 0, 0, 0, 0);
//while (ACILobRead(svc, err, clob, (ub4*)&lFileLen, 1, szV + readedlen, sizeof(szV), 0, 0, 0, 0) == ACI_NEED_DATA)
//{
// readedlen += lFileLen;
// lFileLen = 0;
//}
printf("读取到的clob数据为：%s\n", szV);
r = ACIDescriptorFree(clob, ACI_DTYPE_LOB);
checkerr(err, r);
}
void write_getlength_lob()
{
char *inEmpBlobSql = (char *)"insert into testtb1(id, b) values(1,empty_blob())";
char *updateBlobSql = (char *) "select b from testtb1 where id = 1 for update ";
ACILobLocator *blob;
ub4 lobLen = 0;
char szBuff[LOB_BUFF_LEN] = "我是中国人， 我爱中国";
ub4 buffLen = strlen(szBuff);
sword r = 0;
//插入空blob数据
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
checkerr(err, r);
r = ACIStmtPrepare(stmt, err, (const OraText*)inEmpBlobSql, (ub4)strlen(inEmpBlobSql), ACI_NTV_SYNTAX, ACI_DEFAULT);
checkerr(err, r);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_COMMIT_ON_SUCCESS);
checkerr(err, r);
r = ACIDescriptorAlloc(env, (void**)&blob, ACI_DTYPE_LOB, 0, NULL);//blob描述符初始化
checkerr(err, r);
r = ACIStmtPrepare(stmt, err, (const OraText*)updateBlobSql, (ub4)strlen(updateBlobSql), ACI_NTV_SYNTAX, ACI_DEFAULT);
checkerr(err, r);
r = ACIDefineByPos(stmt, &pDef[4], err, 1, &blob, sizeof(blob), SQLT_BLOB, 0, 0, 0, ACI_DEFAULT);
checkerr(err, r);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_COMMIT_ON_SUCCESS);
checkerr(err, r);
r = ACILobGetLength(svc, err, blob, &lobLen);
checkerr(err, r);
printf("blob的长度为%d\n", lobLen);
r = ACILobWrite(svc, err, blob, &buffLen, 1, szBuff, sizeof(szBuff), ACI_ONE_PIECE, 0, 0, 0, SQLCS_IMPLICIT);
checkerr(err, r);
printf("实际写入的长度为%d\n", buffLen);
r = ACILobGetLength(svc, err, blob, &lobLen);
checkerr(err, r);
printf("写入后通过ACILobGetLength获取到的blob长度为%d\n", lobLen);
}
//第一种，通过临时大对象方式导入
void insertblobtmp()
{
// create table t_blob(a blob);
int i = 0;
sword r = 0;
ACIStmt * stmt = NULL;
ACIBind * pBind = NULL;
ACILobLocator *blob = NULL;
char buf[256];
ub4 amtp = 256;
char dsql[] = "drop table t_blob";
char *createT = (char *)"create table t_blob (a blob)";
char * sqlstrinsert = (char *) "insert into t_blob values(:1)";
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
r = ACIStmtPrepare(stmt, err, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_DEFAULT);
//Create table
r = ACIStmtPrepare(stmt, err, (CONST OraText*)createT,
(ub4)strlen(createT), ACI_NTV_SYNTAX, ACI_DEFAULT);
checkerr(err, r);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
checkerr(err, r);
for (i = 0; i < 256; i++)
{
buf[i] = i;
}
checkerr(err, ACIDescriptorAlloc(env, (dvoid **)&blob, ACI_DTYPE_LOB,
0, 0));
checkerr(err, ACILobCreateTemporary(svc, err, blob, 0, 0,
ACI_TEMP_BLOB, 0, 0));
checkerr(err, ACILobWrite(svc, err, blob, &amtp, 1, buf, sizeof(buf), 0,
0, (ACICallbackLobWrite)0, 0, 0));
checkerr(err, ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0,
0));
checkerr(err, ACIStmtPrepare(stmt, err, (OraText *)sqlstrinsert,
(ub4)strlen(sqlstrinsert), ACI_NTV_SYNTAX, ACI_DEFAULT));
checkerr(err, ACIBindByPos(stmt, &pBind, err, 1, &blob, sizeof(blob), SQLT_BLOB, 0, 0, 0, 0, 0, ACI_DEFAULT));
checkerr(err, ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_DEFAULT));
checkerr(err, ACITransCommit(svc, err, 0));
}
//第二种，通过returning语法导入
void insertblob()
{
// create table t_blob(a blob);
int i = 0;
sword r = 0;
ACIStmt * stmt = NULL;
ACIBind * pBind = NULL;
ACILobLocator *blob = NULL;
char buf[256];
ub4 amtp = 256;
char dsql[] = "drop table t_blob";
char *createT = (char *)"create table t_blob (a blob)";
char * sqlstrinsert = (char *)"insert into t_blob values(empty_blob())returning a into :1";//冒号后没有空格
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
r = ACIStmtPrepare(stmt, err, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_DEFAULT);
//Create table
r = ACIStmtPrepare(stmt, err, (CONST OraText*)createT,
(ub4)strlen(createT), ACI_NTV_SYNTAX, ACI_DEFAULT);
checkerr(err, r);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
checkerr(err, r);
for (i = 0; i < 256; i++)
{
buf[i] = i;
}
checkerr(err, ACIDescriptorAlloc(env, (dvoid **)&blob, ACI_DTYPE_LOB,
0, 0));
checkerr(err, ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0,
0));
checkerr(err, ACIStmtPrepare(stmt, err, (OraText *)sqlstrinsert,
(ub4)strlen(sqlstrinsert), ACI_NTV_SYNTAX, ACI_DEFAULT));
checkerr(err, ACIBindByPos(stmt, &pBind, err, 1, &blob, sizeof(blob), SQLT_BLOB, 0, 0, 0, 0, 0, ACI_DEFAULT));
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_DEFAULT);
checkerr(err, r);
checkerr(err, ACILobWrite(svc, err, blob, &amtp, 1, buf, sizeof(buf), 0,
0, (ACICallbackLobWrite)0, 0, 0));
checkerr(err, ACITransCommit(svc, err, 0));
}
查询¶
普通绑定
#include <iostream>
#include <aci.h>
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACISession *m_pses = NULL;/*会话句柄*/
ACIStmt *m_pstmt = NULL;/*语句句柄*/
ACIBind * m_bnd[3];/*绑定句柄*/
ACIDefine *m_def = NULL;/*定义句柄*/
int main()
{
sword r = ACI_SUCCESS;
char *m_dblink = (char *)"localhost:2003/OSRDB";
char *m_dbuser = (char *)"sysdba";
char *m_dbpwd = (char *)"szoscar55";
char *dsql = (char *)"drop table testtb";
char *csql = (char *)"create table testtb(id int,b blob)";
char *isql = (char *)"insert into testtb values(1,empty_blob())";
text* update1 = (text*)"SELECT b FROM testtb WHERE id=1 FOR UPDATE";
text* select1 = (text*)"SELECT b FROM testtb WHERE id=1";
char szblob[33] = "我是中国人， 我爱中国";
ub4 nLen = sizeof(szblob);
char* szV = NULL;
ub4 len = 0;
oraub8 * limitp = NULL;
ACILobLocator* blob;
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
r = ACIDescriptorAlloc(m_penv, (void **)&blob, ACI_DTYPE_LOB, 0, NULL);
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)csql, strlen(csql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)isql, strlen((char*)isql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)update1, strlen((char*)update1), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &m_def, m_perr, 1, &blob, -1, SQLT_BLOB, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACILobWrite(m_psvc, m_perr, blob, &nLen, 1, szblob, sizeof(szblob), ACI_ONE_PIECE, 0, 0, 0, SQLCS_IMPLICIT);
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)select1, strlen((char*)select1), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &m_def, m_perr, 1, &blob, -1, SQLT_BLOB, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACILobGetLength(m_psvc, m_perr, blob, &len);
szV = new char[len];
r = ACILobRead(m_psvc, m_perr, blob, &nLen, 1, szV, len, ACI_ONE_PIECE, 0, 0, SQLCS_IMPLICIT);
printf("%s\n", szV);
r = ACIDescriptorFree(blob, ACI_DTYPE_LOB);
}
游标类型¶
获得out游标
#include <iostream>
#include <aci.h>
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACISession *m_pses = NULL;/*会话句柄*/
ACIStmt *m_pstmt = NULL;/*语句句柄*/
ACIBind * m_bnd[3];/*绑定句柄*/
ACIDefine *m_def = NULL;/*定义句柄*/
typedef struct { int sID; char sCol1[30]; } Record;
int main()
{
sword r = ACI_SUCCESS;
int aval = 0;
char bval[30] = "";
char *m_dblink = (char *)"localhost:2003/OSRDB";
char *m_dbuser = (char *)"SYSDBA";
char *m_dbpwd = (char *)"szoscar55";
char *dtsql = (char*) "drop table test";
char *dpsql = (char*) "drop procedure proc_test";
char *ctsql = (char*) "create table test(id int)";
char *itsql = (char*) "insert into test values(:id)";
// 游标类型SYS_REFCURSOR和refcursor相同，写为SYS_REFCURSOR是为了兼容oracle
char *cpsql = (char*) "create or replace procedure proc_test(a int, b out int, curs in out SYS_REFCURSOR) is \
begin \
b := a+100; \
open curs for select id from test order by id; \
end;";
char *sql = (char*) "begin proc_test(:a, :b, :curs);end;";
int id1 = 5;
int id2 = 0;
int id3 = 0;
ACIDefine *hDef = NULL;
ub2 *rlp = NULL, *rcodep = NULL;
ACIStmt *mo_pstmt = NULL;
int i = 0;
int id = 5;
ACIBind *pBind;
ub1 sqlstate[64] = { 0 };
sb4 ercodep = 0;
ub1 errbuf[64] = { 0 };
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&mo_pstmt, ACI_HTYPE_STMT, 0, 0);
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
//环境准备
r = ACIStmtPrepare(m_pstmt, m_perr, (CONST OraText*)dtsql, (ub4)strlen(dtsql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (CONST OraText*)dpsql, (ub4)strlen(dpsql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (CONST OraText*)ctsql, (ub4)strlen(ctsql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (CONST OraText*)cpsql, (ub4)strlen(cpsql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
//test表中插入数据
r = ACIStmtPrepare(m_pstmt, m_perr, (CONST OraText*)itsql, (ub4)strlen(itsql), ACI_NTV_SYNTAX, ACI_DEFAULT);
for (i = 0; i < 3; i++)
{
id = i + 1;
r = ACIBindByPos(m_pstmt, &pBind, m_perr, 1, &id, sizeof(id), SQLT_INT, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
}
ACITransCommit(m_psvc, m_perr, ACI_DEFAULT);
//获取存储过程返回信息
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)sql, strlen(sql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIBindByPos(m_pstmt, &m_bnd[0], m_perr, 1, (void *)&id1, sizeof(id1), SQLT_INT, (void*)0, (ub2*)0, (ub2*)0, 0, 0, ACI_DEFAULT);
r = ACIBindByPos(m_pstmt, &m_bnd[1], m_perr, 2, (void *)&id2, sizeof(id2), SQLT_INT, (void*)0, (ub2*)0, (ub2*)0, 0, 0, ACI_DEFAULT);
r = ACIBindByPos(m_pstmt, &m_bnd[2], m_perr, 3, (void *)&mo_pstmt, 0, SQLT_RSET, (void*)0, (ub2*)0, (ub2*)0, 0, 0, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
if (r != ACI_SUCCESS)
{
ACIErrorGet(m_perr, 1, sqlstate, &ercodep, errbuf, sizeof(errbuf), ACI_HTYPE_ERROR);
printf("111错误信息为:%s\n", errbuf);
return 0;
}
i = 0;
r = ACIDefineByPos(mo_pstmt, &hDef, m_perr, 1, &id3, sizeof(id3), SQLT_INT, NULL, NULL, NULL, ACI_DEFAULT);
r = ACIStmtFetch(mo_pstmt, m_perr, 1, ACI_FETCH_NEXT, ACI_DEFAULT);
if (r != ACI_SUCCESS)
{
ACIErrorGet(m_perr, 1, sqlstate, &ercodep, errbuf, sizeof(errbuf), ACI_HTYPE_ERROR);
printf("错误信息为:%s\n", errbuf);
return 0;
}
//清理环境
r = ACIStmtPrepare(m_pstmt, m_perr, (CONST OraText*)dtsql, (ub4)strlen(dtsql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (CONST OraText*)dpsql, (ub4)strlen(dpsql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACISessionEnd(m_psvc, m_perr, m_pses, ACI_DEFAULT);
}
Raw类型¶
#include <iostream>
#include <aci.h>
#define SUMVALUE 100 //插入数据量
using namespace std;
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACISession *m_pses = NULL;/*会话句柄*/
ACIStmt *m_pstmt = NULL;/*语句句柄*/
ACIBind *m_bnd[3];/*绑定句柄*/
ACIDefine *m_def = NULL;/*定义句柄*/
typedef struct { char x[SUMVALUE][20]; char y[SUMVALUE][20]; char z[SUMVALUE][20]; } Record;
int main()
{
sword r = ACI_SUCCESS;
int i;
ub2 indp1[SUMVALUE] = { 0 };
Record rec = { 1,2,3 };
char dsql[] = "drop table testtb";
char csql[] = "create table testtb(a raw(30))";
char isql[] = "insert into testtb values ('abd')";
char ssql[] = "select * from testtb";/////
char *m_dblink = (char *)"localhost:2003/OSRDB";
char *m_dbuser = (char *)"SYSDBA";
char *m_dbpwd = (char *)"szoscar55";
ACIDefine *def1[3];
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)csql, strlen(csql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)isql, (ub4)strlen(isql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 2, 0, NULL, NULL, ACI_DEFAULT);
ACITransCommit(m_psvc, m_perr, ACI_DEFAULT);
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)ssql, (ub4)strlen(ssql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &def1[0], m_perr, 1, (void *)rec.x, sizeof(rec.x), SQLT_STR, (void*)indp1, 0, 0, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtFetch(m_pstmt, m_perr, 1, ACI_FETCH_NEXT, ACI_DEFAULT);
printf("x=%x\n", rec.x[1]);
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACISessionEnd(m_psvc, m_perr, m_pses, ACI_DEFAULT);
}
Json类型¶
#include <iostream>
#include <aci.h>
#include <stdlib.h>
using namespace std;
#pragma comment(lib, "./aci.lib")
ACIEnv* env = NULL;
ACIError* err = NULL;
ACIServer* srv = NULL;
ACISvcCtx* svc = NULL;
ACISession* ses = NULL;
ACIStmt* stmt = NULL;
char *dblink = (char *) "localhost:2003/osrdb";
char *dbuser = (char *) "SYSDBA";
char *dbpwd = (char *)"szoscar55";
int main(int args, char *argv[])
{
sword r = 0;
ACIJson * pJson = NULL;
ACIParam *colhd = NULL;
ACIParam *colhd1 = NULL;
ACIParam *colhd2 = NULL;
char *createSql = "create table t_json(a json)";
char *selectSql = "select * from t_json";
char *insertSql = "insert into t_json values (:1)";
char *dropSql = "drop table t_json";
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&env, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(env, (void**)&err, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(env, (void**)&srv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(env, (void**)&svc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(env, (void**)&ses, ACI_HTYPE_SESSION, 0, 0);
//连接数据库
r = ACIServerAttach(srv, err, (const OraText *)dblink, (dblink != NULL ? (sb4)strlen(dblink) : 0), ACI_DEFAULT);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, srv, sizeof(srv), ACI_ATTR_SERVER, err);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbuser, (ub4)strlen(dbuser), ACI_ATTR_USERNAME, err);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbpwd, (ub4)strlen(dbpwd), ACI_ATTR_PASSWORD, err);
r = ACISessionBegin(svc, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, ses, sizeof(ses), ACI_ATTR_SESSION, err);
//分配语句句柄
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
r = ACISessionBegin(svc, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, ses, sizeof(ses), ACI_ATTR_SESSION, err);
//建表
r = ACIStmtPrepare(stmt, err, (const OraText *)createSql,
(ub4)strlen(createSql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_COMMIT_ON_SUCCESS);
//查询
r = ACIStmtPrepare(stmt, err, (const OraText *)selectSql,
(ub4)strlen(selectSql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 0, 0, 0, 0, ACI_COMMIT_ON_SUCCESS);
r = ACIParamGet(stmt, ACI_HTYPE_STMT, err, (void **)&colhd, 1);
for (int i = 1;; i++)
{
r = ACIParamGet((void *)colhd, ACI_DTYPE_PARAM, err, (void **)&colhd1, i);
if (r == ACI_ERROR) break; //需要通过错误来判断是否结束
/* 获得类型*/
int type = 0;
r = ACIAttrGet((void *)colhd1, ACI_DTYPE_PARAM, (void *)&type, (ub4 *)0, ACI_ATTR_DATA_TYPE, err);
}
//删除
r = ACIStmtPrepare(stmt, err, (const OraText *)dropSql,
(ub4)strlen(dropSql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_COMMIT_ON_SUCCESS);
ACISessionEnd(svc, err, ses, ACI_DEFAULT);
}
空值/空串处理¶
Bind插入NULL值方法； Define绑定NULL值方法:
#include <iostream>
#include <aci.h>
using namespace std;
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACISession *m_pses = NULL;/*会话句柄*/
ACIStmt *m_pstmt = NULL;/*语句句柄*/
ACIBind *m_bnd = NULL;/*绑定句柄*/
ACIDefine *m_def = NULL;/*定义句柄*/
typedef struct { char x[20]; char y[20]; char z[20]; } Record;
int main()
{
sword r = ACI_SUCCESS;
char x = NULL;
char y[5] = "DEF";
char z[5] = "GHI";
ACIDefine *def1[3];
//根据位置定义输出变量
ub2 *rlp = NULL, *rcodep = NULL;
Record rec = { 1,2 };
char dsql[] = "drop table testtb";
char csql[] = "create table testtb(a varchar(20),b varchar(20),c varchar(20))";
char isql[] = "insert into testtb values (:1,:2,:3)";
char ssql[] = "select * from testtb";/////
char *m_dblink = (char *)"127.0.0.1:2003/OSRDB";
char *m_dbuser = (char *)"SYSDBA";
char *m_dbpwd = (char *)"szoscar55";
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)csql, strlen(csql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)isql, (ub4)strlen(isql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIBindByPos(m_pstmt, &m_bnd, m_perr, 1, (void *)x, sizeof(x), SQLT_STR, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIBindByPos(m_pstmt, &m_bnd, m_perr, 2, (void *)y, sizeof(y), SQLT_STR, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIBindByPos(m_pstmt, &m_bnd, m_perr, 3, (void *)z, sizeof(z), SQLT_STR, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACITransCommit(m_psvc, m_perr, ACI_DEFAULT);
//准备SQL语句
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)ssql, (ub4)strlen(ssql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &def1[0], m_perr, 1, (void *)rec.x, sizeof(rec.x), SQLT_STR, 0, 0, 0, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &def1[1], m_perr, 2, (void *)rec.y, sizeof(rec.y), SQLT_STR, 0, 0, 0, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &def1[2], m_perr, 3, (void *)rec.z, sizeof(rec.z), SQLT_STR, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 0, 0, 0, 0, ACI_DEFAULT);
while (((r = ACIStmtFetch(m_pstmt, m_perr, 1, ACI_FETCH_NEXT, ACI_DEFAULT)) != ACI_NO_DATA))
{
printf("x=%s,y=%s ,z=%s \n", rec.x, rec.y, rec.z);
}
}
Bind插入’’值方法； Define绑定’’值方法:
#include <iostream>
#include <aci.h>
using namespace std;
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACISession *m_pses = NULL;/*会话句柄*/
ACIStmt *m_pstmt = NULL;/*语句句柄*/
ACIBind *m_bnd = NULL;/*绑定句柄*/
ACIDefine *m_def = NULL;/*定义句柄*/
typedef struct { char x[20]; char y[20]; char z[20]; } Record;
int main()
{
sword r = ACI_SUCCESS;
char x[5] = "";
char y[5] = "DEF";
char z[5] = "GHI";
ACIDefine *def1[3];
//根据位置定义输出变量
ub2 *rlp = NULL, *rcodep = NULL;
Record rec = { 1,2 };
char dsql[] = "drop table testtb";
char csql[] = "create table testtb(a varchar(20),b varchar(20),c varchar(20))";
char isql[] = "insert into testtb values (:1,:2,:3)";
char ssql[] = "select * from testtb";/////
char *m_dblink = (char *)"127.0.0.1:2003/OSRDB";
char *m_dbuser = (char *)"SYSDBA";
char *m_dbpwd = (char *)"szoscar55";
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)csql, strlen(csql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)isql, (ub4)strlen(isql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIBindByPos(m_pstmt, &m_bnd, m_perr, 1, (void *)x, sizeof(x), SQLT_STR, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIBindByPos(m_pstmt, &m_bnd, m_perr, 2, (void *)y, sizeof(y), SQLT_STR, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIBindByPos(m_pstmt, &m_bnd, m_perr, 3, (void *)z, sizeof(z), SQLT_STR, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACITransCommit(m_psvc, m_perr, ACI_DEFAULT);
//准备SQL语句
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)ssql, (ub4)strlen(ssql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &def1[0], m_perr, 1, (void *)rec.x, sizeof(rec.x), SQLT_STR, 0, 0, 0, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &def1[1], m_perr, 2, (void *)rec.y, sizeof(rec.y), SQLT_STR, 0, 0, 0, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &def1[2], m_perr, 3, (void *)rec.z, sizeof(rec.z), SQLT_STR, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 0, 0, 0, 0, ACI_DEFAULT);
while (((r = ACIStmtFetch(m_pstmt, m_perr, 1, ACI_FETCH_NEXT, ACI_DEFAULT)) != ACI_NO_DATA))
{
printf("x=%s,y=%s ,z=%s \n", rec.x, rec.y, rec.z);
}
}
存储过程执行¶
#include <iostream>
#include <stdlib.h>
#include"aci.h"
using namespace std;
ACIEnv* env = NULL;
ACIError* err = NULL;
ACIServer* srv = NULL;
ACISvcCtx* svc = NULL;
ACISession* ses = NULL;
ACIStmt *stmt = NULL;
ACIBind *hBind = NULL;
ACIDefine *hDefine = NULL;
char *dblink = (char *) "localhost:2003/osrdb";
char *dbuser = (char *) "sysdba";
char *dbpwd = (char *)"szoscar55";
int main(int args, char *argv[])
{
sword r = 0;
char *dropSQL =(char *) "drop table ociTest";
char *createSQL = (char *) "create table ociTest (name varchar(256))";
char *insertSQL = (char *)"insert into ociTest values('ociTest') ";
char *dropPro = (char *)"drop procedure test_registerOutParam";
char *createPro = (char *)"create or replace procedure test_registerOutParam ( k OUT varchar(256))AS \
BEGIN \
select name into k from ociTest;\
commit;\
END; \
LANGUAGE 'ploscar';";
char *exec_sql = (char *)"begin test_registerOutParam(:1); end;";
char outbuf[8] = { 0 };
sb4 len = sizeof(outbuf);
sb2 ind = -1;
ub2 alen = 0;
r = ACIEnvCreate(&env, ACI_DEFAULT, 0, 0, 0, 0, 0, 0);
r = ACIHandleAlloc(env, (void**)&err, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(env, (void**)&srv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(env, (void**)&svc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(env, (void**)&ses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
r = ACIServerAttach(srv, err, (const OraText *)dblink, (dblink != NULL ? (sb4)strlen(dblink) : 0), ACI_DEFAULT);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, srv, sizeof(srv), ACI_ATTR_SERVER, err);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbuser, (ub4)strlen(dbuser), ACI_ATTR_USERNAME, err);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbpwd, (ub4)strlen(dbpwd), ACI_ATTR_PASSWORD, err);
r = ACISessionBegin(svc, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
ACIAttrSet(svc, ACI_HTYPE_SVCCTX, ses, 0, ACI_ATTR_SESSION, err);
ACIStmtPrepare(stmt, err, (CONST OraText*)dropPro, (ub4)strlen(dropPro), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACIStmtPrepare(stmt, err, (CONST OraText*)dropSQL, (ub4)strlen(dropSQL), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACIStmtPrepare(stmt, err, (CONST OraText*)createSQL, (ub4)strlen(createSQL), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACIStmtPrepare(stmt, err, (CONST OraText*)insertSQL, (ub4)strlen(insertSQL), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACIStmtPrepare(stmt, err, (CONST OraText*)createPro, (ub4)strlen(createPro), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACIStmtPrepare(stmt, err, (CONST OraText*)exec_sql, (ub4)strlen(exec_sql), ACI_NTV_SYNTAX, ACI_DEFAULT);
ACIBindByPos(stmt, &hBind, err, 1, outbuf, len, SQLT_STR, (void *)&ind, &alen, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACIStmtPrepare(stmt, err, (CONST OraText*)dropPro, (ub4)strlen(dropPro), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACIStmtPrepare(stmt, err, (CONST OraText*)dropSQL, (ub4)strlen(dropSQL), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACISessionEnd(svc, err, ses, ACI_DEFAULT);
}
Returning语法执行¶
#include <iostream>
#include <aci.h>
#include <stdlib.h>
using namespace std;
#pragma comment(lib, "./aci.lib")
#define LOB_BUFF_LEN 512 /*要操作的大对象buff长度*/
/*要插入的记录*/
typedef struct tagInsRecord
{
int in_int;
char in_varchar[64];
}InsRecord;
ACIEnv* env = NULL;
ACIError* err = NULL;
ACIServer* srv = NULL;
ACISvcCtx* svc = NULL;
ACISession* ses = NULL;
ACIStmt* stmt = NULL;
ACIBind* pBind[10];
ACIDefine* pDef[10];
char *dblink = (char *) "localhost:2003/osrdb";
char *dbuser = (char *) "SYSDBA";
char *dbpwd = (char *)"szoscar55";
void insertblob();
void checkerr(ACIError* err, sword status)
{
text errbuf[512];
ub4 buflen = 0;
sb4 errcode;
switch (status)
{
case ACI_SUCCESS:
break;
case ACI_SUCCESS_WITH_INFO:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - ACI_SUCCESS_WITH_INFO:%s\n", errbuf);
exit(-1);
break;
case ACI_NEED_DATA:
printf("Error - ACI_NEED_DATA\n");
exit(-1);
break;
case ACI_NO_DATA:
printf("Error - ACI_NO_DATA\n");
exit(-1);
break;
case ACI_ERROR:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - %d %s\n", errcode, errbuf);
exit(-1);
break;
case ACI_INVALID_HANDLE:
printf("Error - ACI_INVALID_HANDLE\n");
exit(-1);
break;
case ACI_STILL_EXECUTING:
printf("Error - ACI_STILL_EXECUTE\n");
exit(-1);
break;
case ACI_CONTINUE:
printf("Error - ACI_CONTINUE\n");
exit(-1);
break;
default:
break;
}
}
int main(int args, char *argv[])
{
sword r = 0;
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
checkerr(err, r);
r = ACIEnvInit(&env, ACI_DEFAULT, 0, 0);
checkerr(err, r);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(env, (void**)&err, ACI_HTYPE_ERROR, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&srv, ACI_HTYPE_SERVER, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&svc, ACI_HTYPE_SVCCTX, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&ses, ACI_HTYPE_SESSION, 0, 0);
checkerr(err, r);
//连接数据库
r = ACIServerAttach(srv, err, (const OraText *)dblink, (dblink != NULL ? (sb4)strlen(dblink) : 0), ACI_DEFAULT);
checkerr(err, r);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, srv, sizeof(srv), ACI_ATTR_SERVER, err);
checkerr(err, r);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbuser, (ub4)strlen(dbuser), ACI_ATTR_USERNAME, err);
checkerr(err, r);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbpwd, (ub4)strlen(dbpwd), ACI_ATTR_PASSWORD, err);
checkerr(err, r);
r = ACISessionBegin(svc, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
checkerr(err, r);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, ses, sizeof(ses), ACI_ATTR_SESSION, err);
checkerr(err, r);
//分配语句句柄
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
checkerr(err, r);
insertblob();
return 0;
}
void insertblob()
{
int i = 0;
sword r = 0;
ACIStmt * stmt = NULL;
ACIBind * pBind = NULL;
ACILobLocator *blob = NULL;
char buf[256];
ub4 amtp = 256;
char dsql[] = "drop table t_blob";
char *createT = (char *)"create table t_blob (a blob)";
char * sqlstrinsert = (char *)"insert into t_blob values(empty_blob())returning a into :1";//冒号后没有空格
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
r = ACIStmtPrepare(stmt, err, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_DEFAULT);
//Create table
r = ACIStmtPrepare(stmt, err, (CONST OraText*)createT,
(ub4)strlen(createT), ACI_NTV_SYNTAX, ACI_DEFAULT);
checkerr(err, r);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
checkerr(err, r);
for (i = 0; i < 256; i++)
{
buf[i] = i;
}
checkerr(err, ACIDescriptorAlloc(env, (dvoid **)&blob, ACI_DTYPE_LOB,
0, 0));
checkerr(err, ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0,
0));
checkerr(err, ACIStmtPrepare(stmt, err, (OraText *)sqlstrinsert,
(ub4)strlen(sqlstrinsert), ACI_NTV_SYNTAX, ACI_DEFAULT));
checkerr(err, ACIBindByPos(stmt, &pBind, err, 1, &blob, sizeof(blob), SQLT_BLOB, 0, 0, 0, 0, 0, ACI_DEFAULT));
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_DEFAULT);
checkerr(err, r);
checkerr(err, ACILobWrite(svc, err, blob, &amtp, 1, buf, sizeof(buf), 0,
0, (ACICallbackLobWrite)0, 0, 0));
checkerr(err, ACITransCommit(svc, err, 0));
}
批量bind写入¶
#include <iostream>
#include <aci.h>
#define SUMVALUE 100 //插入数据量
using namespace std;
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACISession *m_pses = NULL;/*会话句柄*/
ACIStmt *m_pstmt = NULL;/*语句句柄*/
ACIBind *m_bnd[3];/*绑定句柄*/
ACIDefine *m_def = NULL;/*定义句柄*/
typedef struct { char x[SUMVALUE][20]; char y[SUMVALUE][20]; char z[SUMVALUE][20]; } Record;
int main()
{
sword r = ACI_SUCCESS;
ub1 errbuf[64] = { 0 };//获取错误信息的缓冲
ub1 sqlstate[64] = { 0 };// ACIErrorGet()的第三个参数变量
sb4 ercodep = 0;
char x[5][5] = { "ABC","abc" };
char y[5][5] = { "DEF","def" };
char z[5][5] = { "GHI","ghi" };
int i;
//根据位置定义输出变量
ub2 indp4[SUMVALUE] = { 0 };
ub2 indp5[SUMVALUE] = { 0 };
ub2 indp6[SUMVALUE] = { 0 };
ub2 indp1[SUMVALUE] = { 0 };
ub2 indp2[SUMVALUE] = { 0 };
ub2 indp3[SUMVALUE] = { 0 };
ub2 alenp1[SUMVALUE] = { 4,4};
ub2 alenp2[SUMVALUE] = { 4,4};
ub2 alenp3[SUMVALUE] = { 4,4};
Record rec = { 1,2,3 };
char dsql[] = "drop table testtb";
char csql[] = "create table testtb(a varchar(20),b varchar(20),c varchar(20))";
char isql[] = "insert into testtb values (:1,:2,:3)";
char ssql[] = "select * from testtb";
char *m_dblink = (char *)"localhost:2003/OSRDB";
char *m_dbuser = (char *)"SYSDBA";
char *m_dbpwd = (char *)"szoscar55";
ACIDefine *def1[3];
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)csql, strlen(csql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)isql, (ub4)strlen(isql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIBindByPos(m_pstmt, &m_bnd[0], m_perr, 1, (void *)x[0], sizeof(x[0]), SQLT_STR, (void*)indp4, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIBindArrayOfStruct(m_bnd[0], m_perr, sizeof(x[0]), sizeof(SQLT_INT), 0, 0);
r = ACIBindByPos(m_pstmt, &m_bnd[1], m_perr, 2, (void *)y[0], sizeof(y[0]), SQLT_STR, (void*)indp5, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIBindArrayOfStruct(m_bnd[1], m_perr, sizeof(y[0]), sizeof(SQLT_INT), 0, 0);
r = ACIBindByPos(m_pstmt, &m_bnd[2], m_perr, 3, (void *)z[0], sizeof(z[0]), SQLT_STR, (void*)indp6, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIBindArrayOfStruct(m_bnd[2], m_perr, sizeof(z[0]), sizeof(SQLT_INT), 0, 0);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 2, 0, NULL, NULL, ACI_DEFAULT);
ACITransCommit(m_psvc, m_perr, ACI_DEFAULT);
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)ssql, (ub4)strlen(ssql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &def1[0], m_perr, 1, (void *)rec.x, sizeof(rec.x), SQLT_STR, (void*)indp1, 0, 0, ACI_DEFAULT);
r = ACIDefineArrayOfStruct(def1[0], m_perr, 20, sizeof(ub2), 0, 0);
r = ACIDefineByPos(m_pstmt, &def1[1], m_perr, 2, (void *)rec.y, sizeof(rec.y), SQLT_STR, (void*)indp2, 0, 0, ACI_DEFAULT);
r = ACIDefineArrayOfStruct(def1[1], m_perr, 20, sizeof(ub2), 0, 0);
r = ACIDefineByPos(m_pstmt, &def1[2], m_perr, 3, (void *)rec.z, sizeof(rec.z), SQLT_STR, (void*)indp3, 0, 0, ACI_DEFAULT);
r = ACIDefineArrayOfStruct(def1[2], m_perr, 20, sizeof(ub2), 0, 0);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 2, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtFetch(m_pstmt, m_perr, 1, ACI_FETCH_NEXT, ACI_DEFAULT);
for (i = 0; i < 2; i++)
{
printf("x=%s,y=%s ,z=%s \n", rec.x[i], rec.y[i], rec.z[i]);
}
printf("数据获取结束\n");
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACISessionEnd(m_psvc, m_perr, m_pses, ACI_DEFAULT);
}
批量define查询¶
#include <iostream>
#include <aci.h>
#define SUMVALUE 100 //插入数据量
using namespace std;
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACISession *m_pses = NULL;/*会话句柄*/
ACIStmt *m_pstmt = NULL;/*语句句柄*/
ACIBind *m_bnd[3];/*绑定句柄*/
ACIDefine *m_def = NULL;/*定义句柄*/
typedef struct { char x[SUMVALUE][20]; char y[SUMVALUE][20]; char z[SUMVALUE][20]; } Record;
int main()
{
sword r = ACI_SUCCESS;
ub1 errbuf[64] = { 0 };//获取错误信息的缓冲区
ub1 sqlstate[64] = { 0 };// ACIErrorGet()的第三个参数变量
sb4 ercodep = 0;
char x[5][5] = { "ABC","abc" };
char y[5][5] = { "DEF","def" };
char z[5][5] = { "GHI","ghi" };
int i;
//根据位置定义输出变量
ub2 indp4[SUMVALUE] = { 0 };
ub2 indp5[SUMVALUE] = { 0 };
ub2 indp6[SUMVALUE] = { 0 };
ub2 indp1[SUMVALUE] = { 0 };
ub2 indp2[SUMVALUE] = { 0 };
ub2 indp3[SUMVALUE] = { 0 };
ub2 alenp1[SUMVALUE] = { 4,4};
ub2 alenp2[SUMVALUE] = { 4,4};
ub2 alenp3[SUMVALUE] = { 4,4};
Record rec = { 1,2,3 };
char dsql[] = "drop table testtb";
char csql[] = "create table testtb(a varchar(20),b varchar(20),c varchar(20))";
char isql[] = "insert into testtb values (:1,:2,:3)";
char ssql[] = "select * from testtb";
char *m_dblink = (char *)"localhost:2003/OSRDB";
char *m_dbuser = (char *)"SYSDBA";
char *m_dbpwd = (char *)"szoscar55";
ACIDefine *def1[3];
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)csql, strlen(csql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)isql, (ub4)strlen(isql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIBindByPos(m_pstmt, &m_bnd[0], m_perr, 1, (void *)x[0], sizeof(x[0]), SQLT_STR, (void*)indp4, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIBindArrayOfStruct(m_bnd[0], m_perr, sizeof(x[0]), sizeof(SQLT_INT), 0, 0);
r = ACIBindByPos(m_pstmt, &m_bnd[1], m_perr, 2, (void *)y[0], sizeof(y[0]), SQLT_STR, (void*)indp5, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIBindArrayOfStruct(m_bnd[1], m_perr, sizeof(y[0]), sizeof(SQLT_INT), 0, 0);
r = ACIBindByPos(m_pstmt, &m_bnd[2], m_perr, 3, (void *)z[0], sizeof(z[0]), SQLT_STR, (void*)indp6, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIBindArrayOfStruct(m_bnd[2], m_perr, sizeof(z[0]), sizeof(SQLT_INT), 0, 0);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 2, 0, NULL, NULL, ACI_DEFAULT);
ACITransCommit(m_psvc, m_perr, ACI_DEFAULT);
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)ssql, (ub4)strlen(ssql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIDefineByPos(m_pstmt, &def1[0], m_perr, 1, (void *)rec.x, sizeof(rec.x), SQLT_STR, (void*)indp1, 0, 0, ACI_DEFAULT);
r = ACIDefineArrayOfStruct(def1[0], m_perr, 20, sizeof(ub2), 0, 0);
r = ACIDefineByPos(m_pstmt, &def1[1], m_perr, 2, (void *)rec.y, sizeof(rec.y), SQLT_STR, (void*)indp2, 0, 0, ACI_DEFAULT);
r = ACIDefineArrayOfStruct(def1[1], m_perr, 20, sizeof(ub2), 0, 0);
r = ACIDefineByPos(m_pstmt, &def1[2], m_perr, 3, (void *)rec.z, sizeof(rec.z), SQLT_STR, (void*)indp3, 0, 0, ACI_DEFAULT);
r = ACIDefineArrayOfStruct(def1[2], m_perr, 20, sizeof(ub2), 0, 0);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 2, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtFetch(m_pstmt, m_perr, 1, ACI_FETCH_NEXT, ACI_DEFAULT);
for (i = 0; i < 2; i++)
{
printf("x=%s,y=%s ,z=%s \n", rec.x[i], rec.y[i], rec.z[i]);
}
printf("数据获取结束\n");
r = ACIStmtPrepare(m_pstmt, m_perr, (OraText*)dsql, strlen(dsql), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
r = ACISessionEnd(m_psvc, m_perr, m_pses, ACI_DEFAULT);
}
分片绑定或定义¶
#include <iostream>
#include <aci.h>
#include <stdlib.h>
using namespace std;
#pragma comment(lib, "./aci.lib")
ACIEnv* env = NULL;
ACIError* err = NULL;
ACIServer* srv = NULL;
ACISvcCtx* svc = NULL;
ACISession* ses = NULL;
ACIStmt* stmt = NULL;
char *dblink = (char *) "localhost:2003/osrdb";
char *dbuser = (char *) "SYSDBA";
char *dbpwd = (char *)"szoscar55";
int main(int args, char *argv[])
{
sword r = 0;
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&env, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(env, (void**)&err, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(env, (void**)&srv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(env, (void**)&svc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(env, (void**)&ses, ACI_HTYPE_SESSION, 0, 0);
//连接数据库
r = ACIServerAttach(srv, err, (const OraText *)dblink, (dblink != NULL ? (sb4)strlen(dblink) : 0), ACI_DEFAULT);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, srv, sizeof(srv), ACI_ATTR_SERVER, err);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbuser, (ub4)strlen(dbuser), ACI_ATTR_USERNAME, err);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbpwd, (ub4)strlen(dbpwd), ACI_ATTR_PASSWORD, err);
r = ACISessionBegin(svc, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, ses, sizeof(ses), ACI_ATTR_SESSION, err);
//分配语句句柄
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
ACIBind *hBind[2];
char *dsql = (char *)"drop table ociTest";
char *csql = (char *) "create table ociTest(id int, val blob)";
char *isql = (char *) "insert into ociTest(id, val) values(1, empty_blob())";
char *usql = (char *) "update ociTest set val = :blob where id = 1";
ub4 getype = 0;
ub1 in_out = 0;
ub4 iter = 0;
ub4 idx = 0;
ub1 piece = 0;
void *hndl = NULL;
char buf[15] = "abcdefabcdefab";
ub4 alne = strlen(buf) + 1;
sb2 ind = 0;
ub2 rcode = 0;
ACISessionBegin(svc, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
ACIAttrSet(svc, ACI_HTYPE_SVCCTX, ses, 0, ACI_ATTR_SESSION, err);
ACIStmtPrepare(stmt, err, (CONST OraText*)dsql, (ub4)strlen(dsql), ACI_NTV_SYNTAX, ACI_DEFAULT);
ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACIStmtPrepare(stmt, err, (CONST OraText*)csql, (ub4)strlen(csql), ACI_NTV_SYNTAX, ACI_DEFAULT);
ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACIStmtPrepare(stmt, err, (CONST OraText*)isql, (ub4)strlen(isql), ACI_NTV_SYNTAX, ACI_DEFAULT);
ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACIStmtPrepare(stmt, err, (CONST OraText*)usql, (ub4)strlen(usql), ACI_NTV_SYNTAX, ACI_DEFAULT);
ACIBindByName(stmt, &hBind[1], err, (OraText*)":blob", (sb4)strlen(":blob"), 0, 15, SQLT_STR, 0, 0, 0, 15, 0, ACI_DATA_AT_EXEC);
ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACIStmtGetPieceInfo(stmt, err, (void**)(&hndl), &getype, &in_out, &iter, &idx, &piece);
piece = ACI_ONE_PIECE;
ACIStmtSetPieceInfo(hndl, getype, err, buf, &alne, piece, &ind, &rcode);
ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
ACISessionEnd(svc, err, ses, ACI_DEFAULT);
}
动态绑定或定义¶
#include <iostream>
#include <aci.h>
#include <stdlib.h>
using namespace std;
#pragma comment(lib, "./aci.lib")
ACIEnv* env = NULL;
ACIError* err = NULL;
ACIServer* srv = NULL;
ACISvcCtx* svc = NULL;
ACISession* ses = NULL;
ACIStmt* stmt = NULL;
char *dblink = (char *) "localhost:2003/osrdb";
char *dbuser = (char *) "SYSDBA";
char *dbpwd = (char *)"szoscar55";
typedef struct oci_bind_param {
sb2 dtype;
dvoid *valuep;
sb2 indicator;
ub4 alen;
ub2 retcode;
}oci_bind_param_t;
static sb4 oci_bind_input_cb(dvoid *ctx, ACIBind *bindp, ub4 iter, ub4 index, dvoid **bufpp, ub4 *alenp, ub1 *piecep, dvoid **indpp)
{
oci_bind_param_t *param = (oci_bind_param_t*)ctx;
*indpp = ¶m->indicator;
*bufpp = param->valuep;
*alenp = param->alen;
*piecep = ACI_ONE_PIECE;
return ACI_CONTINUE;
}
static sb4 oci_bind_output_cb(dvoid *ctx, ACIBind *bindp, ub4 iter, ub4 index, dvoid **bufpp, ub4 **alenp, ub1 *piecep, dvoid **indpp, ub2** rcodepp) /* {{{ */
{
oci_bind_param_t *param = (oci_bind_param_t*)ctx;
*indpp = ¶m->indicator;
*bufpp = param->valuep;
*alenp = ¶m->alen;
*rcodepp = ¶m->retcode;
*piecep = ACI_ONE_PIECE;
return ACI_CONTINUE;
}
int main(int args, char *argv[])
{
sword r = 0;
ACIBind *hBind;
char *dsql = (char *)"drop table ociTest";
char *csql = (char *)"create table ociTest(id int,col1 varchar2(10),col2 date,col3 varchar2(10),col4 float,col5 clob)";
char *isql = (char *) "insert into ociTest(id) values(:id)";
char *selectSQL = (char *)"select id from ociTest order by id";
int id = 8;
oci_bind_param_t *param = NULL;
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&env, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(env, (void**)&err, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(env, (void**)&srv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(env, (void**)&svc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(env, (void**)&ses, ACI_HTYPE_SESSION, 0, 0);
//连接数据库
r = ACIServerAttach(srv, err, (const OraText *)dblink, (dblink != NULL ? (sb4)strlen(dblink) : 0), ACI_DEFAULT);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, srv, sizeof(srv), ACI_ATTR_SERVER, err);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbuser, (ub4)strlen(dbuser), ACI_ATTR_USERNAME, err);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbpwd, (ub4)strlen(dbpwd), ACI_ATTR_PASSWORD, err);
//分配语句句柄
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
r = ACISessionBegin(svc, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
param = (oci_bind_param_t *)malloc(sizeof(oci_bind_param_t));
memset(param, 0, sizeof(oci_bind_param_t));
param->dtype = SQLT_INT;
param->valuep = &id;
param->indicator = 0;
param->alen = sizeof(id);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, ses, 0, ACI_ATTR_SESSION, err);
r = ACIStmtPrepare(stmt, err, (CONST OraText*)dsql, (ub4)strlen(dsql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACIStmtPrepare(stmt, err, (CONST OraText*)csql, (ub4)strlen(csql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACIStmtPrepare(stmt, err, (CONST OraText*)isql, (ub4)strlen(isql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIBindByName(stmt, &hBind, err, (OraText*)":id", (sb4)strlen(":id"), 0, sizeof(id), SQLT_INT, 0, 0, 0, 0, 0, ACI_DATA_AT_EXEC);
r = ACIBindDynamic(hBind, err, param, oci_bind_input_cb, param, oci_bind_output_cb);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
int id2 = 5;
memset(param, 0, sizeof(oci_bind_param_t));
param->dtype = SQLT_INT;
param->valuep = &id2;
param->indicator = 0;
param->alen = sizeof(id2);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACITransCommit(svc, err, ACI_DEFAULT);
int id_out = 0;
ACIDefine* def = NULL;
r = ACIStmtPrepare(stmt, err, (CONST OraText*)selectSQL, (ub4)strlen(selectSQL), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIDefineByPos(stmt, &def, err, 1, (dvoid*)&id_out, (sb4)sizeof(id_out), SQLT_INT, 0, 0, 0, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
r = ACIStmtFetch(stmt, err, 1, ACI_FETCH_NEXT, ACI_DEFAULT);
ACISessionEnd(svc, err, ses, ACI_DEFAULT);
}
语句句柄缓存¶
#include <iostream>
#include <stdlib.h>
#include"aci.h"
using namespace std;
/*要插入的记录*/
typedef struct tagInsRecord
{
int in_int;
char in_varchar[64];
}InsRecord;
ACIEnv* env = NULL;
ACIError* err = NULL;
ACIServer* srv = NULL;
ACISvcCtx* svc = NULL;
ACISession* ses = NULL;
ACIStmt* stmt = NULL;
ACIBind* pBind;
ACIDefine* pDef;
char *dblink = (char *) "localhost:2003/osrdb";
char *dbuser = (char *) "sysdba";
char *dbpwd = (char *)"szoscar55";
void checkerr(ACIError* err, sword status)
{
text errbuf[512];
ub4 buflen = 0;
sb4 errcode;
switch (status)
{
case ACI_SUCCESS:
break;
case ACI_SUCCESS_WITH_INFO:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - ACI_SUCCESS_WITH_INFO:%s\n", errbuf);
exit(-1);
break;
case ACI_NEED_DATA:
printf("Error - ACI_NEED_DATA\n");
exit(-1);
break;
case ACI_NO_DATA:
printf("Error - ACI_NO_DATA\n");
exit(-1);
break;
case ACI_ERROR:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - %d %s\n", errcode, errbuf);
exit(-1);
break;
case ACI_INVALID_HANDLE:
printf("Error - ACI_INVALID_HANDLE\n");
exit(-1);
break;
case ACI_STILL_EXECUTING:
printf("Error - ACI_STILL_EXECUTE\n");
exit(-1);
break;
case ACI_CONTINUE:
printf("Error - ACI_CONTINUE\n");
exit(-1);
break;
default:
break;
}
}
int main(int args, char *argv[])
{
sword r = 0;
char *selectSQL = (char*) "select col1 from ociTestPre2 where col1 = :1";
char *selectSQL2 = (char*)"select col2 from ociTestPre2 where col1 = :1";
char *ptrKey = (char*)"ins1";
char *ptrKey2 = (char*) "ins2";
char *createSQL = (char*)"create table ociTestPre2(col1 int, col2 int)";
char *dropSQL = (char*) "drop table ociTestPre2";
char *insertSQL = (char*)"insert into ociTestPre2(col1, col2) values(:1, :2)";
int inNum = 10;
int inNum2 = 20;
int outNum = 0;
ub2 rlp = 0;
ub2 rcodep = 0;
ACIBind *bnd = NULL;
ACIDefine *def = NULL;
ACICPool *cpool = NULL;
OraText *poolName = NULL;
sb4 poolNameLen = 0;
ACISvcCtx* svc = NULL;
ACIAuthInfo* authhp = NULL;
//初始化环境句柄
r = ACIEnvCreate(&env, ACI_DEFAULT, 0, 0, 0, 0, 0, 0);
checkerr(err, r);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(env, (void**)&err, ACI_HTYPE_ERROR, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&srv, ACI_HTYPE_SERVER, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&svc, ACI_HTYPE_SVCCTX, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&ses, ACI_HTYPE_SESSION, 0, 0);
checkerr(err, r);
r = ACIServerAttach(srv, err, (const OraText *)dblink, (dblink != NULL ? (sb4)strlen(dblink) : 0), ACI_DEFAULT);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, srv, sizeof(srv), ACI_ATTR_SERVER, err);
checkerr(err, r);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbuser, (ub4)strlen(dbuser), ACI_ATTR_USERNAME, err);
checkerr(err, r);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbpwd, (ub4)strlen(dbpwd), ACI_ATTR_PASSWORD, err);
checkerr(err, r);
r = ACISessionBegin(svc, err, ses, ACI_CRED_RDBMS, ACI_STMT_CACHE);
checkerr(err, r);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, ses, sizeof(ses), ACI_ATTR_SESSION, err);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
//删除表
r = ACIStmtPrepare(stmt, err, (const OraText *)dropSQL,
(ub4)strlen(dropSQL), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_COMMIT_ON_SUCCESS);
//建表
r = ACIStmtPrepare(stmt, err, (const OraText *)createSQL,
(ub4)strlen(createSQL), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_COMMIT_ON_SUCCESS);
//插入数据
r = ACIStmtPrepare(stmt, err, (const OraText *)insertSQL,
(ub4)strlen(insertSQL), ACI_HTYPE_ERROR, ACI_DEFAULT);
r = ACIBindByPos(stmt, &bnd, err, 1, (void *)&inNum, sizeof(inNum),
SQLT_INT, (void *)0, (ub2 *)0, (ub2 *)0, 0, 0,
ACI_DEFAULT);
r = ACIBindByPos(stmt, &bnd, err, 2, (void *)&inNum2, sizeof(inNum2),
SQLT_INT, (void *)0, (ub2 *)0, (ub2 *)0, 0, 0,
ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, 0, 0, ACI_COMMIT_ON_SUCCESS);
//查询1
ACIStmt *stmt2 = NULL;
//使用提供的语句prepare
r = ACIStmtPrepare2(svc, &stmt2, err, (const OraText *)selectSQL,
(ub4)strlen(selectSQL), (OraText *)ptrKey, strlen(ptrKey), ACI_HTYPE_ERROR, ACI_DEFAULT);
//checkerr(err, r);
r = ACIBindByPos(stmt2, &bnd, err, 1, (void *)&inNum, sizeof(inNum), SQLT_INT, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIDefineByPos(stmt2, &def, err, 1, (void *)&outNum,
sizeof(outNum), SQLT_INT, (void *)0, (ub2 *)&rlp,
(ub2*)&rcodep, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt2, err, 1, 0, 0, 0, ACI_DEFAULT);
//使用提供的key标记并缓存stmt。
ACIStmtRelease(stmt2, err, (const text *)ptrKey, strlen(ptrKey), ACI_DEFAULT);
//查询2
ACIStmt *stmt3 = NULL;
outNum = 0;
//使用提供key查找stmt缓存，忽略提供的sql语句
r = ACIStmtPrepare2(svc, &stmt3, err, (const OraText *)selectSQL2,
(ub4)strlen(selectSQL2), (OraText *)ptrKey2, strlen(ptrKey2), ACI_HTYPE_ERROR, ACI_DEFAULT);
//checkerr(err, r);
r = ACIBindByPos(stmt3, &bnd, err, 1, (void *)&inNum, sizeof(inNum), SQLT_INT, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIDefineByPos(stmt3, &def, err, 1, (void *)&outNum,
sizeof(outNum), SQLT_INT, (void *)0, (ub2 *)&rlp,
(ub2*)&rcodep, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt3, err, 1, 0, 0, 0, ACI_DEFAULT);
//使用不同的key重新标记stmt
r = ACIStmtRelease(stmt3, err, (const OraText *)ptrKey2, strlen(ptrKey2), ACI_DEFAULT);
checkerr(err, r);
ACIStmt *stmt5 = NULL;
outNum = 0;
//使用提供key查找stmt缓存，忽略提供的sql语句
r = ACIStmtPrepare2(svc, &stmt5, err, (const OraText *)selectSQL2,
(ub4)strlen(selectSQL2), (OraText *)ptrKey2, strlen(ptrKey2), ACI_HTYPE_ERROR, ACI_DEFAULT);
//checkerr(err, r);
r = ACIBindByPos(stmt5, &bnd, err, 1, (void *)&inNum, sizeof(inNum), SQLT_INT, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIDefineByPos(stmt5, &def, err, 1, (void *)&outNum,
sizeof(outNum), SQLT_INT, (void *)0, (ub2 *)&rlp,
(ub2*)&rcodep, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt5, err, 1, 0, 0, 0, ACI_DEFAULT);
//使用不同的key重新标记stmt
r = ACIStmtRelease(stmt5, err, (const OraText *)ptrKey2, strlen(ptrKey2), ACI_STRLS_CACHE_DELETE);
checkerr(err, r);
//查询2
ACIStmt *stmt6 = NULL;
outNum = 0;
//使用提供key查找stmt缓存，忽略提供的sql语句
r = ACIStmtPrepare2(svc, &stmt6, err, (const OraText *)selectSQL,
(ub4)strlen(selectSQL), (OraText *)ptrKey2, strlen(ptrKey2), ACI_HTYPE_ERROR, ACI_DEFAULT);
//checkerr(err, r);
r = ACIBindByPos(stmt6, &bnd, err, 1, (void *)&inNum, sizeof(inNum), SQLT_INT, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIDefineByPos(stmt6, &def, err, 1, (void *)&outNum,
sizeof(outNum), SQLT_INT, (void *)0, (ub2 *)&rlp,
(ub2*)&rcodep, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt6, err, 1, 0, 0, 0, ACI_DEFAULT);
//使用不同的key重新标记stmt
r = ACIStmtRelease(stmt6, err, (const OraText *)ptrKey2, strlen(ptrKey2), ACI_DEFAULT);
checkerr(err, r);
//查询3
ACIStmt *stmt4 = NULL;
outNum = 0;
//使用提供key查找stmt缓存，忽略提供的sql语句
r = ACIStmtPrepare2(svc, &stmt4, err, (const OraText *)selectSQL2,
(ub4)strlen(selectSQL2), (OraText *)ptrKey, strlen(ptrKey), ACI_HTYPE_ERROR, ACI_DEFAULT);
//checkerr(err, r);
r = ACIBindByPos(stmt4, &bnd, err, 1, (void *)&inNum, sizeof(inNum), SQLT_INT, 0, 0, 0, 0, 0, ACI_DEFAULT);
r = ACIDefineByPos(stmt4, &def, err, 1, (void *)&outNum,
sizeof(outNum), SQLT_INT, (void *)0, (ub2 *)&rlp,
(ub2*)&rcodep, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt4, err, 1, 0, 0, 0, ACI_DEFAULT);
//使用不同的key重新标记stmt
r = ACIStmtRelease(stmt4, err, (const OraText *)ptrKey2, strlen(ptrKey2), ACI_DEFAULT);
checkerr(err, r);
ACILogoff(svc, err);
ACIConnectionPoolDestroy(cpool, err, ACI_DEFAULT);
ACIHandleFree(authhp, ACI_HTYPE_AUTHINFO);
authhp = NULL;
ACIHandleFree(cpool, ACI_HTYPE_CPOOL);
cpool = NULL;
}
描述信息获取¶
#include <iostream>
#include <aci.h>
using namespace std;
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACISession *m_pses = NULL;/*会话句柄*/
ACIStmt *m_pstmt = NULL;/*语句句柄*/
ACIBind *m_bnd = NULL;/*绑定句柄*/
ACIDefine *m_def = NULL;/*定义句柄*/
typedef struct { int sID; char sCol1[30]; } Record;
int main()
{
int i = 0;
int temp = 2;
sword r = ACI_SUCCESS;
int id = 0;
//根据位置定义输出变量
ub2 *rlp = NULL, *rcodep = NULL;
Record rec = { 1,2 };
char *m_dblink = (char *)"localhost:2003/OSRDB";
char *m_dbuser = (char *)"SYSDBA";
char *m_dbpwd = (char *)"szoscar55";
text objptr[] = "ceshi";/* 要描述的表名 */
sb1 scale;
ub4 numcols, col_width;
ub1 char_semantics, precision;
ub2 coltyp;
ub4 objp_len = (ub4)strlen((char *)objptr);
ACIParam *parmh = (ACIParam *)0;/* 参数句柄 */
ACIParam *collsthd = (ACIParam *)0;/*指向各列信息的句柄 */
ACIParam *colhd = (ACIParam *)0;/* 列句柄 */
ACIDescribe *dschp = (ACIDescribe *)0;/* 描述句柄 */
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
ACIHandleAlloc((void *)m_penv, (void **)&dschp, (ub4)ACI_HTYPE_DESCRIBE, (size_t)0, (void **)0);
//printf("0%d\n", temp);
/* 获取一个表的描述句柄 */
if (ACIDescribeAny(m_psvc, m_perr, (void *)objptr, objp_len, ACI_OTYPE_NAME, 0, ACI_PTYPE_TABLE, dschp))
return ACI_ERROR;
//printf("1%d\n", temp);
/* 获取参数句柄 */
if (ACIAttrGet((void *)dschp, ACI_HTYPE_DESCRIBE, (void *)&parmh, (ub4 *)0, ACI_ATTR_PARAM, m_perr))
return ACI_ERROR;
//printf("2%d\n", temp);
/* 在本例中，对象的类型信息ACI_PTYPE_TABLE从ACIAttrGet()返回的
参数描述符中获取 */
/* 获取表中列的数目 */
numcols = 0;
if (r = ACIAttrGet((void *)parmh, ACI_DTYPE_PARAM, (void *)&numcols, (ub4 *)0, ACI_ATTR_NUM_COLS, m_perr))
return ACI_ERROR;
/* 获取表中各列的句柄*/
if (ACIAttrGet((void *)parmh, ACI_DTYPE_PARAM, (void *)&collsthd, (ub4 *)0, ACI_ATTR_LIST_COLUMNS, m_perr))
return ACI_ERROR;
printf("numcols=%d\n", numcols);
/*通过列清单和检索每一列的数据类型，然后递归描述列类型。*/
for (i = 1; i <= numcols; i++)
{
/* 为第i列获取参数*/
if (r = ACIParamGet((void *)collsthd, ACI_DTYPE_PARAM, m_perr, (void **)&colhd, (ub4)i))
return ACI_ERROR;
/* 举例获取第i列的数据类型 */
coltyp = 0;
if (r = ACIAttrGet((void *)colhd, ACI_DTYPE_PARAM, (void *)&coltyp, (ub4*)0, ACI_ATTR_DATA_TYPE, m_perr))
return ACI_ERROR;
printf("coltyp=%d\n", coltyp);
/* 获取列的长度语义*/
char_semantics = 0;
ACIAttrGet((void*)colhd, ACI_DTYPE_PARAM, (void*)&char_semantics, 0, (ub4)ACI_ATTR_CHAR_USED, (ACIError *)m_perr);
printf("char_semantics=%d\n", char_semantics);
col_width = 0;
if (char_semantics)
/* 获取以字符为单位的列宽度*/
ACIAttrGet((void*)colhd, (ub4)ACI_DTYPE_PARAM, (void*)&col_width, (ub4 *)0, (ub4)ACI_ATTR_CHAR_SIZE, (ACIError *)m_perr);
else
/* 获取以字节为单位的列宽度 */
ACIAttrGet((void*)colhd, (ub4)ACI_DTYPE_PARAM, (void*)&col_width, (ub4 *)0, (ub4)ACI_ATTR_DATA_SIZE, (ACIError *)m_perr);
printf("col_width=%d\n", col_width);
/*获取精度*/
precision = 0;
ACIAttrGet((void*)colhd, ACI_DTYPE_PARAM, (void*)&precision, 0, (ub4)ACI_ATTR_PRECISION, (ACIError *)m_perr);
printf("precision=%d\n", precision);
/*获取尺度*/
scale = 0;
ACIAttrGet((void*)colhd, ACI_DTYPE_PARAM, (void*)&scale, 0, (ub4)ACI_ATTR_SCALE, (ACIError *)m_perr);
printf("scale=%d\n", scale);
}
if (dschp)
ACIHandleFree((void *)dschp, ACI_HTYPE_DESCRIBE);
return 0;
}
大容量导入¶
3列：int、decimal(30,10) 、timestamp的导入
#include <stdio.h>
#include <aci.h>
#include <stdlib.h>
#include "time.h"
#include <string.h>
using namespace std;
/*要插入的记录*/
typedef struct tagInsRecord
{
int in_int;
char in_varchar[64];
}InsRecord;
ACIEnv* env = NULL;
ACIError* err = NULL;
ACIServer* srv = NULL;
ACISvcCtx* svc = NULL;
ACISession* ses = NULL;
ACIStmt* stmt = NULL;
ACIBind* pBind;
ACIDefine* pDef;
char *dblink = (char *) "localhost:2003/osrdb";
char *dbuser = (char *) "sysdba";
char *dbpwd = (char *)"szoscar55";
void checkerr(ACIError* err, sword status)
{
text errbuf[512];
ub4 buflen = 0;
sb4 errcode;
switch (status)
{
case ACI_SUCCESS:
break;
case ACI_SUCCESS_WITH_INFO:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - ACI_SUCCESS_WITH_INFO:%s\n", errbuf);
exit(-1);
break;
case ACI_NEED_DATA:
printf("Error - ACI_NEED_DATA\n");
exit(-1);
break;
case ACI_NO_DATA:
printf("Error - ACI_NO_DATA\n");
exit(-1);
break;
case ACI_ERROR:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - %d %s\n", errcode, errbuf);
exit(-1);
break;
case ACI_INVALID_HANDLE:
printf("Error - ACI_INVALID_HANDLE\n");
exit(-1);
break;
case ACI_STILL_EXECUTING:
printf("Error - ACI_STILL_EXECUTE\n");
exit(-1);
break;
case ACI_CONTINUE:
printf("Error - ACI_CONTINUE\n");
exit(-1);
break;
default:
break;
}
}
int main(int args, char *argv[])
{
sword r = 0;
ACIHIMP *imp;
int sdwRowsPerBatch = 10000; //每批中数据的行数
int bufferSize = 1024 * 1024;
char noval[100] = "5";
char doubleval[100] = "56.432";
char charval[100] = "2021-4-19 18:20:31";
int RECORD = 20000;
int total_count = 0;
char *dsql = (char *)"drop table alltest";
char *csql = (char *)"CREATE TABLE alltest(a int,b decimal(30,10),c timestamp)";
//初始化环境句柄
r = ACIEnvCreate(&env, ACI_DEFAULT, 0, 0, 0, 0, 0, 0);
checkerr(err, r);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(env, (void**)&err, ACI_HTYPE_ERROR, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&srv, ACI_HTYPE_SERVER, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&svc, ACI_HTYPE_SVCCTX, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&ses, ACI_HTYPE_SESSION, 0, 0);
checkerr(err, r);
r = ACIServerAttach(srv, err, (const OraText *)dblink, (dblink != NULL ? (sb4)strlen(dblink) : 0), ACI_DEFAULT);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, srv, sizeof(srv), ACI_ATTR_SERVER, err);
checkerr(err, r);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbuser, (ub4)strlen(dbuser), ACI_ATTR_USERNAME, err);
checkerr(err, r);
r = ACIAttrSet(ses, ACI_HTYPE_SESSION, dbpwd, (ub4)strlen(dbpwd), ACI_ATTR_PASSWORD, err);
checkerr(err, r);
r = ACISessionBegin(svc, err, ses, ACI_CRED_RDBMS, ACI_DEFAULT);
checkerr(err, r);
r = ACIAttrSet(svc, ACI_HTYPE_SVCCTX, ses, sizeof(ses), ACI_ATTR_SESSION, err);
checkerr(err, r);
r = ACIHandleAlloc(env, (void**)&stmt, ACI_HTYPE_STMT, 0, 0);
checkerr(err, r);
r = ACIHandleAlloc(env, (void **)&imp, ACI_HTYPE_BULKIMP, 0, 0);
//删除表
r = ACIStmtPrepare(stmt, err, (OraText*)dsql, strlen((char*)dsql), ACI_NTV_SYNTAX, ACI_DEFAULT);
ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
//创建表
r = ACIStmtPrepare(stmt, err, (OraText*)csql, strlen((char*)csql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(svc, stmt, err, 1, 0, NULL, NULL, ACI_DEFAULT);
checkerr(err, r);
r = ACIAttrSet(imp, ACI_HTYPE_BULKIMP, &bufferSize, sizeof(bufferSize), ACI_ATTR_BUF_SIZE, err);
r = ACIAttrSet(imp, ACI_HTYPE_BULKIMP, &sdwRowsPerBatch, sizeof(sdwRowsPerBatch), ACI_ATTR_NUM_ROWS, err);
r = ACIBulkInit(svc, imp, NULL, (char *)"ALLTEST", NULL, err);
checkerr(err, r);
for (int i = 0; i < RECORD; i++)
{
r = ACIBulkSetColumn(imp, SQLT_CHR, (void*)noval, (sb2)sizeof(noval), err);
r = ACIBulkSetColumn(imp, SQLT_CHR, (void*)doubleval, (sb2)sizeof(doubleval), err);
r = ACIBulkSetColumn(imp, SQLT_CHR, (void *)charval, (sb2)sizeof(charval), err);
r = ACIBulkEndRow(imp, err);//结束一行的 插入
checkerr(err, r);
if (i%sdwRowsPerBatch == 0 && i > 0)
{
r = ACIBulkExec(imp, err);
checkerr(err, r);
}
}
r = ACIBulkExec(imp, err);
checkerr(err, r);
r = ACITransCommit(svc, err, ACI_DEFAULT);
r = ACIAttrGet(imp, ACI_HTYPE_BULKIMP, &total_count, 0, ACI_ATTR_NUM_ROWS, err);
printf("total_count=%d\n", total_count);
r = ACIHandleFree(imp, ACI_HTYPE_BULKIMP);
r = ACISessionEnd(svc, err, ses, ACI_DEFAULT);
}
直接路径加载¶
4列：int、decimal(30,10) 、datetime，clob的导入，设置日期格式字符串，大对象支持1M
#define _CRT_SECURE_NO_DEPRECATE
#define _CRT_SECURE_NO_WARNINGS
#include <iostream>
#include <aci.h>
#include <stdlib.h>
using namespace std;
#define COL_NUM 1500
#define MAX_NAME_LEN 128 //最大的对像名长度
#define MAX_FETCH_ROWS 10 //在获取语句或存贮过程返回的结果集时，一次性从服务器提取的行数
#define TABLE_NAME "dp_test" //表名
#define MAX_COL_NUM 4 //表的列数
#define MAX_RECORD 1 //加载数据的行数
#define MAX_BUFFER_RECORD 10 //数据缓冲区行数
//定义列数据的属性信息的结构
typedef struct ColAttr {
char col_name[MAX_NAME_LEN + 1];
ub2 data_type; //ACI的数据类型
ub4 data_size; //数据的大小，以字节计算
ub4 data_offset; //注意，这个参数是留给ACI内部自己使用的
ub2 precision; //数据类型的精度
ub1 scale; //数据的刻度
void *data; //注意，如果在使用行绑定的方式调用DirPathLoadStream，那么这个参数是没有作用
sb2 *indp; //这个参数可以为NULL，表示该列的数据大小没有空值，如果要指定该列的某一行为NULL
}ColAttr_t;
ACIEnv *m_penv = NULL;/*环境句柄*/
ACIError *m_perr = NULL;/*错误句柄*/
ACIServer *m_psrv = NULL;/*服务器句柄*/
ACISvcCtx *m_psvc = NULL;/*服务上下文句柄*/
ACISession *m_pses = NULL;/*会话句柄*/
ACIStmt *m_pstmt = NULL;/*语句句柄*/
ACIBind *m_bnd = NULL;/*绑定句柄*/
ACIDefine *m_def = NULL;/*定义句柄*/
ACIDirPathCtx *dpctx;
ACIDirPathColArray *dpca;
ACIDirPathStream *dpstr;
ACIParam *m_pprm;
ACIDefine * def[10];
char *m_dblink = (char *)"localhost:2003/OSRDB";
char *m_dbuser = (char *)"SYSDBA";
char *m_dbpwd = (char *)"szoscar55";
#define SET_COLINFO(COLINFO_name,COLINFO_type,COLINFO_size) \
strcpy(col_info[i].col_name,COLINFO_name); \
col_info[i].data_type = COLINFO_type; \
col_info[i].data_size = COLINFO_size;
void checkerr(ACIError* err, sword status)
{
text errbuf[512];
ub4 buflen = 0;
sb4 errcode;
switch (status)
{
case ACI_SUCCESS:
break;
case ACI_SUCCESS_WITH_INFO:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - ACI_SUCCESS_WITH_INFO:%s\n", errbuf);
exit(-1);
break;
case ACI_NEED_DATA:
printf("Error - ACI_NEED_DATA\n");
exit(-1);
break;
case ACI_NO_DATA:
printf("Error - ACI_NO_DATA\n");
exit(-1);
break;
case ACI_ERROR:
ACIErrorGet(err, (ub4)1, (text *)NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4)ACI_HTYPE_ERROR);
printf("Error - %d %s\n", errcode, errbuf);
exit(-1);
break;
case ACI_INVALID_HANDLE:
printf("Error - ACI_INVALID_HANDLE\n");
exit(-1);
break;
case ACI_STILL_EXECUTING:
printf("Error - ACI_STILL_EXECUTE\n");
exit(-1);
break;
case ACI_CONTINUE:
printf("Error - ACI_CONTINUE\n");
exit(-1);
break;
default:
break;
}
}
void droptable(const char* schema, const char *table, char**ppsql, ub2 cols)
{
sword r = ACI_SUCCESS;
ub4 sql_size = 0;
char *sql = NULL;
char *sql_temp = NULL;
ACIStmt *stmthp = NULL;
*ppsql = NULL;
sql_size = (MAX_NAME_LEN + 30) * cols;
sql = (char*)malloc(sql_size);
*ppsql = sql;
sql_temp = sql;
strcpy(sql_temp, "DROP TABLE ");
if (schema && schema[0] != 0)
{
strcat(sql_temp, schema);
strcat(sql_temp, ".");
sql_temp += strlen(sql_temp);
}
strcat(sql_temp, table);
ACIHandleAlloc((dvoid *)m_penv, (dvoid **)&m_perr, ACI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
ACIHandleAlloc(m_penv, (void**)&stmthp, ACI_HTYPE_STMT, 0, 0);
ACIStmtPrepare(stmthp, m_perr, (OraText*)sql_temp, strlen(sql_temp), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, stmthp, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
}
bool GetDataTypeName(ub2 dtype, ub4 data_size, char* type_name)
{
*type_name = 0;
switch (dtype)
{
case SQLT_CHR:
case SQLT_STR:
sprintf(type_name, "VARCHAR(%d)", data_size);
break;
case SQLT_VNU:
strcpy(type_name, "NUMBER");
break;
case SQLT_NUM:
strcpy(type_name, "NUMERIC");
break;
case SQLT_INT:
strcpy(type_name, "INT");
break;
case SQLT_FLT:
strcpy(type_name, "FLOAT");
break;
case SQLT_BLOB:
strcpy(type_name, "BLOB");
break;
case SQLT_CLOB:
strcpy(type_name, "CLOB");
break;
case SQLT_ODT:
case SQLT_DAT:
strcpy(type_name, "DATE");
break;
case SQLT_TIMESTAMP:
strcpy(type_name, "TIMESTAMP");
break;
case SQLT_PDN:
strcpy(type_name, "DECIMAL");
break;
default:
return FALSE;
}
return TRUE;
}
bool CreateTable(const char* schema, const char *table, ColAttr_t *col, ub2 cols, char**ppsql)
{
sword r = ACI_SUCCESS;
ub4 sql_size = 0;
char *sql = NULL;
char *sql_temp = NULL;
ub2 i = 0;
char type_name[128] = { 0 };
*ppsql = NULL;
sql_size = (MAX_NAME_LEN + 30) * cols;
sql = (char*)malloc(sql_size);
if (sql == NULL)
{
return FALSE;
}
*ppsql = sql;
sql_temp = sql;
strcpy(sql_temp, "CREATE TABLE ");
sql_temp += strlen(sql_temp);
if (schema && schema[0] != 0)
{
strcat(sql_temp, schema);
strcat(sql_temp, ".");
sql_temp += strlen(sql_temp);
}
strcat(sql_temp, table);
strcat(sql_temp, "(");
sql_temp += strlen(sql_temp);
for (i = 0; i < cols; i++)
{
if (!GetDataTypeName(col[i].data_type, col[i].data_size, type_name))
{
return FALSE;
}
if (i == cols - 1)
{
sprintf(sql_temp, "%s %s)", col[i].col_name, type_name);
}
else
{
sprintf(sql_temp, "%s %s,", col[i].col_name, type_name);
}
sql_temp += strlen(sql_temp);
}
ACIHandleAlloc((dvoid *)m_penv, (dvoid **)&m_perr, ACI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
if (schema != NULL && strlen(schema) != 0)
{
char schemasql[1024];
sprintf(schemasql, "CREATE SCHEMA %s", schema);
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)schemasql, strlen(schemasql), ACI_NTV_SYNTAX, ACI_DEFAULT);
ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
}
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)sql, strlen(sql), ACI_NTV_SYNTAX, ACI_DEFAULT);
r = ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, NULL, NULL, ACI_DEFAULT);
return TRUE;
}
bool SetAttrInfo(const char *schema, const char *table, ColAttr_t *col, ub2 cols, ub4 rows, ub4 *nrows, ub1 input_type, ACIDirPathCtx **dpctxpp, ACIDirPathColArray **dpcapp, ACIDirPathStream **dpstrpp, ACIParam **colLstDescpp, ACIError *errhp)
{
ub2 i = 0;
ub2 pos = 0;
ACIParam *colDesc = NULL; /* column parameter descriptor */
ub4 buf_size = 0;
ub1 dirpathinput = input_type;//ACI_DIRPATH_INPUT_STREAM//ACI_DIRPATH_INPUT_TEXT
ub2 dtype = SQLT_CHR;
char dfltdatemask_tbl[100] = "YYYY-MM-DD HH24:MI:SS";
ACIParam *colLstDesc = NULL; /* column list parameter handle */
ACIDirPathCtx *dpctx = NULL; /* direct path context */
ACIDirPathColArray *dpca = NULL; /* direct path column array handle */
ACIDirPathStream *dpstr = NULL; /* direct path stream handle */
ACIStmt *stmthp = NULL; //这个参数无作用，只是为了可以使用宏DCI_CHECK
*dpctxpp = NULL;
*dpcapp = NULL;
*dpstrpp = NULL;
ACIHandleAlloc((dvoid *)m_penv, (dvoid **)&m_perr, ACI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
ACIHandleAlloc((dvoid *)m_penv, (dvoid **)&dpctx, (ub4)ACI_HTYPE_DIRPATH_CTX, (size_t)0, (dvoid **)0);
*dpctxpp = dpctx;
ACIAttrSet((dvoid *)dpctx, (ub4)ACI_HTYPE_DIRPATH_CTX, (dvoid *)table, (ub4)strlen((const char *)table), (ub4)ACI_ATTR_NAME, m_perr);
if (schema && strlen(schema) != 0)
{
ACIAttrSet((dvoid *)dpctx, (ub4)ACI_HTYPE_DIRPATH_CTX, (dvoid *)schema, (ub4)strlen((const char *)schema), (ub4)ACI_ATTR_SCHEMA_NAME, m_perr);
}
/* Note: setting tbl default datemask will not trigger client library
* to check strings for dates - only setting column datemask will.
*/
ACIAttrSet((dvoid *)dpctx, (ub4)ACI_HTYPE_DIRPATH_CTX, (dvoid *)dfltdatemask_tbl, (ub4)strlen((const char *)dfltdatemask_tbl), (ub4)ACI_ATTR_DATEFORMAT, m_perr);
/* set the data input type to be text */
ACIAttrSet((dvoid *)dpctx, ACI_HTYPE_DIRPATH_CTX, (dvoid *)&dirpathinput, (ub4)0, ACI_ATTR_DIRPATH_INPUT, m_perr);
/* set number of columns to be loaded */
ACIAttrSet((dvoid *)dpctx, (ub4)ACI_HTYPE_DIRPATH_CTX, (dvoid *)&cols, (ub4)0, (ub4)ACI_ATTR_NUM_COLS, m_perr);
/* get the column parameter list */
ACIAttrGet((dvoid *)dpctx, ACI_HTYPE_DIRPATH_CTX, (dvoid *)&colLstDesc, (ub4 *)0, ACI_ATTR_LIST_COLUMNS, m_perr);
for (i = 0, pos = 1; i < cols; i++, pos++)
{
/* get parameter handle on the column */
ACIParamGet((CONST dvoid *)colLstDesc, (ub4)ACI_DTYPE_PARAM, m_perr, (dvoid **)&colDesc, pos);
*colLstDescpp = colLstDesc;
/* column name */
ACIAttrSet((dvoid *)colDesc, (ub4)ACI_DTYPE_PARAM, (dvoid *)col[i].col_name, (ub4)strlen(col[i].col_name), (ub4)ACI_ATTR_NAME, m_perr);
/* column type */
if (dirpathinput == ACI_DIRPATH_INPUT_STREAM)
{
ACIAttrSet((dvoid *)colDesc, (ub4)ACI_DTYPE_PARAM, (dvoid *)&col[i].data_type, (ub4)0, (ub4)ACI_ATTR_DATA_TYPE, m_perr);
}
else
{
ACIAttrSet((dvoid *)colDesc, (ub4)ACI_DTYPE_PARAM, (dvoid *)&dtype, (ub4)0, (ub4)ACI_ATTR_DATA_TYPE, m_perr);
}
/* max data size */
ACIAttrSet((dvoid *)colDesc, (ub4)ACI_DTYPE_PARAM, (dvoid *)&col[i].data_size, (ub4)0, (ub4)ACI_ATTR_DATA_SIZE, m_perr);
/* free the parameter handle to the column descriptor */
ACIDescriptorFree((dvoid *)colDesc, ACI_DTYPE_PARAM);
*colLstDescpp = NULL;
}
buf_size = 64 * 1024;
ACIAttrSet((dvoid *)dpctx, (ub4)ACI_HTYPE_DIRPATH_CTX, (dvoid *)&buf_size, (ub4)0, (ub4)ACI_ATTR_BUF_SIZE, m_perr);
//ACIHandleFree((dvoid *)m_perr, ACI_HTYPE_ERROR);
return TRUE;
}
int main()
{
sword r = ACI_SUCCESS;
ub4 i;
int j = 1;
char *sql = NULL;
ub4 rownum = 0;
ub2 colIdx = 0;
ub1 *cvalp = NULL;
ColAttr_t col_info[MAX_COL_NUM]; //MAX_COL_NUM=10
//定义数据缓冲区
ub1* data_buffer;
ub1* data_bufder_point;
int data_buffer_size = 0;
//定义加载控制变量
int all_record = 10;
ub4 buffer_record = 1;
//初始化环境句柄
r = ACIInitialize(ACI_DEFAULT, NULL, NULL, NULL, NULL);
r = ACIEnvInit(&m_penv, ACI_DEFAULT, 0, 0);
//分配并初始化各类应用句柄
r = ACIHandleAlloc(m_penv, (void**)&m_perr, ACI_HTYPE_ERROR, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psrv, ACI_HTYPE_SERVER, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_psvc, ACI_HTYPE_SVCCTX, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pses, ACI_HTYPE_SESSION, 0, 0);
r = ACIHandleAlloc(m_penv, (void**)&m_pstmt, ACI_HTYPE_STMT, 0, 0);
//连接数据库
r = ACILogon(m_penv, m_perr, &m_psvc, (const OraText *)m_dbuser, strlen(m_dbuser), (const OraText *)m_dbpwd, strlen(m_dbpwd), (const OraText *)m_dblink, strlen(m_dblink));
//设定表信息
for (i = 0; i < MAX_COL_NUM; i++)
{
switch (i)
{
case 0:SET_COLINFO("col_1", SQLT_INT, sizeof(int)); break;
case 1:SET_COLINFO("col_2", SQLT_PDN, 30); break;
case 2:SET_COLINFO("col_3", SQLT_CLOB, 50); break;
case 3:SET_COLINFO("col_4", SQLT_TIMESTAMP, 50); break;
}
}
//计算一行数据的总字节大小
for (i = 0; i < MAX_COL_NUM; i++)
{
data_buffer_size += col_info[i].data_size + sizeof(sb2);
}
//分配数据缓冲区
data_buffer = (ub1*)calloc(MAX_BUFFER_RECORD, data_buffer_size);
r = ACIHandleAlloc(m_penv, (void**)&dpctx, ACI_HTYPE_DIRPATH_CTX, 0, 0);
//prepare的准备工作
droptable(NULL, TABLE_NAME, &sql, MAX_COL_NUM);
r = CreateTable(NULL, TABLE_NAME, col_info, MAX_COL_NUM, &sql);
r = SetAttrInfo(NULL, TABLE_NAME, col_info, MAX_COL_NUM,
buffer_record, (ub4*)data_buffer, ACI_DIRPATH_INPUT_STREAM,
&dpctx, &dpca, &dpstr, &m_pprm, m_perr);
//DpPrepare
r = ACIDirPathPrepare(dpctx, m_psvc, m_perr);
r = ACIHandleAlloc(dpctx, (void**)&dpca,
ACI_HTYPE_DIRPATH_COLUMN_ARRAY, 0, 0);
r = ACIHandleAlloc(dpctx, (void**)&dpstr, ACI_HTYPE_DIRPATH_STREAM, 0, 0);
while (all_record > 0)
{
//决定一批次数据行数，最多5行
if (all_record > 5)
buffer_record = 5;
else
buffer_record = all_record;
//在数据缓冲区中构造一批次数据
data_bufder_point = data_buffer;
for (i = 0; i < buffer_record; i++)
{
//第i行第一列
*((int*)data_bufder_point) = j++;
data_bufder_point += col_info[0].data_size;
*((sb2*)data_bufder_point) = 0;
data_bufder_point += sizeof(sb2);
//第i行第二列
*(data_bufder_point) = (float)12345.123;
data_bufder_point += col_info[1].data_size;
*((sb2*)data_bufder_point) = (rand() % 2) - 1;
data_bufder_point += sizeof(sb2);
//第i行第三列
strcpy((char*)data_bufder_point, "12345");
data_bufder_point += col_info[2].data_size;
*((sb2*)data_bufder_point) = (rand() % 2) - 1;
data_bufder_point += sizeof(sb2);
//第i行第四列
sprintf((char*)data_bufder_point,
"%04d-%02d-%02d %02d:%02d:%02d", 2011,
(rand() % 12 + 1), (rand() % 28 + 1), (rand() % 24),
(rand() % 60), (rand() % 60));
data_bufder_point += col_info[3].data_size;
*((sb2*)data_bufder_point) = (rand() % 2) - 1;
data_bufder_point += sizeof(sb2);
}
r = ACIDirPathColArrayReset(dpca, m_perr);
r = ACIDirPathStreamReset(dpstr, m_perr);
cvalp = (ub1*)data_buffer;
for (rownum = 0; rownum < buffer_record; rownum++)
{
for (colIdx = 0; colIdx < MAX_COL_NUM; colIdx++)
{
if (*(sb2*)(cvalp + col_info[colIdx].data_size) == -1)
{
r = ACIDirPathColArrayEntrySet(dpca, m_perr, rownum,
colIdx, cvalp, col_info[colIdx].data_size,
ACI_DIRPATH_COL_NULL);
}
else
{
r = ACIDirPathColArrayEntrySet(dpca, m_perr, rownum, colIdx,
cvalp, col_info[colIdx].data_size, ACI_DIRPATH_COL_COMPLETE);
}
cvalp += col_info[colIdx].data_size + sizeof(sb2);
}
}
/* Convert Col Array to Stream */
r = ACIDirPathColArrayToStream(dpca, dpctx, dpstr, m_perr, buffer_record, 0);
checkerr(m_perr, r);
/* Load Stream */
r = ACIDirPathLoadStream(dpctx, dpstr, m_perr);
checkerr(m_perr, r);
/*Save Data*/
r = ACIDirPathDataSave(dpctx, m_perr, ACI_DIRPATH_DATASAVE_FINISH);
checkerr(m_perr, r);
/*Finish*/
r = ACIDirPathFinish(dpctx, m_perr);
//完成一批次加载，减少总行数
all_record -= MAX_BUFFER_RECORD;
}// end of while(all_record > 0)
int result = 0;
char *stmt = (char *)"select count(*) from dp_test";
ACIStmtPrepare(m_pstmt, m_perr, (OraText*)stmt, strlen(stmt), ACI_NTV_SYNTAX, ACI_DEFAULT);
ACIDefineByPos(m_pstmt, &def[0], m_perr, 1, &result, sizeof(result), SQLT_INT, NULL, NULL, NULL, ACI_DEFAULT);
ACIStmtExecute(m_psvc, m_pstmt, m_perr, 1, 0, 0, 0, ACI_DEFAULT);
return 0;
}