General methods for calling stored procedures by VC ()

Here we will discuss the ADO call stored procedure. According to the msdn example, the following call methods are generally used (Excerpted from msdn ):

Hresult hR = s_ OK;

// Define ADO object pointers.
// Initialize pointers on define.
// These are in the ADODB: namespace.
_ Recordsetptr prstbyroyalty = NULL;
_ Recordsetptr prstauthors = NULL;
_ Commandptr pcmdbyroyalty = NULL;
_ Parameterptr pprmbyroyalty = NULL;
_ Connectionptr pconnection = NULL;

_ Bstr_t strcnn ("provider = sqloledb; Data Source = SRV ;"
"Initial catalog = pubs; user id = sa; Password = ;");

_ Bstr_t strmessage, strauthorid;
Int introyalty;
Variant vtroyalty;

Try
{
// Open a connection.
Testhr (pconnection. createinstance (_ uuidof (connection )));
HR = pconnection-> open (strcnn, "", "", null );
Pconnection-> cursorlocation = aduseclient;

// Open command object with one parameter
Testhr (pcmdbyroyalty. createinstance (_ uuidof (command )));
Pcmdbyroyalty-> commandtext = "byroyalty ";
Pcmdbyroyalty-> commandtype = adw.storedproc;

// Define integer/variant.
Vtroyalty. Vt = vt_i2;
Vtroyalty. ival = introyalty;
Pprmbyroyalty = pcmdbyroyalty-> createparameter (
"Percentage", adinteger, adparaminput, sizeof (INT), vtroyalty );
Pcmdbyroyalty-> parameters-> append (pprmbyroyalty );
Pprmbyroyalty-> value = vtroyalty;

// Create recordset by executing the command
Pcmdbyroyalty-> activeconnection = pconnection;
Prstbyroyalty = pcmdbyroyalty-> execute (
Null, null, adcmdstoredproc );

// Process the execution result, omitting...

Prstbyroyalty-> close ();
Prstauthors-> close ();
Pconnection-> close ();
}

Catch (_ com_error & E)
{
// Handle exceptions...
}
}

You must specify that the commandtype for SQL statement execution is ad‑storedproc. If the stored procedure has parameters, createparameter must generate the parameters. Therefore, calling the Stored Procedure becomes a very troublesome task, different parameters must be generated for different stored procedures. One or both of them are acceptable. If the business logic of the Project is complex and requires a large number of stored procedures, and the project requirements or functions may change or expand frequently, that's a nightmare. Is there a general method? Of course, only common queries are required.

_ Connectionptr pconnection = NULL;
M_conn.createinstance (_ uuidof (connection ));
M_conn-> connectionstring = "provider = sqloledb; Data Source = SRV;" Initial catalog = pubs; user id = sa; Password = ;";
M_conn-> open ("", null );

_ Variant_t m_param;
_ Bstr_t m_bstr;
Int Index = 0, nfieldcount;
_ Recordsetptr m_rs;
M_rs.createinstance (_ uuidof (recordset ));
M_rs-> open ("select * from titles", (idispatch *) (m_conn-> m_conn), adopenstatic, adlockreadonly, ad1_text );
Nfieldcount = m_rs-> fields-> count;
While (! M_rs-> endoffile)
{
For (Index = 0; index <nfieldcount; index ++)
{
M_param.clear ();
M_param = (short) index;
M_param = m_rs-> fields-> getitem (m_param );
If (m_param.vt! = Vt_null)
M_bstr = m_param;
Else
M_bstr = "";
Printf ("% s/n", (char *) m_bstr );
}
M_rs-> movenext ();
}
M_rs-> close ();
M_conn-> close ();

The above section is the code for executing the query SQL statement. For simplicity, all protection and judgment codes are removed. For Ms sqlserver, if it is a stored procedure for executing the returned result set, for example, you only need to replace "select * from titles" in the above Code with "byroyalty 100", that is, "process name parameter 1, parameter 2 ,... "format.

In this way, you do not need to generate parameters. You only need to change the SQL statement to call the MS sqlserver stored procedure. However, for the stored procedure of the returned parameters, this code will not work, but it can still be solved through the SQL statement:
"Declare @ Q int exec checkpwd 'user', 'pwd', @ ret = @ Q output select @ Q"
Checkpwd is a stored procedure with two input parameters, user name and password. The third parameter is the output parameter @ ret, which returns the user verification result. By executing the preceding SQL statement and returning the output parameters as the result set, you can adapt to the preceding VC code. The same is true for the stored procedure of the returned values. Execute "declare @ Q int, @ return int exec @ return = checkpwd 'user', 'pwd', @ ret = @ Q output select @ Q, @ return.

Here we will talk about the SQL Server Stored Procedure Call. This code can also call the Oracle stored procedure, but you need to make some adjustments to the SQL statement, which will be available later.