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.
In my experience, SPs have been
able to provide database functionality that could not be achieved any
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
is to set the code syntax properly. With that in mind, I have
a demo application at the bottom of this article. I will discuss
various types of Stored Procedures in dBASE using examples from this
There are two ways to setup a
Alias to access MS SQL Server. One is to use the native
driver, and the other is to use an ODBC-SQLServer driver. For
the purposes of this article I recommend the native BDE driver, but
one will work. Please see the related
article in this issue of the
dBulletin for further details about using these two kinds of BDE
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 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 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.
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.
This can be used in conjunction
with each of the above, but is most often used with input parameters
The most common purpose is to return a set of records with the range
by the input parameters.
CREATE PROCEDURE SP_BATCHTEST_INSERT
(@PARTNUM VARCHAR(20), @QTY INTEGER, @NEXTBATCH INTEGER OUTPUT)
SELECT @NEXTBATCH = (CASE WHEN MAX(BATCH) IS NULL THEN 1 ELSE MAX(BATCH) + 1 END)
INSERT INTO BATCHTEST
(BATCH, PARTNUM, QTY)
(@NEXTBATCH, @PARTNUM, @QTY)
Input parameters (noted in red
above) must specify the data type of the parameter. In the case
MS SQL Server, these variables must be preceded by the @ symbol.
Output parameters (noted in blue) work
like input parameters but they must specify the keyword OUTPUT in
same parameter could be declared as an INPUTOUTPUT parameter, which allows the
to both accept and return information. The returned value (noted
in green) must be specified with the
If a returned value is not specified, a zero is returned by default.
CREATE PROCEDURE SPU_GETBATCHBYPN
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
commands to a stored procedure, like BEGIN
That permits multiple Insert|Update|Delete statements
to happen together. If one fails, they all fail. The
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
blocked processes and freeze or break other applications sharing that
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.
There may be a box of widgets
a facility. The box needs an identification number, which I have
called a Batch number. The only pertinent details are the
Part Number for these items and the quantity that is still in the
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.sql := "SP_GETBATCH"
form.qMain.sql := "EXECUTE SP_GETBATCHBYPN :PARTNUM" // Requires EXECUTE keyword.
form.qMain.params["PARTNUM"] = form.etyPN_Filter.value
form.qMain.active := true
CATCH( Exception e )
if e.message.left(12).toUpperCase() == "SERVER ERROR"
msgbox(SQLMessage(), "Warning", 48)
msgbox(e.message, "Warning", 48)
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
The default exception class
in dBASE tends to truncate the last portion of long error
The SQLMessage() function
allows us to grab the full message for whatever the last SQLServer
was. I tend to use this kind of code for the global function I
that catches all errors in my dBASE apps.
if form.dbConnection.driverName.toUpperCase() == "SQL SERVER"
// BDE-ODBC Driver
// Works for MS SQL Native BDE Driver, not BDE-ODBC.
The Insert button definitely
more fun with SPs. Letís review the
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.
msgbox("New Batch: " + sp.params["Result"].value, "Return Parameter")
msgbox("New Batch: " + sp.params["@NEXTBATCH"].value, "Output Parameter")
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.
StoredProc and the
procedure will save the data you give it and send you back the Batch
that it automatically assigns. As specified in the related
in this issue of the dBulletin, the BDEís native MS SQL driver works a
differently from the ODBC SQL Server driver. One of the small
is that the ODBC driver gets an error if the Stored Procedure has
parameters or a return value defined. There is an alternative to
using output parameters and return values while still returning the
CREATE PROCEDURE SP_BATCHTEST_INSERT_ODBC
(@PARTNUM VARCHAR(20), @QTY INTEGER)
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)
(@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
In this case the SELECT statement
does not come from a table. It selects a local variable,
it to NEWBATCH.
This will be the field name that appears in the rowset that is
If there was more than one value to be returned, more fields could be
for this rowset.
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
In this case, however, the value is extracted from a regular query
instead of a StoredProc parameter.
The differences between the two MS SQL Server drivers is detailed
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
Hopefully these specific examples will allow other dBASE developers to
use Stored Procedures more easily.
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
architecture that separates the data access, business logic and
layers. It might come in the form of custom classes that
most major objects and capabilities in the database. It might
from Stored Procedures and Views stored on the database server
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.
(it is a 5 Kb zipped file)
|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.|