Saving To Text based BLOB field

rhettp · Post by **rhettp** » Tue 15 Aug 2006 01:28

Does anyone know the best way to post a TStringList to a Memo based BLOB field with IBDAC? I can't seem to find a good way to do this. Posting with Data Aware controls works fine, but using SQL with Params doesn't seem to do the trick.

Thanks,

Rhett Price
IndySoft

Alex · Post by **Alex** » Tue 15 Aug 2006 06:58

You can do it in a several manners:
Using DataSet (i.e. TIBCQuery) you can do the following:

Code: Select all

 
 var
    StringList: TStringList;
    IBCQuery: TIBCQuery;

begin
  ....
  IBCQuery.SQL.Text := 'SELECT ID, BLOB_FIELD FROM BLOB_TABLE ';
  IBCQuery.Open;
  ....
  IBCQuery.Edit;
  IBCQuery.FieldByName('BLOB_FIELD').Assign(StringList);
  IBCQuery.Post;

  //If IBCQuery.AutoCommit is False (default) you should manually commit all changes 
  IBCQuery.Transaction.CommitRetaining; //If   IBCQuery.UpdateTransaction is set than you should use 
                                                //IBCQuery.UpdateTransaction.Commit; instead

Using params and TIBCSQL:

Code: Select all

 
var
   StringList: TStringList;
   IBCSQL: TIBCSQL;
   IBCQuery: TIBCQuery;

begin
  ...
  //NOTE: IBCQuery.Transaction.Params should be set to READ COMMITTED isolation level
  IBCQuery.SQL.Text := 'SELECT ID, BLOB_FIELD FROM BLOB_TABLE ';
  IBCQuery.Open;
  ....
  IBCSQL.SLQ.Text := 'UPDATE BLOB_TABLE SET BLOB_FIELD = :BLOB_FIELD WHERE (ID = :ID)'
  ....
  IBCSQL.ParamByName('ID').AsInteger :=  1;
  IBCSQL.ParamByName('BLOB_FIELD').AsString := StringList.Text;
  IBCSQL.Execute;
  IBCSQL.Transaction.Commit;
  ....
  IBCQuery.Refresh;

rhettp · Post by **rhettp** » Tue 15 Aug 2006 14:22

Thanks for the response - I'm still getting errors on the 2 methods you mentionned. I am using the Delphi 5 version of IBDAC. Here are some notes:

------------------------

** METHOD 1 (parameters):

procedure TBlankMainForm.UpdateACommentWithListTest(sTable, sField, sWhere: String; mMemo: TStrings);
begin
if ((sTable = '') or (sField = '')) then exit;
ibQueryTemp.Close;
ibQueryTemp.SQL.Clear;
ibQueryTemp.SQL.Text := 'UPDATE ' + sTable + ' SET ' + sField + ' = :MEMOTEMP ' + sWhere;
ibQueryTemp.ParamByName('MEMOTEMP').AsString := mMemo.Text;
ibQueryTemp.Execute;
end;

- Returns the error:

Dynamic SQL Error
SQL error code = -303
feature is not supported
BLOB and array data types are not supported for move operation

- the blob type I'm attempting to update is:

BLOB sub_type 1 segment size 80

- Here is the TIBCQuery object from dfm:

object ibQueryTemp: TIBCQuery
Connection = IBCConnection1
FetchAll = True
AutoCommit = True
Left = 588
Top = 216
end

------------------------

** METHOD 2 (edit):

procedure TBlankMainForm.UpdateACommentWithListTest(sTable, sField, sWhere: String; mMemo: TStrings);
var
slTemp : TStringList;
begin
if ((sTable = '') or (sField = '')) then exit;

IBCQuery2.Close;
IBCQuery2.SQL.Clear;
// selects all key fields and memo field (sField)
IBCQuery2.SQL.Text := 'SELECT EVENT_NUM, ATTRIBUTE_NAME, COMPANY, GAGE_SN ' + sField + ' FROM ' + sTable + ' ' + sWhere;
IBCQuery2.Open;

slTemp := TStringList.Create;
try
slTemp.Text := mMemo.Text;
IBCQuery2.Edit;
IBCQuery2.FieldByName(sField).Assign(slTemp);
IBCQuery2.Post;
finally
slTemp.Free;
end;
end;

- if I do not have TFields created for IBCQuery2, running the code above returns the error:

Cannot assign a TStringList to a TStringField

- if I have TFields created for IBCQuery2, with my blob field declared as TMemoField, returns the error:

IBCQuery2: Type mismatch for field 'ATTRIBUTE_AS_MEMO', expecting: Memo actual String.

Alex · Post by **Alex** » Wed 16 Aug 2006 09:54

We couldn't reproduce this problem. We need more detailed description of it.
Please specify Table DDL SQL, client and server versions, IBDAC version.
Also send small complete sample project with scripts creating server side objects to IBDAC support address if possible.

rhettp · Post by **rhettp** » Wed 16 Aug 2006 16:55

Sure thing. I will send to support. Thanks for the help.

Alex wrote:We couldn't reproduce this problem. We need more detailed description of it.
Please specify Table DDL SQL, client and server versions, IBDAC version.
Also send small complete sample project with scripts creating server side objects to IBDAC support address if possible.

Guest · Post by **Guest** » Tue 22 Aug 2006 17:51

In case anyone is curious, here is how support told us to update a Notes based BLOB field. This method works just fine.

IBCSQL1.SQL.Text := 'INSERT INTO IBDAC_BLOB_TEXT (Code, Title, Val) VALUES (100, ''TEST'', :BLOB_FIELD)';
IBCSQL1.ParamByName('BLOB_FIELD').DataType := ftBlob; //<<<<<<<< Here we set right param datatype
IBCSQL1.ParamByName('BLOB_FIELD').ParamType := ptInput; //<<<<<<<< Here we set right ParamType
IBCSQL1.ParamByName('BLOB_FIELD').AsString := StringList.Text;

Watson007 · Post by **Watson007** » Sun 03 Jun 2007 15:52

i have this problem too. If i want to fill Data over Parameter in a Textblob-field, i have this error:

BLOB and array data types are not supported for move operation

I use Delphi 7 Prof und IBDAC2.

Sorry for my bad English...

this doesnt work:

IBCSQL1.ParamByName('BLOB_FIELD').DataType := ftBlob; //<<<<<<<< Here we set right param datatype
IBCSQL1.ParamByName('BLOB_FIELD').ParamType := ptInput; //<<<<<<<< Here we set right ParamType
IBCSQL1.ParamByName('BLOB_FIELD').AsString := StringList.Text;

Alex · Post by **Alex** » Tue 05 Jun 2007 06:02

Please specify you update SQL and script of updating table. If it is possible, send us a small demo project that demonstrates this problem to IBDAC support address.

Watson007 · Post by **Watson007** » Tue 05 Jun 2007 06:45

It was a blob sub_type 1

I used this method:

insert into tabelle values(:id,:blob)

query.param[0].asinteger:=id;
query.param[1].asstring:=blob;
commit;

I now solved this Problem while using stringstream and

query.param[1].loadfromstream