Publish date: 2003-01-05
Work with BLOB field (VB, C++)
Sub sample4() Dim cn As New ADODB.Connection cn.Open "file name=d:\database\employee.ibp" Dim cmd As New ADODB.Command Dim rs As ADODB.Recordset cmd.ActiveConnection = cn cmd.CommandText = "select proj_name,proj_desc from project" Set rs = cmd.Execute While Not rs.EOF Debug.Print rs("proj_name") Debug.Print "BLOB size=" & CStr(rs("proj_desc").ActualSize) Debug.Print rs("proj_desc") 'BLOB rs.MoveNext Wend rs.Close ' use Field.GetChunk method for work with very large BLOB-field Debug.Print "Use Field.GetChink method" Set rs = cmd.Execute Dim chink As Variant Dim str As String While Not rs.EOF Debug.Print rs("proj_name") str = "" chink = "" Do str = str & chink chink = rs("proj_desc").GetChunk(16) Loop While VarType(chink) <> vbNull Debug.Print str rs.MoveNext Wend cn.BeginTrans Dim ins_cmd As New ADODB.Command ins_cmd.ActiveConnection = cn ins_cmd.CommandText = "insert into project" & Chr(13) & _ "(proj_id,proj_name,proj_desc,team_leader,product)" & Chr(13) & _ "values(?,?,?,?,?)" If (False) Then ' directly installation of the descriptions of parameters ins_cmd.Parameters.Append ins_cmd.CreateParameter("proj_id", adBSTR, , , "AAA") ins_cmd.Parameters.Append ins_cmd.CreateParameter("proj_name", adBSTR, , , "Test project 1") ins_cmd.Parameters.Append ins_cmd.CreateParameter("proj_desc", adLongVarChar, , -1, "This is test project 1") ins_cmd.Parameters.Append ins_cmd.CreateParameter("team_leader", adBSTR, , , Null) ins_cmd.Parameters.Append ins_cmd.CreateParameter("product", adBSTR, , , "software") Else ' Get the description of parameters ins_cmd.Parameters.Refresh ins_cmd(0) = "AAA" ins_cmd(1) = "Test project" ins_cmd(2) = "This is empty project." 'BLOB iiea ins_cmd(3) = Null 'no leader ins_cmd(4) = "software" End If ins_cmd.Execute 'cn.CommitTrans 'no commit insert operation End Sub
void TSampleCode4::execute() { cout<<"Sample #4"<<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)) t_db_command cmd; t_db_row row; _THROW_OLEDB_FAILED(cmd,create(session)) _THROW_OLEDB_FAILED(cmd,prepare("select proj_name,proj_desc from project",&row)) _THROW_OLEDB_FAILED(cmd,execute(NULL)) cout<<"direct reading of BLOB..."<<endl; while(cmd.fetch(row)==S_OK) { cout<<"---------------"<<endl; cout<<row["proj_name"].as_string<<endl; cout<<row["proj_desc"].as_string<<endl; }//while _THROW_OLEDB_FAILED(cmd,m_last_result); //delete TEST records ------------------------------------------ t_db_command del_cmd; _THROW_OLEDB_FAILED(del_cmd,create(session)) _THROW_OLEDB_FAILED(del_cmd,prepare("delete from project where proj_id in (\'AAA\',\'BBB\')",NULL)) _THROW_OLEDB_FAILED(del_cmd,execute(NULL)) //-------------------------------------------------------------- t_db_command ins_cmd; t_db_row param; _THROW_OLEDB_FAILED(ins_cmd,create(session)) //unnamed parameters ------------------------------------ _THROW_OLEDB_FAILED(ins_cmd, prepare("insert into project\n" "(proj_id,proj_name,proj_desc,team_leader,product)\n" "values(?,?,?,?,?)",NULL)) param[0]="AAA"; param[1]="Test project"; set_param(param,2,adLongVarChar,"This is test project"); set_param(param,3L).null=true; param[4]="software"; param.set_count(5); _THROW_OLEDB_FAILED(session,start_transaction()) _THROW_OLEDB_FAILED(ins_cmd,execute(¶m,false)) //named parameters -------------------------------------- _THROW_OLEDB_FAILED(ins_cmd, prepare("insert into project\n" "(proj_id,proj_name,proj_desc,team_leader,product)\n" "values(:id,:name,:desc,:leader,:prod)",NULL)) _THROW_OLEDB_FAILED(ins_cmd,describe_params(param)) param["id"] ="BBB"; param["name"]="Test Project 2"; param["desc"]="This is desc of Test Project 2"; param["leader"].null=true; param["prod"]="software"; _THROW_OLEDB_FAILED(ins_cmd,execute(¶m,false)) _THROW_OLEDB_FAILED(session,commit()) } catch(exception& exc) { cout<<"error: "<<exc.what()<<endl; } }//execute