Retrieving Result Rows from an SQL Stored Procedure

Issue

How do you retrieve result rows from an SQL stored procedure?

 

Solution

The attached robots and SQL script can be used to demonstrate retrieving result rows from an SQL stored procedure with the Query Database step action. The simple stored procedure takes a single letter as an input and returns one result row containing the corresponding code word from the NATO phonetic alphabet (for example, the input ‘d’ returns ‘delta’). There are two robots included – one to populate the table used by the stored procedure, and another to demonstrate executing the stored procedure via the Query Database command.

IMPORTANT: The SQL query and attached robots have not been tested rigorously, and are not official supported Kapow or Kofax software releases. They are intended only as examples. No warranty or representation is made regarding their suitability for any purpose. If you choose to use them, it is at your own risk.

To walk through the instructions, you’ll need a working connection to a MySQL 5.5 database in Design Studio (it can be pushed out from Management Console or defined in Design Studio settings) and a mapping to that database in your current Design Studio project. The robots are configured to use a database connection / mapping named “mysql”, but that can be changed. The robot and type files were created using Kapow Design Studio version 9.3.9, but these techniques can be applied to older versions as well.

Step 1.
Create the table for the nato_phonetic.type file in the MySQL database.

Step 2.
Run NATOPhoneticAlphabet_CreateTable.robot in debug mode to extract the phonetic code word for each letter from a Wikipedia page and store it in the database table. (There’s a disabled Create Page step in the robot as a backup in case the Wikipedia page changes in the future.)

Step 3.
Execute the SQL query from phonetic.sql to create the stored procedure in your database. Make sure you store the procedure in the same schema used by the Design Studio connection. You can do this using a SQL client like MySQL Workbench, or you can even use an Execute SQL step in a new robot (not provided).

Step 4.
Now you’re ready to run SQLCallStoredProc.robot and see how a robot gets result rows from a stored procedure.

The Query Database step is used to retrieve one or more result rows produced by a SQL Query. In this case, only one row is produced, so the Query Database loop will have only one iteration.

The SQL Query expression is simply "CALL phonetic('a');" which executes the procedure with the input 'a'. The codeword output column is mapped into scratchPad.shortText1, and if you step through the robot in design mode, you can see “Alpha” as the output.

Depending on the SQL version the method for calling the stored procedure might be different. I.e. "EXEC phonetic @ltr='a' might be needed instead of CALL. Please use the call method verified in SQL Manager.

You can edit the SQL Query expression to try other letters. You can even add an input variable and pull the letter into the expression dynamically, such as from a Kapplet.

You may want to DROP the nato_phonetic table and phonetic procedure from your database after you’re finished.

Microsoft SQL Server:

If you are using Microsoft SQL Server, the sample zip file, MS_SQLS_Files.zip, provides a sample robot (v10.3.2.2) and type as well as a script to create a stored procedure ("sproc") to query Microsoft's AdventureWorks sample database. The sample demonstrates how to use an Execute Query step to call a sproc and store the result into a variable. You must configure a source to Microsoft's AdventureWorks database in Kapow / RPA and also obtain a copy of the database from Microsoft (link).

 

Level of Complexity 

Moderate

 

Applies to  

Product	Version	Build	Environment	Hardware
Kofax RPA	All

References

Add any references to other internal or external articles