The Gateway ADC uses the structure of the DP4 database combined with the operations requested by the application to generate SQL. The following information is intended to summarise how this is done in the current implementation. It is not guaranteed that future versions will work in the same way - we will improve the SQL if possible. The information in this section is also relevant to the native Oracle and Sybase Gateway ADCs<. However, these may have special handling of some operations, particularly as regards database updates. Information about the other Gateway ADCs will be added to this document in a future edition.
You may find it helpful to refer to the DP4 SQL guide for the meanings of some of the terms used in the following information.
The names, and datatypes associated with the tables and fields on the SQL database are all taken from the corresponding DP4 database, subject to the modifications implied by the various translation and configuration sections of the DP4 Configuration File. The Gateway ADC does not attempt to read any catalog (dictionary) information from the SQL database whatsoever.
The Gateway ADC has no knowledge of any constraints that could be inferred from aspects of the application such as Conditions to List in QAB or Report Writer.
All operations are atomic - for example if a QAB program reads a main table and joins parents, it performs a sequence of DP4 fetches. These are translated into individual SELECT statements on the SQL database.
All operations use all the columns in the table in question. The columns are names explicitly. No attempt to use the * abbreviation is made. (It is possible to equivalent the Oracle and Sybase variants so that this abbreviation is used). INSERT and UPDATE statements specify the columns in the order implied by the col_order setting, though this is immaterial.
The DP4 database API does not distinguish between record
insertions and updates (because it adds needless complexity),
whereas SQL does. This causes problems for the Gateway ADC, which
does not have enough information to decide which is appropriate.
When a DP4 program posts a record, the Gateway ADC will first try
an UPDATE, and if this fails it will try an INSERT. (The alternate
strategy is configurable in the AUXORACL equivalent of AUXWODBC).
When performing an UPDATE a WHERE clause is generated specifying
that every keyfield should be equal to its current value. An
alternative strategy for updates is used when use_checksum=1 is
specified in the [AUXWODBC] section of the DP4 Configuration File.
In this case and update causes a SELECT for UPDATE statement to be
generated, and this will be followed by either an UPDATE or an
INSERT depending on whether or not the SELECT returns a row. From
DP4 C you can use the UPDATE_ONLY flag in calls to
rec_post_with_flags() to force an UPDATE rather than an INSERT
(though this is only relevant to AUXORACL). Unfortunately there is
no means of specifying an INSERT.
DP4 programs typically fetch records one at a time or in small batches. Programs ask for records in an ordered sequence, that is they can ask for an EQUAL, NEXT (next greater), PREV (next less), and FIRST or LAST. The range of records which this sequence can span can either be the whole table, or a subset where one or more leading keys have a fixed value. DP4 databases therefore behave as though there were a number of SQL result sets immediately available, within which a cursor can be moved freely. Unfortunately there is no means of doing any of this in SQL, except by generating the result sets the DP4 fetch needs. In the worst case scenario (which may well arise frequently), AUXWODBC may have to generate a result set containing almost every row in a table, even though only the first row will be used by the DP4 program. In detail DP4 fetches are handled as follows:
The DP4 index specified will be used to generate a WHERE and an ORDER BY clause using the fields contained in the index. However, for secondary indexes the situation is slightly more complicated AUXWODBC will expand the key set to include all the primary keys of the table: an EQUAL fetch on a secondary index is treated as though it was a FIRST_BUT on this expanded index (which may of course not exist on the DP4 database). This treatment of the secondary indexes is necessary to ensure that a fetch(NEXT) on a non unique secondary index is possible after a rec_fetch_main(), an operation frequently performed by DP4 C programs).
The WHERE clause will specify that the appropriate number of
keyfields should match their current value (i.e. use predicates of
the form fieldname=value) ,according to
the type of fetch:
EQUAL - all keys from the DP4 index used must match.
other directions - no keys have to match unless DEPTH or BUT is specified, in which cases the number of keys specified for the depth if the DEPTH flag is specified, or implied if the BUT flag if is used must match the current value.
For remaining keys there WHERE clause contains a condition which
expresses that records to be retrieved must succeed this record in
the sequence implied by the DP4 index used. For a single part key
this will be a simple condition such as WHERE
key>value. For multi-part keys the
condition will be more complex , and will take the form WHERE
key1>value1 or (key1=value1 and
key2>value2). This part of the WHERE clause
will not be generated except for NEXT and PREV fetches. There is
currently no constraint to limit the number of records returned
other than this. Thus an isolated fetch(NEXT) will generate
WHERE key>value (potentially a huge
number of records) and not WHERE
key>value and key<value2.
There is no reliable method that we know of by which AUXWODBC could
generate such an additional constraint desirable though it might
be. In simple cases (where there are single part keys) using MAX or
MIN column functions might be possible. However there is no
guarantee that that these operations are implemented efficiently by
the SQL database. Some databases may support using a special column
such as ROWID to allow the number of rows returned to be limited,
but we have not investigates this in detail yet.
An ORDER BY clause is generated specifying all the keys which can vary. An ORDER BY clause is only not generated for calls to rec_fetch_main(EQUAL,&table.l). Ascending order will be specified for FIRST and NEXT searches, Descending order for PREV and LAST fetches.
Whenever a fetch is performed AUXWODBC will try to avoid generating a new result set if there is an unused row from a previous result set which will contain the correct row. For example following a fetch(FIRST) the correct rows for a fetch(NEXT) on the same table will be in the result set generated for the fetch(FIRST). This is only possible where the same index is used in both cases. The DEPTH does not necessarily have to match, but where it is different, an additional SELECT statement may have to be generated once the result set is exhausted. Result sets are always discarded by commit() or decommit() operations.
The same result set cannot be used for both PREV and NEXT searches. Therefore for pick_record() calls in an application (QAB Windows), using the mouse or cursor keys to move round the window may force AUXWODBC to generate a second large result set. For this reason it is highly desirable in C code to use the buffered fetch functions, and with a reasonably large buffer, to minimise the extent of this problem.
The above explanation of WHERE clauses ignores the question of
fields that contain values that would be regarded as NULL in a DP4
database. If a fixed part of a key contains a value that would be
NULL on a DP4 database then instead of the predicate
key=value the predicate key
is null is generated instead. The nullity or otherwise of
field values is ignored for keys that can vary, and will generate
predicates involving > or <. Potentially this might cause
problems. For example a correct SQL implementation will
never return a record with a null key, where that key was
used in a predicate with any relational operator, because no
comparison involving a null field can ever succeed except "is
null". On the other hand, where no predicate was specified for a
key, rows where that key is null may be returned either at the
beginning or the end of the result set depending on the database.
Thus where there are records with null keys the results of a fetch
may not be clearly defined. Also a DP4 database will never allow
two records to be created with identical keys, but some SQL
databases might well (and legitimately) allow this if null primary
keys are permitted.
Problems with nulls may be exacerbated for databases which treat certain values as null values incorrectly. This is particularly troublesome on character fields. Many databases automatically strip trailing blanks from character fields, and regard a blank character field as being null. This is incorrect. DP4 treats a blank key as being different from a null key, but the same may not hold true for all databases. As a result fetches on blank character keys may also be problematical.
You are therefore recommended to avoid creating records with null and blank keys as far as possible. In some cases it may be necessary to use a special value (such as '_' that the SQL database does not regard as null instead of leaving fields blank, though this may of course cause problems of its own.
The distinction between the various types of character fields may not be maintained by the ODBC database source. Also the character collate sequence may not match what it would be on a DP4 database. For general remarks on constraint checking see Limitations. An important point to bear in mind is that DP4 databases do not perform range checking on the data in the fields at update time. This aspect of referential integrity is handled by the DP4 terminal manager and the application. Thus a DP4 database will not complain if a type N field contains a bit pattern that does not correspond to a valid number. Most importantly, DP4 databases allow any bit pattern to be placed in character fields, and these are therefore sometimes used to hold binary data. Many databases will range check data being posted to them, and some will reject updates where character fields contain "invalid" characters.