General
ODBC versions
SQL
SQL queries in multi control files
Dynamic assigning of aliases
SQL-Queries in the field “REF”

---

General

SQL can be accessed directly from CIB merge via the CIB SQL DLL additionally stored in the program directory. The result of a query (a table) is used like a CSV dataset file. Instead of specifying a control file in the multi control file, a query is placed there in SQL. It is also possible to dynamically assign a control file or SQL query to existing or new control file aliases. Dynamic alias assignment is also allowed without any control file or multi control file. Furthermore, it is possible to insert the value of the first column and first row of a result table of an SQL query immediately into the raw text (without the detour via an alias, the field "Next" and variables).

ODBC versions

The CIB SQL Dll uses internally ODBC API conformance levels Core, Level 1 (e.g.: long data) and Level 2 (e.g.: scrollable cursor). The SQL conformance level Extended SQL Grammar is required for data fields. See ODBC SDK 2.10 Programmers Reference.

It is therefore important to ensure that the ODBC versions and driver versions used support the corresponding levels.

The CIB SQL functionalities have been tested with ODBC 3.51 under Windows NT 4.0 using the Microsoft Jet ODBC drivers for MS Access, which are supplied with the corresponding ODBC versions.

This version also supports joins (select to multiple tables).

SQL

SQL access should be possible directly from CIB merge. The result of a query (a table) should be used like a control file. Instead of specifying a control file in the multi-control file, a query is placed in SQL. A second extension should be able to dynamically assign a control file or SQL query to existing or new control file aliases. A third extension is to insert the value of the first column and first row of a result table of an SQL query immediately into the raw text (without the detour via an alias, the "Next" field and variables). A fourth extension should allow dynamic alias assignment even without a multi-control file and possibly even without a normal control file.

SQL queries in multi control files

The entry of a field in the multi-control file usually looks like this: "[;]control file" or "header file;dataset file". For SQL accesses, there is the option of "SQL:Database;Query".

The new function is only available with a DLL for SQL queries.

The query is sent to the database. Further accesses are made with the known commands for control files with unchanged meaning. The only difference is the data source: The table is not in the control file as a set of records, but in a database. (The database must be set up as a user or system data source in the system settings for ODBC.)

The following example provides two control files with the aliases NormalDat and SqlDat The control file SqlDat contains two columns from the prices table. The database containing the table is made available via ODBC with the name ODBC Tariffs Database.

Multi control file:

NormalDat;SqlDat
“normal.csv”; “SQL:ODBC Tariffs Database;SELECT TariffNo, TariffName FROM Tariffs ORDER BY TariffNo”

Dynamic assigning of aliases

The "Next" command usually reads the next data row or places the read position in front of the beginning of the data source. But it is also possible to redefine or reassign an alias with "NEXT DEF:Alias;Data source”.

The alias will be redefined if it does not exist yet. If the alias is already occupied, the associated data source will be closed and the alias reassigned.

The data source can be a control file or an SQL query. The value behind the semicolon must therefore look like an entry in the multi control file.

This function is also available without any control file or multi control file being specified in the parameters passed to CIB merge.

The example dynamically defines the alias SqlDyn in the raw text as the entire tariffs table. Certain fields are then listed in a table.

Raw text:

{ NEXT “DEF:SqlDyn;SQL:ODBC Tariffs table;SELECT * FROM Tariffs table ORDER BY TariffNo” }

Tariff number

Name

{ IF { DATASET ?SqlDyn } = 1 “

{ REF TariffNo \*Character format }

{ REF TariffName \*Character format }

{ Next SqlDyn }” \*while }

 

Restriction: Initially this function should only be available if a multi-controller file has been specified. A later extension to the case without any control file or at least without multi control file is intended.

SQL-Queries in the field “REF”

The command "REF" previously inserted the value of a variable into the raw text. The goal is to use "REF SQL:Database;Query" to insert the value of the first row and first column of the query result table into the text.  No alias or variable is required, but the rest of the table is no longer available without a new query. (However, the purpose of this type of query is also to retrieve a single special value from the database, for which an extra query was necessary anyway.)

The following example inserts the name of a tariff into the raw text. The tariff is selected via the previously defined variable N. For this example, this variable must have the content "'801"" according to the database structure and SQL syntax. (including the single quotation marks), because the condition field TariffNo used is a text field.

Control file:

N;...
"'801'"; ...

Raw text:

{ REF "SQL:ODBC Tariffs Database;SELECT TariffNo FROM Tariffs WHERE TariffNo = {REF N}" }

Limitation:

This way, no memo fields can be selected in the first version. This is due to a technical limitation of Microsoft ODBC.