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(¶m)) 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(¶m)) print_records(cmd); } catch(exception& exc) { cout<<"error: "<<exc.what()<<endl; } }//SampleFunc3