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
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.
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.
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.
(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. |