Publish date: 2003-01-05

Work with command (VB, C++)

Sub print_rs(rs As ADODB.Recordset)
 While Not rs.EOF And Not rs.EOF
  Debug.Print RowToStr(rs)
  rs.MoveNext
 Wend
End Sub

Sub sample3()
 Dim cn As New ADODB.Connection
 cn.Open ("file name=e:\database\employee.ibp")

 cn.BeginTrans

 Dim cmd As New ADODB.Command

 cmd.ActiveConnection = cn

 Dim sql_with_unnamed As String
 Dim sql_with_named As String

 sql_with_unnamed = "select emp_proj.proj_id,emp.*" & Chr(13) & _
                    "from employee emp join employee_project emp_proj" & Chr(13) & _
                                      "on emp.emp_no=emp_proj.emp_no" & Chr(13) & _
                    "where emp_proj.proj_id=?"


 sql_with_named = "select emp_proj.proj_id,emp.*" & Chr(13) & _
                  "from employee emp join employee_project emp_proj" & Chr(13) & _
                                    "on emp.emp_no=emp_proj.emp_no" & Chr(13) & _
                  "where emp_proj.proj_id=:proj_id"

 Debug.Print "explicit parameters definition"
 cmd.CommandText = sql_with_unnamed
 cmd.Parameters.Append cmd.CreateParameter(, adBSTR)
 cmd(0) = "VBASE"

 Call print_rs(cmd.Execute)

 Debug.Print "implicit parameters definition"
 Set cmd = Nothing
 cmd.ActiveConnection = cn
 cmd.CommandText = sql_with_unnamed
 cmd(0) = "VBASE"

 Call print_rs(cmd.Execute)

 Debug.Print "Using named parameters"
 Debug.Print "Only implicit parameter definition"
 cmd.CommandText = sql_with_named
 cmd.Parameters.Refresh
 cmd("proj_id") = "VBASE"

 Call print_rs(cmd.Execute)

End Sub 'sample3

void TSampleCode3::execute()
{
 cout<<endl<<"Sample #3"<<endl;

 try
 {
  t_db_data_source source;
  t_db_session     session;

  //attach data source (auto commit mode)
  _THROW_OLEDB_FAILED(source,attach("file name=employee.ibp"))
  _THROW_OLEDB_FAILED(session,create(source))

  const string sql_with_unnamed=
   "select emp_proj.proj_id,emp.*\n"
   "from employee emp join employee_project emp_proj\n"
                      "on emp.emp_no=emp_proj.emp_no\n"
   "where emp_proj.proj_id=?";

  const string sql_with_named=
   "select emp_proj.proj_id,emp.*\n"
   "from employee emp join employee_project emp_proj\n"
                      "on emp.emp_no=emp_proj.emp_no\n"
   "where emp_proj.proj_id=:proj_id";

  t_db_command cmd;
  t_db_row     param;

  _THROW_OLEDB_FAILED(cmd,create(session))

  cout<<"unnamed parameter and explicit user definition of it type"<<endl;

  _THROW_OLEDB_FAILED(cmd,prepare(sql_with_unnamed,NULL))

  //define command parameter
  param[0]="VBASE"; //proj_id
  param.set_count(1);

  _THROW_OLEDB_FAILED(cmd,execute(&param))

  print_records(cmd);

  cout<<endl<<"named parameter and use describe_params method"<<endl;

  _THROW_OLEDB_FAILED(cmd,prepare(sql_with_named,NULL))

  //retrive parameter information
  _THROW_OLEDB_FAILED(cmd,describe_params(param))

  param["proj_id"]="VBASE";

  _THROW_OLEDB_FAILED(cmd,execute(&param))

  print_records(cmd);
 }
 catch(exception& exc)
 {
  cout<<"error: "<<exc.what()<<endl;
 }
}//SampleFunc3