Call the stored procedure with parameters and return the result set-Oracle

call the stored procedure for data insertion:

// Call the stored procedure to insert a record bool cdbtestappdlg: insertrecord () {cadoparameter param1, param2, param3; cadocommand comm; If (conntodb () {comm. setconnection (m_pconnection); param1.setsize (20); param1.setname ("c_name"); param1.setdirection (adparaminput); param1.settype (advarchar); // varchar2param1. setvalue (cstring) "hello113"); // type conversion is required. The default parameter type is bool. Therefore, comm is incorrect during conversion. append (param1.getparameter (); param2.setname ("c_age"); par Am2.setdirection (adparaminput); param2.settype (adinteger); // integerparam2.setvalue (21); Comm. append (param2.getparameter (); param3.setname ("c_exetime"); param3.setdirection (adparaminput); param3.settype (advarchar); // varchar2param3. setvalue (cstring) "2010-1-10"); Comm. append (param3.getparameter (); Comm. setcommandtext ("proc_insert"); Comm. setcommandtype (adcmdstoredproc); try {comm. execute (); MessageBox ("Pro Cedure execute success! "," Executed successfully ", mb_ OK | mb_iconinformation); Return true;} catch (cexception * E) {char errormessage [256]; e-> geterrormessage (errormessage, 255 ); messageBox (errormessage) ;}} return false ;}

The database connection function is:

 
Bool cdbtestappdlg: conntodb () {bool nresult = true; If (m_pconnection = NULL) {m_pconnection = new cadoconnection; If (! M_pconnection-> createinstance () {MessageBox ("failed to create database instance"); Delete m_pconnection; m_pconnection = NULL; return false ;}}if (m_pconnection-> isopen ()) m_pconnection-> close (); m_pconnection-> setconnecttimeout (2); m_precordset.setadoconnection (m_pconnection); If (! M_pconnection-> connection (m_sprovider) {// MessageBox ("failed to connect to the Business Database"); nresult = false ;} else {// MessageBox ("successful connection to the Business Database");} return nresult ;}
 

To return a result set, you must first create a package and then a package body,CodeAs follows:

Create or replace package pkg_getresultas type myresult is ref cursor; -- Define the return value type procedure getresult (age Number, presult out myresult); -- declare presult as the output result set variable end pkg_getresult; /create or replace package body pkg_getresultas procedure getresult (age Number, presult out myresult) is sqlstr varchar2 (200); begin If age = 0 then open presult for select c_id, c_name, c_age, c_intime, c_salary, c_exetime from t_test; else sqlstr: = 'select c_id, c_name, c_age, c_intime, c_salary, c_exetime from t_test where c_age =: w_age '; open presult for sqlstr using age; end if; end getresult; end pkg_getresult;
 

Call the stored procedure in this package in VC. Call method: package name. Stored Procedure name (parameter 1, parameter 2 ,...):

Bool cdbtestappdlg: getresult (cstring procname, int age) {If (conntodb () {try {cstring SQL; SQL. format ("{call % s (% d)}", procname, age); // call the stored procedure in the package: packagename. procedurename (parameter 1, parameter 2 ...) trace (SQL + "\ n"); m_precordset.open (SQL, ad1_text, adopenstatic, adlockreadonly); trace ("procedure execute success! "); While (! M_precordset.iseof () {cstring name, age; m_precordset.getcollect ("c_name", name); m_precordset.getcollect ("c_age", age); MessageBox ("name =" + name + ", age = "+ age); // trace (" name = "+ name +", age = "+ age); m_precordset.movenext ();} return true ;} catch (cexception * E) {char errormessage [256]; e-> geterrormessage (errormessage, 255); MessageBox (errormessage); Return false ;}} return false ;}
 

You can use the open () method of crecordset to return the result set and traverse it. Some custom functions are used here, which are not provided because they are relatively simple. The call here is ad1_text, not ad1_storedproc