Pathname support for stored procedures

If your application includes any stored procedures, you should include a procedure named SQLScriptPath in your application. If any of your stored procedures are stored in a database other than your application’s default database, you must include this procedure in your dictionary. The SQLScriptPath procedure adds pathname information to the stored procedure name.

The call sproc statement passes a stored procedure name to the SQLScriptPath procedure, which then adds the appropriate pathname information to the beginning of the stored procedure name. This script ensures that the call sproc statement sends the command to execute the stored procedure to the proper location. If this script isn’t included in your application, Dexterity will search only the default database for all called stored procedures.

The SQLScriptPath procedure is much like the SQLPath procedure. It has similar syntax, and adds pathname information to the beginning of a resource name. However, this procedure is used to add that information to the names of stored procedures.

SQLScriptPath procedure parameters

The SQLScriptPath procedure uses four parameters; three in parameters and one out parameter. The out parameter specifies the pathname added to the stored procedure name.

• product_ID – An integer in parameter that contains the ID of the dictionary associated with the stored procedure.
• core_ID – An integer in parameter that contains the ID of the core associated with the stored procedure’s prototype procedure. This is the core selected in the Procedures window and passed in when your applcation is running in test mode or with the runtime engine.

The cores and their corresponding integer values are listed in the following table:

• procedure_ID – An integer in parameter that contains the resource ID of the stored procedure’s prototype procedure.
• data_path – A string out parameter that specifies the location of the stored procedure. Use it to return a pathname in the following format, which is added to the beginning of the stored procedure name. Because the server name and owner name portions of the path are optional, they are shown in braces:

{:server_name:}database_name{/owner_name}/

Example

This example shows a SQLScriptPath procedure written for use in an application that accesses a data source in which the stored procedures for all databases associated with the data source are located in a single database, StoredProcs, created by user JWILCOX.

in integer product_ID;
in integer core_ID;
in integer procedure_ID;
out string data_path;

data_path = "StoredProcs/JWILCOX/";

If the owner name weren’t included in this script, Dexterity would have used the current SQL user ID as the default owner name.

Documentation Feedback

---