Stored Procedures and dBASE
by Jamie A. Grant, AV-Base Systems, Inc.


Relational Database Diagram and Hedge Maze: Look Familiar?
A  DATABASE is like a hedge maze.  A hedge maze can have more than one entrance, and it has numerous routes that can be taken.  If youíre with a group of people, you might end up losing someone along the way if youíre not careful.  You might want to pack a lunch before you start in case you canít find your way out.

In the same way, a database can have many entrances.  The same data can be accessed using different programming languages, database engines, SQL statements, views or stored procedures.  Once in, there are many different ways to update or extract data.  As with the hedge maze, caution is needed or else an important piece of information may be lost along the way.  As with a large hedge maze, a database without a map might require packing a lunch to get through it, if you get lost.

There are plenty of ways to make sure a database doesnít seem like a maze.  Stored Procedures in relational databases can be one of these methods, providing key routes through a database.  Stored Procedures can be thought of as a function that is stored within the database structure itself.  Tell the function the starting point of your maze, and it gets everything to the other side.

Stored Procedures (SPs) have several advantages.

  1. Standard Database Interface: Different applications and programming languages will get consistent results from an SP.
  2. Easier Updates: As a standard interface, itís easier change the SP logic as needed for database changes, without needing to update every reference to the updated table or field.  (Custom classes, database views and n-tier architecture help provide this same advantage.)
  3. Data Integrity: SPs help verify the input, and can make sure every piece of data is always extracted and stored properly.  Transactions within a stored procedure can ensure that consecutive sequences of changes happen together or not at all.
  4. Faster Execution: SPs are stored in the database itself.  The SQL statement does not need to be translated from an incoming SQL statement, itís ready for immediate use.
This particular article is going to focus on the use of Stored Procedures for relational databases, with examples for MS SQL Server.  (Most examples that I provide below should work with slight syntax differences for any other database server.)

In my experience, SPs have been able to provide database functionality that could not be achieved any other way.  Iíve also found them useful when two different applications share the same database, as with the dBASE and Java products line that I help develop.  The main trick with using stored procedures in dBASE is to set the code syntax properly.  With that in mind, I have provided a demo application at the bottom of this article.  I will discuss various types of Stored Procedures in dBASE using examples from this demo.
 
 
  1. BDE Connection
  2. Creating Stored Procedures
  3. Using Stored Procedures in dBASE 
  4. Conclusion
 

BDE Connection

There are two ways to setup a BDE Alias to access MS SQL Server.  One is to use the native BDE-SQLServer driver, and the other is to use an ODBC-SQLServer driver.  For the purposes of this article I recommend the native BDE driver, but either one will work.  Please see the related article in this issue of the dBulletin for further details about using these two kinds of BDE drivers.

Creating Stored Procedures

Stored Procedures can be used for most kinds of SQL statements: Select, Insert, Update, Delete, etc.  Designing them boils down to how the Stored Procedures will be used.

Input Parameters

Input parameters are entirely optional, and one or more parameters can be used.  The main restriction on parameters is that the TEXT data type cannot be used for MS SQL Server, so memos canít be passed in directly.

Output Parameters

Output parameters are entirely optional, and one or more parameters can be used.   Output parameters should be used if several different kinds of data need to be returned once the SP has completed.

Return Value

One return value is always returned from an SP, regardless of whether it is specified in the SPís SQL statement.  By default it returns zero, but this return value is ideal if only one item needs to be returned.  This return value can be used to indicate if the SP finished successfully or not, often as an integer of -1 or 1.

Rowset Result

This can be used in conjunction with each of the above, but is most often used with input parameters only.  The most common purpose is to return a set of records with the range specified by the input parameters.
 
 
CREATE PROCEDURE SP_BATCHTEST_INSERT
(@PARTNUM VARCHAR(20), @QTY INTEGER, @NEXTBATCH INTEGER OUTPUT)
AS
SELECT @NEXTBATCH = (CASE WHEN MAX(BATCH) IS NULL THEN 1 ELSE MAX(BATCH) + 1 END)
FROM BATCHTEST
INSERT INTO BATCHTEST
(BATCH, PARTNUM, QTY)
VALUES
(@NEXTBATCH, @PARTNUM, @QTY)
RETURN @NEXTBATCH
   

Input parameters (noted in red above) must specify the data type of the parameter.  In the case of MS SQL Server, these variables must be preceded by the @ symbol.  Output parameters (noted in blue) work much like input parameters but they must specify the keyword OUTPUT in their declaration.  The same parameter could be declared as an INPUTOUTPUT parameter, which allows the variable to both accept and return information.  The returned value (noted in green) must be specified with the Return keyword.  If a returned value is not specified, a zero is returned by default.
 
 
CREATE PROCEDURE SPU_GETBATCHBYPN
@PARTNUM VARCHAR(20)
AS
SELECT BATCH, PARTNUM, QTY FROM BATCHTEST
WHERE PARTNUM = @PARTNUM
ORDER BY BATCH
   

The above example returns a rowset with three columns.  This SP could be written without the input parameter, and output parameters and a return value could be specified as well.

Tip: You can add more than one SQL statement to a stored procedure.

Tip: You can add transaction-level commands to a stored procedure, like BEGIN TRANSACTION.  That permits multiple Insert|Update|Delete statements to happen together.  If one fails, they all fail.  The warning for this kind of SP is that transactions that take too long to run have a tendency to lock other users out of records or tables, which could create blocked processes and freeze or break other applications sharing that database.

Using Stored Procedures in DBASE

A connection to a SQL Server is required to use the demo app for this article.  Start the program Startup.prg and choose the BDE Alias that you want to use.  The BDE Alias and the related UserID and password that you use should have Create/Drop ownership privileges on your SQL Server.  Once a database connection has been created, the application will automatically create a sample table called BATCHTEST, along with several related Stored Procedures.

The premise for this demo app is fairly simple but it demonstrates several things.

  1. How to create and drop stored procedures.
  2. How to use a dBASE Query for an SP that has no parameters and returns a rowset.
  3. How to use a dBASE Query for an SP that has input parameters and returns a rowset.
  4. How to use a dBASE StoredProc for an SP that has input parameters and returns either an output parameter or a return value.

There may be a box of widgets in a facility.  The box needs an identification number, which I have called a Batch number.  The only pertinent details are the original Part Number for these items and the quantity that is still in the box.  The Batch number is a consecutive number that is created automatically but it is not an AutoIncrement field, itís just an Integer field.  The Part Number and Quantity can be any value at all, but we might want to see all of the Batches related to a specific Part Number.
 
 
form.qMain.params.removeAll()
if empty(form.etyPN_Filter.value)
   form.qMain.sql := "SP_GETBATCH"
else
   form.qMain.sql := "EXECUTE SP_GETBATCHBYPN :PARTNUM" // Requires EXECUTE keyword.
   form.qMain.params["PARTNUM"] = form.etyPN_Filter.value
endif
TRY
   form.qMain.active := true
   form.qMain.rowset.first()
CATCH( Exception e )
   if e.message.left(12).toUpperCase() == "SERVER ERROR"
      msgbox(SQLMessage(), "Warning", 48)
   else
      msgbox(e.message, "Warning", 48)
   endif
   return false
ENDTRY
   

The code to load the grid calls two different stored procedures.  The first stored procedure is named SP_GETBATCH() and gets every record from the test table.  The second stored procedure SP_GETBATCHBYPN() is called if there is a Part Number filter that needs to be used.  The syntax of the sql property is similar to the syntax you use for normal query.params[] except that it requires the Execute keyword as well.  The StoredProc class in dBASE is also capable of returning a rowset, but you might have difficulty if you use this rowset as a datalink.

The syntax for this code isnít too tricky, at least it isnít with a good example.  The only other part to note in this section is how the code is handling potential errors.  The default exception class in dBASE tends to truncate the last portion of long error messages.  The SQLMessage() function allows us to grab the full message for whatever the last SQLServer error was.  I tend to use this kind of code for the global function I use that catches all errors in my dBASE apps.
 
 
if form.dbConnection.driverName.toUpperCase() == "SQL SERVER" 
   // BDE-ODBC Driver
   class::Insert_ODBCWorkAround() 
else 
   // Works for MS SQL Native BDE Driver, not BDE-ODBC. 
   class::Insert_WithParams() 
endif
   

The Insert button definitely has more fun with SPs.  Letís review the Insert_WithParams() function first.
 
 
Function Insert_WithParams 
   sp = new storedProc() 
   sp.database := form.dbConnection 
   sp.procedureName := "SP_BATCHTEST_INSERT" 
   sp.params["@PARTNUM"].value  := form.etyPN_Insert.value 
   sp.params["@QTY"].value      := form.spnQty_Insert.value 
   sp.params["@NEXTBATCH"].type := 1  // Output. This param defaults to Input,
                                      // which returns nothing. 
   sp.execute() 
   if ShowReturnValue 
      msgbox("New Batch: " + sp.params["Result"].value, "Return Parameter") 
   else 
      msgbox("New Batch: " + sp.params["@NEXTBATCH"].value, "Output Parameter") 
   endif
   

When the procedureName property is set, it runs an automatic query against the SQL Server and figures out what the parameters should be for you SP, preparing the params[] associative array automatically.  Itís easy enough to fill in the input parameters, as this code does for the @PARTNUM and @QTY parameters.  The only tricky part is the output parameter of @NEXTBATCH, which is created as an input parameter by default even though itís actually an output parameter.  To correct that, you just need to change the type property of your parameter to Output.  (It can be set to input, inputout, output and result.)

Even if this SP did not have a return value specifically stated, the StoredProc class would still create a Result parameter automatically.  This kind of parameter is set to the proper type automatically, the result type.  With an ODBC driver, this property will default to display a parameter name of RETURN_VALUE rather than Result.

Run this StoredProc and the SP_BATCHTEST_INSERT stored procedure will save the data you give it and send you back the Batch number that it automatically assigns.  As specified in the related article in this issue of the dBulletin, the BDEís native MS SQL driver works a little differently from the ODBC SQL Server driver.  One of the small differences is that the ODBC driver gets an error if the Stored Procedure has output parameters or a return value defined.  There is an alternative to using output parameters and return values while still returning the necessary results.
 
 
CREATE PROCEDURE SP_BATCHTEST_INSERT_ODBC 
(@PARTNUM VARCHAR(20), @QTY INTEGER) 
AS 
DECLARE @NEXTBATCH AS INTEGER 
SELECT @NEXTBATCH = (CASE WHEN MAX(BATCH) IS NULL THEN 1 ELSE MAX(BATCH) + 1 END) FROM BATCHTEST 
INSERT INTO BATCHTEST 
(BATCH, PARTNUM, QTY) 
VALUES 
(@NEXTBATCH, @PARTNUM, @QTY) 
SELECT @NEXTBATCH AS NEWBATCH
   

The Stored Procedure has to be designed in a different way.  No output parameter or return value is defined, but it does have a SELECT statement at the end.  In this case the SELECT statement does not come from a table.  It selects a local variable, @NEXTBATCH, and renames it to NEWBATCH.  This will be the field name that appears in the rowset that is returned.  If there was more than one value to be returned, more fields could be defined for this rowset.
 
 
Function Insert_ODBCWorkAround 
   q = new query() 
   q.database := form.dbConnection 
   q.requestLive := false 
   q.sql := "EXECUTE SP_BATCHTEST_INSERT_ODBC :PARTNUM, :QTY" 
   q.params["PARTNUM"] = form.etyPN_Insert.value 
   q.params["QTY"]     =  form.spnQty_Insert.value 
   q.active := true 
   msgbox("New Batch: " + q.rowset.fields["NEWBATCH"].value, "ODBC Work-Around")
   

As with the previous example, a messagebox is still given to indicate the new batch that has been created.  In this case, however, the value is extracted from a regular query rowset instead of a StoredProc parameter.  The differences between the two MS SQL Server drivers is detailed further in the article about MS SQL Server and BDE Setup, but the ODBC driver will work as expected in most cases.  And when it doesnít, there are creative alternatives available.  Hopefully these specific examples will allow other dBASE developers to use Stored Procedures more easily.

Conclusion

Stored Procedures are useful for almost any kind of SQL statement.  Some database administrators, particularly on significant Oracle installations, do not permit any direct access to tables.  For reasons of security and data integrity, some mission-critical databases can only be accessed through Views and Stored Procedures.  At the other extreme, no stored procedures are used at all and every SQL statement is embedded inside a dBASE .wfm file.

The key is to figure out a good map for your local hedge maze.  It might come in the form of multi-tiered architecture that separates the data access, business logic and interface layers.  It might come in the form of custom classes that encapsulate most major objects and capabilities in the database.  It might come from Stored Procedures and Views stored on the database server itself.  I recommend that Stored Procedures be applied as one of a useful array of tools.  Put it into your toolbox and youíll be surprised at how useful it can become.

To download the included application, click here
(it is a 5 Kb zipped file)

AV-Base Systems Jamie A. Grant has been a dBASE programmer with AV-Base Systems, Inc. since 1999.  AV-Base Systems has been providing Aviation Maintenance Management Software to the global aviation industry for the past twenty years.  Thanks to AV-Base Systems for their continued support of the dBASE community and their key contribution to this article.