1.1
ODBC应用程序结构
|
申请环境句柄(SQLAllocHandle)
|
|
设置环境句柄(SQLSetEnvAttr)
|
|
申请连接句柄(SQLAllocHandle)
|
|
连接数据源(SQLConnect)
|
|
设置连接属性(SQLSertConnect)
|
|
连接阶段
|
|
设置驱动程序信息(SQLGetInfo)
|
|
申请语句句柄(SQLAllocHandle)
|
|
设置语句属性(SQLSetStmt)
|
|
初始化阶段
|
|
程序主体
|
|
执行语句、处理结果和提交事务
|
ODBC应用程序结构
1.2
利用ODBC API函数完成对ODBC数据源的连接
|
释放语句句柄(SQLFreeHandle)
|
|
断开数据源连接(SQLDisconnect)
|
|
释放连接句柄(SQLFreeHandle)
|
|
结束部分
|
|
释放环境句柄(SQLFreeHandle)
|
利用ODBC API函数完成对ODBC数据源的连接
int CManuODBC::InitODBC(SQLCHAR *szDsn, SQLCHAR *szUserID, SQLCHAR *szPassword)
{
SQLRETURN retcode;
SQLHENV hEnv;
SQLHDBC hDbc;
SQLHSTMT hStmt;
retcode = SQLAllocHandle( SQL_HANDLE_ENV, NULL, &hEnv );
if ( retcode != SQL_SUCCESS )
return RT_ERROR;
else
SetEnv( hEnv );
retcode = SQLSetEnvAttr( m_hEnv, SQL_ATTR_ODBC_VERSION,
( SQLPOINTER )SQL_OV_ODBC3, SQL_IS_INTEGER );
if ( retcode != SQL_SUCCESS )
return RT_ERROR;
retcode = SQLAllocHandle( SQL_HANDLE_DBC, m_hEnv, &hDbc );
if ( retcode != SQL_SUCCESS )
return RT_ERROR;
else
SetDbc( hDbc );
try
{
retcode = SQLConnect( m_hDbc,
szDsn, ( SWORD )strlen( ( char* )szDsn ),
szUserID, ( SWORD )strlen( ( char* )szUserID ),
szPassword, ( SWORD )strlen( ( char* )szPassword ) );
}
catch( ... )
{
DispErrMsg( hStmt, retcode );
return RT_ERROR;
}
retcode = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );
if ( retcode != SQL_SUCCESS )
{
DispErrMsg( hStmt, retcode );
return RT_ERROR;
}
else
SetStmt( hStmt );
return RT_OK;
}
1.3 利用ODBC API函数执行SQL语句
int CManuODBC::ExecSQL( CString strManuDB )
{
SQLRETURN retcode;
SQLHSTMT hstmt = this->GetStmt();
retcode = SQLPrepare( hstmt, ( SQLCHAR* )( LPSTR )( LPCSTR )strManuDB, SQL_NTS );
if ( retcode != SQL_SUCCESS )
{
return RT_ERROR;
}
try
{
retcode = SQLExecute( hstmt );
if ( retcode != SQL_SUCCESS )
{
throw retcode;
}
}
catch( SQLRETURN &rtcde )
{
DispErrMsg( hstmt, retcode );
return RT_ERROR;
}
catch( ... )
{
AfxMessageBox( "发生异常，中止导入！" );
return RT_ERROR;
}
//retcode = SQLBindCol( hstmtNew, 1, SQL_C_CHAR, szName, MAXNAME, &cbName );
//SQLNumResultCols( hstmtNew, &nNum );
/*while ( SQLFetch( hstmt ) != SQL_NO_DATA )
{
if ( retcode == SQL_ERROR )
{
return;
}
if ( retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO )
{
}
}*/
return RT_OK;
}
1.4 自定义配置文件的读取
BOOL CTableInfo::ReadProfile(HANDLE hFile)
{
ULONG uBytesWrite = 0;
ULONG uFileSize = 0;
int nTbCnt;
char szInitFile[ 128 ];
CString strSecName, key1, key2, key3, strCurSecName;
CArray< DBTableConfig, DBTableConfig> tbConfig;
DBTableConfig tbConfigA;
strcpy( szInitFile, ".//DispTableConfig.INI" );
//strcpy( szInitFile, ".//EvtTableConfig.INI" );
//自定义配置文件格式如下
/*[Gerneral]
ODBCNum = 3
[001]
Dsn = DBHistory
UID = sa
PSD = 1
[002]
Dsn = DBData
UID = sa
PSD = 1
[003]
Dsn = DBEvt
UID = sa
PSD = 1*/
//读取自定义配置文件中的整形变量
/* UINT GetPrivateProfileInt(
LPCTSTR lpAppName, // address of section name
LPCTSTR lpKeyName, // address of key name
INT nDefault, // return value if key name is not found
LPCTSTR lpFileName // address of initialization filename
);
*/
nTbCnt = ::GetPrivateProfileInt( "General", "tbCount", NULL, szInitFile );
tbConfig.SetSize( nTbCnt );
WriteFile( hFile, &nTbCnt, sizeof( int ), &uBytesWrite, NULL );
for( int i=0; i<nTbCnt; i++ )
{
strSecName.Empty();
if( i < 10 )
strCurSecName.Format( "00%d", i+1 );
else
strCurSecName.Format( "0%d", i+1 );
memset( &tbConfig[i], 0, sizeof( tbConfigA ) );
//读取自定义配置文件中的字符串变量
/* DWORD GetPrivateProfileString(
LPCTSTR lpAppName, // points to section name
LPCTSTR lpKeyName, // points to key name
LPCTSTR lpDefault, // points to default string
LPTSTR lpReturnedString, // points to destination buffer
DWORD nSize, // size of destination buffer
LPCTSTR lpFileName // points to initialization filename
);*/
//读取自定义配置文件中的结构体
/*BOOL GetPrivateProfileStruct(
LPCTSTR lpszSection, // address of section name
LPCTSTR lpszKey, // address of key name
LPVOID lpStruct, // address of return buffer
UINT uSizeStruct, // size of return buffer
LPCTSTR szFile // address of initialization filename
);*/
::GetPrivateProfileString( strCurSecName, "TbName", "", tbConfig[i].szTbName, sizeof( tbConfig[i].szTbName), szInitFile );
WriteFile( hFile, tbConfig[i].szTbName, sizeof( tbConfig[i].szTbName ), &uBytesWrite, NULL );
::GetPrivateProfileString( strCurSecName, "tbChiDesc", "", tbConfig[i].szTbChiDesc, sizeof( tbConfig[i].szTbChiDesc), szInitFile );
WriteFile( hFile, tbConfig[i].szTbChiDesc, sizeof( tbConfig[i].szTbChiDesc ), &uBytesWrite, NULL );
tbConfig[i].nTbSrcCount = ::GetPrivateProfileInt( strCurSecName, "tbSrcCount", NULL, szInitFile );
WriteFile( hFile, &tbConfig[i].nTbSrcCount, sizeof( int ), &uBytesWrite, NULL );
::GetPrivateProfileString( strCurSecName, "tbSrcInfo", "", tbConfig[i].szTbSrcInfo, sizeof( tbConfig[i].szTbSrcInfo), szInitFile );
GetFieldInfo( tbConfig[i].szTbSrcInfo, hFile );
tbConfig[i].nTbFieldCount = ::GetPrivateProfileInt( strCurSecName, "tbFieldCount", NULL, szInitFile );
WriteFile( hFile, &tbConfig[i].nTbFieldCount, sizeof( int ), &uBytesWrite, NULL );
::GetPrivateProfileString( strCurSecName, "tbFieldInfo", "", tbConfig[i].szTbFieldInfo, sizeof( tbConfig[i].szTbFieldInfo ), szInitFile );
GetFieldInfo( tbConfig[i].szTbFieldInfo, hFile );
/*for( int j=1; j<=tbConfig[i].CmdTran; j++ )
{
key1.Format( "Tran_0%d_Sql", j );
key2.Format( "Tran_0%d_Para", j );
key3.Format( "Tran_0%d_Col", j );
switch( j )
{
case 1:
{
memset( tbConfig[i].Tran_01_Col, 0, 256 );
memset( tbConfig[i].Tran_01_Para, 0, 256 );
memset( tbConfig[i].Tran_01_Sql, 0, 1024 );
::GetPrivateProfileString( strCurSecName,key1, "", tbConfig[i].Tran_01_Sql, sizeof(tbConfig[i-1].Tran_01_Sql ), szInitFile );
::GetPrivateProfileString( strCurSecName,key2, "", tbConfig[i].Tran_01_Para, sizeof(tbConfig[i-1].Tran_01_Para ), szInitFile );
//tbConfig[i-1].Para1_Len=GetParaNum(tbConfig[i-1].Tran_01_Para);
::GetPrivateProfileString(strCurSecName,key3, "", tbConfig[i].Tran_01_Col, sizeof( tbConfig[i-1].Tran_01_Col ), szInitFile );
//取出配置文件中的命令参数
break;
}
case 2:
{
memset( tbConfig[i].Tran_02_Col, 0, 256 );
memset( tbConfig[i].Tran_02_Para, 0, 256 );
memset( tbConfig[i].Tran_02_Sql, 0, 1024 );
::GetPrivateProfileString( strCurSecName,key1, "", tbConfig[i].Tran_02_Sql, sizeof( tbConfig[i-1].Tran_02_Sql ), szInitFile );
::GetPrivateProfileString( strCurSecName,key2, "", tbConfig[i].Tran_02_Para, sizeof( tbConfig[i-1].Tran_02_Para ), szInitFile );
//tbConfig[i-1].Para2_Len=GetParaNum(tbConfig[i-1].Tran_02_Para);
::GetPrivateProfileString( strCurSecName, key3,"", tbConfig[i].Tran_02_Col, sizeof( tbConfig[i-1].Tran_02_Col ), szInitFile );
break;
}
case 3:
{
memset( tbConfig[i].Tran_03_Col, 0, 256 );
memset( tbConfig[i].Tran_03_Para, 0, 256 );
memset( tbConfig[i].Tran_03_Sql, 0, 1024 );
::GetPrivateProfileString( strCurSecName,key1, "", tbConfig[i].Tran_03_Sql, sizeof( tbConfig[i-1].Tran_03_Sql ),szInitFile );
::GetPrivateProfileString( strCurSecName,key2, "", tbConfig[i].Tran_03_Para, sizeof( tbConfig[i-1].Tran_03_Para ),szInitFile );
//tbConfig[i-1].Para3_Len=GetParaNum(tbConfig[i-1].Tran_03_Para);
::GetPrivateProfileString( strCurSecName,key3, "", tbConfig[i].Tran_03_Col, sizeof( tbConfig[i-1].Tran_03_Col ), szInitFile );
break;
}
default:
break;
}
}*/
}
return TRUE;
}
1.5 ODBC API函数中的对参数绑定的实现
/*SQLRETURN SQLBindParameter(
SQLHSTMT StatementHandle,
SQLUSMALLINT ParameterNumber,
SQLSMALLINT InputOutputType,
SQLSMALLINT ValueType,
SQLSMALLINT ParameterType,
SQLUINTEGER ColumnSize,
SQLSMALLINT DecimalDigits,
SQLPOINTER ParameterValuePtr,
SQLINTEGER BufferLength,
SQLINTEGER StrLen_or_IndPtr);
SQLHSTMT StatementHandle,
SQLUSMALLINT ParameterNumber,
SQLSMALLINT InputOutputType,
SQLSMALLINT ValueType,
SQLSMALLINT ParameterType,
SQLUINTEGER ColumnSize,
SQLSMALLINT DecimalDigits,
SQLPOINTER ParameterValuePtr,
SQLINTEGER BufferLength,
SQLINTEGER StrLen_or_IndPtr);
*/
/*注意：
参数一为申请的语句句柄；hStmt
参数二为参数编号；如SQL语句为INSERT INTO T1(value1,value2) VALUES(?,?)，则1就代表对value1字段进行的绑定，2亦然
参数三为参数类型，输入或输出，通常为SQL_PARAM_INPUT或者SQL_PARAM_OUTPUT
参数四为参数的ODBC C类型；
参数五为参数的ODBC API类型；C和API类型必须一一对应，且与缓冲区数据类型对应;
参数六为其所在列的长度；一般整型和单精度浮点型为4，双精度浮点型为8等等，字符串类型为创建表是定义的该字段的字符串长度
参数七为参数所对应的小数点位数；
参数八为参数所对应的缓冲区；
参数九为缓冲区长度，整型、浮点型或者日期型等均为&nX(SQLINTEGER nX = 0；)
参数九在参数为整型、浮点型或日期型时为0，参数为字符串时为&nX (SQLINTEGER nX = SQL_NTS；)；
*/
retcode = SQLPrepare( hstmt, ( SQLCHAR* )( LPSTR )( LPCSTR )strInsertSQL, SQL_NTS );
if ( retcode != SQL_SUCCESS )
{
return RT_ERROR;
}
switch ( pField->GetType() )
{
case DATA_INTEGER:
if ( pField->GetLength() > 1 )
{
CString strFieldName = pField->GetName();
int nFieldSize = pField->GetSize();
}
else
{
pIntType[nCurIntTypeCount] = *(int* )pData;
retcode = SQLBindParameter( hstmt, nCurFieldCount, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 4, 0, &pIntType[nCurIntTypeCount], 0, &cbInteger );
}
nDataOffset = nDataOffset + pField->GetSize();
nCurIntTypeCount++;
break;
case DATA_FLOAT_SINGLE:
if ( pField->GetLength() > 1 )
{
}
else
{
float fValue = *( float* )pData;
retcode = SQLBindParameter( hstmt, nCurFieldCount, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 4, 0, &fValue, 0, &cbFloat );
}
nDataOffset = nDataOffset + pField->GetSize();
break;
case DATA_DATETIME:
case DATA_DATETIME_SECOND:
case DATA_DATETIME_MINUTE:
case DATA_DATETIME_MONTH:
case DATA_DATETIME_HOUR:
case DATA_DATETIME_WEEK:
case DATA_DATETIME_USECOND:
{
strTime = ( char* )pData;
SQL_TIMESTAMP_STRUCT tm;
tm = FormatTime( strTime );
retcode = SQLBindParameter( hstmt, nCurFieldCount, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 8, 0, &tm, 0, &cbTime );
nDataOffset = nDataOffset + SIZE_TIMESTR;
break;
}
case DATA_STRING:
retcode = SQLBindParameter( hstmt, nCurFieldCount, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, pField->GetSize() - 1, 0, pData, pField->GetSize(), &cbChar );
nDataOffset = nDataOffset + pField->GetSize();
break;
case INVALID_FIELD_TYPE:
return RT_ERROR;
default:
return RT_ERROR;
}
retcode = SQLExecute( hstmt );
1.8 利用ODBCAPI函数插入或修改二进制数据
BOOL main()
CString strInsertSQL = "Update ";
strInsertSQL += strTableName;
strInsertSQL += strTypeSuffix;
strInsertSQL += " Set ";
strInsertSQL += strFieldName;
strInsertSQL += " = ? Where f_Time = ?";
void* pData = NULL;
SQLHSTMT hstmt = pODBC->GetStmt();
SQLRETURN retcode;
SQL_TIMESTAMP_STRUCT tmNew;
SDWORD cbBinary = SQL_LEN_DATA_AT_EXEC( MAX_BLOB_DATA_LENGTH );
//准备执行SQL语句
retcode = SQLPrepare( hstmt, ( SQLCHAR* )( LPSTR )( LPCSTR )strInsertSQL, SQL_NTS );
SDWORD cbTime = 0;
//绑定参数列
retcode = SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_BINARY, MAX_BLOB_DATA_LENGTH, 0, pData, 0, &cbBinary );
retcode = SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 8, 0, &tmNew, 0, &cbTime );
tmNew = tm;
pData = data;
//执行SQL语句
retcode = SQLExecute( hstmt );
SQLPOINTER pToken;
SQLINTEGER cbData = MAX_BLOB_DATA_LENGTH;
//向绑定的参数列传递数据
retcode = SQLParamData( hstmt, &pToken );
while ( retcode == SQL_NEED_DATA )
{
//开始传递
retcode = SQLPutData( hstmt, pData, cbData );
//pData = ( char* )data + MAX_BLOB_DATA_LENGTH;
//完成数据传递
retcode = SQLParamData( hstmt, &pToken );
}
return RT_OK;
}
l 软件运行的初始化工作必须在撰写代码之前做详细的规划。例如程序在执行一段循环程序，在每一步的循环中都要用到注册表中的一个或几个键值，此时可以在程序初始化时定义一个全局变量，在程序启动时就将要用到的注册表键值全部读入一个结构体中；再比如用ODBC编写数据库程序，则只需建立一个ODBC连接，每次执行新的SQL语句时只需重新申请语句句柄即可，这样即可以节省数据库操作时间，又使程序出错机会大大减少。
1.10 读取注册表
bool ReadHKey( void )
{
HKEY hkey = HKEY_LOCAL_MACHINE;
DWORD len;
DWORD type;
Char szKey1[128], szKey2[128];
//打开键
if ( ::RegOpenKeyEx( hkey, "Software//WSPQMC//WSPQMCS", 0, KEY_ALL_ACCESS, &hkey ) == ERROR_SUCCESS )
{
len = 128; //设置读取键值的最大长度
type = REG_SZ; //设置读取键值的类型
//读取键值
if ( ::RegSetValueEx( hkey, "DSNHistory", NULL, &type, szKey1, &len ) != ERROR_SUCCESS )
{
::RegCloseKey( hkey );
return FALSE;
}
//读取新键值，首先重新设置键值长度和类型
len = 128;
type = REG_SZ;
if ( ::RegSetValueEx( hkey, "DSNData", NULL, &type, ( unsigned char* ) szKey2, &len ) != ERROR_SUCCESS )
{
::RegCloseKey( hkey );
return FALSE;
}
Cstring strKey1,strKey2;
strKey1 = szKey1;
strKey2 = szKey2;
}
return true;
}
1.13 在VC中使用ADO
首先要在CMyApp::InitInstance()函数中加入初始化Com环境
if ( FAILED(::CoInitialize(NULL)) )
{
::AfxMessageBox( "Com Init Fail !" );
::exit(0);
}
在CMyApp::ExitInstance()函数中加入
::CoUninitialize();
然后引入ADO库文件，一般是在stdAfx.h文件中添加
#import <msado15.dll> no_namespace rename( "EOF", "adoEOF" )
这条语句会在工程所在目录生成msado15.tlh和msado15.tli两个文件。
在你的类声明文件中加入智能指针对象实例的定义，做为成员变量，
_ConnectionPtr m_pConn;
_RecordsetPtr m_pRs;
使用之前先初始化，
//初始化ADO成员
TESTHR( m_pConn.CreateInstance( __uuidof( Connection ) ) );
TESTHR( m_pRs.CreateInstance( __uuidof( Recordset ) ) );
其中TESTHR定义如下：
void TESTHR(HRESULT x)
{
if FAILED(x)
_com_issue_error(x);
};
然后就是连接数据库，获取Recordset……用不着我多说了吧，代码如下：
//Open Connection
TESTHR( m_pConn->Open( strConn/*连接字符串*/, "", "", adConnectUnspecified ) );
//Open table
TESTHR( m_pRs->Open( SQL/*SQL查询语句*/
_variant_t((IDispatch*)m_pConn, true),
adOpenKeyset,//adOpenForwardOnly,
adLockReadOnly,
adCmdText) );
1.14 写注册表动态创建SQL Server ODBC数据源
各个参数的意义：
DBServer: 数据库所在主机
DBName: 数据库名称
DSN: 数据源名称
UID: 登陆用户
返回值：
-1: 没有安装SQL Server驱动程序
-2: 其他错误
0: 成功
实现函数：
int MakeSQLServerODBCDSN(LPCTSTR DBServer,LPCTSTR DBName,LPCTSTR DSN,LPCTSTR UID)
{
BOOL bInstallDriver = TRUE;
CRegKey regKey;
LONG lRet = regKey.Open( HKEY_LOCAL_MACHINE, "Software//ODBC//ODBCINST.INI//SQL Server" );
If ( lRet != ERROR_SUCCESS )
{
bInstallDriver = FALSE;
}
else
{
char szDirverPath[MAX_PATH] = "";
DWORD dwCount = 100;
lRet=regKey.QueryValue(szDirverPath,"Driver",&dwCount);
if ( ( lRet != ERROR_SUCCESS ) || ( dwCount < 1 ) )
{
DWORD dwErr = GetLastError();
BinstallDriver = FALSE;
}
regKey.Close();
}
if ( !bInstallDriver )
{
return -1;
}
CString strKeyValueName="Software//ODBC//ODBC.INI//";
StrKeyValueName += DSN;
lRet=regKey.Create(HKEY_LOCAL_MACHINE,strKeyValueName);
if ( lRet != ERROR_SUCCESS )
{
return -2;
}
regKey.SetValue(DBName,"Database");
regKey.SetValue("SQLSrv32.dll","Driver");
regKey.SetValue(DBServer,"Server");
regKey.SetValue(UID,"LastUser");
regKey.m_hKey=HKEY_LOCAL_MACHINE;
regKey.SetKeyValue("Software//ODBC//ODBC.INI//ODBC Data Sources","SQL Server",DSN);
regKey.Close();
return 0;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
