Using a stored procedure to query a Microsoft Access database or an Oracle server requires special consideration because of specific elements incorporated in the design of these databases.
Microsoft Access Databases
Microsoft Access databases do not truly support stored procedures. However, they do support queries, which ADO treats in the same way as stored procedures. Currently, the MS Jet 3.51 and 4.0 OLE DB Providers cannot retrieve parameter information from Access queries. Therefore, when Stored Procedure is selected as the command type in the Record Source tab of the Data Control OLE Properties dialog, no parameters or mapped query properties appear for a selected Access query.
To get around this problem, you can try one of two solutions:
- Connect to the Microsoft Access database through an ODBC driver, rather than through the native MS Jet OLE DB Provider
- If you know the parameters defined in the Access query, you can enter the query as an SQL Select statement. In this example, ScrBatchSummary is the name of the Access query and Batch_Serial_No is an input parameter to the query:
Select * From ScrBatchSummary where ScrBatchSummary.batch_serial_no = QP1
Oracle Servers
Oracle stored procedures that Update, Insert, or Delete database information work as expected in VisiconX. However, stored procedures that retrieve, or Select, data from an Oracle server require special consideration. In order for an Oracle stored procedure to return a Recordset, you must create a package.
The Oracle package defines an array that is populated with the Recordset data returned to ADO and VisiconX. You cannot call this package using the "Stored Procedure" command type in VisiconX. You must call it as an SQL command. In this example, packperson is the name of the package, oneperson is the name of the stored procedure, resultset 2 tells the package the maximum number of rows to return, ssn, fname, and lname specify the columns to return, and QP1 is the VisiconX variable that controls the selection:
{call packperson.oneperson(QP1,{resultset 2, ssn, fname, lname})}
For details on how to create a package in Oracle that is able to return a Recordset to ADO and VisiconX, look at Article Q176086 in the Microsoft Support KnowledgeBase.
