Setting up SQL applications

QAnywhere SQL allows you to perform, in SQL, much of the messaging functionality of the QAnywhere .NET, C++, and Java APIs. This functionality includes creating messages, setting or getting message properties and content, sending and receiving messages, triggering message synchronization, and setting and getting message store properties.

Messages that are generated with QAnywhere SQL can also be received by clients created with the programming APIs. If you have configured a JMS connector on your server, the messages can also be received by JMS clients. Similarly, QAnywhere SQL can be used to receive messages that were generated by QAnywhere .NET, C++, or Java API, or JMS clients.

QAnywhere SQL messaging coexists with user transactions. This means that committing a transaction commits all the QAnywhere operations on that connection.

See Writing QAnywhere client applications.

Permissions

Only users with DBA privilege have automatic permission to execute the QAnywhere stored procedures. To give permission to a user, a user with DBA privilege must call the procedure ml_qa_grant_messaging_permissions.

See ml_qa_grant_messaging_permissions.

Acknowledgement modes

The QAnywhere SQL API does not support IMPLICIT_ACKNOWLEDGEMENT or EXPLICIT_ACKNOWLEDGEMENT modes. All messaging through the SQL API is transactional.

Example

The following example creates a trigger on an inventory table. The trigger sends a message when the inventory for an item falls below a certain threshold. The message is sent after the transaction invoking the trigger is committed. If the transaction is rolled back, the message is not sent.

CREATE TRIGGER inventory_trigger AFTER UPDATE ON inventory
REFERENCING old AS oldinv new AS newinv
FOR EACH ROW
begin
   DECLARE msgid VARCHAR(128);
   IF oldinv.quantity > newinv.quantity AND newinv.quantity < 10 THEN
      -- Create the message
      SET msgid = ml_qa_createmessage();
      -- Set the message content
      CALL ml_qa_settextcontent( msgid,
         'Inventory of item ' || newinv.itemname
            || ' has fallen to only ' || newinv.quantity );
      -- Make the message high priority
      CALL ml_qa_setpriority( msgid, 9 );
      -- Set a message subject
      CALL ml_qa_setstringproperty( msgid,
         'tm_Subject', 'Inventory low!' );
      -- Send the message to the inventoryManager queue
      CALL ml_qa_putmessage( msgid,
         'inventoryManager' );
   end if;
end