ADO database programming
Li Anne
February 28, 2002
Abstract This paper briefly introduces the basic steps for client database programming in VC 6.0, as well as solutions for common problems, and can be used for reference.
Keyword ADO VC database programming
ADO is currently a popular client database programming technology in a Windows environment. ADO is an advanced programming interface established on the underlying technology of OLE DB, so it has a powerful data processing function (handling various types of data sources, distributed data processing, etc.) and extremely simple, easy to use Programming interfaces and thus have been widely used. And according to Microsoft's intentions, OLE DB and ADO will gradually replace ODBC and DAO. There are a lot of articles and books for ADO applications, and this paper focuses on the perspective of beginners, briefly explore some of the problems when using ADO programming in VC . We wish to read this article, you have some understanding of the basic principles of ADO technology. First, using the ADO programming ADO in VC is actually a component consisting of a set of Automation objects, so it can be used as an ADO like any other Automation object. The most important objects in Ado have three: Connection, Command, and RecordSet, which represents the connection object, command object, and record set object, respectively. If you are familiar with the ODBASE (CRecordset) programming in the MFC, then learn ADO programming is easy. One of the following three methods can be employed when programming using ADO: 1. Using the pre-processing instruction # import # import "c: / program files / common files / system / ado / msado15.dll" / no_namespace rename ("eof", " "), But be careful not to put the beginning of the stdafx.h file, but should be placed behind all the include instructions. Otherwise it will be wrong when compiling. The program reads the type library information in MSADO15.DLL during the compilation process, automatically generates the header files of this type library and implement file Msado15.TLH and Msado15.tli (in your debug or release directory). All objects and methods of ADO are defined in both files, as well as some constants of some enumerations. Just call these methods directly, we are very similar to the COLEDISPATCHDRIVER class using the COLEDISPATCHDRIVER class in the MFC. 2, using the CIDISPATCHDRIVER in the MFC is the derived class of the COLLDISPATCHDRIVER class by reading the type library information in MSADO15.DLL, and then calls the ADO object by it. 3. Use the API provided directly with COM as follows: CLSID CLSID; HRESULT HR = :: CLSIDFROMPROGID (L "AdoDb.Connection", & clsid; if (Failed (HR)) {...} :: CoCreateInstance (CLSID , NULL, CLSCTX_SERVER, IID_IDISPATCH, (VOID **) & PDISPATCH; IF (Failed (HR)) {...} The above three methods, first and second like, may be the first to use, third Programming may be the most troublesome. However, the third method is also the highest efficiency, the size is also the smallest, and the control capability of ADO is also the strongest. According to Microsoft data, the first method does not support the default parameters in the method call, of course, the second method is the same, but the third is not the case. The level of the third method is also highest. When you need to bypass the ADO, you must use the third method when you call the OLE DB underlayer. The key to ADO programming is to skilfully use ADO to provide various objects, methods, properties, and multi-contains, and containers (Collection).
Also, if you are programmed on a large database such as MS SQL or Oracle, you must be skilled in using the SQL language. Second, the programming step using the #import method This is recommended to use #import method because it is easy to learn, easy to use, and the code is relatively simple. 1. Add the #import instruction to open the stdafx.h file, add the following to all the include instructions: #include
Note that the connection string parameters in the Open method of the Connection object must be the BSTR or _BSTR_T type. In addition, this example is to establish a connection through the OLE DB Provider, so there is no need to establish a data source. (2) Connect to the Database Server connection string format with the Database Server connection string format and direct use of ODBC programming: _BSTR_T strConnect = "DSN = Datasource_Name; Database = Database_name; uid = user_name; pwd = password;"; at this time with ODBC Like programming, you must first create a data source. 3, define _recordsetptr type variables, and open the data set definition _RecordSetPtr type variable, and then invoke the RECORDSET object's Open method to open a data set. So the Recordset object is similar to the CRecordset class in the MFC, which also has the current record, the current record pointer concept. Such as: _RecordsetPtr m_pRecordset; if (FAILED (m_pRecordset.CreateInstance (__uuidof (Recordset))) {m_pDoc-> m_initialized = FALSE; return;} try {m_pRecordset-> Open (_variant_t ( "mytable"), _variant_t ((IDispatch *! PMYCONNECT, TRUE, AdopenKeyset, Adopopopoptimistic, Adcmdtable; }: messagebox (NULL, "Unable to open myTable table.", "Tips", MB_OK | MB_ICONWARNING);} The open method of the Recordset object is very Important, its first parameter can be a SQL statement, a table name or a command object, etc .; the second parameter is the pointer to the previously established connection object. In addition, the Execute method of the connection and Command object can also Get record set, but read-only .4, read the current recorded data I think the most convenient way to read data is as follows: try {m_precordset-> movefirst (); while (m_precordset-> adoEOf == variant_false) {/ / Retrieve Column's Value: CString Sname = (Char *) (_BSTR_T) (m_precordset-> fields-> GetItem (_variant_t ("name")) -> value); Short Cage = (SHORT) (m_precordset-> fields-> GetItem (_VARIANT_T ("age")) -> value); // do something what you want to do: ... m_precordset-> MoveNext ();}} // try catch (_COM_ERROR & E) {CSTRING STR = (char *) E.DESCRIPTION (); :: Messagebox (null Str "/ n has a problem. "," Tips ", MB_ok | MB_ICONWARNING);
The Name and AGE in this example are field names, and the read field values are saved in the SNAME and CAGE variables, respectively. The fields in the example are the container of the Recordset object, and the GetItem method returns the field object, and the value is an attribute of the field object (ie the value of the field). In this case, the method of manipulating the object attribute should be grasped. For example, the value to obtain the value attribute of the field object can reference it (such as above) directly with the attribute name value, but can also call the GET method, for example: cstring sname = (char *) (_ bstr_t) (m_precordset-> Fields- > GetItem (_variant_t ("name")) -> getValue ()); From this example, it is also possible to see whether to reach the end of the recordset, use the AdoEOF attribute of the recordset, if it is true, it is true. Not arrived. Determine whether to reach the beginning of the recordset, the BOF property can be used. In addition, reading data has a method to define a binding class, then get field values by binding variables (see later described later). 5, modify data method 1: try {m_precordset-> movefirst (); while (m_precordset-> adoeof == variant_false) {m_precordset-> fields-> getItem (_variant_t ("name")) -> value = _bstr_t ("Zhao Wei "); ... m_precordset-> update (); m_precordset-> MoveNext ();}} // TRY changes the value of the value attribute, that is, the value of the field is changed. Method 2: m_precordset-> fields-> GetItem (_variant_t ("name")) -> PUTVALUE (_BSTR_T ("Zhao Wei")); method 3: is the method of defining binding classes (see later described later). 6. After adding a record new record, it is automatically recorded. There are two forms of the AddNew method, and one contains parameters, and the other does not with parameters. Method 1 (without parameters): // Add New Record Into this table: try {= (! M_precordset-> supports (adaddnew)) Return;
m_precordset-> addnew (); m_precordset-> fields-> getitem (_variant_t ("name")) -> value = _BSTR_T ("Zhao Wei"); m_precordset-> fields-> GetItem (_variant_t ("gender") -> Value = _BSTR_T ("female"); m_precordset-> fields-> getitem (_variant_t ("age")) -> value = _variant_t (short); m_precordset-> fields-> getITEM (_variant_t ("marry") ) -> value = _BSTR_T ("unmarried"); m_precordset-> Update ();} // try catch (_COM_ERROR & E) {:: MessageBox (null, "is a problem.", "Tips", MB_ok | MB_ICONWARNING) This method will also call Update (). Method 2 (with parameters): _variant_t varname [4], Narvalue [4]; varName [0] = L "Name"; varName [1] = L "gender"; varName [2] = L "agname"; varname [ 3] = L "marry"; Narvalue [0] = _ bstr_t ("Zhao Wei"); Narvalue [1] = _ bstr_t ("female"); Narvalue [2] = _ variant_t (short) 20); Narvalue [3] = _BSTR_T ("unmarried");
const int nCrit = sizeof varName / sizeof varName [0]; // Create SafeArray Bounds and initialize the array SAFEARRAYBOUND rgsaName [1], rgsaValue [1]; rgsaName [0] .lLbound = 0; rgsaName [0] .cElements = nCrit ; SAFEARRAY * psaName = SafeArrayCreate (VT_VARIANT, 1, rgsaName); rgsaValue [0] .lLbound = 0; rgsaValue [0] .cElements = nCrit; SAFEARRAY * psaValue = SafeArrayCreate (VT_VARIANT, 1, rgsaValue); // Set the values For Each Element of the Array HRESULT HR1 = S_OK.HR2 = S_OK; for (long i = 0; I
Try {m_precordset-> MoveFirst (); while (m_precordset-> adoEOf == variant_false) {cstring sname = (char *) (_ bstr_t) (m_precordset-> fields-> getitem (_variant_t ("name")) -> value) ; If (:: messagebox (null, "Name =" SNAME "/ N delete her?", "Tips", MB_YESNO | MB_ICONWARNING) == iDYES) {m_precordset-> delete (adAffectCurrent); m_precordset-> Update } M_precordset-> MoveNext ();}} // Try catch (_ERROR & E) {:: MessageBox (null, "," prompt ", MB_OK | MB_ICONWARNING);} 8, use parameters The command Command object is representative of a provider, such as a SQL statement, and the like. The key to using the Command object is to set the statement indicating the command to the CommandText property, and then call the Execute method of the Command object. In general, there is no need to use parameters in commands, but sometimes use parameters, you can increase its flexibility and efficiency. (1). Establish a connection, command object, and record set object this example indicating that the command is a SQL statement (SELECT statement). Question mark in the SELECT statement? On behalf of the parameters, if you want multiple parameters, put more questions, each question mark represents a parameter. _CONNECTIONPTR CMD1; parametersptr * params1 = null; // not an instance of a smart pointer._parameterptr param1; _recordsetptr rs1;
try {// Create Connection Object (1.5 Version) Conn1.CreateInstance (__uuidof (Connection)); Conn1-> ConnectionString = bstrConnect; Conn1-> Open (bstrEmpty, bstrEmpty, bstrEmpty, -1); // Create Command Object Cmd1. CreateInstance (__UUIDOF (Command)); cmd1-> ActiveConnection = conn1; cmd1-> commandtext = _BSTR_T ("SELECT * from myTable WHERE AGE activeConnection = conn1; (2). Create a parameter object, and give parameter assignment // crete parameter ObjectParam1 = cmd1- > CREATEPARAMETER (_BSTR_T (BSTREMPTY), AdINteger, Adparaminput, -1, _variant_t ((long) 5); param1-> value = _variant_t (long) 5); cmd1-> parameters-> append (param1); command with command The object's method to create a parameter object, where the length parameter (third) is fill in -1 if it is a fixed length type, if it is a variable length of the string, fill in the actual length. Parameters is a container of the command object, and its Append method is to append the created parameters to the container. The parameters in which the append in the order in the SQL statement correspond to the left to right one by one in the order. (3). Implementation command Open Record // Open RecordSet Objectrs1 = CMD1-> Execute (& VTempty, & Vtempty2, AdcmdText); But note that Recordset obtained with the Execute method of Command and Connection objects is read-only. Because we can't set its LockType property before opening RecordSet (its default value is read-only). The LockType is set up after opening. I found that after the above method, after the record set RS1, not only the records in the RS1 cannot be modified, even if any records in the same table are modified directly with the SQL statement. To modify data, or use Recordset's own Open method, such as: try {m_precordset-> open (idispatch *) cmd1, vtMissing, adopenStatic, adlickTimistic, adpMdunspecified;} catch (_CM_ERROR & E) {:: MessageBox (NULL, "MyTable Table does not exist.", "Tips", MB_OK | MB_ICONWARNING);} The OPEN method of the Recordset object is great, the first parameter can be a SQL statement, a table name, a command object pointer, etc. .
9. Responding to the ADO notification event notification event is that when a particular event occurs, inform the client, in other words, it is a specific method (ie, the process of processing function) is called by the Provider. So in response to an event, the most critical is to implement the handler of the event. (1) derived from a class ConnectionEventsVt the interface in response to the notification event _Connection, the interface should be derived from a class ConnectionEventsVt: class CConnEvent: public ConnectionEventsVt {private: ULONG m_cRef; public: CConnEvent () {m_cRef = 0;} ; ~ CConnEvent () {}; STDMETHODIMP QueryInterface (REFIID riid, void ** ppv); STDMETHODIMP_ (ULONG) AddRef (void); STDMETHODIMP_ (ULONG) Release (void); STDMETHODIMP raw_InfoMessage (struct Error * pError, EventStatusEnum * adStatus, struct _Connection * pConnection); STDMETHODIMP raw_BeginTransComplete (LONG TransactionLevel, struct Error * pError, EventStatusEnum * adStatus, struct _Connection * pConnection); ......};. (2) implemented for each event handler (usually with raw _ prefix-treat it implements): STDMETHODIMP CConnEvent :: raw_InfoMessage (struct Error * pError, EventStatusEnum * adStatus, struct _Connection * pConnection) {* adStatus = adStatusUnwantedEvent; return S_OK;};
Some methods don't need it, but you must implement it, just simply return a S_OK. However, if you want to avoid frequently being called, you should also set the adstus parameter to AdstatusunWantedEvent, then it will not be called later after this call. There must also be three ways of queryinterface, addref, and release: stdmethodimp cconnevent :: queryinterface (refiid riid, void ** ppv) {* ppv = null; if (riid == __UUIDOF (iUnknown) || riid == __UUIDOF ConnectionEventsVt)) * ppv = this; if (* ppv == NULL) return ResultFromScode (E_NOINTERFACE); AddRef (); return NOERROR;} STDMETHODIMP_ (ULONG) CConnEvent :: AddRef () {return m_cRef;}; STDMETHODIMP_ ( Ulong) cconnevent :: release () {if (0! = --M_cref) return m_cref; delete this; return 0;} (3). Start response notification Event // Start USING THE CONNECTION Events iconnectionPointContainer * PCPC = NULL; iconnectionPoint * pcp = null; hr = pconn.createInstance (__ uuidof (connection)); if (Failed (HR)) Return;
hr = pConn-> QueryInterface (__ uuidof (IConnectionPointContainer), (void **) & pCPC); if (FAILED (hr)) return; hr = pCPC-> FindConnectionPoint (__ uuidof (ConnectionEvents), & pCP); pCPC-> Release () ; If (Failed (HR)) Return;
PCONNEVENT = new cconnevent (); hr = pConnevent-> queryinterface (__ uuidof (iUndnown), (void **) & punk; if (Failed (HR)) Return RC; HR = PCP-> Advise (Punk, & DWConnevt); PCP -> Release (); if (failed (hr)) return;
PCONN-> Open ("DSN = Pubs;", "sa", "", adconnectunspecified); that is, doing these things before the connection (OPEN). (4). Stop response notification event PCONN-> Close (); // stop using the connection events hr = pconn-> queryinterface (_ uuidof (iconnectionPointContainer), (void **) & pcpc); if (Failed (HR)) Return HR = PCPC-> FindConnectionPoint (__ uuidof (connectionectionevents), & PCP); PCPC-> Release (); if (Failed (HR)) Return RC; HR = PCP-> Unadvise (dwconnevt); PCP-> Release (); IF (Failed (HR)) Return; Do this after the connection is closed. 10. Bonding data defines a binding class, binding its member variables to a specified recordset, to facilitate access to the field value of the recordset. (1) from CADORecordBinding derive a class: class CCustomRs: public CADORecordBinding {BEGIN_ADO_BINDING (CCustomRs) ADO_VARIABLE_LENGTH_ENTRY2 (3, adVarChar, m_szau_fname, sizeof (m_szau_fname), lau_fnameStatus, false) ADO_VARIABLE_LENGTH_ENTRY2 (2, adVarChar, m_szau_lname, sizeof (m_szau_lname) , lau_lnameStatus, false) ADO_VARIABLE_LENGTH_ENTRY2 (4, adVarChar, m_szphone, sizeof (m_szphone), lphoneStatus, true) END_ADO_BINDING () public: CHAR m_szau_fname [22]; ULONG lau_fnameStatus; CHAR m_szau_lname [42]; ULONG lau_lnameStatus; CHAR m_szphone [14] Ulong LPhoneStatus;}; where the fields to be bound to the variable name began with begin_ado_binding macro. Each field corresponds to two variables, a value of a field, and the status of the field stores. The field is represented by the serial number starting from 1, such as 1, 2, 3, etc. It is important to note that if the field to be bound is a string type, the number of elements of the corresponding character array must be larger than the field length 2 (such as m_szau_fname [22], the length of the field Au_FNAME is actually actually Is 20), not so binding will fail. I analyzed more 2 may be a word in the end of the empty character NULL and the BSTR string at the end of the string (indicating the length of the BSTR). This issue may be an unexpected problem for beginners. The definition of the CadorecordBinding class is in the ICRSINT.H file, the content is: class cadorecordbinding {public: stdmethod_ (const ado_binding_entry *, getadobindingintries) (void) Pure;};
BEGIN_ADO_BINDING macro definition also icrsint.h document, content: #define BEGIN_ADO_BINDING (cls) public: / typedef cls ADORowClass; / const ADO_BINDING_ENTRY * STDMETHODCALLTYPE GetADOBindingEntries () {/ static const ADO_BINDING_ENTRY rgADOBindingEntries [] = {ADO_VARIABLE_LENGTH_ENTRY2 macro definition Also in ICRSINT.H file: #define ado_variable_length_entry2 (Ordinal, Datatype, Buffer, Size, Status, Modify) / {Ordinal, / Dattype, / 0, / 0, / size, / offsetof (AdorowClass, Buffer), / Offsetof (AdorowClass, Status), / 0, / Classoffset (Cadorecordbinding, AdorowClass), / Modify},
#define end_ado_binding macro is also in ICRSINT.H file: #define end_ado_binding () {0, Adempty, 0, 0, 0, 0, 0, 0, 0, False}}; / return rgadobindinTries;} (2) Bind_RecordSetPtr RS1; Iadorecordbinding * Picrs = NULL; CCUSTOMRS RS; ... RS1-> Queryinterface (_ UUIDOF (il), (lpvoid *) & picrs); picrs-> bindtorecordset (& RS); derived The class must be binded through the Iadorecordbinding interface, calling its Bindtorecordset method. (3). The variables in RS are the value of the current record field // set sort and filter condition: // step 4: manipulate the datars1-> fields-> GetItem ("au_lname") -> Properties-> GetItem (" Optimize ") -> value = true; rs1-> sort =" au_lname ASC "; rs1-> filter =" Phone Like '415 5 *' "
RS1-> MoveFirst (); while (variant_false == rs1-> endoffile) {printf ("Name:% S / T% S / TPHONE:% S / N", (rs.lau_fnamestatus == adfldok? Rs.m_szau_fname: "" ", (RS.LAU_LNAMESTATUS == Adfldok? Rs.m_szau_lname:" "), (rs.lphoneStatus == Adfldok? Rs.m_szphone:")); if (rs.lphoneestatus == adfldok) STRCPY (RS. " M_SzPhone, "777"); testhr (Picrs-> Update (& RS)); // add change to the bath system} RS1-> filter = (long) Adfilternone; ... IF (Picrs) Picrs-> Release (); rs1-> close (); pconn-> close (); as long as the status of the field is Adfldok, you can access. If you modify the field, don't forget to call the Picrs's Update (notice that the recordset's Update), then close, don't forget to release Picrs (ie picrs-> release ();). (4). You can also add a new record if (Failed (Picrs-> Addnew (& RS)))) ... 11. The long data accessed in Microsoft SQL includes Text, Image, etc. Such long types of data are treated as binary bytes. You can use the GetChunk and Appendchunk methods of the field object. Each time you can read or write a portion of all data, it remembers the location of the last accessed. But if you have access to other fields, you have come from the beginning. Please see the example below: // Write a photo to the database: Variant Varchunk; SafeArray * PSA; SafeArrayBound Rgsabound [1];
// vt_array | vt_ui1cfile f ("h: //aaa.jpg", cfile :: modeRead); Byte Bval [chunksize 1]; uint uisread = 0; // Create a Safe Array to Store's Array Of bytes while ( 1) {uisread = f.read (bval, chunksize); if (uisread == 0) Break; rgsabound [0] .CELEMENTS = uisread; rgsabound [0] .llbound = 0; PSA = SafeArrayCreate (vt_ui1, 1, rgsabound) ); For (long index = 0; index
_variant_t varChunk; BYTE buf [ChunkSize]; while (lPhotoSize> 0) {lIsRead = lPhotoSize> = ChunkSize ChunkSize:? lPhotoSize; varChunk = m_pRecordset-> Fields-> Item [ "photo"] -> GetChunk (lIsRead); for ( LONG INDEX = 0; Index To read the data in SafeArray: (1). Read byte BUF [Lisread] with SafeArraygetElement; for (long index = 0; Index This method reads and writes SafeArray, it can manipulate the SafeArray data buffer, which is fast than SafeArraygetElement and SafearrayPutelement. It is especially suitable for reading data. But don't forget to call :: SafeArrayunAccessData (PSA), otherwise it will be wrong. 13. Use the bookmark bookmark to uniquely identify a record in the recordset, used to quickly move the current record back to have accessed records, and filtering, etc. Provider will automatically generate a bookmark for each record in the recordset, and we only need to use it. We can't try to display, modify or compare bookmarks. The ADO's bookmark property represents the current recorded bookmark. Usage steps: (1). Create a variant type variable _variant_t varbookmark; (2). Save the current recorded bookmark value is the current value of the Bookmark property of the record set. VarBookmark = RST-> Bookmark; (3). Return to the previous record to set the bookmark value to the record to the bookmark attribute of the record set: // Check for WHether Bookmark Set for a recordiff f (varbookmark.vt == vt_empty) Printf ("NO Bookmark Set! / N"); Else Rst-> Bookmark = varbookmark; After setting, the current record will move to record points to the bookmark. 14. Setting the filter condition Recordset object's Filter property represents the current filtering condition. Its value can be a conditional expression (excluding WHERE keyword) that is connected by AND or OR, which is provided by a group of bookmarks or a FiltergroupENum enumeration value provided by ADO. After setting a new value for the Filter property, the current record pointer of the Recordset will be automatically moved to the first record that meets the filter criteria. For example: RST-> filter = _bstr_t ("Name = 'Zhao Wei' and Gender = 'Female'"); Note the following questions when using conditions express: (1), can form complex expressions, for example: RST-> filter = _bstr_t ("(Name = 'Zhao Wei' AND Gender = 'Female') OR Age <25"); But Microsoft does not allow it in brackets, then uses AND, for example,: RST-> Filter = _BSTR_T ("(Name = 'Zhao Wei' Or Gender = 'Female') And Age <25"); must be modified to: RST-> filter = _BSTR_T ("(Name = 'Zhao Wei' and age <25) or (gender) = 'Female' And Age <25) "); (2), the comparison operator in the expression can be a string containing wildcarder * after the LikeLike, the asterisk represents a number of characters. The head and tail of the string can be brought up as an asterisk * RST-> filter = _BSTR_T ("Name Like '* Zhao *'"); or only the tail with star: RST-> filter = _BSTR_T ("Name Like 'Zhao * '"); The type of Filter property is Variant. If the filtering condition is an array composed of bookmarks, you need to convert the array to SafeArray, then package it into a variant or _variant_t type variable, and then assign it to the Filter. Attributes. 15. Index and Sort (1), establish an index When you use a Find method to use a Find method, you can temporarily establish an index within the recordset in order to speed up the speed. Just set the Optimize property of this field to true, for example: prSt-> Fields-> GetItem ("Name") -> Properties-> GetItem ("Optimize") -> PutValue); PRST-> Find ("Name = 'Zhao Wei'", 1, AdSearchForward); ... prSt-> Fields-> GetItem ("Name") -> Properties-> GetItem ("Optimize") -> PUTVALUE ("False" "); prSt-> Close (); Description: Optimize property is the property provided by the Provider (called dynamic attributes in ADO), and ADO itself does not have this property. (2), sorting is also very simple, just set the keyword list to be sorted to the sort property of the Recordset object, for example: prStauthors-> CursorLocation = aduseclient; prStauthors-> Open ("Select * from myTable" _Variant_t (iDispatch *) PConnection, adopenStatic, adlickreadONLY, ADCMDTEXT); ... PRST-> Sort = "Name DESC, age ASC"; keyword (ie, field name) is separated by commas, If you want to sort a keyword descending order, you should add a null after the keyword, plus DESC (, in an example). Asc adding does not care when ascending. This operation is made by the index and does not perform physical sorting, so the efficiency is higher. But note that set the CursorLocation property of the record set to AduseClient before opening the record set, as shown in the example. The sort attribute value can be modified at any time when needed. 16. Transaction processing in the ADO is also very simple, just call three methods of the Connection object in the appropriate location, these three methods are: (1), call PCNN-> Begintrans at the beginning of the transaction. ); (2) When the transaction ends and successfully calls PCNN-> CommitTrans (); (3), call PCNN-> rollbackTrans () when the transaction ends and fails; when using transaction processing, try to reduce transactions The range is reduced from the time interval between the start to the end (submission or rollback) to improve system efficiency. You can also set the ISOLATIONLEVEL attribute value of the Connection object before you call the Begintrans () method, and see the technical information about ADO in MSDN for details. Third, using the ADO programming FAQ The following is discussed in the case of MS SQL 7.0 programming. 1. Connection failed possible Cause In Enterprise Managemer, open the Properties dialog box, in the Security tab, there is an option Authentication. If this option is Windows NT Only, the connection string used by your program must include the trusted_connection parameter, and its value must be yes, such as "provider = sqloledb; server = 888; trusted_connection = yes" "; data = Master; UID = LAD; "; If the program is not followed by the above operation, the connection will inevitably failed. If the Authentication option is SQL Server and Windows NT, the connection string used by your program may not include the trusted_connection parameters, such as "provider = sqloledb; server = 888; database = master; UID = lad; pwd = 111;"; Because the default value given by the ADO is NO, it can be omitted. I think it is still safe to take default. 2. Change the method of the current database to use the USE statement in tansct-sql. 3, how to determine if a database exists (1), open a view called Schemata in the Master database, which lists all the database names on the server. (2), the easier method is to use the USE statement, the existence is successful; unsuccessful, there is no existence. For example: try {m_pConnect-> Execute (_bstr_t ( "USE INSURANCE_2002"), NULL, adCmdText | adExecuteNoRecords);} catch (_com_error & e) {blSuccess = FALSE; CString str = "database INSURANCE_2002 absence / n!"; Str = E.DESCRIPTION (); :: MessageBox (NULL, STR, "Warning", MB_OK | MB_ICONWARNING);} 4. Determine if a table exists (1), and it is also determined whether or not there is any table, or whether it is successfully opened. is determined, is very convenient, for example: try {m_pRecordset-> Open (_variant_t ( "mytable"), _variant_t ((IDispatch *) m_pConnection, true), adOpenKeyset, adLockOptimistic, adCmdTable);} catch (_com_error & e) {:: MessageBox (NULL, "The table does not exist.", "Tips", MB_OK | MB_ICONWARNING); (2), otherwise the method can be used, that is, there is a table named SysObjects in each database on the MS-SQL server, and see if the contents of this table know if the specified table is in the database. (3), in the same, there is a view called Tables (View) in each database, see if the content of this view is known to be specified in the database. 5, type conversion problem (1), type variant_bool type variant_bool is equivalent to Short type. The VARIANT_BOOL is equivalent to short see it's definition below:. Typdef short VARIANT_BOOL (2), _ com_ptr_t class type can be automatically converted into conversion _ConnectionPtr IDspatch * type, which is actually an instance because _ConnectionPtr _com_ptr_t class, and This class has this type of conversion function. Similarly, _RecordSetPtr and _CommandPTR can also be converted. (3), _ bstr_t and _variant_t classes are useful in _bstr_t and _variant_t these two classes, saving many BSTR and VARIANT type conversion. 6. When the problem is opened, when the recording set is turned on, when the Recordset's Open method is called, the last parameter must not contain AdaSyncexecute, otherwise, because it is an asynchronous operation, data cannot be read when data is read. 7. Exception Problem For all calling ADO statements must be captured with TRY and CATCH statements, otherwise the program will abnormally exit when an exception occurs. 8. Using SafeArray Problem In the initial use, I have encountered a problem of hurting brains. Be sure to pay attention to: After defining the SafeArray pointer, if you intend to repeat multiple times, you can call :: SafeArrayDestroyData release data, But never call :: SafeArrayDestroyDescriptor, otherwise it will inevitably, even if you call SafeArrayCreate. For example: SafeArray * PSA; ... // when the data area no longer to be used ::: SafeArrayDestroyData (PSA); I analyzed an instance of a SafeArray when defining a PSA pointer (that is, SafeArray descriptor) Also also automatically created. But as long as one call :: SafeArrayDestroyDescriptor, the descriptor is destroyed. So I think :: SafeArrayDestroyDescriptor can not be called at all, even if the call must also be called. 9. Repeat the command object problem A command object If you want to repeat multiple times (especially with parameter commands), you should set it to true before the first execution is executed. This will slow down the first execution, but it can be accelerated in the future implementation. 10. Binding string string field Issues If the field to be bound is a string type, the corresponding character array element must be larger than the field length 2 (such as m_szau_fname [22], which is bound field au_fname The length is actually 20), so it will fail to bind. 11. Problems using appendchunk When you have just added a new record to your recordset, you cannot write data to a long data field (Image Type), you must call appendchunk after writing data to other fields. Write this field, otherwise an error. That is, Appendchun cannot be followed immediately after addNew. In addition, after writing other fields, you must also call Appendchunk, and after calling the UPDATE method of the record set, the appendchunk is called, otherwise it will be wrong when you call appendchunk. In other words, it is necessary to appendchunk before, update is behind. Thus, this time, I can't use AddNew, because the addNew with parameters will automatically call the record set Update, so appendchunk runs back to Update, only wrong! Therefore, this should be used with ADDNEW without parameters. I speculate that this may be a problem with MS SQL 7.0, and there is no problem in MS SQL 2000, but Appendchunk still cannot be after Update. Fourth, Summary Under the case, Connection and Command's Execute are used to perform commands that do not generate records, and the RECORDSET's OPEN is used to generate a recordset, of course, is not absolute. Special Command is mainly used to perform parameterized commands, can be executed directly by the Command object, or the Command object can be passed to the RECORDSET Open. The code snippet in this article is debugged in VC 6.0, Windows NT 4.0 SP6 and MS SQL 7.0. I believe that you should have a simple database program after you read it. Of course, you need to write more practical, complicated programs, you need to know more about Ole DB, ADO, and database platforms, I hope you will continue to work, you will be successful! For details, please refer to the Microsoft MSDN July 2000 CD or MS SQL 7.0 Online Data (Books Online). It is inevitable that there is a mistake and wrong in the article. Reference: 1, Microsoft MSDN Library - July 2000 / Platform SDK / Data Services / Microsoft Data Access Components (MDAC) / Microsoft ActiveX Data Objects (ADO) 2, Microsoft MS SQL 7.0 Online Data (Books Online) ---- Building SQL Server Applications / ADO and SQL Server3, Microsoft MS SQL 7.0 online documentation (Books online) ---- Building SQL Server Applications / Transact - SQL Reference4, Microsoft MSDN Library - July 2000 / Platform SDK / data Services / Microsoft data Access Components / Microsoft OLE DB Address: Li An, Huaibei Industry and Trade School, Huaibei Industry and Trade School, Huaibei Industry and Trade School, Luohe West Road, Anhui Province, China Postcode: 234000 Tel: 0557-3650004E-mail: lad@ah163.com
