Database programming (medium) 4 in Visual C . Execute the SQL command and acquire the result record set
In order to obtain the result record set, we define a pointer to the Recordset object: _RecordSetPtr m_precordset; and create an instance of the RecordSet object: m_precordset.createInstance ("adodb.recordset"); SQL command execution can take a variety of forms, below An explanation.
(1) Execute SQL commands using the Execute method of the Connection object
The prototype of the Execute method is as follows:
_RecordSetPtr Connection15 :: EXECUTE (_BSTR_T Commandtext, Variant * Recordsaffected, Long Options)
Where the CommandText is a command string, usually the SQL command. Parameter recordsaffected is the number of rows affected after the operation is completed. The parameter Options represents the type of content in CommandText. Options can take one of the values: AdcmdText: Indications CommandText is a text command adcmdtable: indicate that CommandText is a table name AdcmdProc: indicating that CommandText is a table name AdcmdProc: Store procedure adcmdunknown: unknown
EXECUTE is executed, return a pointer to the record set, below we give specific code and instructions. _variant_t RecordsAffected; /// execute SQL commands: CREATE TABLE create table users, users contains four fields: plastic ID, string username, plastic old, date-type birthday m_pConnection-> Execute ( "CREATE TABLE users (ID INTEGER, username TEXT , Old Integer, Birthday DateTime) ", & Recordsaffected, AdcmdText);
/// Add record M_PConnection-> Execute ("INSERT INTO USERS (ID, Username, Old, Birthday) VALUES (1, '' '' '1970/1 / 1 '' '') ", & Recordsaffected, AdcmdText);
/// add a value of all the records of the OLD field ("Update Users Set Old = OLD 1", & Recordsaffected, AdcmdText);
/// execute SQL command to get statistical record set comprising a number of records m_pRecordset = m_pConnection-> Execute ( "SELECT COUNT (*) FROM users", & RecordsAffected, adCmdText); _variant_t vIndex = (long) 0; _variant_t vCount = m_pRecordset- > GetCollect (vindex); /// get the value of the first field to put in the vcount variable 2 sentences can be written - _variant_t vcount = m_precordset-> getCollect ((_ variant_t) ((long) 0)); m_precordset-> close ); /// Close the record set cstring message; "a total of% D record", vcount.lval); AFXMessageBox (Message); // / display The current record number (2) Use the Command object to perform SQL command
_CommandPtr m_pCommand; m_pCommand.CreateInstance ( "ADODB.Command"); _ variant_t vNULL; vNULL.vt = VT_ERROR; vNULL.scode = DISP_E_PARAMNOTFOUND; /// no parameters defined m_pCommand-> ActiveConnection = m_pConnection; /// a critical , The established connection assignments to it m_pcommand-> commandtext = "select * from users"; /// command string m_precordset = m_pcommand-> execute (& vnull, & vnull, adcmdtext); // / execution command, acquisition record set
In this code, we just use the Command object to perform the SELECT query statement, and the Command object can really reflect its role in the call to the stored procedure. Next time we will introduce it.
(3) Directly use the RecordSet object to obtain record sets
Example -
Void cgmsadlg :: OndbSelect () {// Todo: add your control notification handler code he_RecordSetPtr rs1; // Define RecordSet object_BSTR_T Connect ("DSN = GMS; UID = SA; PWD =;"); / / Define Connection Characters String _BSTR_T SOURCE ("SELECT Count (*) from Buaa.mdb010"); // To execute SQL statement :: Coinitialize (null); // Initialize RS1 object hresul hr = rs1.createInstance (__UUIDOF (Recordset); / / Omitting the return value HR RS1-> Open (Source, Connect, Adopenforwardonly, AdlockRectonly, -1); _VARIANT_T TEMP = RS1-> getCollect (_variant_t (long) 0); cstring straTemp = (char *) _BSTR_T) TEMP; MessageBox ("OK!" strTemp);
E.g. m_pRecordset-> Open ( "SELECT * FROM users", _variant_t ((IDispatch *) m_pConnection, true), adOpenStatic, adLockOptimistic, adCmdText); Open prototype method is such that: HRESULT Recordset15 :: Open (const _variant_t & Source, Const _variant_t & ActiveConnection, Enum Cursortypeenum Cursortype, Enum LockTypeenum LockType, Long Options
among them:
1Source is a data query string 2ActiveConnection is a established connection (we need to use the Connection object pointer to construct a _variant_t object) 3CURSORTYPE cursor type, which can be one of the following values, please see this enumeration structure:
Enum cursortypeenum {adopenunSpecified = -1, /// does not specify adopenforwardonly = 0, /// front rolled static cursor. This cursor can only browse the record in front, such as scrolling forward with MoveNext, this way can improve the browsing speed. But such as Bookmark, RecordCount, AbsolutePosition, AbsolutePosition, AbsolutePage can't use AdopenKeyset = 1, /////> Records that use this cursor can not see new, delete operations, but for updating the original records, you are visible to you of.
AdoPENDYNAMIC = 2, // / Dynamic Cursor. The operation of all databases will be immediately reacted on each user recordset. AdopenStatic = 3 /// Static cursor. It produces a static backup for your recordset, but the new, delete, and update operations of other users are invisible to your record set. }; 4LockType lock type, it can be one of the following values, please see the following enumeration structure:
Enum LockTypeenum {AdlockunSpecified = -1, /// Non-specified AdlockReadonly = 1, /// read-only record set AdlockPESSIMISTIC = 2, pessimistic locking method. Data locks all other actions at the time of update, this is the safest lock mechanism AdlockOptimistic = 3, optimistic locking mode. Lock the record only when you call the UPDATE method. You can still do data update, insert, delete, etc. before this, AdlockBatchOptimistic = 4, optimistic batch update. When editing, the record does not lock, change, insert, and delete it is done in batch mode. }
5Options can take one of the values: AdcmdText: Indicates that CommandText is the text command adcmdtable: indicates that CommandText is a table name AdcmdProc: indicating that CommandText is a stored procedure AdcmDunkNown: unknown
