The SQL command invokes specialized routines that are stored inside a database.
A procedure is a set of SQL statements grouped together as an executable unit to perform a specific task. Stored procedures give the SQL language the ability to perform database-related application processing within the database itself - a capability normally associated with programming languages. The application processing capabilities include conditional execution, looping, block structure, named variables, and named procedures. Prior to the advent of relational database systems, databases were essentially used only for the storage and retrieval of data.
The SQL Stored Procedure Call in ECMap allows a map to issue a call to a stored procedure from within ECMap. However, not all databases support the entire range of options offered by ECMap. As a result, mappers must be aware of any limitations imposed by the specific ODBC database drivers that they are using. In addition, some databases limit the number of Select statements that are permitted. (In such a case, you would create rules to eliminate unnecessary Select statements.)
On the SQL Procedure Call window, you enter information about the stored procedure itself, the database in which it is stored, and the values passed into and out of the stored procedure.
Enter the following on the SQL Procedure Call window:
Procedure Name – the name that was given to the procedure when it was created and under which it is stored in the database.
User File Name – the name of the table that contains the data on which you are performing a Fetch After Procedure Call.
You create the association between the file name and the DSN on the New File or File Properties window. When you choose SQL DATABASE as the File Type, a Data Source Name text box appears. You enter the DSN in this text box and the program creates and displays the Driver Connect String in the text box at the bottom of the window.
User File Record – the name of the record associated with the cursor (or h statement) that points to a location in the database specifying the table in the database to be used. A stored procedure can return no value or it can return either a value or a specific location in the database. When it returns a location in the database, the program accesses the User File Record to find the cursor that points to the specific row in the database that was returned by the stored procedure.
You create the association between the file (containing the DSN pointing to the database) and the record (containing the cursor pointing to the location in the database) by dragging the record from the Records/Tables window and dropping it on the file on the Files/Databases window.
Record Field Parameters – in this section of the SQL Procedure Call window, indicate whether you pass parameters to the stored procedure, and if so, which fields in the record hold the parameters.
Select No Record Field Parameters – if no parameters are passed to the stored procedure.
Select All Fields in Rec Are Parameters – if all fields in the Parameter Record (specified in the following Parameters section) are passed as parameters to the stored procedure.
Select Marked Fields in Rec Are Parameters – if only specific fields in the Parameter Record (specified below) will be passed as parameters to the stored procedure.
When you define the fields in a record, you may mark a field as an SQL Stored Procedure Parameter on the Options tab of the New Field or Field Properties window.)
Parameters – in this section of the SQL Procedure Call window, enter information about data being passed into or out of the stored procedure.
Parameter Record – contains the data being passed into the stored procedure.
Return Memory Variable – contains the data that is returned by the stored procedure. The stored procedure can return either a value or a location. (If the data returned is a location, that location is specified by the cursor linked to the User File Record.)
Return SQL Type – indicates the type of value that is returned by the stored procedure. You can choose from the drop-down list of eighteen possible SQL types.
Stored Procedure Call – displays the SQL stored procedure call statement after it has been compiled.
Select the Compile Statement button to compile the SQL stored procedure call statement.
Select the Test Statement button to test whether you can connect properly to the ODBC database containing the stored procedure.
