Querying s-Server Tables through SQL/MED

<< Click to Display Table of Contents >>

Navigation:  »No topics above this level«

Querying s-Server Tables through SQL/MED

Return to chapter overview

Using the s-Server engine driver, you can create loopback tables, that you can query with the SQL/MED plugin as if they were external database tables. A loopback table is a kind of alias to a native sqlstream table, which makes native tables appear to be tables in an external database, connected using the SQL/MED plugin. Once you create a loopback table, you can query native tables using the same standard three-level qualifier scheme that you use for external database tables.

Note: This is the only recommended use for the s-Server engine driver as of s-Server 5.0.0.

We recommend using loopback tables to access a native table from inside application pipelines. You should use these for applications that need to access tables via adapters, UDXs, or pumps. For applications that access native tables through JDBC or SQL, loopback tables are not necessary.

To create a loopback table, you first create a loopback foreign server, that is, a sql/med server using the SYS_JDBC wrapper that points back at s-Server. See the topic Reading Data from RDBMS Sources for more details. This server references the s-Server engine driver, com.sqlstream.aspen.vjdbc.SqlStreamVJdbcEngineDriver:

CREATE OR REPLACE SERVER LOOP_S_SERVER

FOREIGN DATA WRAPPER SYS_JDBC

OPTIONS(

   DRIVER_CLASS 'com.sqlstream.aspen.vjdbc.SqlStreamVJdbcEngineDriver',

   URL 'JDBC:SQLSTREAM:ENGINE:',

   STREAMING 'TRUE',

   USER_NAME 'SA');

 

You can then query s-Server tables using this server as part of the standard three-level qualifier scheme:

SELECT STREAM ROWTIME, * FROM LOOP_S_SERVER.SALES.BIDS;

SELECT STREAM *

 FROM LOOP_S_SERVER.SALES.BIDS OVER (ROWS 3 PRECEDING)

 JOIN LOOP_S_SERVER.SALES.ASKS OVER (ROWS CURRENT ROW)

 ON BIDS."TICKER" = ASKS."TICKER";