C++接続oracleデータベース
14786 ワード
方式1、OCCIを使用する:
ダイレクトコード
方法2:OCIを使用して、私たちのプロジェクトで使用している方法は、複雑に見えます.
コンパイル検証はありませんが、間違いがあればご覧ください.
明らかにOCCIはOCIの進級版であり、個人的にはハードウェアが制約条件ではない今日、OCCIとOCIの実行効率は悪くないと考えているが、プログラマーの呼び出しの便利さから言えば、明らかにOCCIは便利で多く、今後のプロジェクトが必要であれば、本人はOCCIを選ぶことができる.
ダイレクトコード
#include <iostream>
#include <string>
#include <vector>
#include <occi.h>
using namespace oracle::occi;
using std::vector;
using namespace std;
class conndba
{
private:
Environment *env;
Connection *conn;
Statement *stmt;
public:
conndba(string user, string password, string db)
{
env = Environment::createEnvironment(Environment::DEFAULT);
conn = env->createConnection(user, password, db);
}
~conndba()
{
env->terminateConnection(conn);
Environment::terminateEnvironment(env);
}
void insertBind(string s1, string s2, string s3, string s4)
{
string sqlStmt = "INSERT INTO t_user(userid, username, loginname, createdate) VALUES (:1, :2, :3, :4)";
stmt=conn->createStatement (sqlStmt);
try
{
stmt->setString(1, s1);
stmt->setString(2, s2);
stmt->setString(3, s3);
stmt->setString(4, s4);
stmt->executeUpdate();
cout << "insert - Success" << endl;
}
catch (SQLException ex)
{
cout << "Exception thrown for insertBind" << endl;
cout << "Error number: " << ex.getErrorCode() << endl;
cout << ex.getMessage() << endl;
}
conn->terminateStatement(stmt);
}
void updateRow(string s1, string s2)
{
string sqlStmt = "UPDATE t_user SET userid = :x WHERE username = :y";
stmt = conn->createStatement(sqlStmt);
try
{
stmt->setString(1, s2);
stmt->setString(2, s1);
stmt->executeUpdate();
cout << "update - Success" << endl;
}
catch (SQLException ex)
{
cout << "Exception thrown for updateRow" << endl;
cout << "Error number: " << ex.getErrorCode() << endl;
cout << ex.getMessage() << endl;
}
conn->terminateStatement(stmt);
}
void deleteRow(string s1)
{
string sqlStmt = "DELETE FROM t_user WHERE userid = :x";
stmt = conn->createStatement(sqlStmt);
try
{
stmt->setString(1, s1);
stmt->executeUpdate();
cout << "delete - Success" << endl;
}
catch (SQLException ex)
{
cout << "Exception thrown for deleteRow" << endl;
cout << "Error number: " << ex.getErrorCode() << endl;
cout << ex.getMessage() << endl;
}
conn->terminateStatement(stmt);
}
void displayAllRows()
{
string sql =
"select userid, username, loginname, createdate from t_user";
stmt = conn->createStatement(sql);
ResultSet *rs = stmt->executeQuery();
try
{
while (rs->next())
{
cout << "userid: " << rs->getInt(1) << "\t"
cout << "username: " << rs->getString(2) << "\t"
cout << "loginname: " << rs->getString(3) << "\t"
cout << "createdate: " << rs->getString(4) << endl;
}
}
catch (SQLException ex)
{
cout << "Exception thrown for displayAllRows" << endl;
cout << "Error number: " << ex.getErrorCode() << endl;
cout << ex.getMessage() << endl;
}
stmt->closeResultSet(rs);
conn->terminateStatement(stmt);
}
void displayAllRowsDesc()
{
string sql =
"select userid, username, loginname, createdate from t_user order by userid desc";
stmt = conn->createStatement(sql);
ResultSet *rs = stmt->executeQuery();
try
{
while (rs->next())
{
cout << "userid: " << rs->getInt(1) << "\t"
cout << "username: " << rs->getString(2) << "\t"
cout << "loginname: " << rs->getString(3) << "\t"
cout << "createdate: " << rs->getString(4) << endl;
}
}
catch (SQLException ex)
{
cout << "Exception thrown for displayAllRows" << endl;
cout << "Error number: " << ex.getErrorCode() << endl;
cout << ex.getMessage() << endl;
}
stmt->closeResultSet(rs);
conn->terminateStatement(stmt);
}
};
int main(int argc, char *argv[])
{
string user = "UOP_ACT4";
string password = "123456";
string db = "hyacttst";
conndba *demo = new conndba(user, password, db);
cout << " :" << endl;
demo->displayAllRowsDesc();
cout << " id !" << endl;
cout << " id:" << argv[1] << endl;
demo->deleteRow(argv[1]);
cout << " id !" << endl;
demo->displayAllRows();
cout << " !";
delete (demo);
}
方法2:OCIを使用して、私たちのプロジェクトで使用している方法は、複雑に見えます.
#include <oci.h>
#include <iostream>
#include <string>
#include <string.h>
#include <stdlib.h>
using namespace std;
//
struct result
{
char ename[20];
char cname[20];
result()
{
memset(ename, '\0', sizeof(ename));
memset(cname, '\0', sizeof(cname));
}
};
int main()
{
// OCI
OCIEnv *envhpp = NULL;
//
OCIServer *servhpp = NULL;
// OCI
OCIError *errhpp = NULL;
//
OCISession *usrhpp = NULL;
//
OCISvcCtx *svchpp = NULL;
//
OCIStmt *stmthpp = NULL;
string server = "mydb";
// OCI , 。
sword swResult = OCIEnvCreate(&envhpp, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);
if (swResult != OCI_SUCCESS && swResult != OCI_SUCCESS_WITH_INFO)
{
cout 《 "Oracle environment initialization error!" 《 endl;
exit(1);
}
cout 《 "Oracle environment initialization success!" 《 endl;
//
OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
//
OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&servhpp, OCI_HTYPE_SERVER, (size_t)0, (dvoid **)0);
// ,
if (OCIServerAttach(servhpp, errhpp, (text *)server.c_str(), strlen(server.c_str()), 0) != OCI_SUCCESS)
{
int errcno;
char errbuf[512] = "";
sb4 errcode;
// OCI
OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
errcno = errcode;
cout 《 "Oracle server attach error:" 《 errbuf 《 endl;
OCIHandleFree((dvoid *)envhpp,OCI_HTYPE_ENV);
OCIHandleFree((dvoid *)servhpp,OCI_HTYPE_SERVER);
OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR);
exit(1);
}
cout 《 "Oracle server attach success!"《 endl;
/***************** ****************/
string user = "user";
string pas = "passwd";
errhpp = NULL;
//
(void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
//
(void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&svchpp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **)0);
//
(void) OCIAttrSet((dvoid *)svchpp, OCI_HTYPE_SVCCTX, (dvoid *)servhpp, (ub4)0, OCI_ATTR_SERVER, (OCIError *)errhpp);
//
(void) OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&usrhpp, (ub4)OCI_HTYPE_SESSION, (size_t) 0, (dvoid **)0);
// 、
(void) OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)user.c_str(), (ub4)strlen(user.c_str()), (ub4)OCI_ATTR_USERNAME, errhpp);
(void) OCIAttrSet((dvoid *)usrhpp, (ub4)OCI_HTYPE_SESSION, (dvoid *)pas.c_str(), (ub4)strlen(pas.c_str()), (ub4)OCI_ATTR_PASSWORD, errhpp);
//
if(OCISessionBegin(svchpp, errhpp, usrhpp, OCI_CRED_RDBMS, (ub4)OCI_DEFAULT) != OCI_SUCCESS)
{
int errcno;
char errbuf[512]=
{ '\0'};
sb4 errcode;
// OCI
OCIErrorGet((dvoid *)errhpp, (ub4)1, (text *)NULL, &errcode, (ub1 *)errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
errcno = errcode;
cout 《 "User session error:" 《 errbuf 《 endl;
OCIHandleFree((dvoid *)errhpp,OCI_HTYPE_ERROR);
OCIHandleFree((dvoid *)usrhpp,OCI_HTYPE_SESSION);
OCIHandleFree((dvoid *)svchpp,OCI_HTYPE_SVCCTX);
exit(1);
}
cout 《 "user session success!" 《 endl;
(void) OCIAttrSet((dvoid *)svchpp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *)usrhpp, (ub4)0, (ub4)OCI_ATTR_SESSION, errhpp);
/*********************** ************************/
/*************** *** SQL ******************/
errhpp = NULL;
//
if (OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&stmthpp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0) != OCI_SUCCESS)
{
cout 《 "Create STMT error !" 《 endl;
exit(1);
}
cout 《 "Create stmt success !" 《 endl;
//
OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
// Select
char sql[255] = "select col1, col2 from table1 ";
if (OCIStmtPrepare(stmthpp, errhpp, (text *)sql, (ub4)strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT) != OCI_SUCCESS)
{
cout 《 "Create prepare error!" 《 sql 《 endl;
exit(1);
}
cout 《 "Create prepare success!" 《 endl;
/********* ***********/
//
OCIDefine *bhp1 = NULL;
OCIDefine *bhp2 = NULL;
//
struct result rst;
//
ub2 datalen = 0;
// ,
char isnul[6] = "";
// ,
OCIDefineByPos(stmthpp, &bhp1, errhpp, 1, (dvoid *)&rst.ename, sizeof(rst.ename), SQLT_CHR, NULL, &datalen, NULL, OCI_DEFAULT);
OCIDefineByPos(stmthpp, &bhp2, errhpp, 2, (dvoid *)&rst.cname, sizeof(rst.cname), SQLT_STR, NULL, &datalen, NULL, OCI_DEFAULT);
// SQL
ub2 stmt_type;
OCIAttrGet ((dvoid *)stmthpp, (ub4)OCI_HTYPE_STMT, (dvoid *)&stmt_type, (ub4 *)0, (ub4)OCI_ATTR_STMT_TYPE, errhpp);
// SQL
OCIStmtExecute(svchpp, stmthpp, errhpp, (ub4)0, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);
//
int rows_fetched;
do
{
cerr 《 rst.ename《 " ";
cerr 《 rst.cname《 "
";
}
while(OCIStmtFetch2(stmthpp, errhpp, 1, OCI_FETCH_NEXT, 1, OCI_DEFAULT) != OCI_NO_DATA);
//
OCIAttrGet((CONST void *)stmthpp, OCI_HTYPE_STMT, (void *)&rows_fetched, (ub4 *)sizeof(rows_fetched), OCI_ATTR_ROW_COUNT, errhpp);
cout 《 " rows :" 《 rows_fetched 《 endl;
/*************** SQL ******************/
if (OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&stmthpp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0) != OCI_SUCCESS)
{
cout 《 "Create STMT error !" 《 endl;
exit(1);
}
cout 《 "Create stmt success !" 《 endl;
OCIHandleAlloc((dvoid *)envhpp, (dvoid **)&errhpp, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
// Insert
char sql2[255] = "insert into table1 (col1, col2) values('testoci', 'testoci')";
// Sql
if (OCIStmtPrepare(stmthpp, errhpp, (text *)sql2, (ub4)strlen(sql2), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT) != OCI_SUCCESS)
{
cout 《 "Create prepare error!" 《 sql2 《 endl;
exit(1);
}
cout 《 "Create prepare success!" 《 endl;
// SQL
OCIStmtExecute(svchpp, stmthpp, errhpp, (ub4)1, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);
//
OCILogoff(svchpp, errhpp);
//
OCIServerDetach(servhpp, errhpp, OCI_DEFAULT);
//
OCIHandleFree((dvoid *) stmthpp, OCI_HTYPE_STMT);
OCIHandleFree((dvoid *) svchpp, OCI_HTYPE_SVCCTX);
OCIHandleFree((dvoid *) servhpp, OCI_HTYPE_SERVER);
OCIHandleFree((dvoid *) errhpp, OCI_HTYPE_ERROR);
return 0;
}
コンパイル検証はありませんが、間違いがあればご覧ください.
明らかにOCCIはOCIの進級版であり、個人的にはハードウェアが制約条件ではない今日、OCCIとOCIの実行効率は悪くないと考えているが、プログラマーの呼び出しの便利さから言えば、明らかにOCCIは便利で多く、今後のプロジェクトが必要であれば、本人はOCCIを選ぶことができる.