10, bonded data
Define a binding class to bind its member variables to a specified recordset to facilitate access to the field value of the recordset.
(1). Delicate a class from cadorecordbinding:
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 field to be bound to the variable name is used 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 *, GetADOBindingEntries) (VOID) PURE;}; BEGIN_ADO_BINDING also icrsint.h macro definitions file, the content is: #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 also icrsint.h macro definition file: #define ADO_VARIABLE_LENGTH_ENTRY2 (Ordinal, DataType, Buffer, Size, Status, Modify) / {Ordinal, / DataType , / 0, / 0, / size, / offsetof (AdorowClass, Status), / 0. / Classoffset (Cadorecordbinding, AdorowClass), / Modify}, # define end_ado_binding macro definitions are also ICRSINT .h file: #define end_ado_binding () {0, Adempty, 0, 0, 0, 0, 0, 0, 0, False}}; / return}}; / return}}; / return}}; / RTURN RGADOBINDINTRIES;} (2). Bind
_RecordSetPtr RS1; Iadorecordbinding * picrs = null; ccustomRS RS; ... RS1-> queryinterface (__ uuidof (ipvoid *), (lpvoid *) & picrs); picrs-> bindtorecordset (& RS);
The derived class must be binded through the Iadorecordbinding interface, calling its bindtorecordset method.
(3). The variable in the RS is 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.lphoneestatus == adfldok? rs.m_szphone : "")); if (rs.lphoneestatus == adfldok) STRCPY (rs.m_szphone, "777"); testhr (Picrs-> Update (& RS)); // add change to the bathrs1-> movelnext (); } Rs1-> filter = (long) Adfilternone; ... if (picrs) picrs-> release (); rs1-> close (); pconn-> close (); as long as the status of the field is Adfldok, 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 with the IadorecordBinding interface.
IF (failed (picrs-> addnew (& rs)) ......
11. Access long data
Long data in Microsoft SQL includes TEXT, Image, etc. Type of data, 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 :: moderad); byte bval [chunksize 1]; uint uisread = 0; // Create a Safe Array to store the 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 12. It is also important to use SafeArray issues to use SafeArray because it is often used in ADO programming. Its main purpose is to pass the passage of array parameters in Automation. Because in a network environment, arrays cannot be delivered directly, but they must be packaged into safgonaray. Essentially SafeArray adds a descriptor to add a description of the usual array, indicating that its dimension, length, boundary, element type, etc. SafeArray is not used alone, but is then packaged in a variable of the Variant type, and then transfer it as a parameter. The value of VARIANT's VT member if it contains VT_Array│ ..., then it is packaged is a SafeArray, and its Parray member is a pointer to SafeArray. The type of element in SafeArray can be any type of Variant's encapsulation, including the Variant type itself. Use SafeArray specific steps: Method 1: Packing a SafeArray: (1). Define variables, such as: Variant Varchunk; SafeArrayBound RgsAbound [1]; (2). Create a SafeArray descriptor: Uisread = f.read (bval, chunksize); // read array from a file.if (uisread == 0) Break; rgsabound [0] .CELEments = uisread; rgsabound [0] .llbound = 0; PSA = SafeArrayCreate Vt_ui1, 1, rgsabound; (3). Place data elements to SafeArray: For (long index = 0; index VARCHUNK.VT = VT_Array│VT_UI1; VARCHUNK.PARRAY = PSA; This allows VARCHUNK as a parameter. Steps to read the data in SafeArray: (1). Read by SafeArraygetElement BYTE BUF [Lisread]; for (long index = 0; index BYTE * BUF; SafearrayAccessData (Varchunk.Parray, (void **) & buf); F.Write (BUF, Lisread); SafearrayunAccessData (VARCHUNK.PARRAY); (2). Write buffer: BYTE * BUF; :: SafeArrayAccessData (PSA, (void **) & buf; for (long index = 0; index RST-> filter = _bstr_t ("Name = 'Zhao Wei' and Gender = 'Female'"); Note the following questions when using conditional expression: (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 to be used in parentheses, and 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 Like Like The compared is a string containing wildcard *, as an asterisk represents several arbitrary 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 value is Variant. If the filtering condition is an array composed of bookmarks, then the array is required to SafeArray, then package it to a variant. Or in the _variant_t type variable, it is then assigned to the Filter property. 15. Index and Sort (1), establish an index When you look for a Find method with a field, in order to speed up the speed, you can temporarily establish an index inside the recordset in this field. As long as the Optimize property of this field is set to TRUE, for example: PRST-> FIELDS-> GetItem ("Name") -> Properties-> GetItem ("Optimize") -> PutValue ("true"); prSt-> Find ("Name = 'Zhao Wei'", 1, AdSearchForward); ... prSt-> Fields-> GetItem ("Name") -> Properties-> GetItem ("Optimize") -> PutValue ("false"); prSt-> Close (); Description: Optimize property is The attribute provided by the Provider (called dynamic attributes in ADO), and ADO itself does not have this property. (2), sorting to be sorted is also very simple, as long as you set the keyword list to be sorted to the sort properties of the Recordset object, for example: pRstAuthors-> CursorLocation = adUseClient; pRstAuthors-> Open ( "SELECT * FROM mytable", _ variant_t ((IDispatch *) pConnection), adOpenStatic, adLockReadOnly, adCmdText); ...... pRst-> Sort = "name DESC, Age ASC "; Between the keyword (ie the field name) is separated by a comma. If you want to sort in a keyword descending order, you should add a null after the keyword, plus DESC (such as above). 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 transaction ADO is also very simple, just call three methods of the Connection object in the appropriate location, these three methods are: (1), call at the beginning of the transaction PCNN-> BeGintrans (); (2), call PCNN-> CommitTrans () in the end of the transaction and success, calls when the transaction ends and fails PCNN-> rollbackTrans (); When using transaction processing, minimize the range of transactions, that is, reduce the time interval between transactions until 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" "; database = master; uid = lad;"; If the program is not followed, the program is running inevitable. If the Authentication option is SQL Server and Windows NT, the connection string used by your program may not contain a trusted_connection parameter, such as: "Provider = SQLOLEDB; server = 888; database = master; uid = lad; pwd = 111;"; "because the default value given by 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. E.g: 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 possible to judge whether a table exists or whether it is successfully opened. It 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, "it does not exist "," Tips ", MB_OK │ MB_ICONWARNING);} (2) Otherwise, it can be used to use a point, that is, there is a table named Sysobjects in each database on the MS-SQL server, view this table. The content knows 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. E.g:
