C Programming Starter Kit 2.0/SamsPublishing-CProgrammingStarterKit-v2.0-Win31.iso/bde/readlink.pak/PDOXSQL.TXT

Text File | 1997-07-24 | 14.2 KB | 371 lines

                    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.