problem with oraclequery and clob

[angel]

Hi, I am trying to set a clob parameter in an oracle query, using the following code:

LOB := TLOBLocator.Create(Session, otBlob);
TIdText(Msg.MessageParts.Items).body.SaveToStream(LOB);
QyInsertAttachment.SetComplexVariable('BOOKC_NOTE', LOB);

but when execute the saveToStrean instruction, I get the an exception with message 'TLobLocator invalid handle'.

I am using rad studio 2007, Oracle 10g and doa 410.

Some idea about this?

Thanks very much

 

[Marco Kalter]

If you use TLOBLocator.Create you get a persistent BLOB. This is a LOB Locator that is tied to a database column. Before you can write data, you need to fetch the BLOB from the database after an insert, update or "select for update".

You can also create a temporary BLOB though, by using TLOBLocator.CreateTemporary. In this case you can immediately write the data and pass it to an insert/update statement.

 

[angel]

Hi, Marco thank you for your quick answer.
Now I have an other problem, when do the execute i get the Access violation in module oracommon10.dll.

Here is the code

procedure prepareInsertSql;
   var i: integer;
       ms: TMemoryStream;
       bBodySetted: boolean;
       LOB: TLOBLocator;
   begin
      ms  := TMemoryStream.Create;
      try
      for i := 0 to Pred(Msg.MessageParts.Count) do
      begin
     //    ms.Clear;
         if (Msg.MessageParts.Items[i] is TIdAttachment) then
         begin //general attachment
            TIdAttachmentmemory(Msg.MessageParts.Items[i]).SaveToStream(ms);
            QyInsertAttachment.SetVariable('BOOK_ID', MsgRec.MsgBookId);
            QyInsertAttachment.SetVariable('BOOK_SERVICE_ID', MsgRec.MsgBookId);
            QyInsertAttachment.SetVariable('COMP_ID', QySelBookRequest.FieldAsString('COMP_ID'));
            QyInsertAttachment.SetVariable('LANG_ID', QySelBookRequest.FieldAsString('LANG_ID'));

//Long Raw column            QyInsertAttachment.SetLongVariable('BOOKC_ATTACHMENT', ms, ms.Size);

            QyInsertAttachment.SetVariable('BOOKC_ATTACHMENT_NAME', TIdAttachment(Msg.MessageParts.Items[i]).FileName);
            QyInsertAttachment.SetVariable('BOOKC_ATTACHMENT_TYPE', TIdAttachment(Msg.MessageParts.Items[i]).ContentType);
            QyInsertAttachment.SetVariable('OPER_ID', QySelBookRequest.FieldAsString('OPER_ID'));
            QyInsertAttachment.SetVariable('EMPL_ID', QySelBookRequest.FieldAsString('EMPL_ID'));
         end
         else
            if Msg.MessageParts.Items[i] is TIdText and not bBodySetted then
            begin
               bBodySetted := true;
               LOB := TLOBLocator.CreateTemporary(Session, otBlob, false);
               TIdText(Msg.MessageParts.Items[i]).body.SaveToStream(LOB);
               QyInsertAttachment.SetComplexVariable('BOOKC_NOTE', LOB);
            end;
      end;
      finally
         ms.Free;
      end;
end;

after called I do QyInsertAttachment.execute.

and get the error.

Could it depend on a wrong longraw field setting?

Thank you again

 

[angel]

Hi, Forgot to say that continuing after the exception get the error ora-24909 pending call

 

[Marco Kalter]

Do you have a long raw field? Which one? Note that a TLOBLocator can only be used for BLOB, CLOB and BFILE fields.

 

[angel]

Yes I have BOOKC_ATTACHMENT that is lonraw and that is loaded using

QyInsertAttachment.SetLongVariable('BOOKC_ATTACHMENT', ms, ms.Size);

as well the problem seems to be in this setting, because, if comment this line, all run fine.

Maybe I do something wrong in settigs?

respect to the original code sended the ms.clear now is uncommented.

 

[angel]

Hi, I have found the problem. in

QyInsertAttachment.SetLongVariable('BOOKC_ATTACHMENT', ms, ms.Size);

erroneusly passed the stream instead of ms.memory

Thanks very much for the help