SQL: Stored Procedures

Please review the introduction to OmniRush Stored Procedures and create the OmniRush example database before working with this section.

Example scripts are in the "/Examples/Sample Code/Open Database Connector/" directory on the OmniRush Server PC. Additional documentation on the sprocs can be found in the Open_StoredProcs.rtf in the same directory.

To test proper execution of either the sample scripts, or your customized live scripts, follow these steps:

Prerequisites:

All SP names MUST BE exactly the same as in the specification.
All field names and data types MUST BE exactly the same as in the specification. If you are not going to use some fields then just leave them blank, but don’t remove from SP header.
CustomField1 and CustomField2 MUST BE of “char” or “varchar” or “text (blob)” data type and COULD have any size.

Notes:

Stored procedures have different prefix for Oracle and ADO:

- ADO flavor expects “@” as a first character of each SP parameter

- Oracle flavor expects “s” as a first character of each SP parameter

Testing and debugging SP

We assume that you have Interactive SQL tool that allows you to execute any SP manually and see result set and output parameter values. (Note that the Query Analyzer that is part of MS SQL v7 does not support interactively calling sprocs that have output parameters.) Or you can use the OmniRush “OpenDBIO_debug.exe” utility. Following steps work with MS SQL, you might need to change some syntax for Oracle.

Create 2 new jobs (schedule activity) in your host database for 2 different tracks “TRACK1” and “TRACK2”. Then follow this sequence to make sure that your SPs really work the way OmniRush expects. These steps replicate the steps OmniRush takes when it works with the database.

“SELECT” zfQueryNew “TRACK1”, “”, 10

should return 1 record with 1 field “ActivityID”, that contains the first job ID ( JobID_1).

“SELECT” zfQueryNew “TRACK2”, “”, 10

should return 1 record with 1 field “AcitvityID” that contains the second job ID ( JobID_2).

“SELECT” zfQueryNew “TRACK3”, “”, 10

should return 0 records.

“EXECUTE” zfGetActivityRecord JobID_1

(where JobID_1 is ID of the first job) should return at least :

@ContactID = Valid contact ID (ContID)

@TrackRef = ‘TRACK1’

@Reference = Valid reference line

“EXECUTE” zfGetContactRecord ContID

(where ContID is “Valid contact ID” from zfGetActivityRecord call) should return at least:

@FirstName

@LastName

@Phone

@Fax

@Email

@Address1

@Address2

@City

@State

@ZIP

“EXECUTE” zfFlagActivityRecord JobID_1, “~TRACK1”, “MS0000000000001”

Function doesn’t have output parameters but should update job (activity) record in host database.

“SELECT” zfQueryNew “TRACK1”, “”, 10

should return 0 records

“SELECT” zfQueryFlagged “~TRACK1”, 10

should return 1 record with 1 field “ActivityID”, that contains the first job ID ( JobID_1).

“EXECUTE” zfGetActivityOwner JobID_1

should return “MS0000000000001”

“EXECUTE” zfForwardActivityRecord JobID_1, ContID, @TrackRef = ‘TRACK1’, (set other parameters to your taste)

This function doesn’t have output parameters but should update the job (activity) record in the host database. Make sure that this procedure clears “ModuleID” field in the activity record (see SP specification).

“SELECT” zfQueryFlagged “~TRACK1”, 10

should return 0 records

“SELECT” zfQueryNew “TRACK1”, “”, 10

should return 1 record with 1 field “ActivityID”, that contains the first job ID ( JobID_1).

“EXECUTE” zfCreateHistoryRecord (set parameters to your taste).

Function should create a valid history record in your host database.

“EXECUTE” zfDeleteActivityRecord JobID_1

Function should delete activity 1 from your host database.