作者：张修勇 AppBuilder的网址：http://www.ucancode.com
第一章：如何为你的开发系统配置DSN：
（一）、打开Windows的控制面板。选择ODBC Data Sources图标，双击打开此图标内容，此时出现ODBC Data Source Administrator对话框。
（2）、在对话框中选择User DSN然后你可以选择<添加>按钮来新增一个DSN.
第二章：如何利用ODBC API在VC下面直接编写基于数据库的程序：
ODBC数据库编程
一、 一般步骤：
分配环境
应用系统在调用任何ODBC函数之前，首先必须初始化ODBC，并建立一个环境。
ODBC用该环境监视应用系统已经建立的数据库连接。每个应用系统只建立一个
环境是很有必要的，因为不管有多少连接都可以在一个环境中建立。完成这一分
配过程的ODBC函数SQLAllocEnv在下一小节描述。
SQLAllocEnv
SQLAllocEnv为环境句柄分配内存，并初始化应用系统使用的ODBC调用层接口。
应用系统在调用任何其他ODBC函数之前必须调用SQLAllocEnv。
以下是SQLAllocEnv的语法：
RETCODE SQLAllocEnv(phenv)
SQLAllocEnv的参数如下表所示，其返回码是SQL-ERROR。因为调用SQLError
时无有效句柄，所以该函数没有SQLSTATE返回码；
//分配环境句柄
SQLRETURN m_retcode;
if( m_henv != SQL_NULL_HENV )
return FALSE;
if (SQL_SUCCESS == (m_retcode = SQLAllocEnv( &m_henv )))
{
//创建新的DSN
CreateDSN(IDS_HOME_DSNNAME,IDS_HOME_DBFILENAME);
//分配连接句柄
if (SQL_SUCCESS == (m_retcode = SQLAllocConnect( m_henv, &m_hdbc )))
{
// 连接数据源
if (SQL_SUCCESS == (m_retcode = SQLConnect( m_hdbc, (UCHAR *)((LPCTSTR)m_strDSN), SQL_NTS, NULL, 0, NULL, 0 )))
{
m_bConnected = TRUE;
}
}
}
分配连接句柄
就象应用系统的环境由环境句柄代表一样，连接句柄代表应用系统与数据源
之间的连接。对于应用系统所要连接的每一个数据源而言，都必须分配一个连接
句柄。例如，如果需要同时与dBase和BTrieve的数据源连接，必须分配两个连接
句柄。下一小节描述函数SQLAllocConnect.
SQLAllocConnect在henv标识的环境里为连接句柄分配内存。以下是
SQLAllocConnect的语法：
RETCODE SQLAllocConnect(henv,phdbc)
SQLAllocConnect 的参数如下表所示，其返回码是：
SQL-SUCCESS
SQL-SUCCESS-WITH-INFO
SQL-ERROR
SQL-INVALID-HANDLE
SQLSTATE的返回码是：
01000 S1000 S1001 S1009
利用核心函数与数据源连接
尽管有很多ODBC函数可以建立连接，但在核心API层却只有一种，即函数
SQLConnect。它提供简单、有效的方法与数据源的连接。所有驱动程序都支持
SQLConnect，所以它是最具有互用性的解决方案。下面是SQLConnect的描述。
SQLConnect加载一个数据库驱动程序，并建立一个与数据源的连接。该连接
句柄确定所有连接信息（包括它的状态，事务状态和错误信息）的存储位置。
SQLConnect的语法如下：
RETCODE SQLConnect(hdbc,szDSN,cbDSN,szUID,cbUID,szAuthStr,cbAuthAtr)
SQLConnectde的返回码是：
SQL-SUCCESS
SQL-SUCCESS-WITH-INFO
SQL-ERROR
SQL-INVALID-NUMBER
与数据源断开
应用系统一旦使用完成一个数据源连接，便应与之断开。连接是十分昂贵
的资源，因为很多DBMS对同时连接的每一个许可人员或用户都是收费的。当
连接完成时，应当把它返回，以便其它用户能注册进入该系统。下面描述的ODBC
函数SQLDisconnect处理这一操作过程。
SQLDisconnect关闭与指定的连接句柄相关的数据源连接。SQLDisconnect
的语法如下：
RETCODESQLDisconnect（hdbc）
SQLDisconnect的返回码是：
SQL-SUCCESS
SQL-SUCCESS-WITH-INFO
SQL-ERROR
SQL-INVALID-HANDLE
如果数据源连接成功的话，就可以继续下一步；
CString strSQLString;
RETCODE ReturnCode;
SQLHSTMThstmt;
if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT,theApp.m_hdbc,&hstmt))
return;
strSQLString.Format(
"SELECT "
"NID,"
"ARIQI,"
"NJINE,"
"ASHUOMING,"
"ALAIYUAN,"
"ACUNZHE,"
"AYONGTU,"
"AXIAOFEI,"
"ABEIZHU"
" FROM HHZhiChu "
" WHERE NID=%u",
nID);
if (SQL_SUCCESS == (ReturnCode = SQLExecDirect(hstmt,(UCHAR*)((LPCTSTR)strSQLString),SQL_NTS)))
{
SQLBindCol(hstmt,1,SQL_C_SLONG,&dbdata.m_Nid,0,&cb);
SQLBindCol(hstmt,2,SQL_C_TIMESTAMP,&dbdata.m_Ariqi,0,&cb);
SQLBindCol(hstmt,3,SQL_C_DOUBLE,&dbdata.m_Njine,0,&cb);
SQLBindCol(hstmt,4,SQL_C_CHAR,dbdata.m_Ashuoming,HHZhiChu_aShuoMing_SIZE,&cb);
SQLBindCol(hstmt,5,SQL_C_CHAR,dbdata.m_Alaiyuan,HHZhiChu_aLaiYuan_SIZE,&cb);
SQLBindCol(hstmt,6,SQL_C_CHAR,dbdata.m_Acunzhe,HHZhiChu_aCunZhe_SIZE,&cb);
SQLBindCol(hstmt,7,SQL_C_CHAR,dbdata.m_Ayongtu,HHZhiChu_aYongTu_SIZE,&cb);
SQLBindCol(hstmt,8,SQL_C_CHAR,dbdata.m_Axiaofei,HHZhiChu_aXiaoFei_SIZE,&cb);
SQLBindCol(hstmt,9,SQL_C_CHAR,dbdata.m_Abeizhu,HHZhiChu_aBeiZhu_SIZE,&cb);
if (SQL_SUCCESS == (ReturnCode = SQLFetch(hstmt)))
{
//读数据成功，可一对数据进行处理了。
}
}
::SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
程序完了后要关闭数据库，
//断开连接
if (m_bConnected)
{
SQLDisconnect(m_hdbc);
SQLFreeHandle(SQL_HANDLE_DBC,m_hdbc);
m_hdbc = NULL;
}
//删除DSN
if (FoundDSNName(IDS_HOME_DSNNAME))
DeleteDSN(IDS_HOME_DSNNAME);
//删除环境句柄
if (m_henv)
{
SQLFreeHandle(SQL_HANDLE_ENV,m_henv);
m_henv = NULL;
}
二、 针对数据库的操作
以下的方法只对ACCESS数据库有效，
1、 生成数据库
CString strFileName="c:/1.mdb";
CString strDriver;
char szFileName[100+_MAX_PATH];
strDriver = "Microsoft Access Driver (*.mdb)/0";
sprintf(szFileName,"CREATE_DB=%s General/0/0",strFileName);
SQLConfigDataSource(NULL,ODBC_ADD_DSN,strDriver,szFileName);
2、 压缩数据库
BOOL SuperDatabase::Compaction(CString strSourName, CString strDestName)
{
if (strSourName.IsEmpty())
return FALSE;
if (strDestName.IsEmpty())
strDestName = strSourName;
char szCommand[100+_MAX_PATH];
int j;
CString strDriver;
strDriver = "Microsoft Access Driver (*.mdb)/0";
j = sprintf(szCommand,"COMPACT_DB=%s %s General/0/0",strSourName,strDestName);
return SQLConfigDataSource(NULL,ODBC_ADD_DSN,strDriver,szCommand);
}
3、 取得数据库的名称：
如果已经打开了一个数据源，可以通过数据源来取得当前的数据库的名称；
CString SuperDatabase::GetDatabaseName()
{
ASSERT(m_hdbc != SQL_NULL_HDBC);
char szName[MAX_TNAME_LEN];
SWORD nResult;
SQLGetInfo(m_hdbc, SQL_DATABASE_NAME,
szName, MAX_TNAME_LEN, &nResult);
return szName;
}
三、 针对数据源的操作：
1、 增加数据源
BOOL SuperDatabase::CreateDSN(CString strDriver, CString strFileName,CString strDSN, CString strUserID, CString strPWD)
{
char szAttr[100+_MAX_PATH];
int j;
if (strDriver.IsEmpty())
strDriver = "Microsoft Access Driver (*.mdb)/0";
j = sprintf(szAttr,"DSN=%s/0",strDSN);
j++;
j = sprintf(szAttr+j,"DBQ=%s/0/0 ",strFileName);
return SQLConfigDataSource(NULL,ODBC_ADD_DSN,strDriver,szAttr);
}
2、 删除数据源
void SuperDatabase::RemoveDSN(CString strDSN)
{
char szDSN[255];
sprintf(szDSN,"DSN=%s/0/0",strDSN);
BOOL bIsSuccess = SQLConfigDataSource(NULL,ODBC_REMOVE_DSN,"Microsoft Access Driver (*.mdb)/0",szDSN);
}
3、 取得系统已有得DSN
SWORD nDataSourceNameLength; //DSN str length
SWORD nSourceDescriptionLength; //Driver Description str length
char szSourceDescription[MAXBUFLEN+1]; //Driver Description string
SQLRETURN nResult; //Return Code
SWORD nDirection=SQL_FETCH_FIRST;
if ((nResult = SQLDataSources(m_henv, nDirection, (UCHAR *)((LPCTSTR)strDataSourceName), MAXBUFLEN, &nDataSourceNameLength, (UCHAR *)szSourceDescription, MAXBUFLEN, &nSourceDescriptionLength)) != SQL_NO_DATA && nResult != SQL_ERROR)
{
nDirection=SQL_FETCH_NEXT;
// szSourceDescription为DSN 的描述，可以在这里取来用；
}
四、 针对表的操作：
1、 取得表名：
void SuperDatabase::GetTable(CStringList &tableList)
{
ASSERT(m_bIsConnect);
if (!m_bIsConnect)
return;
SQLHSTMT hstmt = NULL;
if (SQL_SUCCESS == ::SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &hstmt))
{
if (SQL_SUCCESS == ::SQLTables(hstmt, NULL, 0, NULL, 0,
NULL, 0, NULL, 0))
{
SDWORD cb;
char szTable[255];
char szTableType[255];
::SQLBindCol(hstmt, 3, SQL_C_CHAR, szTable, 255, &cb);
::SQLBindCol(hstmt, 4, SQL_C_CHAR, szTableType, 255, &cb);
while (SQL_SUCCESS == ::SQLFetch(hstmt))
{
if (0 == strcmp(szTableType, "TABLE"))//TABLE表示表，VIEW表示查询
{
tableList.AddTail(szTable);
}
}
}
}
::SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
hstmt = NULL;
}
2、 创建表：可以用SQL语句生成表，使用的命令为："CREATE TABLE"：
CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]]) 其中，各类型type如下：
数据类型 Type参数
整型 SHORT
长整型 INTEGER
单精度 FLOAT
双精度 DOUBLE
字符串 TEXT
日期 DATETIME
是/否 BIT
货币 CURRENCY
系统自动编号 COUNTER
五、 对列的操作：
1、 取得SQL语句中的列数：
int SuperRecordSet::GetColCount()
{
SWORD swColCount;
if (m_hstmt == NULL)
return -1;
if (SQL_SUCCESS == ::SQLNumResultCols(m_hstmt,&swColCount))
return swColCount;
else
return -1;
}
七、数据的读取
1、列绑定：
CString strSQLString;
RETCODE ReturnCode;
SQLHSTMT hstmt;
if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT,theApp.m_hdbc,&hstmt))
return;
FF_DB_HHZHICHU_SET_FIELDS dbdata;
SDWORD cb1;
SDWORD cb2;
SDWORD cb3;
SDWORD cb4;
SDWORD cb5;
SDWORD cb6;
SDWORD cb7;
SDWORD cb8;
SDWORD cb9;
// Build the SQL Statement
strSQLString.Format(
"SELECT "
"NID,"
"ARIQI,"
"NJINE,"
"ASHUOMING,"
"ALAIYUAN,"
"ACUNZHE,"
"AYONGTU,"
"AXIAOFEI,"
"ABEIZHU"
" FROM HHZhiChu "
" WHERE NID=%u",
nID);
if (SQL_SUCCESS == (ReturnCode = SQLExecDirect(hstmt,(UCHAR*)((LPCTSTR)strSQLString),SQL_NTS)))
{
SQLBindCol(hstmt,1,SQL_C_SLONG,&dbdata.m_Nid,0,&cb1);
SQLBindCol(hstmt,2,SQL_C_TIMESTAMP,&dbdata.m_Ariqi,0,&cb2);
SQLBindCol(hstmt,3,SQL_C_DOUBLE,&dbdata.m_Njine,0,&cb3);
SQLBindCol(hstmt,4,SQL_C_CHAR,dbdata.m_Ashuoming,HHZhiChu_aShuoMing_SIZE,&cb4);
SQLBindCol(hstmt,5,SQL_C_CHAR,dbdata.m_Alaiyuan,HHZhiChu_aLaiYuan_SIZE,&cb5);
SQLBindCol(hstmt,6,SQL_C_CHAR,dbdata.m_Acunzhe,HHZhiChu_aCunZhe_SIZE,&cb6);
SQLBindCol(hstmt,7,SQL_C_CHAR,dbdata.m_Ayongtu,HHZhiChu_aYongTu_SIZE,&cb7);
SQLBindCol(hstmt,8,SQL_C_CHAR,dbdata.m_Axiaofei,HHZhiChu_aXiaoFei_SIZE,&cb8);
SQLBindCol(hstmt,9,SQL_C_CHAR,dbdata.m_Abeizhu,HHZhiChu_aBeiZhu_SIZE,&cb9);
// Fetch and store...
if (SQL_SUCCESS == (ReturnCode = SQLFetch(hstmt)))
{
m_Record.m_Nid = dbdata.m_Nid;
m_Record.m_Ariqi.SetDateTime(dbdata.m_Ariqi.year,
dbdata.m_Ariqi.month,
dbdata.m_Ariqi.day,
dbdata.m_Ariqi.hour,
dbdata.m_Ariqi.minute,
dbdata.m_Ariqi.second);
m_Record.m_Njine = dbdata.m_Njine;
m_Record.m_Ashuoming = dbdata.m_Ashuoming;
m_Record.m_Alaiyuan = dbdata.m_Alaiyuan;
m_Record.m_Acunzhe = dbdata.m_Acunzhe;
m_Record.m_Ayongtu = dbdata.m_Ayongtu;
m_Record.m_Axiaofei = dbdata.m_Axiaofei;
m_Record.m_Abeizhu = dbdata.m_Abeizhu;
}
}
::SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
其中，如果cb1、cb2、cb3、cb4、cb5、cb6、cb7、cb8、cb9是接受返回的数据的大小的，如果他们的值等于SQL_NULL_DATA，那么表示此记录的这个字段的值为空。
2、块绑定
CStuffbasicdataQry::CStuffbasicdataQry(CODBCDatabase* pDB) : m_nRowSetSize(1000), m_pDatabase(pDB)
{
m_hstmt = SQL_NULL_HSTMT;
RETCODE ReturnCode = SQLAllocHandle(SQL_HANDLE_STMT, m_pDatabase->GetHDBC(), &m_hstmt);
if (ReturnCode != SQL_SUCCESS)
TRACE("Unable to allocate Statement Handle in CTtttempSet/n");
}
CStuffbasicdataQry::~CStuffbasicdataQry()
{
Reset();
if (m_hstmt)
SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
}
void CStuffbasicdataQry::Reset()
{
if (m_hstmt)
SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
for (int i = 0; i < m_aData.GetSize(); i++)
delete m_aData.GetAt(i);
m_aData.RemoveAll();
}
int CStuffbasicdataQry::Load()
{
CString strSQLString;
//RETCODE ReturnCode;
UDWORD RowsFetched;
UWORD *pRowStatus;
pFF_DB_STUFFBASICDATAQRY_FIELDS pHostData;
ASSERT(m_nRowSetSize > 0);
pRowStatus = new UWORD[m_nRowSetSize];
ASSERT(pRowStatus);
pHostData = new FF_DB_STUFFBASICDATAQRY_FIELDS[m_nRowSetSize];
ASSERT(pHostData);
SQLSetStmtOption(m_hstmt,SQL_BIND_TYPE,sizeof(FF_DB_STUFFBASICDATAQRY_FIELDS));
SQLSetStmtOption(m_hstmt,SQL_CONCURRENCY,SQL_CONCUR_READ_ONLY);
SQLSetStmtOption(m_hstmt,SQL_CURSOR_TYPE,SQL_CURSOR_KEYSET_DRIVEN);
SQLSetStmtOption(m_hstmt,SQL_ROWSET_SIZE,m_nRowSetSize);
strSQLString.Format(
"SELECT * "
" FROM STUFFBASICDATA");
if (SQL_SUCCESS == SQLExecDirect(m_hstmt,(UCHAR*)((LPCTSTR)strSQLString),SQL_NTS))
{
SQLBindCol(m_hstmt,1,SQL_C_CHAR,pHostData[0].m_aStuffID,StuffbasicdataQry_aStuffID_SIZE,&pHostData[0].m_aStuffIDInd);
SQLBindCol(m_hstmt,2,SQL_C_CHAR,pHostData[0].m_aNameCN,StuffbasicdataQry_aNameCN_SIZE,&pHostData[0].m_aNameCNInd);
SQLBindCol(m_hstmt,3,SQL_C_CHAR,pHostData[0].m_aNameEN,StuffbasicdataQry_aNameEN_SIZE,&pHostData[0].m_aNameENInd);
SQLBindCol(m_hstmt,4,SQL_C_CHAR,pHostData[0].m_aEducation,StuffbasicdataQry_aEducation_SIZE,&pHostData[0].m_aEducationInd);
SQLBindCol(m_hstmt,5,SQL_C_CHAR,pHostData[0].m_aGender,StuffbasicdataQry_aGender_SIZE,&pHostData[0].m_aGenderInd);
SQLBindCol(m_hstmt,6,SQL_C_TIMESTAMP,&pHostData[0].m_dWorkDate,0,&pHostData[0].m_dWorkDateInd);
SQLBindCol(m_hstmt,7,SQL_C_CHAR,pHostData[0].m_aDepartment,StuffbasicdataQry_aDepartment_SIZE,&pHostData[0].m_aDepartmentInd);
SQLBindCol(m_hstmt,8,SQL_C_CHAR,pHostData[0].m_aBusiness,StuffbasicdataQry_aBusiness_SIZE,&pHostData[0].m_aBusinessInd);
SQLBindCol(m_hstmt,9,SQL_C_CHAR,pHostData[0].m_aBusinessLevel,StuffbasicdataQry_aBusinessLevel_SIZE,&pHostData[0].m_aBusinessLevelInd);
SQLBindCol(m_hstmt,10,SQL_C_CHAR,pHostData[0].m_aWorkType,StuffbasicdataQry_aWorkType_SIZE,&pHostData[0].m_aWorkTypeInd);
SQLBindCol(m_hstmt,11,SQL_C_TIMESTAMP,&pHostData[0].m_dLeaveWorkDate,0,&pHostData[0].m_dLeaveWorkDateInd);
SQLBindCol(m_hstmt,12,SQL_C_CHAR,pHostData[0].m_aEnageType,StuffbasicdataQry_aEnageType_SIZE,&pHostData[0].m_aEnageTypeInd);
SQLBindCol(m_hstmt,13,SQL_C_TIMESTAMP,&pHostData[0].m_dCreateDocDate,0,&pHostData[0].m_dCreateDocDateInd);
while (SQL_SUCCESS == SQLExtendedFetch(m_hstmt,SQL_FETCH_NEXT,1,&RowsFetched,pRowStatus))
{
for (UINT Count = 0; Count < RowsFetched; Count++)
{
if (pRowStatus[Count] != SQL_ROW_DELETED && pRowStatus[Count] != SQL_ROW_ERROR )
{
pDB_STUFFBASICDATAQRY_FIELDS pData = new DB_STUFFBASICDATAQRY_FIELDS();
ASSERT(pData);
if (pHostData[Count].m_aStuffIDInd == SQL_NULL_DATA)
pData->m_aStuffID = "";
else
pData->m_aStuffID = pHostData[Count].m_aStuffID;
if (pHostData[Count].m_aNameCNInd == SQL_NULL_DATA)
pData->m_aNameCN = "";
else
pData->m_aNameCN = pHostData[Count].m_aNameCN;
if (pHostData[Count].m_aNameENInd == SQL_NULL_DATA)
pData->m_aNameEN = "";
else
pData->m_aNameEN = pHostData[Count].m_aNameEN;
if (pHostData[Count].m_aEducationInd == SQL_NULL_DATA)
pData->m_aEducation = "";
else
pData->m_aEducation = pHostData[Count].m_aEducation;
if (pHostData[Count].m_aGenderInd == SQL_NULL_DATA)
pData->m_aGender = "";
else
pData->m_aGender = pHostData[Count].m_aGender;
pData->m_dWorkDate.SetDateTime(pHostData[Count].m_dWorkDate.year,
pHostData[Count].m_dWorkDate.month,
pHostData[Count].m_dWorkDate.day,
pHostData[Count].m_dWorkDate.hour,
pHostData[Count].m_dWorkDate.minute,
pHostData[Count].m_dWorkDate.second);
if (pHostData[Count].m_aDepartmentInd == SQL_NULL_DATA)
pData->m_aDepartment = "";
else
pData->m_aDepartment = pHostData[Count].m_aDepartment;
if (pHostData[Count].m_aBusinessInd == SQL_NULL_DATA)
pData->m_aBusiness = "";
else
pData->m_aBusiness = pHostData[Count].m_aBusiness;
if (pHostData[Count].m_aBusinessLevelInd == SQL_NULL_DATA)
pData->m_aBusinessLevel = "";
else
pData->m_aBusinessLevel = pHostData[Count].m_aBusinessLevel;
if (pHostData[Count].m_aWorkTypeInd == SQL_NULL_DATA)
pData->m_aWorkType = "";
else
pData->m_aWorkType = pHostData[Count].m_aWorkType;
pData->m_dLeaveWorkDate.SetDateTime(pHostData[Count].m_dLeaveWorkDate.year,
pHostData[Count].m_dLeaveWorkDate.month,
pHostData[Count].m_dLeaveWorkDate.day,
pHostData[Count].m_dLeaveWorkDate.hour,
pHostData[Count].m_dLeaveWorkDate.minute,
pHostData[Count].m_dLeaveWorkDate.second);
if (pHostData[Count].m_aEnageTypeInd == SQL_NULL_DATA)
pData->m_aEnageType = "";
else
pData->m_aEnageType = pHostData[Count].m_aEnageType;
pData->m_dCreateDocDate.SetDateTime(pHostData[Count].m_dCreateDocDate.year,
pHostData[Count].m_dCreateDocDate.month,
pHostData[Count].m_dCreateDocDate.day,
pHostData[Count].m_dCreateDocDate.hour,
pHostData[Count].m_dCreateDocDate.minute,
pHostData[Count].m_dCreateDocDate.second);
m_aData.Add(pData);
}
}
if (RowsFetched < (UINT)m_nRowSetSize)
break;
}
}
delete [] pRowStatus;
delete [] pHostData;
return m_aData.GetSize();
}
八、写数据库
1、普通方法：
CString strSQL="INSERT INTO TABLE (FILE1,FILE2,FILE3) VALUES (value1,value2,value3)"
long ExecuteSQL(SQLHDBC hdbc,CString strSQL)
{
SQLRETURN ReturnCode;
SQLHSTMT m_hstmt;
BOOL bReturn = FALSE;
long nRowCount = 0;
if (SQL_SUCCESS == (ReturnCode = ::SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&m_hstmt)))
{
if (SQL_SUCCESS == (ReturnCode = ::SQLExecDirect(m_hstmt, (UCHAR*)((LPCTSTR)strSQL),SQL_NTS)))
{
bReturn = TRUE;
::SQLRowCount(m_hstmt,&nRowCount);
}
}
ReturnCode = ::SQLFreeHandle(SQL_HANDLE_STMT,m_hstmt);
// if (!bReturn) nRowCount = 0;
return nRowCount;
}
注意，要把字段赋为空，字符串、日期应是NULL，可以用下面的函数修改：
CString VerifySQLStr(CString strSQL)
{
int nLength = strSQL.GetLength();
int n1 = 0;//check '';
int n2 = 0;
int m1 = 0;//check ##;
int m2 = 0;
for (int i = 0; i< nLength;i++)
{
if (strSQL[i] == 39)
{
n2 = i;
if (n2-n1 == 1)
{
strSQL.Delete(n1,2);
strSQL.Insert(n1,"NULL");
nLength +=2;
n1 = n2;
}
else
{
n1 = n2;
}
}
if (strSQL[i] == '#')
{
m2 = i;
if (m2-m1 == 1)
{
strSQL.Delete(m1,2);
strSQL.Insert(m1,"NULL");
nLength +=2;
m1 = m2;
}
else
{
m1 = m2;
}
}
}
return strSQL;
}
2、参数绑定：
bool CCddataSet::Insert(pDB_CDDATA_SET_FIELDS pData, bool bFirstTime /* true */)
{
RETCODE ReturnCode;
CString strSQLString;
static FF_DB_CDDATA_SET_FIELDS HostData;
// Prepare the statement and bind the columns once
if (bFirstTime)
{
memset(&HostData,0,sizeof(HostData));
SQLFreeStmt(m_hstmt, SQL_CLOSE); // Close the cursor if any
SQLFreeStmt(m_hstmt, SQL_RESET_PARAMS); // Reset the statement handle
strSQLString.Format("INSERT INTO CDData ("
"NGUANGPAN,"
"NWENJIAN,"
"NCLASS,"
"AWENJIAN,"
"NSHANGJI,"
"NSHUXING,"
"ABEIZHU )"
"VALUES ("
"?," // nGuangPan
"?," // nWenJian
"?," // nClass
"?," // aWenJian
"?," // nShangJi
"?," // nShuXing
"? )" // aBeiZhu
); // end format
ReturnCode = SQLPrepare(m_hstmt, (UCHAR*)((LPCTSTR)strSQLString),SQL_NTS);
if (ReturnCode != SQL_SUCCESS)
{
return false;
}
SQLBindParameter(m_hstmt,1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0,
0,&HostData.m_Nguangpan,0,&HostData.m_NguangpanInd );
SQLBindParameter(m_hstmt, 2,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER, 0,
0,&HostData.m_Nwenjian,0, &HostData.m_NwenjianInd );
SQLBindParameter(m_hstmt,3,SQL_PARAM_INPUT, SQL_C_SLONG,SQL_INTEGER, 0,
0,&HostData.m_Nclass,0,&HostData.m_NclassInd );
SQLBindParameter(m_hstmt,4,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,
CDData_aWenJian_SIZE,0,HostData.m_Awenjian,0,&HostData.m_AwenjianInd );
SQLBindParameter(m_hstmt,5,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER,0,
0,&HostData.m_Nshangji, 0, &HostData.m_NshangjiInd );
SQLBindParameter(m_hstmt,6,SQL_PARAM_INPUT,SQL_C_SLONG,SQL_INTEGER, 0,
0, &HostData.m_Nshuxing, 0,&HostData.m_NshuxingInd );
SQLBindParameter(m_hstmt, 7,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,
CDData_aBeiZhu_SIZE,0, HostData.m_Abeizhu, 0,&HostData.m_AbeizhuInd );
}
// Move the data to host structure and execute the statement
HostData.m_Nguangpan = pData->m_Nguangpan;
HostData.m_Nwenjian = pData->m_Nwenjian;
HostData.m_Nclass = pData->m_Nclass;
HostData.m_AwenjianInd = SQL_NTS;
memcpy(HostData.m_Awenjian, pData->m_Awenjian, CDData_aWenJian_SIZE);
HostData.m_Nshangji = pData->m_Nshangji;
HostData.m_Nshuxing = pData->m_Nshuxing;
HostData.m_AbeizhuInd = SQL_NTS;
memcpy(HostData.m_Abeizhu, pData->m_Abeizhu, CDData_aBeiZhu_SIZE);
ReturnCode = SQLExecute(m_hstmt);
if (ReturnCode != SQL_SUCCESS)
{
return false;
}
return true;
}
九、其他操作：
1、检测SQL错误：
void DisplayError(SQLRETURN nResult, SWORD fHandleType, SQLHANDLE handle)
{
UCHAR szErrState[SQL_SQLSTATE_SIZE+1]; // SQL Error State string
UCHAR szErrText[SQL_MAX_MESSAGE_LENGTH+1]; // SQL Error Text string
char szBuffer[1000];
char szDispBuffer[1000]; // Display Buffer
// formatted Error text Buffer
SWORD wErrMsgLen; // Error message length
long dwErrCode; // Native Error code
int iSize; // Display Error Text size
SQLRETURN nErrResult; // Return Code from SQLGetDiagRec
SWORD sMsgNum = 1;
SWORD fFirstRun = TRUE;
szBuffer[0] = '/0';
do
{
// continue to bring messageboxes till all errors are displayed.
// more than one message box may be reqd. as err text has fixed
// string size.
// initialize display buffer with the string in error text buffer
strcpy(szDispBuffer, szBuffer);
// call SQLGetDiagRec function with proper ODBC handles, repeatedly until
// function returns SQL_NO_DATA. Concatenate all error strings
// in the display buffer and display all results.
while ((nErrResult = SQLGetDiagRec(fHandleType, handle, sMsgNum++,
szErrState, &dwErrCode, szErrText,
SQL_MAX_MESSAGE_LENGTH-1, &wErrMsgLen))
!= SQL_NO_DATA) {
if(nErrResult == SQL_ERROR ¦¦ nErrResult == SQL_INVALID_HANDLE)
break;
wsprintf(szBuffer, SQLERR_FORMAT, (LPSTR)szErrState, dwErrCode, (LPSTR)szErrText);
iSize = strlen(szDispBuffer);
if (iSize && (iSize+strlen(szBuffer)+1) >= 1000)
break;
if (iSize)
strcat(szDispBuffer, "/n");
strcat(szDispBuffer, szBuffer);
}
// display proper ERROR or WARNING message with proper title
if (nResult == SQL_SUCCESS_WITH_INFO)
MessageBox(NULL, szDispBuffer, (fFirstRun? SQLWRNMSGTITLE : SQLWRNCNTDTITLE),
MB_OK ¦ MB_ICONINFORMATION);
else
MessageBox(NULL, szDispBuffer, (fFirstRun? SQLERRMSGTITLE : SQLERRCNTDTITLE),
MB_OK ¦ MB_ICONEXCLAMATION);
if (fFirstRun)
fFirstRun = FALSE;
}
while (!(nErrResult == SQL_NO_DATA ¦¦ nErrResult == SQL_ERROR ¦¦ nErrResult == SQL_INVALID_HANDLE));
}
分配和释放语句
任何与处理和传递SQL语句相关的SQL函数都要求一个有效的语句句柄作为
参数。语句句柄很象环境或连接句柄，区别之处在于它引用SQL语句或者其它
返回结果的ODBC函数。一个连接句柄可以与几个语句句柄相关连，但每一个
语句句柄只能与一个连接句柄相关连。应用系统要想分配语句句柄，只需调用
下面描述的SQLAllocStmt即可。
SQLAllocStmt为语句句柄分配内存存储区，并将此句柄与连接句柄指定的
连接联系在一起。应用系统必须先用SQLAllocStmt为SQL语句分配内存，然后
才能提供引用某一特殊语句句柄的SQL语句。
SQLAllocStmt的语法如下：
RETCODE SQLAllocStmt（hdbc，phstmt）
SQLAllocStmt的返回码为：
SQL-SUCCESS
SQL-SUCCESS-WITH-INFO
SQL-INVALID-HANDLE
SQL-ERROR
SQLFreeStmt
SQLFreeStmt完成以下动作：
.停止任何与指定语句句柄相关的、当前正在处理的SQL语句；
关闭任何与指定语句句柄相关的打开光标；
舍弃所有未完成的结果。
有选择的释放与指定语句句柄相关的所有资源；
SQLFreeStmt的语法为：
RETCODE SQLFreeStmt（hstmt,fOption)
SQLFreeStmt的返回码是：
SQL-SUCCESS
SQL-SUCCESS-WITH-INFO
SQL-INVALID-HANDLE
SQL-ERROR
最后
在我们的开发系统AppBuilder中你可以不必对上面的复杂的过程进行完整的了解，你也不必书写任何的调用代码，我们将这一切都自动为你产生，同时还和开发最终程序的界面融合在一起，一切都非常简单。为你的开发自动编写大量的代码，如果你需要看看，请到:www.ucancode.com中下在一个免费的版本。
