BORLAND SQL LINKS TIPS FOR PARADOX 5.0 FOR WINDOWS USERS --------------------------------- This file contains important, late-breaking information you need if your current Paradox 5.0 application uses the Borland SQL Links and you will be building or using an application built with a newer version of the SQL Links. For example, if you are building or using applications built with Delphi. * * * * * TABLE OF CONTENTS ----------------- 1. General Information 2. Informix Driver 3. InterBase Driver 4. Oracle Driver 5. Sybase Driver 1. GENERAL INFORMATION ---------------------- The Table method sort keyword and sortTo TCursor method are missing from the list of standard ObjectPAL methods that do not support SQL. 2. INFORMIX DRIVER ------------------ SQLPASSTHRU MODE AND MULTIPLE INFORMIX CONNECTIONS. This release of the SQL Link Informix driver uses Informix ESQL/C 4.10, which allows only one connection to an Informix server at a time from each workstation. This means that you cannot be connected to two Informix servers or to two databases on the same server simultaneously. SQL Link operations on Informix databases are of two types: - those that execute pass-through SQL statements (whether interactively or through ObjectPAL) - all other database operations (such as running queries, viewing tables, editing through forms, and executing methods of ObjectPAL's tCursor, table, and UIObject types) The SQL Link Informix driver "sees" these two types of operations as different connections. If you set the Informix driver's SQLPASSTHRU MODE to one of the SHARED settings (either SHARED AUTOCOMMIT, which is the default, or SHARED NOAUTOCOMMIT), you can execute both pass-through SQL and non-pass-through operations in the same connection. If you set SQLPASSTHRU MODE to NOT SHARED or blank, you will be unable to switch between pass-through SQL statements and other non-pass-through operations in the same session. If your first operation on the server is a pass-through SQL statement, you can only perform pass- through SQL operations in that session. If your first operation is a non-pass-through operation, you can only perform non-pass-through operations in that session. To switch from one to the other, select the alias for the database to which you are connected in the Alias Manager dialog, choose Disconnect, then choose Connect. If you attempt to perform an operation that cannot be executed because SQLPASSTHRU MODE is not set to allow shared connections, you may see the following: - Pass-through SQL, ObjectPAL, and other operations return the error message "Multiple connections not supported". - You can view the SQL equivalent of a QBE query in the SQL Editor, but you cannot execute the pass-through version of that query while QBE is open. If you have set SQLQRYMODE to NOT SHARED and have already started a session working with QBE, follow these steps to execute a pass-through query: 1. Use Query | Show SQL to display the SQL equivalent of the query in the SQL Editor 2. Use File | Save to save the query as an .SQL file. 3. Use the Alias Manager to explicitly disconnect from the Informix server. 4. Use the Alias Manager to explicitly connect to the Informix server. 5. Select File | Open | SQL File to select the saved SQL query. 6. Execute the query as pass-through SQL. To execute a QBE after starting a session in pass-through: 1. Use the Alias Manager to explicitly disconnect from the Informix server. 2. Use the Alias Manager to explicitly connect to the Informix server. 3. Select File | Open | Query to open the saved Borland desktop query. WORKING WITH INFORMIX SERIAL FIELDS. The Informix Serial field type is a "write once, read always" field type. In an Informix database, when you insert a new record into a table you can either assign a value greater than zero to a serial field, or let Informix supply a value for you. If the serial field has a unique index placed on it, any value assigned to the serial field must be unique. Once the record is committed to the table, that value cannot not be updated. Paradox for Windows supports all of the above behavior of the Informix Serial type, with the assumption that the Informix server (and not you) will supply values for any newly-inserted Serial fields. This means that when you create a form with a field bound to an Informix serial field, Paradox "protects" the Serial field by marking it Read Only. If you want to be able to assign your own value to the serial field, 1. Put the form in Design Mode. 2. Right Click on the field to display the Property Inspector pop-up menu. 3. De-select Runtime | Read Only. When you run the form, you will be able to specify the value to be inserted in the Informix Serial field, along with other fields in the table. Remember: you cannot insert a duplicate value for the Serial field if there is a unique index on the field; you also cannot modify the Serial field of an existing record. 3. INTERBASE DRIVER ------------------- IMPROVING PERFORMANCE OF ONE-TO-MANY LINKS. Often a one- to-many link joins a single-field index in the master table to the first field of a multi-field index in the detail. A common example is ORDERS.ORDERNUM joined to LINEITEM.ORDERNUM, where LINEITEM has a unique index on its ORDERNUM and ITEMNUM fields together. In InterBase 3.3 you can sometimes improve performance by adding a non- unique index on LINEITEM.ORDERNUM alone. BEGINTRANSACTION METHOD. Improvements in the BDE's handling of transaction isolation levels may affect your application if it uses the OPAL Database method beginTransaction(). (See the description of beginTransaction() in the Paradox 5.0 ObjectPAL reference or online help for a description of isolation levels.) Unless you use beginTransaction() to initiate a DirtyRead or ReadCommitted transaction, your application will not be affected by these improvements. You can use your application unchanged if you set the driver flags for the InterBase driver. See READLINK.TXT. Setting the driver flag will make transaction operations compatibile with SQL Links, version 2.0 (or earlier), disabling the improvements for all applications which use the InterBase driver. Under SQL Links, version 2.0 (or earlier), all beginTransaction() calls initiated a transaction with the RepeatableRead isolation level. RepeatableRead was used even if the optional isoLevel parameter was provided. Thus all the following beginTransaction() calls were equivalent and used RepeatableRead: var db Database success Logical endVar success = db.beginTransaction() success = db.beginTransaction("DirtyRead") success = db.beginTransaction("ReadCommitted") success = db.beginTransaction("RepeatableRead") BDE's new transaction support runs your transaction at a level supported by the server at or above the level you specify. If the server does not support an isolation level at or above that level, an error will be returned. If you specify an isolation level and write your application correctly assuming you have that isolation level, your application will execute correctly or will receive an error. For InterBase databases, db.beginTransaction() and db.beginTransaction("RepeatableRead") still initiate a RepeatableRead transaction. db.beginTransaction("DirtyRead") and db.beginTransaction("ReadCommitted") initiate a ReadCommitted transaction. So, if your application specified DirtyRead or ReadCommitted, it will now execute at the ReadCommitted level instead of the RepeatableRead level. You should test it to ensure that it operates correctly at the ReadCommitted level. 4. ORACLE DRIVER ---------------- BEGINTRANSACTION METHOD. Improvements in the Borland Database Engine's (BDE) handling of transaction isolation levels will affect your application if it uses the OPAL Database method beginTransaction(). (See the description of beginTransaction() in the Paradox 5.0 ObjectPAL reference or online help for a description of isolation levels.) If you do not use beginTransaction(), your application will not be affected by these improvements. You can use your application unchanged if you set the driver flags for the Oracle driver. See READLINK.TXT. Setting the driver flag will make transaction operations compatibile with SQL Links, version 2.0 (and earlier), disabling the improvements for all applications which use the Oracle driver. If you do not set the driver flag, you will probably need to change your use of the beginTransaction() method. Under SQL Links, version 2.0 and earlier, all beginTransaction() calls for Oracle databases initiated a transaction with the ReadCommitted isolation level. ReadCommitted was used even if the optional isoLevel parameter was provided. Thus all the following beginTransaction() calls were equivalent and used ReadCommitted: var db Database success Logical endVar success = db.beginTransaction() success = db.beginTransaction("DirtyRead") success = db.beginTransaction("ReadCommitted") success = db.beginTransaction("RepeatableRead") The BDE's new transaction support runs your transaction at a level supported by the server at or above the level you specify. If the server does not support an isolation level at or above that level, an error will be returned. If you specify an isolation level and write your application correctly assuming you have that isolation level, your application will execute correctly or will receive an error. For Oracle databases, db.beginTransaction("DirtyRead") and db.beginTransaction("ReadCommitted") still initiate a ReadCommitted transaction. db.beginTransaction() and db.beginTransaction("RepeatableRead") initiate a read-only RepeatableRead transaction. If your application makes updates during the transaction, you should change these calls to specify ReadCommitted. Otherwise an error will be generated when you attempt to commit the update (or unlock an updated record). If you are using Paradox 4.5 and you use beginTransaction() for an update transaction on an Oracle database, you must set the driver flag. The optional isoLevel argument was added to beginTransaction() in Paradox 5.0. TABLE\INDEX NAME CAPITALIZATION. Under SQL Links, version 2.0 and earlier, Oracle table and index names were always forced to uppercase. This release of SQL Links supports names in mixed case. Previously, SQL Links could be used to access the table CUSTOMER but not Customer, for example. Existing Paradox Forms, Reports, QBE queries, Scripts and Libraries may have refered to these using mixed case. For example, the table CUSTOMER might be refered to as Customer in a Form. Since the names were forced to upper case, this reference was to CUSTOMER. Under this release of SQL Links, the use of a table name in a Form, Report, QBE query, Script or Library which does not match the name on the server exactly will fail. So a reference to the CUSTOMER table which is spelled Customer will fail. You can use your Paradox and Oracle objects unchanged if you set the driver flags for the Oracle driver. See READLINK.TXT. Setting the driver flag will make naming conventions compatibile with SQL Links, version 2.0 and earlier, disabling the improvements for all applications which use the Oracle driver. If you do not set the driver flag, you may either change your Paradox references to match the names on the server (which are probably all upper case), or change the names on the server to match those in your Paradox objects. 5. SYBASE DRIVER ---------------- BEGINTRANSACTION METHOD. Improvements in the Borland Database Engine's (BDE) handling of transaction isolation levels will affect your application if it uses the OPAL Database method beginTransaction(). (See the description of beginTransaction() in the Paradox 5.0 ObjectPAL reference or online help for a description of isolation levels.) If you do not use beginTransaction(), your application will not be affected by these improvements. You can use your application unchanged if you set the driver flags for the Sybase driver. See READLINK.TXT. Setting the driver flag will make transaction operations compatibile with SQL Links, version 2.0 (and earlier), disabling the improvements for all applications which use the Sybase driver. If you do not set the driver flag, you will probably need to change your use of the beginTransaction() method. Under SQL Links, version 2.0 and earlier, all beginTransaction() calls for Sybase databases initiated a transaction with the ReadCommitted isolation level. ReadCommitted was used even if the optional isoLevel parameter was provided. Thus all the following beginTransaction() calls were equivalent and used ReadCommitted: var db Database success Logical endVar success = db.beginTransaction() success = db.beginTransaction("DirtyRead") success = db.beginTransaction("ReadCommitted") success = db.beginTransaction("RepeatableRead") The BDE's new transaction support runs your transaction at a level supported by the server at or above the level you specify. If the server does not support an isolation level at or above that level, an error will be returned. If you specify an isolation level and write your application correctly assuming you have that isolation level, your application will execute correctly or will receive an error. For Sybase databases, db.beginTransaction("DirtyRead") and db.beginTransaction("ReadCommitted") still initiate a ReadCommitted transaction. db.beginTransaction() and db.beginTransaction("RepeatableRead") attempt to initiate a RepeatableRead transaction, but an error is generated because RepeatableRead transactions are not supported. You should change these calls to specify ReadCommitted. If you are using Paradox 4.5 and you call beginTransaction() for a Sybase database, you must set the driver flag. The optional isoLevel argument was added to beginTransaction() in version 5.0.