Updatable recorset. Controlled mode (VB, C++)

Sub sample7_3()
 Dim cn As New ADODB.Connection
 cn.Provider = "LCPI.IBProvider.2"
 Call cn.Open("data source=main:e:\database\employee.gdb", "gamer", "vermut")

 'begin trans (commit retaining mode)
 cn.Attributes = adXactCommitRetaining
 cn.BeginTrans

 Dim job_rs As New ADODB.Recordset
 job_rs.ActiveConnection = cn

 'append only
 job_rs.Properties("Append-Only Rowset") = True

 job_rs.Properties("insert_sql") = _
  "insert into job " + _
  "(job_code,job_grade,job_country," + _
   "job_title,min_salary,max_salary,job_requirement,language_req) " + _
   "values(:new.job_code,:new.job_grade,:new.job_country," + _
          ":new.job_title," + _
          ":new.min_salary,:new.max_salary," + _
          ":new.job_requirement,:new.language_req)"

 job_rs.Properties("update_sql") = _
  "update job set" + Chr(13) + _
  "job_code        =:new.job_code," + _
  "job_grade       =:new.job_grade," + _
  "job_country     =:new.job_country," + _
  "job_title       =:new.job_title," + _
  "min_salary      =:new.min_salary," + _
  "max_salary      =:new.max_salary," + _
  "job_requirement =:new.job_requirement," + _
  "language_req    =:new.language_req" + Chr(13) + _
  "where job_code =:old.job_code and " + _
        "job_grade=:old.job_grade and " + _
        "job_country=:old.job_country"

 job_rs.Properties("delete_sql") = _
  "delete from job " + _
  "where job_code    =:old.job_code and " + _
        "job_grade   =:old.job_grade and " + _
        "job_country =:old.job_country"

 Call job_rs.Open("job", , adOpenKeyset, adLockOptimistic, adCmdTable)

 'insert new row ------------------------
 job_rs.AddNew
 job_rs!job_code = "user"      ' PK
 job_rs!job_grade = "3"        ' PK
 job_rs!job_country = "Canada" ' PK [FK to COUNTRY]
 job_rs!job_title = "Tester of IBProvider"
 job_rs!min_salary = 1
 job_rs!max_salary = 150
 job_rs!job_requirement = "ADO, OLEDB, Builder C++, Visual C++, VB"

 'make array with requirement to languages
 Dim language_req(1 To 4) As String
 language_req(1) = "Russian"
 job_rs!language_req = language_req

 job_rs.Update

 'commit retaining
 cn.CommitTrans

 'update --------------------------------
 job_rs!job_requirement = (job_rs!job_requirement) & ", InterBase SQL Server"
 language_req(2) = "English"
 job_rs!language_req = language_req

 job_rs.Update

 'commit retaining
 cn.CommitTrans

 'delete record -------------------------
 job_rs.Delete

 'commit retaining
 cn.CommitTrans
End Sub 'sample7_3

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

////////////////////////////////////////////////////////////////////////////////
//class TSampleCode7_3

void TSampleCode7_3::execute()
{
 cout<<"Sample #7.3"<<endl;

 try
 {
  LPCSTR table_name            = "job"              ; //PK
  LPCSTR col_job_code          = "job_code"         ; //PK
  LPCSTR col_job_grade         = "job_grade"        ; //PK
  LPCSTR col_job_country       = "job_country"      ;
  LPCSTR col_job_title         = "job_title"        ;
  LPCSTR col_min_salary        = "min_salary"       ;
  LPCSTR col_max_salary        = "max_salary"       ;
  LPCSTR col_job_requirement   = "job_requirement"  ;
  LPCSTR col_language_req      = "language_req"     ;

  //query templates
  LPCSTR f_insert_sql=
   "insert into %1 (%3,%4,%5,%6,%7,%8,%9,%10)\n"
   "values(%2.%3,%2.%4,%2.%5,%2.%6,%2.%7,%2.%8,%2.%9,%2.%10)";

  LPCSTR f_update_sql=
   "update %1\n"
   "set %4=%2.%4,%5=%2.%5,%6=%2.%6,%7=%2.%7,%8=%2.%8,%9=%2.%9,%10=%2.%10\n"
   "where %4=%3.%4 and %5=%3.%5 and %6=%3.%6";

  LPCSTR f_delete_sql=
   "delete from %1 where %3=%2.%3 and %4=%2.%4 and %5=%2.%5";

  //--------------------------------------------------------
  t_db_data_source data_source;
  t_db_session     session;
  t_db_open_rowset job_rs;

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

  _TRACE(session,start_transaction())

  _TRACE(job_rs,create(session))

  _TRACE(job_rs.m_props,set(t_db_prop_id::rowset_append_only,true))

  //BUILD SQL statements
  str_formatter sql_builder;

  //INSERT -------------------------
  sql_builder(f_insert_sql)
   <<table_name
   <<":new"
   <<col_job_code
   <<col_job_grade
   <<col_job_country
   <<col_job_title
   <<col_min_salary
   <<col_max_salary
   <<col_job_requirement
   <<col_language_req;

  cout<<"INSERT SQL:\n"<<sql_builder.str()<<endl;
  _TRACE(job_rs.m_props,set("insert_sql",sql_builder.str()));

  //DELETE -------------------------
  sql_builder(f_delete_sql)
   <<table_name
   <<":old"
   <<col_job_code
   <<col_job_grade
   <<col_job_country;

  cout<<"DELETE SQL:\n"<<sql_builder.str()<<endl;
  _TRACE(job_rs.m_props,set("delete_sql",sql_builder.str()));

  //UPDATE -------------------------
  sql_builder(f_update_sql)
   <<table_name
   <<":new"
   <<":old"
   <<col_job_code
   <<col_job_grade
   <<col_job_country
   <<col_job_title
   <<col_min_salary
   <<col_max_salary
   <<col_job_requirement
   <<col_language_req;

  cout<<"UPDATE SQL:\n"<<sql_builder.str()<<endl;
  _TRACE(job_rs.m_props,set("update_sql",sql_builder.str()));

  _TRACE(job_rs,open_table("job"))

  //INSERT NEW ROW -----------------------------------------
  t_db_row row;
  _TRACE(job_rs,describe(row))

  row["job_code"       ]="user";    //PK
  row["job_grade"      ]="3";       //PK
  row["job_country"    ]="Canada";  //PK [FK to COUNTRY]
  row["job_title"      ]="Tester of IBProvider";
  row["min_salary"     ]=1;
  row["max_salary"     ]=150;
  row["job_requirement"]="ADO, OLEDB, Builder C++, Visual C++, VB";

  row["language_req"   ].create_array(DBTYPE_BSTR,0,1,4);
  row["language_req"   ].set_element(1,"Russian");

  _TRACE(job_rs,insert_row(row,true))

  _TRACE(session,commit_retaining())

  //UPDATE NEW ROW -----------------------------------------

  //define compact row
  t_db_row row1;
  row1.columns(0)=row.columns("job_requirement");
  row1.columns(1)=row.columns("language_req");
  row1.count=2;

  row1["job_requirement"]=row["job_requirement"].as_string+", InterBase SQL Server";
  row1["language_req"   ]=row["language_req"];
  row1["language_req"   ].set_element(2,"English");

  _TRACE(job_rs,update_row(row1));
  _TRACE(session,commit_retaining())

  //DELETE NEW ROW -----------------------------------------
  _TRACE(job_rs,delete_row());
  _TRACE(session,commit())

 }
 catch(const exception& exc)
 {
  cout<<"error:"<<exc.what()<<endl;
 }

 cout<<endl;
}//execute