Microsoft SQL Server ManagerがODBCを介してVSに接続する操作例プログラム
14774 ワード
データソースを作成するとき、ODBCが何ビットのVSエンジニアリングが何ビットのものであるべきか、私がここで使っているのは32ビットのODBCなので、VSエンジニアリングも32ビットのものです.
#ifndef __ODBC_H__
#define __ODBC_H__
#include
#include
#include
#include
#include
#include "sql.h"
#include "sqltypes.h"
#include "sqlext.h"
class MyODBCAPI
{
public:
MyODBCAPI();
~MyODBCAPI();
SQLHENV henv; //
SQLHDBC hdbc; //
SQLHSTMT hstmt; //
SQLRETURN retcode;
BOOL bInit;
BOOL InitODBC();
// ODBC
BOOL Connect(char* odbcName, char* userID, char* pass);
BOOL ExecSQL(char* sql);
void QueryCustomer(int iMode,char* Field, char* Target);
void InsertCustomer(char* CustomerID, char* CompanyName);
void DeleteCustomer(char* CustomerID);
};
#endif
#include
#include
#include
#include
#include
#include "sql.h"
#include "sqltypes.h"
#include "sqlext.h"
using namespace std;
#include "odbc.h"
MyODBCAPI::MyODBCAPI()
{
InitODBC(); // ODBC
}
MyODBCAPI::~MyODBCAPI()
{
if (hstmt != NULL)
{
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}
if (hdbc != NULL)
{
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (henv != NULL)
{
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
}
BOOL MyODBCAPI::InitODBC()
{
henv = NULL;
hdbc = NULL;
hstmt = NULL;
bInit = false;
//
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if ((retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
{
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
if ((retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
{
bInit = true;
}
}
return bInit;
}
BOOL MyODBCAPI::Connect(char* odbcName, char* userID, char* pass)
{
if (!bInit)
{
cout<<"Inite ODBC API failed"<return false;
}
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if ((retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
{
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (void*)10, 0);
retcode = SQLConnect(hdbc, (SQLCHAR*)odbcName, SQL_NTS, (SQLCHAR*)userID, SQL_NTS, (SQLCHAR*)pass, SQL_NTS);
if ((retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
{
cout<<"connect db successed!"<return true;
}
else
{
cout<<"connect db failed!"<return false;
}
}
else
{
cout<<"allocate handle failed!"<return false;
}
}
BOOL MyODBCAPI::ExecSQL(char* sql)
{
cout<if ((retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
{
retcode = SQLExecDirect(hstmt, (SQLCHAR*)sql, SQL_NTS);
if ((retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO))
{
cout<<"Exec sql statement successed!"<return true;
}
else
{
cout<<"Exec sql statement failed!"<return false;
}
}
else
{
cout<<"Allocate sql statement handle failed!"<return false;
}
}
void MyODBCAPI::QueryCustomer(int iMode, char* Field, char* Target)
{
if (!Connect("yyc", "Test123", "Yycwto103"))
{
cout<<"Connect db failed!"<return;
}
char sql[100];
if (iMode == 0)//
{
if (!ExecSQL("SELECT * FROM dbo.Customer"))
{
cout<<"Exec sql statement failed!"<return;
}
}
else if (iMode == 1)//
{
sprintf(sql,"SELECT * FROM dbo.Customer where %s='%s'",Field,Target);
if (!ExecSQL(sql))
{
cout<<"Exec sql statement failed!"<return;
}
}
else
{
return;
}
long cbNameLen = 500;
char* CustomerID[500];
SQLBindCol(hstmt, 1, SQL_C_CHAR,(void*)CustomerID, cbNameLen, &cbNameLen);
char* CompanyName[500];
SQLBindCol(hstmt, 2, SQL_C_CHAR,(void*)CompanyName, cbNameLen, &cbNameLen);
int i = 0;
while (SQLFetch(hstmt) == SQL_SUCCESS && i < 100)
{
i++;
if (retcode == SQL_NO_DATA_FOUND)
break;
printf("[%d]%s, %s\r
",i,CustomerID,CompanyName);
}
}
void MyODBCAPI::InsertCustomer(char* CustomerID, char* CompanyName)
{
if (!Connect("yyc", "Test123", "Yycwto103"))
{
cout<<"Connect db failed!"<return;
}
char sql[100];
sprintf(sql,"INSERT INTO Customer VALUES('%s', '%s') SELECT * FROM Customer",CustomerID,CompanyName);
if (!ExecSQL(sql))
{
cout<<"Insert customer info failed!"<return;
}
cout<<"Insert customer info successed!"<return;
}
void MyODBCAPI::DeleteCustomer(char* CustomerID)
{
if (!Connect("yyc", "Test123", "Yycwto103"))
{
cout<<"Connect db failed!"<return;
}
char sql[100];
sprintf(sql,"DELETE FROM Customer WHERE CustomerID='%s'",CustomerID);
if (!ExecSQL(sql))
{
cout<<"Delete customer info failed!"<return;
}
cout<<"Delete customer info successed!"<
#include
#include
#include
#include
#include
#include
using namespace std;
#include "odbc.h"
int main()
{
int i = 10;
char* odbcName = "yyc"; //
char* userID = "Test123"; //
char* pass = "Yycwto103"; //
time_t t;
MyODBCAPI odbc;
odbc.InitODBC();
bool bRtn = odbc.Connect(odbcName, userID, pass);//
odbc.InsertCustomer("hpe","test");//
odbc.DeleteCustomer("hpe");//
odbc.QueryCustomer(0,NULL,NULL);//
odbc.QueryCustomer(1,"CompanyName","test");//
getchar();
return 0;
}