Хранимые процедуры. ODBC-вызов. IN-OUT параметры (WSF:VBS, C++)

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' stored procedute text
' 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 =arg_date+1;
'
'  out_str  =upper(arg_str);
' end
'
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
call sample_odbc_exec_sp()

'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sub sample_odbc_exec_sp()
 p_echo "****************************************************************"
 p_echo "* execute odbc-query '{?=call sp(...)}' as 'execute procedure' *"
 p_echo "****************************************************************"

 set g_cn = wscript.createobject("adodb.connection")

 call g_cn.open("file name=ibp_test.ibp")

 call print_connection_info(g_cn)

 set cmd=wscript.createobject("adodb.command")

 cmd.activeconnection=g_cn

 cmd.namedparameters=true

 'enable support odbc-queries
 cmd.properties("support_odbc_query")=true

 'translate {?=call sp} to "execute procedure sp"
 cmd.properties("odbc_call_sp")=1

 p_echo "----------------------------------------------------------"
 call cmd_set_text(cmd,"{:ret=call sp_exec_1(:in1,:in2,:in3,:out1,:out2,:out3,:out4)}")

 cmd.parameters.refresh

 cmd("in1")=123
 cmd("in2")="01.01.2003"
 cmd("in3")="qwerty"

 call cmd_execute(cmd)

 p_echo "prepare stmt:"&cmd.properties("prepare stmt")

 'print result
 call print_cmd_params(cmd)

 p_echo "----------------------------------------------------------"

 call cmd_set_text(cmd,"{call sp_exec_1(:in1,default,:in3," & _
                                        ":out1," & _
                                        "/*ignore*/," & _
                                        ":out3/*ret as null*/," & _
                                        ":out4)}")

 cmd.parameters.refresh

 cmd("in1")=11
 cmd("in3")="ibprovider"

 call cmd_execute(cmd)

 p_echo "prepare stmt:"&cmd.properties("prepare stmt")

 'print result
 call print_cmd_params(cmd)

end sub 'sample_odbc_exec_sp

Вывод.

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

////////////////////////////////////////////////////////////////////////////////
//class TSampleCode8_4

void TSampleCode8_4::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())

  const t_db_auto_commit __auto_commit(session);

  _TRACE(cmd,create(session))

  t_db_obj_props cmd_props(false);

  _TRACE(cmd_props,attach_command(cmd.command_obj()))

  _TRACE(cmd_props,set("support_odbc_query",true))

  _TRACE(cmd_props,set("odbc_call_sp",1))

  _TRACE(cmd,prepare("{:ret=call sp_exec_1(:in1,:in2,:in3,:out1,:out2,:out3,:out4)}",NULL))

  t_db_row params;

  _TRACE(cmd,describe_params(params))

  params["in1"]=123;
  params["in2"]="01.01.2003";
  params["in3"]="qwerty";

  _TRACE(cmd,execute(&params,false,NULL,/*reset_param_info*/false))

  cout<<"ret="<<params["ret"]<<endl;
  cout<<"out1="<<params["out1"]<<endl;
  cout<<"out2="<<params["out2"]<<endl;
  cout<<"out3="<<params["out3"]<<endl;
  cout<<"out4="<<params["out4"]<<endl;

  _TRACE(cmd_props,refresh())

  if(const t_db_property* const p=cmd_props["prepare stmt"])
   cout<<"prepare stmt=["<<p->value().as_str()<<"]"<<endl;

  cout<<endl;

  _TRACE(cmd,prepare("{call sp_exec_1(:in1,default,:in3,"
                                     ":out1,"
                                     "/*ignore*/,"
                                     ":out3/*ret as null*/,"
                                     ":out4)}",NULL))

  _TRACE(cmd,describe_params(params))

  params["in1"]=11;
  params["in3"]="ibprovider";

  _TRACE(cmd,execute(&params,false,NULL,/*reset_param_info*/false))

  cout<<"out1="<<params["out1"]<<endl;
  cout<<"out3="<<params["out3"]<<endl;
  cout<<"out4="<<params["out4"]<<endl;

  _TRACE(cmd_props,refresh())

  if(const t_db_property* const p=cmd_props["prepare stmt"])
   cout<<"prepare stmt=["<<p->value().as_str()<<"]"<<endl;
 }
 catch(const exception& exc)
 {
  cout<<"error:"<<exc.what()<<endl;
 }
}//execute