/ * - Special Technical Learning - Database Access Technology ODBC API This class accesss the ODBC data source through the ODBC API function: I can help the class function to insert, increase the function of the class. , Modification, simple statistics, etc. (Note: Information by dynamic changing tables: Table name, table field, field name, field type, whether the field is a value, the field is the primary key. This will access the ODBC data source. Table) Alternatively provides a function to delete other tables of the data source (including a list), modify the function LSZ 2001.08.25 * /
/*head File*/
#ifndef _wdc_database_h # define_wdc_database_h
#include
#define max_fields 500 #define max_error_msg 1024 # Define Max_Data 500
class CWdcDatabase {public: CWdcDatabase (); virtual ~ CWdcDatabase (); BOOL Open (const CString & sDSN, const CString & sTable, const CString & sUserId, const CString & sPassword); / * open a database ODBC data source * /
Public: bool m_bopen; / * Sign Database Open * / BOOL M_BBRACKETS; / * Identifier Name, Field Name is the User Billy Mission Release * / INT M_NERROR; / * ODBC Error Return Code * / CString M_SQuery; / * Operation ODBC data source public statement for easy constructing * / cstring m_serrmsg; / * ODBC error return information * / cstring m_sdsn; / * ODBC data source DSN * / cstring m_stable; / * Temporary binding table * /
Public: void DrophstMtSelect (); / * Make sure the public query's statement handle release * / long getRecordCount (CSTRING & SSEL); / * acquire the number of records * / bool nextmatchingRecord (); / * Query the following record * / Int selectmatchingRecords (CSTRING & SWHERECONTEXT); / * Query the requirements of the qualified record * / bool updateRecord (); / * Update a record of the temporary binding table * / bool addRecord (); / * Add a temporary binding table a new record * / Bool DeleteRecord (CSTRING & SWHEREDELETE); / * Remove the temporary binding table meets the conditional record * / BOOL EXECUTE (CONST CSTRING & STEXT); / * Perform a SQL statement, Note: You can perform the stored procedure * / void close (); / * Close the database * / public: int m_nfields; / * Timed binding table field * / cstring m_sfieldname [max_fields]; / * Temporary binding table field name * / cstring m_sfieldValue [max_fields]; / * Temporary binding table field Value * / BOOL M_BKEYFIELD [MAX_FIELDS]; / * Temporary Bind Table field is the primary key * / bool m_bnumeric [max_fields]; / * Is the temporary binding table field is numeric type * / private: cstract quote (cstring & square); / * String Replace * / RetCode RC; / * ODBC API Culture Performs Return Code * / HSTMT HSTMT, HSTMT_SELECT; / * Public Statement Handle * / Unsigned Char Szdsn [200]; / * DSN * / Unsigned char Szid [200]; / * UID * / unsigned char szpassword [200] connected to the data source [200]; / * PWD * / unsigned char szquery [3000] connected to the data source [3000]; / * Public execution statement handle * / unsigned char Sztemp [1000]; / * String of public use * / cstring STEMP; / * Common String * / Henv Henv; / * Environment Handle * / HDBC HDBC; / * ODBC Connection Handle * /};
#ENDIF
/ * Implement file * /
#include "stdafx.h" #include "wdcdatabase.h" #include
#ifdef _debug # undef this_filestatic char this_file [] = __ file __; # Define new debug_new # Endif
CWDCDATABASE :: CWDCDATABASE () {m_nerror = 0; m_bopen = false; m_nfields = 0; m_bbrackets = false;}
CWDCDATABASE :: ~ cwdcdatabase () {if (m_bopen) {close ();}}
BOOL CWdcDatabase :: Open (const CString & sDSN, const CString & sTable, const CString & sUserId, const CString & sPassword) {if (m_bOpen) {Close ();} hstmt_select = NULL; RETCODE rc; CString stemp; memset (szDSN, 0x00,200 ); MEMSET (SZID, 0x00, 200); Memset (Szpassword, 0x00, 200); / * Open Database * / SQLAllocenv (& HENV); SQLAlLocconnect (HENV, & HDBC);
STRCPY (CHAR *) SZDSN, SDSN);
STEMP = SUSERID; STRCPY ((char *) SZID, STEMP);
STEMP = Spassword; Strcpy ((char *) szpassword, step);
m_sdsn = sdsn; m_stable = set;
Rc = SqlConnect (HDBC, SZDSN, SQL_NTS, SZID, SQL_NTS, SZPASSWORD, SQL_NTS);
m_nerror = (int) RC;
IF ((RC == SQL_SUCCESS) || (RC == SQL_SUCCESS_WITH_INFO)) {m_bopen = true; trace0 ("Open Database succeed! / n"); return true;} else {m_bopen = false; trace0 ("Open Database Failed ! / n "); Return False;}
}
Void cwdcdatabase :: close () {if (m_bopen) {if (hstmt_select) {SQLFREESTMT (hstmt_select, sql_drop); hstmt_select = null;}
Sqldisconnect (HDBC); / * DIS HDBC * / SQLFreeConnect (HDBC); / * free odbc * / sqlfreeeenv (heng); / * free Henv * / m_bopen = false; "Close Database succeed! / N");} }
Bool cwdcdatabase :: EXECUTE (const cstring & ssql) {retcode rc; unsigned char szsqlstate [6]; sdword Nerr; unsigned char msg [max_error_msg 1]; Sword CBMSG;
m_serrmsg = ""; m_squery = ssql;
IF (! m_bopen) Return False;
SQLallocStmt (HDBC, & HSTMT); MEMSET ((char *) szquery, 0x00, sizeof (szquery)); strcpy ((char *) szquery, m_squery);
rc = SQLExecDirect (hstmt, szQuery, SQL_NTS); m_nError = (int) rc; if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) {SQLTransact (henv, hdbc, SQL_COMMIT); SQLFreeStmt (hstmt, SQL_DROP); return TRUE } Else {m_serrmsg = "" "" "(sqlerror (0, 0, HSTMT, SzsqlState, & Nerr, MSG, SIZEOF (MSG), & CBMSG) == SQL_SUCCESS) m_serrmsg =" / t / t " (cstring) MSG "/ n"; SQLFreeStmt (hstmt, sql_drop); return false;}
}
/ * Conditions for WHER * / BOOL CWDCDATABASE :: deleteRecord (cstring & swheretele) {Retcode RC; unsigned char szsqlstate [6]; sdword nerr; unsigned char msg [MAX_ERROR_MSG 1]; SWORD CBMSG;
CString STEMP;
IF (! m_bopen) Return False;
M_SQuery = ""; m_serrmsg = ""; / * Start constructing delete statement * / m_squery.format ("delete from% s", m_stable);
/ * int ff = 0; for (int F = 0; f
IF (M_BNUMERIC [F]) STEMP.FORMAT ("% s =% s", m_sfieldname [f], m_sfieldvalue [f]); Else STEMP.FORMAT ("% s =% s", m_sfieldname [f], quote M_SfieldValue [f])); m_squery = step;}} * / if (swhereete! = ") m_squery =" where " swhereeete;
TRACE0 (m_sQuery); memset ((char *) szQuery, 0x00, sizeof (szQuery)); strcpy ((char *) szQuery, m_sQuery); SQLAllocStmt (hdbc, & hstmt); rc = SQLExecDirect (hstmt, szQuery, SQL_NTS);
m_nerror = (int) RC; if (RC == SQL_Success || RC == SQL_SUCCESS_WITH_INFO) {/ * If successful * / sqltransact (henv, hdbc, sql_commit); sqlfreeestmt (hstmt, sql_drop); return true;} else { m_serrmsg = ""; while (sqlerror (0, 0, HSTMT, SzsqlState, & Nerr, MSG, SIZEOF (MSG), & CBMSG) == SQL_SUCCESS M_SERRMSG = "/ T / T" (CString) MSG "/ N" ; SQLFreeStmt (HSTMT, SQL_DROP); RETURN FALSE;}
}
Cstring cwdcdatabase :: quote (cstring & stext) {cstring SResult = ""; cstring scha; int itextlen = step.getLength ();
For (int POS = 0; POS Return CString ("'" SResult "); } Bool cwdcdatabase :: addrecord () {unsigned char szsqlstate [6]; sdword nerr; unsigned char msg [max_error_msg 1]; Sword CBMSG; CString STEMP; IF (! m_bopen) Return False; m_squery.format ("INSERT INTO% S (", M_STABLE); INT ff = 0; for (INT f = 0; f FF ; / * --- If the data is not empty * / if (m_sfieldvalue [f]! = ") {if (m_bnumeric [f]) STEMP.FORMAT ("% s ", m_sfieldValue [f]); Else STEMP.FORMAT ("% s", quote (m_sfieldvalue [f]));} else {/ * If the data is empty * / stepp.format ("% s", "null");} m_squery = stemp; } m_squery = ")"; MEMSET ((char *) szquery, 0x00, sizeof (szquery); strcpy ((char *) szquery, m_squery); SQLAllocStmt (HDBC, & HSTMT); RC = SQLEXECDirect (HSTMT, SZQUERY, SQL_NTS); m_nError = (int) rc; if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) {SQLTransact (henv, hdbc, SQL_COMMIT); SQLFreeStmt (hstmt, SQL_DROP); hstmt = NULL; return TRUE;} else {m_sErrMsg = " "; WHILE (SQLError (0, 0, HSTMT, SzsqlState, & Nerr, MSG, SIZEOF (MSG), & CBMSG) == SQL_SUCCESS m_serrmsg = "/ t / t" (cstring) msg "/ n"; SQLFreeStmt (HSTMT, SQL_DROP); return false;}} / * Table specified record * / Bool CWDCDATABASE :: updateRecord () {Unsigned Char szsqlstate [6]; SDWORD NERR; UNSIGNED CHAR MSG [MAX_ERROR_MSG 1]; SWORD CBMSG; Retcode rc; cstring stemp; if (! M_bopen) returnaf false; M_SQuery.Format ("Update% s set", m_stable); intlf = 0; for (int F = 0; f IF (m_sfieldvalue [f]! = ") / * data is empty * / {if (m_bnumeric [f]) STEMP.FORMAT ("% s =% s ", m_sfieldname [f], m_sfieldvalue [f]); ELSE STEMP.FORMAT ("% s =% s", m_sfieldname [f], quote (m_sfieldValue [f]));} else STEMP.FORMAT ("% s =% s", m_sfieldname [f], null); m_squery = STEMP;} m_squery = "where"; FF = 0; for (f = 0; f M_SQuery = STEMP;}} Memset (Char *) szquery, 0x00, sizeof (szquery); strcpy ((char *) szquery, m_squery); SQLAllocStmt (HDBC, & HSTMT); RC = SQLEXECDirect (HSTMT, SZQUERY, SQL_NTS); m_NERROR = (int) RC; if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) {SQLTransact (henv, hdbc, SQL_COMMIT); SQLFreeStmt (hstmt, SQL_DROP); return TRUE;} else {m_sErrMsg = ""; while (SQLError (0, 0, hstmt , SzsqlState, & Nerr, MSG, SIZEOF (MSG), & CBMSG) == SQL_SUCCESS M_SERRMSG = "/ T / T" (CString) MSG "/ n"; SQLFreeStmt (HSTMT, SQL_DROP); Return False;} } / * Find a record --- Return value 1: There is no error to find a record-1: Error 0 in the lookup process: There is no error in the lookup process, but no record of the condition, that is, no return record * / int CWdcDatabase :: SelectMatchingRecords (CString & sWhereContext) {unsigned char szSQLSTATE [6]; SDWORD nErr; unsigned char msg [MAX_ERROR_MSG 1]; SWORD cbmsg; CString sTemp; char szData [MAX_DATA]; SDWORD cbData; RETCODE rc; if (! m_bopen) return -1; STEMP = ""; INT ff = 0; for (int J = 0; j IF (SwhereContext == ") M_SQuery.Format (" SELECT% S from% s ", STEMP, M_STABLE); Else M_SQuery.Format (" SELECT% S from% s where% s ", STEMP, M_STABLE, SWHERECONTEXT); SQLAlocStmt (HDBC, & HSTMT_SELECT); MEMSET ((char *) szquery, 0x00, sizeof (szquery)); strcpy ((char *) szquery, m_squery); Rc = SQLEXECDirect (hstmt_select, szquery, sql_nts); m_nerror = (int)) RC; IF (RC == SQL_SUCCESS || RC == SQL_SUCCESS_WITH_INFO) {RC = SQLFETCH (HSTMT_SELECT); if (rc == SQL_SUCCESS || RC == SQL_SUCCESS_WITH_INFO) {/ * Perform success, and data * / for (int F = 0; F Return 1; / * Perform success, and have data * /} else if (rc == 100) {/ * look up success, but no data * / return 0;}} m_serrmsg = ""; while (SQLError (0, 0 , hstmt_select, szSQLSTATE, & nErr, msg, sizeof (msg), & cbmsg) == SQL_SUCCESS) m_sErrMsg = "/ t / t" CString (msg) "/ n"; SQLFreeStmt (hstmt_select, SQL_DROP); hstmt_select = NULL Return -1;} Bool cwdcdatabase :: nextmatchingrecord () {unsigned char szsqlstate [6]; sdword nerr; unsigned char msg [MAX_ERROR_MSG 1]; SWORD CBMSG; Char Szdata [MAX_DATA]; SDWORD CBDATA; RETCODE RC; IF (! m_bopen) Return False; rc = SQLFetch (hstmt_select); if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) {for (int f = 0; f m_serrmsg = ""; while (sqlerror (0, 0, hstmt_select, szsqlstate, & nerr, msg, sizeof (msg), & cbmsg) == SQL_SUCCESS M_SERRMSG = "/ T / T" CSTRING (MSG) "/ N "; SQLFreeStmt (hstmt_select, sql_drop); hstmt_select = null; Return False;} / * Statistical Equivalent Record Total Return Value-1: Perform Error> 0: Perform Success, and Return Statistics * / Long CHAR SZSQLSTATE [6]; SDWORD NERR Unsigned Char errmsg [500]; SWORD CBMSG; long Lret; / * Return Value * / Unsigned char lpselect [1000]; MEMSET ((char *) lpselect, 0x00, sizeof (lpselect)); if (ssel == ") STEMP.FORMAT (" SELECT COUNT (*) from% s ", m_stable); Else STEMP.FORMAT ("SELECT Count (*) from% s where% s", m_stable, ssel); if (m_bOpen!) return -1; strcpy ((char *) lpSelect, sTemp); SQLAllocStmt (hdbc, & hstmt_select); rc = SQLExecDirect (hstmt_select, lpSelect, SQL_NTS); if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO ) {/ * SQLExecDirect True * / SQLBindCol (hstmt_select, 1, SQL_INTEGER, & lRet, sizeof (int), NULL); rc = SQLFetch (hstmt_select); if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) {/ * have data * / SQLFreeStmt (hstmt_select, SQL_DROP); hstmt_select = NULL; return lRet;} else if (rc == 100) {/ * no data * / SQLFreeStmt (hstmt_select, SQL_DROP); hstmt_select = NULL; return 0;} else if (RC <0) {/ * error * / goto end;}} else {/ * sqlexecdirect false * / goto end;} end: {m_serrmsg = ""; while (sqlerror (0, 0, hstmt_select, szsqlstate, & nerr, Errmsg, Sizeof (Errmsg), & CBMSG) == SQL_SUCCESS M_SERRMSG = "/ T / T" CSTRING (ERRMSG) "/ N"; SQLFreeStmt (hstmt_select, SQL_DROP); hstmt_select = NULL; return -1;}} / * function is useful, for use in ensuring the release of the statement handle * / void CWdcDatabase :: DropHstmtSelect () {if (hstmt_select = NULL!) {SQLFreeStmt (hstmt_select , SQL_DROP); hstmt_select = null;}}
