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.
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.
The QAnywhere SQL API does not support IMPLICIT_ACKNOWLEDGEMENT or EXPLICIT_ACKNOWLEDGEMENT modes. All messaging through the SQL API is transactional.
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