ADO连接Oracle、MySql数据库
1.
sprintf(datasrc,"%s","upapp");
sprintf(connstr,"%s%s","Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Data Source=",datasrc);//连接oracle，依赖tnsnames.ora
这里的upapp要在tnsnames.ora中配置：
2.sprintf(connstr,"%s","DSN=MySql_ODBC;server=localhost;database=test;");//连接mysql
其中 DSN=MySql_ODBC要根据实际情况自己配置。
官网odbc驱动下载： http://dev.mysql.com/downloads/connector/odbc/
两个都下载安装，只安装64bit的话，VS x86工程连接mysql会报错。
控制面板 -> 系统和安全 -> 管理工具 -> 数据源ODBC -> 用户DSN -> 添加
如果看不到ODBC数据源，就用命令行启动 ：
C:\>cd \windows\SysWOW64
C:\Windows\SysWOW64>odbcad32.exe
DBOperation.h
#import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")
class CDBOperation
{
public:
//初始化数据库操作需要的对象
CDBOperation(void);
~CDBOperation(void);
//连接至数据库
bool ConnToDB(char *ConnectionString, char *UserID, char *Password);
//数据库操作函数
//查询操作 删除以及添加
_RecordsetPtr ExecuteWithResSQL(const char *);
//bool ExecuteNoResSQL(const char *);//delete and add
private:
void PrintErrorInfo(_com_error &);
private:
//初始化数据库连接、命令、记录集
_ConnectionPtr CreateConnPtr();
_CommandPtr CreateCommPtr();
_RecordsetPtr CreateRecsetPtr();
private:
//数据库连接需要的连接、命令操作对象
_ConnectionPtr m_pConnection;
_CommandPtr m_pCommand;
_RecordsetPtr m_pRecordset;
bool m_bIsOpenSuccess;
};
DBOperation.cpp
#include "DBOperation.h"
CDBOperation::CDBOperation(void)
{
CoInitialize(NULL);
m_pConnection = CreateConnPtr();
m_pCommand = CreateCommPtr();
m_pRecordset = CreateRecsetPtr();
m_bIsOpenSuccess = false;
}
CDBOperation::~CDBOperation(void)
{
//m_pCommand->Close();
if (m_bIsOpenSuccess)
{
m_pConnection->Close();
}
}
bool CDBOperation::ConnToDB(char *ConnectionString, char *UserID, char *Password)
{
if (NULL == m_pConnection)
{
printf("Failed to create connection\n");
return false;
}
try
{
HRESULT hr = m_pConnection->Open(_bstr_t(ConnectionString), _bstr_t(UserID), _bstr_t(Password), NULL);
if (TRUE == FAILED(hr))
{
return false;
}
m_pCommand->ActiveConnection = m_pConnection;
m_bIsOpenSuccess = true;
return true;
}
catch(_com_error &e)
{
PrintErrorInfo(e);
return false;
}
}
_RecordsetPtr CDBOperation::ExecuteWithResSQL(const char *sql)
{
//已经在连接至数据库的时候进行判断了
//if (NULL == m_pCommand || 0 == m_pConnection->State)
//{
// printf("Failed to create command OR the state of connection is zero\n");
// return NULL;
//}
//char *query = new char;
//strcpy(query, sql);
try
{
m_pCommand->CommandText = _bstr_t(sql);
_RecordsetPtr pRst = m_pCommand->Execute(NULL, NULL, adCmdText);
return pRst;
//_variant_t ra;
//_RecordsetPtr pRst = m_pConnection->Execute((_bstr_t)query, &ra, adCmdText);
}
catch(_com_error &e)
{
PrintErrorInfo(e);
return NULL;
}
}
//bool CDBOperation::ExecuteNoResSQL(const char *sql)
//{
// //if (NULL == m_pCommand || 0 == m_pConnection->State)
// //{
// // printf();
// //}
// try
// {
// char *query = NULL;
// strcpy(query, sql);
// m_pCommand->CommandText = (_bstr_t)query;
//
// }
//}
void CDBOperation::PrintErrorInfo(_com_error &e)
{
printf("Error infomation are as follows\n");
printf("ErrorNo: %d\nError Message:%s\nError Source:%s\nError Description:%s\n", e.Error(), e.ErrorMessage(), (LPCTSTR)e.Source(), (LPCTSTR)e.Description());
}
_ConnectionPtr CDBOperation::CreateConnPtr()
{
HRESULT hr;
_ConnectionPtr connPtr;
hr = connPtr.CreateInstance(__uuidof(Connection));
if (FAILED(hr) == TRUE)
{
return NULL;
}
return connPtr;
}
_CommandPtr CDBOperation::CreateCommPtr()
{
HRESULT hr;
_CommandPtr commPtr;
hr = commPtr.CreateInstance(__uuidof(Command));
if (FAILED(hr) == TRUE)
{
return NULL;
}
return commPtr;
}
_RecordsetPtr CDBOperation::CreateRecsetPtr()
{
HRESULT hr;
_RecordsetPtr recsetPtr;
hr = recsetPtr.CreateInstance(__uuidof( Command));
if (FAILED(hr) ==TRUE)
{
return NULL;
}
return recsetPtr;
}
mai.cpp
#include <iostream>
#include <string>
#include <stdio.h>
#include <map>
#include <vector>
#include "DBOperation.h"
using namespace std;
int main()
{
CDBOperation dbOper;
char connstr[128] = { 0 };
char datasrc[16] = { 0 };
//sprintf(datasrc,"%s","upap");
//sprintf(connstr,"%s%s","Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Data Source=",datasrc);//连接Oracle
sprintf_s(connstr, "%s", "DSN=mysql_odbc;server=172.16.8.110;database=scistock;");//连接MySql DSN=mysql_odbc
bool bConn = dbOper.ConnToDB(connstr, "root", "123456");
if (false == bConn)
{
printf("连接数据库出现错误\n");
system("PAUSE");
return 0;
}
_RecordsetPtr pRst;
map<string, int, string> map_BigDataStrategy;
//执行查询语句
//char *sql = "select * from TSTUDENT";
char sql[128] = { 0 };
char gscode[16] = { 0 };
//sprintf(gscode,"%s","机构热荐");
//int bigdataStrategy_CalcDate = 20161104;
//sprintf(sql,"%s%s%s%d%s","select SEC_MAR_PAR,STK_CODE from upapp.STK_BASIC_INFO WHERE STK_UNI_CODE in (select STK_UNI_CODE from upapp.STRA_FACT_SCORE WHERE STRA_UNI_CODE in (select STRA_UNI_CODE from upapp.STRA_BASIC_INFO WHERE STRA_NAME = '",gscode,"') AND STA_DATE = to_date('",bigdataStrategy_CalcDate,"','yyyymmdd'))");
//执行查询语句
string sSql = "select * from calcgsdata_once where gscode = 'QQQ';";
pRst = dbOper.ExecuteWithResSQL(sSql.c_str());
if (NULL == pRst)
{
printf("查询数据出现错误！\n");
system("PAUSE");
return 0;
}
if (pRst->adoEOF)
{
pRst->Close();
printf("There is no records in this table\n");
return 0;
}
vector<string> vFields;
int iColNum = pRst->Fields->Count;
_variant_t vaIndex;
vaIndex.vt = VT_I2;
for (int i = 0; i < iColNum; ++i)
{
vaIndex.iVal = i;
string sField = (const char *)pRst->Fields->GetItem(vaIndex)->Name;
vFields.push_back(sField);
}
_variant_t vaGpCode, vaYmd;
int ymd = 0;
string strSet;
string sGpCode;
while (!pRst->adoEOF)
{
if (std::count(vFields.begin(), vFields.end(), "gpcode") > 0)
{
vaGpCode = pRst->GetCollect(_variant_t("gpcode"));
}
if (std::count(vFields.begin(), vFields.end(), "ymd") > 0)
{
vaYmd = pRst->GetCollect(_variant_t("ymd"));
}
sGpCode = (char *)_bstr_t(vaGpCode);
strSet = sGpCode.substr(0, 2);
sGpCode = sGpCode.substr(2);
if (vaYmd.iVal != NULL)
{
ymd = vaYmd.iVal;
}
pRst->MoveNext();
}
//执行插入语句
sprintf_s(sql, "insert into calcgsdata_once(gscode, gpcode, f1) values('%s', '%s', %f)", "TEST", "SH880002", 1.0);
pRst = dbOper.ExecuteWithResSQL(sql);
if (NULL != pRst)
{
printf("插入数据成功\n");
}
//执行删除语句
sprintf_s(sql, "delete from calcgsdata_once where gscode = '%s'", "TEST");
pRst = dbOper.ExecuteWithResSQL(sql);
if (NULL != pRst)
{
printf("删除数据成功\n");
}
system("PAUSE");
return 0;
}
浙公网安备 33010602011771号