Considerations to keep in mind as you develop and deploy your FairCom DB Standalone SQL Service solution. Note references to SQL in this chapter and the web links below are also applicable to FairCom DB Standalone SQL Service.
Referential Integrity
Note, the FairCom DB Standalone SQL Service does not support transactions nor transaction roll back capability and is therefore intended as a SQL query interface over existing data. By default, FairCom DB Standalone SQL Service does not allow updates or inserts. It is possible to enable inserts by adding the following keyword pair in ctsrvr.cfg:
SQL_OPTION FPUTFGET_RW
This configuration enables inserts and updates and is solely intended for single row database updates. If you choose to update multiple rows at a time (not recommended), you should ensure you have a recent complete database backup as any errors encountered during multi-row updates will result in loss of relational data integrity.
ODBC Notes
FairCom Corporation has offered two different ODBC Drivers over the years:
- The first ODBC driver, called the "FairCom 32-Bit ODBC Driver," was released in the mid 1990’s and had a version for c-tree V4 files and one for c-tree Plus V6 and later.
- The second ODBC driver, called the "FairCom DB ODBC Driver," was released around 2003.
Many c-tree stand-alone (e.g. FPUTFGET) applications over the years have used the older "FairCom 32-Bit ODBC Driver." If you are migrating such an application to use the more powerful FairCom DB ODBC Driver, then please be aware of the following slight differences in behavior between these two drivers:
- All table names returned by the FairCom DB ODBC Driver are as "owner.tablename". The c-tree ODBC Driver does not prepend the "owner" name to the table name.
- The SQL_OPTION OWNER_FILE_NAMES only affects the name of the table created on disk. It determines whether the owner name is prepended to the physical table name or not. The table names returned by the FairCom ODBC driver are not affected by the SQL_OPTION setting.
- Using the c-tree ODBC driver it was common practice to represent the symbolic table name in uppercase. You can use uppercase table names in FairCom DB SQL as long as you do not wrap them in double quotes.
Another option is to use the SQL_OPTION DB_CASE_INSENSITIVE keyword before creating the template database and the FairCom DB SQL databases.
- Hidden Fields: Both the c-tree and the FairCom DB ODBC drivers will not display a field whose field name given in the DODA begins with an underscore: "_". Hiding fields that are used to form keys is not recommended and may cause problems.
- Read-Only Fields: Both the c-tree and the FairCom DB ODBC drivers will display but will not allow updates on a field whose field name given in the DODA begins with the "legacy read only" field identifier, a right square bracket: "]".
FairCom DB SQL import does not handle this field identifier. This character is imported as part of the field name unless the -k option (skip fields not complying with conventional identifiers rules) is specified on import. However, this is an unconventional character and must be double-quoted.
See also:
Information for Developers
Transaction Control
All c-tree files (.dat and .idx) are FPUTFGET and do not support transaction control. (All system tables and housekeeping files, including FAIRCOM.FCS, are under full transaction control).
The FairCom DB Standalone SQL Service does not support transaction control of the data files as it is tied to the multi-user standalone database model, which also lacks transactional support. Therefore, be very careful if you choose to update your data files through the ODBC Driver as you lose a large degree of database consistency, which would be taken for granted with a fully relational DBMS, such as FairCom DB SQL.
For example, if the system crashes, or a routine error occurs in the middle of a large update that touches multiple rows or tables, your files will not be rolled back to their original consistent state. You might end up with a few of the tables, or rows, updated, and a few of the tables, or rows, without the updates. ALTER TABLE operations have the potential to lose entire tables if an error were to occur during the operation.
Note that FairCom offers a full ACID-compliant transaction processing layer with its FairCom DB SQL database server technology.
Backups
FairCom DB Standalone SQL Service does not provide quiesce functionality to help during backups because the FPUTFGET standalone model does not support transaction control. FairCom DB Standalone SQL Service cannot provide these extended features because it is not providing a central point of control.
Simply copying files can work in this model, provided users are not actively working against them. Note that copying files while they are being updated can result in "corrupt" opens when restored. Therefore, it is important to ensure all applications are not active against the data when doing backups using the FPUTFGET standalone model.
VSS_WRITER is not recommended for backups of FairCom DB Standalone SQL Service (or any FPUTFGET standalone applications) because it expects a single point of control for all files, which is not provided in this model.
Paths
The path to the system table is crucial, as it is a physical path requirement from the import utility.
FairCom DB Standalone SQL Service will need to access the system table and other FairCom system files. For best performance, install FairCom DB Standalone SQL Service on the same machine as these files. If it is installed on another machine, it will need a network mount to access them.
Permissions
Tables require permissions for access by ODBC. Two types of permissions are required: OS file system permissions and SQL permissions.
OS File System Permissions:
The FairCom DB Standalone SQL Service will need to have Read-Only or Read/Write to the data, index, and system files shared by your Multi-User Standalone users.
When you select an ODBC data source, you may have the option of selecting Read-Only instead of Read/Write. The Read-Only option will keep you from inadvertently altering data in your database and is our best practice recommendation.
SQL Permissions:
ODBC access requires that tables have been granted SQL permissions using the SQL GRANT command.
ODBC Registry Entry
ODBC driver information is stored in subkeys of the Windows registry.
User data sources are stored under the HKEY_CURRENT_USER key and are available only to the current user. System data sources are stored under the HKEY_LOCAL_MACHINE key and can be used by more than one user.
For user data sources, the complete registry key is HKEY_CURRENT_USER\Software\ODBC\ODBC.INI.
The ODBCINST.INI subkey is a subkey of HKEY_LOCAL_MACHINE, which describes the number and types of ODBC drivers installed on the system. The ODBC Data Source Administrator reads the subkey when it invokes the driver’s setup DLL to add or modify a data source for the driver.
ODBC Drivers - 32- vs. 64-Bit
ODBC uses separate drivers for 32-bit and 64-bit applications.
If you are installing the FairCom DB ODBC Driver on a 64-bit system, you may need both drivers. Match the number of bits supported by your ODBC-compliant application with the appropriate version of the FairCom DB ODBC Driver. For example, most third-party applications are still 32-bit and therefore will require the 32-bit driver even though they are running on a 64-bit operating system.
Architecture Mismatch
In trying to connect to the FairCom ODBC data source, you may see an error message along these lines:
Microsoft Data Link Error
The specified DSN contains an architecture mismatch between the driver and the application.
This generally indicates the ODBC application in use does not match the number of bits supported by the ODBC Driver that is loaded. For example, the application is a 32-bit application, and the ODBC Driver is a 64-bit driver. To resolve this error, ensure the number of bits match between the ODBC Driver and the application.
Rebuilding
Should the system table (superfile) become corrupted, the ctscmp (or ctsbld) utility can be used to rebuild that table.
TCP/IP Port
The FairCom DB Standalone SQL Service uses the standard FairCom SQL port: 6597
This port must be open on your firewall.
Locking Issues
The FairCom DB Standalone SQL Service uses the same Windows-based locking facilities as the FairCom DB multi-user non-server programs. For details, see the "Standalone Multi-User" chapter in the FairCom DB Programmer’s Reference Guide.
Starting and Stopping the Service
An entry in the Windows Service menu called "FairCom-SQL" was created during installation. The Windows Service Console can be used to stop and start this service in the same manner as any other Windows service.
Rebuilding Corrupt Files
To rebuild any data/index file, use the same approach you use today: Call a standalone rebuild function on the table. The ctrbldif utility (standalone version) is appropriate in this regard.
Adding and Maintaining Users
ODBC and other SQL users will need to authenticate to the FairCom DB Standalone SQL Service. The FairCom DB Standalone SQL Service includes extensive SQL access controls which can be unique to each user including SELECT INSERT, UPDATE, and DELETE access at the column level. FairCom DB Standalone SQL Service for standalone provides an assortment of utilities to manage SQL user accounts. This information is maintained in the FAIRCOM.FCS file under the service process working area and should be regularly backed up and protected from external access.
Covered in this section:
- FairCom DB utilities: ISQL, ctadmn, ctpass, etc.
- Default ADMIN "super user" account
- Adding users
- Username/Password limits and restrictions
- The User's password
FairCom DB Utilities
The following utilities are useful for administration as well as maintaining users and passwords:
isql.exe - The FairCom DB SQL Interactive SQL utility (ISQL) provides an industry-standard "command processing" interface to the FairCom DB SQL Database Engine. (Installed by the FairCom DB Standalone SQL Service installation program).
ctadmn - Administrator Utility. (Installed by the FairCom DB Standalone SQL Service installation program)
ctpass - Utility to allow users to change their password.
ctcpvf - Master password verification file utility. (Installed by the FairCom DB Standalone SQL Service installation program)
ctencrypt - Utility to change the master password. (Installed by the FairCom DB Standalone SQL Service installation program)
sa_admin - Scriptable command-line utility for performing security administration.
c-treeSecurityAdmin - GUI tool for performing security administration.
For more information on these utilities, see the Command-Line Tools book.
Default ADMIN Account
When it is initially installed, FairCom DB recognizes only one user: The Administrator. This "super user" has the unchangeable User ID name of ADMIN and the initial password of ADMIN. Administrator functions can be run by anyone with knowledge of the Administrator User ID and password.
The first thing to do is to change the initial password and keep the new password secure.
Adding Users
The Administrator, or "super user", is the only user with a preset, and unchangeable, User ID (ADMIN). The Administrator uses the ADMIN User ID and their private password to obtain exclusive access to the Administrator utilities needed to carry out the responsibilities discussed in this section.
The Administrator can create new User IDs (and passwords) for other users, who then logon with these names. This includes new members to the ADMIN group with limited Administrator capabilities.
Username/Password Limits and Restrictions
User IDs can be up to 31 characters long. Characters can be letters, numbers, or punctuation marks. User IDs are not case sensitive (i.e., upper and lower case characters are treated as the same).
User passwords can be up to 63 characters. Characters can be letters, numbers, or punctuation marks. Passwords are case sensitive (i.e., upper case and lower case characters are treated as different).
Note: Users, including ADMIN, can use the ctpass utility to change their own password. Members of the ADMIN group can use the FairCom DB Server Administrator Utility to change the password for a User ID that is not a member of the ADMIN group; only the super ADMIN account (named ADMIN) can change a password for an account that is a member of the ADMIN group.
The User's Password
The following steps are required for a user to change the password associated with their own User ID:
- Run the utility program ctpass as any other program in the environment.
- Enter your current User ID.
- Enter the current password for your User ID, if you have one. (Maximum 63 characters. Maximum nine characters for V9 and prior).
- Continue by entering the current name of the FairCom Server (i.e., the default name or another name, supplied in the FairCom Server configuration file).
- Now change your password by entering the new password.
- To be sure to enter the new password, you may be asked to enter it twice before it will be accepted. If the same name is not entered both times, try again.
Note: Whenever input is requested, the user may enter a question mark (?) to receive HELP.
After the new password is entered and confirmed, a message saying your User ID password has been successfully updated will be displayed. After being updated successfully, the new password must be used with the User ID to log on to the FairCom Server.
Note: All users can change their own passwords. In addition, users who are members of the ADMIN group can change the password of all accounts that are not members of the ADMIN group. Only the super ADMIN account (named ADMIN) can change a password for an account that is a member of the ADMIN group.
Product Layout and Utilities
FairCom DB Standalone SQL Service contains the following files and folders:
-
FairCom-DB.StandAloneSQL* - All of your FairCom files are located in this root folder.
- sql_service - The FairCom DB Standalone SQL Service executable, faircom_sql_service.exe, and dynamic libraries are in this folder.
faircom_sql_service.exe – This is the FairCom DB Standalone SQL Service executable.
ctreedbs.dll, ctreestd.dll, CTSRES.DLL, ctsrmc.dll – These are dynamic libraries used by the FairCom DB Standalone SQL Service executable.
The sql_service folder also contains the classes folder (providing Java classes) and the data folder (with ctreeSQL.dbs and SQL_SYS nested in it), which hold the data and system files used by FairCom DB Standalone SQL Service.
-
data - This is where we recommend you storing your application data. This folder also contains ctreeSQL.dbs and SQL_SYS nested in it, which hold the data and system files used by FairCom DB Standalone SQL Service. Note that you can change the data folder location by using the LOCAL_DIRECTORY keyword in ctsrvr.cfg and you can change the default name of the Database Folder by using the SQL_DATABASE keyword
- config - Contains ctsrvr.cfg. This is the FairCom DB Standalone SQL Service configuration file.
- Tools - This folder contains useful command-line tools. Any of these tools can be distributed; it is up to the developer to choose which files to distribute.
You can find documentation for these tools in the Command-Line Tools book.
The command-line tools are contained in two folders: client and standalone
FairCom DB Standalone SQL Service Production License Agreement
The use of this software is governed by a license agreement. This agreement sets forth the terms that govern the development use of this software.
This agreement sets forth the terms that govern development use of this software. It does NOT address the terms under which you may deploy the applications created with the software. Deployment is only authorized under the terms of a separate, signed deployment amendment.
Click here to see the license: