Server-side setup v42.7.3.1

To use advanced queueing functionality on your JMS-based Java application perform following steps in EDB-PSQL or EDB-JDBC:

1. Create a user-defined message type, which may be one of the standard JMS message types. However, EDB-JDBC also supports any user-defined message types. These message types will be covered in detail in the upcoming sections.
2. Create a queue table specifying the payload type. This type will typically be the one created in step 1.
3. Create a queue using the queue table created in the previous step.
4. Start the queue on the database server.
5. You have the option to use either EDB-PSQL or EDB-JDBC JMS API in your Java application.

Using EDB-PSQL

Invoke EDB-PSQL and connect to the EDB Postgres Advanced Server host database. Use the SPL commands in EDB-PSQL to:

• Create a user-defined type
• Create the queue table
• Create the queue
• Start the queue

Create a user-defined type

To specify a RAW data type, create a user-defined type.

This example shows how to create a user-defined type named as mytype:

CREATE OR REPLACE TYPE mytype AS (code INT, project TEXT, manager VARCHAR(10));

Create the queue table

A queue table can hold multiple queues with the same payload type.

This example shows how to create a queue table named MSG_QUEUE_TABLE:

EXEC DBMS_AQADM.CREATE_QUEUE_TABLE
      (queue_table => 'MSG_QUEUE_TABLE',
       queue_payload_type => 'mytype',
       comment => 'Message queue table');
END;

Create the queue

This example shows how to create a queue named MSG_QUEUE in the table MSG_QUEUE_TABLE:

EXEC DBMS_AQADM.CREATE_QUEUE 
      (queue_name => 'MSG_QUEUE', 
       queue_table => 'MSG_QUEUE_TABLE', 
       comment => 'This queue contains pending messages.');

Start the queue

Once the queue is created, start the queue.

This example shows how to start a queue in the database:

EXEC DBMS_AQADM.START_QUEUE(queue_name => 'MSG_QUEUE');
commit;

Using EDB-JDBC JMS API

The following JMS API calls perform the same steps performed using EDB-PSQL to:

• Connect to the EDB Postgres Advanced Server database
• Create the user-defined type
• Create the queue table and queue
• Start the queue

edbJmsFact = new EDBJmsConnectionFactory("localhost", 5444, "edb", "edb", "edb");

conn = (EDBJmsQueueConnection) edbJmsFact.createQueueConnection();
                        
session = (EDBJmsQueueSession) conn.createQueueSession(true, Session.CLIENT_ACKNOWLEDGE);            
            
String sql = "CREATE OR REPLACE TYPE mytype AS (code int, project TEXT);";
UDTType udtType = new UDTType(conn.getConn(), sql, "mytype");
Operation operation = new UDTTypeOperation(udtType);
operation.execute();

queueTable = session.createQueueTable(conn.getConn(), "MSG_QUEUE_TABLE", "mytype", "Message queue table");
            
Queue queue1 = new Queue(conn.getConn(), "MSG_QUEUE", "MSG_QUEUE_TABLE", "Message Queue");
operation = new QueueOperation(queue1);
operation.execute();

queue = (EDBJmsQueue) session.createQueue("MSG_QUEUE");
queue.setEdbQueueTbl(queueTable);
            
queue.start();

export const _frontmatter = {"title":"Server-side setup","deepToC":true,"indexdepth":3}

• On this page
• Using EDB-PSQL
  • Create a user-defined type
  • Create the queue table
  • Create the queue
  • Start the queue
• Using EDB-JDBC JMS API