Registering existing c-tree files in c-tree SQL Server
In order to work on c-tree files via SQL, it's necessary to link the files into the c-tree SQL database. The ctutil utility provides a function for this purpose:
-sqlize.
The first step is to create special dictionary files for the archives that need to be installed in the c-tree SQL Server. In order to create the dictionary files, COBOL programs containing file descriptions must be compiled with
-efc option.
For example:
The isCOBOL Compiler will generate a file named <filename>.iss for each file described in the COBOL program.
Note -c-tree SQL doesn’t allow identifiers whose name begins with underscore. If one of the field names in your FD begins with underscore, you should change that name for the iss dictionary by using the EFD
NAME Directive.
When dictionary files are available, the following command can be launched:
ctutil -sqlize c-tree-file iss_file database_name[-sign=convention] [-symb=table_name] |
Where:
• c-tree-file is the name of the physical c-tree file. This name is resolved server-side, so if a relative path is specified, the path will be relative to the ctsrvr working directory.
• iss-file is the name of the iss file generated by the isCOBOL Compiler. This file is loaded client-side, if a relative path is specified, the path will be relative to the ctutil working directory.
• database_name is the name of the database in which the file must be installed. c-tree SQL provides a default database named ctreeSQL. The user can create new databases using DBA utilities.
• convention specifies the data convention to be used for the c-tree file. Use "A" if COBOL programs are compiled with –dca or without options. Use "M" if programs are compiled with –dcm, use "I" for –dci and "D" for –dcd. If omitted, "A" is assumed.
• table_name is an optional parameter that allows to specify a different name for the table on the database. By default the table on the database has the same name of the c-tree file.
For example:
ctutil –sqlize file1 file1.iss ctreeSQL |
The administrator password is required.
The administrator password is included in the configuration file.* This file can be:
• in the working directory in a file named ctree.conf
• in the directory specified in the environment variable CTREE_CONF
• passed to ctutil using the -c option in the command line
This configuration file should contain this statement:
<config> <instance server="FAIRCOMS" user="admin" password="ADMIN"> </instance> </config> |
Other statements can be added to the configuration file as needed.
*To be compatible with older c-tree versions that allowed you to pass the administrator password in the command line, you can add the new configuration variable COMPATIBILITY SQLIMPORT_ADMIN_PASSWORD to the c-tree Server configuration file (ctsrvr.cfg). However, adding the admin and password to the configuration file as shown above is considered the best practice.
The sqlize option has the same effect as running in sequence the following deprecated options:
-sqlunlink | Unlink the file from the c-tree SQL database. The file is no more available as table for SQL clients despite it still exists on disk. |
-sqlinfo | Include the iss dictionary in the c-tree DAT file header. |
-sqlllink | Link the file to the c-tree SQL database. The file is now available as table for SQL clients. |
The above options are still supported for backward compatibility and are documented in the
ctutil manual.
How to deal with homonymous files
When working with ISAM disc files you can have the same file name in different paths. Let’s think about a multi-company application that keeps the history of some archives among the years. On disc you could find:
• compay1\2020\invoices.dat
• compay1\2021\invoices.dat
• compay2\2020\invoices.dat
• compay2\2021\invoices.dat
• etcetera
In order to reflect this situation in SQL, you might consider having multiple databases (one database per company) and use a prefix before the table name (use the year as prefix).
To create additional databases in c-treeSQL you can use either the
c-treeSqlExplorer graphical utility or the
ctsqlcdb command line utility.
Assuming that you have created two databases, DBCompany1 and DBCompany2, you can sqlize the above archives as follows:
ctutil –sqlize /path/to/company1/2020/invoices invoices.iss DBCompany1 -prefix=2020_ ctutil –sqlize /path/to/company1/2021/invoices invoices.iss DBCompany1 -prefix=2021_ ctutil –sqlize /path/to/company2/2020/invoices invoices.iss DBCompany2 -prefix=2020_ ctutil –sqlize /path/to/company2/2021/invoices invoices.iss DBCompany2 -prefix=2021_ |