Devart Forums

MDrueck

MaximG wrote: ↑Fri 06 May 2022 11:27Has your customer tried to use ODAC with Apache ActiveMQ installed and configured?

Our customer knows nothing about ODAC. ;-) They are just asking if we can use Apache ActiveMQ instead of Oracle Queues, they don't care which tools we are using nor do they test any tools for us. Thanks, Markus

MDrueck

Hi,

currently we are using the ODAC components to connect to an Oracle Database via Oracle Advanced Queuing. Now our customer wants us to connect via Apache ActiveMQ to their database. Is that possible with ODAC too, or any other product from DevArt?

Thanks in advance, Markus

MDrueck

MaximG wrote: ↑Fri 10 Jul 2020 16:21 What features of Advanced Queueing are you going to use? Our components don't offer a dedicated option for enabling or disabling Direct Mode for Advanced Queueing.

Sorry, I don't know how to answer that question. ;-) I just want that the ODAC components work, nothing more nothing less. In the meantime me managed to connect to the database and successfully ENQUEUE a message. Now our main problem is, how to dequeue. We are connecting in directe mode.

So we have code:

Code: Select all

function TfrmMainAOS.DequeueFARMS : Boolean;
var
  Agents           : TQueueAgents;
  Agent            : TQueueAgent;
  QueueMessage     : TQueueMessage;
begin
  Result := False;
  try
    Agents := TQueueAgents.Create;
    try
      Agents.Add.Address := edtLEJQueueTo.Text;
      Agent := TQueueAgent.Create;

      OraQueue.QueueName                  := edtLEJQueueTo.Text;
      OraQueue.DequeueOptions.WaitTimeout := AQ_NO_WAIT;
      OraQueue.DequeueOptions.Navigation  := qnNextMessage;
      OraQueue.DequeueOptions.DequeueMode := dqmRemove;

      try
        Agent := TQueueAgent.Create;
        try
          OraQueue.Listen(Agents, Agent, FLEJTimeOut);
          var DequeueOptions: TDequeueOptions := TDequeueOptions.Create;
          try
            DequeueOptions.DequeueMode := dqmBrowse;
            QueueMessage := TQueueMessage.Create;
            try
              OraQueue.Dequeue(QueueMessage, DequeueOptions);
              OraQueueMessage(OraQueue, QueueMessage.MessageId, QueueMessage.MessageProperties);
            finally
              FreeAndNil(QueueMessage);
            end;
          finally
            FreeAndNil(DequeueOptions);
          end;
        finally
          Agent.Free;
        end;
      except
        on E : System.SysUtils.Exception do
        begin
          Log.Fatal(Format('OraSession.LastError:     [%d]', [OraSession.LastError]));
          HandleExceptionPrim(E, E.message, 10, 'TfrmMainAOS.DequeueFARMS');
        end;
      end;
    finally
      Agents.Free;
    end;

    Result := True;
  except
    on E : System.SysUtils.Exception do begin
      Log.Fatal(Format('OraSession.LastError:     [%d]', [OraSession.LastError]));
      HandleExceptionPrim(E, E.message, 11, 'TfrmMainAOS.DequeueFARMS');
    end;
  end;
end;

And:

Code: Select all

procedure TfrmMainAOS.OraQueueMessage(Sender: TOraQueue; const MessageId: string;
  const MessageProperties: TQueueMessageProperties);
var
  ObjectPayload   : TOraObject;
  FileName        : string;
  MsgId           : string;
  PayLoad         : string;      // Die eigentlichen Nutzdaten
  EnqueueTime     : TDateTime;
begin
  OraQueue.DequeueOptions.MessageId        := MessageId;
  OraQueue.DequeueOptions.DeliveryMode     := qdmPersistentOrBuffered;
  OraQueue.DequeueOptions.DequeueCondition := EmptyStr;
  EnqueueTime                              := MessageProperties.EnqueueTime;

  Log.Info(Format('MessageProperties.Delay = [%d]', [MessageProperties.Delay]));
  if MessageProperties.Delay > AQ_NO_DELAY then begin
    MWWait(MessageProperties.Delay * 1000);
  end;

  case MessageProperties.State of
    qmsReady     : Log.Info('MessageProperties.State = qmsReady');
    qmsWaiting   : Log.Info('MessageProperties.State = qmsWaiting');
    qmsProcessed : Log.Info('MessageProperties.State = qmsProcessed');
    qmsExpired   : Log.Info('MessageProperties.State = qmsExpired');
  end;

  if {(IncSecond(EnqueueTime,MessageProperties.Expiration) <= now) and } (MessageProperties.State = qmsReady) then begin
    try
      ObjectPayload := TOraObject.Create;

      try
        memXMLData.Lines.Clear; // das ist ein TMemo auf dem Formular
        FileName := Format('%s\FARMS To AM %s.xml', [edtTxtImportUpdateFolder.Text, FormatDateTime('YYYY DD MM - HH NN SS', Now)]);
        Log.Info(Format(' Message ID: [%s]', [MessageId]));
        Log.Info(Format(' Enqueue Time : [%s]', [FormatDateTime('YYYY DD MM - HH NN SS', EnqueueTime)]));
        Log.Info(Format(' Dequeue Attempts : [%s]', [IntToStr(MessageProperties.Attempts)]));

        try
          var DequeueOptions: TDequeueOptions := TDequeueOptions.Create;
          try
            DequeueOptions.DequeueMode := dqmRemove; // Es ist standardmäßig beim Create auf dqmRemove gesetzt, hier einfach zum Hinweis
            // hier jetzt die richtige Message holen, die über das Event übergeben wurde (MessageId)
            MsgId := OraQueue.Dequeue(ObjectPayload, MessageProperties, DequeueOptions);
          finally
            FreeAndNil(DequeueOptions);
          end;
        except
          on E : System.SysUtils.Exception do begin
            Log.Fatal(Format('OraSession.LastError:     [%d]', [OraSession.LastError]));
            Log.Fatal(Format('OraQueue.Empty            [%s]', [E.Message]));
          end;
        end;

        // hier die Daten holen die in der Payload stehen
        if ObjectPayload.AttrIsNull['text_lob'] then begin
          PayLoad := ObjectPayload.AttrAsString['text_vc']
        end else begin
          PayLoad := ObjectPayload.AttrAsLob['text_lob'].AsWideString;
        end;

        Log.Info(Format('DEQUEUE: MsgId: [%s]; Length: [%d]', [MsgId, ObjectPayload.AttrAsInteger['text_len']]));
        memXMLData.Lines.Add(PayLoad);
        OraQueue.Session.Commit;
        Log.Info(Format('OraSession.LastError: [%d]', [OraSession.LastError]));
      finally
        FreeAndNil(ObjectPayload);
        memXMLData.Lines.SaveToFile(FileName);
      end;
    except
      on E : System.SysUtils.Exception do begin
        Log.Fatal(Format('OraSession.LastError:     [%d]', [OraSession.LastError]));
        HandleExceptionPrim(E, E.message, 12, 'TfrmMainAOS.OraQueueMessage');
      end;
    end;
  end else begin
    Log.Fatal(Format(' Error message expired -> Message ID: [%d]', [MessageId]));
  end;
end;

The big question is, when do I know that the queues is empty? How often do I have to get a record from the queue in OraQueueMessage?

Thanks in advance.

MDrueck

Hi,

here it says:

Direct Mode

Allows your application to work with Oracle directly via TCP/IP without involving Oracle Client, thus significantly facilitating deployment and configuration of your applications.

Is that also true when I try to use Advanced Queuing?

Which properties of TOraSession do I have to set accordingly?

Thanks in advance, Markus

MDrueck

OK, I solved it my self, using the source code, I also bought. Case closed.

MDrueck

What I did:

closed Delphi 10.3.3.
installed "odac112d26pro.exe"
opened a project with components:
OraSession: TOraSession;
OraQueueTable1: TOraQueueTable;
OraDataSource1: TOraDataSource;
OraQueue: TOraQueue;
tried to compile;
got the error
„[dcc32 Fataler Fehler] AOSMain.pas(289): F2063 Verwendete Unit 'OraAQ' kann nicht compiliert werden“ (used Unit 'OraAQ' can't be compiled)

The folders „C:\Program Files (x86)\Devart\ODAC for RAD Studio 10.3\Lib\Win32“ and „C:\Program Files (x86)\Devart\ODAC for RAD Studio 10.3\Bin\Win32“ are part of the „Tools/Options/Language/Delphi/Library/Library path“. The file „C:\Program Files (x86)\Devart\ODAC for RAD Studio 10.3\Lib\Win32\OraAQ.dcu“ does exist.

So, what have I done wrong? Any help is appreciated. Thanks, Markus

MDrueck

AlexP wrote:I have answered you via email.

Yes, I know. I really appreciate your persistence in this case. But I don't have a simpler example at hand. All of the database definition is done by another company, not by me. And I don't have any knowledge with Oracle database at all, specially with queues. And my own Delphi code originates in parts from another developer who is out of business.

I tried to get help in this case in other (Delphi) forums, with no avail. I would pay for a solution because I think that the code to query an oracle queue should not be to hard to implement.

Any help is highly appreciated. Thanks, Markus

MDrueck

AlexP wrote:I received your scripts, however it is quite difficult to deal with these objects due to their volume. Please compose a SIMPLE sample, including only the objects, that are used to reproduce the problem situation.

I sent a private mail with a simple sample.

TIA, Markus

MDrueck

AlexP wrote:Hello,

Please send the full sample to support*devart*com.

I did sent you source by private mail "Re: [Devart #179514]: Re: Dequeue does not give result", 18.07.2016 15:30. Is there something you found out what is wrong?

TIA, Markus

MDrueck

Hi,

I still have trouble to get an application running using ODAC components. What seems to work ist the enqueuing, see line "[15.07.2016 13:01:45,803] OraSession.LastError: [0]" in my log file. Even the dequeuing gives no error, but I don't get any data back from the queue also.

The problem I have is, I don't run the Oracle database, I also did not created the queue, I'm only the user of the queue. And to make things worse, I don't have any access to the database/queue on my system. Only on our customers PC I do have access to the database/queue.

Most of the code is not written by me, but from another developer, but he quit his contract. Anybody here who can help making this code run?

TIA, Markus D

I use:
Delphi Seattle 10
ODAC Professional Edition 9.6.21 for RAD Studio 10
Windows 10, 64 bit

Components:

Code: Select all

object OraSession: TOraSession
  Options.Charset = 'AL32UTF8'
  Options.UseUnicode = True
  Username = 'am'
  Server = 'FARMSMQ'
  LoginPrompt = False
  HomeName = 'OraClient11g_home1'
  Left = 32
  Top = 472
end
object OraQueue: TOraQueue
  DequeueOptions.WaitTimeout = 0
  OnMessage = OraQueueMessage
  Left = 360
  Top = 471
end

Code:

Code: Select all

function TfrmMainAOS.DequeueFARMS : Boolean;
begin
  AddResults('Start DequeueFARMS');

  Result := False;
  try
    if OraSession.Connected then begin
      OraSession.Disconnect;
    end;

    OraSession.ConnectString            := edtConnectString.Text;
    OraQueue.Session                    := OraSession;
    OraQueue.QueueName                  := edtQueueTo.Text;
    OraQueue.DequeueOptions.WaitTimeout := AQ_NO_WAIT;
    OraQueue.DequeueOptions.Navigation  := qnNextMessage;
    OraQueue.DequeueOptions.DequeueMode := dqmRemove;

    OraSession.Connect;
    if OraSession.Connected then begin
      // kann nur gesetzt werden bei einer aktiven Session
      // wann immer ein neuer Eintrag in die Queue gemacht wird, erhalten wir jetzt eine notification
      // und OnMessage wird ausgelöst
      OraQueue.AsyncNotification := True;
    end;

    Result := True;
  except
    on E : Exception do
    begin
      HandleExceptionPrim(E, E.message, 4, 'DequeueFARMS');
    end;
  end;
  AddResults('Stop  DequeueFARMS');
end;  // DequeueFARMS

function TfrmMainAOS.EnqueueFARMS : Boolean;
var
  OraSQL           : TOraSQL;
  PayLoad          : string;
begin
  AddResults('Start EnqueueFARMS');

  Result := False;
  try
    OraSession.ConnectString := edtConnectString.Text;

    PayLoad := Format(mom_out.Lines.Text, [
        XMLDateTimeStrWithMS(Int(FStartDate)), XMLDateTimeStrWithMS(Int(FEndDate)), XMLDateTimeStrWithMS(Now)]);
    AddResults(Format('PayLoad: [%s]', [PayLoad]));

    // Hier wird das SQL direkt an die Oracle übergeben, und dann anschließend ausgeführt
    // Das SQL Statement muss hierbei natürlich der PL/SQL Syntax folgen
    // Hierfür ist folgender Link recht hilfreich :
    // http://download.oracle.com/docs/cd/B13789_01/appdev.101/b10802/d_aqadm.htm#1014384
    try
      OraSQL          := TOraSQL.Create(nil);
      OraSQL.Session  := OraSession;
      OraSQL.SQL.Text := 'declare' + #13 +
        '  message       SYS.AQ$_JMS_TEXT_MESSAGE;' + #13 +
        '  agent         sys.aq$_agent   := sys.aq$_agent('' '', null, 0);'+ #13 +
        '  queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;' + #13 +
        '  msg_props     DBMS_AQ.MESSAGE_PROPERTIES_T;' + #13 +
        '  msgid         raw(16);' + #13 +
        'begin' + #13 +
        '  message := sys.aq$_jms_text_message.construct;' + #13 +
        '  message.set_replyto(agent);' + #13 +      // Nur der Vollständigkeit als Beispiel mit befüllt, nicht notwendig
        '  message.set_type(''farms'');' + #13 +     // Nur der Vollständigkeit als Beispiel mit befüllt, nicht notwendig
        '  message.set_userid(''aquser'');' + #13 +  // Nur der Vollständigkeit als Beispiel mit befüllt, nicht notwendig
        '  message.set_appid(''am'');' + #13 +       // Nur der Vollständigkeit als Beispiel mit befüllt, nicht notwendig
        '  message.set_text(:text);' + #13 +         // Das ist die Payload, ist der Text größer als die max. Größe eines Varchar2
                                                     // wird daraus automatisch ein CLOB
        '  DBMS_AQ.ENQUEUE( queue_name         => :FromQueue' + #13 +
        '                 , enqueue_options    => queue_options' + #13 +
        '                 , message_properties => msg_props' + #13 +
        '                 , payload            => message' + #13 +
        '                 , msgid              => msgid);' + #13 +
        'end;';
      OraSQL.Prepare;
      OraSQL.ParamByName('text').AsString      := PayLoad;
      OraSQL.ParamByName('FromQueue').AsString := edtQueueFrom.Text;

      AddResults(Format('SQL.Text:' + #13#10 + '[%s]', [OraSQL.SQL.Text]));
      AddResults('Start: OraSQL.Execute');

      OraSQL.Execute;
      OraSession.Commit;
      AddResults(Format('OraSession.LastError: [%d]', [OraSession.LastError]));
      AddResults(Format('ENQUEUE: Payload:' + #13#10 + '%s' + 'Length: %d', [PayLoad, Length(PayLoad)]));
      AddResults('Stop: OraSQL.Execute');
      Result := True;
    except
      on E : Exception do begin
        HandleExceptionPrim(E, E.message, 4, 'EnqueueFARMS');
      end;
    end;
  finally
    FreeAndNil(OraSQL);
  end;
  AddResults('Stop  EnqueueFARMS');
end;  // EnqueueFARMS

procedure TfrmMainAOS.OraQueueMessage(Sender: TOraQueue; const MessageId: string;
  const MessageProperties: TQueueMessageProperties);
  { . }
var
  ObjectPayload   : TOraObject;
  FileName        : string;
  MsgId           : string;
  PayLoad         : string;
  StartTime       : TDateTime;
  EnqueueTime     : TDateTime;
begin
  // in MessageID steht die ID der Message, diesen ID an
  // dann das dequeueing starten
  OraQueue.DequeueOptions.MessageId        := MessageId;
  OraQueue.DequeueOptions.DeliveryMode     := qdmPersistentOrBuffered;
  OraQueue.DequeueOptions.DequeueCondition := EmptyStr;
  EnqueueTime                              := MessageProperties.EnqueueTime;

  // kann die Message gleich nach dem Enqueueing abgerufen werden, dann ist MessageProperties.Delay = 0
  // ansonsten abwarten bis delay abgelaufen ist
  // man könnte hier auch die Enqueueing Time mit der aktuellen vergleichen und dann enstsprechend abwarten
  if MessageProperties.Delay > AQ_NO_DELAY then begin
    MWWait(MessageProperties.Delay * 1000);
  end;

  // Message ist abgelaufen, wenn ich es richtig verstehe dann ist es bei FARMS immer 0
  // MessageProperties.State eignet sich besser zum abprüfen als expire zeit
  if {(IncSecond(EnqueueTime,MessageProperties.Expiration) <= now) and } (MessageProperties.State = qmsReady) then begin
    try
      StartTime     := Now;
      ObjectPayload := TOraObject.Create;

      try
        memXMLData.Lines.Clear;
        FileName := Format('%s\FARMS To AM %s.xml', [edtTxtImportUpdateFolder.Text, FormatDateTime('YYYY DD MM - HH NN SS', Now)]);
        AddResults(Format(#13#10 + ' Message ID: [%d]', [MessageId]));
        AddResults(Format(#13#10 + ' Enqueue Time : [%d]', [FormatDateTime('YYYY DD MM - HH NN SS', EnqueueTime)]));
        // wie oft wurde versucht die Message zu holen
        AddResults(Format(#13#10 + ' Dequeue Attempts : [%d]', [IntToStr(MessageProperties.Attempts)]));

        try
          // hier jetzt die richtige Message holen, die über das Event übergeben wurde (MessageId)
          MsgId := OraQueue.Dequeue(ObjectPayload);
        except
          on E : Exception do begin
            AddResults(Format('ERROR: OraQueue.Empty; [%s]', [E.Message]));
          end;
        end;

        // hier die Daten holen die in der Payload stehen
        if ObjectPayload.AttrIsNull['text_lob'] then begin
          PayLoad := ObjectPayload.AttrAsString['text_vc']
        end else begin
          PayLoad := ObjectPayload.AttrAsLob['text_lob'].AsWideString;
        end;

        AddResults(Format('DEQUEUE: MsgId: [%s]; Length: [%d]', [MsgId, ObjectPayload.AttrAsInteger['text_len']]));
        memXMLData.Lines.Add(PayLoad);
        OraQueue.Session.Commit;
        AddResults(Format('OraSession.LastError: [%d]', [OraSession.LastError]));
      finally
        FreeAndNil(PayLoad);
        memXMLData.Lines.SaveToFile(FileName);
      end;
    except
      on E : Exception do begin
        HandleExceptionPrim(E, E.message, 4, 'DequeueFARMS');
      end;
    end;

  end else begin
    // error handling hier dann gesondert abhandeln, eventuell löschen
    AddResults(Format(#13#10 + ' Error message expired -> Message ID: [%d]', [MessageId]));
  end;
end;  // OraQueueMessage

What is logged with "AddResults":

Code: Select all

[15.07.2016 13:01:40,846] Start EnqueueFARMS
[15.07.2016 13:01:40,846] PayLoad: [<soap-env:Envelope 
  xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:aodb="urn:com.tsystems.ac.aodb">
  <soap-env:Header>
    <aodb:control>
      <aodb:message-id>q4de3msys11:7384cfc6:125feac3cf8:-7fe7</aodb:message-id>
      <aodb:message-version>1.3</aodb:message-version>
      <aodb:message-type>DATASET</aodb:message-type>
      <aodb:request>
        <aodb:datatype>op_turn</aodb:datatype>
        <aodb:start-time>2016-07-14T00:00:00.000</aodb:start-time>
        <aodb:end-time>2016-07-19T00:00:00.000</aodb:end-time>
      </aodb:request>
      <aodb:timestamp>2016-07-15T13:01:40.846</aodb:timestamp>
      <aodb:sender>AM</aodb:sender>
    </aodb:control>
  </soap-env:Header>
  <soap-env:Body/>
</soap-env:Envelope>
]
[15.07.2016 13:01:45,753] SQL.Text:
[declare
  message       SYS.AQ$_JMS_TEXT_MESSAGE;
  agent         sys.aq$_agent   := sys.aq$_agent(' ', null, 0);
  queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
  msg_props     DBMS_AQ.MESSAGE_PROPERTIES_T;
  msgid         raw(16);
begin
  message := sys.aq$_jms_text_message.construct;
  message.set_replyto(agent);
  message.set_type('farms');
  message.set_userid('aquser');
  message.set_appid('am');
  message.set_text(:text);
  DBMS_AQ.ENQUEUE( queue_name         => :FromQueue
                 , enqueue_options    => queue_options
                 , message_properties => msg_props
                 , payload            => message
                 , msgid              => msgid);
end;
]
[15.07.2016 13:01:45,753] Start: OraSQL.Execute
[15.07.2016 13:01:45,803] OraSession.LastError: [0]
[15.07.2016 13:01:45,803] ENQUEUE: Payload:
<soap-env:Envelope 
  xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:aodb="urn:com.tsystems.ac.aodb">
  <soap-env:Header>
    <aodb:control>
      <aodb:message-id>q4de3msys11:7384cfc6:125feac3cf8:-7fe7</aodb:message-id>
      <aodb:message-version>1.3</aodb:message-version>
      <aodb:message-type>DATASET</aodb:message-type>
      <aodb:request>
        <aodb:datatype>op_turn</aodb:datatype>
        <aodb:start-time>2016-07-14T00:00:00.000</aodb:start-time>
        <aodb:end-time>2016-07-19T00:00:00.000</aodb:end-time>
      </aodb:request>
      <aodb:timestamp>2016-07-15T13:01:40.846</aodb:timestamp>
      <aodb:sender>AM</aodb:sender>
    </aodb:control>
  </soap-env:Header>
  <soap-env:Body/>
</soap-env:Envelope>
Length: 768
[15.07.2016 13:01:45,803] Stop: OraSQL.Execute
[15.07.2016 13:01:45,803] Stop  EnqueueFARMS
[15.07.2016 13:01:45,803] Start DequeueFARMS
[15.07.2016 13:01:48,213] Stop  DequeueFARMS

MDrueck

Hi,

I'm updating a project from ODAC February 2011 to ODAC 9.6.21 for RAD Studio 10.

There's one line of code which does not compile:

Code: Select all

function TfrmMainAOS.EnqueueFARMS : Boolean;
var
  OraSQL           : TUniSQL;
  PayLoad          : string;
begin
  Result := False;
  try
    OraSession.ConnectString := edtConnectString.Text;

    PayLoad := Format(mom_out.Lines.Text, [
        XMLDateTimeStr(Int(FStartDate)), XMLDateTimeStr(Int(FEndDate)), XMLDateTimeStr(Now)]);
    AddResults(Format('PayLoad: [%s]', [PayLoad]));

    OraSQL          := TUniSQL.Create(nil);
    OraSQL.Session  := OraSession;

The last line does not compile because of "OraSQL.Session" -> unknown identifier.

How do I fix that? TIA, Markus

MDrueck

AlexP wrote:These are specific components of ODAC, and they are not included in UniDAC. If you want to use TOraQueue, you need to install ODAC.

In 2011 I bought UniDAC.

Excerpt from the Devart Sales mail:
Thank you for the registration of Universal Data Access Components
Professional team license.

Your developer license number is xxxx.
Please notice it when contacting us.

You can download the full version of UniDAC Professional
at http://secure.devart.com/

So I thought I just have to renew my subscription to UniDAC, what I did. But as it seems now, I bought the wrong product. Any chance to convert my UniDAC license to a ODAC license?

TIA, M. Drück

MDrueck

Hi,

I used "odac710d14pro-1.exe" and "unidac360d14pro.exe" (installed February 2011) until now in a Delphi 2010 project. Now I upgraded to Delphi 10 Seattle and also to "unidac62d23pro.exe".

After opening my project I got an error message saying that TOraSession was not found. After looking here I found out that I have to migrate with the Migration Wizard. I did that, and the project opened with no error. So far so good.

But the source does not compile.

Some code:

Code: Select all

  TfrmMainAOS = class(TForm)
    OraSession: TUniConnection;

function TfrmMainAOS.EnqueueFARMS : Boolean;
var
  OraSQL           : TUniSQL;
  PayLoad          : string;
begin
  Result := False;
  try
    OraSession.ConnectString := edtConnectString.Text;

    PayLoad := Format(mom_out.Lines.Text, [
        XMLDateTimeStr(Int(FStartDate)), XMLDateTimeStr(Int(FEndDate)), XMLDateTimeStr(Now)]);
    OraSQL          := TUniSQL.Create(nil);
    OraSQL.Session  := OraSession;[b] <--- "OraSQL.Session" unknown identifier[/b]
    OraSQL.SQL.Text := 'declare' + #13 +
      '  message       SYS.AQ$_JMS_TEXT_MESSAGE;' + #13 +
      '  agent         sys.aq$_agent   := sys.aq$_agent('' '', null, 0);'+ #13 +
      '  queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;' + #13 +
      '  msg_props     DBMS_AQ.MESSAGE_PROPERTIES_T;' + #13 +
      '  msgid         raw(16);' + #13 +
      'begin' + #13 +
      '  message := sys.aq$_jms_text_message.construct;' + #13 +
      '  message.set_replyto(agent);' + #13 +
      '  message.set_type(''farms'');' + #13 +
      '  message.set_userid(''aquser'');' + #13 +
      '  message.set_appid(''am'');' + #13 +
      '  message.set_text(:text);' + #13 +
      '  DBMS_AQ.ENQUEUE( queue_name         => :FromQueue' + #13 +
      '                 , enqueue_options    => queue_options' + #13 +
      '                 , message_properties => msg_props' + #13 +
      '                 , payload            => message' + #13 +
      '                 , msgid              => msgid);' + #13 +
      'end;';
    OraSQL.Prepare;
    OraSQL.ParamByName('text').AsString      := PayLoad;
    OraSQL.ParamByName('FromQueue').AsString := edtQueueFrom.Text;

    AddResults(Format('SQL.Text:' + #13#10 + '[%s]', [OraSQL.SQL.Text]));
    AddResults('Start: OraSQL.Execute');

    OraSQL.Execute;
    OraSession.Commit;
    AddResults(Format('OraSession.LastError: [%d]', [OraSession.LastError]));[b] <--- "OraSession.LastError" unknown identifier[/b]

This code here is more complicated:

Code: Select all

function TfrmMainAOS.DequeueFARMS : Boolean;
var
  OraQueue         : TOraQueue;
  ObjectPayload    : TOraObject;
  FileName,
  MsgId, PayLoad   : string;
  DoBreak          : Boolean;
  OperationCount   : Integer;
  StartTime        : TDateTime;
begin
  Result := False;
  try
    StartTime := Now;
    OraSession.ConnectString := edtConnectString.Text;

    OraQueue            := TOraQueue.Create(nil);
    OraQueue.Session    := OraSession;
    OraQueue.QueueName  := edtQueueTo.Text;
    OraQueue.DequeueOptions.WaitTimeout := AQ_NO_WAIT;
    try
      Sleep(2000); // etwas warten
      memXMLData.Lines.Clear;
      OperationCount := 0;
      FileName := Format('%s\FARMS To AM %s.xml', [edtTxtImportUpdateFolder.Text, FormatDateTime('YYYY DD MM - HH NN SS', Now)]);
      repeat
        AddResults(Format(#13#10 + 'OperationCount: [%d]', [OperationCount]));
        DoBreak := False;
        repeat
          ObjectPayload := TOraObject.Create;
          try
            MsgId := OraQueue.Dequeue(ObjectPayload);
          except
            on E : Exception do begin
              DoBreak := True;
              Inc(OperationCount);
              AddResults(Format('ERROR: OraQueue.Empty; [%s]', [E.Message]));
            end;
          end;

          if ObjectPayload.AttrIsNull['text_lob'] then begin
            PayLoad := ObjectPayload.AttrAsString['text_vc']
          end else begin
            PayLoad := ObjectPayload.AttrAsLob['text_lob'].AsWideString;
          end;

          AddResults(Format('DEQUEUE: MsgId: [%s]; Length: [%d]', [MsgId, ObjectPayload.AttrAsInteger['text_len']]));

          DoBreak := Pos('>ACK<', PayLoad) > 1;

          memXMLData.Lines.Add(PayLoad);
          OraQueue.Session.Commit;
          ObjectPayload.Free;
        until DoBreak or ((Now - StartTime) > EncodeTime(0, 5, 0, 0));
        AddResults(Format('OraSession.LastError: [%d]', [OraSession.LastError]));
        Sleep(2000); // etwas warten
      until (OperationCount > 10) or ((Now - StartTime) > EncodeTime(0, 5, 0, 0));

      memXMLData.Lines.SaveToFile(FileName);
    finally
      OraQueue.Free;
    end;
    Result := True;
  except
    on E : Exception do begin
      HandleExceptionPrim(E, E.message, 4, 'DequeueFARMS');
    end;
  end;
end;  // DequeueFARMS

I watched in the online help, but could not find any info on how to migrate the code. Any info on how to migrate the code? Any ideas are welcome.

TIA, M. Drück

MDrueck

Hello,

AlexP wrote:Please provide us scripts to create both queues and a sample in which the FARMSMQ.AQ_TO_AM is filled.

I only have the code (from the documentation provided by the owner of the oracle database) for the queues. I'm not able to provide sample data, because we don't run the oracle database.

TIA, M. Drück

Code: Select all

BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => 'AQIF_FROM_AM_TABLE'
,QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE'
,COMPATIBLE => '8.1.3'
,STORAGE_CLAUSE => 'TABLESPACE TS_AODB_QUEUES
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL KEEP
)'
,SORT_LIST => 'ENQ_TIME'
,MULTIPLE_CONSUMERS => FALSE
,MESSAGE_GROUPING => 0
);
END;
/
BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE
(
QUEUE_NAME => 'AQIF_FROM_AM'
,QUEUE_TABLE => 'AQIF_FROM_AM_TABLE'
,QUEUE_TYPE => SYS.DBMS_AQADM.NORMAL_QUEUE
,MAX_RETRIES => 25
,RETRY_DELAY => 0
,RETENTION_TIME => 0
);
END;
/
BEGIN
SYS.DBMS_AQADM.START_QUEUE
(
QUEUE_NAME => 'AQIF_FROM_AM'
,ENQUEUE => TRUE
,DEQUEUE => TRUE
);
END;
/
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => 'AQIF_TO_AM_TABLE'
,QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE '
,COMPATIBLE => '8.1.3'
,STORAGE_CLAUSE => 'TABLESPACE TS_AODB_QUEUES
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL KEEP
)'
,SORT_LIST => 'ENQ_TIME'
,MULTIPLE_CONSUMERS => FALSE
,MESSAGE_GROUPING => 0
);
END;
/
BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE
(
QUEUE_NAME => 'AQIF_TO_AM'
,QUEUE_TABLE => 'AQIF_TO_AM_TABLE'
,QUEUE_TYPE => SYS.DBMS_AQADM.NORMAL_QUEUE
,MAX_RETRIES => 25
,RETRY_DELAY => 0
,RETENTION_TIME => 0
);
END;
/
BEGIN
SYS.DBMS_AQADM.START_QUEUE
(
QUEUE_NAME => 'AQIF_TO_AM'
,ENQUEUE => TRUE
,DEQUEUE => TRUE
);
END;
/

MDrueck

Hello,

AlexP wrote:As it can be seen from your code, you are using different queue names when using the Enqueue and Dequeue methods: FARMSMQ.AQ_FROM_AM when using Enqueue, and FARMSMQ.AQ_TO_AM when using Dequeue. That's why you get a wrong result. If you fix the Dequeue call by setting the queue name to FARMSMQ.AQ_FROM_AM, you will get a correct result.

We are using different queues, thats correct. We are told to do so. I'm not familiar with Oracle Queues, but reading your advice it seems that this is not very common. We are not the owner or creator of the queues, we only use them as a third party.

Any idea how it is possible to get a value higher than 0 in

Code: Select all

ObjectPayload.AttrAsInteger['text_len']

but the

Code: Select all

ObjectPayload.AttrAsString['text_vc']

is empty? This seems very strange to me.

TIA, M. Drück

Devart Forums

Search found 18 matches

Re: Support for Apache ActiveMQ?

Support for Apache ActiveMQ?

Re: Advanced Queuing possible without Oracle Client?

Advanced Queuing possible without Oracle Client?

Re: Project with ODAC does not compile

Project with ODAC does not compile

Re: Dequeue does not give result

Re: Dequeue does not give result

Re: Dequeue does not give result

Dequeue does not give result

TUniSQL.Session -> unknown identifier.

Re: "TOraQueue" / "TOraObject" missing

"TOraQueue" / "TOraObject" missing