*-----Visual dBASE C/S--------------------------by Ray Kiuchi------ Ed: Ray Kiuchi is on the Visual dBASE development team. His specialty is interfacing VdB to the Borland Database Engine. This is a two-part article (the second part will appear in the next issue of the VdDN Newsletter) on using Client/Server Stored Procedures - code that sits on and gets executed from the server. One advantage of Stored Procedures is that they are front-end language independent - all your VdB programs can use them, as can any other program (regardless of its language) that supports access to Stored Procedures. Using Stored Procedures Part I Visual dBASE provides an easy and familiar mechanism for using database server stored procedures. If you are familiar with the EXTERN command for declaring functions in a DLL, you already know how to declare stored procedures. Stored procedures are similiar to DLL functions in that they exist outside the scope of Vdb. Execution of stored procedures is done at the database server. The often-quoted advantages of database stored procedures: (1) Performance - SQL statements are already prepared. Instead of several pass-thru statements, you can make only one call to the server. (2) Centralized procedural business rules - Located at the server not in application code. (3) Security - App code could potentially never access a table if procedures do everything. Some of the disadvantages: (1) Yet another language - Usually a pseudo-Pascal. (2) Performance - Keep in mind that it is occuring at the server. From the online help, here is the EXTERN SQL syntax: EXTERN SQL [] ( []) [] [ FROM ] Remember that stored procedures do not return values like functions. They return values thru one of the parameters. In Vdb you use CPTR, CPTROUT to identify parameters that the stored procedure modifies. The difference is that CPTR identifies parameters that the stored procedure modifies but also uses as an input value (input and output). CPTROUT identifies a parameter that is modified but not used as a input value ( output only ). Following is an example assuming you have Visual dBASE Client/Server Local Interbase installed. In the sample iblocal database there is a stored procedure called Dept_Budget. Here are the parameters for Dept_Budget: DNO INPUT CHAR(3) TOT OUTPUT NUMERIC(15,2) The EXTERN SQL equivalent parameter types for these two are CSTRING and CPTROUT. So to declare the Dept_Budget procedure in Vdb: OPEN DATABASE iblocal SET DATABASE TO iblocal EXTERN SQL dept_budget( CSTRING, CPTROUT ) The first parameter of the dept_budget stored procedure takes a string value that identifies a dept number. The second parameter identifies the variable to store a numeric value for the retrieved budget. After the EXTERN SQL command it is now declared like any DLL function and can be executed. To execute dept_budget: bud = 0 dept_budget( "670", bud ) * The variable bud now contains the retrieved budget. Next installment: Details *-----Visual dBASE C/S--------------------------by Ray Kiuchi------ Ed: Ray Kiuchi is on the Visual dBASE development team. His specialty is interfacing VdB to the Borland Database Engine. This is the second installment of a two-part article. Using Stored Procedures Part II - Advanced Details The first installment of this article outlined the steps in declaring and executing stored procedures in Visual dBASE. Part two discusses more details about using stored procedures. EXTERN SQL ... FROM... The FROM option specifies the actual name of a stored procedure. It is necessary if the name of a stored procedure conflicts with Visual dBASE naming conventions or with another EXTERN function previously declared. The alias name is put in place of the actual procedure name for declaration. For example, to declare the sample stored procedure "Dept_Budget" as the alias name "GetBudget" : EXTERN SQL GetBudget( CSTRING, CPTROUT ) FROM "Dept_Budget" When GetBudget() is executed, the actual procedure name "Dept_Budget" is communicated to the server. This option becomes more useful for Oracle servers because Oracle allows overloaded stored procedures. Overloading a procedure name means that the same name is used for more than one procedure. The parameter list of each procedure must be different. Visual dBASE can only differentiate between overloaded procedures by using alias names. Stored Functions... In general, a procedure does not return values whereas functions do. Oracle servers have stored functions that can also be declared using EXTERN SQL. For example, if you have an Oracle stored function called GetOrdersShipped that returns an integer value, the following code declares and executes the stored function: EXTERN SQL CINT GetOrdersShipped( CSTRING ) IF GetOrdersShipped( "Visual dBASE C/S" ) > 10000 UPDATE stock SET ... ENDIF Stored functions can be used like any built-in function and included in expressions. AUTOEXTERN Visual dBASE has the capability to automatically declare all stored procedures when opening a database. The AUTOEXTERN option of the OPEN DATABASE command accomplishes this. Following is an example that declares all the stored procedures in the "Iblocal" database : OPEN DATABASE Iblocal AUTOEXTERN AUTOEXTERN is not available on all servers. AUTOEXTERN requires that the server's system tables contain enough information so Visual dBASE can internally build and execute an EXTERN SQL command ( Interbaseand Oracle support this ). Known Problems... Similar to the pointer and invalid memory problems that can arise when calling external DLLs, EXTERN SQL can get you into trouble too. In 5.5 there is a crash problem with expecting a returned character value. If you do not get the return value size correct, memory can be overwritten and a crash is likely. There is no problem with passing in character values. SQLEXEC()... With the SQLEXEC() function SQL statements are sent directly to the server (pass-through). Stored procedures can also be executed using SQLEXEC(). Here are some examples of the different syntax required to execute a stored procedure with SQLEXEC() : Oracle: SQLEXEC(" BEGIN ( ); END;") Interbase: SQLEXEC("EXECUTE PROCEDURE ()") ODBC: SQLEXEC(" { CALL ( ) } ") Instead of using SQLEXEC(), use EXTERN SQL to shield you from the peculiarities of each server's syntax.