Contents

DB Stored Procedure

This Database tool calls a stored procedure on your database server. This tool's properties allow you to bind handler variables/expressions to the stored procedure parameters, result columns, and return value. After this tool executes, you can use DB Fetch to fetch result rows (if necessary). Depending on which database server product you are using, the output parameter values and the return value will either be populated:

immediately on the call to DB Stored Procedure, or
if a result set is returned, output parameters and return value won’t be set until all rows have been fetched. This is for all databases.

Note: For stored procedures that return a result set, you must wait until after all of the rows in the result set are processed to check output parameters and return values. This is a standard ODBC requirement.

Consult with your database administrator to determine the behavior of your particular database server.

What is a stored procedure?

A stored procedure is a function or script that is saved on a database server. This script can be executed on the database server at the request of a database client. Stored procedures can receive input from the client at the time they're executed. Stored procedures carry out their processing on the server and can return data/results of that processing to the client. Database administrators use stored procedures to carry out complex functions that are best performed on the server (as opposed to the client). In addition, stored procedures often outperform normally submitted SQL because the SQL in the procedure is pre-parsed, and execution plans pre-generated.

A definition of a stored procedure specifies parameters, result set, and the return value. A stored procedure parameter is similar to the standard programming concept of a function parameter -- these parameters can be input data, meaning the value passed in is used during processing, or output data, meaning the reference to a value will be "filled in" during processing, or they can be both, meaning their initial value will be used during processing, and that value may then be changed before processing completes. Result sets are the same concept as for DB Query -- the stored procedure can execute a query during its processing, and the client will be able to access the result set of that query by calling fetch for each row in the result set. The stored procedure return value is similar to an output parameter -- it's a value that will be "arrived at" during processing and will be returned to the client.

An example of how this tool is used:

First, a stored procedure must be created. For MS SQL Server, this can be done using the SQL Server Query Analyzer: Open the Analyzer, type the Stored Procedure’s contents, and click on the Execute button (Green arrow). The stored procedure is created.

Here’s an example of a stored procedure created for testing purposes:

CREATE PROC makeatable

@lastcalltext char(20) output,

@totalrows int output

declare @lastcall datetime

drop table test1234

SELECT calldetail.* INTO test1234

FROM calldetail

WHERE (((calldetail.siteid)="101"));

UPDATE test1234 SET test1234.siteid = "991";

SELECT @lastcalltext = max (InitiatedDate) from test1234

SELECT @totalrows=COUNT(*)

FROM test1234

print @lastcalltext

print @totalrows

return @totalrows

Notes:

The Stored Procedure name is makeatable.
Makeatable is designed to work on the same SQL Server database CIC uses to store its call data (the database referred to by the CIC Server EIC_Tables ODBC DSN).
The stored procedure, as written, assumes that the CIC Server’s site identifier is 101 (to find out what the Site ID is for a given CIC Server, open Interaction Administrator, click on the Production container and double-click on Configuration item on the right. You can change the 101 in the example to whatever the site ID is for the CIC Server under test.
Makeatable takes 2 parameters: @lastcalltext, a string, and @totalrows, an integer. It needs to receive these 2 parameters on input, but doesn’t use them. It outputs the date and time of the last call logged in the database (@lastcalltext), and the amount of calls that server processed (@totalrows).

makeatable will delete a table called test1234 (without warning), if it exists, so make sure you don’t have any valuable data in such a table.

Before using a stored procedure in Interaction Designer, it’s a good idea to make sure it runs properly on the SQL Server. So, open the SQL Server Query Analyzer, type

makeatable "blah", 5

and click on the execute button. The output should display the number of rows affected (twice) followed by the date of the last call in the database, followed by some integer (number of calls, same as the number of rows affected).

If this worked, the test Stored Procedure can be used in Interaction Designer. Here’s how:

Create a new Handler in Interaction Designer. Use the SendCustomNotification initiator so that you can launch the handler from a command line.
Use two Assignment steps to initialize the 2 variables mentioned in step 4 of the previous list: A string variable, which will be bound to @lastcalltext, and an integer variable, which will be bound to @totalrows. Assign "blah" and 0 to the 2 variables, respectively (it really doesn’t matter what’s in there, as long as the data type is correct).

Note: You can also declare new variables directly from the DB Stored Procedure tool created in Step 5 of this procedure. After you click the Bind button, you can declare a new variable from the Bind dialog that appears.

Use a DBOpen step to open ODBC DNS eic_tables. Use a user ID of eic_user, and a password of i3 (unless the default password has been changed).
Use a DBGetConnection step.
In a DB Stored Procedure step, select the stored procedure. To do this, you'll pick the Data Source Name (EIC_Tables), Schema (that’s the procedure creator/owner, usually dbo), and the Stored Procedure (makeatable). If this works properly, @lastcalltext and @totalrows should automatically appear in the parameters window. Bind variables to these parameters to receive the values output from the stored procedure.

Note: If your parameters don't appear in the parameters window, you may need to manually modify CIC's definition of the stored procedures in the DB Stored Procedure Definition dialog.

Add any additional desired logic to the handler, save it, publish it, and activate it.

Note: If you batch publish a handler with this tool and use the /LogBatchPublish command lien argument, the publish process may display a publish dialog. See the Batch Publish topic for more information.

Start the handler from the command line using the sendcustomnotification command.

When the handler runs, your stored procedure is run on the DB server.

Parameters Page

DB Connection

The variable containing the database connection generated by the DB Get Connection tool.

DSN (Catalog =)

The name of the data source on which the procedure was created. The concept of the catalog varies from one RDBMS vendor to the next. It usually corresponds to the physical database, but there are exceptions. For example, with dBase & FoxPro it usually corresponds to the directory in which the data files reside. While some RDBMSs allow you to dynamically which catalog contexts within a connection, CIC currently doesn’t support this, and assumes the catalog is tied to the ODBC DSN. So, if access to multiple catalogs is required, you must create and configure multiple ODBC DSNs.

Schema

The schema in which the stored procedure resides. The "schema" is the official ANSI term for what used to be commonly known as the "qualifier". It is usually the creator/owner of the database object. Worth noting is that Sybase and MS SQL Server have a special schema name that is used when the owner of the object is also the database owner: "dbo".

Stored Procedure

The name of the stored procedure as it is stored on the DB server.

Note: You cannot change actual name as it's defined on the DB server from this field.

Runtime Stored Procedure

An optional expression that results in a string that names a stored procedure. This allows you to run a stored procedure dynamically from a handler. If you specify a value here, the any value in the Stored Procedure parameter is ignored.

Note: If specifying a value here, you are responsible for including any required schema prefix. For example, if the stored procedure is called ‘MyProc’, and it is owned by user ‘Fred’ who is not the database owner (dbo), and the connection was created under a user other than Fred, then you would need to provide ‘Fred.MyProc’ as the value here. As another example, if Fred was the dbo, then you could supply either ‘dbo.MyProc’ or simply ‘MyProc’, since Sybase and MS SQL server will automatically attempt to resolve any unqualified object name with ‘dbo’. As a final example, if the database connection was created using Fred’s account, then either ‘Fred.MyProc’ or ‘MyProc’ will work.

Execution String

Optional. Any executable SQL statement can be provided. If the statement contains embedded parameter markers (‘?’), they will be bound to the corresponding bind parameters that are specified (it is up to the user to make sure the number of parameter markers matches the number of bound parameters, and that the ordinal positions are correct). The intent is to allow a way to provide the RDBMS-specific stored procedure call syntax in the event that the ODBC driver doesn’t support the ODBC escape syntax for stored procedure execution. Fortunately, since most drivers nowadays support the escape syntax, you should not normally need to provide anything here.

Note: Although not originally intended as a feature, the execution string can be any valid SQL – it is not limited to a stored procedure call. For example, you could use it to perform a SELECT Count(*). You can pass input parameters and also get a result set; however, you cannot set output parameters or get a procedure return (since there is no procedure).

Timeout (seconds)

The number of seconds to wait for this operation to complete successfully before taking the failure path. Any value less than or equal to zero results in the default value of 60 seconds being used. You may specify a decimal value, such as 5.5.

Parameters list

The parameters for the specified stored procedure. You can bind variables to the input and input/output parameters. If you do not see all of the parameters that should be listed, your ODBC driver may not be passing all of the parameters to this tool. In this case you may need to define these parameters in the DB Stored Procedure Definition dialog (which you can open through the Utilities menu).

Note: In some cases you may have to take a couple of extra steps to convert values in a handler to parameters in the stored procedure. For example, you have a SMALLINT data type as an input parameter that is basically acting as a Boolean (i.e. it will either hold a 1 or a 0). When you open the expression editor to supply a value, the expression editor only displays the primary mapping type for SMALLINT, which is an CIC integer. This is because the expression editor is designed with the notion that everything is with respect to the current (single) data type. Unlike Interaction Designer, it has no way of presenting a list of both Boolean and integer variables. To work around this problem, define an integer variable to use, then set it with the Boolean value using one of the expression editor's type conversions.

Bind button

Click the Bind button to bind the selected parameter to a variable (for in/out and out parameters) or expression (for input parameters).

Unbind button

Click the Unbind button to unbind a parameter from a variable or expression.

Clear Bindings button

Click the Clear Bindings button to remove all bindings to all parameters.

Stored Procedure Return Value

A value returned as a result of the processing performed by the stored procedure. The type of value returned depends on the RDBMS. For example, Oracle and DB2 allow you to return most legal SQL data types (LONG or BLOB types usually being the exceptions), but Sybase and MS SQL Server both limit return types to INTEGERs. RDBMSs don't typically allow complex types, like lists, to be returned. The handler must include a condition to inspect the return value.

Result Set Page

This list is populated if the stored procedure fetches any data. You can bind variables to the fetched data. If you do not see all of the column names that should be listed, your ODBC driver may not be passing all of the column names to this tool. In this case you may need to define these column names and other result set properties in the DB Stored Procedure Definition dialog (which you can open through the Utilities menu).

Note: CIC does not currently support retrieving multiple result sets. If the stored procedure generates multiple result sets, then only the first result set can be retrieved.

Name

The name of the column from which the data is fetched.

Position

The ordinal position of the column in the result set, starting from 1.

Expression

This is the variable to which the fetched data is bound. You can specify only a variable, not an expression.

Bind… button

Click the Bind button to bind the selected column to a variable

Unbind button

Click the Unbind button to unbind a variable from a column.

Clear Bindings… button

Click the Clear Bindings button to remove all bindings to all columns.

Exit Paths

Success

This step takes the Success exit path if the ODBC call succeeds, regardless of the value that is returned by the stored procedure.

Failure

This step takes the Failure exit path if the ODBC call fails. Probable causes include:

Stored procedure not found. Usually this is due to a wrong or missing schema qualifier, or insufficient privileges.
Insufficient execution privileges.
Data type mismatch with one of the procedure parameters or return value.
To troubleshoot the cause of a failure, the most important thing is to examine the IPDBServer.log file – it will normally (well, hopefully) contain a useful RDBMS error message.