Sample Code for Setting up TableReader for PostgreSQL and Other Databases

<< Click to Display Table of Contents >>

Navigation:  Integrating SQLstream Blaze with Other Systems > Appendix A: Legacy Adapters and Agents > Table Reader Adapter >

Sample Code for Setting up TableReader for PostgreSQL and Other Databases

Previous pageReturn to chapter overviewNext page

Note: the functionality of this adapter has been replaced by the SQL/MED Plugin for JDBC. If you are new to s-Server, please use this plugin to connect with an external database. This plugin provides SQL/MED access to any foreign DBMS for which a JDBC driver is available.

The following code snippet can be used to set up a state table, foreign data wrapper, server object, and control stream for a PostgreSQL database. Similar code can be used in other databases. In this case, SQLstream is the owner of the state table and is the login user used.

CREATE TABLE "SQLS_TableReader_State" (

   "SQLS_tableName" varchar(30) PRIMARY KEY NOT NULL,

   "SQLS_highId" bigint,

   "SQLS_highTime" timestamp without time zone

);

COMMENT ON TABLE "SQLS_TableReader_State" IS 'high-water marks for TableReader adapter';
 

ALTER TABLE "SQLS_TableReader_State" OWNER TO SQLstream;Examples
 

--set initial high water mark using a timestamp(not mandatory)

INSERT INTO SQLS_TableReader_State VALUES ('my_table',null,'2014-01-01 00:00:01');

 

-- Install TableReader
 

CREATE OR REPLACE FOREIGN DATA WRAPPER "TableReader"

   LIBRARY 'class com.sqlstream.plugin.tablereader.TableReaderStreamControlPlugin'

   LANGUAGE java

   DESCRIPTION 'adapter for reading "events" from an external database';

--Create server

CREATE OR REPLACE SERVER "Postgres_TableReader"

    FOREIGN DATA WRAPPER "TableReader"

    OPTIONS (

        URI 'jdbc:postgresql://localhost/yourdatabase',

        DRIVER 'org.postgresql.Driver',

        connParamPrefix 'dbConn_',

        "dbConn_user" 'username',

        "dbConn_password" '',

        "dbConn_applicationName" 'SQLstream TableReader Adapter',

        sqlDialect 'Postgres 8.x',

        stateTable 'SQLS_TableReader_State',

        pollingMillis '10000')

    DESCRIPTION 'Foreign server to monitor tables in "yourdatabase"';

 

The connection information above can alternately be replaced with the following code:

   connParams 'my/poc/postgres',

 

in which case the postgres.properties file would be found in $SQLSTREAM_HOME/plugin/jndi/my/poc/postgres.properties.

Note: $SQLSTREAM_HOME refers to the installation directory for s-Server, such as /opt/sqlstream/5.0.XXX/s-Server.

An example properties file that works with PostgreSQL would look as follows:

URI=jdbc:postgresql://localhost/nswrta_renderer_dev

DRIVER=org.postgresql.Driver

CONNPARAMPREFIX=dbConn_

dbConn_databaseName=mydatabase

dbConn_user=SQLstream

dbConn_password=mypassword

dbConn_applicationName=SQLstream TableReader Adapter  

Example Code to Reset the State Table

The following code is an example of how to reset the State Table.

-- reset TableReader state table
 

DELETE

   FROM "SQLS_TableReader_State"

   WHERE "SQLS_tableName" = 'reports_sqlstream_clv';

INSERT

   INTO "SQLS_TableReader_State"

       ("SQLS_tableName", "SQLS_highId", "SQLS_highTime")

   VALUES ('reports_sqlstream_clv', 38018908, NULL);
 

-- End reset.postgres.sql

Additional State Table Examples

MySQL examples

The example below will work as SQLS_TableReader_State declarations for MySQL:

CREATE TABLE 'SQLS_TableReader_State' (

'SQLS_tableName' varchar(30) PRIMARY KEY NOT NULL,

'SQLS_highId' bigint,

'SQLS_highTime' datetime,

CHECK ('SQLS_highId' IS NOT NULL OR 'SQLS_highTime' IS NOT NULL)

) COMMENT 'high-water marks for TableReader adapter';

 

--set initial high water mark using a timestamp(not mandatory)

INSERT INTO SQLS_TableReader_State VALUES ('my_table',null,'2014-01-01 00:00:01');

 

Note: 'SQLS_highTime' timestamp NULL can be used in place of  'SQLS_highTime' datetime.

Oracle Example

CREATE TABLE "SQLS_TableReader_State" (

"SQLS_tableName" varchar2(30) PRIMARY KEY NOT NULL,

"SQLS_highId" integer,

"SQLS_highTime" timestamp

);

COMMENT ON TABLE "SQLS_TableReader_State" IS 'high-water marks for TableReader adapter';

 
-- add constraint to ensure that at least one high-water mark is not null

 
ALTER TABLE "SQLS_TableReader_State"

ADD CONSTRAINT "CK_SQLS_TableReader_State"

CHECK ("SQLS_highId" IS NOT NULL OR "SQLS_highTime" IS NOT NULL);

 

--set initial high water mark using a timestamp(not mandatory)

INSERT INTO SQLS_TableReader_State VALUES ('my_table',null,'2014-01-01 00:00:01');

 

Paraccel Example

CREATE TABLE "test"."sqls_tablereader_state" (

"SQLS_tableName" varchar(30) PRIMARY KEY NOT NULL,

"SQLS_highId" integer,

"SQLS_highTime" datetime

);

-- add constraint to ensure that at least one high-water mark is not null

ALTER TABLE "test"."SQLS_TableReader_State"

ADD CONSTRAINT "CK_SQLS_TableReader_State"

CHECK ("SQLS_highId" IS NOT NULL OR "SQLS_highTime" IS NOT NULL);

 

--set initial high water mark using a timestamp(not mandatory)

INSERT INTO SQLS_TableReader_State VALUES ('my_table',null,'2014-01-01 00:00:01');

 
PostgreSQL Example

CREATE TABLE "SQLS_TableReader_State" (

"SQLS_tableName" varchar(30) PRIMARY KEY NOT NULL,

"SQLS_highId" integer,

"SQLS_highTime" timestamp

);

-- add constraint to ensure that at least one high-water mark is not null

ALTER TABLE "SQLS_TableReader_State"

ADD CONSTRAINT "CK_SQLS_TableReader_State"

CHECK ("SQLS_highId" IS NOT NULL OR "SQLS_highTime" IS NOT NULL);

 

--set initial high water mark using a timestamp(not mandatory)

INSERT INTO SQLS_TableReader_State VALUES ('my_table',null,'2014-01-01 00:00:01');

 

SQLserver Example

CREATE TABLE [SQLS_TableReader_State] (

[SQLS_tableName] nvarchar(30) PRIMARY KEY NOT NULL,

[SQLS_highId] integer,

[SQLS_highTime] datetime2

);

-- add constraint to ensure that at least one high-water mark is not null

ALTER TABLE [dbo].[SQLS_TableReader_State]

WITH CHECK ADD CONSTRAINT [CK_SQLS_TableReader_State]

CHECK ([SQLS_highId] IS NOT NULL OR [SQLS_highTime] IS NOT NULL)

;

--set initial high water mark using a timestampusing a timestamp(not mandatory)

INSERT INTO SQLS_TableReader_State VALUES ('my_table',null,'2014-01-01 00:00:01');