IBPhoenix Development

Using Server Specific Features in Delphi

By Lance C. Bullock, Borland Developers Conference 1995

Overview

Client/Server computing introduces the concept of "division of labor" between a front-end application and a back-end relational database server. In order for this concept to work optimally, the front-end must be able to access a wide variety of server-specific features as well as communicate effectively what operations our application would like the server to perform. In this presentation we will look at some of these "server-specific features" and see how they are accessed through Delphi Client/Server.

Configuring a BDE Alias

The first step needed in order for Delphi to communicate with a server, is to set up the drivers and create an alias using the BDE (Borland Database Engine) Configuration Utility.

FIGURE 1 - The BDE Configuration Utility

Figure 1 shows the Drivers page of the BDE Configuration Utility. This is where you will set certain global options which will tell Delphi how to connect and talk to your server. The Alias page of the BDE Configuration Utility looks very similar, it is where you define a specific connection to your server. You may notice that the Alias page has many of the same settings as the driver page, this is because you will probably set up many aliases per driver and when you specify a new alias for a particular driver, the driver settings act as a template and will be filled into the alias settings for you. The settings can then be changed in the alias to provide specific behavior for each server connection.

BDE Configuration - The SQLPASSTHRU Mode setting

One of the most important BDE Configuration settings is SQLPASSTHRU Mode. The value of this setting will later determine how Delphi communicates to the back-end server through the Borland Database Engine. The SQLPASSTHRU Mode has three possible values which are detailed below :

NOT SHARED

Pass-thru SQL and non pass-thru SQL (SQL generated by BDE) will each use a different connection to the server. Thus not conflicting with each others transactions.

SHARED NOAUTOCOMMIT

Pass-thru SQL and non pass-thru SQL will share a connection, but pass-thru SQL will not be committed after each statement.

SHARED AUTOCOMMIT

Pass-thru SQL and non pass-thru SQL will share a connection, and pass-thru SQL will be committed after each statement unless wrapped in a client transaction (Delphi starttransaction..commit/rollback block).

The Delphi TDatabase Component

The next step in our eventual connection from Delphi to our server data is configuring the TDatabase component. The TDatabase component provides a shared connection for all of our tables and queries which connect to that particular database. Following are some of the key properties of the TDatabase component :

FIGURE 2 - The TDatabase component editor

The TDatabase component editor (see Figure 2) allows you to alter many of the database connection properties at once. Click the Defaults button to fill the params array property with the settings taken from either the BDE Driver or Alias. These parameters can then be changed or read at design-time through the use of the params property editor or at run time by accessing the params array directly. For example, to supply a new password at runtime (given the password is the ninth item in the zero based params array) :

database1.connected := false ; 
	database1.params[8] := 'mynewpassword' ; 
	database1.connected := true ;

We have now looked at the steps required, and some of the options we have, to allow Delphi to access our server data. Having accomplished this, we may now move on to see how Delphi Client/Server can utilize features which are specific to your particular relational database server.

Using the TQuery Component with pass-thru (server specific) SQL

We will now explore one of the most powerful Client/Server tools within Delphi, the TQuery component. Many developers think of the TQuery component as useful primarily for querying or retrieving data. While this is true, it is the purpose of this discussion to point out the a TQuery component can be used to send ANY SQL statement that your server will support !

Query.open vs. Query.execSQL

In order to fully exploit the power of the TQuery object, we must be comfortable with the two methods which cause the query SQL to be sent to the server. The open method is for queries which return a cursor, or a result set. An example of a SQL statement which requires the open method to execute the query would be :

select * from employee where emp_no = 12 

The execSQL method is used for queries which do not return a cursor or result set. When setting the active property of a query to true, this is the same as specifying the open method, this means that execSQL and queries which do not return a result set cannot be executed at design time. The execSQL method must be specified in your code at run-time. Following is an example of using execSQL for an insert statement which does not return a result set.

query1.sql.add('insert into employee (fname,lname) 
values (''Gwen'',''Heistand''') ; 
query1.execSQL ;

Now that we have learned the two methods for sending server SQL we can use any SQL statement with server specific syntax, even DDL (data definition language) statements.

FIGURE 3 - A DDL statement within a TQuery component

Dynamic SQL / Parameterized Queries

Another server specific feature which is surfaced in Delphi is the ability to prepare dynamic SQL statements. Although there are many ways to create SQL dynamically within Delphi (concatenating strings, using the Format procedure, etc.) the following process is included in this paper because it utilizes a feature available on most database servers, where the server can prepare (or compile) the SQL statement ahead of time and the parameters can be substituted later. This means that when the same SQL statement is reused with different parameters, the server will not have to recompile or prepare the SQL statement each time.

A parameter (or bind variable) can be specified in the where clause of a SQL statement by preceding the variable with a : (colon).

The steps for issuing dynamic SQL to a server are as follows :

1. Define the SQL - SQL is stored in Delphi TString object.
2. Prepare the SQL - SQL is sent to server, compiled and server prepares the execution plan.
3. Satisfy the parameters - Delphi stores values in the TQuery params array.
4. Execute the SQL - SQL with parameter values is sent to server.

Some example code for the above process would look like :

Step 1

query1.sql.clear ;
query1.sql.add('update employee set first_name = ''Devin'' 
where emp_no = :myparm') ;

Step 2

query1.prepare ;

Step 3

query1.params[0].asinteger := 5 ; 
	or
	query1.parambyname('myparm').asinteger := 5 ;

Step 4

query1.execSQL ;	(use query1.open if results are returned)

Parameters can also be supplied at design time using the params property editor.

Example : Using a TQuery and InterBase specific SQL

Let's now use the concepts we've learned to build a SQL entry window in Delphi. We will then use this window to enter InterBase SQL to create a table, create a number generator, and a trigger which will fire this number generator to give us a unique id for each new record entered into our table. A trigger is a server procedure which is fired automatically by the server for certain events. Triggers move the most amount of work to the server because they don't even require Delphi to execute them explicitly.

FIGURE 4 - A simple SQL entry screen with code

We will now enter the following InterBase specific SQL statements into our SQL window and execute each one :

create table sqlcount (inc integer, item1 char(10))

create generator myinc_gen

create trigger create_rec for sqlcount
before insert
position 0
as begin
new.inc = gen_id(myinc_gen, 1); 
end

We now have a table where the server will add a new auto incrementing integer every time a new record is added to the table. The power and reusability lies in the fact that the server will perform this work no matter what part of your application (or other applications for that matter) is performing the insert. We can see our generator and trigger at work by creating a simple Delphi form with a data entry grid.

Using the TStoredProc Component

The Delphi TStoredProc component provides a visual way to access stored procedures on a database server. A stored procedure is a procedure which performs some operation or set of operations on server data. Stored procedures are written in the native SQL language of the server, and are stored and executed on the database server. A stored procedure is similar to a trigger except it is not fired based on certain database events. Instead it is called explicitly by the front-end application. The Delphi TStoredProc component helps us set up these explicit calls and the parameters that are required.

Much like queries, stored procedures may return a result set (cursor) or no result set, stored procedures may also return output parameters. The way stored procedures return their results can be different for each server, and the Delphi architecture gives us the ability to gain access to these results easily. For example, InterBase stored procedures which return result sets (select procedures) are accessed using a SQL select statement as if the stored procedure were a table or a view. This means that Delphi can access an InterBase select procedure using the TQuery component that we have already seen.

The following SQL code shows an InterBase select procedure which returns the department code in ascending sorted order :

create procedure getdeptno returns(dept smallint) as
begin
for select dept_no from department order by dept_no into :dept 
do
begin
suspend ;
end  
end

This stored procedure can then be executed using a TQuery and setting the SQL property to :
select * from getdeptno
{getdeptno is the stored procedure name}

For stored procedures on other servers or InterBase execute procedures, a stored procedure component must be used.

FIGURE 5 - The TStoredProc properties

As can be seen in Figure 5, once you connect a TStoredProc component to a database, the StoredProcName property editor contains all the stored procedure names that this user has access to on the server. Once a stored procedure has been selected, the params property editor will display the input and output parameter names, their types, and allow you to supply default values (See Figure 6).

Important Note : It is important to keep in mind that the TStoredProc component has the same ancestor as TTable and TQuery. This means that you will be able to use many of the same properties, events and methods. A stored procedure that returns a result set can also be used for the dataset property of a TDataSource, much like a TTable or TQuery.

FIGURE 6 - The TStoredProc params property editor

The steps required to execute a stored procedure with parameters is quite similar to executing a dynamic SQL statement within a TQuery. These steps are detailed below :

1. Define the stored proc - StoredProcName property is set.
2. Prepare the procedure - Params array is set up (can be done with params editor).
3. Satisfy the parameters - Delphi stores values in the TStoredProc params array.
4. Execute the procedure - Params are sent to server and procedure is executed.
5. Get the output values - Retrieve values from params array.

Using the dept_budget stored procedure from the employee database, which takes a department number as input parameter DNO and returns the total department budget as output parameter TOT, the following code will perform the above steps to return the total budget to an edit box.

Step 1

storedproc1.name := 'dept_budget';

Step 2

storedproc1.prepare ; 

Step 3

storedproc1.params[0].asstring := '600' ; 
or
storedproc1.parambyname('DNO').asstring := '600' ;

Step 4

storedproc1.execproc ; 

Step 5

edit1.text := storedproc1.params[1].asstring ; 
or
edit1.text := storedproc1.parambyname('TOT').asstring ;

As you can see, the syntax for accessing the parameters of the stored procedures works the same way as it did for the TQuery component.

The TBatchMove Component

One of the challenges in rolling out a Client/Server application is moving your existing data into the server. There are currently many different ways to do this, the Database Desktop, native SQL code, and server specific loading utilities are among the tools that one can use. Another option is the Delphi TBatchMove component. The TBatchMove component provides an easy to use option for moving data to and from any database types which are supported by the Borland Database Engine.

FIGURE 7 - TBatchMove properties

Figure 7 shows the published properties of the TBatchMove component. Some of the more important properties are detailed below :

Like all the Delphi components, these properties may be set at run time so it is easy to see how we could build a generic table move/copy utility. Once the properties have been set for the operation you wish to perform, the execute method will perform the batch move.

Accessing Heterogeneous Data

Heterogeneous data is that which resides in different servers. For example, we may wish to show some relationship between data which resides on an Oracle7 server and some data which is stored on an InterBase NT server. Within Delphi there are basically two ways to show data relationships between heterogeneous data.

Heterogeneous Linked Cursors

To show master/detail relationships between data residing on different servers, we simply connect our multiple TQueries or TTables to the respective servers and create a link just like we would normally, there are no special steps involved !

Heterogeneous Joins

To join data from different servers within a single SQL statement in a TQuery component there are a couple of steps required. First, since a TQuery only has one DatabaseName property and we are accessing different servers, we must make the DatabaseName point to a local alias on our drive or the network. We must also have an alias for each of the databases we wish to access. In our SQL statement, each reference to a table must be preceded by the alias of the proper server. Finally, our SQL syntax must conform to that of Local SQL.

Conclusion

In this presentation, we have seen only a small subset of the numerous possibilities left open to us by using server specific features. The Delphi architecture provides us with the opportunity to exploit all of the powerful server features which have been developed over many years by the various server vendors. This accessibility to server specific features is the key to delivering secure, robust, maintainable, reusable, and well performing Client/Server Applications.