Author: Zhang Xiuyong AppBuilder URL: http: //www.ucancode.com
Chapter 1: How to configure DSN for your development system:
(1) Open Windows control panel. Select the ODBC Data Sources icon, double-click to open this icon content, and the ODBC Data Source Administrator dialog box appears.
(2), select the user DSN in the dialog and then you can select the
Chapter II: How to use the ODBC API to write a database-based program directly under the VC:
ODBC database programming
First, general steps:
Allocation
The application system must first initialize ODBC before calling any ODBC function, and establish an environment.
ODBC uses this environment to monitor the database connection that has been established. Each application is only established
The environment is very necessary because it can be established in an environment regardless of how many connections. Complete this point
The ODBC function of the process process SQLAllocenv is described in the next section.
SQLallocenv
SQLallocenv assigns memory for the environment handle and initializes the ODBC call layer interface used by the application system.
The application must call SQLAllocenv before calling any other ODBC function.
The following is the syntax of SQLAllocenv:
Retcode Sqlallocenv (Phenv)
The parameters of SQLallocenv are shown in the following table, and its return code is SQL-Error. Because calling SQLError
There is no valid handle, so this function does not have a SQLSTATE return code;
// Assign Environment Handle
SQLRETURN M_RETCODE;
IF (m_henv! = SQL_NULL_HENV)
Return False;
IF (SQL_SUCCESS == (m_retcode = sqlallocenv (& m_henv))))
{
// Create a new DSN
CreatedSN (IDS_HOME_DSNNAME, IDS_HOME_DBFILENAME);
// Assign the connection handle
IF (SQL_SUCCESS == (m_retcode = sqlallocconnect (m_henv, & m_hdb)))))
{
// Connect the data source
IF (SQL_Success == (m_retcode = sqlconnect (m_hdbc, (uchar *) ((lpctstr) m_STRDSN, SQL_NTS, NULL, 0, NULL, 0)))
{
m_bconnected = true;
}
}
}
Assign a connection handle
Like the environment of the application system, the connection handle represents the application system and data source.
the connection between. For each data source to connect to the application system, you must allocate a connection
Handle. For example, if you need to connect to DBASE and BTRIEVE, you must assign two connections.
Handle. Next section description function sqlallocconnect.
SQLALLOCCONNECT assigns memory for the connection handle in the environment of the HENV logo. Below
Sqlallocconnect's grammar:
Retcode Sqlallocconnect (HENV, PHDBC)
The parameters of SQLACCONNECT are as shown in the following table, and their return code is:
SQL-SUCCESS
SQL-SUCCESS-WITH-INFO
SQL-Error
SQL-INVALID-HANDLE
SQLSTATE's return code is:
01000 S1000 S1001 S1009
Use core functions to connect to data source
Although there are many ODBC functions to establish a connection, only one is in the core API layer, that is, the function sqlconnect. It provides a simple and effective way to connect with the data source. All drivers support
SqlConnect, so it is the most interoperable solution. Here is a description of SqlConnect.
SqlConnect loads a database driver and establishes a connection to the data source. This connection
The handle determines the storage location of all connection information (including its state, transaction status, and error message).
The syntax of SqlConnect is as follows:
Retcode Sqlconnect (HDBC, SZDSN, CBDSN, Szuid, CBUID, Szauthstr, Cbauthatr)
The return code of SqlConnectDe is:
SQL-SUCCESS
SQL-SUCCESS-WITH-INFO
SQL-Error
SQL-INVALID-NUMBER
Skeleton with data source
Once the application system is connected to the completion of a data source, it should be disconnected. Connection is very expensive
Resources because many DBMS are charged for every license person or user connected at the same time. when
When the connection is complete, it should return it so that other users can register into the system. The ODBC described below
Function SQLDisConnect Processes this operation process.
SqldisConnect closes the data source associated with the specified connection handle. Sqldisconnect
The syntax is as follows:
RetcoDesqldisconnect (HDBC)
The return code of SqldisConnect is:
SQL-SUCCESS
SQL-SUCCESS-WITH-INFO
SQL-Error
SQL-INVALID-HANDLE
If the data source is successful, you can continue the next step;
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))))
{
// Read data is successful, and the data can be processed.
}
}
:: SQLFreeHandle (SQL_HANDLE_STMT, HSTMT);
After the program is over, turn off the database.
//Disconnect
IF (m_bconnected)
{
Sqldisconnect (M_HDBC);
SQLFreeHandle (SQL_HANDLE_DBC, M_HDBC);
M_HDBC = NULL;
}
// Delete DSN
IF (Ids_Home_Dsnname)
DeletedSn (IDS_HOME_DSNNAME);
/ / Delete Environment Handle
IF (m_henv)
{
SQLFreeHandle (SQL_HANDLE_ENV, M_HENV);
m_henv = NULL;
}
Second, the operation of the database
The following methods are only valid for the Access database.
1, generate a database
CSTRING STRFILENAME = "C: /1.mdb";
Cstring strdriver;
Char szfilename [100 _max_path];
Striver = "Microsoft Access Driver (* .mdb) / 0";
Sprintf (SZFileName, "CREATE_DB =% s general / 0/0", strfilename);
SqlconfigDataSource (NULL, ODBC_ADD_DSN, STRDRIVER, SZFILENAME);
2, compressed database
Bool SuperDatabase :: Compaction (CString Strsourname, CSTRING STRDESTNAME)
{
STRSOLNAME.ISEMPTY ())
Return False;
IF (strDestname.isempty ())
STRDESTNAME = STRSOURNAME;
Char szcommand [100 _max_path];
Int J;
Cstring strdriver;
Striver = "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, get the name of the database:
If a data source has been opened, the name of the current database can be obtained by the data source;
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;
}
Third, for the operation of the data source:
1, increase data source
Bool SuperDatabase :: Created Striver, CString Strfilename, CString Strdsn, CString Struserid, CString StrPwd
{
Char szattr [100 _max_path];
Int J;
IF (strDriver.isempty ())
Striver = "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, delete data sources
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, get the system has DSN
Sword nDataSourceNameLength; // DSN STR Length
Sword nsourcedescriptionLENGTH; // Driver Description Str Length
Char szsourcedeDescription [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 is a description of the DSN, which can be used here;
}
Fourth, the operation of the table:
1, get the table name:
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, SZTABLETY, 255, & CB);
While (SQL_Success == :: SQLFETCH (HSTMT))
{
IF (0 == StrCMP (SZTableType, "Table")) // Table representation table, View represents query
{
Tablelist.addtail (sztable);
}
}
}
}
:: SQLFreeHandle (SQL_HANDLE_STMT, HSTMT);
HSTMT = NULL;
}
2. Create a table: You can use the SQL statement to generate a table, the command used is: "CREATE TABLE":
Create Table Table (Field1 Type [(Size)] [NOT NULL] [INDEX1] [, Field2 Type [(Size)] [NOT NULL] [Index2] [, ...]] [, Constraint MultifieldIndex [, ... ]]), Where each type of Type is as follows:
Data type TYPE parameter
Integer short
Long integer
Single precision float
Double precision Double
String Text
Date DateTime
Yes / No Bit
Currency Currency
System Auto Number Counter
V. Supreme:
1. Take the number of columns in the SQL statement:
Int superRecordset :: getColcount ()
{
Sword swcolcount;
IF (m_hstmt == null)
Return -1;
IF (SQL_Success == :: SQLNUMRESULTCOLS (M_HSTMT, & SWCOLCOUNT))
Return swcolcount;
Else
Return -1;
}
Seven, data reading
1, column binding:
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 stat
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);
Among them, if CB1, CB2, CB3, CB4, CB5, CB6, CB7, CB8, CB9 are the size of the returned data, if their value is equal to SQL_NULL_DATA, then the value of this field indicates that the value of this record is empty.
2, block binding
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)
"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 Delete m_adata.getat (i); m_adata.removeall (); } INT cstuffbasicdataqry :: loading () { 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, "); SQLBINDCOL (M_HSTMT, 7, SQL_C_CHAR, PhostData [0] .m_ade_ade, stuffbasicdataqry_adepartment_size, & phostdata [0] .m_adepartment); SQLBINDCOL (M_HSTMT, 8, SQL_C_CHAR, PhostData [0] .m_abusiness, stuffbasicdataqry_abusiness_size, & phostdata [0] .m_abusiness); 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_dcreatedonomateind); While (SQL_SUCCESS == SQLEXTENDEDFETCH (M_HSTMT, SQL_FETCH_NEXT, 1, & ROWSFETCHED, ProWSTATUS) { For (uint count = 0; 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_aname 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.mbon, 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_ABUSINESSLVEL = ""; 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 (); } Eight, write the database 1, normal method: 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; } Note that the field should be empty, the string, the date should be NULL, and you can modify the following function: 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 { 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, parameter binding: Bool ccddata :: 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 overce 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 = SqlpReprePare (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 stat 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, 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; } Nine, other operations: 1. Detect SQL error: 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 || NerrRRESULT == SQL_INVALID_HANDLE) Break; WSPrintf (szbuffer, sqlerr_format, (lpstr) szerrstate, dwerrcode, (lpstr) szerrtext); isize = strlen; szdispbuffer; IF (isize && (isize strlen 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, SZDISPBUFER, (FFIRSTRUN? SQLWRNMSGTILE: SQLWRNCNTDITLE), MB_OK | MB_ICONITIONFORMATION Else MessageBox (NULL, SZDISPBUFER, (FFIRSTRUN? SQlerrmsgtitle: SQlerRcntdtitle, MB_OK | MB_ICONEXCLAMATION; IF (FFIRSTRUN) FFIRSTRUN = FALSE; } While (! (Nerrresult == SQL_NO_DATA || Nerrresult == SQL_ERROR || NerrRRESULT == SQL_INVALID_HANDLE)); Assign and release statements Any SQL function associated with processing and delivery of SQL statements requires a valid statement handle as parameter. The statement handle is very like an environment or connecting handle, the difference is that it references SQL statements or others. Returns the ODBC function of the result. A connect handle can be associated with a few statement handles, but each one The statement handle can only be associated with a connection handle. Application system wants to assign a statement handle, just call The sqlallocstmt described below can be. SQLALLOCSTMT assigns a memory storage area for the statement handle, and specifies this handle with the connection handle The connection is linked. The application must first assign memory with SQLAllocStmt for SQL statement, then Can you provide a SQL statement that references a special statement handle. The syntax of SQLAlocstmt is as follows: Retcode SqlallocStmt (HDBC, phSTMT) The return code of the SQLALLOCSTMT is: SQL-SUCCESS SQL-SUCCESS-WITH-INFO SQL-INVALID-HANDLE SQL-Error SQLFreeestmt SQLFREESTMT completes the following: Stop any SQL statement related to the specified statement handle; Turn off any open cursor associated with the specified statement handle; Abandon all unfinished results. Select all resources associated with the specified statement handle; The syntax of SQLFreeStmt is: Retcode SQLFreeStMt (HSTMT, FOPTION) The return code of SQLFreeStmt is: SQL-SUCCESS SQL-SUCCESS-WITH-INFO SQL-INVALID-HANDLE SQL-Error At last In our development system AppBuilder, you don't have to fully understand the complex process above, you don't have to write any call code, we will automatically generate this, and also integrate with the development of the final program. Together, everything is very simple. Automatically prepare a lot of code for your development, if you need to see, go to: www.ucancode.com in a free version.
