ITP DID Language

DID Module

You must use ODBC for the CONNECTION attribute in a DID Module that contains ODBC Connections Entries. A DID can contain multiple ODBC Connection DID Modules. However, ITP can connect to only one data source at a given time. You can use different data sources for different DIDs and Models, but not within a single DID.

Entries and Fields

ITP uses the DATABASE_FIELD attribute to construct the SELECT part of an SQL statement. You can define most column specifications that are valid in an SQL statement in the DATABASE_FIELD attribute.

Example

The basic usage of DATABASE_FIELD is to change a column name to an ITP Field name. In the following example, the column ORDNR will be renamed to Order_number.

Order_number     NUMERICAL(8 0)  DATABASE_FIELD "ORDNR"

Example

You can also use renaming for Entries that use columns from different tables:

...
DATA_RETRIEVAL   "from CUSTOMER, ADDRESS where CUSTOMER.CUSTOMERNUMBER = ADDRESS.CUSTOMERNUMBER"
...
Surname          C_CHAR    (31  )  DATABASE_FIELD "CUSTOMER.SURNAME"
City             C_CHAR    (31  )  DATABASE_FIELD "ADDRESS.CITY"
...

Example

You can also do some simple conversions with DATABASE_FIELD. In the following example, the contents of the column CITY will be changed to all uppercase symbols. You can achieve this through the function UCASE.

City             C_CHAR    (30  )  DATABASE_FIELD "{fn ucase(CITY)}"

Example

Of course, you can combine the above examples into more complex conversions. ITP expects dates to be of the (model document language) type NUMBER and in a "YYYYMMDD" format. In your database, it is probably specified as a DATE Field. You can use DATABASE_FIELD to convert the date automatically to the correct ITP format.

Some_date        NUMERICAL (11 0)  DATABASE_FIELD "( {fn year(SOME_DATE)} * 10000 +{fn month(SOME_DATE)} * 100 + {fn dayofmonth(SOME_DATE)} )"

Datatypes

Currently only the data types FieldC_CHAR, TEXT, NUMERICAL, DOUBLEW_CHAR make sense for Fields in the ODBC Connection. The DID Language allows you to use other data types, but this might give unexpected results.