How to get database notification to C++ applicatio

Question

We are developing a C++ server which will process based on time based events. For example if a particular user has configured a particular task that has to be processed at a particular time, then an event or notification needs to be sent to the C++ server from the database, to initiate the task. The timming configuration is stored in the database, and the C++ server shouldnt poll the database at an interval, but event should be notified from the database at the configured timming.

We are using odatabase and odynaset libraries to connect and access the oracle database 11g.

Kindly request you to provide a solution for the above problem.

Answer 1

One way would be to use dbms_pipe or dbms_alert. ie you connect from your C++ server session to the db (PRO C) and call dbms_pipe/alert which will block until the db, in another session sends data down the pipe that the c++ server will read and then process. for example, if its a simple "GO RUN NOW!" you want to send, then dbms_alert will do.

for example:

SQL> declare
  2    v_name  varchar2(200);
  3    v_msg   varchar2(200);
  4    v_sts   number; -- 0 = alert occured, 1 = timeout
  5  begin
  6    dbms_alert.register('RUN_PROGRAM_A');
  7    dbms_alert.register('RUN_PROGRAM_B');
  8
  9    loop
 10      dbms_alert.waitany(v_name,
 11                         v_msg,
 12                         v_sts,
 13                         dbms_alert.maxwait);
 14
 15      if (v_sts = 0)
 16      then
 17        dbms_output.put_line('i got alert: ' ||v_name);
 18        dbms_output.put_line(' with assoc message: ' ||v_msg);
 19      end if;
 20      if (v_name  = 'RUN_PROGRAM_B')
 21      then
 22        exit;
 23      end if;
 24    end loop;
 25  end;
 26  /
i got alert: RUN_PROGRAM_A
with assoc message: whatever you want to transmit.
i got alert: RUN_PROGRAM_A
with assoc message: whatever you want to transmit.
i got alert: RUN_PROGRAM_B
with assoc message: whatever you want to transmit.

PL/SQL procedure successfully completed.

where the db controlling session issued this:

SQL> exec dbms_alert.signal('RUN_PROGRAM_A', 'whatever you want to transmit.');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> exec dbms_alert.signal('RUN_PROGRAM_A', 'whatever you want to transmit.');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> exec dbms_alert.signal('RUN_PROGRAM_B', 'whatever you want to transmit.');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

another approach is a scheduler approach (dbms_queue), which is a message queue approach, in which you'd be polling (dequeueing) against a queue table and would do something when a message arrives.

Answer 2

One approach is to use Oracle's Advanced Queuing. For that purpose you need to set up a queue (and a queue table) and write a PL/SQL procedure that waits for the next message in the queue.

The C++ side then calls the PL/SQL procedure, which returns when the next event has occurred.

On the Oracle side, you will need to use DBMS_SCHEDULER or a similar facility to create the event, i.e. to insert a new message into the queue at the appropriate time.

It's still a polling approach. However, there's absolutely no activity between two events.

Update:

Here's some sample code.

Initial setup of the queue (the message contains a numeric and a text value):

grant AQ_ADMINISTRATOR_ROLE to appuser;
grant EXECUTE ON DBMS_AQ to appuser;
grant EXECUTE ON DBMS_AQ to appuser;


CREATE TYPE sample_payload_type AS OBJECT
(
  cmd  VARCHAR2(20),
  id   NUMBER
);


BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE (
    queue_table        => 'sample_queue_table',
    queue_payload_type => 'sample_payload_type',
    sort_list          => 'ENQ_TIME',
    compatible         => '10.0'
  );
END;
/

BEGIN
  DBMS_AQADM.CREATE_QUEUE (
    queue_name         => 'sample_queue',
    queue_table        => 'sample_queue_table'
  );

  DBMS_AQADM.START_QUEUE (
    queue_name         => 'sample_queue'
  );
END;
/

Package header:

create or replace package sample_queue_pkg
as

  procedure get_next_msg(
    i_max_wait      number
   ,o_cmd      out  varchar2
   ,o_id       out  number
  );


  procedure put_msg(
    i_cmd           varchar2
   ,i_id            number
  );

end sample_queue_pkg;
/

Package body:

create or replace package body sample_queue_pkg
as

  procedure get_next_msg(
    i_max_wait      number
   ,o_cmd      out  varchar2
   ,o_id       out  number
  )
  is
    dequeue_options dbms_aq.dequeue_options_t;
    message_properties dbms_aq.message_properties_t;
    message_handle RAW(16);
    message sample_payload_type;

    NO_MESSAGE_RECEIVED EXCEPTION;
    PRAGMA EXCEPTION_INIT(NO_MESSAGE_RECEIVED, -25228);

  begin
    dequeue_options.wait := i_max_wait;
    DBMS_AQ.DEQUEUE (
      queue_name => 'appuser.sample_queue',
      dequeue_options => dequeue_options,
      message_properties => message_properties,
      payload => message,
      msgid => message_handle
    );

    o_cmd := message.cmd;
    o_id := message.id;

  exception
    when NO_MESSAGE_RECEIVED then
      o_cmd := null;
      o_id := null;

  end get_next_msg;


  procedure put_msg(
    i_cmd           varchar2
   ,i_id            number
  )
  is
    enqueue_options dbms_aq.enqueue_options_t;
    message_properties dbms_aq.message_properties_t;
    message_handle RAW(16);
    message sample_payload_type;
    message_id NUMBER;

  begin
    message := sample_payload_type(i_cmd, i_id);
    DBMS_AQ.ENQUEUE(
      queue_name => 'appuser.sample_queue',
      enqueue_options => enqueue_options,
      message_properties => message_properties,
    payload => message,
      msgid => message_handle
    );
  end put_msg;

end sample_queue_pkg;
/

The database server can send a message using the following code:

sample_queue_pkg.put_msg('run_task', 8234);
commit;

The C++ server can wait for message (and receive them) calling the stored sample_queue_pkg.get_next_msg. The parameter i_max_wait specifies the maximum time to wait for the next message in seconds. You probably want to implement a loop that waits for the next message and processes it until it receives a signal that the server is about to quit.