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. |
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 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. |
| [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.
|
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.