Stored procedure. IN-OUT parameters (VB, C++)

'SAMPLE OF WORK WITH EXECUTED STORED PROCEDURES
'
'CREATE PROCEDURE SP_EXEC_1 (ARG_INT   INTEGER,
'                            ARG_DATE  DATE,
'                            ARG_STR   VARCHAR(32))
'RETURNS (OUT_INT_1 INTEGER,
'         OUT_INT_2 INTEGER,
'         OUT_DATE  DATE,
'         OUT_STR   VARCHAR(32))
'AS
'BEGIN
' OUT_INT_1=-ARG_INT;
' OUT_INT_2=2*ARG_INT;
'
' OUT_DATE =OUT_DATE+1;
'
' OUT_STR  =UPPER(ARG_STR);
'END


'check SP result
Sub sample_8_1_check_out_parametrs(params As ADODB.Parameters)
 If (params("out_int_1") <> -params("int")) Then Err.Raise -1, , "incorrect OUT_INT_1"
 If (params("out_int_2") <> 2 * params("int")) Then Err.Raise -1, , "incorrect OUT_INT_2"
 If (params("out_date") <> params("date") + 1) Then Err.Raise -1, , "incorrect OUT_DATE"
 If (params("out_str") <> UCase(params("str"))) Then Err.Raise -1, , "incorrect OUT_STR"
End Sub

Sub sample_8_1()
 Dim cn As New ADODB.Connection
 cn.Provider = "LCPI.IBProvider"
 cn.Open "data source=main:e:\database\ibp_test.gdb;ctype=win1251", "gamer", "vermut"

 cn.BeginTrans

 Dim cmd As New ADODB.Command
 cmd.ActiveConnection = cn

 cmd.CommandText = "execute procedure sp_exec_1(:int,:date,:str)"

 'automating defining of parameters ----------------------------------------------
 cmd.Parameters.Refresh

 cmd("int") = 2
 cmd("date") = Date
 cmd("str") = "lower chars"

 cmd.Execute

 'check SP result
 Call sample_8_1_check_out_parametrs(cmd.Parameters)

 'user defining of UNNAMED parameters --------------------------------------------
 cmd.CommandText = "execute procedure sp_exec_1(?,?,?)"

 While cmd.Parameters.Count
  cmd.Parameters.Delete (0)
 Wend

 'IN-parameters
 cmd.Parameters.Append cmd.CreateParameter("int", adInteger, adParamInput, , 2)
 cmd.Parameters.Append cmd.CreateParameter("date", adDate, adParamInput, , Date)
 cmd.Parameters.Append cmd.CreateParameter("str", adBSTR, adParamInput, , "small chars")

 'OUT-parameters
 cmd.Parameters.Append cmd.CreateParameter("out_int_1", adInteger, adParamOutput)
 cmd.Parameters.Append cmd.CreateParameter("out_int_2", adInteger, adParamOutput)
 cmd.Parameters.Append cmd.CreateParameter("out_date", adDate, adParamOutput)
 cmd.Parameters.Append cmd.CreateParameter("out_str", adBSTR, adParamOutput)

 cmd.Execute

 'check SP result
 Call sample_8_1_check_out_parametrs(cmd.Parameters)

 'user defining of NAMED parameters (support only in ADO 2.6 and next) ------------
 cmd.CommandText = "execute procedure sp_exec_1(:int,:date,:str)"

 While cmd.Parameters.Count
  cmd.Parameters.Delete (0)
 Wend

 'IN-parameters
 cmd.Parameters.Append cmd.CreateParameter("int", adInteger, adParamInput, , 3)
 cmd.Parameters.Append cmd.CreateParameter("date", adDate, adParamInput, , Date - 10)
 cmd.Parameters.Append cmd.CreateParameter("str", adBSTR, adParamInput, , "small chars 2")

 'OUT-parameters
 cmd.Parameters.Append cmd.CreateParameter("out_int_1", adInteger, adParamOutput)
 cmd.Parameters.Append cmd.CreateParameter("out_int_2", adInteger, adParamOutput)
 cmd.Parameters.Append cmd.CreateParameter("out_date", adDate, adParamOutput)
 cmd.Parameters.Append cmd.CreateParameter("out_str", adBSTR, adParamOutput)

 cmd.NamedParameters = True 'support only ADO 2.6 and next

 cmd.Execute

 'check SP result
 Call sample_8_1_check_out_parametrs(cmd.Parameters)

 'Selective reception OUT-parameters - only 'out_int_2' and 'out_str'
 cmd.Parameters.Delete ("out_int_1")
 cmd.Parameters.Delete ("out_date")

 'init out-parameters
 cmd("out_int_2") = Empty
 cmd("out_str") = Empty

 cmd.Execute

 'print of 2 and 4 OUT-parameters
 Debug.Print "out_int_2=" & CStr(cmd("out_int_2"))
 Debug.Print "out_str=" & cmd("out_str")

 cn.CommitTrans
End Sub 'sample_8_1

#define _TRACE(obj,func)            \
{                                   \
 cout<<#obj<<"."<<#func<<endl;      \
                                    \
 _THROW_OLEDB_FAILED(obj,func)      \
}

////////////////////////////////////////////////////////////////////////////////
//class TSampleCode8_1

void TSampleCode8_1::test_value(t_db_base_row& params,LPCSTR param_name,LPCSTR value)
{
 using structure::str_formatter;

 const t_db_base_row::size_type x=params.get_index(param_name);

 if(x==t_db_base_row::npos)
 {
  throw runtime_error(str_formatter("param \"%1\" not found")<<param_name);
 }

 if(params[x].as_string!=value)
 {
  str_formatter fmsg("incorrect value of %1\n"
                     "need [%2]. get from SP [%3].");

  throw runtime_error(fmsg<<param_name<<value<<params[x].as_string);
 }

 cout<<param_name<<":"<<params[x].as_string<<endl;
}//test_value

//------------------------------------------------------------------------
void TSampleCode8_1::sample_8_1_check_out_parametrs(t_db_base_row& params)
{
 cout<<" #sample_8_1_check_out_parametrs"<<endl;

 test_value(params,"out_int_1","-2");
 test_value(params,"out_int_2","4");
 test_value(params,"out_date","02.11.2004");
 test_value(params,"out_str","LOWER CHARS");
}//sample_8_1_check_out_parametrs

//------------------------------------------------------------------------
void TSampleCode8_1::execute()
{
 using namespace oledb_lib;

 cout<<"Sample #8.1"<<endl;

 try
 {
  t_db_data_source data_source;
  t_db_session     session;
  t_db_command     cmd;

  _TRACE(data_source,attach("file name=ibp_test.ibp"))
  _TRACE(session,create(data_source))

  _TRACE(session,start_transaction())

  t_db_auto_commit const __auto_commit(session);

  _TRACE(cmd,create(session))

  _TRACE(cmd,prepare("execute procedure sp_exec_1(:int,:date,:str)",NULL))

  //Automating defining of parameters ------------------------------------
  cout<<endl
      <<"test #1 [provider return param descriptions]"<<endl;

  t_db_row params;

  _TRACE(cmd,describe_params(params))

  params["int"]  =2;
  params["date"] ="01.11.2004";
  params["str"]  ="lower chars";

  _TRACE(cmd,execute(&params,false))

  sample_8_1_check_out_parametrs(params);

  //user defining of parameters ------------------------------------------
  cout<<endl
      <<"test #2 [user build param descriptions]"<<endl;

  params.init();

  params.create_param("int")  =2;
  params.create_param("date") ="01.11.2004";
  params.create_param("str")  ="lower chars";

  params.create_param("out_int_1",adInteger ,DBPARAMFLAGS_ISOUTPUT);
  params.create_param("out_int_2",adInteger ,DBPARAMFLAGS_ISOUTPUT);
  params.create_param("out_date" ,adDate    ,DBPARAMFLAGS_ISOUTPUT);
  params.create_param("out_str"  ,adVarChar ,DBPARAMFLAGS_ISOUTPUT);

  _TRACE(cmd,execute(&params,false))

  sample_8_1_check_out_parametrs(params);

  //select 'out_int_2' and 'out_str' parameters --------------------------
  cout<<endl
      <<"test #2 [select 'out_int_2' and 'out_str' parameters]"<<endl;

  params.init();

  //use alternative method of assign parameters descriptions
  params.set_param(0,"int")  =123;
  params.set_param(1,"date") ="03.11.2004";
  params.set_param(2,"str")  ="qwerty";

  params.set_param(3,"out_int_2",adInteger,DBPARAMFLAGS_ISOUTPUT);
  params.set_param(4,"out_str"  ,adBSTR   ,DBPARAMFLAGS_ISOUTPUT);

  params.count=5;

  _TRACE(cmd,execute(&params,false))

  cout<<"out_int_2:"<<params["out_int_2"].as_string<<endl;
  cout<<"out_str:"<<params["out_str"].as_string<<endl;
 }
 catch(const exception& exc)
 {
  cout<<endl;
  cout<<"error:"<<exc.what()<<endl;
 }
}//execute