How to Use This Guide

This guide describes how to install, configure and use four SQL-related products:

  • The DP4 Gateway ADCs (Auxiliary Database Controllers)

    On Microsoft Windows platforms the ODBC Gateway ADC product offers DP4 applications seamless access to foreign (non-DP4) databases that are compliant with the ODBC interface. The Oracle OCI Gateway ADC perfroms the same function for Oracle databases using the native Oracle interface, and is available on both Windows and Unix platforms.

  • The DP4 ODBC Drivers

    Under Windows and Windows NT this product allows third-party applications that support the ODBC standard to access DP4 databases.

  • The DP4 SQL interface

    This product, available on many platforms, allows you to submit SQL statements to the DP4 SQL Engine to query and update data on DP4 databases, either interactively or from a batch mode program that can be called from other programs.

  • The DP4 DDE interface

    Under Windows 3.1 this product (now just part of the SQL interface supplied for Windows 3.1) allows third-party applications that support the DDE standard to access DP4 databases.

The DP4 ODBC drivers and the SQL and DDE interface all ultimately depend on the DP4 SQL Engine. The SQL language understood by this component, and details of how SQL statements are translated into calls to the DP4 database manager are documented in the DP4 SQL Engine Reference.

Most of the examples in this manual use the SALESORD Database.

Suggestions for improvements or corrections to this manual are welcome. Find out how to comment here.


What's New in the DP4 SQL Products Guide

This page will tell you about recent changes to this manual. The most recent changes are mentioned first.

New in 4.621

  • 06 Sep 2004 Corrections to description of read-only operation. If you want to create a read-only ODBC connection, you may find article T40000006 on the DP4 web site helpful.

New in 4.620

  • 14 Feb 2004 The description of the process of setting up ODBC data sources for DP4 databases has been updated.

  • 26 Apr 2002 Information about using DP4 ODBC from Microsoft Access updated (this is not 4.620 specific).

  • 28 Nov 2001 Information added about SQLMAKE. Information on diagnostics, and networking considerations for Gateway ADCs added.

New in 4.619

  • 26 Jun 2001 New configuration options for Gateway ADCs in 4.523.This information is actually mainly in the Guide to DP4 Configuration.

  • 21 May 2001 Information added describing the compilation hints introduced for version 4.523.

  • 16 February 2001 Information relating to the Installation and Configuration of the Oracle Gateway ADC has been added, and the section relating to the Gateway ADC configuration options improved generally.


The SALESORD Database

The examples in this manual are based upon the dealings of a fictitious company, Acme Sales Order Plc, which sells a number of products throughout the United Kingdom. An example database called SALESORD is supplied on which you may experiment with the database utilities. SALESORD stores information on Acme employees, customers, orders and supplier’s materials. Its structure is illustrated below. See below further information.

SALESORD Entity Relationship Diagram

The principal tables in the SALESORD database are as follows:

EMPLOYEE

Stores details of Acme employees who manage customer sales regions and customer accounts, including the employee’s name, gender, company grade and date of birth. The primary key is a unique number that identifies the employee

REGION

Stores details of the UK regions where customers live, including the name of the region and the company employee managing the region. The primary key is a unique code that identifies each region

CUSTOMER

Stores the customer’s name, address and region code, credit arrangements and balance. The primary key is a unique number that identifies the customer

ORDER_HEADER

Stores the details of the date and time the order was placed, its value and customer payments made against it, delivery details, customer number and code of the Acme employee responsible for the account. The primary key is a unique number that identifies each order. A customer may have several order header records, each of which may have several order lines giving materials and quantities ordered. An order header record may also have several order payment records

ORDER_LINE

Includes one record for each different material in an order. ORDER_LINE contains the order number, a line number that identifies an order line, the material code and quantity ordered, together with the value of the order line. The order number plus line number form the primary key that identifies each record

ORDER_PAYMENT

Includes one record for each payment made against an order. ORDER_PAYMENT records the order number, a payment number that identifies separate payments, the media code (cheque, direct debit, credit card), the amount paid, the card number and payment date and time

MATERIAL

Stores details of each material including its code, description, unit price, quantity in stock and re-order level together with the code of the principal and the alternative supplier of the material. The material code is the primary key to the MATERIAL table

SUPPLIER

Stores the company’s name and address. The primary key is a unique number that identifies the company


How to Comment on this Help File

Our aim is to provide you with top quality documentation. In order to do that, we need your help. Feedback from you is the best way we have of measuring the quality of our documentation, so comments, criticisms and suggestions on our manuals are greatly appreciated.

If you are familiar with HTML you can use the view source option to get an editable copy of each page. You are welcome to e-mail us with edited versions of pages of this or any other DP4 manual. You can contact us at CustomerCare@itim-ts.com.


The DP4 Gateway ADCs

This part of this manual describes the DP4 Gateway ADCs (Auxiliary Database Controllers), and particular the ODBC Gateway ADC for Microsoft Windows platforms. This product offers DP4 users and applications seamless access of foreign (non-DP4) databases, compliant with the ODBC interface. Additionally specially tailored Gateway ADCs are available to operate with Oracle and Sybase using those databases' native interfaces, but these are not part of the standard DP4 product and must be purchased separately.

A basic knowledge of DP4 is assumed, including an understanding of fields, tables, roles, key fields, indexes and other relational database concepts.

Information on the Gateway ADC is contained in the following topics:

  • Overview of the Gateway ADC: ODBC Version or Oracle Version
  • Installation and Configuration (ODBC) including:
    • Adding ODBC data sources
    • Running the installation aid program (which creates configuration for AUXWODBC and helps you to create a DP4 database corresponding to the ODBC data source)
    • Creating the DP4 database
    • Creating an OEM database from a DP4 database
  • Installation and Configuration (Oracle) including:
    • Creating Oracle Schemas from DP4 databases
  • Gateway ADC Configuration Options
  • Running the Gateway ADC
  • Implementation Details (Contains information on the generated SQL)
  • Gateway ADC Limitations


Overview: The ODBC Gateway ADC

Overview of the ODBC Gateway ADC

If you would like the Oracle version of this page, please click here.

The diagram on the right shows the interaction of the software components, both DP4 and non-DP4 when using the ODBC Gateway ADC.

As with all schemes in which an Auxiliary Database Controller (ADC) is utilised, the logic of the application is separated from the logic of server selection; the DP4 application is unaware that it is accessing information from other than a local DP4 database. When the application wants to access the database it makes its requests of the local DP4 database manager, SRVW or DP4SRVR.W32. A separate program, the DP4 ODBC gateway ADC, intercepts the database accesses, creates SQL statements and directs them to the ODBC driver program. Typically provided by the foreign database vendor, the ODBC driver is analogous to the DP4 database manager and accesses data from its associated ODBC data source. An ODBC data source includes the data the application wants to access and the information needed to get to that data. Two examples of data sources are: an SQL server database, the server on which it resides and the network used to access that server; a directory containing a set of dBASE files.

A DP4 database must also be present for each ODBC data source that is to be accessed. While the DP4 database does not hold any data, it must have the same table and field definitions as its associated ODBC data source. The reason for this is that the ODBC data source may have an incompatible data dictionary, and so the structure information for fields and tables is always taken from the base dictionary of the DP4 database. You may wonder how DP4 determines that base dictionary tables should be accessed on the DP4, rather than the foreign, database. This is due to the DP4 "local/remote" mechanism. That is, when the ODBC gateway is loaded, base dictionary tables are, by default, marked as local and fetched from the DP4 database and application tables containing data are, by default, marked as remote, and fetched from the ODBC data source. This can be overridden by use of entries in the DP4 Configuration file.

The ODBC gateway ADC constructs SQL statements, for accessing the ODBC data source, based on DP4 table names and fieldnames.As you might expect, some of the names on the DP4 database may be invalid on the ODBC data source and the converse is also true. To cater for this, you can define a translation table that converts from one set of names to the other.

It should be noted that the generated SQL statements are constructed purely as a translation of the DP4 function being requested. The Gateway ADC has no knowledge of what the application is trying to do. This means that the SQL generated may not be optimal. For example the Gateway ADC has no knowledge of the constraints the application may be using to filter the records it is fetching. Thus a bigger result set may be generated than is required. A particular problem is there is no way in SQL of fetching records one at a time. This means that many operations which are very efficient on a DP4 database are inefficient against a SQL database. Problems are likely to show up particularly on large databases. See Implementation Details for more information.


Installation and Configuration of the Gateway ADC

Prerequisites

The DP4 ODBC Gateway ADC requires:

  • An operational DP4 for Windows 3.x or Windows 9x/NT installation. If this is not available, refer to the appropriate DP4 installation guide for help on installing DP4 for your environment.
  • The Microsoft ODBC driver manager.
  • ODBC driver(s) for the foreign database(s).

Components

The ODBC Gateway ADC files are contained on the DP4 CD-ROM. You can install the required files from there by selecting the "ODBC Client Interface" product from within the DP4 Install program appropriate to your version of DP4.

Windows 3.x Files | Windows 9x/NT Files | Description
AUXWODBC.EXE | AUXWODBC.W32 | DP4 ODBC Gateway ADC
AUXWINST.EXE | AUXWINST.EXE | Installation aid program

How to Install, Configure and Use the ODBC Gateway ADC

Before you can use the Gateway ADC to access non-DP4 databases you must perform the following tasks, which are described in more detail in the linked topics:

  1. Either Install the ODBC Client Interface product appropriate to your release of DP4, or copy the appropriate files in the list above from the DP4 CD-ROM, into an appropriate directory - that used for other DP4 programs is recommended.

  2. Add the ODBC data source(s).

  3. Run the DP4 installation aid program, AUXWINST, separately for each ODBC data source added in step 2. Each time the installation program runs it optionally produces two files:

    • A COPYDB-format file for building a DP4 database that structurally matches an ODBC data source;

    • An .INI file that contains initialisation information enabling a DP4 database built from the COPYDB-format file to work with the ODBC data source.

  4. If necessary, run the DP4 utility MAKEDB and create the empty DP4 databases to be used with the ODBC data sources. With a text editor, review and, if necessary, amend any COPYDB-format files so that they will post without errors. Run the DP4 utility COPYDB and post the structure files to the empty DP4 databases. See Using the COPYDB-Format Files.

  5. With a text editor, review and, if necessary, amend the .INI files created by AUXWINST, and merge them with the DP4 configuration file. See Gateway ADC Configuration Options.

  6. Follow the instructions appropriate for your version of DP4 for loading the AUXWODBC ADC. See Loading the Gateway ADC.

The above instructions assume that you are going to be creating a DP4 database to match an existing non-DP4 database. It is possible that instead you want to create a non-DP4 database to match an existing DP4 database. There are two possibilities here:

  • We supply a program (SQLMAKE) that generates a SQL data definition language file for the database. Provided the other database engine can understand DDL you can create a database using this file. It is possible that you will have to edit this file to get it to post to the other database, because of problems with reserved words and so on. The use of SQLMAKE is described in more detail here.

  • The other database software may have a facility to import a database structure from another ODBC database. In this case you should be able to use that facility to create your database from the DP4 database. This process is described for Oracle in the installation notes for the Oracle Gateway ADC.


Adding ODBC Data Sources

The Microsoft ODBC driver manager and foreign database driver give you the capability to add ODBC data sources. Most foreign database vendors bundle the Microsoft ODBC driver manager along with their own ODBC programs. It follows that if you have installed a foreign database with support for ODBC, it is likely that the Microsoft ODBC driver manager is already available on your system. In attempting to locate this program, open the Control Panel and check whether this icon is there (or a similar one - a slightly different icon is used on Windows 98,Me and 2000):

On Windows Me you may have to select the option to "Show all Control Panel Options". On Windows 2000 and Windows XP this part of Control Panel has been hidden inside the "Administrative Tools Option - it will definitely be present). If you cannot find this option, you may find an icon to run it in one of the program groups created by the foreign database vendor. For example, the "Oracle7 ODBC" group contains an icon entitled ODBC Administrator that runs the Microsoft ODBC driver manager.

As mentioned earlier, the other essential component is the ODBC driver for the foreign database. Although this is always supplied by the foreign database vendor, third-party vendors often provide alternate ODBC drivers with fuller support for the ODBC interface.

Assuming that the ODBC drivers are installed, you can use the Microsoft ODBC driver manager to define one or more data sources for each driver, as follows:

  1. Run the Microsoft driver manager by double-clicking the ODBC icon in the Control Panel or by double-clicking the appropriate icon. The Data Sources dialog box appears:

    This dialog enables you to add, delete or configure data sources. (It may also enable you to install new ODBC drivers on your system.) The data sources defined for all the currently installed drivers are listed in the Data Sources (Driver) list

    There are seven buttons:

    Option (W32)

    Option (W16)

    Description

    Add

    Add

    Allows you to add a new data source to the list

    Remove

    Delete

    Deletes the data source highlighted in the list

    Configure

    Setup

    Allows you to change the configuration of the data source highlighted in the list

    OK

    Close

    Closes the dialog box, confirming any changes you have made

    Cancel

    -

    Closes the dialog box, cancelling any changes you have made

    Apply

    -

    Closes the dialog box, forcing any changes you have made to take effect immediately

    Help

    Help

    Takes you into help screens

    -

    Drivers

    Allows you to install new ODBC drivers or delete existing ones. (In 32-bit Windows this is installed by application software.)

    -

    Options

    Allows you to specify how the ODBC driver manager traces calls to ODBC functions. (In 32-bit Windows select the Tracing tab.)

  2. You can use any kind of data source for AUXWODBC. However, it is probably best to use a System DSN where possible. After selecting the type of DSN to use, click Add to add a new data source. The Add Data Source dialog box appears:

    This dialog lists the currently installed ODBC drivers

  3. From the list, select the driver that the data source will use - for example, SQL Server. Click Finish or OK. The appropriate driver dialog box appears, for example:

    Fill in the data source details. In the above example, the SQL Server driver requests you to name the data source, to describe the data in the data source and to name the SQL server from which the driver will retrieve data.
  4. Having defined the data source, click OK to return to the Data Sources dialog box. The new data source should now be listed in the Data Sources (Driver) list.
  5. Repeat the above procedure to add all the desired data sources


Running the Installation Aid Program

The installation aid program, AUXWINST, helps you to configure the DP4 ODBC gateway ADC for accessing the ODBC data sources. You can only run AUXWINST if three conditions are met:

  • The DP4 ODBC Gateway ADC programs reside on your system (see step 1 of the Installation Summary);
  • The ODBC data sources are defined (see Adding ODBC Data Sources);
  • The foreign databases corresponding to the ODBC data sources are running and available.

When you run the installation aid program:

  • It will ask for the name of a DP4 database;
  • It will ask you to select the ODBC data source to be accessed with the DP4 database;
  • It will ask further questions to enable a connection to be established with the ODBC data source (such as your user name and password for the foreign database).

The installation program will optionally create:

  • A COPYDB-format file - once you have amended this file you can post it to an empty DP4 database to create one with the same structure as the ODBC data source;
  • A .INI file - this contains all the required initialisation information (including the name translation table) to enable the DP4 database based on the COPYDB-format file to be accessed.

Some final points to consider before running the installation aid program are:

  • It does not require the DP4 database manager;
  • It must be run separately for each ODBC data source to be accessed;
  • It may be re-run at any time to alter the configuration. For example, if the name of the data source is changed after running the installation aid program, you must re-run it and select the data source under its new name.

Instructions for using the installation aid program:

  1. Start the program (AUXWINST.EXE). The program presents its opening screen:
  2. Enter the name of the DP4 database to be used when accessing the data source, for example "Catalog" - the database need not exist at this time. Click OK. (If you want to leave the installation aid program, click Cancel instead.) The SQL Data Sources dialog box appears, listing all the ODBC data sources defined for the currently installed ODBC drivers
  3. Select the data source for the DP4 database you named in step 2, for example "Sample database", and click OK. The dialog that appears now depends on the foreign database to be accessed. For example, the Oracle logon dialog requests your database username and password:
  4. Answer any questions and click OK

    Any SQL errors that occur here are due to the failure of the installation aid program to establish a connection with the foreign database. Two common reasons for this are: the data source is incorrectly configured (see Adding ODBC Data Sources to reconfigure the data source); the foreign database is shutdown or unavailable (check the status of the foreign database)

    The Select COPYDB File Name dialog box appears

    This dialog allows you to specify the name and location of the COPYDB-format file to be created. (If you are going to create the DP4 database without using a COPYDB-format file, click Cancel to skip the creation of this file). Take care not to overlook three important check boxes on the right-side of the dialog:

    • Convert date/time to timestamp - Enable this option to convert 'date' or 'time' fields on the ODBC data source into a pair of date and time fields on the DP4 database. This conversion is useful when the ODBC data source has a data type described as 'date' which is actually a combined date and time

    • Convert numeric to char - Enable this option to convert 'numeric' fields on the ODBC data source into character fields on the DP4 database. This conversion is useful when the ODBC data source stores numbers in an internal format that the ODBC driver cannot translate into the standard floating-point format - this is true for Oracle databases

    • Names are case sensitive - Enable this option if names on the ODBC database are case sensitive. If this option is disabled, no name translation entries are generated in the .INI file for names that differ only in their case

    Be aware that the installation aid program treats any data structures on the ODBC data source that are composed of fields and records but not classified as tables, such as Views, as if they were tables

  5. Select the desired conversion options, use the Network, Drives, Directories and File Name boxes to select the location and name of the COPYDB-format file to be created. (Leave the List File As Type as "COPYDB Files" to add the extension .PRN to the filename automatically). Click OK. The Select INI File Name dialog appears:
  6. Using the Drives, Directories and File Name boxes, select the location and name of the .INI file to be created. (Leave the Save File As Type: box as "INI Files" to add the extension .INI to the filename automatically). Click OK. AUXWINST takes a few seconds to complete its processing and then displays the message: Installation complete
  7. Click OK to exit the program.


Using the COPYDB-Format Files

If you opted to create COPYDB-format files while running the installation aid program, you may have to edit them into a format suitable for posting to their DP4 Databases. This is because some ODBC data sources do not have key fields and, worse still, some ODBC drivers, for example Oracle's, are incapable of providing the installation aid program with keyfield information. In either case, you must edit the COPYDB-format file with a text editor and add this information yourself.

The most efficient way of finding out whether a COPYDB-format file requires editing is to post it to its DP4 database - see later in this section.

Adding Key Field Information

Suppose, for instance, an Oracle BONUS table had a two-part primary key comprising ENAME+JOB, the relevant lines of the COPYDB-format file might omit this key information as follows:

#reldata name="BONUS" flags=0 read=0 write=0 last_change=0
________________
Bonus
________________
#field_descr name="ENAME" type=C length=10 occurs=1 read=0 write=0
format=0 iotype=C,
def=""
#relfield name="ENAME" key=0 sequence=5
________________
Ename
________________
#field_descr name="JOB" type=C length=9 occurs=1 read=0 write=0 format=0
iotype=C,
def=""
#relfield name="JOB" key=0 sequence=10

To rectify this omission, you would edit the key precedence to key=1 for ENAME and key=2 for JOB.

Changing Fieldnames

At times, you may wish to change the field names, either by editing the COPYDB-format file or by using the DP4 utility MAKEDB after you have built the database. If you do this, you must alter any corresponding name translation entries in the .INI file (see Using the .INI Files ).

Posting the COPYDB-Format File

Once you have made the desired changes to the COPYDB-format files, use the DP4 utility MAKEDB to create empty DP4 databases for them. It's important that the names of the DP4 databases match the names you specified when running the installation aid program. If you cannot recall the names, you can view the information that describes the correspondence of a DP4 Database name with an ODBC data source name in the [sql] section of the appropriate .INI file (see Configuring the ODBC Connection in Using the .INI Files ).


SQLMAKE

SQLMAKE is a program which creates a text file of SQL statements that will create a database with approximately the same structure as the starting DP4 database. It can cater for problems caused by SQL reserved words being used as field or table names on the DP4 database, and also for name changes necessary because the target SQL database has a limit on the number of characters permitted in identifiers.

To run SQLMAKE you specify the database name in the usual way, and any of the numerous command tail options as listed in the help for the program. Two text files are used by the program to help generate the SQL, which you can edit to change the program behaviour:

  • The file reserved.sql contains a list of reserved words on SQL followed by an alternative name to give a field,table or role that uses that name. This file is not guaranteed complete by any means, and the alternatives may not be to your liking, so do edit the file.

  • The file dtypes.sql contains a list of the DP4 datatypes, with SQL equivalents. The SQL types used by default are somewhat lowest common denominator - for example no attempt is made to use types such as BYTE, BIT(1), or VARCHAR, all of which may be more approriate for your needs. SQLMAKE will always specify a length for character fields, but never for other fields. Therefore for types such as dates and times you may specify any other properties you wish for the field. You may need/want to change the types for dates and times.

The program produces two output files:

  • dbname.sql contains the SQL statements to create the database.
  • dbname.ini contains suitable [sql.xlat dbname] and [sql.xlat2 dbname] for using the database with the DP4 Gateway ADCs or ODBC drivers

Notes

You can use the -limit n command tail to specify a limit on identifier length. The default is 64, or 30 if the -oracle option is specified, whereas in previous versions it was 18. SQLMAKE reduces identifier length where necessary by eliminating underscores, then vowels, then repeated consonants, then 'S' and 'R', and finally constructing a name based on the internal number of the table/field. By default the same shortened name will always be used for each table, role, or field, but apart from that the minimum amount of reduction will be done.

Two options radically affect the amount of name translation that may be required (reducing name translation may be beneficial if you want to be easily port SQL between a DP4 and SQL database)

  • A new option -index_anonynmous is added. This causes create index statements to use names based on the internal numbering of the DP4 database, not the names. This can dramatically reduce the number of entries in the name translation tables.

  • A new option -avoid_translation is added. When this option is selected, instead of always using the shrunken version of a database name (especially for fields and roles), the name is only shrunk where necessary. This can cause the fields to have different names on the SQL database where they would have the same name on the DP4 database, but it is harmless and reduces the number of entries in the name translation tables.

SQLMAKE generates CREATE SEQUENCE statements for auto-increment tables when the -oracle option is specified. The name for the sequence can be controlled using the -sequence_format command tail - normally you would specify either "%s_SEQ" or "SEQ_%s".

SQLMAKE does not currently try to specify a precision for numbers, or impose any constraints on the fields (other than that primary keys should not be null). No doubt it would be possible to generate more sophisticated SQL. If further enhancements to this program would be useful to you please let us know what your requirements are.

If you specify the -oracle option the default role separator is changed to _, which is the default for AUXORACL. In this case it is possible that two distinct fields in a table may be given the same name. If you use a $ role separator this cannot happen.

There is a known limitation in the way CREATE INDEX statements are generated: SQLMAKE ensures all indexes are unique by adding in components from the primary key of the table after any secondary key components. In some cases this can result in two indexes being created for the same set of fields. Oracle treats this as an error. The error is harmless, but you can avoid it by switch off one or other of the indexes using SETLINK. If this error arises it is an indication that you can improve your DP4 database by switching off the index with fewer key fields and updating your programs to use the other index. After reorganisation your database will be smaller and updates to affected tables will be faster.

Please make sure the RESERVED.SQL and DTYPES.SQL file are present in the working directory for SQLMAKE.


Installation and Configuration of the Oracle Gateway ADC

Prerequisites

The DP4 Oracle Gateway ADC requires:

  • An operational DP4 for Windows 9x/NT installation, or a DP4 for Unix installation. If this is not available, refer to the appropriate DP4 installation guide for help on installing DP4 for your environment.
  • The Microsoft ODBC driver manager.
  • Oracle Client software, including OCI and SQL *Net.
  • Access to an Oracle Server with SQL *Net

You must be using at least version 7 of Oracle. Oracle versions 7 and 8 are currently supported.

Components

The Oracle Gateway ADC will be supplied on diskette or via e-mail. The ADC is a single file, called AUXORACL.W32 for the Windows version, and auxoracl for the Unix version.

How to Install, Configure and Use the Oracle Gateway ADC

Before you can use the Gateway ADC to access Oracle databases you must perform the following tasks, which are described in more detail in the linked topics:

  1. Copy the AUXORACL.W32 or auxoracl file into an appropriate directory - that used for other DP4 programs is recommended.
  2. If you have an existing Oracle database which you want to access from DP4, then you can use the "installation aid" program (AUXWINST) included with the ODBC Gateway ADC to creata a COPYDB-format file suitable for creating a corresponding DP4 database. Refer to Installing and Configuring the ODBC Gateway ADC.
  3. If, on the other hand, you have a DP4 database for which you want to create a corresponding Oracle database you need to create an Oracle Schema from the DP4 database.
  4. Update the DP4 Configuration File as necessary to select configuration options for AUXORACL.
  5. Load the AUXORACL ADC, as described in Using the Gateway ADC.


Creating Oracle Schemas from DP4 Databases

To create an Oracle schema you can use Oracle's own 'Database Designer' product. To use this, you will need your DP4 database set up as an ODBC data source, and to have installed the DP4 ODBC Drivers.

Use the Reverse Engineer Wizard to retrieve the information from the DP4 database as an ODBC database. To produce a schema more suited for use with the DP4 Oracle Interface you need to have the following entry in your DP4 Configuration File:

[odbc]
oracle=1

When you have produced the data diagram, you simply use the Generation Wizard to produce the schema file.

Problems with Reserved words

If the DP4 database has any field, table, or index names that are Oracle reserved words, the schema produced by the Database Designer will contain these illegal names.

If you know that some of these names exist, edit the schema file by hand. Make a note of the names that need changing and the changes you make, so that this information can be included later in a translation table for the DP4 Oracle Interface.

If you do not know whether your DP4 database uses Oracle reserved words, we recommend that you post the schema file into Oracle, watching for errors. These will indicate that you are trying to create a schema object using an Oracle reserved word. If you do find any reserved words, as before, rename them to allowed words, making a note of changed names for inclusion in a translation table later.

You can use the DP4 program RESWORDS to add a list of reserved words to a database to prevent them being used as table or field names. We can supply files ready for posting with RESWORDS that contain the official list of SQL reserved words (there may be some more for Oracle).


Gateway ADC Configuration options

The Gateway ADCs have a great many configuration options that are specified in the DP4 configuration file. Most of these are set on a per-database basis, though for some you can also set default values to be used for all databases. AUXWINST (the installation aid program for the ODBC Gateway ADC) will create a .INI file whose sections can be merged into your existing DP4 configuration file. Although geared to AUXWODBC this program may be useful for creating the corresponding information for the Oracle Gateway ADC.

Although AUXWINST will generate some of the information needed for the DP4 configuration file, it is very likely that you will want to edit this information, or override the the default value for one or more configuration options. For a full technical specification of the DP4 configuration file entries please refer to the appropriate sections of the Guide to DP4 configuration. Most of the links on this page are to relevant pages in this document, and will not work if it is not available.

The information in the .INI files created by AUXWINST is for the following sections:

  • [sql] - this section is mandatory and contains one or more entries specifying the DP4 database to ODBC data source (or Oracle) connection details. When using AUXOWDBC you may never need to edit this section because you can run the installation aid program to specify new DP4 to ODBC data source connections and to amend existing ones. When using AUXORACL you must set this information up - the information set up by AUXWINST is not in the correct format.
  • [sql.config DP4_database_name] - this section allows you to configure the Gateway ADC on a per-database basis. The options that are configurable vary according to which Gateway ADC is used.
  • [sql.remote DP4_database_name] - this section allows you to override the default local/remote table scheme and specify whether tables are held on the non-DP4 database or the DP4 database.
  • [sql.xlat DP4_database_name] - this section allows you to specify name translation between a DP4 database and another database. Entries take the form: DP4_name=sql_name. The Gateway ADC uses this entries in this table to decide on the appropriate column name for a field on the DP4 database
  • [sql.xlat2 DP4_database_name] - this section allows you to specify name translation between another database and a DP4 database. Entries take the form: oem_name=DP4_name. The DP4 SQL Engine uses entries in this table to translate names submitted to it. Entries in this table can be used to work around problems caused by third party software submitting incorrect column names to the DP4 SQL Engine.

Use a text editor to merge the .INI file information generated into the DP4 configuration file.

As well as these sections entries in either [AUXWODBC], [ORACLE] or [SYBASE] sections sections are used to configure the various Gateway ADCs. There are also several options in the [sql.config dbname] section that you can use to change the deafult behavior of the Gateway ADC in various ways, and you may wish to change some values in other sections as well (for diagnostic purposes for example). The [sql] page has a useful summary of how all the various SQL related sections are used by different DP4 components.

Translating Names

The DP4 ODBC gateway generates table and field names for the SQL database which, by default, are the same as the names on the DP4 database.

In the case of fields which have a role, the SQL field name is "FIELD_ROLE". Multi-occurs fields are treated as several separate SQL fields with names, such as "FIELD_ROLE_0", "FIELD_ROLE_1" and so on.

If the names on the SQL database are not the same as on the DP4 database, you must supply a translation table to translate the DP4 names to the SQL names. This table is set up in the DP4 configuration file, in the [sql.xlat DP4_database_name] section.

Each entry in the translation table takes the form DP4_name=sql_name where DP4_name is a table or field name formed as described above, and sql_name is the name into which it is to be translated.

In DP4, names must be unique within the set of table and field names on the database. This may not be so in the foreign DBMS. However, the installation aid program detects duplicate names and automatically creates translation table entries to accommodate them, using the following naming convention:

sql_name@1=sql_name for the first duplicate
sql_name@2=sql_name for the second
sql_name@n=sql_name for the nth

Note that the installation aid program changes any duplicate names in the COPYDB-format file to reflect the translation table entries it creates.

In DP4, names may be up to 16 characters in length. The installation aid program detects any names in the foreign database that exceed this limit and automatically creates translation table entries as follows:

truncated_sql_name=sql_name_over_16_characters_long

If the resulting truncated_sql_name would not be unique, in the same way as before, @1, @2 and so on is appended to the duplicates.

Entries in the [sql.config DP4_database_name] section can be used to modify, role and field name order, role separator choice and occurs starting number.


[SQL]

There are currently four products that may make use this section.

  • The SQL interpreter uses this section for replacement [trmw] entries in the same way as other DP4 programs

  • Three ADCs that implement the DP4 database as a client of OEM databases.

    • Auxwodbc
    • Auxoracl
    • Auxwsybas

The AUXWODBC ADC (Auxiliary Database Controller) provides seamless access to foreign ODBC compliant Databases. The necessary information for the .INI files can be created by AUXWINST, and is categorised below. Fuller installation details are contained in the SQLODBC help file.

The AUXORACL ADC provides access to Oracle databases, using native Oracle calls rather than the ODBC interface. Further information is contained in the DP4 SQL Products manual.

AUXSYBAS ADC is a similar product to AUXORACL, but for Sybase databases.

Entries in the [SQL] section

The DP4 configuration file should contain one [sql] section with one or more lines, each of which specifies a connection between a DP4 database and an OEM data source. The format of these entries is dependent on which of the above ADCs is used to access that database, and, for AUXWODBC, the type of ODBC source.

For example, an ODBC connection to a Microsoft database using auxwodbc might be specified like this:

DF7=DSN=Sample database;DBQ=MS7;UID=SCOTT;PWD=tiger

Where ;DF7 is the DP4 database name, Sample database is the ODBC data source name, MS7 is the name of the foreign database, SCOTT is the user identity for the database and tiger is the password. The DP4 database and ODBC data source names always appear as in the above example, but other details will vary depending on the ODBC server.

For Oracle and Sybase (using the native Gateway ADCs rather than the ODBC Gateway) the connect string will have the following form:

db=user/password@service

For example: df7=scott/tiger@ora

Associated entries in other general sections:

[STARTUP]

The startup section will include a command to load the appropriate ADC, for example as follows:

[startup]
1=dp4srvr.w32 -aux
2=auxoracl.w32

[DEBUG]

The debug section has an entry to allow debug output from AUXxxxxx to be displayed in the errlog program and/or to be output to a file. The format is as follows:

[debug]
debug_auxsql=n

where n is 0, 1, 2, or 3

This has the usual format for being set to 1 for on and 0 for off. If set to 2 or 3 more detailed diagnostic information is produced.

Note that turning on diagnostics should only be done during problem investigation. These diagnostics may significantly degrade performance.

Each of these ADCs may also make use of the following sections:

  • [sql.config DP4_database_name]
  • [sql.remote DP4_database_name]
  • [sql.xlat DP4_database_name]

The DP4 SQL Engine uses the following section, which may be useful when converting a system to use DP4 rather than a non DP4 database:

  • [sql.xlat2 DP4_database_name]

There are also product specific sections for each ADC:

  • auxwodbc [AUXWODBC]
  • auxoracl [ORACLE]
  • auxsybas [SYBASE]


[sql.config DP4_database_name]

This section allows you to configure the ODBC gateway server (AUXWODBC) for specific ODBC connections.

Where DP4_database_name is the DP4 database name, not the OEM database name, should that be different.

It can have the following entries

  • first_occurs ◊
  • role_first
  • role_sep ◊
  • single_query
  • statements
  • timestamps
  • use_long_query ◊
  • use_subquery ◊

Several of the options above (marked with a ◊) are per database versions of values in the AUXOWDBC section. The defaults on a per database level are the actual values of the AUXWODBC variables. This allows you to specify these values separately for each database.

This may be useful if you have several OEM databases created in different ways or with different capabilities. For example if you create an Access database from a DP4 database and the DP4 database contained a field staff_name with a role of manager, the Access database name will contain a field called manager$staff_name. So for this database you would specify role_sep='$' and role_first=1, which will allow you to use the existing DP4 database to access the Access database. Otherwise you would need to use AUXWINST to create a new DP4 database with amended names.


[sql.remote DP4_database_name]

The [sql.remote DP4_database_name] section is used to specify whether tables are to be accessed remotely on the SQL database, or locally on the DP4 database. By default the application data tables are all remote and the base dictionary tables are all local. This can be overridden as follows:

[sql.remote db]
@menu_user=1
customer=0

This [sql.remote <dbname>] section has overrides for the base dictionary @menu_user table to be remote, (on the OEM database), and for the customer table to be local, (on the DP4 database). In order to specify a DP4base dictionary table as remote you will need to create the schema object for this table manually. You will also need a translation table for the table name and all the field names. You should not attempt to store base dictionary tables on the SQL database unless you clear this with Itim Technology Solutions first. Some DP4 applications require correct implementation of the NO_UPDATE and NO_DATA flags, and cannot be held on non DP4 databases as a result.


[sql.xlat <DP4_database_name>]

This section is used by the Gateway ADCS, to translate names on a DP4 database to their equivalent on a non-DP4 database. The format is as follows:

[sql.xlat DP4_database_name]
dp4name=oemname

Where dp4name is a name on the DP4 database and oemname is the name on the OEM database. Normally dp4name will be an SQL reserved word, or oemname a name on the OEM database that cannot be used on a DP4 database. You may be able to use auxwinst to generate this information automatically.


[sql.xlat2

[sql.xlat2 DP4_database_name ]

This section is used by the DP4 SQL Engine to translate table and column names submitted to it into the correct DP4 name. The format is as follows:

[sql.xlat2 DP4_database_name]
oemname=dp4name

Where oemname is a name submitted to the SQL Engine and dp4name is the corresponding name on the DP4 database.

In principle, this section should not be required. Correctly written software would submit correct SQL to the DP4 SQL Engine, using the table and column names the DP4 engine would report to the catalog functions. However it has been known for third party software to make invalid assumptions about table and field names, causing it to submit an incorrect name.

A second, and more valid reason for requiring this section is that you may have a previously written SQL statement for a database which was originally a non DP4 database, but for which you have now created a DP4 database. The DP4 database may have to have changed some of the names from the original database. If this section contains the translation from the original name to the name on the new DP4 database the SQL Engine will accept a statement that uses the original name, thus saving you from having to update it for the DP4 database. In this case the AUXWINST program will probably have created the appropriate entries for you automatically.


[AUXWODBC]

The AUXWODBC section is used by the ODBC Gateway ADC (AUXWODBC), described in the DP4 SQL Products Guide. The following entries may be specified in the AUXWODBC section:

  • col_order
  • first_occurs
  • role_sep
  • use_checksum
  • use_long_query
  • use_subquery


col_order

[SYBASE], [AUXWODBC], [ORACLE]
col_order = 1

1 means base dictionary number order; 2 means sequence number order

This allows the user to alter the order in which the column names appear in SQL statements. The default order is to use the DP4 base dictionary number order for the names. This option allows the user to change that order so that it mirrors the sequence number order which can be modified within the DP4 utility MAKEDB when creating database tables. This option is normally used in conjunction with short_sql.


first_occurs

[AUXWODBC], [ORACLE], [SYBASE], [sql.config DP4_database_name]
first_occurs = 0

This allows the user to override the default occurs numbering scheme used to construct names on the OEM database: DP4 multi occurs fields must be mapped to a series of fields on the OEM database.

For example a C30*4 address field becomes ADDRESS_0,ADDRESS_1...

first_occurs=1 will cause the indexes to start from 1 instead of 0.

first_occurs=00 or first_occurs=01 will cause the number to use two digits instead of 1 in all cases. (e.g. address_00), which may be useful if there are fields with occurs>10.

Refer to [sql.config DP4_database_name] section for more information.


role_sep

[AUXWODBC], [ORACLE], [sql.config DP4_database_name], [SYBASE]
Role_sep = '_'

This allows the user to override the standard default setting (an underscore) as the separator between field and role names when translating DP4 database names to names on an OEM database.

Auxoracl needs to generate a name that will be valid on the SQL database that combines the DP4 role and field name. Normally this name will be <fieldname><role_sep><rolename>.

Refer to the [sql.config dbname] section for more information.


use_checksum

[AUXWODBC], [ORACLE], [SYBASE]
use_checksum=0

0 means switched off, 1 means switched on.

use_checksum is a record locking and contention control mechanism for SQL databases. The ADC takes a checksum of all the data records read from the SQL database and stores this within the timestamp field of the DP4 data record structure.

When the user subsequently attempts to update this record, the ADC sends off a 'SELECT ... FOR UPDATE' to lock the record. This also reads the record, for which another checksum is taken and compared with the original checksum to determine whether the data record has been altered since the user first read the record and hence whether there is possible data loss due to the record having been updated by another user.

Although the default for this value is 0 you are strongly recommended to set it to 1 as otherwise contention errors may well not be detected.


use_long_query

[AUXWODBC], [ORACLE], [SYBASE]
use_long_query=0 or 1

0 means switched off, 1 means switched on. The default is 1 in AUXORACL and 0 otherwise.

For tables with multiple key fields SQL does not have an elegant method of selecting the record whose key is the next key in sequence. (Actually SQL2 defines a nice syntax for this allowing comparisons of the form (field1,field2) > (value1,value2). Unfortunately existing databases do not support this syntax and it therefore is not used by the Gateway ADCs at present.)

Instead a where clause of the form WHERE field1 > value1 or (field1 = value1 and field2 > value2) must be used. Another way of expressing this condition is WHERE field1 >= value1 and (field1 > value1 or field2 > value2).

If there are more than two keys the second form of the condition is longer than the first form, because in the first form each field is mentioned at most twice, but in the second form the first key must be mentioned as many times as there are key fields, the second key one fewer times and so on.

SQL databases may behave differently given the two forms of the condition. For example with Oracle there is no difference if there are only two keys, but if there are three, the shorter form of the condition stops the use_rownum feature from optimising the fetch time effectively. If you experience performance problems accessing tables with multi-part keys it is worth experimenting with this setting to see which gives better performance.

It should be noted that this option is partially incompatible with the use_subquery option. If both options are turned on the use_subquery option will only have an effect when exactly one key can vary.


use_subquery

[AUXWODBC], [ORACLE], [SYBASE]
use_subquery=0

0 means switched off, 1 means switched on.

use_subquery controls whether an additional predicate involving a subquery is generated in SELECT statements. The purpose of the subquery is to limit the first key allowed to vary in a non-exact match fetch to have the nearest possible value to its current value. Whether or not this improves performance will depend on how efficient the SQL database is at executing a subquery to determine a MIN or MAX value, and whether the expense of this operation is offset by the saving due to a smaller result set being generated. Experiments suggest that on the whole turning on this option is beneficial.

One situation where the option is not beneficial is if this key does not in fact vary much. For example a point of service system may well have tables where the first key component of most tables is a work station ID. On a local database this key would typically be constant, so the subquery feature would not restrict the result set. In such a system using a non DP4 database would lead to poor performance with a Gateway ADC unless applications fix that key when doing fetch(NEXT) type operations. It would be best to change the key structure of the tables so that the first key does discriminate between records better, or else to alter the application so that it specifies that this key must match.

It should be noted that this option is partially incompatible with the use_long_query option. If both options are turned on the use_subquery option will only have an effect when exactly one key can vary.


DEBUG

[DEBUG]
debug_auxsql=0
debug_disk=0
debug_error=0
debug_ipc=0
debug_loader=0
debug_odbc=0
debug_sql=0
debug_version=0
debug_win1632=0

This section can be used to turn on diagnostics, by changing the values to 1, in various parts of DP4. The diagnostics are sent to the ERRLOG program. In some cases setting the value to 2, 3 or even 4, will produce additional diagnostics. Normally you will only turn on diagnostics when asked to do so by Itim Technology Solutions during problem diagnosis.

In 4.622 disk debug_disk=2 reports delays due to I/O. debug_disk=3 reports timings for all physical I/O. debug_disk=4 gives additional diagnostics showing how DP4 converted "logical" to "physical" I/O. In 4.620/4.621 debug_disk supports the same range of values, but the meanings are not so clearly defined. debug_disk=2 is intended not to impact performance too severely, and may well be of use if you are investigating performance. Some apparent delays are probably insignificant - the system clock usually has a granularity of 15ms, so delays around this length will occur from time simply because a clock interrupt has occurred while DP4 is doing I/O related work.

WARNING

If these values are set to 1, DP4 programs will use up processor time formatting diagnostics even if load_errlog is set to 0.

debug_error and debug_version are set to 1 in the issued DP4 configuration file. Prior to release 4.620 the version information reported by debug_version often refers to one of the DLLs loaded by the program, and not the program itself. From release 4.620 version information is always accurate.


[ORACLE]

This section is used by the DP4 Oracle Gateway ADC (AUXORACL.W32). This is not a standard DP4 component and must be purchsed separately. It is described in the DP4 SQL Products Guide.

The following entries may be specified in the Oracle section:

  • allow_null_key
  • array_fetch
  • bin_data
  • block_post
  • bool_as_char
  • col_order
  • first_occurs
  • ignore_time
  • max_max_cursors
  • max_statements
  • role_sep
  • schema_name
  • sequence_format
  • short_sql
  • s_as_raw
  • use_checksum
  • use_logon_dialog
  • use_long_query
  • use_rownum
  • use_subquery

  • use_serializable

  • Customer specific values


allow_null_key

[ORACLE]
allow_null_key= 0

An attempt to create a record with null primary keys on Oracle will generate a constraint violation. Normally this would cause system error 311 to be generated. You can set allow_null_key=1 for this error to be ignored.

Note that this does not actually enable you to create records with null keys - it just stops programs that do create them from terminating with a system error.


array_fetch

[ORACLE]
array_fetch=1

0 means switched off, 1 means switched on.

This allows DP4 block fetches to be translated into Oracle OCI array fetches which may improve performance.


bin_data

[ORACLE]
bin_data = 0

You need to set this to 1 if you have want to store binary data in DP4 character fields.


block_post

[ORACLE]
block_post=1

0 means switched off, 1 means switched on.

This entry controls how AUXORACL implements calls which update the database. If block_post=0 each call to update the database generates a separate SQL statement. If block_post=1, several separate updates are combined into one, and the update is done via a call to a stored procedure, the name of which is dependent on the set of updates to be performed. If the procedure does not exist on Oracle, auxoracl creates it and then executes the procedure again. In theory users could modify the stored procedure to implement additional constraints.

N.B. This option is only enabled when use_checksum is also set to 1.

Although the option currently defaults to 1 you are recommended not to use it until the DP4 database structure is finalised, as otherwise the stored procedure may be incorrect. We may change the implementation details of this feature, in future, to allow use of more sophisticated features of databases and it may also be extended to include auxsybas and auxwodbc


bool_as_char

[ORACLE]
bool_as_char=0

In AUXORACL boolean fields on the DP4 database are assumed to be implemented on the Oracle database as numbers that can only take the value 0 or 1. If this option is set to 1 they are assumed to be implemented as character fields that can take the value N or Y (N and Y are hardcoded). This may make it easier to do certain types of import/export operation.


ignore_time

[ORACLE]
ignore_time=0

0 means switched off, 1 means switched on.

This adds the TRUNC function to SQL statements on the DATE datatype for when related DP4 date/time fields are combined into one Oracle DATA datatype and the application wishes to select all the records for a particular day irrespective of time for example.

It basically truncates the DATE within Oracle to chop off the time portion making the time part of each record 00:00:00.

This option was added in the past in connection with the timestamps option and is similarly not recommended.


max_max_cursors

[ORACLE], [SYBASE]
max_max_cursors=100 (or 0)

[ORACLE]

Possible settings: 1 (or 0 after build 96) to maximum of open_cursors parameter in the Oracle database INIT<SID>.ORA file

This is the maximum number of cursors that auxoracl will attempt to open in Oracle. When this number, the limit across all connections, is reached, it begins to close cursors to make them available for reuse. The advantage of the seemingly high devault value is that it keeps cursors open. When cursors associated with SQL statements have been closed, they have to be re-parsed within Oracle. Keeping cursors open avoids this and reduces traffic across the network, speeding up processing.

As of build 96 of AUXORACL there is usually no reason to limit cursors in this way (it can result in an "unfair" division of cursors between different users), and you may specify a limit of 0, which is the default from build 97 on

[SYBASE]

The meaning of the variable is identical. However we do not have any information about the maximum value allowed, nor the effect on performance of changing it.


max_statements

[SYBASE], [ORACLE]
max_statements=64

This is the maximum number of cursors that auxoracl or auxsybas will attempt to open per connection before it begins to close cursors to free up some for reuse.

The advantage of having this reasonably high is that it may be possible to reuse existing prepared SQL statements. The amount of reuse possible varies according to the database in use.

Note that this value is not used with auxwodbc because there is a per database way to specify the number of statements to keep for this program. See statements.

Prior to build 96 of AUXORACL, this value was in fact counted up on a per table rather than per connection basis. This meant it did not work as intended and the maximum number of cursors might be exceeded. This problem has now been fixed. On Oracle, you are recommended to specify a value for max_statements that it is only slightly less than the value specified for open_cursors in the initdbname.ora file for the Oracle database (which you can edit and increase to a larger value if required) .

If the SQL database reports that the maximum number of cursors has been exceeded DP4 will silently close old cursors and reduce the value of max_statements that applies to that database.


qab_lock

This entry is no longer available as its use could cause a hang to occur.

The entry was available in previous versions of auxoracl. The behaviour in the present version is now always similar to what it would have been when qab_lock was set to 1. When an application tries to lock a record, if the record is already locked (selected for update) by another application the LOCKED variable is set to TRUE and return to the application is immediate.


s_as_raw

[ORACLE]
s_as_raw=0

0 means switched off; 1 means switched on

This setting determines whether or not character translation is to be performed for DP4 type S fields. This can enable the data to be stored in Oracle in a RAW column rather than as Varchar. This does however, assume that the user will only be attempting to put binary data in this column/field.


schema_name

[ORACLE], [SYBASE]
schema_name=""

Table names in generated SQL statements are prefixed with this value if specified.


sequence_format

[ORACLE]
sequence_format=SEQ_%s

This option controls the name of the Oracle Sequence onject used to implement autoinc(). The name is constructed by replacing %s with the appropriate name table. For example if you specify sequence_format=%s_SEQ the sequence object used to autoincrement the CUSTOMER table would CUSTOMER_SEQ. Some third party software may expect the sequence object to have a particular name.


short_sql

[ORACLE], [SYBASE]
short_sql=0

If this is 1 select statements are generated as select * from table. For this to work you have to be absolutely certain that Oracle or Sybase will return the columns in the correct order. Auxoracl/Auxsybas do not check this.


sys_on_app_name

[oracle]
sys_on_app_name=dbname

This entry is no longer available. In previous versions of auxoracl it was used as a way of holding system database entries on Oracle. However it could cause problems, and degraded performance. Now you can map the system database in the same way as other databases, i.e. specify an appropriate entry for the system database in the [sql] section.


use_logon_dialog

[ORACLE]
Use_logon_dialog=0

0 means switched off, 1 means switched on

If this is set to 1 and the connection string specified for a database in the [sql] section is just "1", then the user is requested for log on details for the database via a Windows dialog.


use_rownum

[ORACLE]
use_rownum=0

The value is the maximum number of rows to be returned by a single select.

use_rownum controls whether SQL select statements generated by AUXORACL limit the number of rows to be returned by modifying the generated select statement to be SELECT * FROM (select_statement) WHERE ROWNUM<=n". This useful facility was introduced in Oracle Version 8.1. There is no elegant means in standard SQL of limiting the number or rows to be returned by a SQL SELECT.

This facility can only be used with Oracle 8.1 (and presumably later versions). It is not supported by Oracle 8.

Using this facility can help performance when accessing a large table. For example usually a DP4 fetch(FIRST) will read the entire table. Although subsequent calls to fetch(NEXT) will be fast, in the case where only the FIRST record is required the performance hit may be unacceptable, particularly on very large databases. Using use_rownum=1 makes the fetch FIRST faster. On the other hand a subsequent sequenece of fetch(NEXT) operations will be rather slower. You need to bear in mind the following:

  • The time taken for a select statement is dependent on a combination of the number of rows that need to be examined, and the number of rows that need to be placed in the result set. Limiting the number of rows only improves the second component.
  • If you specify a value for use_rownum grater than 1 then in many cases subsequent fetches on a table can reuse an earlier result set. Such fetches are very fast compared with fetches that need to execute a select statement.

A value of use_rownum between 100 and 1000 will probably give quite good results.

It would be useful if rownum could be used as a simple additional predicate. Unfortunately testing has indicated that this does not work reliably, and that Oracle will not always return rows in the expected order.


use_serializable

[ORACLE]
use_serializable=0

0 means switched off, 1 means switched on.

This switches Oracle's serializable transaction isolation. When on transactions are executed in such a way that they appear to be processed serially, one at a time, rather than concurrently.

(See the Oracle on-line documentation, Oracle 7 Server Concepts for more details.)


Customer specific values

[ORACLE]
multiple_db=0
use_local=0
casewise=2
multi_db_name="PDBASEV6"

These values are reserved for use by Casewise with their modeler product.


Loading the Gateway ADC

Loading AUXWODBC

  • Instructions for 16−bit DP4

    To load the ADC, ensure that SRVW is running, then run the ODBC gateway ADC program, AUXWODBC. The icon for SRVW will change to show that the ADC is running. After doing this, DP4 applications and utilities, such as BROWSER, are able to open any DP4 databases specified in the installation procedure and use the data on their associated ODBC data sources.

  • Instructions for 32−bit DP4

    AUXWODBC is loaded like other ADCs, as described in the ADC developers manual. That is, in the [startup] section of the DP4 Configuration file include the following:

    [startup]
    1=dp4srvr.w32 −aux
    2=auxwodbc.w32

Please refer to the ADC developers' manual for instructions on loading multiple ADCs.

Loading AUXORACL

  • Instructions for 32−bit Windows DP4

    AUXORACL is loaded like other ADCs, as described in the ADC developers manual. That is, in the [startup] section of the DP4 Configuration file include the following:

    [startup]
    1=dp4srvr.w32 −aux
    2=auxoracl.w32

  • Instructions for Unix DP4

    AUXORACL is loaded like other ADCs, as described in the ADC developers manual.

    srv3 −aux −remain
    auxoracl −remain

Please refer to the ADC developers' manual for instructions on loading multiple ADCs. Prior to release 4.620/4.524 Options which are read from the DP4 Configuration file file in Windows DP4 are instead read from a file called AUXORACL.INI. This file must be in the /usr/datafit directory, and is identical in format to the DP4 Configuration file.

Diagnostics

The information in this section pertains to builds after 21st November 2001

You can obtain diagnostics from the Gateway ADC by setting debug_auxsql to a non zero value as follows:

  • debug_auxsql=1 Prints diagnostics associated with call to a DP4 database function
  • debug_auxsql=2 Additionally displays the name and value of each input field for executed SQL statements
  • debug_auxsql=3 Additionally displays a hex dump for incoming and outgoing data

On Windows the diagnostics are only produced if error logging is turned on (e.g. by loading DP4 as follows:
srvw32 −start −load_errlog −debug_echo

On Unix the diagnostics are written to a file called auxoracl.out. This file is periodically restarted to prevent it growing too large and the previous file is renamed to auxoracl.out.bak. You can set the maximum size by using the −maxsize n command tail which specifies the maximum log file size in kilobytes, or you can prevent the file being restarted by specifying −maxsize 0 on the command line. The diagnostics are also echoed to screen if you specify −echo on the command line to auxoracl. To prevent auxoracl from detaching from the console specify −nodetach. You can force timing information to be added to the auxoracl.out file by specifying −timing.

Gateway ADCs and networking

If your SQL database does not reside on the same machine as the DP4 program there are two possoble configurations for accessing the database:

  • Load the Gateway ADC on the client, and use the OEM database's native network client software to access the database.

  • Load the Gateway ADC on the server underneath the DP4 network manager and access the database using DP4 networking. An example DP4 configuration on Windows would be as follows:

    [startup]
    1=dp4srvr.w32 -aux
    2=auxoracl.w32
    3=tcpmgr.w32

    The client machine loads DP4 networking in the normal way. It is probably a good idea to specify the −noblock command tail on the DP4 network requester, as it has been found that the "network block fetch optimisation" is counter-productive on OEM databases.

Which of these configurations you prefer should usually be a matter of informed choice (you should compare performance and total cost of ownership for the two configurations). In some cases there may be no suitable OEM network client available forcing you to use DP4 networking.


How the Gateway ADC generates SQL

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.

    Nulls

    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.

    Collation Sequence and Constraints

    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.


Gateway ADC Limitations

  • The NO_DATA flag is not currently implemented on fetches. This may be fixed in future versions.
  • The PRIVATE_DB and NO_UPDATE flags are not implemented. The state of records returned by fetches of uncommitted updates is determined by the SQL database, and currently there are no plans for their implementation.
  • LOCK is implemented as a SELECT FOR UPDATE. Therefore all LOCKS are unlocked at the next commit() or decommit().
  • The UNLOCK flag on fetches is implemented as a COMMIT WORK if there are no updates outstanding. Therefore any UNLOCK unlocks all locked records.
  • All the flags that can be specified on rec_post() or rec_kill() e.g. parent checking, child deletion, are ignored. You may be able to use SQL database constraints to simulate their behaviour.
  • In order to prevent looping all fetches on secondary indexes also use the whole of the primary key. In theory this could cause unusual applications to miss records.
  • The rec_autoinc() function is only available for tables stored on an Oracle database, and only when AUXORACL is used in place of AUXWODBC.


DP4 ODBC Drivers Guide

This part of the manual describes the DP4 ODBC Drivers for the Windows environment. This product allows third-party applications that support the ODBC standard to access DP4 databases.

Information specific to the DP4 ODBC drivers product is contained in the topics below. The drivers use the DP4 SQL Engine for SQL processing, so you should refer to the DP4 SQL Engine Reference for information about the SQL statements accepted by DP4.

  • Installation and Configuration including
    • Adding ODBC Data Sources
  • Using the ODBC Interface
  • Logging on to a DP4 Database
  • How column names are created from DP4 field names
  • Conformance to the ODBC Standard


Installation and Configuration of the DP4 ODBC Drivers

Prerequisites

Before you can install and use the DP4 ODBC Drivers, you require:

  • An operational DP4 for Windows 3.x or for Windows 9x/NT installation. If this is not available, refer to the DP4 installation guide for Windows for help on installing DP4 for your environment.
  • The Microsoft ODBC driver manager.

ODBC Driver Components

The DP4 CD ROM contains the separate 16-bit and 32-bit versions of the product:

16 bit Files | 32 bit Files | Description
ODBCLIBW.DLL | ODBCLIBT.DLL | DP4 ODBC driver
SQLLIBW.DLL | SQLLIBT.DLL | DP4 SQL Engine
INSTUTIL.EXE | INSTUTIL.EXE | Installation program
SYSLIBW.DLL |  |

We no longer test the 16 bit version of our ODBC drivers. On Windows 9x/NT/2000 you must install and use the 32 bit drivers (and hence the 32 bit version of DP4).

How to Install, Configure, and Use the DP4 ODBC Drivers

  • Installation

    You can install the files required for access via ODBC to DP4 databases by installing the version of the "ODBC Drivers" product appropriate to your DP4 installation from the DP4 CD-ROM. To install manually:

    1. Copy the required files as detailed above from the DP4 CD-ROM into an appropriate directory - that used for the other DP4 programs is recommended

    2. Run (from the start menu or a command prompt) the DP4 ODBC installation program as follows:INSTUTIL -ODBC
      INSTUTIL takes a few moments to add the appropriate entries to describe the DP4 ODBC driver to the Windows registry and or/the ODBCINST.INI configuration file. (You will know this has been succesful if the DP4 driver is listed in the Control Panel's list of ODBC drivers). You may need to specify the full pathname for the instutil program.

  • Configuration

    Once you have installed the DP4 ODBC drivers you need to add ODBC data sources for the DP4 databases that you plan to access via ODBC. See Adding ODBC Data Sources for more information.

  • Using ODBC

    Once you have installed the drivers and set up your ODBC data sources you will be able to access your DP4 databases via the ODBC interface. See Using the ODBC Interface for details of how to do this.


Adding ODBC Data Sources

The Microsoft ODBC driver manager and DP4 ODBC driver give you the capability to add ODBC data sources for DP4 databases. Most vendors bundle the Microsoft ODBC driver manager along with their own ODBC programs. So, if you have installed a third-party application with support for ODBC, it is likely that the Microsoft ODBC driver manager is already available on your system. In attempting to locate this program, open the Control Panel and check this icon is there (or a similar one - a slightly different icon is used on Windows 98,Me and 2000):

On Windows Me you may have to select the option to "Show all Control Panel Options". On Windows 2000 and Windows XP this part of Control Panel has been hidden inside the "Administrative Tools Option - it will definitely be present). If it is not, you will probably find an icon to run it in one of the third-party vendors program groups - for example, the Oracle7 ODBC group contains an icon entitled ODBC Administrator that runs the Microsoft ODBC driver manager.

Using the Microsoft ODBC driver manager, add a data source for each DP4 database to be accessed through the ODBC interface:

  1. Run the Microsoft driver manager by double-clicking the ODBC icon in the Control Panel or by double-clicking the appropriate icon. The Data Sources dialog box appears:

This dialog enables you to add, delete or configure data sources. The data source defined for all the currently installed drivers are listed in the Data Sources lists or the various types of DSN. (There are several different types of DSN. If you want your DSN to be accessible to any application run by anyting user on the machine you should use a System DSN)

There are seven buttons:

Option (W32) | Option (W16) | Description
Add | Add | Allows you to add a new data source to the list
Remove | Delete | Deletes the data source highlighted in the list
Configure | Setup | Allows you to change the configuration of the data source highlighted in the list
OK | Close | Closes the dialog box, confirming any changes you have made
Cancel | - | Closes the dialog box, cancelling any changes you have made
Apply | - | Closes the dialog box, forcing any changes you have made to take effect immediately
Help | Help | Takes you into help screens
- | Drivers | Allows you to install new ODBC drivers or delete existing ones. (In 32-bit Windows this is installed by application software.)
- | Options | Allows you to specify how the ODBC driver manager traces calls to ODBC functions. (In 32-bit Windows select the Tracing tab.)

  2. Click Add to add a new data source. The Add Data Source dialog box appears:

    This dialog lists the currently installed ODBC drivers

  3. From the list, select the DP4 driver and click Finish or OK. A small DP4 dialog requesting the name of the data source (or database for older DP4 releases) appears:

  4. Fill in the data source name and click OK. (You can use any name, but it is usual to use the database name). A second DP4 dialog appears:

Click on an control in the illustration above for information about it.

  5. After setting the appropriate properties the data source, click OK. you will be returned to the Data Sources dialog box, which will have been updated to show the new data source.

  6. Repeat the above procedure to add all the desired data sources, then click Close


User Name

The username you enter must exist in the system database's user list. This username is presented as a default by many ODBC client applications when they establish an ODBC connection. You may also leave it blank.

See Maintaining the User List in the DP4 Developer's Guide for details of DP4 user management. DP4 does not support using Windows usernames and passwords.


Password

The password for the username. You may leave it blank.


Prompt

Prompt and Pasword

If Prompt is disabled, many applications which normally prompt for a user name will not do so, and will use the default user name and password from this dialog. In this case, you must enter a username and password, which will be stored unencrypted in the ODBCINST.INI file or the Windows registry, depending on the version of Windows that is in use. If Prompt is enabled, a password is not supplied here because applications will prompt for the username and password when they first establish a connection with the DP4 data source.

Note that these names and passwords must match those stored on the system database and maintained using MENUEDIT. See Maintaining the User List in the DP4 Developer's Guide. DP4 does not support using Windows usernames and passwords.


Omit checks

Omit checks tells the driver to omit all relational database checks when inserting, deleting or updating data, such as the IGNORE_CHILD and NO_CHECK flags for post and kill in the database manager.


Go Faster

Go Faster tells the driver not to use the private database. This can speed up big transactions such as DELETE FROM tablename, but may sometimes cause a SELECT or UPDATE statement to work using the old version of a record that has been updated but not yet committed. Using this option does not bypass referential integrity checks. To do that, and speed up processing more. select the "Omit checks" option as well.

This option is equivalent to -noprivate on the SQL Interpreter, and SQL Filter program, and not the latter program's -gofaster command tail.


Using the ODBC Interface

Before using the DP4 ODBC interface, you may have to ensure the DP4 database manager is loaded, or on Windows NT that the DP4 System service is started. In general DP4 for Windows 9x/NT will auto-load the database manager in any case. However in some circumstances this may not be possible, for example on Windows NT IIS (Internet Information Server) service may not be allowed to start the DP4 service because of security considerations.

How you access a DP4 database through the ODBC interface, depends on the application program from which you want access. See the instructions for your application for details. Some examples of using DP4 databases with Microsoft Office applications and with Crystal Reports are given below. Whenever you connect to a DP4 database via ODBC you may be asked to log on to the database. How to do this, and how to avoid having to do it are explained in Logging onto a DP4 database.

Using DP4 Data Sources

Much of the information in this section was created several years ago. As a result you may find some of the instructions are slightly inaccurate if you are using the latest versions of these products. Please let us know if you find any serious inaccuracies. The DP4 User Group web site will often contain information relating to the use of DP4 via ODBC with new products and new versions of products.

  • Connecting to DP4 from Microsoft Excel

  • Connecting to DP4 from Microsoft Access

  • Sending Microsoft Access Data to Microsoft Excel/Microsoft Word

  • Connecting to DP4 from Microsoft Query

  • Connecting to DP4 from Crystal Reports


Logging on to a DP4 Database

Unless you suppressed their prompting, you must normally supply a username and password when logging on to a DP4 database through the ODBC interface. These are validated against the username list, set up on the system database.

To allow users to log on to a DP4 database anonymously, with a blank user name and password, you can add the line:

anon_logon_ok = 1

to the appropriate [<DP4_data_source>] section of the ODBC.INI file. (This does not stop the system prompting for a user name and password - to stop this, refer to step 4 in Adding Data Sources). If you do this ODBC connections will have unrestricted access to the database without supplying any login credentials.


Connecting to DP4 from Microsoft Excel

Three methods of working with DP4 data sources from Microsoft Excel are described in the following sections:

  • Using the SQL.REQUEST function to execute an SQL statement on the DP4 data source and return the results as an array

  • Using a pivot table to create an interactive summary report of numerical DP4 data. Microsoft Excel does this through a DDE link to Microsoft Access, which establishes an ODBC connection with the DP4 data source

  • Using the Windows tool bar or the Object Packager to call Microsoft Access from within Microsoft Excel. This information is included because Microsoft Access allows you to update, as well as view, data on a DP4 data source


Using SQL.REQUEST to Return an Array of Data

You can use the ODBC Add-in for Excel to access and retrieve data from the DP4 data source directly, without using Microsoft Query. If you selected the Data Access check box in the Complete/Custom option you have all the files you need.

The ODBC Add-in contains additional functions that extend the Excel macro language for ODBC and a special function called SQL.REQUEST.

SQL.REQUEST connects with an ODBC data source and runs a query from the Excel worksheet, returning the result as an array, without the need for macro programming. If this function is not available, you must install the ODBC Add-in (XLODBC.XLA) (see Installing the ODBC Add-In later in this section).

The following example uses the SQL.REQUEST function to retrieve customer names and balances, selected by their balances, but assumes that SALESORD is an available ODBC data source.

  1. Select the array range by highlighting a block of cells, two columns wide by ten rows deep
  2. Select Function on the Insert menu. Step 1 of the Function Wizard appears
  3. In the Function Category list, click Database
  4. In the Function Name list, click SQL.REQUEST, followed by Next

Step 2 of the Function Wizard appears. It displays a number of boxes for entering the parameters of the SQL.REQUEST function:

Parameter | Description
connection_string | A string or array that supplies information required by the DP4 driver to connect to the data source, in the format:
 | DSN=<data_source_name>;DBQ=Datafit [;UID=<username>;PWD=<password>]
output_ref | (optional). A cell reference where you want the completed connection string placed
driver_prompt | (optional) An integer from 1 through 4 that determines when the DP4 driver dialog is displayed
query_text | The SQL statement you want to execute on the DP4 data source. You can update a query by concatenating references into query_text. In this example, every time $C$2 changes, SQLREQUEST uses the new value to update the query:
 | ". . . WHERE balance >" & $C$2,
column_names_logical | TRUE returns column names as the first row of the query results. FALSE suppresses column names in the query results
  5. In the connection_string box, type:
    DSN=salesord;DBQ=DP4
    to establish a connection with the SALESORD data source. Press <Tab>
  6. In the output_ref box, press <Tab> to leave it blank
  7. In the driver_prompt box, type: 3 to display the dialog only if information provided by the connection_string is insufficient to complete the connection. Press <Tab>
  8. In the query_text box, type:
    "SELECT name, balance FROM customer WHERE balance >" & $C$2
    to select names and balances of customers with balances greater than the value in cell C2. Press <Tab>
  9. In the column_names_logical box, type: TRUE to return NAME and BALANCE as headings in the first row of the results
  10. Click Finish to close the Function Wizard. The Formula bar should now display the formula:

=SQL.REQUEST("DSN=salesord;DBQ=DP4",,3,"SELECT name, balance FROM customer WHERE balance >" & $C$2,TRUE)

  11. Click anywhere in the Formula bar and press <Ctrl+Shift+Enter>

Excel executes the SQL.REQUEST function and fills the array range with the results of the query on the SALESORD database

If you want to change the selection value, changing the value of C2 refreshes the array with new data.

The sample salesord.xls worksheet supplied contains an array that makes use of the SQL.REQUEST function. For more information on this function or about array formulas, refer to the information provided by Microsoft Excel.

Installing the ODBC Add-In

To make the Excel ODBC Add-in available:

  1. Select Add-Ins on the Tools menu. The Add-Ins Available dialog appears
  2. Select XLODBC.XLA (normally found in the \excel\library\msquery directory)


Using a Pivot Table

A pivot table is an interactive table that can summarise large amounts of numerical database data. To create one:

  1. Select PivotTable from the Excel Data menu. Step 1 of the PivotTable Wizard appears
  2. In step 1, click External Data Source. Step 2 of the PivotTable Wizard appears
  3. In step 2, click Get Data to start Microsoft Query from which you can retrieve data from your DP4 data source
  4. In Microsoft Query, create the necessary query that selects the data you want to use, then select Return Data to Microsoft Excel on the File menu
  5. Follow the remaining steps in the PivotTable Wizard to finish the creation of your pivot table

The sample salesord.xls worksheet supplied contains a pivot table.


Using Microsoft Access from within Excel

Running Microsoft Access from within Excel allows you to update tables on DP4 data sources. There are two approaches:

  • You can enable the Microsoft tool bar, which provides an icon for starting Microsoft Access
  • You can use the Object Packager to create an icon in the spreadsheet that runs Microsoft Access and opens a specified database

Enabling the Microsoft Tool Bar

To enable the Microsoft tool bar, select Toolbars from the View menu and enable the Windows checkbox in the Toolbars dialog.

Using the Object Packager

To create an icon in the spreadsheet for a Microsoft Access database:

  1. Click a cell where you want the icon
  2. Select Object from the Insert menu
  3. In the Object dialog, click Create from File
  4. Use the File dialog to select the Microsoft Access database (*.mdb) with linked tables from the DP4 data source, and click OK

You are returned to the spreadsheet which should now contain the Microsoft Access icon. Try double-clicking it.

The sample salesord.xls worksheet supplied contains an icon for a Microsoft Access database.


Connecting to DP4 from Microsoft Access

In Microsoft Access, the linked table facility creates an ODBC link from an Access database to a DP4 data source so that you can view and edit data in the table. You can continue to use linked tables in both DP4 and in Microsoft Access.

After you link a table, you can set some field properties in the table's Design view. Although you can't change the structure of a linked DP4 table, you can set the following field properties: Format, DecimalPlaces, InputMask and Caption. You can't set the ValidationRule, ValidationText and DefaultValue properties for the table, but you can set those properties in a form bound to the attached table.

You will need to set the Format property of DP4 Yes/No fields because the Yes value is normally displayed as -1 and the No value as 0. You can choose to output Yes/No fields with "True/False", "Yes/No" or "On/Off" values.

If you rename a linked table in DP4, you can update the connection in Microsoft Access with the Linked Table Manager (if it works at all!).

In Microsoft Access, to link tables:

  1. Open an existing Access database or create a new one
  2. Select Get External Data and then Link Tables... from the File menu. The Link dialog appears.
  3. In File of Type Combo Box, select ODBC Databases and click Link. The ODBC Data Sources dialog appears.
  4. Select the DP4 data source you want to use, for example SALESORD, and click OK. The Link Tables dialog appears. It lists all the application tables on the DP4 data source.
  5. Select tables by highlighting one and clicking Link
  6. When you are finished linking tables, select Close. You are returned to the Database window where the linked tables are listed.

With certain restrictions, most of which have already been discussed, you can use the linked tables as if they were ordinary Microsoft Access database tables. Please note that the names for fields will not be the same as when you are using other products. The correct names to use will normally be shown as the column headings. Refer to the information on ODBC name mangling for further details. If attempting to manipulate date or time fields please refer to the note about Access on the SQL Notation page.

If you are using Microsoft Access 2000, you may experience problems connecting to DP4. This is a bug in Microsoft Access, not DP4. You should visit the Microsoft Web Site, or MSDN and download and install the latest version of MDAC, which will probably cure the problem. It may also be worth downloading the Microsoft Jet ODBC drivers which are not included with MDAC any more.


Sending Microsoft Access Data to Microsoft Excel/Microsoft Word

There are two relatively simple ways to send formatted data from Microsoft Access to Microsoft Excel/Word:

  • You can highlight the data you want transferred and use the standard Copy/Paste commands on these application's Edit menus
  • You can use Output to on the File menu to save the output of a table, query, form, report or module to a file in one of the following formats: Microsoft Excel (.XLS), Rich Text Format (.RTF) or MS-DOS Text (.TXT)

As an alternative to this command, you can use Access to ... toolbar buttons to save the data to a file in the Microsoft Access working directory and immediately start the appropriate application.


Connecting to DP4 from Microsoft Query

You can use Microsoft Query (installed as part of Microsoft Office 97 or 2000) to query the data on a DP4 data source. To select a DP4 data source:

  1. In Microsoft query, bring up the Select Data Source dialog by selecting New Query or Table Definition from the File menu, or by selecting Execute SQL from the File menu and clicking Data Sources
  2. If the Available Data Sources list does not include the DP4 data source you want to use, click Other... Microsoft Query displays a dialog box listing the ODBC data sources installed on your computer
  3. Select the desired DP4 data source from the list and click OK. You are returned to the Select Data Source dialog where the DP4 data source you chose is highlighted. Click Use to use it

For details on how to use Microsoft Query, refer to its online help or user guide.

Microsoft Query is a useful diagnostic tool - if you are having problems with a particular SQL statement when using a DP4 ODBC connection it is well worth trying to use the same statement in the DP4 SQL Interpreter and also in Microsoft Query. This may well help isolate the cause of the problem - for example if the SQL Interpreter accepts the statement, but Microsoft Query does not then the problem is either within ODBC itself or in the DP4 ODBC driver, and not in the DP4 SQL Engine.


Connecting to DP4 from Crystal Reports

The following instructions are for Crystal Reports Version 5 or thereabouts. The details vary slightly for later versions.

Using Crystal Reports you can create reports based on DP4 data sources, as follows:

  1. When creating a new report, in the Choose Database File dialog, click SQL Table... The Log On To Server dialog appears
  2. In the SQL Types list, select the DP4 data source you want to report on, and click OK. The Choose SQL Table dialog appears. It lists the DP4 data source's application tables in the SQL Tables list
  3. Highlight a table and click OK. The Report Window is opened with a floating dialog containing all the fields from the selected table

You can now build the report as you would any other Crystal Reports report. (To create a report based on more than one table, select Add File to Report from the Database menu.)

If you are using Crystal Reports Version 8 add the following line to the DP4 Configuration file in the [odbc] section (which should be created if necessary):
detect_crystal_reports=0
For information on why this is necessary please consult the following article on the DP4 user group web site: a4000011.htm


How the DP4 ODBC driver generates column names from field names

The information in this section is primarily intended for programmers accessing DP4 databases using ADO, though it may also be useful when using the OLEDB provider for ODBC, or even ODBC itself. This information is taken from a DP4 User group article.

When ADO uses SQL to query a database, it also asks ODBC to report the name of every column in the query. If the code using ADO refers to fields in the record-set by name, ADO uses these names, and not the names specified in the query. This causes some problems with DP4, because our SQL interpreter is fairly flexible - it will ignore underscores in names, and allows various methods of specifying roles and occurs numbers. To access columns by name the ADO code must specify the names exactly as our SQL library reports them.

DP4 Field Name | Occurs | Name report by SQL | Comments
field | single | field |
field.role | single | role$field | There are configurable settings which allow ODBCLIBT.DLL to mangle this name in various ways. This may be useful when using Oracle products.
field | multi | field[1],field[2] etc. | If ODBCLIBT.DLL recognises queries as coming from Microsoft Access it replaces square brackets with curly braces. This behaviour can be configured to happen by default.
field.role | multi | role$field[1], role$field[2] etc. |

ODBC Name Mangling

You can set default options to control name mangling in the [odbc], and, for historical reasons, [oracle] sections of the DP4 configuration file. These options can be over-ridden on a per DSN basis by specifying the same option in the [dbname] section of odbc.ini. The following settings are available. More information on the DP4 configuration file is contained in the Guide to the DP4 configuration, which can be accesed from here.

DP4 configuration file Section Option=Default Description
[odbc] curly_occurs=0

If curly_occurs=1 then the [ and ] characters used in the name of multi-occurs fields are replaced with { and } brace characters.

N.B. When the client is MS Access this change is made automatically as Access does not like square bracket characters in names.

[odbc] oracle=0

If oracle=1 then the [oracle] entries listed below are used to mangle the names, otherwise they are not.

When this option is selected column names are constructed as field[separator]role[separator]occurs rather than role$field[occurs]

[oracle] first_occurs='0'

DP4 multi occurs fields are catalogued as series of fields for ODBC. For example a C30*4 address field becomes ADDRESS_0,ADDRESS_1...

first_occurs=1 will cause the indexes to start from 1 instead.

first_occurs=00 or first_occurs=01 will cause the number to use two digits instead of 1 in all cases. (e.g. ADRESS_00) This may be useful if there are fields with occurs>10.

[oracle] role_sep='_'

This sets the separator to be used between the field and role name parts of a column name, and also between the field name and any occurence number . Note that once the oracle=1 setting is in effect, that the default role separator is '_' rather than '$'.

There is also an option which affects the order in which column names are returned when cataloguing tables. This may be relevant for Wizard type programs that generate SQL or ADO code automatically.

[oracle] col_order=1

This option changes the order of the fields within generated SQL statements.

  • If col_order is 2 then fields are processed by sequence number (as set in MAKEDB).
  • If col_order is 1 then fields are processed by internal field and role number sequence.

You can use AS to specify your own name for a field or column. For example:
select order_number as ordernr from orderheader where order_number=10000
Refer to Printing Columns for more information on selecting columns.


Conformance to the ODBC Standard

Third-party client applications that use the ODBC interface to access DP4 data sources (databases), do so by sending SQL statements to the DP4 ODBC server. The DP4 ODBC server, which might also be termed an SQL server, uses the DP4 SQL Engine to translate these statements into native DP4 access requests and directs them to the DP4 database manager. The database manager then accesses the appropriate DP4 database, returning whatever data was requested.

The general implications of accessing DP4 databases through the ODBC interface are:

  • The DP4 SQL Engine determines what SQL syntax is supported. See the Reference for details.
  • DP4 databases contain native features that are not supported by SQL

More specifically, the DP4 ODBC interface is an implementation of ODBC level 2 version 1, with the following restrictions:

  • The DP4 database management system uses optimistic concurrency control based on timestamps; the ODBC interface does not support changing this to any other type of concurrency control
  • The DP4 SQL Engine does not currently support 'CREATE TABLE' and other data definition statements
  • The DP4 SQL Engine does not support the execution of a 'SELECT' statement with multiple sets of parameters. Only one set of parameters may be bound to a 'SELECT' statement. Note, however, that updates ('INSERT', 'UPDATE' and 'DELETE' statements) with multiple sets of parameters are emulated by the DP4 driver
  • The DP4 SQL Engine does not support moving the position of the current of row back into a block of rows that have already been fetched, without moving the cursor which determines the next row to be fetched. This means that fetching more than one row at a time with the extended fetch function is not supported
  • The SQLForeignKeys and SQLStatistics functions are not implemented and so extended fetch is not supported; the result set provided by these functions can only be accessed by a fetch in the forward direction

Our primary intention is to make the DP4 database usable with the widest possible range of ODBC applications, not rigorous adherence and implementation of particular standards. It would not be helpful for us to tell you that you cannot use a particular tool, because it is deviating from ODBC or SQL standards, though this would in fact often be the case. If you have an issue with our ODBC drivers please help us to help you not by insisting on blanket implementation of a raft of new features, but by allowing us to address the specifc problem you are encountering.


DP4 SQL Guide

This part of the guide describes how to install and use the DP4 SQL Interpreter and DP4 SQL Filter programs:

  • The DP4 SQL Interpreter allows you to submit SQL statements directly to the DP4 SQL Engine to query and update data on DP4 databases, and is described in Using the DP4 SQL Interpreter.
  • The DP4 SQL Filter program (SQLEXE) offers an alternate batch-driven means of accessing the DP4 SQL Engine, through the use of input and output redirection or the use of pipes, and is described in Running SQL Statements in Batch Mode.
Both these facilities are available for all operating systems that support the full DP4 run-time environment. Installing DP4 SQL details the requirements, SQL components, and installation method for the various platforms.

Although an understanding of standard SQL is assumed as well, the DP4 SQL Engine Reference describes the SQL syntax as accepted by DP4 and has some examples. The same section also describes how the SQL Engine translates SQL statements into calls to the DP4 database manager.


Installing and Configuring DP4 SQL

Installing DP4 SQL

Prerequisites

DP4 SQL is available on these platforms:

  • Windows (16-bit)
  • Windows NT/95
  • MS-DOS
  • OS/2
  • Unix and Linux (Intel Only)
  • FlexOS

The DP4 SQL product requires an operational DP4 system. If this is not already installed, refer to the appropriate DP4 installation guide for help on installing DP4 for your environment.

Components

The DP4 SQL product is included on the DP4 CD-ROM. You can install it by running the DP4 install program and selecting to install the SQL package, which is called "SQL for Windows 95/NT" for Windows, and the obvious equivalent for the other operating systems. For Unix and Linux you need to copy the appropriate TAR file to your machine, and extract it, and then run ./dp4install as usual. In all cases you must install the SQL version appropriate to the version of DP4 you are using - it is not safe to use an earlier or later release of SQL then your general DP4 installation. Once installed you can start to use the SQL Interpreter and Filter on your DP4 databases immediately. There are no configuration steps.

You can install manually by copying the appropriate components from the tables below to a suitable directory. The directory where DP4 programs are installed is recommended.

The files required for the various platforms are as follows:

Windows

The Windows files are:

16-bit | 32-bit | function
SQL.EXE | SQL.EXE | DP4 SQL Interpreter
SQLLIBW.DLL | SQLLIBT.DLL | DP4 SQL Engine
SQLEXE.EXE | SQLEXE.EXE | DP4 SQL filter program
SYSLIBW.DLL | - | DP4 System DLL needed by 16 bit SQL run-time
SQLDDE.EXE | - | DP4 DD4 Interface (obsolete) as described in DP4 DDE Interface

MS-DOS

*SQL.EXE | DP4 SQL Interpreter
SQLEXE.EXE | DP4 SQL filter and SQL Engine

*Due to memory limitations it is very likely that you will not be able to run the MS-DOS version of the SQL program. SQLEXE may be the only way of executing DP4 SQL on MS-DOS.

OS/2

SQL.EXE | DP4 SQL Interpreter
SQLLIBO.DLL | DP4 SQL Engine
SQLEXE.EXE | DP4 SQL filter program

UNIX

sql | DP4 SQL Interpreter and DP4 SQL Engine
sqlexe | DP4 SQL filter program

FlexOS

SQL.286 | DP4 SQL Interpreter
SQLLIBF.SRL | DP4 SQL Engine
SQLEXE.286 | DP4 SQL filter program

In the past the map files for the SQL programs were issued. This is no longer the case as the issued system database has included these maps since at least release 4.517.


Using the DP4 SQL Interpreter

To run the DP4 SQL Interpreter:

  1. Run SQL.EXE. The program starts and presents a list of databases
  2. Select the database with which you want to work. The main screen of the DP4 Interpreter appears. Click on an area of the illustration below for further details of the options available.

Running a Simple SELECT Statement

To test whether the DP4 SQL Interpreter is functional, you might like to run a simple SELECT statement against the SALESORD database:

  1. Select the STATEMENT option. The cursor moves into the Command window
  2. To list all the records on the EMPLOYEE table, enter the following:
    SELECT * FROM employee;
    The employee records are listed in the Results area:

If you want to browse the data, select the Results option and use the <↓> and <↑> keys.

For information on:

  • how you write constants, table names and field names, and how you construct expressions and conditions, refer to SQL Notation.
  • the different SQL statements you can use, refer to SQL Statements.

Command Line options

The SQL Interpreter accepts the following command line options: -gofaster, -noprivate, -readonly. These options all have the same meaning as the corresponding options for SQLEXE. See SQLEXE Command line Options for further information. SQL does not accept any of the other special SQLEXE options, only standard DP4 command tails such as -db.

The SQL Interpreter expects these options to be supplied without any _ separators, whereas the SQL Filter expects them with _ separators. Sorry about that!

Exiting From the DP4 SQL Interpreter

To leave the program, either press <Esc> with one of the menu options highlighted or select the Exit menu option.


Running SQL Statements in Batch Mode

This section describes how you can use the DP4 SQL Filter program (SQLEXE) to execute SQL statements in batch mode that perform queries and updates on a DP4 database.

SQLEXE is a Unix-style filter program which reads SQL statements from its standard input, and writes the results to its standard output, with error messages going to standard error. You can, therefore, use SQLEXE in two ways:

  • By redirecting SQLEXE's input and output to files. This is typically done under MS-DOS but can be done for all other operating systems as well, and is described in File-Driven SQL Sessions.

  • By piping into and out of SQLEXE. This is typically done under Unix and Linux and is described in Unix-Based SQL Sessions.

To use SQLEXE, you must have installed the DP4 SQL product as described in Installing DP4 SQL.

There is an extensive set of command line options that you can use to configure the behaviour of SQLEXE. This is described in SQLEXE Command Line Options.


File-Driven SQL Sessions

Suppose you have an input file called QUERY1 that contained the following:

SELECT REGION_CODE, CUSTOMER.NAME, PAYMENT_VALUE
FROM REGION, CUSTOMER, ORDER_HEADER
WHERE PAYMENT_VALUE > 1000
AND ORDER_HEADER.CUSTOMER_NUMBER = CUSTOMER.CUSTOMER_NUMBER
AND CUSTOMER.REGION_CODE = REGION.REGION_CODE
ORDER BY REGION_CODE ASC, PAYMENT_VALUE DESC;

You could use this command line:

SQLEXE <query1 >result1 -DB salesord

to run the SQL statement held in QUERY1 against the SALESORD database and to send any data which resulted to RESULT1. (The database must be specified with the '-db' command tail). In some situations you may not be able to rely on a command line to redirect SQLEXE's input and output like this. In this case you could use this command line instead:

SQLEXE -read query1 -write result1

The numerous SQLEXE command tails are described here.

The results should appear in the file RESULT1 as shown below:

Region_Code     Name                              Payment_Value

-----------     ----                              -------------

ANG             Barkataki Z Y                           1119.48

LON             Golf Oil Ltd                            2378.40

NEE             Mercer Q                                2531.00

NEE             Mercer Q                                1034.26

SCT             Mackintosh D.C.Electrical Ltd           5506.52

SCT             Mackintosh D.C.Electrical Ltd           5506.52

SCT             Mackintosh D.C.Electrical Ltd           4531.00

SCT             Mackintosh D.C.Electrical Ltd           2253.26

SEE             Richmond P                              2054.34

SEE             Kennedy R P                             1094.75


Unix-Based SQL Sessions

Unix and Linux SQL Sessions

Under Unix and Linux, the SQL filter program can run as a background process and other programs can attach to it through the use of pipes. A code fragment that shows how this can be achieved is:

int pipefd2[2];
int pipefd1[2];

pipe(pipefd1);            /* make 2 pipes */
pipe(pipefd2);

if (fork() != 0)          /* parent process */
{
  close(pipefd1[0]);      /* close reading side of 1st pipe */
  close(pipefd2[1]);      /* close writing side of 2nd pipe */
  close(0);               /* close std in and std out */
  close(1);

  dup2(pipefd2[0],0);        /* reading end of 2nd pipe - stdin */
  dup2(pipefd1[1],1);        /* writing end of 1st pipe - stdout */

  close(pipefd2[0]);      /* close duplicates .. */
  close(pipefd1[1]);      /* close duplicates .. */

  /* At this point, stdin in from pipe 2, and stdout is pipe 1 */

  /* Do whatever is required , i/o will be to/from SQL */

}
else                      /* child process */
{
  close(pipefd2[0]);      /* close reading side of 2nd pipe */
  close(pipefd1[1]);      /* close writing side of 1st pipe */
  close(0);               /* close std in and std out */
  close(1);
  dup2(pipefd1[0],0);     /* reading end of 1st pipe - stdin */
  dup2(pipefd2[1],1);     /* writing end of 2nd pipe - stdout */
  close(pipefd1[0]);      /* close duplicates .. */
  close(pipefd2[1]);      /* close duplicates .. */

  /* at this point, stdin from pipe 1, and stdout is pipe 2 */

  /* SQL reads and writes are now via these pipes */

  execl("sqlexe", "sqlexe", "-noheadings", "-e", "37", 0);

}


SQLEXE Command Line Options

You can append command tails to the SQLEXE command line to modify how SQLEXE operates. These are listed in the following table.

Command Tail | Meaning
−d number | Uses the ASCII character specified by number as an SQL statement delimiter (this implies −delim ). Normally, this is 59; the ASCII semicolon (;) character
−db dbname | Specifies the database name to use. This is mandatory
−delim | Specifies that SQL statements are delimited by semicolons. This allows you to put several statements on the same line or large statements across several lines. Normally, one statement is expected per line
−e number | Uses the ASCII character specified by number as an error message lead-in. This causes error messages to be sent to stdout instead of stderr, and to be prefixed by the specified character
−echo | Echoes the SQL statement to the output. This is useful for storing the statement with its results
−f number | Uses the ASCII character specified by number as a tab character (this implies −tabs ). Normally, this is 9; the standard ASCII tab character
−go_faster | Turns off referential checking, such as parent checks. This also turns off the private database.
−names_as_headings | Uses column names as column headings
−no_fail | Does not terminate on SQL error. This allows you to execute further SQL statements in the file when a statement generates an SQL error
−no_headings | Produces output without column headings
−no_private | Turns off the private database facility, without disabling referential checking. This may speed up large inserts,updates, and deletes or selects done after these operations but prior to a commit or rollback work. Wrong results may be returned to a select in as much as uncommitted changes will not be seen by the program if records are read more than once in the same transaction.
−read filename | Input statements are read from this file rather than stdin. This command tail is supplied to allow sqlexe to be invoked without the need for input redirection (for example from Windows short-cuts, which do not support command line redirection.)
−read_only | Allows only SELECT statements
−tabs | Uses tabs as column separators. Normally, blanks are used as column separators
−trigraph | Interprets trigraph sequences in the input (such as ??q for a quote character and so on)
−write filename | Results are written to this file rather than stdout. This command tail is supplied to allow sqlexe to be invoked without the need for output redirection (for example from Windows short-cuts, which do not support command line redirection). −file can be used as a synonym for −write.


DP4 SQL Engine Reference

This part of the DP4 SQL guide describes the SQL language as implemented by the DP4 SQL Engine. This determines the SQL statements that are accepted by both the DP4 SQL product, and the ODBC Drivers and DP4 DDE products.

If you are already familiar with another vendor's implementation of the SQL language, you may find it useful to review the information in Comparison with Other SQL Implementations.

For a guide to standard SQL, refer to A Guide to the SQL Standard by C. J. Date, or a similar work. (We use the Reference Manual published by Osborne, in conjunction with Microsoft ODBCODBC documentation, as well as manuals for products such as Oracle and Sybase as a basis for deciding on the correct parsing and interpretation of SQL statements.)

If you are using the DP4 ODBC driver via a program such as Microsoft Access you should be aware that the SQL it shows you may be significantly different from the SQL it submits to DP4. This can be a cause of problems, as the program may generate parse errors itself for SQL that would in fact be acceptable to DP4.

The following pages describe the language accepted by the DP4 SQL Engine.

  • SQL Statements introduces the various SQL statements:
    • The SELECT statement
    • The INSERT statement
    • The UPDATE statement
    • The DELETE statement
    • The COMMIT WORK and ROLLBACK WORK statements
  • SQL Notation describes the low-level syntax for identifiers, literals and so on, including :
    • Date/Time Arithmetic
    • SQL Trigraphs
  • SQL Implementation describes how the various SQL statements are executed on the DP4 database. This section contains information which may be useful in decding how best to express a SQL query.
  • Comparison with Other SQL Implementations


SQL Notation

  • Constants

    Numeric constants can be written in the usual way (123.456) or in scientific notation (12.34E-56). String constants are enclosed by single quotes, for example 'string' (do not use double quotes). You can include a single quote in a string literal by doubling it, for example 'Brian''s'. Date constants are written as dd.mm.yyyy (only dots are accepted as separators) and time constants as hh:mm or hh:mm:ss. However if you are using a third party product such as Microsoft Access to access DP4 you will need to use that product's date or time syntax in SQL statements. For example Access expects dates to be written in the form #26/04/2002# or #26 May 2002#. Although this format is not acceptable to DP4 it will work because Access does not submit dates as literals, but as ? parameters. Character strings may be used instead of dates in many instances, and automatic conversion will take place.

  • Comments

    Comment lines (beginning with -- and ending with the end of line) are ignored, as are C-style comments, such as /* comment */. However C style comments containing the word DP4SQL immediately after the opening /* are treated as compiler hints (rather like pragmas in C), and can be used to help control how the SQL Engine executes a particular query.

  • Special Constants

    The special constants:

    • CURRENT DATE
    • CURRENT TIME
    • USER
    • NO
    • YES

    should be self explanatory.

Information on the following topics may be found by clicking on the appropriate link.

  • Table and Field Names
  • Expressions
  • Conditions and Predicates


Table and Field Names

Field names are written as table.role.field[occurs]. The table name is optional where no ambiguity would arise from its omission. The role must be specified if it is not blank. The occurs may be omitted; in this case the field name is taken as referring to all the occurrences of the field, viewed as a single entity.

For example, a field called ADDRESS with role HOME in table CUSTOMER, having occurs 3, could be referred to as:

CUSTOMER.HOME.ADDRESS

or

HOME.ADDRESS

but not as:

CUSTOMER.ADDRESS

This is true even if HOME is the only possible choice of role. The first line of the address would be CUSTOMER.HOME.ADDRESS[1]. (Note that the ADDRESS field does not have a role in the SALESORD database).

Table or field names may optionally be surrounded by double quotes, for example "<field>". A name so quoted is never read as a reserved word. This provides a method for referring to table or field names which happen to coincide with SQL reserved words (such as DATE or TIME).


Expressions

The SQL language distinguishes between expressions and conditions.

An expression may be either:

  • Any constant

  • Any field name

  • An arithmetic expression involving:

    • Constants
    • Fields
    • The operators +, -, * and /
    • The column functions AVG, COUNT, MAX, MIN and SUM
    • The scalar functions (see Date/Time Arithmetic )

Date and time arithmetic is allowed, and date or time values may be assigned to character fields, but there is no other provision for operators or functions taking character or yes/no operands, or returning results of these types. However from version 4.523 the DP4 SQL Engine implements the non standard UPPER() function which takes a character argument and returns the argument converted to upper-case. Also if the result is used in a comparison in a predicate case will be ignored.


Conditions

A condition comprises one or more predicates, possibly prefixed by NOT and connected together by one of the following:

  • AND
  • OR

Predicates

Each predicate may be a comparison, using the operators <, >, =, <>, <=, >= or one of the following:

  • <expression> [NOT] BETWEEN <expression> AND <expression>
  • <field> IS [NOT] NULL<R>
  • <field> [NOT] LIKE <string-constant> [ESCAPE <string-constant>]
  • <expression> [NOT] IN (<constant>,<constant>, … <constant>)
  • <expression> [NOT] IN (<subselect>)
  • EXISTS (<subselect>)

Pattern Matching

The LIKE operator does wildcard matching; the character '_' in the right-hand string matches any one character on the left, and '%' matches zero or more characters. The ESCAPE clause, if present, specifies an escape character which may precede '_' or '%' to make them match only themselves. For example:

NAME LIKE 'ab%cd\%' ESCAPE '\'

is true if NAME is 'abcd%' or 'abxyzcd%' but false if NAME is 'abcdx'. From version 4.523 DP4 SQL allows the right operand of LIKE to be any character expression and does not restrict you to a constant.

Conditions can only be used in WHERE clauses and HAVING clauses of the SELECT statement; it is not possible (for example) to use a condition to calculate the value of a yes/no field.

Nested SELECT

The IN and EXISTS predicates take a nested select statement as one of their operands. A nested select statement may also be used on the right-hand side of a comparison. In this case the select must return exactly one record, for example:

SELECT name, grade FROM employee
WHERE grade = (SELECT grade FROM employee WHERE name = 'John Brown');

unless it is preceded by one of the words ANY or ALL, for example:

SELECT name, grade FROM employee
WHERE grade < ANY (SELECT grade FROM employee)

The word SOME is a synonym for ANY.

Null Values

Note that, for the purpose of comparisons, all null values are different and they are neither greater than nor less than any other value. A comparison will always return false if one of the values being compared is null. The only way of testing for null values is to use the IS NULL predicate.


Date/Time Arithmetic

Date/time arithmetic in DP4 SQL attempts to follow the IBM SAA standard. Unfortunately, the wording of the standard in this area is obscure, so it may not have been implemented exactly as its authors intended.

The ANSI standard does not support the date or time data types

The following describes date arithmetic; time arithmetic is similar.

Date constants are written in the form dd.mm.yy or dd.mm.yyyy and time constants as hh:mm or hh:mm:ss.However if you are using a third party product such as Microsoft Access to access DP4 you will need to use that product's date or time syntax in SQL statements. For example Access expects dates to be written in the form #26/04/2002# or #26 May 2002#. Although this format is not acceptable to DP4 it will work because Access does not submit dates as literals, but as ? parameters.

There are no date or time constants in the SAA standard

An integer constant followed by one of the words DAYS, MONTHS or YEARS is called a labelled_duration. The corresponding words for times are HOURS, MINUTES and SECONDS. The singular forms of these words are also accepted.

A date_duration is the result of subtracting one date from another. They should be interpreted as yyyymmdd, where yyyy represents the number of years, mm the number of months, and dd the number of days. For example, the result of:

order_date-31.12.1993

is 1012, where ORDER_DATE equals the 12th November 1994. A duration of 0 years, 10 months and 12 days.

The operations possible on dates are as follows:

date + date_duration or labelled_duration
date - date_duration or labelled_duration

giving a date result, or

date - date
string - date
date - string

giving a date_duration.

If a number of months is added or subtracted, and the resulting date is illegal, the day is adjusted downwards; for example, the result of adding 1 month to 31.01.1989 is 28.02.1989.

Subtracting a date from a character string (or the reverse) is the same as subtracting one date from another. The character string is automatically converted into a date and the result is returned as a date_duration.

Adding or subtracting a date_duration or labelled_duration to or from a date has the same effect as separately adding or subtracting the corresponding number of years, months and days (in that order).

The following functions can be used:

Function | Action
DATE(number) | Converts number into a date; 1 becomes 01.01.0001
DATE(string) | Converts a string into a date. The string must be exactly in the date format given by the user's current preference settings.
DATE(date) | Returns its argument unchanged
TIME(string) | Converts a string into a time
TIME(time) | Returns its argument unchanged
DAY(date) | Returns the day part of its argument, which can be a date or a date duration
MONTH(date) | Performs the corresponding operation in the same way as the DAY function
YEAR(date) | As MONTH
HOUR(time) | As MONTH
MINUTE(time) | As MONTH
SECOND(time) | As MONTH
DAYS(date) | Returns the number of days since 01.01.0001
DAYS(string) | Converts the string into a date, and returns the number of days since 01.01.0001
CHAR(date/time) | Converts its argument into a character string. An optional second argument specifies the format to be used; this may be one of the following:
EUR | IBM European Standard
ISO | ISO Standard
JIS | Japanese Industrial Standard
USA | IBM USA standard
 | The default is to use the current preference settings

For example, the expression:

DAYS(date1) - DAYS(date2)

returns the number of days between date1 and date2, whereas:

DAY(date1 - date2)

expresses the difference between the dates in the form YYYYMMDD and returns the day part of the result.

Where a character string is automatically converted to a date (in date subtraction, or in the argument to the DAYS function) the conversion is made as though the DATE function had been used.

Date or time values may be assigned to character fields, and character values can be assigned to date fields; an automatic conversion is performed as though the DATE or CHAR function had been called.


SQL Trigraphs

Trigraphs are allowed in SQL statements. In the current versions of DP4 trigraphs are automatically enabled for SQLDDE, but not otherwise. For SQLEXE this feature can be enabled by running SQLEXE with the following command tail:

SQLEXE -TRIGRAPH

The table shows the special sequences of characters interpreted as escape sequences.

Trigraph | Interpreted as | Trigraph | Interpreted as
??a | @ | ??n | ^
??b | ( | ??o | ,
??c | : | ??p | +
??d | " | ??q | '
??e | = | ??r | *
??g | > | ??s | ;
??h | / | ??t | .
??k | ) | ??u | _
??l | < | ??x | !
??m | - | ??? | ?

The expansion of trigraphs is done even within quotes. A single question mark is interpreted as that, but two in succession are treated as the start of a trigraph. For example, to get two question marks into the SQL statement, you must enter six successive question marks.

The intention of this feature is to allow characters to be inserted into the SQL statement with which the requester program is incapable of dealing.

Contact Itim Technology Solutions Customer care if you require support for SQL Trigraphs in an ODBC connection to a DP4 database. This would be a simple enhancement and can be made if necessary.


SQL Statements

Various standards for the SQL language exist. The DP4 SQL implementation attempts to satisfy, as far as practicable, two of the better known ones, namely the then ANSI SQL2 standard (1992) and the IBM SAA standard (i.e. DB2). Any departures from these are mainly due to the different data types and underlying database structure of the DP4 RDBMS.

The many restrictions on the language specified in the ANSI standard, may seem arbitrary, but are also reflected in the DP4 implementation to simplify the internal working of the DP4 SQL Engine. However, some statements that are illegal according to the standard, but which have an obvious interpretation are, in fact, accepted by the DP4 system. Other than the differences between the DP4 implementation and the ANSI and IBM standards and a few minor enhancements, no attempt has been made to extend the language described by the standards.

The SQL data manipulation statements fall into two categories:

  • Non-cursor operations:

    • SELECT
    • INSERT
    • UPDATE (searched update)

    • DELETE (searched delete)

  • Transaction termination operations:

    • COMMIT WORK
    • ROLLBACK WORK


The SELECT Statement

The general form of this statement is:

SELECT <expression>, <expression>,
FROM <table> [<alias>], <table> [<alias>],
FROM <table> <alias> [LEFT|RIGHT|FULL] [OUTER|INNER] JOIN <table> <alias>
ON <condition>}
WHERE <condition>
GROUP BY <field>, <field>, …
HAVING <condition>
ORDER BY <field>, <field>, …;

There are two forms of the FROM clause. The first form gives an inner join, the second generally gives an outer join and was introduced by the SQL2 standard.

The SELECT statement is described on the following pages:

  • Printing (Selecting) Columns
  • Selecting Records
  • Grouping Records
  • Column Functions
  • Selecting Groups
  • Sorting Records
  • Aliases,Joining a Table to Itself
  • SQL2 Joins
  • Subqueries


Printing Columns

You can select any column from one of the tables listed in the FROM clause, you can also use expressions to display columns derived any of these columns. The specified tables are joined and the expressions are extracted from the join and the columns printed, for example:

SELECT NAME FROM CUSTOMER;

prints all the names of all the entries in the CUSTOMER table.

AS

You can use the word AS to name or rename a column, for example:

SELECT NAME AS CUST_NAME FROM CUSTOMER;

Column aliases are only useful when accessing the database via ODBC - for example they allow a program accessing the database via ADO to access a derived column by name rather than ordinal. The new name is for "external" use only - it cannot be used elsewhere in the statement.

DISTINCT

The word DISTINCT can be used to eliminate duplicate lines, for example:

SELECT DISTINCT NAME FROM CUSTOMER;

prints each name once, even if there are several customers of that name.

Column Abbreviations

In the list of expressions to be printed, the shorthand forms: <table>.*, meaning 'all the fields in <table>' , and *, meaning 'all the fields in all the tables of the FROM clause', are available. For example:

SELECT * FROM CUSTOMER;

prints the whole CUSTOMER table.

When you use column abbreviations, especially an unqualified * in a multi-table query, the order of the fields may not be what you expect. The DP4 SQL Engine will not necessarily order the columns in the order of the tables. Use a list of <table>.* abbreviations instead if the column order matters.


Selecting Records

The WHERE clause, if present, is applied to the output as a condition to list, for example:

SELECT *
FROM CUSTOMER
WHERE NAME LIKE 'Russell';

prints the details of all customers named 'Russell' (double quotes are not accepted).

Output may be obtained from more than one table; all the tables are joined, and the WHERE condition is applied to the resulting joined table (which has n1*n2*... entries if the individual tables have n1, n2,... records). In practice, the WHERE condition is usually used to give equality joins between tables so that only one line is printed for each entry in the first table, for example:

SELECT *
FROM order_header, customer
WHERE customer.customer_number=order_header.customer_number;

prints one line for each order, giving details of the order and of the corresponding customer.


Grouping Records

The GROUP BY clause is used to produce a 'subtotal only' report, where one line is printed for each group, for example:

SELECT order_number
FROM order_line
GROUP BY order_number;

groups together the orders and prints each order number once. You can use the column functions to calculate values such as group totals and averages.


Column Functions

The column functions that act on the groups are:

  • AVG
  • COUNT
  • MAX
  • MIN
  • SUM

For example, to print each order number and the total quantity for that order:

SELECT order_number, SUM(quantity)
FROM order_line
GROUP BY order_number;

To count the number of records in a group:

SELECT COUNT(*) FROM customer;

where COUNT(*) counts the number of customers (as there is no GROUP BY here, the whole customer table is treated as a single group).

The word DISTINCT may be included in the argument of a column function to cause it to ignore duplicate values; for example:

COUNT(DISTINCT name)

counts the number of different values for NAME.


Selecting Groups

The HAVING condition is a 'condition to list' applied to groups. For example:

SELECT order_number FROM order_line
GROUP BY order_number
HAVING SUM(quantity)=5;

prints all order numbers for which at least five items have been ordered.


Sorting Records

The ORDER BY clause indicates the order into which the output is to be sorted. Fields in the output can be specified either by name or by number, and the words ASC or DESC can be used to obtain ascending (the default) or descending order.

For example:

SELECT ol.order_number, c.name
FROM order_header oh, order_line ol, customer c
WHERE oh.order_number=ol.order_number
AND c.customer_number=oh.customer_number
GROUP BY ol.order_number
HAVING SUM(quantity)=5
ORDER BY 2 DESC;

prints order numbers for which at least five items have been ordered, and the corresponding customer names, in descending order of customer name. Notice how aliases, defined after the table names in the FROM clause, are used to reduce the amount of typing.


Joining a Table to Itself

Aliases are used where a table is joined to itself and referred to more than once in a SELECT statement. In this case, the different uses of a table may be followed by an 'alias' name, which can be used instead of the table name to distinguish which instance of the table is meant. For example, suppose you wanted to know which employees have grades higher than Michael Holmes:

SELECT x.name, y.name, y.grade
FROM employee x, employee y
WHERE y.grade > x.grade AND x.name='Michael Holmes'
ORDER BY y.grade, y.name

Here X and Y are used as aliases for the two copies of the EMPLOYEE table which are being joined.


SQL2 Joins

The SQL2 standard introduced more precise methods of specifying how tables are to be joined. There are numerous possibilities, including a number of rarely used options for which there are simpler alternatives. The DP4 SQL run-time supports most of the useful SQL2 join constructions. For a more in depth explanatation of the join concept refer to SQL Joins, which is a revised version of a DP4 User group article.

Both left and right outer joins are supported. For example, to show customers who have not been allocated to regions (as well as those who have):

SELECT customer.name, region.name
FROM customer LEFT OUTER JOIN region
ON customer.region_code=region.region_code

To show the reverse (regions with no customers as well as with) :

SELECT region.name, customer.name
FROM customer RIGHT OUTER JOIN region
ON customer.region_code=region.region_code

The word LEFT can be omitted. Alternatively the word OUTER can be omitted if either LEFT or RIGHT is specified. The word FULL is also accepted instead of LEFT or RIGHT. However FULL outer joins are not implemented correctly - the LEFT outer join is returned instead. The order of comparison in the ON clause makes no difference to the results of the query.

DP4 does not support SQL2 NATURAL joins. ON clauses must be used instead. Neither UNION nor CROSS joins are permitted. In the latter case simply listing the tables without using a JOIN or a WHERE clause is equivalent.

DP4 supports multiple levels of join. You should parenthesise nested join expressions to clarify the intended order of execution. In some cases the DP4 SQL Engine may generate an internal error on unparenthesised nested joins. Apart from this minor restriction, any mixture of left and right outer joins is accepted (but see below for a general comment on right joins). It should be noted that using outer joins may force DP4 to access the database in a less efficient manner. In the example above, using a right outer join will force DP4 to read the region table first and then to read the customer table to find the customers for each region. If the region index for the customer table is not set to update, this means the whole customer table may need to be read for each region. In the above examples the LEFT outer join is not inefficient - in DP4 terms it allows a record with a "missing parent" to be listed normally. You must bear in mind that in SQL customer LEFT JOIN region and region RIGHT JOIN customer are more or less equivalent, so it is not always the case that left joins are efficient and right joins are not. You must consider the structure of the database.

Although right joins are accepted, and the correct results are produced for them, internally they are processed by the DP4 SQL Engine transforming them into the equivalent left join expression. In rare cases where there are multiple levels of join this may cause an internal error. Such an error is not possible with a left join.

Alternate Syntaxes

The following alternative syntaxes adopted from ODBC and other SQL database implementations are also accepted:

SELECT region.name, customer.name
FROM {OJ customer LEFT OUTER JOIN region
ON region.region_code=customer.region_code}

SELECT region.name, customer.name
FROM customer, region
WHERE customer.region_code = region.cd_region (+)

The former syntax is the SQL2 syntax but enclosed in an ODBC escape. In the latter notation, adopted from Oracle and used by some versions of Crystal Reports, (+) implies that the relevant table is to have "imaginary rows" added, (with null columns), to ensure that every item in the first table can be listed. Typically the (+) symbol will appear on the opposite side of the WHERE clause (i.e. it is on the right for a left outer join and vice-versa.)

You are strongly recommended not to use these alternate means of specifying outer joins, especially the latter (which cannot be used to specify multiple outer joins in a non-ambiguous way). The first documented syntax adheres to the SQL2 standard.


Subqueries

Subqueries can be used in the WHERE and HAVING clauses of a SELECT. These are useful where you want to select records or groups from a table with a condition that depends on data in the table itself.

Suppose you want to find all the employees who have the same grade as John Brown, without knowing his grade:

SELECT name, grade FROM employee
WHERE grade = (SELECT grade FROM employee WHERE name='John Brown')

This subquery returns one value, the grade of John Brown.

You can use subqueries that return several values, but you must insert the ANY or ALL keywords before the subquery. For example, to list employees with higher grades than any other employee:

SELECT name, grade FROM employee
WHERE grade < ANY (SELECT grade FROM employee)

In effect, this selects employees that have grades higher than employees with the lowest grade.

For more examples, refer to An Introduction to Database Systems, chapter 6, by C. J. Date.


The INSERT Statement

The INSERT statement has two different forms:

INSERT INTO <table> (<field>, <field>,...)
VALUES (<constant>, <constant>,...);

or:

INSERT INTO <table> (<field>, <field>,...)
SELECT ...;

The first form inserts a new record into the specified table. The specified fields are given the specified values; all other fields are set to NULL. If the list of fields is omitted, the values are matched to fields in their default (sequence number) order.

The second form is the same, except that the field values are taken from the output of the SELECT statement. In this case, as many records will be inserted as there are records output by the SELECT statement.

Note
All the records being inserted must be new records. If any one of the records already exists, an error occurs and none of the records is inserted.

Also, the COMMIT WORK statement must be executed before the insertions are finally made.


The UPDATE Statement

The general form of the UPDATE statement is:

UPDATE <table> [<alias>]
SET <field>=<expression>, <field>=<expression>, ...
WHERE <condition>;

All the records in the specified table which match the condition are updated. If the condition is omitted, all the records are updated.

For each record, the specified fields are set equal to the specified expressions (which may refer to the same or other fields of the table). Fields not specified in the statement are unchanged.

If any of the expressions refer to any of the fields of the table, the values used in calculating the expressions are the value that the fields had before any of the updates were made.

Note that the COMMIT WORK statement must be executed before the updates are finally made.


The DELETE Statement

The general form of the DELETE statement is:

DELETE FROM <table> [<alias>]
WHERE <condition>;

All the records in the specified table which match the condition are deleted. If the condition is omitted, all the records are deleted.

Deleting all records in a table from SQL is potentially very slow, as the DP4 database does not have a quick way of doing this, so the SQL engine must individually delete every record in a single transaction. On a large table this transaction can become too large to be efficient. If possible it is much more efficient to read all the records using a select, and to delete individual records or small groups of records in separate transactions. Other DP4 tools such as QAB or the Deletion Program facility of Report Writer, or the -delete option of REORGDB are better ways of deleting large amounts of data from a DP4 database.

Note that the COMMIT WORK statement must be executed before the deletions are finally made.


COMMIT WORK and ROLLBACK WORK Statements

  • The statement:

    COMMIT WORK;

    causes a checkpoint to be done, so that the changes made by all previous INSERT, UPDATE and DELETE are actually written to the database.

    An implicit COMMIT WORK statement is performed when the SQL interpreter is terminated.

  • The statement:

    ROLLBACK WORK;

    causes all the changes made since the last COMMIT WORK statement to be abandoned.

Although it is required by the ANSI standard, the word WORK in these statements is redundant and can be omitted.

In the DP4 SQL Interpreter you can execute these statements by clicking on the appropriate buttons.


SQL Statement Execution

The purpose of this part of the guide is to provide some information on how the DP4 SQL Engine executes SQL statements, so that developers have the best chance of writing statements in the most efficient form. You must not develop your system in such a way as to rely on the DP4 SQL Engine executing statements in a particular way: wherever possible we will seek to improve the SQL Engine by adding new optimisations, which may mean the database is accessed in a different way.

The code of the SQL Engine is very complex. When a particular statement is being compiled the Engine may transform the statement in many ways to reduce the number of accesses to the database to the minimum and to minimise the amount of extra work it has to undertake, for example to sort records. These transformations take the form of scoring the various parts of the statement in various ways, to determine which to execute first. It is very difficult to explain these transformations in simple terms, especially for the SELECT statement. In case of doubt use the AUXDEBUG ADC to discover exactly how a statement is executed.

The following explanations mention various flags from time to time:

  • The go faster flag is only ever used by SQL and SQLEXE, and is set if the -gofaster command tail is supplied. This command tail causes several statements to be executed incorrectly in some cases. It is not set by the ODBC go faster option. This flag prevents deletes from cascading - the CHILD_DELETE flag is not specified in any calls to rec_kill().
  • The omit checks flag is automatically set if the go faster flag is set. It is also set by the omit checks option in the DP4 ODBC DSN dialog. There is currently no way to set this option in SQL or SQLEXE. When this flag is set any calls to rec_post() are done without referential checks, and deletions are done without orphan checking.
  • The no private option is set by the -noprivate option in SQL and SQLEXE, and the "go faster" box in option in the DP4 ODBC DSN dialog. When this flag is in effect all reads from the database are done with the NO_UPDATE flag, which means that changes made in this transaction are not perceived. This can make a difference to whether a statement succeeds or fails, or to the values in fields that are not set explicitly by an update statement. For an example an INSERT might fail if there is an uncomitted DELETE of a record with the same key.
  • The readonly flag can be be set by the -read_only command tail for SQLEXE, -readonly for SQL. This flag causes all INSERT,UPDATE and DELETE statements to fail, and is not discussed further. For ODBC connections DP4 responds to the SQLSetConnectOption() calls that set SQL_ACCESS_MODE. For ODBC connections the best way to disable database updates is to set a non zero write access levels on the database, and to log on using a username with a zero write access level. Up to release 4.621 you must use write access levels set at a table level, (and this is still the only way to control access to individual tables). However, the DP4 SQL library has recently been updated to cope with a database wide write access level that is higher than the current access level. In this case the database is now opened read only. Previously this resulted in system error 174 and no connection.

Before considering the SELECT statement the other statements are briefly considered.

INSERT

An INSERT statement will be executed by a call to rec_fectch_main(EQUAL). If there is a matching record, then the INSERT will fail, unless the table is auto-increment. If a table is auto-increment any call to INSERT will result in a call to rec_autoinc() followed by a call to rec_post_with_flags() specifying either CHECK_PARENT, if omit_checks is not set, or NO_CHECK if it is.

If the go faster flag is set then the record is not fetched initially, and the INSERT may erroneously update an existing record. Therefore,when go faster is set, INSERT statements are only safe on auto-increment tables .

UPDATE

For an explanation of how UPDATE reads the records it will update, see the explanation of the WHERE clause for the SELECT statement.

The updating done by UPDATE statements is executed by a call to rec_post_with_flags specifying the UPDATE_ONLY flag. This will cause a commit failure at the next COMMIT WORK if the record does not exist. If the omit_checks flag is not set the CHECK_PARENT and CHECK_CHANGE flags are also specified.

If an UPDATE statement changes the primary key of the record, it works differently:

  1. First a check is done to see that there is currently no record with this primary key. If there is then the UPDATE fails
  2. Next the existing record is deleted, specifying flags to rec_kill as for a DELETE statement.
  3. The new record is posted, as though for an update, but omitting the UPDATE_ONLY flag.

DELETE

For an explanation of how DELETE reads the records it will update, see the explanation of the WHERE clause for the SELECT statement.

When deleting records the flags are specified as follows:

  • If go faster is set, then neither CHILD_DELETE or ORPHAN_CHECK is set.
  • If omit checks is set, (but not go faster) CHILD_DELETE is set, but not ORPHAN_CHECK.
  • If neither flag is set both CHILD_DELETE and ORPHAN_CHECK are set.
  • If the go faster or no private flag is set the NO_UPD_CHILD flag is set. This would prevent a subsequent select on a table that the DELETE deleted records in by virtue of CHILD_DELETE from perceiving that records have been deleted. However as reads are always done with NO_UPDATE this does not change anything, except that it makes the DELETE faster.

SELECT

The processing of the SELECT statement can be extremely complex, especially where there are several joins. The goal of the SQL Engine is to produce the correct result set for a query with the least amount of database access through query optimisation. In practice this goal is not always currently achieved, as there are some optimisations which could be performed but which are not. Queries more complicated than the optimiser can deal with may well be executed in a fairly inefficient manner.

WHERE Clause Optimisation

The remarks in this section also apply to ON clauses in SQL2 joins. ON clauses are treated as though they were ANDed with the WHERE clause.

The optimisations performed by DP4 SQL are mainly concerned with identifying the best index to use for reading a table, and reading as few records as possible from a table. For any table in a query the whole table will be read unless an index can be found with N keys for which keys 1 through M, 1 ≤ M ≤ N, there is a condition on the keyfield of the form fieldname=expression or expression=value (where expression is an arbitrary expression that evaluates to a result of the apppropriate type (perhaps even a subquery). What is more there must not be any OR which would allow these fields to take other values. It is much better to issue two queries without an OR than to this query:

select * from orderheader where ordernumber=1 or ordernumber=10000

An alternative, which is optimised, would be to use a UNION of two subqueries.

Important possible classes of optimisation are not performed. For example there is no optimisation which uses start and stop points for searching where BETWEEN, or IN clauses are used, nor of LIKE or MAX searches. Such optimisations will be added as time, resources and general usefulness permit. However many potential optimisations are extremely complex in the general case. Compared with earlier releases the current version of DP4 SQL will optimise many computer generated or modified queries better. Some software has a habit of fully parenthesising all expressions (or predicates to use the official jargon), and prior to the release 4.521/4.617 DP4 SQL gave up trying to optimise when it encountered such parentheses.

To clarify, here are some examples:

  • select * from orderheader where ordernumber=1

    This query is optimised by reading records on the primary key with ordernr=1. As there is at most one of these, only one record is read. Similarly queries using IN are not optimised.

  • select * from orderheader where ordernumber=1 or ordernumber=10000

    This query is not currently optimised. All the records in the order table are read, and the matching records are found by filtering.

  • select * from orderheader where ordernumber=1 union select * from orderheader where ordernumber=10000

    This query is optimised. Two subqueries each read one record from the order table.

  • select * from orderheader where ordernumber>10000 and ordernumber<20000

    This query is not optimised. Similary queries using BETWEEN are not optimised. This is the most important class of optimisation that is not performed, and optimisation of this type of query may be added in future.

  • select * from ordernumber where customernumber=1

    How this query is executed will depend on whether there is an active secondary index that can read the order table by customer number - if there is then it will be used.

Where there are multiple optimisable predicates in the WHERE clause, and there are several candidate indexes, the SQL Engine will pick an index according to one of various rules.

  • Prior to release 4.523 the SQL engine looked for the index that was most completely used, i.e. the one for which the fewest key components were unnaccounted for after all leading keys were matched with conditions in the WHERE clause. It did not consider further indexes if it identified an index that is completely used. Unfortunately this meant that it was not always the best index that was necessarily used. For example consider the following query

    select * from ordernumber where customernumber=1 and orderdate=12.11.1994

    The SQL Engine picked the CUSTOMER index rather than the ORDERHEADER index, because the INDEXDATETIME, which could also be used, has a time component as well. However, even if the query specifies a time as well the SQL Engine continued to use the CUSTOMER index, if it happens to come across it first (which will depend on the internal numbering of the tables), because it is completely used. This might well not be the best choice.

  • From 4.523 the default behaviour is pick the prime index, if all its keyfields are accounted for, or to pick the index that accounts for the most predicates otherwise. Compared with the previous algorithm this one may sometimes perform additional dictionary look ups, (and sometimes fewer), and will usually, but not always be more efficient. The situation where a worse index might be picked is where an index on two or more components is less discriminating than one on a single key. For example a SELECT on some kind of credit card table that sepecified a WHERE clause such as NAME='Aardvark' and YEAR_EXPIRY="01" and MONTH_EXPIRY="05" would most likely work faster if a NAME index is used than an index on the YEAR/MONTH_EXPIRY combination. Therefore in 4.523 you can use a compiler hint to specify that the old rule should be used, or that the primary index should be used.

ORDER BY and GROUP BY clauses can both affect the index used for queries, but only if the compilation of the WHERE clause had not already caused an index to be selected.

  • For an ORDER BY clause the SQL Engine will search for an index that has a leading subset of the keyfields which correspond in order, to the fields in the ORDER BY clause, i.e. the field with key precedence 1 in the index is the first field in the order by clause, the field with key precedence 2 is the second field in the order by clause, and so on. It does not matter if there are more keys in the index than fields in the ORDER BY clause. However, in the current implementation the SQL Engine only uses the ORDER BY field to select an index if all the ORDER BY fields can be matched with an index. This is because the sort algorithm used by the SQL Engine, "quick sort" does not take advantage of any existing ordering in the list. It is possible that the sort algorithm will be changes in future to natural merge sort, used by Report Writer and QAB, in which case ORDER BY will have more influence on the choice of index.

    This optimisation is only done if all the fields in the ORDER BY clause are ordered the same way, i.e. all ascending or all descending.

  • For a GROUP BY clause a similar optimisation is performed, but the SQL Engine just searches for an index that contains all the fields grouped on. The order of the fields is not relevant.

The use of DISTINCT on a column may also require the SQL Engine to sort data, and according to internal documenation also affects the choice of index. However, this does not appear to be the case currently.

Optimisation of Joins

So far we have considered only queries on a single table. A very important part of optimisation is concerned with optimising the number of fetches required to construct the result set of joins. You may find it helpful to refer to SQL Joins, which is adapted from information previously published on the DP4 User group. This explains how joins are constructed, and introduces some of the issues involved in optimising join construction.

The basic example of a join is the following:

select ordernumber,line from orderheader, orderline where order_header.ordernumber=order_line.ordernumber

which can also be written as:

select ordernumber,line from orderheader join orderline on order_header.ordernumber=order_line.ordernumber

There are two possible efficient schemes for performing this query:

  • Treating the orderheader table as the driving table:
    1. Fetch the order headers in turn,
    2. For each order header fetch all the corresponding order lines.

  • Treating the orderline table as the driving table:
    1. Fetch all the order lines in turn,

    2. For each order line fetch the order header if it is not the same as the previous order header.

Depending on other aspects of the SELECT statement the SQL Engine may use either of these strategies. In fact for the query as written here the SQL Engine will use the first strategy. If the order of the table names was interchanged it would use the second strategy.

The choice of strategy may be influenced by the type of join used, and by the WHERE clause. This will be illustrated by some examples.

  • select ordernumber,line from orderline right join orderheader on order_header.ordernumber=order_line.ordernumber

    Because a right outer join is used this query must return a row for every orderheader record whether or not there are order lines for it. This means that the second stragey above cannot be used any more. The same applies to orderheader left join orderline. The first strategy would be ruled out by orderline left join orderheader or orderheader right join orderline

  • select ordernumber,line from orderheader,orderline where order.ordernumber=orderline.ordernumber and orderline.materialcode=19949857

    This statement is in the form which would normally cause the first strategy to be used. However the WHERE clause has a condition on the orderline table which can be optimised (because there is a MATERIAL index that can be used to read the order line table. Therefore the SQL Engine uses a revised version of the second strategy:

    1. Read orderlines that have the specified material code using the material index.
    2. For each orderline fetch the order header if is not the current order header.

    It should be noted that because the material index is used, in principle the order lines may not be retrieved in order number sequence, so more order header accesses may be required, and an order by ordernumber will need a sort.

  • select ordernumber,line from orderline,orderheader where order.ordernumber=orderline.ordernumber and orderheader.customernumber=1

    This statement is the reverse case of the previous one: instead of using the second strategy as would be the case without the customer number condition, a revised version of the second stragey is used:

    1. Read orderheaders for the specified customer using the customer index for the order table.
    2. For each orderheader fetch the order lines.

    In general the SQL Engine will reorder a query so that any condition that can be evaluated without having to join all the tables is evaluated first. Then if the condition fails, the remaining records needed for the join do not have to be read from the database.

  • select ordernumber,line from orderline,orderheader where orderheader.ordernumber=orderline.ordernumber and orderheader.customernumber=1 and materialcode=19949857

    This statement raises the question of what happens when there are two conditions of this type. Now there is no perfect strategy for reading the data, as there are conflicting requirements for which query to execute first. As with the original query with no WHERE clause, the strategy used for creating the result set will depend on the order of the tables in the join.

    As written the statement uses this strategy:

    1. Fetch all the orderlines for the given material.
    2. For each orderline fetch the order header (if it is not the same as for the previous line) and see if it is for the right customer.

    If the order of reading the tables were reversed so the statement began select ordernumber,line from orderheader,orderline then this strategy would be used:

    1. Fetch all the order headers for the given customer
    2. For each orderheader read all the order lines for the selected material. List the order-lines that apply to this order.

    This is possibly not optimal - it might better to read all the order lines for the current order and select the lines that apply. On the other hand, although the order lines are read from the database repeatedly, if there are only relatively few order lined for this particular material the database manager will probably cache them efficiently, and they will be fetched in a single block fetch operation, (actually there will be at least three calls to fetch the records, but the first will only fetch one order line, and the last won't fetch any, so the reads will be cheap), whereas in the other case every order line is read at least once. In this example the strategy used when the the orderline table is listed first is almost certainly superior, as the number of reads is at most the twice the number of order lines for the material, where as in the second case the number of reads is this number multiplied by the number of orders for the customer, though the actual amount of disk access may be less if there are relatively few orders for a particular material relative to the total number of orders.

Where more than two tables are read in a single select statement the current SQL engine can occasionally make very poor decisions about the best order to read the tables in. From version 4.523 you can control the order tables are read in, and therefore force the query to be executed in a more efficient way.

Strategies for writing efficient SQL

Some implementations of SQL allow a programmer to supply hints to the SQL Engine as to how a query should be executed. It is reasonable to do this, because as the relatively simple example above shows there is sometimes no absolutely right way to execute a query. In many cases the best strategy will be different depending on the relative number of records in the tables concerned. The DP4 SQL Engine did not support hints until release 4.523. The hint syntax used by DP4 may affect portability of your SQL to some extent - it will cause problems if your SQL engine does not accept C style comments (an extension invented by Oracle). Although SQL has some powerful features, it also has many glaring gaps, of which this is one of the most prominent (another being the impossibility of simply fetching one record in a particular direction relative to one you know about already).

The most important thing to do is to keep statements simple. In many cases there is no particular reason to generate a hugely complex query. If you are accessing the DP4 SQL Engine from a C/C++ program, or indeed any sufficiently powerful language, using either ODBC or pipes to SQLEXE, then you can probably write your own code to combine results from several simpler queries in an appropriate manner.

Other than this, the only real influence you have over the execution of the statement is the order tables are listed in. The first listed table is the least likely to be read multiple times. Therefore you should put the table for which reading repeatedly would be most inefficient first in the list of tables and join other tables from that one, and whenever possible ensure that tabels are listed in an order which will allow efficient use of indexes. If you use the /*DP4SQL O0*/ compiler hint mentioned in the next section the SQL Engine will not change the order of your tables at all, and you can experiment with ordering the tables in different ways to get the best performance.

The order of predicates in a WHERE clause, and the order of expressions on either side of a comparison has no effect on the efficiency of your SQL in DP4.

Compiler Hints

From version 4.523 you can affect the optimisation behaviour of the SQL Engine to some extent, by using a compiler hint that takes the form of a C style comment. (It might be considered better to use an ANSI or ODBC escape syntax, but the comment syntax has the merit that it can be placed anywhere in the SQL statement and is syntactically independent of it). The compiler hint takes the following form:

/* DP4SQL optionvalue... */

The value will always be a single digit. Currently the only options are O to specify how to reorder the tables in a multi table SELECT, and I to specify how to choose the indexes to be used. For example the comment /* DP4SQL O0*/ will force the SQL Engine to access tables in the exact order specified in the FROM clause of a SELECT. You can specify a default value for the O and I options in the [SQLLIB] section of the DP4 Configuration file. Specify a value for the O option by adding a line of the form table_sort=n. Specify a value for the I option by adding a line of the form table_index=n.

The meanings of the various values for the options are given in the table below:

Option | Value | Meaning
O | 0 | The tables are accessed in exactly the order specified in the FROM clause
O | 1 | The tables are reordered so that where possible tables which are used for grouped or higher precedence sorted fields are accessed earliest. It is not possible to reorder tables where one or other is outer joined.
O | 2 | The tables are reordered so that tables which are mentioned most often in the WHERE clause are accessed first. Tables mentioned equally often are sorted so that where possible (not outer joined) tables which are used for grouped or higher precedence sorted fields are accessed earliest.
O | 3 | The tables are reordered so that where possible child tables are accessed before parent tables that are mentioned in a condition, and otherwise those which are mentioned most often in the WHERE clause are accessed first. Tables mentioned equally often are sorted so that where possible tables which are used for grouped or sorted fields are accessed earliest.
I | 0 | All tables are accessed using the prime index
I | 1 | Tables are accessed using the first index where the total number of keyfields minus the number of leading keys specified by a predicate is as small as possible.
I | 2 | Tables are accessed using the prime index if all its keys are specified by predicates, or the first index encountered that has the largest leading subset of its keys specified by a predicate.

There should only be at most one hint for each option in a statement. Each hint option applies on a global level to the whole statement - you cannot use multiple hints to force different behaviours on different tables.


SQL Joins

In DP4 the word join is typically used to denote the action of fetching a parent record whose primary key is available from corresponding foreign key fields in a child table. This type of join is very easy using the built in processing in QAB and other DP4 utilities such as ReportWriter. The Index analysis program (MAKELINK) analyses the data dictionary and constructs a table called @PC_RECORD (short for parent child record) which describes all the possible direct (and indirect) joins between tables. Except in the case where there is more than one instance of a foreign key in the child table (possible in DP4 through the role concept) a join is typically thought of as the unique parent record that corresponds to the child. In the DP4 API a program joins a table by copying the key fields from the child record that has already been read into the key fields of the parent and calling a DP4 fetch function to read the EQUAL record on the main index.

Typically we might do something like this to retrieve an order and the associated customer record:

askf_n(1,1,&order.order_nr); /* input order number */
rec_fetch_main(&EQUAL,&order.l);
customer.cust_nr = order.cust_nr;
rec_fetch_main(EQUAL,&customer.l);

In SQL a join is any kind of SELECT statement where columns (fields in DP4 terminology) are fetched from more than one table.

The fundamental SQL join is the cross or Cartesian product of the tables, consisting of all possible pairs of rows selecting one row from the first table , and one row from the second table . Any more useful join is expressed by restricting the rows returned through the use of a WHERE clause, or an equivalent construct (of which there are several in the SQL2 standard). In SQL the traditional way of expressing the DP4 code above would be something like:

SELECT ORDERHEADER.*,CUSTOMER.* FROM ORDERHEADER,CUSTOMER WHERE ORDERHEADER.ORDER_NR=? AND CUSTOMER.CUST_NR=ORDERHEADER.ORDER_NR

In order to return the single order and customer record the SQL engine notionally constructs a temporary table consisting of all possible pairs of orders and customers and then filters it leaving only the one row of interest. Of course you have to hope that the SQL engine does not work like this in practice. If you have a database of 10 customers and 50 orders it won't matter too much. If you have 10000 customers and 50000 orders then a SQL engine that worked like this would take all week to find one record.

A SQL engine will contain various optimisations that enable it to recognise certain kinds of query and execute them efficiently by limiting the records actually read from the database in some way.

It is important to realise that any SQL engine will have only a limited range of optimisations. When the SQL engine is unable to optimise a query, it really will construct the Cartesian product of the all the tables used in the query, and then filter it. Therefore you should satisfy yourself that any queries you construct are capable of being executed within a reasonable time on a database of the intended size, and not a "noddy" development database with just a few items of test data.

In SQL a join can be constructed using any kind of valid expression. Thus you could equally well have:

SELECT ORDERHEADER.*,CUSTOMER.* FROM ORDERHEADER,CUSTOMER WHERE ORDERHEADER.ORDER_NR=? AND CUSTOMER.CUST_NR=ORDERHEADER.ORDER_NR-10000

This obviously fetches the customer who placed the order number 10000 less than the one you are thinking of now.
More interestingly, though not especially useful is this example:

SELECT ORDERHEADER.*,CUSTOMER.* FROM ORDERHEADER,CUSTOMER WHERE ORDERHEADER.ORDER_NR=? AND CUSTOMER.CUST_NR<ORDERHEADER.CUST_NR.

This would retrieve the list of all customers with a customer number less than the customer for this order.

In SQL the parent child concept, which is central to DP4, is almost invisible.

SELECT ORDERHEADER.*,ORDERLINE.* from ORDERHEADER,ORDERLINE WHERE ORDERNUMBER=? AND ORDERLINE.ORDERNUMBER=ORDERHEADER.ORDERNUMBER

This query is syntactically identical to the first example. However, since presumably there can be many order lines in a single order, the query may return many rows.

Multi-table joins

DP4 also supports the notion of an indirect join . For example the CUSTOMER table might well have an ACCOUNT_MANAGER field which was a foreign key into an EMPLOYEE table. A program that takes orders might need to check the customer's credit rating. If the customer has a poor payment history it might want to look up the account manager and automatically send a memo to him, requesting authorisation of the order. The DP4 program has to add the following code:

employee.emp_nr = customer.account_manager.emp_nr;
rec_fetch_main(EQUAL,&employee.l);

Note that the program cannot retrieve the "correct" employee details until it has first read the appropriate customer record.
The SQL for this would be something like:

SELECT ORDERHEADER.*,CUSTOMER.*,EMPLOYEE.* FROM ORDERHEADER,CUSTOMER,EMPLOYEE WHERE ORDERHEADER.ORDER_NR=? AND CUSTOMER.CUST_NR=ORDERHEADER.ORDER_NR AND EMPLOYEE.EMPNR=CUSTOMER.EMP_NR$ACCOUNT_MANAGER

(The details would vary according to how the ACCOUNT_MANAGER foreign key was named. Only DP4 would support CUSTOMER.ACCOUNT_MANAGER.EMP_NR as other databases lack roles)

Join failure and Outer Joins.

Consider our first example again (fetching an order and its associated customer record). Up until now we have not considered the possibility that an order record might exist for which the customer record was missing for some reason. In DP4 this situation is called join failure . It is usually up to the application to decide how to handle this situation. Note that the DP4 program will manage to retrieve the order even though the customer does not exist.

In SQL the situation is a little different. Recall that the result of a query is a subset of the Cartesian product of the rows. Now if there is no customer with a particular customer number, any query involving selecting a customer with a customer number equal to that number will necessarily return 0 rows. This could potentially cause confusion - a naive programmer might jump to the conclusion that when the

SELECT ORDERHEADER.*,CUSTOMER.* FROM ORDERHEADER,CUSTOMER WHERE ORDERHEADER.ORDER_NR=? AND CUSTOMER.CUST_NR=ORDERHEADER.ORDER_NR

query fails it is because there is no order with that number.

One potential solution is to issue two SQL queries instead of one query with a join. However this is likely to be inefficient and if the join is complex then it may not be an option. Because of this the notion of an Outer join was added. Roughly speaking, in an outer join if there is no record that satisfies the condition on the record an imaginary "null" record is created, and this record is deemed to satisfy the condition involving that record. "Regular" joins are therefore sometimes known as Inner joins .

The notion of outer joins was not part of SQL originally. As a result outer joins have been implemented in different ways by different database products. For example in MS SQL Server outer joins are apparently specified by using a syntax using *= instead of = in where conditions. In Oracle a completely different syntax using (+) was invented. The SQL2 standard introduced many extensions allowing the specification of joins of various types. Unfortunately this standard is far from being universal as yet - Oracle 7 does not understand SQL2 joins for example, and Crystal reports does not use SQL2 syntax to specify outer joins. The SQL2 syntax has the merit of allowing multiple outer joins to be specified in a non-ambiguous way, which is not the case with the other syntaxes.

Before going any further it would be best to explain the concept of an outer join more accurately. In the example we want our query to return an order record, whether or not the corresponding customer record exists. The Oracle and Crystal reports syntax for doing this is as follows:

SELECT ORDERHEADER.*,CUSTOMER.* FROM ORDERHEADER,CUSTOMER WHERE ORDERHEADER.ORDER_NR=? AND CUSTOMER.CUST_NR (+) = ORDERHEADER.CUST_NR.

The (+) directs Oracle, (or other databases that understand this syntax) to return a NULL row when there is no customer with the customer number matching the customer number in the order. All the fields in the customer record returned in this case will be NULL. (I am not quite sure what the value in the CUST_NR field itself would or should be. In the DP4 implementation this field would also be NULL, which is not perhaps ideal). It should be noted that the field the (+) follows is significant. It establishes which table has imaginary null records added. I should perhaps stress that these imaginary nulls are only hovering in the wings to be called upon when there is no other way of satisfying the condition of the join. If this were not the case the output of the query would be peppered with unwanted null records.

If we had a more general query (missing out the condition on the order number) we would end up with a list of all orders on the database, whether or not the corresponding customer record existed. Another query we might like to issue is one that lists the most recent order for any customer.

SELECT CUSTOMER.*,MAX(ORDERNUMBER) FROM CUSTOMER, ORDER WHERE ORDERHEADER.CUSTNR =CUSTOMER.CUSTNR GROUP BY CUSTNR

This query would miss out customers who had not placed an order yet. In order to get them included as well we would specify an outer join. (The GROUP BY clause is needed otherwise the query would only return the customer who had placed the most recent order of all)

SELECT CUSTOMER.*,MAX(ORDERNUMBER) FROM CUSTOMER, ORDER WHERE ORDERHEADER.CUSTNR (+) = CUSTOMER.CUSTNR GROUP BY CUSTNR

Note that now the (+) is appearing after the ORDER field, because we want imaginary nulls in the ORDER table.

This query could equally well be written:

SELECT CUSTOMER.*,MAX(ORDERNUMBER) FROM CUSTOMER, ORDER WHERE CUSTOMER.CUSTNR = ORDERHEADER.CUSTNR (+) GROUP BY CUSTNR

In SQL2, rather than using a WHERE clause, joins can be specified using various join operators and a USING or ON clause. The ON clause contains the part of the condition that is concerned with limiting which rows of the two tables being joined correspond.

Using SQL2 syntax the last two queries would probably be expressed as:

SELECT ORDERHEADER.*,CUSTOMER.* FROM ORDER JOIN CUSTOMER ON CUSTOMER.CUST_NR = ORDERHEADER.CUST_NR WHERE ORDERHEADER.ORDER_NR=?
SELECT CUSTOMER.*,MAX(ORDERNUMBER) FROM CUSTOMER JOIN ORDER ON ORDERHEADER.CUSTNR=CUSTOMER.CUSTNR GROUP BY CUSTNR

However the queries can equally well be expressed the other way around:

SELECT ORDERHEADER.*,CUSTOMER.* FROM CUSTOMER JOIN ORDER ON CUSTOMER.CUST_NR = ORDERHEADER.CUST_NR WHERE ORDERHEADER.ORDER_NR=?
SELECT CUSTOMER.*,MAX(ORDERNUMBER) FROM ORDER JOIN CUSTOMER ON ORDERHEADER.CUSTNR=CUSTOMER.CUSTNR GROUP BY CUSTNR

These queries would be bound to return the same result set, though possibly in a different order. (This is an example of where optimisation of queries can cause trouble - in DP4 the "natural" form of the queries is more likely to be executed more optimally, than back to front equivalents.) In all four cases this syntax joins the tables using the inner join, so will not list the order if the customer is missing, or the customers without orders. If desired the word INNER can precede the word JOIN. (Probably this is rarely if ever done, though doubtless pedantic reference books will recommend programmers to use it "to avoid ambiguity")

The first two cases can be converted into outer joins by using the word OUTER:

SELECT ORDERHEADER.*,CUSTOMER.* FROM ORDER OUTER JOIN CUSTOMER ON CUSTOMER.CUST_NR = ORDERHEADER.CUST_NR WHERE ORDERHEADER.ORDER_NR=?
SELECT CUSTOMER.*,MAX(ORDERNUMBER) FROM CUSTOMER OUTER JOIN ORDER ON ORDERHEADER.CUSTNR=CUSTOMER.CUSTNR GROUP BY CUSTNR

However, if we consider the alternate form of these queries it should become obvious that this syntax is potentially ambiguous. In SQL2 there are both LEFT and RIGHT outer joins.

  • FROM ORDER LEFT OUTER JOIN CUSTOMER implies a join which adds imaginary nulls to the CUSTOMER table.
  • FROM ORDER RIGHT OUTER JOIN CUSTOMER implies a join which adds imaginary nulls to the ORDER table.

In some sense the table on the left of a LEFT outer join is the "main" table as is the table on the right of a RIGHT outer join. Normally the SQL engine will want to discover records in that table first before attempting to discover which if any records in the other table match.

The notions of left and right outer join are essentially completely equivalent. However, there are probably cases where one or the other (usually LEFT) is more natural than the other.

If a SQL2 join expression specifies the use of LEFT or RIGHT then the word OUTER is optional (and superfluous). If OUTER is specified on its own it implies a left outer join.

It should be noted that using the SQL2 syntax a left outer join will most naturally correspond to an Oracle statement where the (+) appeared on the right hand side of a comparison.

In DP4 SQL you should refrain from using right outer joins where possible. Internally the DP4 SQL engine generates right outer joins by interchanging the two tables and using a left join instead. In a few cases join expressions involving nested joins cannot be converted properly resulting in an internal error from the DP4 SQL engine. This will be fixed when possible if it becomes necessary.

In ODBC, outer joins are considered to be an extension, and are implemented using a so called escape syntax.

SELECT ORDERHEADER.ORDERNUMBER,CUSTOMER.NAME FROM ORDER LEFT OUTER JOIN CUSTOMER ON CUSTOMER.CUST_NR = ORDERHEADER.CUST_NR WHERE ORDERHEADER.ORDER_NR=?

becomes instead:

SELECT ORDERHEADER.ORDERNUMBER,CUSTOMER.NAME FROM {OJ ORDER LEFT OUTER JOIN CUSTOMER ON CUSTOMER.CUST_NR = ORDERHEADER.CUST_NR } WHERE ORDERHEADER.ORDER_NR=?

In the ODBC version of outer joins I believe that the word OUTER is mandatory in theory. ODBC drivers for databases such as Oracle are likely to mangle this syntax into the form expected by the database's native SQL support. This is a bit of a joker in the pack, as some drivers generate completely invalid SQL as a result. For example one early driver generated SELECT statements with two WHERE clauses if both an OUTER and an ordinary join were used.

Multiple joins

If we return to our example where we also joined the account manager record from the employee table you will see that a nested outer join may be necessary. In this case the FROM part of the query using inner joins would be something like:

ORDER JOIN (CUSTOMER JOIN EMPLOYEE ON CUSTOMER.ACCOUNT_MANAGER.EMP_NR=EMPLOYEE.EMP_NR) ON ORDERHEADER.CUSTNR=CUSTOMER.CUSTNR

Our interpretation of the syntax of nested joins is as follows:

  • All join operators are binary operators between two tables, the result of which returns a new table containing all the fields from both tables for the purposes of nesting joins. (Actually, the ON or USING clause may mean that JOIN is actually the first operator symbol in a ternary operator).

  • In an unparenthesised join expression involving multiple joins the joins must be constructed in is determined by the position of ON or USING CLAUSES: the last two tables encountered are joined when an ON or USING clause is encountered. For union or cross joins there is no ON or USING clause, so the tables on the left and right of these operators can be joined immediately. Thus A LEFT JOIN B LEFT JOIN C ON X ON Y, must first joins B and C and then joins the result with A, whereas A LEFT JOIN B ON X LEFT JOIN C ON Y first joins A and B, and then joins the result with C.

    In the DP4 SQL engine nested JOIN operations should be parenthesised. This is possibly not required by SQL2 syntax, though I could find no examples in my book which did not parenthesise them. Currently not all unparenthesised nested joins are compiled.

However inner,union or cross joins are combined with each other or a single outer join the end result will be the same. Where multiple OUTER joins are used this may not be the case since in a nested JOIN expression different ways of expressing the same join can yield differing rows with imaginary null records.


Comparison with Other SQL Implementations

Omissions

The features under this heading are either in the ANSI standard or in the IBM SAA standard, but are not implemented.

Statements Not Supported

The following statements are not supported:

  • ALTER TABLE
  • DROP TABLE
  • COMMENT ON
  • DROP VIEW
  • CREATE INDEX
  • GRANT
  • CREATE TABLE
  • LOCK TABLE
  • CREATE VIEW
  • REVOKE
  • DROP INDEX

Special Registers Not Supported

The following special register is not supported:

  • TIMESTAMP

Differences

The following features are either in the ANSI standard or in the IBM SAA standard, but are implemented differently by DP4:

Identifier Names

Identifier names conform to the DP4 convention for naming fields, for example:

_ is allowed

. is allowed in the names of database fields (which must be named as role.field). $ is allowed as an alternative to .

# is not allowed

In addition, an occurs number in square brackets or braces is allowed.

Field Names

A field name without a dot (or dollar) is regarded as having a blank role name. A field name without an occurs number refers to all the occurs of the field, which is regarded as a single field. Up to 16 characters are significant. Case is not significant except within character constants.

Data Types

The data types correspond to the DP4 data types (for example, uppercase and Boolean data types are supported). There are Boolean constants for YES and NO.

Expressions

Expressions with Boolean values are not currently supported.

Arithmetic

Arithmetic on integers gives results of type real number, not long integer. In particular, the result of division of integers is not truncated to an integer answer.

Authorisation

Authorisation is by the usual DP4 mechanism of read/write access levels.

Sorting

For the purposes of sorting, NULL is regarded as lower than every other value (instead of higher).

The not-equals operator may be written in one of these ways: !=, ^=, <>. This enhancement comes from Oracle.

Dates and Times

Date constants must be written dd.mm.yyyy and time constants in two ways hh:mm or hh:mm:ss

Comments

SQL statements can contain comments surrounded by /* and */. This enhancement comes from Oracle. Comments beginninf with the word DP4SQL are treated as compiler hints.

CHAR Function

The CHAR function applied to a date returns 11 characters (instead of 10); this is because the date may be converted into the form dd.mmm.yyyy if the month is to be written as a word.

The words to be used as second parameters to the CHAR function, namely: EUR, ISO, JIS and USA are treated as reserved words. Some other reserved words which are not implemented are not treated as reserved.

Data Integrity

The various data integrity constraints (such as parent checking and child deletion) are performed by the DP4 system; in standard SQL none of these constraints exist. To allow these checks to be made, it is necessary to prevent the UPDATE statement from changing the primary key of a record.

Other Constructs

Many syntactically incorrect constructions are not detected, and will execute (usually with the expected result).


DP4 DD4 Interface

DP4 DDE Interface

This part of the manual describes how to and use the DP4 DDE interface for Windows. This product allows third-party applications that support the DDE standard to access DP4 databases. The main difference between the DDE and ODBC standards, apart from the former somewhat predating the latter, is that the data is dynamically linked. This means that when the data changes on the DP4 database the linked representation of it in the client application is updated.

This product is considered to be obsolete, and is no longer officially supported.

Prerequisites

DP4 for Windows 3.x must be installed and operational, as described by the Windows installation guide. The DP4 SQL for Windows 3.1 product must also be installed.

Components

The DP4 DDE Interface is supplied and installed as part of the SQL for Windows 3.1 product on the DP4 CD ROM as described in Installing DP4 SQL.

Using the DP4 DDE Server

For a typical example of this product's use see Linking to DP4 from Microsoft Excel.


Linking to DP4 from Microsoft Excel

Within Micrsoft Excel, you can create a DDE link to your DP4 database by entering a formula array. More precisely, the formula is a remote reference formula of this general construction:

=SQLDDE|<database_name>!<SQL_query>

where SQLDDE names the DP4 SQL DDE server, <database_name> is the DP4 database from which you want to retrieve data, and <SQL_query> is the SELECT statement that is run against the database.

The following example uses such a formula to retrieve customer names:

  1. Select the array range by highlighting a block of cells, one column wide
  2. Click the Formula bar and enter the formula as:
    SQLDDE|salesord!'SELECT name FROM customer'
  3. Press <Ctrl+Shift+Enter>. Excel establishes the link and fills the array with the results of the query on the SALESORD database

The sample salesord.xls worksheet supplied includes the above example. For more information on this facility or about array formulas, refer to the information provided by Microsoft Excel.


trm_set_progname()

Purpose | Windows only, sets the current program
Syntax | void trm_set_progname(progname)
Parameters | char* progname | Program name to set as current program
Description | The trm_set_progname() function sets the specified program as the current program for the terminal manager (TRM<n>). This function is only relevant in Windows, where it causes the appropriate settings in the DP4 configuration file to be read.


3DBorderSize

[TRMW]
3DBorderSize=3

normally specified in [TRMW]only.

The number of pixels to allow for Windows 95 style 3D borders. See also 3DBorderStyle.

If you have maps that were designed using pre 4.612 versions of DP4, specifying 3dBorderSize=6 will restore maps to their original size.


3DBorderStyle

[TRMW]
3DBorderStyle=0 or 1

normally specified in [TRMW] only.

If 0 (default on Windows 3.1 and NT 3.5x) the border style used for maps with a 3D border is the same as the style normally used for DP4 help messages.

If 1 (default on Windows 95 and NT 4) 3D style maps are drawn with the Windows 95 look.


[SYBASE]

This section is used only by the AUXSYBAS program, used to access data from Sybase. AUXSYBAS is not a standard DP4 component. The following entries may be specified in the Sybase section.

  • dbo_name
  • col_order
  • first_occurs
  • max_max_cursors
  • max_statements
  • role_sep
  • schema_name
  • short_sql
  • use_checksum
  • use_long_query
  • use_subquery


Default Window Size and Position

The following information will only apply when there is no size or position information in either the [trmw] section or the section for the program itself. The size and position of an application window are otherwise determined by the x, y, width, and depth values.

  • Prior to 4.620, or if use_workarea=0 is set the following scheme is used:

    The default screen size used for DP4 programs is height=y_factor*rows and width=x_factor*cols. Prior to release 4.616 x_factor and y_factor cannot be set explicitly, and always take their default values. From 4.603 to 4.614 rows and cols could not be set explicitly and the userdata screen depth and width were used instead. They can be set in all other releases.

    If the window size calculated from these initial defaults is very near the size of a maximised window that size is used instead, and the window is also positioned as a maximised window, to avoid the window contents appearing off the edge of the screen. Otherwise the initial position for the window will be chosen by the Windows operating system.

    This scheme works well for applications that display information in a fairly fixed form that cannot take advantage of a large window.

  • From 4.620 and if use_workarea=1 the default size and position is chosen so that the client area (including the client edge if present) fills the size of a maximised window. The size of a maximised window will vary depending on the taskbar size and properties. It may also be reduced if other toolbars such as the Microsoft Office toolbar is present.

    This option is suited to programs that always want to use the whole available area of the screen, regardless of how big it is.


autoload

[system]
autoload=1

This entry controls whether the DP4 service/database manager is started/loaded automatically when an application that uses it runs. Autoload is enabled by default (autoload=1). To disable it set autoload=0.

Win32 Version

With the Win32 version of DP4 applications can autoload DP4 in a fully configurable way. The autoload differs from that provided in other operating systems in the following ways:

  1. DP4 does not auto unload on termination (unless configured to do so).

  2. The full configuration for the machine is autoloaded, not just the standalone database manager.

The autoload runs srvw32 -start by default. You can configure the command to be run by setting loader=Command in the [win32] section.

Win16 version

Prior to 4.520/4.616 release the 16-bit version of DP4 would not autoload unless pre500=0 is set in the [WINDOWS] section. The program loaded by autoload is set using loader=Command in the [windows] section.


button_text_centred

[TRMW]
button_text_centred=0

normally specified in [TRMW] only.

From 4.622 you can use this setting to completely over-ride the alignment flags set for buttons. This option is intended for use by a specific customer and is unlikely to be useful generally.


buttoncheckboxes

[TRMW]
buttoncheckboxes=1

normally specified in [TRMW] only.

From 4.617 checkboxes that do not have any text associated with them are drawn to look like buttons rather than ordinary checkboxes. This is done so that there is a visible indication when they have focus. Specifying buttoncheckboxes=0 disables this behaviour.


buttonframe

[TRMW]
buttonframe=1

normally specified in [TRMW] only. Processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

The width of the border drawn round buttons that have focus or are currently 'Default' buttons. Some systems set this to 2 to provide a stronger visual indication of the currently active button.


ButtonHeight

[TRMW]
ButtonHeight=Calculated value

used in 4.51x programs only.

This value sets the size to be used for buttons. The default is 7/4 of the normal font height.


buttonstyle

[TRMW]
buttonstyle=0 or 1

Normally specified in [TRMW] only. This entry was introduced in the 4.613 release.

If 0 (default on Windows 3.1 and NT 3.5x) the border style used for buttons and VCR buttons is the same as is used by Windows 3.1.

If 1 (default on Windows 95 and NT 4) the border style used for buttons and VCR buttons is the same as is used by Windows 95.

If 2 (supported from 4.621) buttons are drawn using bitmaps called buttonup.bmp, buttondown.bmp, buttonactive.bmp, and buttondefault.bmp, stored in the dp4.sys directory. See Skinning DP4 for more information. (If none of these bitmaps is present the buttons are drawn as though buttonstyle 1 was in effect. If buttonup.bmp is present, but some other bitmaps are missing you will get a mess.)

From 4.622 four new values, 3 through 6 are available. With these values the appearance of a button will depend on whether or not there is an XP theme active or not, and possibly on the colour scheme of the button. The following table shows how the values are used.

Value | Button uses Default Colour Scheme | Windows XP Theme in effect | Appearance
3 | Yes | Yes | Windows XP Themed Button
3 | No | N/A | Windows 95 style button
3 | N/A | No | Windows 95 style button
4 | Yes | Yes | Windows XP Themed Button
4 | No | N/A | DP4 Custom button
4 | N/A | No | DP4 Custom button
5 | N/A | Yes | Windows XP Themed Button
5 | N/A | No | Windows 95 style button
6 | N/A | Yes | Windows XP Themed Button
6 | N/A | No | DP4 Custom button


charset

[TRMW]
charset=0

Selects the character set to be used for the default font.

0 | selects the ANSI character set
1 | selects a font based on font name without regard to character set
128 | selects the shift JIS character set
255 | selects the OEM (MS-DOS) character set

Other values are possible for this entry. Look in the Windows header file wingdi.h for other possible values, or consult Microsoft documentation.


checkbox_height

[TRMW]
checkbox_height=calculated number

checkbox_height=n controls the height of the box part of a checkbox control or radio button. Normally these are drawn at a fixed size determined by Windows, no matter what the size of the text of the control. Setting this option allows this size to be over-ridden.

This may be useful on touch screens, for which the default checkbox size is inconveniently small. This setting was introduced into 4.623 in builds dated on or after 7 March 2007.


checkbox_width

[TRMW]
checkbox_width=calculated number

checkbox_width=n controls the height of the box part of a checkbox control or radio button. Normally these are drawn at a fixed size determined by Windows, no matter what the size of the text of the control. Setting this option allows this size to be over-ridden.

This may be useful on touch screens, for which the default checkbox size is inconveniently small. This setting was introduced into 4.623 in builds dated on or after 7 March 2007.


Client Edge

From 4.616, On WIN95/NT4 a 'client edge' may be drawn round certain parts of the screen. This improves the appearance of menus and help messages, but may impact existing programs if you are running at a low resolution.

The client edge is enabled if you have useframe=1 and disabled if you have useframe=0 or useframe=−1.

In previous releases of 4.6xx all maps were created as child windows of the application main window which could be discovered by calling trm_get_hwnd(). From 4.616 this window usually has a child window of the same class. The handle of this window can (before any maps are displayed) be found by calling GetTopWindow((HWND) trm_get_hwnd()). You should first call map_clear_all() and trm_refresh() to ensure that there you are not confused by windows used for copyright messages or system messages. The window may not exist, (so 0 would be returned), as the facility is configurable. Most maps are created as child windows of this window. However the following maps are created as children of the main window instead:

  • A menu bar displayed at the top of the screen; (Pulldown menus are displayed as WS_POPUP windows, owned by the parent of the menu)
  • Maps displayed with the BOTTOM flag which are not movable.

This child window will be periodically (possibly very frequently if you have help messages) sized to fit inside any menu bar or BOTTOM maps (especially help messages). If you subclass this window and trap the WM_WINDOWPOSCHANGING message you can further control its position and size. For example you could reduce its size by an amount sufficient to display a Windows toolbar control above it or a status window below it, or indeed any other window. Unless you are running on Windows 3.x or NT 3.x the window may have a 'client edge' which will slightly reduce the area available to maps.

The client edge improves the appearance of help messages and menus. You can change the frame style of the window if you wish (from C/C++ code).

The terminal manager will only put a client edge on this child window by default if you are running Windows 95 or NT 4 or later and are running at a sufficiently high resolution: normally 800*600 with small fonts or 1024*768 with large fonts.

If, for some reason, this window causes you a problem you can force all maps to be created as child windows of the main window, and prevent creation of the child window by specifying useframe=-1. However, there may be some problems if you do this, for example a menubar at the top of the window will be able to scroll off screen.


Colours

[TRMW]; a set of obsolete values, applying only when the 4.5xx terminal manager is in use.

In 4.5xx you can set the exact shade to be used for the various screen colours by specifying a value for one of the following entries.

Black | Blue | Green | Cyan
Red | Magenta | Brown | Lgrey
Dkgrey | Lblue | Lgreen | Lcyan
Lred | Lmgnta | Yellow | White

Entries are specified as RGB values like this:

Yellow=(255,255,0)


cols

[TRMW]
cols=user.scrn_width

applies when 4.5xx, or 4.616 and later terminal managers are in use.

This sets the screen width for the program. The default value is the screen width stored in userdata.sys, which is usually 80.In 4.5xx if the program window is reduced to have a width below character_width*cols a scroll bar is displayed.

This default value for this entry was documented incorrectly in previous versions of this manual.

It is normal to set this to 80.

See the width and default window size pages for more information.


Coordinate system

Map sizes and coordinate controls are stored in an internal coordinate system designed to ensure that maps will display satisfactorily on any device whatever the default font and resolution. From 4.616 you have some additional control over this.

The internal coordinate system is based on assigning the size of a standard character in the default font as 16 internal units horizontally and 32 units vertically. (Typically characters are usually about twice as tall as they are wide). At run time the terminal manager calculates the actual size to be used for this character based on the chosen default font.

The height is the external lead in + the actual character height. This is called the y_factor.

The width is the maximum of the following three quantities: the reported average width, the width of "n", the width of "8". This is called the x_factor.

(For narrow spaced maps the terminal manager only takes the maximum of the average width and the width of 8). (This is called narrow_factor)

From 4.616 these factors are modfied as follows: if isometric=1 is set in then the terminal manager adjusts x_factor and y_factor so that a rectangle that has an equal width and height in internal units appears as a square on screen.

It does this by increasing one or other of x_factor and y_factor as required. If x_factor is increased controls will appear slightly larger horizontally, if y_factor is increased they will be larger vertically. Font sizes are unchanged. The default for isometric (its value if it is missing from the DP4 configuration file), is 0, however it is set to 1 in the issued DP4 configuration file. The value of isometric will only have an effect if your default font is NOT twice as high as it is wide. Note that the System font (at least on US Windows) is isometric anyway. Setting isometric=1 when you use another font (such as MS Sans Serif) as the default font may change the size of maps on screen. Doing so will ensure the aspect ratio of printed maps is the same as display maps, though this will only be necessary if there is significant difference in how Windows realises the font when printing from on screen.

From 4.616 you can specify values for x_factor and y_factor in the DP4 configuration file. The only time you would be likely to need to do this is if you wanted to use a default font that was slightly smaller than your desired x_factor and y_factor. For example if you design an application to run full screen at 640*480 you can ensure it will also run full screen at 800*600 resolution, whether the user chooses "Large" or "Small" fonts by specifying x_factor=10, y_factor=20.

Changes in 4.620

If you increase (or decrease) the size of maps by changing the x_factor or y_factor explicitly, or by changing the default font you use to one that has a different x_factor or y_factor, text may not fit nicely into controls. For example, text is usually aligned to the top of controls, and if the size of controls is increased too much this can become obvious. From 4.620 you can also adjust the size of text in fonts, using the new x_inch and y_inch settings. These settings change the number of points per logical inch from the default value of 72. A smaller value makes your fonts smaller. For full details see the description of the x_inch setting.

From 4.620 DP4 ignores the x_factor and y_factor settings when printing with DFPRINT. Originally x_factor and y_factor where used to that maps would print at the same logical size as they appear on screen. However, it is frequently the case that the size of maps on screen needs to be increased, because Microsoft Windows deliberately does not report the screen resolution correctly, whereas it is very unusual for printer resolutions to change much.

If required you can control all these settings for maps printed with dfprint using dfprint_x_factor,dfprint_y_factor, dfprint_x_inch,dfprint_y_inch.


dbo_name

[SYBASE]
dbo_name = ””

This is like schema name, but provides an additional level of table hierarchy.


debug_console

[WIN32]
debug_console=0

If 1, DP4 programs which contain diagnostics create a console (like a DOS box) to receive the diagnostics instead of sending them to the ERRLOG program.


debug_date

[errlog]
debug_date=0

If 1, the date and time are printed at the start of each line in the DEBUG.OUT file.

If 0 the date and time is printed when more than one second elapses between diagnostics.

The date and time is not echoed to the screen.

You should note that the date and time are the date and time when the error message is received by the error logging program, not when it is generated. Therefore this mechanism cannot be relied on for analyzing time-critical errors.


debug_echo

[errlog]
debug_echo=0

If 0, diagnostics are not displayed but are only written to a file.

If 1 diagnostics are displayed in a special errlog window on screen.

If you require this option you must enable the 'Allow service to interact with desktop' checkbox when using Windows NT or Windows 2000 (set in the startup properties for the DP4 service).

As of release 4.622 this setting only sets the initial state of the error log window. Provided the error log is loaded you can show it at any time by selecting the "View log file" option from the popup menu of the DP4 service icon in the taskbar notification area, and you can hide the error log window by closing it.


debug_file_flush

[errlog]
debug_file_flush=0

If 1, DEBUG.OUT is flushed to disk after every diagnostic. Otherwise DEBUG.OUT is flushed when the ERRLOG program is closed down.

There should be no reason to use this under Windows NT. Under Windows 95 it might be useful if a DP4 system causes Windows to hang completely.


debug_file_name

[errlog]
debug_file_name=debug.out

This specifies the filename used to contain diagnostics.

As of release 4.622, the issued DP4 configuration file for Windows platforms specifies the value debug.txt for this option, as this makes viewing the file with Notepad easier.


debug_file_size

[errlog]
debug_file_size=128

This specifies the maximum size in Kilobytes to which the debug file can grow. If 0, it is allowed to grow indefinitely.


debug_keep_file

[errlog]
debug_keep_file=0

If 0, the current DEBUG.OUT is renamed DEBUG.BAK,deleting any old DEBUG.BAK first, when it reaches the maximum size and a new debug file is created.

If 1, the current DEBUG.OUT is renamed DEBUG.000, DEBUG.001 etc, when it reaches the maximum size, but only if a line containing the word ERROR has been printed since DP4 was loaded.


Default font

From 4.616 it is possible to specify the "default font" in a more user friendly and portable way. The default font is either one of the built in fonts such as the system font or a user specified font. You choose one of the built in fonts by selecting one of types A to E in USERDATA. You can select a custom font by specifying a font name and size in the DP4 configuration file (in the [trmw] or [progname] sections). The fontname is specified with a string such as font="MS Sans Serif". In previous releases the size of the font had to be specified by specifying the font height, and optionally width, in pixels (fh=16 for example). From release 4.616 you can specify a point size instead (pointsize=10). This is slightly more portable in theory, in that the font size should be constant whatever the resolution of the screen. In practice point sizes on screen are unreliable as changing the resolution does not normally change the reported number of pixels per inch on which a point size calculation has to be based. However it will give you a way of specifying a font in a way consistent with other applications. If pointsize is specified fh and fw settings are ignored. From 4.620 the USERDATA program allows you to choose a font from the available Windows fonts and writes the information into the DP4 configuration file automatically. Source code is also available from the DP4 user group site showing how a program can discover and set its default font.

It is possible to force a particular default font on a per application basis. This is done by setting forcefont=1 in the [progname] section of the DP4 configuration file. (If you set forcefont=1 in the [trmw] section it has the effect of completely overriding the USERDATA.SYS font setting). forcefont=1 was advertised in release 4.614 but did not actually work. With that release you actually had to set forcefont=99. You can also force one of the built in fonts. For example forcefont=13 forces the system font. (The values to use for built in fonts are taken from the numeric values of the appropriate constants for GetStockObject())

If you choose a userdata type F font or use forcefont=1, then you should now specify a value either for pointsize or fh as well as font=fontname. The technique of letting Windows choose the font size for you will prevent the new DFPRINT program from working properly. You may also specify values for weight, charset, and, if you used fh rather than pointsize, fw

From 4.616 the API used to select the default font has altered slightly. This has been done to ensure maximum WYSIWYG behaviour of the new DFPRINT print program. This will have no effect unless you specified a default font that could not be created exactly as requested. If you did specify a font like this you may find the font size or appearance is different - it will now more closely match what you have asked for. If this is not actually what you wanted then you would need to change your DP4 configuration file settings.


defer_bug

[TRMW]
defer_bug=1

normally specified in [TRMW] only.

This setting, added in a patch to releases 4.621 and 4.622, controls how trmw32.dll uses of the Windows API DeferWindowPos(). In some circumstances this API fails and causes a leak, which can cause TRMW32.DLL to suffer from resource starvation and eventually crash.(It is not known exactly what triggers this failure in the API - it seems to be very sensitive to the recent history of how Windows have been created and updated on screen, and apparently similar maps can give rise to different results)

There are three possible settings:

  • If defer_bug=0 is set, DP4 expects DeferWindowPos() to work as documented. If a call to DeferWindowPos() fails DP4 does not call EndDeferWindowPos(). This is how Microsoft say you should use handle a failure from DeferWindowPos(), but on Windows XP and 2000, this causes a leak. This setting might be needed on a hypothetical future release of Windows where this bug has been fixed.

  • If defer_bug=1 is set, DP4 attempts to work around the bug in DeferWindowPos() by always calling EndDeferWindowPos() whether DeferWindowPos() succeeds or not. When DeferWindowPos() fails, the last good handle returned by DeferWindowPos() is passed to EndDeferWindowPos(). MSDN documentation states that this is the wrong thing to do, but it stops the leak from happening and does not have any known adverse consequences.

  • If defer_bug=2 is set, DP4 does not use DeferWindowPos() to change the Z-order of windows. Instead it always uses SetWindowPos(). You can use this setting if you find that you are suffering from the leak, and the work-around added does not fix the leak, or has other adverse consequences. Using this setting might cause the screen display to flicker more than when DeferWindowPos() is used.


depth

[TRMW]
depth=calculated value

Processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

depth is the depth of the client area of the main program window. If this is not specified then the value is defaulted in one of two ways:

  • If use_workarea=1 (available from 4.620) then the client area depth matches that of a maximised window.
  • Prior to 4.620 or if use_workarea=0 the default depth is calculated as rows * y_factor.

This value is updated when a program terminates. To prevent updating make the DP4 configuration file read only or set save_position=0.

See the cols, y_factor, and default window size pages for more information.


detach

[TRMW]
detach=0

If set to 1, the program is created with a minimised window. It is normally specified in named program sections only.

Windows cannot be minimised on Windows CE. Prior to 4.622 this option is not supported on Windows CE. From 4.622 if a program is run with this option set the program is initially started so that it is invisible but has an icon and a context menu in the taskbar notification area. The menu allows you either to convert the program to running in the foreground or to exit the program.


[TRMW]

[DFPRINT]

From release 4.621 the [DFPRINT] section can be used to specify a value for any entry that is used by the DFPRINT program when formatting output that would normally be read from the [TRMW] section.

For example, some 4.621 user-interface features like texture may not work well with printed or file output.

Normally the hierarchy for reading [TRMW] type enries is as follows:

  • Look for explicit value in [progname] section. If a value is found use that value
  • Look for an explicit value in [trmw] section. If a value is found use that value
  • Use the default value

Inside DFPRINT this hierarchy is modified as follows:

  • Look for explicit value in [progname] section. If a value is found use that value
  • Look for an explicit value in [dfprint] section. If a value is found use that value
  • Look for an explicit value in [trmw] section. If a value is found use that value
  • Use the default value

It should also be noted that whereas in regular applications the current program name can be controlled by trm_set_progname(), and in particular is changed to the name of the QAB program by QAB, inside DFPRINT the program name is always derived from the name of the executable that created the file DFPRINT reads.

In addition there is one special [DFPRINT] value: no_texture.


dfprint_x_factor, dfprint_y_factor, dfprint_x_inch, dfprint_y_inch

[TRMW]
dfprint_x_factor=calculated number
dfprint_y_factor=calculated number
dfprint_x_inch=72
dfprint_y_inch=72

From release 4.620, when an application is printing using the DFPRINT program the x_factor, y_factor, x_inch and y_inch settings are ignored. This is to avoid problems that otherwise occur during printing when an application must run on a variety of different screen resolutions. For printing these settings replace the settings used outside DFPRINT.

These are regular [trmw] section settings, and may be set in a [progname] section for applications that require special handling. Do not set them in a [DFPRINT] section


DFPRINTForeground

[TRMW]
DFPRINTForeground=0

Must be specified in this section, and not a DFPRINT section.

If set to 1 the DFPRINT program will force itself to be the foregound window and take focus if it requires user input. This option has been added because users sometimes press the Enter key too many times after requesting printing. This can case an apparent hang when the terminal manager is waiting for DFPRINT to finish, but DFPRINT is waiting for user input but is stuck behind the application. Naive users may not realise what has happened.


dfshutdown

[TRMW]
dfshutdown=0

This entry is implemented in 4.5xx, and from 4.617.

If 1 the terminal manager will listen out for the special message which is broadcast when the DP4 service is stopped. When the terminal manager receives this message it will attempt to terminate the program.

If 0 the message is not picked up. Normally the applications will fail when they next try to access the database manager.


disablestyle

[TRMW]
disablestyle=0 or 1

Normally specified in [TRMW] only. This entry was introduced in the 4.613 release.

If 0 (default on Windows 3.1 and NT 3.5x) disabled text in checkboxes buttons and text controls is drawn in the "greyed-out" text colour, as used by Windows 3.1.

If 1 (default on Windows 95 and NT 4) disabled text in checkboxes buttons and text controls is drawn with an embossed (3D) effect using 3D border colours in the same way as Windows 95.


dp4home

[WIN32] [WINCE] [UNIX]
dp4home=System dependent default

This value specifies the directory where the local DP4 licence file is stored. The same location is also used for the default userdata.sys file, and may be used for other files (for example the DP4 terminal server creates its temporary cache files in subdirectories of this directory.)

You only need to specify a value for this entry if you want to store dp4.sys other than in the default location for the operating system. You are recommended not to do this.

See Names and Locations of Configuration Files for details of how the default value is determined.


[ERRLOG]

In WIN32 and Windows CE environments a program called ERRLOG can be loaded as part of the DP4 system. We may ask you to use this program to assist with problem diagnosis. This program is loaded by setting the load_errlog parameter in the [system] section to 1.

The following entries control the behaviour of the errlog program:

  • debug_date
  • debug_echo
  • debug_file_name
  • debug_file_flush
  • debug_file_size
  • debug_keep_file
  • ignore_shutdown


errlog

[win32]
errlog=errlog.w32

[wince]
errlog=errlog.wce

This sets the name of the error logging program that SRVW32 or SRVWCE runs if load_errlog=1 is set in [SYSTEM].


fh

[TRMW]
fh=0

Height of default font. If 0 is specified, Windows will select a value, but this may cause problems when printing. See default font for more information.


first_day

[TRMW]
first_day=1

first_day=n where n is a value between 0 and 6 sets the day of the week that is shown at the left of a DP4 calendar control (introduced into 4.623 versions of trmw32.dll and trmwce.dll built on or after 7th November 2007). The default value of 1 shows Monday. 0 shows Sunday at the left, and 6 shows Saturday. Oher values are also permitted.


flatten_dialog

[TRMW]
flatten_dialog=0

This setting was introduced in 4.621, but has changed considerably in 4.622.

4.621

If 1, maps that have a 3D border and a title bar are shown with a flattened border similar to the border used in Windows XP.

If 0 (maps with a 3D border and title bar are shown with a 3D effect.

4.622

  • If 0 (maps with a 3D border and title bar are shown with a 3D effect, as "normal".

  • If 1, maps that have a 3D border and a title bar are shown with an XP window border, and the borders of 3D edit controls are also changed to match those of XP, but only if a Windows XP theme is in effect.

  • If 2, maps that have a 3D border and a title bar are shown with an XP window border if there is an XP theme, or a fat flattened border (reminiscent of Windows 3.x) otherwise. The borders of 3D edit controls are also changed to match those of XP, if there is a Windows XP theme is in effect, or are flattened anyway otherwise.

You should note, that when there is an XP theme and flatten_dialog is non zero, you cannot be sure of the exact appearance of the map. For example there are various shareware programs available that provide new themes beyond the MS supplied themes.


font

[TRMW]
font="System"

Default font to use where userdata type F is selected or forcefont is 1.


forcefont

[TRMW]
forcefont=0

Normally specified in individual program sections only.

It is possible to force a particular default font on a per application basis. This is done by setting forcefont=1 in the [progname] section of the DP4 configuration file. (If you set forcefont=1 in the [trmw] section it has the effect of completely overriding the USERDATA.SYS font setting). You can also force one of the built in fonts. For example forcefont=13 forces the system font. (The values to use for built in fonts are taken from the numeric values of the appropriate constants for GetStockObject()). If you override the default font then you will need to specify values for these entries: font, pointsize or fh, and may want to specfy values for weight, charset, and fw.

See Default font for more information.


fw

[TRMW]
fw=0

Width of default font.

If 0 Windows will select a value based on the value of fh. See default font for more information.


HelpBorderSize

[TRMW]
HelpBorderSize=6

The number of pixels to allow for the border style used for help messages. The minimum value allowed is 5.


hide_dead_chrome

[TRMW]
hide_dead_chrome=0

hide_dead_chrome=1 changes the behaviour of "prompt", "spinner", and "calendar" VCRs added to edit controls when the map containing them is "glued" to another map (usually when a list of records is displayed in a window or popup). It was introduced into 4.623 versions of trmw32.dll and trmwce.dll built on or after 21st May 2008). When this setting is enabled VCRs are hidden when a map is glued to another map. They will reappear if the map is subsequently used for editing.

This setting is intended to make using the same map for both editing and displaying a line of data work slightly better, and may be convenient for QAB developers. However, this is only a partial solution: you will not be able to put a frame around such edit controls (because if you do the appearance will still be odd), and the map used for editing won't have OK and Cancel buttons. Also, typically the prompt buttons will only be usable when inserting a new line of data (because they most likely are used by keyfields), so the user may be confused when editing an existing line. On the whole it is better to use separate maps for displaying a line of data in a popup and for editing the data when the line is selected.


hide_empty_buttons

[TRMW]
hide_empty_buttons=0

normally specified in [TRMW] only.

If this option, introduced in 4.623, is set to 1, then if a button has neither a picture nor any text, then it is not drawn at all. Note that the user could in theory still click on the button, so you will normally want to ensure such buttons are also disabled. This option is intended for use by a specific customer and is unlikely to be useful generally, but may be useful where you have screens containing many buttons, which you change dynamically using show() functions.


icon_visible

[win32]
icon_visible=0 or 1

If 1, an icon (shown in the system tray on Windows 95/NT4 or later from 4.520/4,616), is created for the SRVW32 program even when it is run as a service. The icon will not be visible unless the service can interact with the desktop.

When DP4 is run as a service you can prevent the icon being created by setting icon_visible=0 in the [win32] section of the DP4 configuration file. The default for icon_visible is 1 rather than 0, from 4.616/4.520, except on NT 3.51

From 4.620 (5) (issued 17 Feb 2003) icon_visible=0 also works when srvw32.exe or srvwce.exe is run as an application.


idletime

[TRMW]
idletime=0

Applies when the 4.51x, or 4.616 and later terminal managers are in use. Processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

Setting a non-zero value for this allows you to slow down DP4 programs which are intended to run as background tasks.

An alternative is to start the program in the [startup] section with a priority setting, or to use the NICE program, which we can supply, to start the program later on with a priority setting.

The idletime specifies a minimum delay in milliseconds between calls to the trm_lookchr() function (nextchar() in QAB). Programs which call this function twice, within this interval, are delayed by the appropriate number of milliseconds.

For further information on priority setting see the STARTUP section.


ignore_shutdown

[errlog]
ignore_shutdown=0

If 1, the errlog program is not terminated when the DP4 service is stopped. This may be useful if you are trying to diagnose a problem that is preventing DP4 from loading correctly.


isometric

[TRMW]
isometric=0

If isometric=1 is set in the DP4 configuration file then the terminal manager adjusts x_factor and y_factor so that a rectangle that has an equal width and depth in internal units appears as a square on screen.

It does this by increasing one or other of x_factor and y_factor as required. If x_factor is increased controls will appear slightly larger horizontally, if y_factor is increased they will be larger vertically. Font sizes are unchanged. The default for isometric (its value if it is missing from the DP4 configuration file), is 0, however it is set to 1 in the issued the DP4 configuration file. The value of isometric will only have an effect if your default font is NOT twice as high as it is wide. Note that the System font (at least on US Windows) IS isometric anyway. Setting isometric=1 when you use another font (such as MS Sans Serif) as the default font may change the size of maps on screen. Doing so will ensure the aspect ratio of printed maps is the same as displayed maps, though this will only be necessary if there is significant difference in how Windows realises the font when printing from on screen.

From 4.616 you can specify values for x_factor and y_factor in the DP4 configuration file. The only time you would be likely to need to do this is if you wanted to use a default font that was slightly smaller than your desired x_factor and y_factor. For example if you design an application to run full screen at 640*480 you can ensure it will also run full screen at 800*600 resolution, whether the user chooses "Large" or "Small" fonts by specifying x_factor=10,y_factor=20.

See Coordinate system for more information.


keep_awake

[TCPW],[TCPMGR],[progname]
keep_awake=1

Applies on Windows CE devices from release 4.621

Windows CE devices are typically configured to go into suspend mode automatically, after only a few minutes of user inactivity. This may be inappropriate if the device is running background processes that are communicating with another computer over a network. From release 4.621 DP4 can prevent Windows CE devices suspending while DP4 network activity is taking place. Note that this new functionality is enabled by default, but there are situations where it may be preferable to disable it.

DP4 prevents devices suspending automatically by resetting to zero the timer that measures the time since the last user activity, whenever certain types of network activity take place. DP4 does not prevent user-initiated suspends (for example the user presses an Off button), and only prevents automatic suspends while it detects significant network activity from DP4 programs.

There are currently three places where keep_awake can be specified:

  • [TCPW]

    Unless you specify keep_awake=0 in the [TCPW] section, then, whenever a DP4 program calls the database manager across the network, the inactivity timer is reset. If DP4 is loaded in the named connection configuration calls passed to tcpw.wce but destined for the local machine are ignored. However, in a multiple resilience configuration, calls destined for the local machine do also reset the timer.

  • [TCPMGR]

    Unless you specify keep_awake=0 in the [TCPMGR] section, then the inactivity timer is reset whenever TCPMGR receives a message other than a network "keepalive".

    If your application includes some kind of polling software that connects to each client machine in turn to look for new transactions, then you may find that the default keep_awake=1 setting prevents your CE devices from suspending. In this case you must either disable the keep_awake setting for [TCPMGR], or modify your polling software so that the delay between succesive polls of CE devices is long enough for them to be allowed to suspend when there is no activity. The latter solution is preferable as it will enable [TCPMGR] to stay awake when more significant network activity is taking place.

  • [progname]

    The keep_awake setting also applies to programs run using the DP4 thin client.

    Unless you specify keep_awake=0 in the [progname] section for an application, then calls received by the DP4 thin client on behalf of that application will reset the inactivity timeout. Please note the following important points about this use of the keep_awake setting:

    • This setting must be specified in the DP4 configuration file on the application server and not on the client Windows CE device.
    • An application will not be able to keep a CE device awake when it is awaiting a response from the user. If the application is waiting for a response, but the user does not provide one in time to prevent the inactivity timer from suspending the machine, then Windows CE will suspend the device. This will cause the network connection to break, and the application to terminate. For this reason it is a good idea to build logic into your application that will allow it to remember its state and resume later on.
    • The keep_awake setting for applications is not a true [TRMW] type setting. It can only be specified in a named program section, and can only be specified at the executable file level - for example you cannot specify this setting for individual QAB programs.


limitsize

[TRMW]
limitsize=0

Normally specified in named program sections for those programs which do not require a maximised screen, such as PROGMAKE, DP4ED. Processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

If 1, the program window cannot be increased in width beyond the value which makes the client area width user.scrn_width*character_width. See description of width for information on how character_width is calculated.


load_dfnet

[system]
load_dfnet=0

Do NOT change or remove this value unless you are instructed to do so by Itim Technology Solutions.


load_errlog

[system]
load_errlog=0

If 1, the ERRLOG program is loaded.


load_win1632w

[win32]
load_win1632w=0

If 1, this causes SRVW32 to attempt to load support for 16 bit applications. You are recommended to convert 16 bit applications to native 32 bit applications rather than relying on this facility, which can cause problems, especially on Windows NT.


loader

[win32]
loader=srvw32 -start

[wince]
loader=srvwce -start

[unix]
loader=dbdaemon -start

The command line which DP4 programs run when they want to autoload the 32 bit version of DP4.

With the Win32 version of DP4, or the 4.525 Unix and Linux version, applications can autoload DP4 in a fully configurable way. You should note the following points about auto-load in these versions of DP4:

  1. DP4 does not auto unload on termination unless configured to do so through the use of the -noremain command tail on the DP4 server (e.g. dp4srvr.w32 or tcpw.w32).

  2. The full configuration for the machine is autoloaded, not just the standalone database manager.

Autoload is enabled by default. To disable it set autoload=0 in [system] . The autoload runs the commands above by default. You can configure the command to be run by setting loader=Command.

For example, if you want to run DP4 as an application rather than a service on Windows NT you would set:

loader=srvw32 -load.

Or you might want a different configuration when autoloading than that used when running as a service.

loader=srvw32 -load -startup=autostart

You would provide an [AUTOSTART] section similar to the [STARTUP] section.

Although SRVW32.EXE is primarily a loader program, you should not attempt to replace it with a batch file or anything similar. SRVW32.EXE also performs the following important functions:

  • During startup and termination it will prevent DP4 programs from attempting to access the DP4 system at critical points where it would cause problems.

  • It validates the configuration as it is loaded and reports errors.

  • When DP4 runs as a service on NT it is the service process.

  • If we require you to help us diagnose a problem using the errlog program, SRVW32.EXE is required as it is responsible for piping diagnostics into errlog.

Prior to release 4.525 there was no loader program on Unix/Linux, and running without dbdaemon is still supported. However, most of the above reasons for using the loader apply equally well to the 4.525 Unix/Linux releases of DP4, and you are recommended to use the loader program.


loader

[windows]
loader=srvw.exe

This sets the program which 16 bit DP4 programs run to autoload the database manager. Note that 16 bit DP4 does not have an (issued) equivalent of the loader program we supply with the 32 bit version of DP4 and so you cannot easily autoload complex configurations such as multiple resilience.


loader

[wince]
loader=pathsrvwce

The command line which DP4 programs run when they want to autoload the Windows CE version of DP4. Because Windows CE does not have the equivalent of a path environement variable the value for the loader entry must include the full pathname. The path used in the default value is taken from the dp4home entry

With the Windows CE version of DP4 applications can autoload DP4 in a fully configurable way. You should note the following points:

  1. DP4 does not auto unload on termination unless configured to do so through the use of the -noremain command tail on the DP4 server (e.g.dp4srvr.wce or tcpw.wce).

  2. The full configuration for the machine is autoloaded, not just the standalone database manager.

Autoload is enabled by default. To disable it set autoload=0 in [system].

You can use the -startup and -preload command tails in the loader entry to control how DP4 is loaded by auto-load for example:

loader=\dp4\win\srvwce -load -startup=autostart


max_programs

[system]
max_programs=128

From release 4.621 determines the maximum number of programs using DP4 IPC that can be run simultaneously on the local machine. ADCs and the Network Manager are included in the total, and also the database manager if the -distributed option is in use. Also one handle is reserved for internal use.

There can be little reason to increase this value except on machines running Citrix or Terminal Services clients with a full 2 Gigabytes of RAM. Reducing the number to a smaller but reasonable value such as 32 will save a small amount of memory for each DP4 process.

This number has no bearing on the number of users that can connect to the database manager via the DP4 networking.

From release 4.525 this setting is also used on Unix/Linux platforms, but in a slightly different way: setting this to a low value will not prevent you running more programs, but will reduce the size of the cache DP4 internally uses for remembering information used for IPC between applications and DP4. Generally you should set this value to the largest number of DP4 programs you want to run simultaneously as on Windows. The amount of memory saved by specifying a low value is minuscule - only eight bytes per server per program (i.e. specifying 64 instead of 128 will save 64*8 bytes in each of srv3, tcp3, and any ADC loaded)


monitor

[system]
monitor=0

This setting is introduced for 4.623. It can also be used on 4.621 installations using 4.621a provided srvw32.exe and syslibt.dll are upgraded to the files included with 4.623. This can be done by applying the 4.621b zip file available from the DP4 FTP site.

If set to a non zero-value n, then every n seconds, srvw32.exe checks the status of the DP4 IPC channels and displays diagnostics in ERRLOG. If srvw32.exe finds that a channel has been assigned to a process that terminated without calling the required cleanup functions it clears the channel and prints a diagnostic including the offending processes process ID.

This setting has been added to assist with diagnosing problems caused by processes that do not terminate cleanly.

When this value is set, the load_errlog setting defaults to 1, and not to 0. However, if load_errlog=0 is set explicitly then the monitor function will still clean up after terminated processes.


Names and Locations of Configuration Files

The names of the various configuration files depends on the version and release of DP4 installed. Different releases of DP4 also have varying degrees of flexibility as to the location of these files, and there are significant changes in the DP4 Enterprise release.

The changes in DP4 Enterprise (4.620 or later) have been made for three reasons:

  1. We are obliged to stop using the Datafit name in the DP4 product.
  2. The changes make DP4 installation on different platforms more similar.
  3. The changes make it possible to run multiple installations of DP4 simultaneously on the same machine without them interfering with each other (except with legacy versions of DP4).

DP4 Enterprise Releases

  1. The name and location of the DP4 configuration file is dependent on the operating system.

    • On Windows platforms there must be a DP4 configuration file in the same directory as syslibt.dll (or syslibce.dll on Windows CE), and it must be named dp4.ini. If for the time being you wish to continue to name the "real" DP4 configuration file datafit.ini and to store it in the Windows directory then dp4.ini must contain the following two lines only:

      [branding]
      dp4.ini=datafit.ini

      If you do this make sure any other new entries you want to add to the DP4 configuration file are added to datafit.ini, not to the new dp4.ini.

      Do not add the line dp4.ini=dp4.ini to dp4.ini. This would cause dp4.ini to be located in the Windows directory and hence to potentially clash with other dp4 installations. If you want to store dp4.ini in some other directory than the directory that contains syslibt.dll make sure you specify a full pathname. Do not use relative path names, because the working directory for a program may not always be what you expect.

      If dp4.ini does not have a definition for dp4.ini then dp4.ini in the directory containing the loaded syslibt.dll/syslibce.dll is used for all DP4 configuration file information.

    • On other platforms, in particular on Unix and Linux, there is no reliable method of discovering the location of a currently executing binary component, and it is therefore not possible to determine a location for dp4.ini as for Windows. Instead dp4.ini is located as follows: if DP4HOME is defined in the environment its value is assumed to point to dp4.ini, otherwise if the current working directory contains dp4.ini then this is assumed to be the correct dp4.ini, otherwise dp4.ini is assumed to reside in /usr/dp4. To maintain compatibility with existing Unix installations of DP4 in /usr/datafit define DP4HOME=/usr/datafit/ in the environment before loading DP4 components. This initial dp4.ini can be redirected as on Windows, (except that where there is no path component in the name the current working directory is assumed) but there is no reason to do so as datafit.ini was not used in earlier releases.

  2. The DP4 licence file is called dp4.sys. The file must reside in the directory given by the dp4home entry in the [win32], [wince] or [unix] section of the DP4 configuration file. (The [unix] section is used for all Unix like variants of DP4, including Linux and AIX). If this is not specified (recommended) the directory where dp4.ini is initially located is used (i.e. the directory containing syslibt.dll or syslibce.dll on Windows/Windows CE and the DP4HOME or /usr/dp4 directories on Unix/Linux). This directory is assigned to the environment variable given by the value of the homename entry in the [branding] section. If homename is not specified DP4HOME is assumed. You can stop any environment variable being created by putting homename= in the DP4 configuration file.

    For compatibility purposes and for the release 4.620 only the licence file may continue to be called datafit.sys. dp4.sys will be used in preference to datafit.sys if both files exist. The two files are currently identical in format, and datafit.sys can just be renamed to dp4.sys. It is probable that some additional information will be added to the added to the end of dp4.sys in future. Only a few DP4 core components access this file by name, so switching to the new name should not cause any problems.

  3. The name and location of the userdata.sys file is described below.

Releases up to 4.619

  1. The DP4 configuration file is called datafit.ini The file is only used in the Windows version of DP4, and must be installed in the "Windows" directory. Depending on the version of Windows this directory is given either by the windir environment variable, or the SYSTEMROOT environment variable.

  2. The DP4 licence file is called datafit.sys. On Unix/Linux the file must reside in /usr/datafit, on other operating systems the file resides in the location given by the DATAFIT environment variable. If this variable is not defined the "current" directory is assumed, or for older versions of DP4 the "root" directory.

  3. The name and location of the userdata.sys file is described below.

userdata.sys

The name and location of the userdata.sys file is the same in all DP4 releases: programs that access userdata.sys first look for a temporary version of the file named menudata.sys in the current directory, then userdata.sys in the current directory, then userdata.sys in the same directory as dp4.sys.


no_auto_close

[wince] , [trmw]
no_auto_close=0

This setting is provided as a work-around for one of Windows CE's more delightful features (at least in its Pocket PC incarnation). When you click the X button on a program it is not actually closed, only minimised. As a result memory can be exhausted quite quickly. When it is, instead of asking what you want to do, Windows CE starts closing applications which it thinks you are not using. Unless you set this option Windows CE may well decide to close SRVWCE, which will result in DP4 closing down altogether.

If no_auto_close is 1 in the [wince] section, then SRVWCE will attempt to resist close commands that do not seem to come from the user. If is a good idea to use this setting in the [wince] section.

The setting can also be used as a [trmw] setting. In this case it should only be enabled for specific programs, not used in the [trmw] section itself, and you will need to provide some means, such as an "Exit" option, for the program to be closed down gracefully by the user

In both cases when no_auto_close is set the user will not be able to close the program cleanly via the Settings,System,Memory,Running Programs, Stop route. If they try to stop a program that has the no_auto_close setting turned on then they will get a "not responding" message from Windows. If the user then chooses to terminate the program it will be terminated in a way that prevents it from tidying up. In particular any databases and DP4 connections opened will be left open.


no_close

[win32] [wince]
no_close=0

If 1, the close option on the SRVW32 or SRVWCE system menu (seen when SRVW32 is loaded as an application, or as a service allowed to interact with the desktop) is disabled.


no_scroll_bars

[TRMW]
no_scroll_bars=0

If no_scroll_bars is 1, the terminal manager will not add scroll bars to the main window if a map is wholly or partially outside the visible area of the window. The user will not be able to access the map unless they can pick it up and drag it to a visible part of the window. I

If no_scroll_bars is 0, the terminal manager will add scroll bars to the main window if any part of a map is outside the visible area of the main window. The user can use the scroll bars to change that part of the window that is visible to bring the map into view.

This option is provided for DP4 users who want to prevent scroll bars appearing because just a small and possibly unimportant part of a amp is outside the window. It also prevents users from doing things like moving one map out to the right, scrolling the map back into view, and then moving the map again.


no_texture

[DFPRINT]
no_texture=1

If no_texture is 1, DFPRINT will not use skinning when rendering controls.

If no_texture is 0, DFPRINT may use skinning, depending on whether or not it would be used by the application that created the report, and on the various skin-related settings in the [dfprint] section.

This value can also be specified in application ([progname]) sections to enable or disabling skinning of reports on a per-application basis.


Non client area

Prior to 4.616 the entire window area of DP4 maps and controls, including frames and title bars were 'client area'. From 4.616 these areas, at least for static and edit controls, and the map frame/title itself, are optionally created as a genuine non client area. (However the area is painted entirely by the terminal manager - the windows do not have any frame or title as far as Windows itself is concerned). The primary reason this has been done is to simplify the code which is responsible for painting controls, and to make it simpler to add future 'non client' functionality such as the 'Close (X) button' or Window (system) menus. There is little or no impact on performance whether or not there is a non client area, and indeed virtually no difference in the code called by the terminal manager (there will be marginally more drawing if you disable the non client area, as it will get painted much more often, but this is partially offset by a reduction in the number of messages)

It will make it easier for an application to create its own controls in a window as the coordinates will be easier to calculate. (We've no idea what would happen if you actually tried this).

If necessary you can disable the non client area of DP4 maps by specifying usenonclient=0 in the DP4 configuration file in the [trmw] or [progname] sections.

You are recommended not to do this unless it really is necessary.


OkEndSession

[TRMW]
OkEndSession=0

Processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

By default DP4 Windows programs will not allow Windows to be shut down when they are running. Set this value to 1 to allow Windows to be closed down when DP4 programs are running. If you do this in the [TRMW] section you should set OkEndSession=0 entries for critical programs such as REORGDB.


OldDP4Numbers

[TRMW]
OldDP4Numbers=0

Normally specified in [TRMW] only; processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

If 1, numbers which need more characters than have been allowed for by a control are displayed as '?????'. The default procedure in this situation is to convert the number normally and allow Windows to 'clip' the displayed value to fit in the window.


pagesetup

[TRMW]
pagesetup=0

Introduced in 4.616. When a program selects to print to DFPRINT then a page set up dialog is displayed. In the 16 bit version this will only allow the user to select orientation and printer to be used. In the 32 bit version it also allows margins to be set. You can disable this dialog by setting pagesetup=0 in the [progname] or [trmw] sections of the DP4 configuration file. The print orientation will default to Landscape if only print to screen is selected, and Portrait otherwise.

See also Printing.


pause

[TRMW]
pause=1

Most batch programs such as MAPPOST, LIBMAKE display a message box and wait for it to be acknowledged on termination. To run these programs from a batch file you can turn off this message box by specifying pause=0 in the relevant program's section, or, from version 4.620,with -pause=0 on the command line for the program.


pointer_help

[TRMW]
pointer_help=1

Applies to 4.6xx version of DP4 only; processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

If 1 and help message display is turned on, the terminal manager will display the help message for the control containing the mouse pointer when the mouse is moved (within the current map).

If 0, any help message displayed is always for the control with input focus.


pointsize

[TRMW]
pointsize=0

You can select a custom default font by specifying a font name and size in the DP4 configuration file (in the [trmw] or [progname] sections. The fontname is specified with a string such as font="MS Sans Serif". In previous releases the size of the font had to be specified by specifying the font height (and optionally width) in pixels (fh=16 for example). From release 4.616 you can specify a point size instead (pointsize=10). This is slightly more portable in theory, in that the font size should be constant whatever the resolution of the screen. In practice point sizes on screen are unreliable as changing the resolution does not normally change the reported number of pixels per inch on which a point size calculation has to be based. However it will give you a way of specifying a font in a way consistent with other applications. If pointsize is specified fh and fw settings are ignored.

See Default font and Printing for more information.


pre500

[windows]
pre500=1

This entry, which applied to 16 bit Windows programs only is no longer used (from release 4.520/4.616 on) because pre 4.500 programs are no longer supported. With previous DP4 releases it forces the database manager to retain support for programs created prior to release 4.515. You had to set this value to 0 to enable autoloading of the Database manager for 16 bit programs.


[PRELOAD]

This section contains a list of programs that are executed when the DP4 service is started. It is normally used to perform such tasks as automatic DBRECOV, DBCHECK etc. It applies only to the Win32 version of DP4.

The syntax is the same as that of the [startup] section.


Printing with DFPRINT

Version 4.616 brought major enhancements to the printing functionality in DP4. There is full support for printing text in multiple fonts and colours and for printing bitmaps, icons etc. The appearance of printed maps will closely match the appearance on screen. To make use of the enhanced print capabilities all that is needed is to change the output device (POD) to one that uses the new DFPRINT print device. No other program changes are necessary, though it would be advisable to read through the remainder of this topic to gain a full understanding of the considerable implications resulting from this change.

Operation of DP4 when printing to DFPRINT devices

When a program selects to print to DFPRINT then a page set up dialog is displayed. In the 16 bit version this will only allow the user to select orientation and printer to be used. In the 32 bit version it also allows margins to be set. You can disable this dialog by setting pagesetup=0 in the [progname] or [trmw] sections of the DP4 configuration file. The print orientation will default to Landscape if only print to screen is selected, and Portrait otherwise. The margins selected in the 32 bit version are taken from margin_left, margin_top, margin_right, margin_bottom entries in the DP4 configuration file. The users selections are written back to the same place. Margins default to half the default value specified by Windows (usually give a value of 12.5 millimetres or half an inch), though this default will be increased if the printer's minimum margins are more than this. The margin_xxxx entries are stored in the units used by Windows, which may be thousandths of an inch or hundredths of a millimetre.

If print to file is selected then the append/delete/cancel dialog may be displayed as normal. However the append option is disabled - it is not currently possible to append to print files intended for DFPRINT. (This may prevent DFPRINT from being used in certain situations). This restriction will be removed in a future release if possible. Print files created using the DFPRINT device can only be accessed with the DFPRINT program (using a command line of the form DFPRINT filename). The files are in a private format which is different between the 16 bit and 32 bit versions of DP4 and which is subject to change in future releases. The file is analogous to a Windows meta file, though the format is much more compact. To create files readable by other applications you should set up a print device that uses the IBM file output or DP4 output devices, or from 4.621, you can use the Save option in DFPRINT to convert the file to various formats usable by other programs. You can also configure PODs that will automatically cause the file to be converted to one of these formats. For more information on these possibilities refer to Configuring DFPRINT PODs in the OSDI Developer's Guide.

As with the old WINPRIN program, which is retained for compatibility only, no printing is done until the application releases the print device. In order to ensure that programs printing to DFPRINT do not appear to hang a comfort message is displayed while the print output is being created. However this is not created until several pages have been created (as creating the file is normally very rapid).

When the program closes the DFPRINT device the output it has created is sent to the new DFPRINT program (unless only print to file was selected, and conversion to another format is not required).

Operation of DFPRINT

The behaviour of DFPRINT will vary according to whether print to screen was selected or not. If it was selected then DFPRINT will normally run interactively and display a print preview of the first page. The user can toggle between viewing the whole page, the page minus the margins or actual size output. It is possible to navigate around the document simply. If DFPRINT is running interactively no printing will take place unless the user presses the Print button, regardless of whether print to printer was selected or not initially. The standard windows print dialog is displayed when the user presses the print button.

If print to screen was not originally selected then DFPRINT runs in batch mode, and prints a single copy of the document on the printer selected in the pagesetup dialog, or the Windows default printer if this dialog was disabled. DFPRINT also runs in batch mode if the flags SCREEN+NOCTL+NO_SELECT are passed to dvc_choose().

In the 32 bit version of DFPRINT there is also a Copy button. This copies an image of the currently displayed page as a scaleable picture (metafile) to the clipboard. This allows individual pages of the document to be imported as graphics into other applications such as MS Word or Paint. It is not currently possible to import the document as text, though this is a likely future enhancement.

From 4.619, again only in the 32 bit version of DFPRINT, there is a Save button that can be used to save the output to a file or files: in versions 4.619 and 4.620 only HTML format is supported, from 4.621 you can also use RTF files which contain each page of the report embedded as a metafile. The HTML format files use CSS2 positioning and may not display correctly in older Web Browsers. Also some HTML editors will have problems with the generated pages. In the RTF files the output is essentially a picture and cannot be edited by the user.

DFPRINT can be used to view a file created on another computer, subject to the following limitations:

  • Bitmaps and icons contained in the executable used to create the file, or in files stored on that computer, will only be displayed correctly if they are present in the same location on the computer used to view the file.

  • If the printer used to create the file does not exist with the same name on the second computer, then DFPRINT will fail if a release prior to 4.621 is used. From 4.621 DFPRINT will ask the user to select another printer before continuing.

To create a file that can be viewed on any Windows computer see Creating Output for Other Applications in the OSDI Developer's Guide.

Appearance of Printed Output

The DFPRINT program is intended to be completely WYSIWYG - maps will be printed in the same colours and at the same size using the same fonts as they have on screen. However there are a few points to bear in mind:

Because Windows does not change the reported resolution of the screen in pixels per inch when the resolution is changed the size of a printed map and the display size will not always match exactly. However a map that is one "logical inch" on screen will be printed at one "logical inch" on the printer. Unfortunately MAPEDIT does not currently have a ruler to indicate the size (and in any case does not always display maps in their correct size) but the ratio of sizes will match that used by programs such as Word or WordPad. From 4.620 you can control the sizes used for printing with the settings listed here.

If maps have colour information then they will be printed grey scale on non colour printers. Depending on the colour scheme you use this may sometimes cause printing to be hard to read.

DFPRINT uses exactly the same information to create the font and convert map co-ordinates from internal units to real word units as the terminal manager (except that from release 4.621 you can use the [DFPRINT] section of dp4.ini to override settings from the [TRMW] section). Therefore at full scale in print preview the map will always look more or less identical to a map displayed on screen, unless the default font is realised differently on the printer from on screen, in which case the preview will match the printed version. Under most circumstances a map will look the same printed or displayed, but certain perverse choices of default font can cause this not to be the case.

For example, suppose you are running US Windows 95, and select userdata type F and specify the default font like this:

font="system"
pointsize=15.

Now, on screen the system font is not available in this point size - it is only available 10 point - 8 pixels wide by 16 high using small fonts, or 10*20 using large fonts. Therefore maps will be displayed by converting a 16*32 internal cell to an 8*16 display cell. However on the printer things are quite different. Windows will be able to scale the system font. Therefore the display cell of the print preview will probably be 12*24 units - and the maps will appear 50% bigger than expected. This kind of problem can only arise when a default font is specified that Windows cannot create exactly on the display. If this is encountered then change the definition of the default font to match what it actually is - in this example by changing the pointsize entry to pointsize=10.

In a few cases the font used to display the map on screen will not be the same as the font used to print the map. This will not be a problem where true type fonts are used or a font which exists in many different resolutions. However the system font and the system fixed font need to be substituted in order to print acceptably, as they only exist for the screen and when printed out would look very poor. The choice of font substitute is left up to Windows, but enough information is provided for it to choose a generally similar font. (i.e. proportional and non proportional sans serif fonts respectively)

When the print preview is displayed all fonts and outputs are scaled to fit the display. For non true type fonts, especially when the preview is resized to a very small size the display may not be accurate. At normal size it will usually be quite accurate.


prtscr_editframe

[TRMW]
prtscr_editframe=0

Applies to 4.6xx version of DP4 only; processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

Determines whether or not the SCREEN.PRN file attempts to insert characters to represent the frames round edit controls. Setting the value to 0 (no frames) may make the SCREEN.PRN file more readable.

This value is not used when you have screendumpstyle=2.


refresh_interval

[TRMW]
refresh_interval=0

Applies when the 4.621 and later terminal managers are in use. Processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

Setting a non-zero value for this allows you to reduce the frequency at which programs such as DBCHECK and REORGDB update the screen.

The refresh_interval specifies a minimum delay in milliseconds between calls to the trm_refresh() function (nextchar() in QAB). Calls to trm_refresh() are ignored if the function was last called within the specified time limit. Using this option may speed up these programs by removing unnecessary screen output. A value of 50 or 100 still allows the screen to be updated frequently and can save quite a lot of time.


role_first

[sql.config DP4_database_name]
role_first=0

role_first=1 effectively allows reversal of the field name and role order.

This may be useful if you have several SQL databases created in different ways. For example if you create an Access database from a DP4 database and the DP4 database contained a field staff_name with a role of manager, the Access database name will contain a field called manager$staff_name. So for this database you would specify role_sep='$' and role_first=1, which will allow you to use the existing DP4 database to access the Access database. Otherwise you would need to use AUXWINST to create a new DP4 database with amended names.


rows

[TRMW]
rows=Calculated value

Applies when the 4.5xx, or 4.616 and later terminal managers are in use.

This sets the default screen depth for the program. The default value is the depth of a maximised window divided by the font height, or from 4.616 the minimum of this value and 28. In 4.5xx if the program window is reduced below this depth a scroll bar is displayed. It sets the maxium number of "lines" on which 4.5xx maps will display without scrolling.

It is normal to set this to 27 or 28. It should not be increased beyond 28 for 4.5xx programs , or, in 4.6xx, for these standard DP4 utilities: DP4ED,TABLEPLN, PROGMAKE.

See the depth and default window size pages for more information.


run_once

[TRMW]
run_once=0

Normally specified in individual program sections only.

From 4.620 it is possible to arrange that only one instance of particular DP4 executables should be allowed to run at a time. This facility is most likely to be useful on Pocket PC 2002, where clicking on the "close" button of an application does not actually close it but only minimises it. Restoring an application is not easy for the user on Pocket PC, and many applications automatically restore a previous instance and exit when the user tries to run a second instance. DP4 tests for a previous instance of the application by making the main window class for an application the full pathname of the application. When an application starts the DP4 terminal manager looks for an existing window of the same class. This setting is available on all Win32 platforms, not just Windows CE.

The run_once setting has three possible values:

  • run_once=0 This allows multiple instances to run without warning the user.

  • run_once=1 A second instance tries to set the first instance into the foreground and then exits. Making another application the foreground application is not always possible on all versions of Windows. In case of difficulty use the "fixsf" application available from the Itim DP4 web site.

  • run_once=2 A second instance brings up a message box dialog asking the user if he wants to start a new instance. If the user answers No, the first instance is set to the foreground in the same way as for run_once=1, otherwise the second instance runs normally. The title and text of the dialog can be customised using run_once_question and run_once_title


run_once_question

[TRMW]
run_once_question="Are you sure you want to run another instance of this program?"

This setting specifies the text that used for the question that appears in the dialog used when run_once=2 is set.


run_once_title

[TRMW]
run_once_title="%s: Program Already Running"

This setting specifies the text that appears in the title bar of the dialog used when run_once=2 is set. If the string contains %s, %s is replaced by the full name of the executable including the path.


save_position

[TRMW]
save_position=1

Processed when C or QAB programs terminate, or when a C application calls the trm_set_progname() function.

When DP4 programs terminate or the name of the program is changed the current position of the program on screen is usually saved, so that when the program is run again it uses the last position. Occasionally, especially where the Window style of the program as created by the DP4 Terminal manager has been modified from C or C++, this does not work properly, and it is desirable to set a fixed position from the program. You can prevent DP4 from saving the current position of the Windows for a program by adding the line save_position=0 to a [progname] section of the DP4 configuration file.

This option should not normally be specified in the [trmw] section itself unles you want to force the initial position for all DP4 applications.


screendumpstyle

[TRMW]
screendumpstyle=1

Applies to 4.6xx version of DP4 only; processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

This setting affects the format of the SCREEN.PRN file produced when the 'Dmp' key is pressed.

  • If 0 the output is formatted as though the application window is sized for user.scrn_depth*user.scrn_width rows and columns of text in the default font.

  • If 1 the output is formatted using the current size of the application window.

  • If 2 (supported from 4.618) an HTML 4 Web page is created which is as similar as possible to the actual application Window. The HTML created uses CSS2 positioning and currently is only displayed correctly by Microsoft Internet Explorer 5 or later, and, from version 4.621, by Gecko browsers (Netscape 7 and Mozilla for example). Prior to release 4.621 a separate .css file is created as well as the HTML file.


screenprint

[TRMW]
screenprint=""

Processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

If a value is specified for this entry, it is assumed to be a command line to be invoked after the trm_screen_dump() function is called. Some systems make use of this feature to provide facilities such as a screen print to a print device supported via an OSDI rather than the Windows Screen Print facility.


Secure operation of DP4

Potential Security Problems in DP4

There are a number of security holes in DP4:

  • Malicious users with access to DP4 programs can copy their own programs onto a server running TCPMGR using NETCOPY and then start them running one way or another. On Windows NT they can also use NETCOPY to access files that are supposed to be denied to them. These problems can be addressed by making simple changes to how TCPMGR is started, or by configuring DP4 to run in an account with less privilege that the local system account that is normally used by the DP4 service.

  • A malicious program could take advantage of a security hole present in programs such as DP4SRVR.W32 and TCPMGR.W32 to bypass normal NT security features and install anything it wanted as part of the operating system. Such an infection might go unnoticed for a long time. Such a program would not need to know anything about DP4, only something about how to look for and exploit security holes, so they almost certainly exist.

    The only mitigating factors for this problem are that the malicious software would already have had to gain some access to the machine, and that quite likely it would find something other than DP4 it could use to make the infection more severe anyway. This problem is fixed by DP4 Enterprise and a fix will be issued for existing systems as well.

Basic Security

The options described in this section are easy to implement and can be used in any envionment, including Windows 9x and Unix. The command tails described are all new in version 4.620.

From 4.620 TCPMGR no longer permits commands to start programs on the server by default (this facility is required by DP4 Terminal Server). To enable the facility specify -allow_execute on the command line to for tcpmgr.

It is possible to disable support for the sf_open() command (and hence all DP4 remote file operations). DP4 remote file operations bypass NT security unless you go to the considerable trouble of implementing the NT security features described below. There are two mutually exclusive command tail options:

  • -nocopy completely disables support for remote file operations via DP4
  • -rocopy only permits file operations that open files with read only access on the server. All write operations are disabled.

Note that either of these options will prevent some DP4 programs from working over a network, notably DBBACKUP, DBCHECK, DBRECOV, DBRESTOR, DYNABACK, REORGDB, and the option to create a new database in MAKEDB (though they will work using DP4 Terminal server if enabled)

For DP4 Terminal Server you are recommended to use the -rocopy command tail to completely prevent a file being copied onto the server and then executed. -nocopy is not recommended because it will stop graphics being displayed.

NT Security Features

The contents of this section are preliminary documentation only.

DP4 Enterprise can be run in an account with less privilege than the local system account, which is the account normally used for services. With earlier versions of DP4 this did not work well because it was hard to diagnose problems.

The basic idea is that DP4SRVR.W32 and TCPMGR.W32 run some of the time using a special logon, and that permissions on the machine should be set up to limit what this logon can get to, but that the DP4 service as a whole still uses the local system account so that it can interact with the desktop, and produce diagnostics properly.

For this to work you have to be running on Windows NT 4,2000, or XP (Server or Workstation). It is advisable to use the latest service packs available, especially on Windows 2000, as it is possible to render a Windows 2000 machine non bootable through making the boot process get an access denied error! Windows 2000 SP2 appears to set default permissions to much more sensible values than used in earlier versions. To run DP4 securely it must be run as a service in the local system account, allowed to interact with the desktop (the default), otherwise you will most likely get access denied errors, especially if you use DP4 Terminal Server.

You must test on an identical machine and configuration to the live configuration if you plan to run DP4 securely. The slightest difference in the way the machines are set up or the version of Windows might mean you get an access denied error on one and not another.

  1. All hard drives should use NTFS as other file systems do not allow permissions to be set. You can use the CONVERT program to convert existing FAT/FAT32 partitions non-destructively. e.g. convert c: /fs:ntfs.

    Initially NTFS partitions may well be set with permissions that allow Full Control to Everyone (at least up to W2K SP2).

  2. Changing permissions so that things work securely is much more complex than might first appear, especially in the \windows directory. The problem is that there is a lot of badly behaved software that wants to create temporary or scratch files in critical directories such as the system32 directory, but which does not give a helpful diagnostic when given an access denied error by the operating system. For example it is difficult/impossible to get active desktop working properly for an ordinary user on a Windows NT machine after setting security, for just this reason. The built in tools for managing access control are barely adequate, though things are somewhat better on Windows 2000 than before.

    If you are serious about using NT security to control access to files you should probably invest in suitable third-party software or literature that tells you how to go about it. Roughly speaking , you need to ensure that administrators, SYSTEM, and CREATOR OWNER have full control everywhere, but that ordinary users are much more restricted, for example EVERYONE should be very restricted indeed. You can use DENY Access control entries restrict access to certain files or directories for specific users.

  3. Using DFSETUP change the layout of DP4 so that all the files are stored in one directory hierarchy such as C:\DP4 and subdirectories DB,WIN,TMP,PRNS. Change the directory for debug.out in the DP4 configuration file so that it goes to the PRNS directory:

    [errlog]
    debug_filename = ..\prns\debug.out

  4. Create a local user with a name such as DP4USER. Make it a member of the guests local group, allow it logon as a service access, and for good measure deny allow interactive logon (though this might just cause a problem, and should not really be necessary, as guest accounts can't do much damage).

  5. Give this user Full Control access to C:\DP4 and subdirectories (for folders only), and read write but not execute for files. Then change the access in C:\DP4\WIN to be Read and Execute only for files.

  6. You could use DENY permissions on some other directories to restrict DP4 further without upsetting anything else. It should be noted that having long ACLs on files can impact performance.

  7. Add the logon details for DP4USER to the [win32] section of the DP4 configuration file like this:

    user=DP4USER
    password=password
    domain=.

Once you do all this dp4srvr.w32 will run impersonating DP4USER which will mean it can only access files this user can, and any processes created by TCPMGR will be run as this user as well. TCPMGR runs as itself most of the time, as otherwise it can get WINSOCK errors, presumably because some internal file cannot be accessed.

You can use secure=0 in the [dp4srvr] or [tcpmgr] sections of the DP4 configuration file or -secure=0 on the command line to dp4srvr.w32 or tcpmgr.w32, to disable impersonation for one or other of the programs.

Security Considerations for Runnning DP4 Client Programs

As well as the security of the DP4 service itself you may be concerned about the type of account your application that uses DP4 will run in. Prior to 4.620 there were some problems running DP4 programs in a non-administrative account. However, since 4.620 there should be no problem with doing this. DP4 has been tested running like this on Windows 2000,XP and Windows Server 2003.

Nevertheless there are some points to bear in mind:

  • The account(s) you plan to use for running applications that use DP4 need read-write access to the directories where DP4 temporary files, and report/export files will be created.
  • If you plan to run DP4 as a service, then the accounts need the right to stop and start services, or at least the right to stop and start the DP4 service, otherwise they will not be able to run if DP4 is not already running. It is usually sufficient for such accounts to be members of the "Power Users" group.
  • On Windows Server 2003, and possibly on Windows XP (if you want to be able to run programs through a "remote desktop connection"), then accounts that will be used for running DP4 programs need the "Create a Global Object" privilege, othewise they will not be able to communicate with the DP4 service. This privilege is needed because it is necessary for some memory to be shared between the DP4 service and DP4 applications. Only "Global" shared memory is suitable because the DP4 service may be (or is on Windows 2003) running in a different session from the DP4 applications. This can be set through the Local Security Policy (secpol.msc) in "User Rights Assigment".


setcommstate

[TRMW]
setcommstate= 0 or 1

normally specified in [TRMW] only.

The default is 1 on Windows 9x/Me, and 0 on other versions. This value enables a workaround for a bug in Windows 95. This bug also affects the 16 bit version of DP4, but unfortunately the same work around is not available.

The bug is as follows:

When DP4 prints to a printer that uses COM1 or COM2 it does not configure the port baud rate etc but assumes that this has been set up by running the mode command or some equivalent. This works perfectly well on Windows 3.x and Windows NT, but not on Windows 95. (It is documented as a valid technique by Microsoft so this is a bug in the Windows 95 OS).

The work around we have added is that if the value of SetCommState in the [trmw] section of the DP4 configuration file is 1 then trmw32.dll will initialise the port according to the values contained in the appropriate entry in the [ports] section of win.ini. The value of these entries can be configured using Control Panel.(Right click on My computer, select Properties, the Device Manager Tab,Open the Ports entry,double click on the port you want to configure and click on the Port Settings tab). SetCommState defaults to 1 on Windows 9x and 0 on other versions of Windows so there should not normally be any need to set it explicity. It would be preferable to use an appropriate entry from the registry to configure the serial port. However there does not appear to be a documented way of accessing the port information (it is stored in a different way in Windows NT from Windows 9x).


shadow_title

[TRMW]
shadow_title=0

If 1, the text in an active title is shown with a drop shadow effect.

If 0,the text in an active title is drawn normally.


SharePrinter

[trmw]
SharePrinter=1

This can be used to force PRN: and similar ports to be opened in shared mode. This is a workaround for a system converted from MS-DOS.


show_im

[TRMW]
show_im=1

Introduced in v 4.620; relevant to Windows CE (Pocket PC versions) only; processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

If 1 then when a DP4 program is activated the terminal manager ensures the Input Panel Button is visible and the taskbar is hidden. (This is done using FindWindow() on the appropriate classes to avoid using CE version specific APIs.


shutdowndelay

[system]
shutdowndelay=5

If the DP4 database manager is shut down while applications are running, this specifies the delay in seconds before the shutdown actually takes place. You can arrange for terminal manager applications to respond to the shutdown messages broadcast by DP4 by setting dfshutdown in their named program section or in the [TRMW] section.


single_query

[sql.config DP4_database_name]
single_query=0

Some databases, notably Microsoft SQL Server, have very severe restrictions on how different cursors can be used in the same connection.

Set single_query=1 if the SQL database will not allow you to execute a new SELECT cursor while an existing query is still open.

Set single_query=2 if the SQL database will not allow you to execute any SQL statement while a fetch is open. single_query=2 is required for accessing SQL Server databases via AUXWODBC.


Skinning DP4

From 4.621 the appearance of DP4 programs can be customised to a much greater extent than in previous releases:

  • Buttons, headers and VCR controls can be drawn with a custom background. This is available on all Windows platforms except Windows CE versions. Buttons and VCR controls do not have to be rectangular.

  • The background parts of other controls can be coloured using a bitmap to give a texture or watermark effect. (However, this does not work well on Windows 95 because the texture is limited to an 8*8 bitmap.On other platforms, including Windows CE, there is no limit to the size of the texture bitmap). DP4 will arrange for the texture to join correctly across controls and maps, as can be seen in the screen shot below:

    Screenshot of DP4 with Texture

    • You should probably not use a texture if you plan to run applications using Windows NT Terminal Server or Windows 2000 or later Terminal Services. Using a texture may reduce the maximum number of users that can succesfully run applications simultaneously, especially if the texture bitmap file is large

    • Using a texture will slow down screen display and can cause performance problems with some graphics cards (See Problem Graphics Cards below). This is unlikely to be significant - on most modern PCs the display will still be more or less instant - but it will have some adverse effect on programs such as DBCHECK and REORGDB that update a count on screen very frequently. You can use the new refresh_interval setting to cure this problem.

Textures will not work well if the Screen is set to display 256 colours or less. Using custom buttons may also cause problems.

Both textures and custom buttons can be handled by DFPRINT. However they are disabled by default in this program, because they may cause problems with some printer drivers, and also because RTF files created by DFPRINT will be much larger if they are not.

You can enabled skinned rendering within DFPRINT by setting the no_texture option to 0

A collection of sample buttons and textures is available. However you can easily create your own versions as described below.

Texture and Button Bitmaps

The bitmaps DP4 uses to create buttons and textures are all standard Windows bitmaps. The various bitmaps should all be stored in the same directory as the DP4 licence file. The names are all configurable via DP4 configuration file entries in the [trmw] section (so that different programs can use different bitmaps if desired). However most of the time you can just use the default name. For example DP4 looks for the texture.bmp file in order to create the texture.

The only peculiarity of the bitmaps is that in order for them to be drawn in a colour that matches the current colour scheme, they should be stored as 256 color bitmaps that are predominantly pure red. At run time DP4 modifies the bitmap by removing the red component and replacing it with the appropriate colour. For this purpose "red" is considered to be the excess (if any) of red over green. For example if a pixel in the bitmap is coloured red=224,green=112,blue=56 (a dullish red-orange), and the colour to be rendered is pale blue (r=186,g=208,b=255) then the pixel will be rendered as r= 112 + 112*186/255 = 193, g = 112+112*208/255 = 203, b = 56+112*255/255=168.

Bitmaps to be used for textures should normally appear to be almost completely red. For example the texture used to create the screen shot above looks like this:

Sample Texture Bitmap

For buttons, parts that you want to be transparent should be coloured light grey (r=192,g=192,b=192). If you use transparency you should make sure that the transparent parts are the same in each of the bitmaps needed for the button. You should make the button bitmap somewhat larger than for a typical button, and it should be proportioned like a typical button.

Creating Buttons and Textures with Paint Shop Pro 7.

All the buttons and textures we supply were created with Paint Shop Pro 7.

To create a button proceed as follows:

  1. Create a new image for the button. A size of 300*100 works well.
  2. Fill the background with light grey (192,192,192) and then hide the background.
  3. Create a new raster layer and use the preset tool shape to create the button outline. You can use one of the available buttons if you like, but you will need to change the colour afterwards. One way to to this is to create a solid red layer and use the Hue or Colour blend modes. Alternatively you can fill the shape with a gradient, for example a red white red gradient.
  4. Use the inner bevel effect to create a button border. If your button is not rectangular you must make sure the background is hidden at this point, otherwise the border will not be applied correctly.
  5. You need to create four different states of the button. We found the best way to do this was always to use the same bevel type and depth and to vary the lighting and shininess to indicate whether a button is the default button or active. For the button down state reduce the bevel width. You can try a V shape bevel to indicate the button down state, but on the whole we did not think this gives good results.
  6. Reduce the colours to 256 using an Optimised Octree palette with error diffusion.
  7. Use the Edit Palette option and change the 192,192,192 entry (which you can find by clicking on the area that is supposed to be transparent) to Bright Magenta (255,0,255) or a similar colour. This will reveal any parts of the image which should be 192,192,192, but are not, because of anti-aliasing. Change all the "bad" pixels to the same Bright Magenta colour, by continuing to use the edit palette option.
  8. Increase the colour depth to true colour, and then reduce the colours to 256 using an Octree palette again. Change the Magenta palette entry to 192,192,192
  9. Save the images as a bitmap.

There are quite a number of tools available for creating buttons. However none of the ones we tried seem to make it easy to create a button down effect.

To create a texture you can use any image as a starting point. Bear in mind that large textures will substantially increase the memory used by DP4, and may impact performance. You should always test performance and appearance on the target machine - the same texture can render quite differently on different machines, even when the same Windows colour scheme is in use. Unfortunately the human eye is much more sensitive to small differences in colour near to grey or white light than it is to changes near a pure colour, therefore you may find it quite hard to judge the point at which your texture will look good when displayed by DP4. We suggest the following technique:

  1. Find a suitable image that you would like to use for a texture and open it in Paint Shop Pro
  2. Create a solid red raster layer and blend it using colour at around 90% opacity. Your original image will show through coloured red.
  3. Create two more solid red raster layers and blend them use lighten and darken modes respectively. Adjust the opacities so you can just see your original image. Alternatively put yet another solid red raster layer on top and blend it using difference mode. Adjust the opacities of the underlying lighten and darken layers so your image shows up just enough to be visible. Then delete the difference layer.
  4. Reduce the colours to 256 using Octree mode with error diffusion. Save the image as a bmp file, and test it out with DP4 by copying it over your current texture.bmp file. Don't exit Paint Shop Pro when you do this - you will most likely find that your texture is still too obtrusive and that you need to adjust the layer opacities some more to get a suitable result. You should test the texture with several different colour schemes to make sure it works well in all of them.
  5. If the edges of your texture do not join up neatly you may get an unpleasant tiled effect. You can use the Seamless Pattern from Selection option to create a texture that will tile neatly. However this can be rather frustrating and does not give very good results always. You can try using the Kaleidoscope or Pattern effects instead, or a gradient that varies the transparency to fade out the image.

Configuration file entries for bitmaps

All the entries are in the [trmw] section. None of the entries are re-read when trm_set_progname() is called, so they can only be specified on an application basis in [progname] sections that match real executable name. These entries can also be set in the [dfprint] section if desired: for example if you set no_texture=0, so that custom buttons can be displayed when printing maps (possibly useful when creating documentation for a system), you might specify texture=., so that textures were still disabled.

buttonup=buttonup
buttondown=buttondown
buttonactive=buttonactive
buttondefault=buttondefault
headerup=headerup
headerdown=headerdown
headeractive=headeractive
vcrup=vcrup
vcrdown=vcrdown
texture=texture

Problem Graphics Cards

Some graphics cards cause DP4 to update the screen very slowly when skinning is enabled. Sometimes skinned buttons are OK, but background texture renders very slowly. This is more likely to occur when running on machines intended as servers - manufacturers often assume that there is no need to supply such machines with high performance graphics cards. It may also happen that a card works well with some settings but not others - for example you may get quite difference performance when a card is set to display in true color resolutions rather than "hi-color".

In release 4.622 DP4 allows you to use true color bitmaps as well as 256 color bitmaps for background texture, as somtimes graphics cards can render true color bitmaps much more quickly than 256 color ones. However, bitmaps for buttons and other controls must still be 256 color as this is required for transparency suppport.

4.622 also has partial support for the XP "Luna" themes, and some third party themes. For details see "Support for XP Style interface"

As has already been mentioned, always test your application on the target machine. If this is not possible make sure users can easily disable texture.

Cards that are known to cause problems:

RAGE XL Pro PCI family


smooth

[TRMW]
smooth=0

Only applies when 4.5xx terminal manager is in use.

If 1, the terminal manager updates the screen via an off-screen bitmap to reduce flicker. Performance is somewhat impaired. There should be no reason to use this option with modern hardware.


STARTUP

This section contains the list of programs started when the DP4 service is started up.

The Win32 loader will accept two different syntaxes in this section. The two different syntaxes cannot be mixed. The old syntax is documented only because you may come across it in an existing DP4 configuration file file. It should no longer be used - the Windows CE loader and the loader for Unix (dbdaemon) only accept the new syntax.

New syntax:

1=[priority][Type]program_name_1 command tails
2=[priority][Type]program_name_2 command tails
3=[priority][Type]program_name_3 command tails
...

Old syntax:

program_name_1=command tails
program_name_2=command tails
program_name_3=command tails
...

In the old syntax, programs were loaded in the order in which their names were found in the file. In the new syntax, the loader looks in turn for the keys '1', '2', '3',... and uses the associated values as the command line to load.

The old syntax cannot handle the same program name twice; the Windows API returns the first set of command tails for the second program.

If no startup section is specified the [startup] section defaults to

1=dp4srvr.w32

or the equivalent command to load the stand-alone database manager on Unix, Windows CE etc.

The server programs such as TCPMGR and AUXDISTR are all service processes, (daemons) that run all the time. When the loader loads these, it must not wait for them to terminate. Instead it waits for them to signal or broadcast a message to indicate that they have completed initialisation. However, if the loader loads an application program such as DBRECOV or DBCHECK, it must wait until it terminates and check the return code before going on to the next program. The loader will stop the loading process if an application program returns a non-zero return code. It may also be required to load a program which does not terminate and does not signal that it has completed initialisation, or one that should not prevent DP4 from loading even if it itself fails to load.

To allow the loader to distinguish these various scenarios, the names of application programs (as distinct from TSRs) must be prefixed by various symbols.

Symbol | Meaning
* | Run to termination and check return code is 0
& | Spawn and do not wait for a return code
! | Wait for completion but ignore return code

If no type is given the program is a DP4 server program and must (on Windows) send a special message to the loader to say it is ready. (On Unix/Linux DP4 server programs are expected to terminate with a 0 exit code after forking to turn themselves into daemons). By default servers have 15 seconds to initialise (set with the startup_delay entry)

The [priority] settings are as follows:

< | Very low
- | Low
. | Normal
+ | High
> | Very high

On Unix and Linux these priorities are all relative to the priority with which dbdaemon is started. "Very High" means the process has the same priority as dbdaemon, each of the other priority levels reduces the priority of the program by increasing its nice value by one point.

Example [PRELOAD] and [STARTUP] sections

[preload]
1=dp4srvr.w32
2=*dbrecov -db dbname
3=*dbcheck -db dbname -check -batch

[startup]
1 = tcpw.w32 -multiple -server 80
2 = dp4srvr.w32 -aux
3 = tcpmgr.w32 -server 80 -aux
4 = auxdistr.w32 -server 80 -use 80

Here is an example startup section for a Windows CE device that is also accessed from one or more PCs:

[startup]
1=.dp4srvr.wce -aux
2=tcpw.wce -server_name localhost -debug_connect
3=.tcpmgr.wce -aux -debug_connect

dp4srvr.wce and tcpmgr.wce are being run with "normal" rather than high priority to prevent the CE device being unresponsive while a PC is accessing it. In some cases it may even be necessary to run tcpmgr.wce with low priority:

3=-tcpmgr.wce -aux -debug_connect

Here is a similar startup section for a Linux or Unix computer

[startup]
1=srv3 -aux
2=tcp3 -server_name localhost -debug_connect
3=tcpmgr -display_address -aux -debug_connect

Alternative [STARTUP] and [PRELOAD] sections

The loader program will look for different section names instead of [startup] and [preload] if it is started with the command tails

-startup startup_section_name

or

-preload preload_section_name

This allows the user to have multiple startup configurations in the DP4 configuration file and to select the one required without editing the file.

If you use autoloading, you can specify the -startup and -preload command tails as part of the value for loader in the [WIN32] section, or as part of the value for loader in the [WINCE] section for Windows CE, or the [UNIX] section for Unix and Linux.


startup, preload

[system]
startup=startup
preload=preload

These set the default names of the [startup] and [PRELOAD] sections. You can change the values here as an alternative to using the -startup and -preload command tails on SRVW32. This might be useful if you want to issue a DP4.INI containing startup/preload sections for multiple configurations and want to be able to choose which sections are used with the minimum of fuss.


startup_delay

[windows]
startup_delay=2

The Win32 version of DP4 can detect when autoloading of the database manager is complete before allowing an application to continue. This is not possible with 16 bit DP4. This value specifies the delay, in seconds, which a 16 bit application that is attempting to autoload DP4 allows for the autoload to complete successfully.


startup_delay

[win32] [wince]
startup_delay=15

This sets the amount of time, in seconds, that each server program is allowed to complete initialisation. Increasing this value will not slow loading if the server programs can load more quickly. It may be necessary,very occasionally, to increase the value when loading certain ADCs or DP4 networking programs as part of the DP4 service.


statements

[sql.config DP4_database_name]
statements=64

This is the maximum number of statements that AUXWODBC will attempt to open per connection before it begins to close statements to free up some for reuse.

AUXOWDBC will query the ODBC driver for this value, so you should only override this if you encounter problems. This value replaces the max_statements entry implemented for AUXORACL and AUXSYBAS, since if more than one ODBC data source is in use you may need different values for the different sources.


StdButtonsWanted

[TRMW]
StdButtonsWanted=1

Applies only when the 4.5xx terminal manager is in use, and is considered to be obsolete.

If 1, some buttons are displayed at the bottom of the screen to provide enhanced mouse operation. This facility is intended as a 'quick fix' for users porting a text-based system.

You are strongly recommended to set this to 0 and to add buttons to maps as appropriate.


stdtitles

[TRMW]
stdtitles=1 or 2

Processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

If 0, titles in title bars are centered according to whether the 'center' flag is set in the map.

If 1 (the default in Windows 3.1 and NT 3.5) titles are centered.

If 2 (the default in Windows 95 and NT 4) titles are left justified.


string_nn

[TRMW]
string_02=DP4 Information
string_03=You must close DP4 applications before exiting Windows
string_04=Cannot access clipboard memory
string_05=[XXXXXXXX]. Program running withuot login."

These settings allow you to customise or translate hard coded messages inside the DP4 terminal manager. Please note the following points:

  • string_02 is used as the title bar for the message box that is displayed when a batch mode DP4 application terminates.

  • string_05 must begin with [XXXXXXXX] characters. It is used when writing information to syslog.prn about programs run outside a DP4 login session.


[SYSTEM]

The [system] section has the following entries:

  • autoload
  • load_dfnet
  • load_errlog
  • monitor
  • max_programs
  • shutdowndelay
  • startup, preload


timestamps

[sql.config DP4_database_name]
timestamps=1

This entry is used when you want to combine DP4 date and time fields with the same role into one SQL combined datetime column. To use this you will need to edit your schema file by hand to combine these fields into one column and also make a note of any name translations that will be needed.

You are recommended not to use this option if your database contains any tables or indexes where either, the time part or date part of such a field is a key field and the date part and time part are not both keys with the time key precedence equal to the date precedence + 1


Title

[TRMW]
Title=""

Normally specified in named program section only (with QAB programs); processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

If a value is specified the text replaces the default value for the title bar text of the program's main window.


touch_calendar

[TRMW]
touch_calendar=0

touch_calendar=1 changes the user interface of the DP4 calendar control (introduced into 4.623 versions of trmw32.dll and trmwce.dll built on or after 7th November 2007). With the default setting (touch_calendar=0) the user has to select a date by clicking on the day of the month, and the selection does not follow the mouse. If touch_calendar=1 is set, the selection follows the mouse (similar to the behaviour in an ordinary pull-down menu or pop-up window). The latter behaviour is probably preferable on machines with a touch-screen. Some users may prefer this behaviour in any case. However, if you use this setting on screens with an ordinary mouse attached, it is possible that the day of the month will sometimes be unintentionally changed.

For example, if the user selects a particular day of the month, and then realises that the month is wrong, and if they then use the mouse to click on one of the buttons to change the displayed month the user will very likely find that the day of the month has also changed, because the mouse has probably moved over another day field to reach the button that was used to change the month. If the change to the day is not noticed the user may enter unintended data.


[TRMW]

The [TRMW] section of the DP4 configuration file contains a large number of possible entries which can be used to configure the behaviour of the DP4 terminal manager. Unless otherwise noted, any entry for the TRMW] section may also be specified in a program specific section. For example you could force a program called MYPROG.EXE to use special values for entries in the [TRMW] section by supplying corresponding entries in the [MYPROG] section. Such entries always take precedence over values specified in the [TRMW] section itself.

Some entries in the [TRMW] section are read when an application starts, and cannot be changed afterwards. Others may be changed by calling the trm_set_progname() function, which can be used to over-ride the section used for accessing the [TRMW] entries. When a QAB program is first loaded it always calls this function, and sets the program name to the name of the QAB program. When trm_set_progname() is called the terminal manager rereads these variables, and may change the appearance, size or position of the application window as a result. Other variables are only read when they are needed and so always use the current program name. Entries marked below with an asterisk (*) are those which are either reread when QAB programs are loaded or when a C application calls the trm_set_progname() function, or which are only read as required and hence automatically use the current program name.

Many entries in the [TRMW] section are also used by the DFPRINT program. However in this case the values can, from 4.621, be overridden in the [DFPRINT] section as well as on a per-application basis.

Entries:

3DBorderSize
3DBorderStyle
buttoncheckboxes
*buttonframe
ButtonHeight
buttonstyle
*button_text_centred
charset
checkbox_height
checkbox_width
Colours
cols
defer_bug
*depth
*detach
dfprint_clip
dfprint_x_factor
dfprint_x_inch
dfprint_y_factor
dfprint_y_inch
DFPRINTForeground
dfshutdown
disablestyle
fh
*first_day
flatten_dialog
font
forcefont
HelpBorderSize
*hide_dead_chrome
*hide_empty_buttons
*idletime
isometric
keep_awake
*limitsize
*no_auto_close
*no_scroll_bars
*OkEndSession
*OldDP4Numbers
*pagesetup
*pause
*pointer_help
pointsize
*prtscr_editframe
*refresh_interval
rows
run_once
run_once_question
run_once_title
*save_position
*screendumpstyle
*screenprint
*setcommstate
shadow_title
SharePrinter
*show_im
smooth
StdButtonsWanted
*stdtitles
string_nn
*Title
*touch_calendar
*use_workarea
use600
useframe
usenonclient
*usestatus
*usestdfonts
*use_viewer
vertical_gradient
*viewer
weight
*width
winime
winprinfont
*winprint
wrapbug
*x
x_factor
x_inch
*y
y_factor
y_inch

From 4.621 there are some additional entries that can be used as part of the skinning facilities. See Configuration file entries for bitmaps for details.

Device Names

From Release 4.621(8) you can map the device names used for printing directly (i.e. not via DFPRINT) to other ports. For example if a program needs to print to a bluetooth device on a virtual COM port such as COM5, then create a print device for it using a device that you do not need, LPT3 perhaps, and add LPT3=COM5: to DP4.INI, either in the [trmw] section (if you are absolutely certain this will not cause problems for DP4 programs printing to another device), or in a [progname] section. This avoids the need for using the MODE command to redirect an LPT device to a COM port.

On Windows CE you must include the trailing : on the device name: DP4 will not add it for you, and the device will not be opened correctly without it.


UNIX

The [unix] section is used, from release 4.525, by the Unix and Linux versions of DP4, and contains the following entries:

  • errlog
  • loader
  • startup_delay

If a Unix DP4 application is multi-threaded, which is only supported using the POSIX pthreads library, you must add the line use_pthreads=1 in the [progname] section of the configuration file. For example if you have a multi-threaded application named multidp4 your dp4.ini file should contain the following:

[multidp4]
use_pthreads=1

There is no reason to add this entry for ordinary DP4 applications. Doing so will cause extra system resources to be consumed.


use600

[TRMW]
use600=1

Normally specified in named program sections only. Applies to Win32 4.6xx installations only.

If 0, programs use the 4.5xx terminal manager instead of the 4.61x version. This may be useful if you want to run a mixed environment. For example you could copy the 4.5xx versions of MAPEDIT and MAPPOST from a 4.5xx release into a 4.6xx installation (renamed to MAPED500 and MAPP500 for example), so that you could edit and post bot 4.5xx and 4.6xx maps for the same database. You would want to add the line use600=0 in the [MAPED500] section.


use_systray

[WINCE]
use_systray=1

By default the DP4 Service tries to run as a task that is displayed in the taskbar notification area (or system tray). However, on some versions of Windows CE there is no facility to actually display this area, and the DP4 service is therefore not visible as a task. If this value is set to 0 then DP4 system will appear as an ordinary task (so it will show up in the list of running programs on Pocket PC for example).


use_viewer

[TRMW]
use_viewer =0

Normally specified in named program section only (with QAB programs); processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

From 4.621, if use_viewer=1 then when a programs prints to the screen and is not using DFPRINT, then instead of immediately echoing all the print output to screen (as in previous versions), a temporary file is created. When the program closes the print output stream, the program specified in the viewer configuration setting is used to view the file.

Please note that the issued 4.621 dp4.ini has had this setting applied for most standard DP4 utilities that do any printing. You are recommended to make sure these new settings are applied to your own DP4 configuration file.


use_workarea

[TRMW]
use_workarea=0 or 1

use_work_area=1 can be used to set the default window size for applications to match the size of a maximised window (one that uses all space not reserved for windows such as the taskbar).

use_workarea defaults to 1 on Windows CE and 0 on other windows platforms. Prior to release 4.620 use_workarea is ignored, and the behaviour matches the use_workarea=0 behaviour of 4.620.

See the default window size page for more information.


useframe

[TRMW]
useframe=0 or 1

From 4.616 this option controls how the terminal manager creates the main window(s) for a DP4 application. When useframe=1 the main window frame is altered by addition of a client edge, which improves the appearance of help messages and menus.

The client edge is enabled if you have useframe=1 and disabled if you have useframe=0 or useframe=-1.

On Windows CE useframe is ignored and the system behaves as though it was set to useframe=-1

See Client Edge for more information.


usenonclient

[trmw]
usenonclient=1

Introduced in 4.616. See Non client area for more information.


usestatus

[TRMW]
usestatus =1

From 4.616, whenever possible help messages are displayed as status windows. The terminal manager will automatically reformat and resize the help message and status windows to fit. Help messages containing multiple controls or with multiple lines and tabs (indicating alignment requirements) won't get reformatted but will still be displayed as a status window where possible.

Maps which are typed as #helpmap, or which are displayed with BOTTOM are treated in the same way. They are not reformatted however.

Help messages won't be displayed as status windows if this would obscure other maps unnecessarily. However maps that have been turned into status windows don't count and will be obscured.

This behaviour has been adopted for compatibility with Windows standards.

You can disable all this new behaviour by setting usestatus=0 in the DP4 configuration file.


usestdfonts

[TRMW]
usestdfonts=0 or 1

Introduced in v 4.613; processed when QAB programs are loaded or when a C application calls the trm_set_progname() function. The details below are subject to modification.

If 0 (the only value allowed in Windows 3.1,NT 3.5x and on Windows CE), the terminal manager always uses the userdata/DP4 configuration file default font for maps for which use the 'Default' font.

If 1 (the default value in Windows 9x and NT 4 or later), the terminal manager uses the fonts specified in the 'Appearance' Tab of the Display Properties control panel item for menus, messages (remarks, warnings and error messages) and help messages (uses the tooltip font).


vertical_gradient

[TRMW]
vertical_gradient=0

If 1 the gradient on the title bar is drawn from top to bottom.

If 0 the gradient on the title bar is drawn from left to right.


viewer

[TRMW]
viewer = view

Normally specified in named program section only (with QAB programs); processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

From 4.621, if use_viewer=1 then when a programs prints to the screen and is not using DFPRINT, then instead of immediately echoing all the print output to screen (as in previous versions), a temporary file is created. When the program closes the print output stream, the program specified in the viewer configuration setting is used to view the file.

Other suitable programs you can use for viewing files are DP4ED, or the Windows Notepad program, or even Microsoft Word. The latter programs give you a Find and Replace Function, but may not display box characters correctly.

Note that you can specify different viewers according to the program being used.


weight

[TRMW]
weight=0

Selects the 'weight' of the default font. If left at 0 Windows will pick a value appropriate to the font. Other possibilities are:

  • 400 'Normal'
  • 700 'Bold'


width

[TRMW]
width=calculated value

Processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

width is the width of the client area of the main program window. If this is not specified then the value is defaulted in one of two ways:

  • If use_workarea=1 (available from 4.620) then the client area width matches that of a maximised window.

  • Prior to 4.620 or if use_workarea=0 the default width is calculated as cols * x_factor.

This value is updated when a program terminates. To prevent updating make the DP4 configuration file read only or set save_position=0.

See the cols, x_factor, and default window size pages for more information.


WIN32

The [win32] section is used by the Win32 version of DP4, and contains the following entries:

  • debug_console
  • dp4home
  • errlog
  • icon_visible
  • load_win1632w
  • loader
  • no_close
  • startup_delay
  • win1632w

If DP4 is set up to use Windows NT security the [win32] section may also contain the follow entries,described in Secure Operation of DP4

  • user=Windows NT username
  • password=Windows NT password
  • domain=Windows NT domain name


win1632w

[win32]
win1632w=win1632w.exe -quiet

The command line used to load win1632w if load_win1632w is set to 1.


WINCE

The [wince] section is used by Windows CE versions of DP4, and contains the following entries:

  • dp4home
  • errlog
  • loader
  • no_auto_close
  • no_close
  • startup_delay
  • use_systray


WINDOWS

Entries in the [windows] section are only used by the 16 bit version of DP4. The following entries are defined:

  • loader
  • pre500
  • startup_delay


winime

[TRMW]
winime=1

This entry applies to Far East versions of DP4 only.

If 1, the terminal manager sends the appropriate IME messages to cause IME data entry to take place in the appropriate control.

If 0 IME data entry is performed outside the control, normally near the bottom of the Windows Desktop.


winprinfont

[TRMW]
winprinfont="System"

The name of the font used by the WINPRIN program. This value can only be specified in [TRMW] section. See [WINPRIN] for other values concerned with font selection for WINPRIN.

See printing for more information about printing from DP4.

#


winprint

[TRMW]
winprint=0

Normally specified in [TRMW] only; processed when QAB programs are loaded or when a C application calls the trm_set_progname() function.

The DP4 device number which replaces the MS DOS device when spooling to the WINPRIN program. The DP4 device numbers are the numbers in the menu of devices offered when setting up device groups.

For example, 1 = PRN. Setting WINPRIN to 1 causes devices which print to PRN to print to WINPRIN instead. In 4.5xx you normally use WINPRIN by printing to the DATAFIT device. In 4.61x you normally use WINPRIN by printing to the WINPRIN device. From 4.616 you are recommended to use the DFPRINT program for printing, and to print to the DFPRINT device, when this value is not relevant.

See Printing for more details.


wrapbug

[TRMW]
wrapbug=0 or 1

Normally specified in [TRMW] only.

Certain versions of Windows erroneously suppress a leading blank immediately following a new line when the DrawText() function is used. Set wrapbug to 1 if your version of Windows has this bug and 0 otherwise.

The terminal manager defaults the value of wrapbug to 1 on Windows 3.1 and NT 3.5x and 0 to Windows 95 and NT 4.

We are not aware of any necessity to specify a value for this variable, but it could be necessary if you use a non-standard version of Windows.


x

[TRMW]
x=CW_USEDEFAULT

Processed when C or QAB programs are loaded, or when a C application calls the trm_set_progname() function.

x is the horizontal-axis coordinate of the top left corner of a window.

If use_workarea=1 (available from 4.620) then the default value for x matches the value for a maximised window.

The x value is updated when a program terminates. To prevent updating, make the DP4 configuration file read only or set save_position=0.

See the default window size page for more information.


x_factor

[TRMW]
x_factor=calculated number

From 4.616 you can specify values for x_factor and y_factor in the DP4 configuration file. The only time you would be likely to need to do this is if you wanted to use a default font that was slightly smaller than your desired x_factor and y_factor.

The default x_factor is the maximum of the following values for the default font:

  • The average character width reported by Windows
  • The width of 'n'
  • The width of '8'

From 4.616 this deafult value may be modified if isometric=1.

See Coordinate system for full explanation of use.

Between 4.616 and 4.620 this option was also used by DFPRINT. This caused problems for users who used x_factor and y_factor to make screens designed for VGA resolution run full size on SVGA or XGA resolution screens. From 4.620 the x_factor for printing with dfprint is set separately using dfprint_x_factor.

If you use x_factor or y_factor to make your screens bigger or smaller, then controls that use a non default font may use a font that is too small or too big. From 4.620 you can control font sizes at run time using the new x_inch and y_inch settings.

In 4.623 builds dated on or after 10 July 2006 you can specify floating point values for x_factor.


x_inch

x_inch,y_inch

[TRMW]
x_inch=72
y_inch=72

DP4 stores font sizes using points (there are 72 points to an inch). In theory this should mean that DP4 scales font sizes correctly when an application is displayed on a higher resolution screen. In practice this may not be the case, because Windows fails to adjust the reported d.p.i (dots per inch) metric when the screen resolution is changed. Also map and control sizes are not stored in points but relative to the x_factor and y_factor size. For either or both of these reasons it may be desirable to scale the fonts used by a DP4 application. This can be done using the x_inch and y_inch settings.

Decreasing this value increases the size of fonts. For instance suppose you have an application that was originally designed to run full screen on a 640*480 resolution screen (using small fonts in the Display Settings), and now want to run full screen at 800*600 still using Small Fonts. One way to achieve this would be as follows:

In the [trmw] or [progname] section set:
x_factor=10 ; was 8 on VGA
y_factor=20 ; was 16 on VGA
x_inch=58 ;(integer value nearest 72*8/10)
y_inch=58 ;(integer value nearest 72*16/20)

This will increase the size of all the fonts in your application by 72/58, except for the following fonts:

  • The chosen default font (and the fonts that replace the default font in menus, remarks etc., when usestdfonts=1)

  • Raster (non scalable) fonts, where the Windows font mapper cannot find a good match to the adjusted size (for example the SYSTEM font only exists at one point size).

If your application originally used the system font as its default font you would need to change it to use a font such as 11 or 12 point Verdana.


y

[TRMW]
y=CW_USEDEFAULT

Processed when C or QAB programs are loaded, or when a C application calls the trm_set_progname() function.

y is the vertical-axis coordinate of the top left corner of window. You must specify the x value if you want to specify the y value otherwise it will be ignored.

If use_workarea=1 (available from 4.620) then the default value for y matches the value for a maximised window.

This value is updated when a program terminates. To prevent updating make the DP4 configuration file read only or set save_position=0.

See the default window size page for more information.


y_factor

[TRMW]
y_factor=calculated number

From 4.616 you can specify values for x_factor and y_factor in the DP4 configuration file. The only time you would be likely to need to do this is if you wanted to use a default font that was slightly smaller than your desired x_factor and y_factor.

The default y_factor is the height of the default font as reported by Windows, including any external leading. From 4.616 this default value may be modified if isometric=1.

See Coordinate system for full explanation of use.

Between 4.616 and 4.620 this option was also used by DFPRINT. This caused problems for users who used x_factor and y_factor to make screens designed for VGA resolution run full size on SVGA or XGA resolution screens. From 4.620 the y_factor for printing with dfprint is set separately using dfprint_y_factor.

If you use x_factor or y_factor to make your screens bigger or smaller, then controls that use a non default font may use a font that is too small or too big. From 4.620 you can control font sizes at run time using the new x_inch and y_inch settings.

In 4.623 builds dated on or after 10 July 2006 you can specify floating point values for y_factor.


Command area

Here you can enter an SQL statement of up to 4 lines by 79 characters. Pressing <Tab> on the fourth line, or <Enter> at any point, runs the SQL statement against the database. This assumes you are using a Windows or SAA style userdata.sys; if not the effect of <Tab> and <Enter> will be the other way around.

You can use the <Page Up> and <Page Down> keys to recall previously entered statements


Commit

This is equivalent to the COMMIT WORK statement


Database Name

Enter the name of the DP4 database this connection should use. You can leave this blank if it is the same as the name you gave the data source.


Exit

Use this to shut down the SQL program


Fields

Displays a submenu of the tables on the database. Selecting a table displays a further submenu showing the names of the table's fields


Fuzzy Null

If the Fuzzy Null flag is enabled, DP4 deliberately returns answers to certain comparisons involving character fields that are strictly speaking wrong, but which may be neccessary to make applications using ODBC to work as expected:

  • If a character field contains no characters that are not blank or binary zero DP4 considers IS NULL comparisons to succeed if the Fuzzy Null flag is set. Similarly IS NOT NULL comparisons will fail for such fields. Without the Fuzzy Null flag only fields are null in DP4 terms match IS NULL comparisons.

  • However, DP4 will still allow comparisons of such fields with other values and will return meaningful results. This is incorrect according to SQL standards: strictly speaking if a field is NULL it has no value and cannot be compared for equality or inequality with any value whatsoever. However many applications erroneously assume that a character field whose value is an empty string is null. This is definitely incorrect - it is similar to assuming that a numeric field whose value is zero is null, something that is obviously wrong.


Help

Displays a brief summary of the syntax of the supported SQL statements


Overview: The DP4 Oracle Gateway ADC

The interaction of the software components, both DP4 and non-DP4, using the Oracle Call Interface (OCI) and SQL *Net are illustrated on the right.

As with all schemes that use an Auxiliary Database Controller (ADC), the logic of the application is separated from the logic of the server selection. The DP4 application is unaware that it is accessing information from something other than a local DP4 database. When the application wants to access the database, it makes its requests to the local DP4 database manager, (DP4SRVR.W32 on Windows,srv3 on Unix). A separate program, the DP4 Oracle Interface, intercepts the database accesses, creates SQL statements and directs them to the OCI.

A DP4 database must also be present. While the DP4 database does not hold any data, it must have the same table and field definitions as its associated Oracle database. The reason for this is that the Oracle database has an incompatible data dictionary, so the structure information for fields and tables is always taken from the base dictionary of the DP4 database.

You may wonder how DP4 determines that base dictionary tables should be accessed on the DP4, rather than the Oracle, database. This is due to the DP4 "local/remote" mechanism. When the Oracle Interface is loaded, base dictionary tables are, by default, marked as local and fetched from the DP4 database. Tables containing data are, by default, marked as remote and fetched from the Oracle database.

The Oracle Interface constructs SQL statements for accessing the Oracle database based on DP4 table names and field names. As you might expect, some of the names on the DP4 database are invalid on the Oracle database and the converse is also true. To cater for this, you can define a translation table that converts one set of names to the other.

It should be noted that the generated SQL statements are constructed purely as a translation of the DP4 function being requested. The Gateway ADC has no knowledge of what the application is trying to do. This means that the SQL generated may not be optimal. For example the Gateway ADC has no knowledge of the constraints the application may be using to filter the records it is fetching. Thus a bigger result set may be generated than is required. A particular problem is there is no way in SQL of fetching records one at a time. This means that many operations which are very efficient on a DP4 database are inefficient on Oracle. Problems are likely to show up particularly on large databases. See Implementation Details for more information.


Print

Brings up the DP4 Select Output Devices dialog so that you can choose where to send the results of an SQL query


Results area

This is the area where the results of your SQL enquiry are displayed. If more data is returned than will fit in the window, as indicated by the message "More below", (or scroll-bars on Windows), you can browse the data by selecting the Results option and using the <↑> and <↓> keys.


Results

Any data returned by the SQL statement is displayed in the Results area. If more data is returned than will fit in the window, as indicated by the message "More below" (or scroll-bars on Windows), you can browse the data by selecting the Results option and using the <↑> and <↓> keys.


Rollback

This is equivalent to the ROLLBACK WORK statement.


Alternate Server

Normally DSNs will connect to databases on the local machine or a machine determined by your DP4 networking configuration. However, if you are using DP4 TCP/IP networking in a configuration that supports named connections (i.e. Simple Client Only Networking or Named Connection Configuration) you can force particular DSNs to connect to a database on a different server by clearing the "Use Default Server" checkbox and entering the name of the server. If the DP4 service is running in configurations that don't support named connections (e.g. standalone or multiple resilience configurations) DP4 will silently ignore this option and connect to the default server anyway.

For further detail on which DP4 networking configurations support named connections see Comparison of DP4 Networking Configurations in the Guide to DP4 Networking and Resilience.


Statement

Moves the cursor to the Command area where you can enter an SQL statement of up to 4 lines by 79 characters. Pressing <Enter> on the fourth line, or <Tab> at any point, runs the SQL statement against the database

You can use the <Page Up> and <Page Dn> keys to recall previously entered statements


Tables

Displays a submenu with the names of the tables on the database