Upsizing Paradox Applications to Client/Server
Client server technology involves adding software to a computer to create what is known as a database server. A database server holds the data for database applications and runs software that allows users on workstations on the LAN to send it various commands to add, delete, change or return data. The command language most commonly used by database servers is structured query language or SQL. Frequently client server computing is referred to simply as SQL.
Table of Contents
-
Client Server/SQL expressions
-
An Overview of Client/Server
-
Preparation Before Migrating to Client Server
-
Required Changes
-
Performance Issues
-
Error Handling
-
Conclusion
Client Server/SQL expressions
Before continuing to discuss client server technology it will be useful to have a common vocabulary:
File Server - A computer that runs software for a Local Area Network (LAN). Files can be shared by all users on the LAN with access to a particular file server. A file server just reads and writes these shared files. It does not perform any processing.
Network Operating System (NOS) - The LAN software that runs a file server
Workstation - For this discussion, a workstation is a computer on the LAN which is used by a user to access files on a file server.
Database Server - A computer that runs database server software, typically SQL database software. The database server is also referred to as the "back end".
Client - A workstation that sends commands to the database server to run queries, create tables, add or remove indexes, maintain user security, or perform any other functions of which the database is capable. The client is also referred to as the "front end".
Local tables - Tables that reside on a file server.
Remote tables - Tables that reside on a database server.
Structured Query Language (SQL) - A standardized query language used to access data. There are currently two SQL standards: ANSI 89 and ANSI 92. Paradox 5 supports ANSI 89 SQL syntax against local tables. Paradox 7 now supports ANSI 92. All current versions of the top selling SQL database software support ANSI 92 SQL. In addition they also have their own enhancements to the language. This means that in spite of the ANSI standards, SQL statements written for one SQL server software will most likely not work with SQL software from a different vendor unless the statements adhere strictly to the ANSI standards.
Row - Synonymous with the term "record" as it pertains to Paradox tables.
Column - Synonymous with the term "field" as it pertains to Paradox tables.
An Overview of Client/Server
Client server technology offers many advantages to database applications. It also adds a great deal of complexity that is not necessarily desirable. Relative to a file server application, there is much to learn and prepare for before attempting to build a client server application.
Any client server software will have to run on a multitasking network operating system. The most common network operating systems (NOS's) are IBM's OS/2 LAN Server, Microsoft's Windows NT, Novell's Netware and Unix. In order to avoid the problem of supporting multiple NOS's, it is frequently prudent to use a NOS that is already being used in your company. Most of the major SQL server software runs on most, if not all, of these NOS's.
Some of the more common SQL server software includes Borland International's Interbase, IBM's DB/2, Informix, Microsoft SQL Server, Oracle, and Sybase. There are a variety of reasons to use any of these network operating systems and SQL server software, but which one is the best for your company is open to interpretation and beyond the scope of this paper.
The first requirement before attempting a client server application is to hire or train at least one primary person and one backup person on-site who are familiar with the SQL server software and the NOS it will run on. SQL server software has administrative needs that require a degree of expertise in the SQL software being used. Furthermore, in order to develop an optimal client server application, programming should be performed on the server itself. More will be discussed on this topic later in the paper.
File server databases versus client server databases
Database servers excel at querying. Almost everything performed on a SQL server is performed through a query. It is the nature of a query is to access subsets of data. SQL servers excel at accessing subsets of data. The performance advantages of SQL servers versus file servers really shows itself with large databases. When working with smaller databases, a database server will not be significantly faster than a file server and may even be slower. To understand why, we need to take a look at how a database server manages requests versus how a file server manages requests.
Simplistically put, a file server only reads and writes data to the disk(s) on the file server. A workstation simply views it as a another hard disk. Similar to a local hard drive, the file server just returns files and writes to files. All processing is performed by the workstation. This means that if you link three tables of one gigabyte each in a single query, three gigabytes of files are read by the file server, sent through the network interface card, down the network lines, and through the network interface card on the workstation where they are fed into its memory. Needless to say this is resource intensive and time consuming.
A database server is a computer whose principal function is to query databases. When a user queries a table, the server runs the query and then returns only the answer table through the network lines. Network traffic is greatly reduced because throughput is greatly reduced.
Since a SQL server only accesses data through queries, a request for all data in a given table will be a slower process when coming from a database server. It will have to run a query to find all of the data. A file server only needs to read the file from disk and return it. No query has to be run.
To Upsize or Not to Upsize?
The implications of this discussion are that database applications that are good candidates for upsizing to client server are those whose database access consists of requesting subsets or summary information from large tables.
Preparation Before Migrating to Client Server
Use Aliases
When building any application in Paradox, it is always wise to use aliases when referencing an object on disk. This is true whether or not the object is a table, form, script, library or any other object on disk. The benefits are that porting an application to another location will mean nothing more than moving the files and changing the aliases. It is a great advantage in flexibility even to applications whose data resides on file servers. In Paradox the only way to reference a table that resides on a database server is through an alias. This means there is no other option but to use them in a client server application. Aliases are essential for all client server projects or projects that may become client server. Any applications open a tCursor, form or report should reference an alias:
if not tcCust.open(":CustData:Customer") then
errorShow()
return
endif
Figure 1 - Referencing an alias while opening a tCursor.
Queries should reference an alias.
qy = Query
:CustData:Customer | CustID | State |
| Calc Count All | Check |
endQuery
Figure 2 - Referencing an alias in a query.
Datamodels should reference an alias also.
Figure 3 - A datamodel object that references an alias.
Field and Table Names
Everything that resides on an SQL server is an object - tables, fields, indexes, stored procedures, triggers etcetera. SQL server software does not allow special characters such as spaces, # and & in the names of objects. For this reason it is advantageous to avoid special characters in the table names and field names of any application that may one day be a candidate for upsizing. If tables are already in use with special characters in table and/or fieldnames, these names will have to be changed. Additionally, any ObjectPAL statements that references them will have to be changed also. This must to be done prior to moving the tables to the database server.
Most of the major SQL server software packages only use uppercase letters when naming objects in a database. This includes table and field names. For this reason it might be a consideration to use strictly uppercase letters when naming fields of a database that may be a candidate for upsizing in the future.
SQL servers do not support Windows field types such as formatted memos, graphic fields and OLE fields. If these field types are to be used in a client server application, they will have to be stored in tables on a file server.
Interface Differences
SQL databases do not use record numbers to reference rows in their tables. When viewing a SQL table in a tableview in Paradox, no record numbers display in the record number column. Although it is possible to get the "record number" of an SQL table with ObjectPAL, this requires Paradox to run a SQL query to get the relative position in the primary index. It is a time consuming process and should be avoided.
One thing that many users find disturbing about working with SQL tables is that the thumbnail tab on a vertical scroll bar for tableviews, tableframes and multi-record objects does not move. It always stays centered on the scroll bar. This is not to say that clicking on the scrollbar does not move through records, it does. When used with local tables, the position of the tab on the scroll bar represents the position of the current record relative to the table. If the user is on one of the last records, the tab is near the bottom of the scrollbar. Since SQL tables do not have record numbers, knowing the position of the current record in the table is impossible without running a time consuming query. When working with SQL tables, Paradox avoids the performance penalty of such a query and simply leaves the thumbnail tab in the center of the vertical scrollbar.
Figure 4 - A tableview of a SQL table. No record numbers and although the first record is current, the thumbnail is in the middle of the scroll bar.
Required Changes
Triggers
Referential Integrity and Triggers
Referential integrity is a wonderful feature of Paradox tables. Paradox referential integrity cannot be implemented on SQL tables although some SQL servers have referential integrity as part of their DDL. Figure 5 show to DDL "alter" statements that were used to add referential integrity between the Orders and Customer tables and between the lineItem and Orders tables. Referential integrity in Interbase may also be added in a "create" statement. These are the same tables that used in the DiveMast example that ships with Paradox for Windows. These alter statements are written in Interbase SQL version 4.
ALTER TABLE ORDERS
ADD CONSTRAINT CUSTOMER_RI FOREIGN KEY (CUSTOMER_NO) REFERENCES CUSTOMER (CUSTOMER_NO)
ALTER TABLE LINEITEM
ADD CONSTRAINT ORDERS_RI FOREIGN KEY (ORDER_NO) REFERENCES ORDERS (ORDER_NO)
Figure 5 - Interbase commands that add referential rules between Customer and Orders; and Orders and lineItem.
If your SQL database does not support referential integrity, a solution to creating referential integrity rules on a SQL database is to use a feature called triggers. A trigger is a routine that automatically executes in response to inserting, deleting or updating a row in a given table. These routines have to be written using SQL statements. In order to create the equivalent of referential integrity in Paradox, triggers have to be written on the database server to ensure no orphan records can be created. There are four ways to create an orphan record, so four different triggers must be written per referential integrity rule. Listed below are the actions the triggers must stop. Beneath each description is a an example of code to create a trigger written in Interbase SQL to stop the orphaning of record(s) by the particular action. The triggers are written to create the referential integrity rules for the Customer and Orders tables of the DiveMast example that ships with Paradox for Windows. The syntax of such triggers can vary from server to server.
1. Changing the linking value of the master table. In this case the action is not stopped, instead a change to the linking value is cascaded across all linked details.
CREATE TRIGGER CASC_CUST_NO FOR CUSTOMER
BEFORE UPDATE POSITION 0
AS
BEGIN
IF (OLD.CUSTOMER_NO <> NEW.CUSTOMER_NO) THEN
UPDATE ORDERS
SET CUSTOMER_NO = NEW.CUSTOMER_NO
WHERE ORDERS.CUSTOMER_NO = OLD.CUSTOMER_NO;
WHEN ANY DO
EXCEPTION CANNOT_CASCADE_CHANGE;
END;
Figure 6 - Interbase code for a trigger that updates the linking values of orders when the customer number is changed.
2. Prohibit deleting of a master record while a detail record(s) depends on it.
CREATE TRIGGER CUST_DEL_CHK_DTLS FOR CUSTOMER
BEFORE DELETE POSITION 0
AS
DECLARE VARIABLE count_of_orders INTEGER;
BEGIN
SELECT COUNT(*)
FROM ORDERS
WHERE CUSTOMER_NO = OLD.CUSTOMER_NO
INTO count_of_orders;
IF (count_of_orders > 0) THEN
EXCEPTION CANNOT_DELETE_MASTER ;
END;
Figure 7 - Interbase code for a trigger that stops the deletion of a customer if there are orders that depend on it.
3. Prohibit adding a detail record whose linking value does not have a corresponding master record.
CREATE TRIGGER ORD_INS_CHK_MASTER FOR ORDERS
BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE count_of_cust INTEGER;
BEGIN
SELECT COUNT(*)
FROM CUSTOMER
WHERE CUSTOMER_NO = NEW.CUSTOMER_NO
INTO count_of_cust;
IF (count_of_cust = 0) THEN
EXCEPTION CANNOT_ADD_WO_MASTER ;
END;
Figure 8 - Interbase code for a trigger that stops the adding of an order record that has no corresponding customer record.
4. Prohibit saving an existing detail record with a changed linking value that does not have a corresponding master record.
CREATE TRIGGER ORD_UPDT_CHK_MASTER FOR ORDERS
BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE count_of_cust INTEGER;
BEGIN
SELECT COUNT(*)
FROM CUSTOMER
WHERE CUSTOMER_NO = NEW.CUSTOMER_NO
INTO count_of_cust;
IF (count_of_cust = 0) THEN
EXCEPTION CANNOT_ADD_WO_MASTER ;
END;
Figure 9 - Interbase code for a trigger that stops the changing of the customer number in an order record to a customer number that does not exist in the customer table.
If any of these actions result in orphaning a record, an exception is raised and the action is prohibited.
Denormalization and Triggers
Normalization is a design technique for removing data redundancy from databases. However denormalization is a technique that is often employed to increase the performance of extracting data from databases. Denormalization has a major disadvantage. Since it results in data redundancy, modifying data in one table can mean that redundantly stored data in other tables becomes inaccurate. The only way to ensure that the data retains its integrity is to update all tables containing related data at one time.
The DiveMast application that is bundled with Paradox is denormalized in that the total dollar amount for an invoice is stored in the Orders table in a field called "Total Invoice". This means that "Total Invoice" must be updated whenever a lineitem is added, deleted or the "Qty" or "Selling Price" fields of a lineitem are modified. In a file server database, the code for maintaining the integrity of the "Total Invoice" field must be programmed by any software that gives the user the ability to modify, add or delete lineitems. In a Paradox application, all forms that give the user access to the lineitem table must have code in place that update the "Total Invoice" field in the Orders table.
Figure 11 shows code that updates the "Total Invoice" field. It is located in the built-in action method of a tableframe bound to the lineitem table. In this example, the variable recOldLI is a record variable with the following structure:
recOldLI record
Selling_Price currency
Qty number
endRecord
Figure 10 - Record variable declaration that stores the original values of selling price and quantity for a lineitem.
The values of Selling Price and Qty are stored in this variable. It is assigned in the built-in arrive method of the record object on the tableframe. This way the code will be able to compare the old value to the new value to test for changes to the Selling Price and Qty fields.
method action(var eventInfo ActionEvent)
switch
case eventInfo.id() = dataPostRecord or
eventInfo.id() = dataUnlockRecord :
if (recOldLI.qty <> qty'value) or
(recOldLI.Selling_Price <> Selling_Price'value) then
if orders.lockRecord() then
orders.total_Invoice'value =
orders.total_Invoice'value -
(recOldLI.qty * recOldLI.Selling_Price) -
(qty'value * selling_price'value)
else
msgStop("Orders Locked","Cannot post changes to " +
"the orders now, try again later.")
eventInfo.setErrorCode(userError)
endif
endif
case eventInfo.id() = dataDeleteRecord :
if orders.lockRecord() then
orders.total_Invoice'value = orders.total_Invoice'value -
(recOldLI.qty * recOldLI.Selling_Price)
else
msgStop("Orders Locked","Cannot post changes to " +
"the orders now, try again later.")
eventInfo.setErrorCode(userError)
endif
endSwitch
endMethod
Figure 11 - ObjectPAL code in the action method that updates the value of Total_Invoice in the customer table whenever a detail record is added, changed or deleted. Please note that the use of an apostrophe "'" instead of a period "." is intentional here. The apostrophe is a form of notation that makes explicit that the reference to the right of it is a property and only a property (rather than the name of another object).
If a user modifies the lineitem table through a tableview or a form that does not have this code, the value of the "Total Invoice" field will be incorrect. It is for reasons such as this that denormalization is particularly perilous when used on file server databases.
In a client server system, this problem is easily alleviated through the use of triggers. Triggers may be written to update the "Total Invoice" field. Since triggers reside on the database server, or "back end", the user may access the lineitem table through any form, tableview or even a different front end such as a spreadsheet and the integrity of the "Total Invoice" field is always maintained.
The following triggers take care of updating the total invoice field no matter how a row in the lineitem table is accessed. The first one adds a check for the condition of changing the order number of an existing lineitem. This would cause the lineitem to become subordinate to another order. This check is not something required by the code in the action method in the example for a file server database because the order field of the lineitem table is not available to the user in that particular form. When creating a SQL trigger, all eventualities must be accounted for.
1. If an existing lineitem is updated, update the "Total_Invoice" field.
CREATE TRIGGER UPDT_TOTAL_INVOICE FOR LINEITEM
ACTIVE BEFORE UPDATE POSITION 2
AS
BEGIN
IF ((OLD.QTY <> NEW.QTY) OR
(OLD.SELLING_PRICE <> NEW.SELLING_PRICE)) THEN
IF (OLD.ORDER_NO = NEW.ORDER_NO) THEN
BEGIN
UPDATE ORDERS SET TOTAL_INVOICE =
(TOTAL_INVOICE - ((OLD.QTY * OLD.SELLING_PRICE) -
(NEW.QTY * NEW.SELLING_PRICE)))
WHERE (ORDER_NO = OLD.ORDER_NO);
WHEN ANY
DO EXCEPTION CANNOT_CHG_TOTAL_INV;
END
ELSE
BEGIN
UPDATE ORDERS SET TOTAL_INVOICE =
(TOTAL_INVOICE - (OLD.QTY * OLD.SELLING_PRICE))
WHERE (ORDER_NO = OLD.ORDER_NO);
WHEN ANY
DO EXCEPTION CANNOT_CHG_TOTAL_INV;
END
BEGIN
UPDATE ORDERS SET TOTAL_INVOICE =
(TOTAL_INVOICE + (NEW.QTY * NEW.SELLING_PRICE))
WHERE (ORDER_NO = NEW.ORDER_NO);
WHEN ANY
DO EXCEPTION CANNOT_CHG_TOTAL_INV;
END
END
Figure 12 - Interbase code for a trigger that updates the Total_Invoice field in orders when quantity or selling price are changed in LineItem.
2. If a new lineitem is inserted, update the "Total Invoice" field.
CREATE TRIGGER INS_TOTAL_INVOICE FOR LINEITEM
ACTIVE BEFORE INSERT POSITION 2
AS
BEGIN
IF (NEW.QTY > 0) THEN
BEGIN
UPDATE ORDERS SET TOTAL_INVOICE =
(TOTAL_INVOICE + (NEW.QTY * NEW.SELLING_PRICE))
WHERE (ORDER_NO = NEW.ORDER_NO);
WHEN ANY
DO EXCEPTION CANNOT_CHG_TOTAL_INV;
END
END
Figure 13 - Interbase code for a trigger that updates the Total_Invoice field in orders when a LineItem is added.
3. If an existing lineitem is deleted, update the "Total Invoice" field.
CREATE TRIGGER DEL_TOTAL_INVOICE FOR LINEITEM
ACTIVE BEFORE DELETE POSITION 2
AS
BEGIN
IF (OLD.QTY > 0) THEN
BEGIN
UPDATE ORDERS SET TOTAL_INVOICE =
(TOTAL_INVOICE - (OLD.QTY * OLD.SELLING_PRICE))
WHERE (ORDER_NO = OLD.ORDER_NO);
WHEN ANY
DO EXCEPTION CANNOT_CHG_TOTAL_INV;
END
END
Figure 14 - Interbase code for a trigger that updates the Total_Invoice field in orders when a LineItem is deleted.
Triggers remove the potential problem of losing data concurrency when denormalization is used. No matter how the user attempts to add, delete or update the data, the values remain accurate.
Lookup Tables
Paradox style Ctrl-Spacebar table lookups are not supported with SQL tables. In order to create this kind of lookup with SQL tables it is necessary to create a dialog box that will be called with ObjectPAL. The dialog box can be coded to be brought up with the Ctrl-Spacebar as is the case with Paradox table lookups.
It is best that lookup tables reside on the SQL server so that the lookup table can be joined in queries at the server. While queries that link remote tables with local tables (called a hybrid query) are possible in Paradox, hybrid queries tend to result in poor performance. There is also the potential for concurrency problems if the system keeps the table on the SQL server and a copy of the lookup table locally.
In order to create a Paradox style lookup, the dialog box should have the lookup table in a tableframe or MRO. The code shown in figure 15 calls a dialog box with an OK button and a Cancel button. The OK button issues "formReturn(True)" and the Cancel button "close()". The form is called from event method action on the field requiring the lookup. In order to create the same style of messaging as Paradox tables, the event method setFocus is coded to put the message "Press Ctrl + Space for lookup help." on the status bar.
Var
fmStock form
endVar
method action(var eventInfo ActionEvent)
if isEdit() and eventInfo.id() = dataLookup then
if fmStock.attach("Stock") then
fmStock.bringToTop()
else
if not fmStock.open(":OPAL3:Stock") then
errorShow()
return
endif
endif
if fmStock.wait() then
self'value = fmStock.stock.stock_no'value
fmStock.close()
endif
bringToTop()
endif
endMethod
method setFocus(var eventInfo Event)
doDefault
message("Press Ctrl + Space for lookup help.")
endMethod
Figure 15 - Calling a dialog box to create a Paradox style lookup using SQL tables.
Security in SQL databases is implemented at the server. Any time an attempt is made to access a SQL database, a user id and password must first be given. This can be supplied by ObjectPAL prior to accessing the SQL database. If it is not supplied by ObjectPAL, the user will be prompted for an identifier and password. Security in most SQL database software is so tight that it meets military specifications. For this reason, maintaining passwords on the server will lead to the tightest security.
Below is an example of ObjectPAL code in an init method that automatically logs the user into a SQL database.
Method init(var eventInfo event)
var
dySQL dynArray[] string
db database
tcCust, tcOrders tCursor
endVar
dySQL["Server Name"] = "C:\\SB\\SQL\\OPAL.GDB"
dySQL["User Name"] = "DPR"
dySQL["Password"] = "fred"
if not db.open("OPAL3SQL", dySQL) then
errorShow()
return
endif
endMethod
Figure 16 - Using objectPAL to log into an SQL server.
Performance Issues
SQL and tCursors
On a local table it is often advantageous to use tCursors to increase performance. TCursors are record pointers and pointers in most database programming languages provide good performance. This is almost never the case when working with remote tables. SQL is a set based language. It is designed for, and excels at, querying. With most of the major server software, using pointers results in poor performance. For optimal performance against remote tables, tCursor scans should be replaced with queries.
Issues Associated with Optimistic Locking
Avoid Explicit Record Locking
When Paradox accesses Paradox tables, modifying a record causes Paradox to lock that record. This ensures that the record cannot be modified by another user. Locking a record on a table in a SQL database is something to avoid. On many database servers locking a record locks not just the one record, but a page of records. This ties up valuable resources. Record locking also has a strong negative impact on performance. For these reasons record locking should be avoided.
Retrieve a Recent Version of the Row
When a user or ObjectPAL touches a record from a SQL table, Paradox does not lock the record. Instead Paradox uses a technique called optimistic locking. It takes a snapshot of the record prior to its being modified. When the record is unlocked, Paradox queries the server for the record, locks it, checks each field value by value to see if any changes have taken place since the user touched the record. If the state of the record has not been changed, the old record is updated with the new values and the lock is released. If the record on the database server has been changed since the record was last retrieved, the new record is returned and the update does not take place.
For these reasons testing for changes by other users is reversed from what is normally done with file server databases. On a file server database, a record is locked before modifications are made. Code is written to test for the success of the lock before modifications begin.
Optimistic locking requires changing the testing from the locking to the unlocking of the record. It is the unlock of the record that could fail because of a change of the record on the database server. The event identifiers to test for in the action method are dataUnlockRecord and dataPostRecord. There will be one of two error codes that will be returned, peOptRecLockFailed and peOptRecLockRecDel. Definitions for these error codes are listed below.
- peOptRecLockFailed - Record could not be saved because the record has been changed since it was last retrieved.
- peOptRecLockRecDel - Record could not be saved because the record was deleted since it was last retrieved.
In order to minimize the likelihood that a record is changed or deleted on the server while it is being modified by a user, use the method forceRefresh to ensure the most recent version of the record. ForceRefresh is available for both tCursor and UIObject types.
Querying
Ignore Source Changes
For optimal performance for explicit queries on SQL tables, run queries with the restart option ignore source changes. When this is invoked, changes to the table do not cause the query to restart. The ObjectPAL statement for setting this is:
setQueryRestartOptions(queryNoLock)
Figure 17 - Changing the query restart options to "Ignore Source Changes".
No Auxiliary Tables
Queries to remote tables do not create auxiliary tables such as "deleted", "inserted", "errorDel" and "changed". Any code written that relies on the creation of auxiliary tables will have to be changed.
Transactions and ObjectPAL
When Paradox accesses remote tables using QBE, forms, reports and most ObjectPAL code, it uses the default channel to the Borland Database Engine (BDE). This default channel to the BDE uses one connection to the remote server. Using the executeSQL method opens another, separate, channel to the BDE. Since the BDE perceives it as a separate channel, it creates a new connection to the database server that is distinct from the default channel. The SQL server perceives each connection as a separate user.
Transactions in any database are limited to a single connection. A transaction cannot include changes from one connection and changes from another. The entire transaction must be through one connection. In terms of ObjectPAL, this means that a transaction cannot be a mix of executeSQL (or executeSQLString) statements and other ObjectPAL statements such as QBE queries and tCursor updates. This is because the executeSQL will use one channel and the QBE and tCursors will use the default channel. Transactions can span across either multiple executeSQL statements, or across a series of ObjectPAL statements that do not include executeSQL.
Indexes
Proper indexing is a critical part of obtaining the best performance on an SQL database. After the ObjectPAL coding of an SQL application is complete, a process known as tuning the server will need to be done. It consists of adding the proper balance of indexes to a database to obtain the best performance. This can be an involved process that can be as mundane as adding an index, testing, and, should the performance not be improved, dropping the index and trying another one. Below are some general guidelines to avoid this time consuming approach.
One set of criteria for indexing is to place indexes on all linking fields. Some SQL databases offer several types of indexes. If your database offers several types of indexes, it is generally good to use a clustered index.
While creating too few indexes can result in poor performance, too many indexes can have the same effect. Avoid creating multiple indexes on the same column. If query selection is going to be performed on different combinations of the same fields, is best to avoid creating every combination of index possible. A good alternative is to create one index on each field.
Beware when posting records with a tCursor on remote tables. If the attempt to post a record to a remote table is not made with the tCursor opened on a unique index, the posting could fail. In order to test for this eventuality, use the ObjectPAL tCursor method isOpenOnUniqueIndex.
Minimize Automated Querying
Paradox does a great job of maintaining the same look and feel when used against SQL tables that it has when used against Paradox tables. The problem with this is that in order for Paradox to maintain the same look and feel, it has to run SQL queries to present the data not only to the user, but also to ObjectPAL code. In spite of the fact that SQL servers are great at querying, to get the best performance out of any client server application it is best to minimize the number of queries required to run it. There are many things that require Paradox to run queries. Some of them are the standard navigation actions such as next, previous, begin, end and locate. To minimize the number of queries to the database server it is best to avoid these actions whenever possible.
Other actions that force Paradox to send query requests to the server are changing indexes and setting ranges and filters on tables. In these situations Paradox must refresh the form or tCursor by retrieving the filtered and/or reordered data with another query. Minimizing the need for Paradox to do this by reducing the use of ObjectPAL methods such as switchIndex, setGenFilter and setRange.
One thing that tends to cause intensive querying on the database server is the use of linked tables in a datamodel. Every time the user arrives on a new record on a master table, all subordinate tables must be requeried by Paradox to get the appropriate detail records. For this reason it is generally a good idea to avoid linked datamodels.
On the other hand, this means the user will lose the ability to see only those detail records that are subordinate to the displayed master record. A way to give this capability to the user is to place an update button on the form that synchronizes the displayed records when the user requires it. This way simple scrolling through records will not result in multiple queries to all subordinate tables. This will happen only when specified by the user (generally done when the user arrives on the record they are interested in working with.)
In order to stop this dynamic filtering create a form with the necessary tables in the datamodel unlinked. Add a button on the form that issues a setRange on the subordinate table as shown in figure 18. Be sure that the tableframe or multiRecord object (Orders in figure 18) is opened on a index based on the linking field(s).
if not orders.setRange(customer_no'value) then
errorShow()
endif
Figure 18 - Code to filter the orders table in an unlinked datamodel. This code can be placed on a an "Update" button.
Error Handling
Queries to database servers will often result in errors. These errors are not Paradox errors, but are errors returned by the server itself. While programming a client server application it will be necessary to have easy access to the list of error codes for the SQL server your application accesses. As the errors that the application is likely to receive are established, responses to these errors should be programmed in a custom method. To determine if the a specific error from your SQL server is in the error stack, use the ObjectPAL procedure:
errorHasNativeErrorCode(-150)
Figure 19 - Using the ObjectPAL procedure errorHasNativeErrorCode to determine if the Interbase server is returned the error for "Table is read only". This checks for the specific SQL error anywhere in the error stack.
To get the error from the server use the procedure:
errorNativeCode()
Figure 20 - This is the procedure for extracting the SQL errorCode out of the errorStack. Unless the errorCode returns the constant value peGeneralSQL, this value will be zero.
Figure 21 shows an error procedure designed to be called after executing anything against an SQL database. A database variable is required as a parameter so that that the procedure will be able to handle the an uncommitted transaction (one of the more common errors when developing an SQL application).
var
strYNC string
liSQLError longInt
endVar
method showSQLError(var dbSQL database)
; If there is an SQL error in the error stack
if errorHasErrorCode(peGeneralSQL) then
; The most relevant error is usually at the end of the stack.
; It is generally the error from the SQL server.
; This loop removes all errors until it encounters an SQL error or it
; runs through the entire error stack.
while errorPop() and errorCode() <> peGeneralSQL
endWhile
switch
case errorNativeCode() = -150 :
msgStop("No updating!", "This table is read only!")
case errorNativeCode() = 901 :
if dbSQL.transactionActive() then
strYNC = msgYesNoCancel("Outstanding Transaction!",
"You must rollback or commit the current transaction "+
"before continuing. The exact error is " +
string(errorNativeCode()) + " (" + errorMessage() +
").\n\nDo you wish to commit the transaction now?" +
"\n\n\tCommit - Yes\n\tRollback - No\n\tCancel - " +
"Ignore and continue")
switch
case strYNC = "Yes" :
dbSQL.commitTransaction()
case strYNC = "No" :
dbSQL.rollbackTransaction()
endSwitch
endif
case errorNativeCode() = 902 :
msgStop("Database Corrupted", "Break out the backups and get " +
"prepared for a very late night. The database is corrupted." +
" The exact error is " + string(errorNativeCode()) +
" (" + errorMessage() + ").")
otherwise :
msgInfo("Unlogged SQL Error!", "The database server just " +
"returned error " + string(errorNativeCode()) +
". The error message is " + errorMessage())
endSwitch
endif
endMethod
Figure 21 - An error procedure for handling SQL errors.
Conclusion
Paradox is one of the most robust client/server front ends on the market. If you have already been creating Paradox applications, you are in a good position to use Paradox as a client/server front-end. The move to client/server takes advantage of everything you already know about Paradox. While there are a number of considerations when upsizing a Paradox application to client/server, the considerations are easily taken care of with good planning. If your database applications can use the performance, security, transaction processing and other advantages of a SQL database - upsize it!