One question of initialization adoDB
You need to first call the OLE in InitInstance (), then import C: / Program files / common files / system / ado / msado15.dll, after the above steps, next to the next steps can be called _ConnectionPtr and _RecordSetPtr To establish a connection object and record set object. To simplify the programming, I encapsulated a very simple class library CDB to achieve a simple call to _ConnectionPtr.
// db.h: interface for the cdb class.////
#if! defined (__ db_h __) # Define __db_h__
#if _MSC_VER> 1000 # prgma overce # endif //_MSC_VER> 1000
#import "c: / program files / common files / system / ado / msado15.dll" NO_NAMESPACE / RENAME ("EOF", "ADOEOF") / Rename ("LockTypeenum", "AdolockTyPeenum") / Rename ("DattyPeenum", "adoDataTypeEnum") # include "icrsint.h" inline void TESTHR (HRESULT x) {if FAILED (x) _com_issue_error (x);} inline BOOL IS_VT_NULL (unsigned short x) {return (x == VT_NULL TRUE: FALSE)? }
Class CDB {public: CDB (); Virtual ~ CDB (); _RecordSetPtr Exec (CSTRING & STRSQL); void close ();
PRIVATE: _CONNECTIONPTR M_CONN;};
class CSecUtil {public: static CString StrReplace (LPTSTR lpszString, LPTSTR lpszKey, LPTSTR lpszReplace); static BOOL StrCheck (LPCTSTR);};
#endif //! defined (__ db_h__)
// db.cpp: importation of the cdb class.////
#include "stdafx.h" #include "db.h"
#ifdef _debug # undef this_filestatic char this_file [] = __ file __; # Define new debug_new # Endif
//// cdb construction / destruction //
CDB :: CDB () {Try {m_conn.createInstance (__ uuidof (connection)); // _BSTR_T connStr = "driver = {SQL Server}; server = 127.0.0.1; dataBase =; uid =; pwd ="; // for mssql _bstr_t connstr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =; user id = admin; jet oledb: database password ="; // for ACCESS} catch (_com_error & e) {AfxMessageBox (e.Description ( ));}} CDB :: ~ cdb () {if (m_conn-> state) {m_conn-> close ();}}
_RecordsetPtr CDB :: Exec (CString & strSql) {_RecordsetPtr rs; try {rs = m_Conn-> Execute ((_ bstr_t) strSql, NULL, adCmdText);} catch (_com_error & e) {AfxMessageBox (e.Description ());}
Return RS;
Void CDB :: Close () {if (m_conn-> state) {m_conn-> close ();}}
Fill the connStr in need, you can use it.
CDB DB;
Establish an object, call DB.exec to execute the SQL statement, which is implemented by calling the EXECUTE () method of the connection object, and the exec () method is called back to a _RecordSetPtr. Call db.close () Turn off the database.
The second type of conversion successfully executed a SELECT operation After saving records from the database in the database, the record set object defined by _RecordSetPtr, can be removed by calling the getCollect method, such as: CDB DB; _RecordSetPtr Rs; cstring strsql; strsql = "SELECT ID, User from logon"; rs = db.exec (strsql); if (RS-> EOF) ... Else RS-> getCollect ("ID");
However, call getCollect () returned to _variant_t (related to MSDN), you need to convert it to a control to the control to display, of course, the original type of data is defined by the database field, which can be used in judgment structures. VT This variable takes out the current data type. This variable returns a value of a VARTYPE type (see Table 1). The following is the conversion of the field type of Access, similar to the field type of Access, and most of which can be converted according to the following method.
Number: RS-> getCollect ("ID"). INTVAL; BYTE: RS-> getCollect ("ID"). BVAL; Text / Remarks / Time: (_ BSTR_T) (RS-> GetCollect ("User" )
It should be noted here that for the digital type, it is necessary to judge the case where there is no symbol. Below is a definition of a Variant structure for data types. Note The type of Vartype is enumerated later. For specific types of conversions can be operated by Character Table 1, for a type of VARTYPE type, can search in MSDN with VARENUM (I am using MSDN 2003 April).
Longlong llval; // vt_i8. Long lval; // vt_i4. Byte bval; // vt_ui1. Short ival; // vt_i2. Float fltval; // vt_r4. Double dblval; // vt_r8. Variant_bool8; // vt_bool. _Variant_bool bool; SCODE scode; // VT_ERROR CY cyVal;. // VT_CY DATE date;. // VT_DATE BSTR bstrVal;. // VT_BSTR IUnknown * punkVal;. // VT_UNKNOWN IDispatch * pdispVal;. // VT_DISPATCH SAFEARRAY * parray.; // vt_Array | *. Byte * pbval; // vt_byref | vt_ui1. Short * pipe; // vt_byref | vt_ . I2 LONG * plVal;. // VT_BYREF | VT_I4 LONGLONG * pllVal; // VT_BYREF | VT_I8 FLOAT * pfltVal;.. // VT_BYREF | VT_R4 DOUBLE * pdblVal;. // VT_BYREF | VT_R8 VARIANT_BOOL * pboolVal; // VT_BYREF | . VT_BOOL _VARIANT_BOOL * pbool; SCODE * pscode; // VT_BYREF | VT_ERROR CY * pcyVal;. // VT_BYREF | VT_CY DATE * pdate;. // VT_BYREF | VT_DATE BSTR * pbstrVal;. // VT_BYREF | VT_BSTR IUnknown ** ppunkVal. ;
. // VT_BYREF | VT_UNKNOWN IDispatch ** ppdispVal;. // VT_BYREF | VT_DISPATCH SAFEARRAY ** pparray;. // VT_ARRAY | * VARIANT * pvarVal;. // VT_BYREF | VT_VARIANT PVOID * byref; // Generic ByRef CHAR cVal;. // vt_i1. Ushort uival; // vt_ui2. Ulong ulval; // vt_ui4. Ulonglong ullval; // vt_ui8. Int tentval; // vt_int. Uint uintval; // vt_uint. Decimal * pdecval // vt_byref | vt_decimal. Char * PCVAL; // vt_byref | vt_i1. ushort * puival; // vt_byref | vt_ui2. ulong * pulval; // vt_byref | vt_ui4. Ulonglong * PullVal; // vt_byref | vt_ui8. INT * PINTVAL; // vt_byref | vt_int. Uint * puintval; // vt_byref | vt_uint. (Table 1) There is also an important value Vt_null, which indicates that the data in the database is empty. The case where VT_NULL is not processed on some controls, for example: ListCtrl. If the data is displayed using ListCtrl, it is possible to determine the following method: IF (RS-> getCollect ("user"). VT == vt_null) // Record is Nullelse // Record is non-null
The three security issues are now popular SQL INJECTION, so filtration of strings is a very important issue. If you use VB to do such a program, it can be easily implemented, and the string operation in VB is easy, and the regular expression in VBScript can also be used (VCRIPT can also use the regular expression in VBScript, however, Method I forgot, remember there is a related article in CodeProject. Or use the ready-made libraries such as Boost, Greta. If you don't regular expression, you can also use the following method:
/// Check if the specified string contains illegal characters // Bool Strcheck (LPCTSTR LPSZSTRING) {cstring strkey = "/ '///"% # | = -) (* & ^ $ @! `~ :; <>,.? / "; Int LEN = strlen (lpszstring); for (int i = 0; i Return True;} If the input needs to be used to use "'" or "" "these two symbols, it should also be converted .// / / For Access and MSSQL // Struser.Replace (" /' "," / '/ '""; struser.replace ("/", "/" / "); //// For MySQL // strUser.Replace ( "/ '", "///'");strUser.Replace("/" "," /// ""); for use of the search, then, need to filter After "_" and "#", after the above steps, it can guarantee the chance of SQL INJECTION. For multi-user systems, the logged password should be encrypted using the MD5 method. Try not to use Access as a developed database, the security is really bad, even if you add a password, you can easily crack it locally. It is recommended to use mysql or MSSQL. Author Blog: http://blog.9cbs.net/cdrea/
