FairCom DB SQL with Callbacks
The introduction of the c-treeDB API Callback feature into FairCom DB SQL makes possible an easy and transparent translation of customers’ existing ISAM or low-level native data to a data format that is compatible with FairCom DB SQL requirements.
The key advantage of this implementation is the callback feature can be used with standard production FairCom DB SQL. If the callback dynamic library is not present, FairCom DB SQL performs normally as before. When the callback dynamic library is present FairCom DB SQL will automatically load the dynamic callback library every time a new FairCom DB SQL user logon is performed.
The current implementation of the FairCom DB SQL Callback library makes use of a configuration file as there is no practical way to detect which CT_INT4 fields are actually a MYTIME field or a CT_INT4 field. The configuration file is loaded automatically by the dynamic library.
The Server Architecture
On startup, FairCom DB SQL will attempt to load the FairCom DB SQL dynamic callback library when the SQL_OPTION LOAD_CALLBACK_LIB configuration option is specified in the server configuration file, ctsrvr.cfg.
Note: For Windows systems, the dynamic callback library is named ctsqlcbk.dll. For Unix-based systems, the dynamic callback library is named libctsqlcbk with a filename extention appropriate to that operating system's default shared library conventions (.so, .sl., .dynlib, etc.).
The diagram below depicts the relationship of FairCom DB SQL with the callback library and its configuration file:
If the callback library is not found, no messages are reported because the dynamic library is not required for normal FairCom DB SQL operation, however, types conversion will not take place.
If the callback library is located and loaded, the following steps are taken:
- The ctsqlcbk.ini configuration file is loaded and parsed;
- The appropriate c-treeDB API callbacks are established;
Windows and Unix-based systems use different methods for locating dynamic libraries. In Windows systems, dynamic libraries are called DLLs - Dynamic Link Libraries (.dll), while Unix-based systems use the term Shared Libraries (.so, .sl). Please refer to the FairCom DB SQL Callback Dynamic Library section below for detailed information on the use of the libraries on Windows and Unix-based systems.
The configuration file is described in detail in the FairCom DB SQL Configuration section.
FairCom DB SQL Callback Dynamic Library
A shared library provides a mechanism that allows a single copy of code to be shared by several instances of programs in a system. Programs can be written to load dynamic libraries without any prior arrangement at link time. Shared libraries are loaded dynamically while the program is running.
One of the major advantages of using dynamic libraries is the possibility to provide new, specialized, or enhanced functionality by developing components that can be loaded at runtime, under program control.
Note: To instruct the server to load the callback library, the SQL_OPTION LOAD_CALLBACK_LIB FairCom Server configuration keyword must be specified.
Windows Environment
Under Windows, the FairCom DB SQL callback dynamic library is called ctsqlcbk.dll, and is created as a Windows DLL.
When loading a DLL under Windows, the system looks through a series of directories, as follows:
- The directory from which the application loaded
- The current directory
- The Windows System32 directory
- The Windows System directory
- The Windows directory
- Directories listed in the PATH variable
We recommend the FairCom DB SQL callback DLL be located in the FairCom DB SQL operating directory.
Unix Environment
Under Unix based systems, the FairCom DB SQL callback dynamic library is called libctsqlcbk, with a file extension appropriate for that operating system's shared library conventions (.so, .sl, .dynlib, etc.) and it is created as a Unix shared library.
Unix shared libraries must be located and loaded by the system on demand. In order for shared libraries to be loaded at runtime, the Unix dynamic loader must know where to locate it at run time.
Unix systems use environment variables to select custom library directories. Among the different Unix platforms, there are three search path variables in use.
The table below lists these variables and the platforms that use them.
Environment Variable |
Unix Platforms |
|---|---|
LD_LIBRARY_PATH |
Solaris, UnixWare, Linux |
LIBPATH |
AIX |
SHLIB_PATH |
HP-UX |
We recommend that the FairCom DB SQL callback shared library be located in the FairCom DB SQL operating directory. For example, if the FairCom DB SQL directory is /home/ctreeSQL, the LD_LIBRARY_PATH environment variable could be:
$ LD_LIBRARY_PATH = /home/ctreeSQL
$ export LD_LIBRARY_PATH
If the LD_LIBRARY_PATH environment variable is already set, it may be necessary to append the FairCom DB SQL operating directory at the end of the path as shown here:
$ LD_LIBRARY_PATH = $LD_LIBRARY_PATH:/home/ctreeSQL
$ export LD_LIBRARY_PATH
FairCom DB SQL Callback Configuration File
Since there is no practical way to inspect a table and detect which CT_INT4 fields are actually MYTIME fields, a configuration is necessary for describing every table that has at least one MYTIME field.
By default, the FairCom DB SQL callback configuration file is called ctsqlcbk.ini. Both Windows and Unix based systems use the same configuration syntax and file name convention.
The syntax of the configuration file is similar to Windows .INI files. Comments are started with a semicolon character. Every character after a semicolon is treated as comments and will be ignored, as shown here.
Configuration .ini
; this is a table declaration
[nmalert]
field = number, CT_FSTRING, 10 ; first field
Each table is described in the configuration file with the table name inside square brackets. Each table represents a section entry, followed by the table’s field, index and index segment definitions as shown above:
[nmalert]
The fields are defined using the following syntax:
field = field-name, field-type, field-length
field-name is a string containing the field name. The field name should not be placed inside single or double quotes, and a comma must not be part of the field name.
field-type is one of the following:
Available Field Types | ||
CT_BOOL |
CT_MONEY |
CT_FSTRING |
CT_CHAR |
CT_CURRENCY |
CT_FPSTRING |
CT_CHARU |
CT_NUMBER |
CT_F2STRING |
CT_INT2 |
CT_DATE |
CT_F4STRING |
CT_INT2U |
CT_TIME |
CT_STRING |
CT_INT4 |
CT_TIMES |
CT_PSTRING |
CT_INT4U |
CT_SFLOAT |
CT_2STRING |
CT_INT8 |
CT_DFLOAT |
CT_4STRING |
CT_INT8U |
CT_ARRAY |
|
field-length is an integer value representing the field length.
Conversion Example
[nmalert]
field = number, CT_FSTRING, 10
field = seq, CT_INT2, 2
field = modwhen, CT_TIMES, 8
field = modby, CT_INT2, 2
field = code, CT_FSTRING, 5
After the field definitions, each index, with its segments, must be declared. The syntax for an index definition is:
index = keylen, nsegs
Conversion Example
[nmalert]
field = number, CT_FSTRING, 10
field = seq, CT_INT2, 2
field = modwhen, CT_TIMES, 8
field = modby, CT_INT2, 2
field = code, CT_FSTRING, 5
index = 12, 2
segment = number, 2
segment = seq, 8
index = 13, 2
segment = code, 2
The index segment must follow the index declaration. Each index segment must be declared using the following syntax:
- If an index segment consists of a whole field:
segment = field-name, segmode
field-name is a string containing the field name. The field name should not be placed inside single or double quotes, and a comma must be part of the field name.
segmode is an integer value representing an index segment mode.
- If an index segment consists of an arbitrary offset in the record buffer that cannot be mapped back to the offset of a field in the DODA, or an index segment length does not match a field length, the index segment syntax is:
seg = segoffset, seglength, segmode
segoffset is an integer value indicating the offset of the segment in the record buffer.
seglength is an integer value indicating the segment length in bytes.
segmode is an integer value representing an index segment mode.
Conversion Example
[nmalert]
field = number, CT_FSTRING, 10
field = seq, CT_INT2, 2
field = modwhen, CT_TIMES, 8
field = modby, CT_INT2, 2
field = code, CT_FSTRING, 5
index = 12, 2
segment = number, 2
segment = seq, 8
index = 13, 2
segment = code, 2
Every table that has one or more MYTIME fields must be added to the configuration file. There is no special ordering of the tables. Below is an example of a configuration file with two tables: nmalert and nmmdesc.
Conversion Example
[nmalert]
field = number, CT_FSTRING, 10
field = seq, CT_INT2, 2
field = modwhen, CT_TIMES, 8
field = modby, CT_INT2, 2
field = code, CT_FSTRING, 5
index = 12, 2
segment = number, 2
segment = seq, 8
index = 13, 2
segment = code, 2
[nmmdesc]
field = number, CT_FSTRING, 10
field = modby, CT_INT2, 2
field = modwhen, CT_TIMES, 8
field = text, CT_STRING, 0
index = 10, 1
segment = number, 2
How to specify MYTIME field translation
A MYTIME field, i.e. a CT_INT4 field used to store the MYTIME data, should be declared in the configuration file as a CT_TIMES field type.
For example, in the nmalert table the modwhen field is an MYTIME field type. The definition of the modwhen field in the configuration file should have a field type of CT_TIMES.
Conversion Example
[nmalert]
field = number, CT_FSTRING, 10
field = seq, CT_INT2, 2
field = modwhen, CT_TIMES, 8
field = modby, CT_INT2, 2
field = code, CT_FSTRING, 5
index = 12, 2
segment = number, 2
segment = seq, 8
index = 13, 2
segment = code, 2
When the callback sees a table declared in the configuration file with a DODA field of type CT_INT4 and a field definition of CT_TIMES in the configuration file, it automatically performs the data translation between the C RTL time functions and the FairCom DB SQL time stamp data type.
Specifying the configuration file location
We recommend that the FairCom DB SQL callback configuration file be located in the operating directory. By default, the FairCom DB SQL callback dynamic library will try to locate the configuration file in the current directory.
There are certain circumstances that can cause the current directory to change, or to be undefined, after the server begins execution. If the server is started as a service under Windows, the current directory may be undefined, or if the server is started as a daemon under Unix systems, the current directory may change.
For these circumstances, the location of the configuration file may be specified using the FairCom DB SQL configuration file. The following entry can be added to the server’s configuration file to specify the location of the FairCom DB SQL callback configuration file:
APP_NAME_LIST full-path-of-configuration-file
Example
SERVER_NAME FAIRCOMS
SQL_DATABASE ctreeSQL
COMPATIBILITY LOG_WRITETHRU
CONSOLE NO_SHUTDOWN_PROMPT
DAT_MEMORY 5000000
IDX_MEMORY 9000000
APP_NAME_LIST C:\FairCom\V8.27\ctreeServers\ctreeSQLServer\ctsqlcbk.ini
When using the APP_NAME_LIST server configuration keyword to specify the location of the configuration file, make sure the APP_NAME_LIST is the first one in the configuration file, that is, no other APP_NAME_LIST entries may appear before the entry that specify the location of the configuration.
The File Definition Generating Utility
A utility has been created to aid in the process of creating a configuration file with table, field and index definitions. The utility is called the File Definition Generating Utility, ctgendef, and its syntax is as follows:
$ ctgendef table-name -u username -p password -s servername
Where:
table-name -- file name with or without a .dat extension.
- username -- username username
- password -- password of user
- servername -- server where the file resides
The output of the ctgendef utility is sent to the console. It may be captured to a file using default shell arguments. For example:
$ ctgendef table-name -u username -p password -s servername > ctsqlcbk.ini
If multiple tables are to be added to the configuration file, the following commands may be used:
$ ctgendef table1 -u username -p password -s servername > ctsqlcbk.ini
$ ctgendef table2 -u username -p password -s servername >> ctsqlcbk.ini
$ ctgendef table3 -u username -p password -s servername >> ctsqlcbk.ini
$ ctgendef table4 -u username -p password -s servername >> ctsqlcbk.ini
Example
$ ctgendef nmalert
[nmalert]
field = number, CT_FSTRING, 10
field = seq, CT_INT2, 2
field = modwhen, CT_INT4, 4
field = modby, CT_INT2, 2
field = code, CT_FSTRING, 5
index = 12, 2
segment = number, 2
segment = seq, 8
index = 13, 2
segment = code, 2
Notice that the ctgendef utility outputs the definition of the modwhen field as CT_INT4 as declared in the nmalert’s DODA. You need to edit the configuration file and, in the particular case of the nmalert table, change the definition of the modwhen field to CT_TIMES. After this modification, the configuration looks as the following, noting the change to the field=modwhen entry.
Example
[nmalert]
field = number, CT_FSTRING, 10
field = seq, CT_INT2, 2
field = modwhen, CT_TIMES, 8
field = modby, CT_INT2, 2
field = code, CT_FSTRING, 5
index = 12, 2
segment = number, 2
segment = seq, 8
index = 13, 2
segment = code, 2