7. Named SQL and Stored Procedures
One of the primary goals of VBSF is to combine the strengths of object and relational technologies. SQL statements and stored procedures are relational tools that do not integrate well with the object model. However, both tools can provide ways to accomplish tasks that might otherwise be difficult or too slow to implement in the object model (e.g. a posting procedure that updates hundreds of rows in batch mode). For security reasons VBSF does not allow clients to execute arbitrary SQL in the database, but it does support the ability to define named custom SQL statements and stored procedures by defining them as Named Commands. Named commands are SQL statements or stored procedure calls that are assigned a name, so a client can refer to the command by name in order to execute it at runtime. This section explains how to define named commands. For information on how to invoke the commands at runtime see section 7. Executing Named SQL and Stored Procedures of the Programmer's Guide.
VBSF allows you to define the following types of commands:
OBJECT QUERY
Object queries return results sets that match the attribute structure of a persistent class, including the object ID. An object query is specified by entering a SQL SELECT statement and providing the fully qualified name of the associated persistent class. When specifying an object query, make sure to explicitly list the names of the columns in the SELECT clause in the same order as the attributes are defined in the corresponding class mappings. This is necessary because VBSF retrieves column values from a result set by index, which is faster than using the column names.
VALUE QUERY
Value queries are SQL SELECT queries that retrieve arbitrary values from the database not associated with a persistent class. A common case is when the value of a SQL aggregate function must be retrieved. Value queries are specified by entering a SQL SELECT statement.
UPDATE
Update statements perform data manipulation and data definition functions that do not return a result set. Update commands are specified by entering a SQL UPDATE, INSERT, DELETE, or DDL statement.
STORED PROCEDURE QUERY
Stored procedure queries are store procedures that return persistent objects. Stored procedures that return objects must be associated with business class and must return a result set matching its attribute structure, including the object ID. Stored procedure queries are specified by entering the full call syntax of the procedure.
STORED PROCEDURE
Stored procedure commands are stored procedures that perform computations or data manipulation functions. They are also specified by entering the full call syntax of the procedure.
Defining Named SQL Commands
To create a named SQL command click on the "SQL Commands" folder in the tree, and chose the Object->New menu option. When prompted, enter the name of the command and click OK. A SQL Command object is then created and added under "SQL Commands" folder.
To edit a SQL command select it in the tree so its corresponding form will be displayed on the right hand side of the split pane as shown below:
The following options may be set:
Command type. Select the type of command being defined. The valid types are: OBJECT QUERY, VALUE QUERY, UPDATE, STORED PROCEDURE QUERY, and STORED PROCEDURE.
Returns objects of type. If the command type is OBJECT QUERY or STORED PROCEDURE QUERY, then you must enter or select from the combo box drop down list the fully qualified name of the persistent class whose instances are returned by the query. Otherwise this field is disabled.
SQL Statement.
Enter a SQL statement or procedure call. Parameters must be specified using the standard marker notation for the database and driver in use (e.g. '?' for JDBC/ODBC). Procedure calls must be specified using the full call syntax. For example, the following syntax would be used under JDBC to invoke a stored procedure named raise_com that accepts two parameters:
{call raise_com(?,?)}