Running pre-selection and post-selection processing queries on the event source

The probe can perform SQL queries on the source data before and after it has been selected by the query specified by the SelectSqlFile property. You specify appropriate queries to run using the PreSqlFile and PostSqlFile properties, respectively.

To specify a query that the probe performs before selecting events from the data source, use the PreSqlFile property. To specify a query that the probe performs after selecting events from the data source, use the PostSqlFile property.

The pre-selection and post-selection queries can contain any of the following SQL statements:

INSERT
UPDATE
DELETE
INSERT INTO ... SELECT
CREATE TABLE ... SELECT
ALTER
TRUNCATE
DROP

Within the pre-selection or post-selection queries, you cannot use SELECT ... FROM ... statements that return a result set. But you can use INSERT into ... SELECT or SELECT INTO statements that select from, or query, an existing table and insert new rows into that table.

If either the PreSqlFile or PostSqlFile query fails, the probe will still run the SelectSqlFile query, but will also write a ProbeWatch message to the log file. If the SelectSqlFile query fails, the probe will not run the PostSqlFile query.

Note: The complexity of the queries that you can specify using the PreSqlFile or PostSqlFile properties depends on the database from which the probe is extracting data. For example, if you are connecting to MS SQL, you can specify files that contain multiple queries. However, if you are connecting to DB2, you can only specify files that contain a single query. If you are connecting to DB2 and you specify either a pre-selection file or post-selection file that contains multiple queries, the queries will fail and the probe will log the error.

Note also that the JDBC Probe only supports SQL files that have no more than 50,000 characters. If any of your SQL query files contains more than 50,000 characters, the probe will write the following message to the error log:

SQL file has exceeded the max characters limit..

Example 1: Performing an insert and a delete

The following example performs an insert and a delete:

The PreSQLFile query inserts an event with a SensorDataRowID of 8881 into the database table.

The SelectSQLFile query checks that the event has been created in the database table.

The PostSQLFile query deletes the event from the database table.

The query specified by PreSQLFile contains the following code:

SET IDENTITY_INSERT SensorData1 ON
INSERT INTO SensorData1
(SensorDataRowID,SensorDataID,AlertName,AlertDateTime,AlertID,SensorName,ProductID,
AlertTypeID,AlertPriority,AlertFlags,SensorAddressInt,SrcAddressInt,
VALUES (8881,8882,'TEST_INSERT','','','',1,2,3,4,1,2,3,4,5,'','','',1,'','','','',
'','','','',1,2,3,'',1,2,3,'','','','',1,'',1,2,3,4,1,2,3,4,5,'','')
SET IDENTITY_INSERT SensorData1 OFF

The query specified by SelectSQLFile contains the following code:

SELECT * FROM SensorData1 WHERE SensorDataRowID='8881'

The query specified by PostSQLFile contains the following code:

DELETE FROM SensorData1 WHERE SensorDataRowID='8881'

Example 2: Performing an update and then performing a second an update

The following example performs an update and then performs a second update:

The PreSQLFile query updates existing events that have AlertName set to TEST_UPDATE, to TEST_UPDATE_HAS_BEEN_UPDATED.

The SelectSQLFile query selects the total number of events that have been updated.

The PostSQLFile query updates value of the AlertName column of the updated events to FLUSHED_TO_JDBC_PROBE.

The query specified by PreSQLFile contains the following code:

UPDATE SensorData1 SET AlertName='TEST_UPDATE_HAS_BEEN_UPDATED'
WHERE AlertName='TEST_UPDATE'

The query specified by SelectSQLFile contains the following code:

SELECT COUNT(AlertName) AS TOTAL_UPDATED_ALERT FROM SensorData1 
WHERE AlertName='TEST_UPDATE_HAS_BEEN_UPDATED'

The query specified by PostSQLFile contains the following code:

UPDATE SensorData1 SET AlertName='FLUSHED_TO_JDBC_PROBE'
WHERE AlertName='TEST_UPDATE_HAS_BEEN_UPDATED'

Example 3: Creating a new table and deleting its contents

The following example creates a new database table, then deletes its contents:

The PreSQLFile query creates a simple database table.

The SelectSQLFile query checks that events are populated in the new table.

The PostSQLFile query clears all the data in the new table at the end of each resynchronization interval.

The query specified by PreSQLFile contains the following code:

CREATE TABLE new_table
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The query specified by SelectSQLFile contains the following code:

SELECT * FROM new_table

The query specified by PostSQLFile contains the following code:

DELETE FROM new_table

Example 4: Creating and dropping a new table

The following example creates a new database table, then drops it:

The PreSQLFile query creates a simple database table.

The SelectSQLFile query checks that events are populated in the new table.

The PostSQLFile query drops the new table at the end of each resynchronization interval.

The query specified by PreSQLFile contains the following code:

CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

The query specified by SelectSQLFile contains the following code:

SELECT * FROM Persons

The query specified by PostSQLFile contains the following code:

DROP TABLE Persons