Out-of-band Database Messages

Wiki (en)
, ,
1

Summary

Most traditional database platforms provide out-of-band messages to supplement normal result set data exchange. The note describes how to use out-of-band messages from the native database platform with Invantive SQL. Samples include: dbms_output, print and notify.

Out-of-Band Text Messages

Almost all traditional database platforms allow transport of message out-of-band communication of text messages. Regular data transport is done using queries and data sets, consisting of metadata, plus a table with contents. But out-of-band communication allows unstructured communication between the database platform and the client application.

Samples of out-of-band communication include:

  • Simple print statements on SQL Server, such as print 'Hello World'.
  • Procedural print statements on Oracle, such as begin dbms_output.put_line('Hello World'); end;.
  • Asynchronous notifications on PostgreSQL, such as listen channel and then notify channel, 'Hello World'.

Invantive SQL

Invantive SQL can receive and process out-of-band text messages from traditional database platform to improve integration with your native database platforms.

SQL Server

On SQL Server, the procedural logic or SQL batches can contain the print statement, such as:

print 'start query'
select 1
print 'end query'

You can use either a stored procedure with this logic or the Invantive SQL alternative:

begin
  execute 
  native 
  'print ''start query'' select 1 print ''end query''' 
  datacontainer 'sqlserver1'
  ;
end;

The data container ‘sqlserver1’ corresponds to a SQL Server-based data container.

When executed, the out-of-band messages are received after execution finishes.

Oracle

On Oracle, the procedural logic from procedures, packages, functions, anonymous blocks and triggers can call the dbms_output package, such as:

begin
  dbms_output.enable; 
  dbms_output.put_line('sample 1'); 
  dbms_output.put_line('sample 2'); 
end;

You can use either a stored procedure with this logic or the Invantive SQL alternative:

begin
  execute 
  native 'begin dbms_output.enable; dbms_output.put_line(''sample 1''); dbms_output.put_line(''sample 2''); end;'
  datacontainer 'oracle'
  ;
end;

The data container ‘oracle’ corresponds to an Oracle-based data container.

This functionality works on both Oracle native as well as Oracle managed SQL*Net drivers.

PostgreSQL

On PostgreSQL, the procedural logic or SQL batches can contain the notify statement, such as:

listen virtual
notify virtual, 'hello world'

You can use either a stored procedure with this logic or the Invantive SQL alternative:

begin
  execute native 'listen virtual' datacontainer 'pg';
  execute native 'notify virtual, ''hello world''' datacontainer 'pg';
end;

The data container ‘pg’ corresponds to a PostgreSQL-based data container.