Calling a Stored Procedure from QlikView Expressor 3
Originally published on 07-21-2011 03:33 AM
Although Expressor 3 does not have operators that you can configure to call a stored procedure, you can easily use the Expressor Datascript SQL Module to accomplish this task from the Read Custom or Write Custom operators or any operator in the Transformers group. The coding is very simple as all of the work is being done by the stored procedure.
To begin, on the computer running Expressor Studio or the Expressor Data Integration Engine, create an ODBC DSN for the database. It is critical that you open the Windows ODBC Administrator from the link in the Start > All Programs > expressor > expressor3 > system tools menu item as this ensures that the correct version of the administrator is used. If there is an Expressor ODBC driver for your database (those drivers named expressor-3-…, where … is the name of the database management system), use this driver when creating the ODBC DSN.
As an example, let’s use a collection of stored procedures against a Microsoft SQL Server table that contains a listing of the presidents of the United States.
The first stored procedure, which would typically be used from the Read Custom operator, although you might use it within an operator in the Transformers grouping to load a local lookup table, returns a result set containing the entire contents of the table.
USE [demo] GO
/****** Object: StoredProcedure [dbo].[getPresidents] ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[getPresidents] AS SELECT place, fname, lname, party FROM dbo.presidents GO
A second stored procedure, includes a WHERE clause, although it might return a result set with one or more rows. You might use this stored procedure from a Transform operator, using a value from the incoming record to formulate the WHERE clause.
USE [demo] GO
/****** Object: StoredProcedure [dbo].[getPresident] ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON
GO CREATE PROCEDURE [dbo].[getPresident] @place int AS SELECT fname, lname FROM dbo.presidents
WHERE place=@place GO
And a third stored procedure inserts a row into the table. This procedure might be used from the Write Custom operator although it could also be used from a Transform operator.
USE [demo] GO
/****** Object: StoredProcedure [dbo].[insertPresident] ******/ SET ANSI_NULLS ON GO
AS INSERT INTO dbo.presidents (place,lname,fname,party) VALUES (@place,@lname,@fname,@party) GO
Usingthe first stored procedure from the Read Custom operator is nearly identical to executing a SELECT statement from within this operator. The only difference is that the statement is a call to the stored procedure rather than a SELECT statement.
Note that it is your responsibility to define the composite type that describes the record corresponding to each row in the result set and then use the read function to initialize each record emitted by the operator.
When you use the second strored procedure, perhaps from within a Transform operator, you must be prepared to handle a result set with one or more rows. Consequently, you coding will need to use the iterator function in order to emit multiple records if necessary. But invoking the stored procedure is very easy, simply supply whichever attributes from the incoming record are needed by the stored procedure as part of the command to be executed.
Note that in this example, all of the values in each result set row are used to initialize the attribute in the emitted record, but that is not a requirement. Your code could only use a subset of the values returned by the stored procedure.
Finally, using the third stored procedure to write new rows to the table is only slightly more involved as you must pass values extracted from the incoming record to the stored procedure. Note how lines 13-15 integrate these values into the invocation statement.
Unlike the first two procedures, where the stored procedure returns a cursor that you use to iteratre over the result set, in this use case the stored procedure returns the number of rows affected. Your code should confirm that this return value is appropriate to your processing logic before you set the return value for the write function. In the above example, the coding confirms that the return value is not null, which indicates success and then it records the number of affected rows (which in this example will always be one) in the log.
If you write a stored procedure that deletes rows from the table, the stored procedure will also return the number of rows deleted rather than an iterator.