IBM Support

Executing a stored procedure on the SQL server from a Rational Robot GUI script

Question & Answer


Question

How can I execute a stored procedure on the SQL server from a GUI script in IBM® Rational® Robot?

Answer

This assumes the stored procedure is already defined on the SQL server. For an example we will use a built-in stored procedure sp_help and the default sql sample database PUBS. The SQL syntax to execute the stored procedure should contain the "Exec" statement plus the procedure name.

Before calling a SQLEXEC query, a connection must be established with a data source using SQLOPEN. A successful call to SQLOPEN returns a unique connection ID number. SQLEXEC query uses that connection ID number to send SQL queries to the data source.

Any results generated from the query will not be returned immediately - SQLEXEC only executes the query. Retrieving results is handled by the functions SQLRETRIEVE and SQLRETRIEVETOFILE or Recordset objects to manipulate data in a database at the record level (see sample below).

Sample script:


Sub Main
   Dim Result As Integer

   'This procedure demonstrates how to instantiate the
   'ADO (Active Data Objects) Database engine which uses
   'ODBC to access a database

   'ODBC Error Handler for improper driver configuration
   'On Error Goto ODBCError
   
   Dim DBEngine As Object      'Database engine
   Dim Records  As Object      'The recordset returned by the Query
   Dim List     As String      'List of database records

   'Start the ADO Engine & select the ODBC Datasource to use
   Set DBEngine = CreateObject("ADODB.Connection")
   DBEngine.Open "Pubs_DSN"
   
   ' Disable ODBC Error Handler
   On Error Goto 0
   
   'Create a recordset object and run a query
   Set Records = CreateObject("ADODB.Recordset")  
Records.Open "exec sp_who", DBEngine  'executing a built-in stored
procedure           sp_help
   
   
   'Move to beginning of Recordset if there are records in the results set
   If Not (Records.EOF and Records.BOF) then Records.MoveFirst
   
   'Loop through all records and make a list of ...
   Do Until Records.EOF
       List = List & Records("loginame") & "   " &  Records
("dbname") & Chr(13)
       Records.MoveNext
   Loop    
   
   MsgBox List, ,"List of Datbase Records"
     
   'Close the database connection
   Set DBEngine = Nothing
   
   Exit Sub

ODBCError:    
   MsgBox "An error has occured.  The most probably cause of this " & _
       "error is an improperly configured ODBC data source.  Go " & _
       "to the Control Panel, and open the ODBC configuration " & _
       "dialog.  From there: " & Chr(13) & Chr(13) & _
       "- Select the System DSN Tab " & Chr(13) & _
       "- Select Add" & Chr(13) & _
       "- Select SQL Server Driver " & Chr(13) & _
       "- Select Finish" & Chr(13) & _
       "- For Data Source Name use: Pubs_DSN" & Chr(13) & _
       "- Select 'Select'" & Chr(13) & _
       "-Pubs database which is in the " & Chr(13) & _
       " List of DB's" & Chr(13) & _
       "- Select Ok" & Chr(13) & Chr(13) & _
       "Once complete, re-run this script",,"ODBC Configuration Error"
       
End Sub


Disclaimer

All source code and/or binaries attached to this document are referred to here as "the Program". IBM is not providing program services of any kind for the Program. IBM is providing the Program on an "AS IS" basis without warranty of any kind. IBM WILL NOT BE LIABLE FOR ANY ACTUAL, DIRECT, SPECIAL, INCIDENTAL, OR INDIRECT DAMAGES OR FOR ANY ECONOMIC CONSEQUENTIAL DAMAGES (INCLUDING LOST PROFITS OR SAVINGS), EVEN IF IBM, OR ITS RESELLER, HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.


[{"Product":{"code":"SSSHDX","label":"Rational Robot"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Scripting","Platform":[{"code":"PF033","label":"Windows"}],"Version":"2003.06.00;2003.06.10;2003.06.12;2003.06.13;2003.06.14;2003.06.15;2003.06.16;7.0;7.0.0.1;7.0.0.2;7.0.0.3;7.0.1;7.0.1.1;7.0.1.2;7.0.0.4;7.0.1.3;7.0.2","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Historical Number

110814655

Document Information

More support for:
Rational Robot

Software version:
2003.06.00, 2003.06.10, 2003.06.12, 2003.06.13, 2003.06.14, 2003.06.15, 2003.06.16, 7.0, 7.0.0.1, 7.0.0.2, 7.0.0.3, 7.0.1, 7.0.1.1, 7.0.1.2, 7.0.0.4, 7.0.1.3, 7.0.2

Operating system(s):
Windows

Document number:
74575

Modified date:
16 June 2018

UID

swg21122038