Public
CHAR m_szau_fname[22];
ULONG Lau_fnamestatus;
CHAR m_szau_lname[42];
ULONG Lau_lnamestatus;
CHAR m_szphone[14];
ULONG Lphonestatus;
};
The field that will be bound is associated with the variable name with the Begin_ado_binding macro. Each field corresponds to a two variable, one that holds the value of the field, and the other holds the state of the field. fields are represented by ordinal numbers starting with 1, such as 1,2,3, and so on.
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 greater than 2 of the field length (for example, m_szau_fname[22], whose bound field au_fname the length of the actual 20), the binding will fail. My analysis of 2 more might be to hold the null character at the end of the string and a word at the beginning of the BSTR string (representing the length of the BSTR). This problem may be an unexpected problem for beginners.
The definition of the CADORecordBinding class is in the Icrsint.h file, which reads:
Class CADORecordBinding
{
Public
Stdmethod_ (const ado_binding_entry*, getadobindingentries) (VOID) PURE;
};
The definition of begin_ado_binding macros is also in the Icrsint.h file, which reads:
#define BEGIN_ADO_BINDING (CLS) public: \
typedef CLS Adorowclass; \
Const ado_binding_entry* Stdmethodcalltype getadobindingentries () {\
static const Ado_binding_entry rgadobindingentries[] = {
The definition of Ado_variable_length_entry2 macros is also in the Icrsint.h file:
#define ADO_VARIABLE_LENGTH_ENTRY2 (Ordinal, DataType, Buffer, Size, Status, Modify) \
{Ordinal, \
DataType, \
0, \
0, \
Size, \
Offsetof (Adorowclass, Buffer), \
Offsetof (Adorowclass, Status), \
0, \
Classoffset (CADORecordBinding, adorowclass), \
Modify},
The definition of the #define END_ADO_BINDING macros is also in the Icrsint.h file:
#define End_ado_binding () {0, adempty, 0, 0, 0, 0, 0, 0, 0, false}};\
return rgadobindingentries;}
(2). Binding
_RecordsetPtr Rs1;
IADORecordBinding *picrs=null;
Ccustomrs rs;
......
Rs1->queryinterface (__uuidof (iadorecordbinding),
(lpvoid*) &picrs));
Picrs->bindtorecordset (&RS);
The derived class must be bound through the IADORecordBinding interface, calling its BindToRecordset method.
(3). The variable in RS is the value of the current record field
Set Sort and filter condition:
Step 4:manipulate The data
Rs1->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.lphonestatus = = adFldOK)
strcpy (Rs.m_szphone, "777");
Testhr (Picrs->update (&rs)); ADD Change to the batch
Rs1->movenext ();
}
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 it. If you modify the field, don't forget to call Picrs Update first (note that it is not the recordset's update) before you close it, and don't forget to release Picrs (that is, picrs->release ()).
(4). You can also add new records with the IADORecordBinding interface at this time
if (FAILED (Picrs->addnew (&RS))
......
11. Access to Long data
Long data in Microsoft SQL includes such long types of data as text, image, and so on as binary bytes.
can be accessed using the GetChunk and AppendChunk methods of the Field object. Each time you can read or write a portion of all the data, it remembers where it was last accessed. But if you have access to another field in the middle, you have to start over again.
Take a look at the following example:
Write a picture to the database:
VARIANT Varchunk;
SAFEARRAY *PSA;
Safearraybound rgsabound[1];
Vt_array│vt_ui1
CFile f ("H:\\aaa.jpg", Cfile::moderead);
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<uisread;index++)
{
if (FAILED (SafeArrayPutElement (psa,&index,&bval[index)))
:: MessageBox (NULL, "Ah, it's wrong again.") "," hint ", mb_ok│mb_iconwarning);
}
VARCHUNK.VT = VT_ARRAY│VT_UI1;
Varchunk.parray = PSA;
try{
M_precordset->fields->getitem ("Photo")->appendchunk (Varchunk);
}
catch (_com_error &e)
{
CString str= (char*) e.description ();
:: MessageBox (null,str+ "\ n) It's gone wrong again." "," hint ", mb_ok│mb_iconwarning);
}
:: VariantClear (&varchunk);
:: Safearraydestroydata (PSA);
if (uisread<chunksize) break;
}//while (1)
F.close ();
Read a picture from a database:
CFile F;
F.open ("H:\\bbb.jpg", cfile::modewrite│cfile::modecreate);
Long lphotosize = m_precordset->fields->item["Photo"]->actualsize;
Long lisread=0;
It is also important to learn to use SAFEARRAY, because it is often used in ADO programming. Its main purpose is to be used for the transfer of array-type parameters in Automation. Because in a network environment, arrays cannot be passed directly, but they must be packaged into SAFEARRAY. Essentially SAFEARRAY is to add a descriptor to the usual array, stating its dimensions, length, bounds, element type, and so on. SAFEARRAY is not used alone, but is then packaged into variant variables, which are then passed out as parameters. If the value of the VT member in the variant contains vt_array│ ..., then it encapsulates a SAFEARRAY whose Parray member is the pointer to the SAFEARRAY. The types of elements in a SAFEARRAY can be any type that can be encapsulated by a variant, including the variant type itself.
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 into SAFEARRAY:
for (long index=0;index<uisread;index++)
{
if (FAILED (SafeArrayPutElement (psa,&index,&bval[index)))
:: MessageBox (NULL, "There is something wrong.") "," hint ", mb_ok│mb_iconwarning);
}
One by one, it's kind of troublesome.
(4). Encapsulated into variant:
VARCHUNK.VT = VT_ARRAY│VT_UI1;
Varchunk.parray = PSA;
This allows the varchunk to be passed as a parameter.
To read data in SAFEARRAY:
(1). read one by one with Safearraygetelement
BYTE Buf[lisread];
for (long index=0;index<lisread;index++)
{
:: Safearraygetelement (Varchunk.parray,&index,buf+index);
}
Went to the buffer buf.
Method Two:
Use Safearrayaccessdata to read and write directly to SAFEARRAY buffers:
VARCHUNK.VT = VT_ARRAY│VT_UI1;
Varchunk.parray = PSA;
This method can be read and write SafeArray, it directly manipulate the SAFEARRAY data buffer, faster than the safearraygetelement and safearrayputelement speed. Especially suitable for reading data. But when you're done, don't forget to call:: Safearrayunaccessdata (PSA), or you'll get an error.
13. Use Bookmarks (bookmark)
A bookmark uniquely identifies a record in a Recordset, which is used to quickly move the current record back to a record that has been visited, as well as to filter and so on. Provider automatically produces a bookmark for each record in the recordset, and we just need to use it. We cannot attempt to display, modify, or compare bookmarks. ADO uses the Bookmark property of a recordset to represent the bookmark for the current record.
Usage steps:
(1). Create a variant of variable type
_variant_t Varbookmark;
(2). Save the current record's bookmark value to the variable
The current value of the bookmark attribute of the recordset.
Varbookmark = rst->bookmark;
(3). Return to previous record
Set the saved bookmark value to the recordset's Bookmark properties:
Check for whether the bookmark set for a record
if (varbookmark.vt = = vt_empty)
printf ("No Bookmark set!\n");
Else
Rst->bookmark = Varbookmark;
When you are finished setting, the current record moves to the record that the bookmark points to.
14, set the filter conditions
The Filter property of the Recordset object represents the current filter condition. Its value can be a conditional expression (without a where keyword) that is concatenated with and or or, an array of bookmarks, or an FilterGroupEnum enumeration value provided by ADO. When you set a new value for the Filter property, the current record pointer for the recordset is automatically moved to the first record that satisfies the filter criteria. For example:
Rst->filter = _bstr_t ("name = ' Vicki ' and sex = ' female '");
You should be aware of the following issues when using conditional expressions:
(1), you can use parentheses to form complex expressions
For example:
Rst->filter = _bstr_t (name = ' Zhao Wei ' and sex = ' female ') or age<25 ');
However, Microsoft does not allow the use of or in parentheses, and then apply and with parentheses, for example:
Rst->filter = _bstr_t ("(name = ' Vicki ' OR sex = ' female ') and age<25");
Must be modified to:
Rst->filter = _bstr_t ("(name = ' Vicki ' and age<25 ') or (gender = ' female ' and age<25)");
(2), the comparison operator in an expression can be like
Like is compared to a string containing the wildcard *, the asterisk denotes several arbitrary characters.
The header and tail of a string can be accompanied with an asterisk *
Rst->filter = _bstr_t ("Name like ' * Zhao * '");
Or you can just tail with an asterisk:
Rst->filter = _bstr_t ("name like ' Zhao * '");
The type of the Filter property value is a variant, and if the filter condition is an array of bookmarks, the array is converted to SAFEARRAY and then encapsulated into a variant or _variant_t variable, which is then assigned to the Filter property.
15. Indexing and Sorting
(1), Index creation
When you use the Find method to locate a field as a keyword, you can create a temporary index within the recordset with that field as a keyword for speed. Just set the field's Optimize property 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: The Optimize property is a property provided by provider (called a dynamic property in ADO), and ADO does not have this attribute in itself.
(2), sorting
It is also simple to sort by setting the list of keywords to be sorted into the Sort property 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";
Keywords (that is, field names) are separated by commas, and if you want to sort by a keyword in descending order, add a space after the keyword, plus desc (for example). The ASC plus does not matter in ascending order. This operation uses the index to carry on, did not carry on the physical sorting, therefore the efficiency is high.
Note, however, that you must set the recordset's CursorLocation property to adUseClient before you open the Recordset, as shown in the previous example. The Sort property value can be modified at any time as needed.
16. Transaction Processing
Transaction processing in ADO is also very simple, by simply calling the three methods of the connection object in the appropriate place, these three methods are:
(1), called at the beginning of the transaction
Pcnn->begintrans ();
(2), called when the transaction ends and succeeds
Pcnn->committrans ();
(3), called when the transaction ends and fails
Pcnn->rollbacktrans ();
When using transactions, you should minimize the scope of the transaction, that is, to reduce the time interval between the start of the transaction and the end (commit or rollback) in order to improve system efficiency. You can also set the IsolationLevel property value of the connection object before calling the BeginTrans () method, as detailed in the technical information about ADO in MSDN.
Use ADO programming Frequently asked Questions
The following are a few of the issues that you encounter when programming for Ms SQL 7.0.
1. Possible reasons for connection failure
Enterprise Managemer, open the Properties dialog box for the server, and in the Security tab, there is an option authentication.
If this option is Windows NT only, the connection string used by your program must contain the Trusted_Connection parameter, and its value must be yes, such as:
"Provider=sqloledb; server=888; Trusted_connection=yes "
";D Atabase=master;uid=lad;";
If you do not press the above action, the connection of the program runtime must fail.
If the authentication option is SQL Server and Windows NT, the connection string used by your program may not contain trusted_connection parameters, such as:
"Provider=sqloledb; server=888;database=master;uid=lad;pwd=111; ";
Because ADO gives this parameter the default value is no, so you can omit. I think it's safer to take a default value.
2, change the current database method
Using the USE statement in Tansct-sql.
3, how to determine whether a database exists
(1) A view, called schemata, that opens the master database that lists all the database names on that server.
(2), the simpler way is to use the using statement, success exists; 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 does not exist! \ n ";
Str+=e.description ();
:: MessageBox (Null,str, "warning", mb_ok│mb_iconwarning);
}
4, to determine whether a table exists
(1), the same judge whether a table exists, can also be used to successfully open it to judge, very convenient, such as:
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.") "," hint ", mb_ok│mb_iconwarning);
}
(2), or else you can use a bit of trouble, that is, in each database on the MS-SQL server has a table named sysobjects, to see the contents of this table to know whether the specified table is in the database.
(3) Similarly, each database has a view that is named tables, and viewing the contents of this view knows whether the specified table is in that database.
5, type conversion issues
(1), type Variant_bool
Type Variant_bool is equivalent to the short type. The VARIANT_BOOL is equivalent to short. The IT ' s definition below:
Typdef Short Variant_bool
(2), type conversion of the _com_ptr_t class
_connectionptr can be automatically converted to idspatch* types because _connectionptr is actually an instance of the _com_ptr_t class that has this type of conversion function.
Similarly, _recordsetptr and _commandptr can also be converted.
(3), _bstr_t and _variant_t classes
When programming in ADO, these two classes of _bstr_t and _variant_t are useful, eliminating the hassle of many BSTR and variant type conversions.
6, open the recordset when the problem
When you open a recordset, when you call the recordset's Open method, it must not contain adasyncexecute in its last argument, otherwise you will not be able to read the data while reading the data because it is an asynchronous operation.
7, exception handling problems
Be sure to catch the exception with the try and catch statements for all statements that call ADO, otherwise the program will quit unexpectedly when an exception occurs.
8, the use of SAFEARRAY problems
In the beginner's use, I have encountered a vexed question, must note:
After defining the SAFEARRAY pointer, if you intend to use it more than once, in the middle you can call:: Safearraydestroydata release the data, but must not call:: Safearraydestroydescriptor, otherwise inevitable error, Not even if you call safearraycreate. For example:
SAFEARRAY *PSA;
......
When the data are no longer to be used:
:: Safearraydestroydata (PSA);
I analyze when defining the PSA pointer, a SAFEARRAY instance (that is, the SAFEARRAY descriptor) is also automatically established. But as soon as one call:: Safearraydestroydescriptor, the descriptor is destroyed.
So I think:: Safearraydestroydescriptor can not call at all, even if the call must also be called at the end.
9. Reuse Command Object problem
A Command object if you want to reuse multiple times (especially commands with parameters), you should set its prepared property to true before it executes for the first time. This slows down the first execution, but it can make all subsequent executions faster.
10, bound string field problem
If the field to be bound is a string type, the corresponding character array must be 2 more than the length of the field (for example, m_szau_fname[22], whose bound field au_fname is 20), and the binding will fail.
11, the use of AppendChunk problems
When you have just added a new record to a recordset by using the AddNew method, you cannot write data to a Long data field (the image type) first, and you must write the data to another field before you can invoke AppendChunk to write the field, or an error occurs. In other words, AppendChunk cannot be immediately after AddNew. In addition, when you write to another field, you must immediately call AppendChunk, and you cannot call the recordset's Update method before calling AppendChunk, otherwise there will be an error when calling AppendChunk. In other words, it is necessary to AppendChunk in front, update in the post. Therefore, this time can not use the AddNew with parameters, because the AddNew with parameters will automatically call the update of the Recordset, so AppendChunk ran to the back of the update, there is only a mistake! Therefore, you should use AddNew with no parameters.
I speculate that this may be a problem with MS SQL 7.0, which does not exist in MS SQL 2000, but AppendChunk is still not available after update.
Iv. Summary
In general, the connection and command's execute is used to execute commands that do not produce a recordset, and the recordset's Open is used to produce a recordset, and certainly not absolute. The special command is primarily used to perform parameterized commands that can be executed directly by the command object or by passing the command object to the recordset's open.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
