June 30, 2010
Comprehensive c-treeACE SQL Database Management
Note: c-treeACE became FairCom DB in October 2020.
FairCom has always sought to offer the best in engineering flexibility with c‑tree, and c‑treeACE SQL continues that tradition. c‑treeACE SQL offers numerous options to handle nearly any data requirements. Application requirements vary widely and flexibility is important for the best performance and features. Here’s a brief overview of some of the unique ways to interact with c‑treeACE SQL and the array of options supporting nearly any data need.
Database Options
The c‑treeACE SQL database engine provides full SQL-92 functionality out of the box. You can quickly get started with numerous interfaces including ODBC, JDBC, ADO.NET, and FairCom’s unique multi-platform C-based API, Direct SQL. Databases often require extended features and here’s a list of common configurations that are encountered by FairCom’s support team.
- Connection Port
c‑treeACE SQL operates on an independent TCP/IP port from the ISAM Server. As such, there are two ports for c‑treeACE SQL: The ISAM port defined by the SERVER_NAME or SERVER_PORT options (default FAIRCOMS, 5597) and the SQL engine port. Use this option to specify the TCP/IP Port Number for the SQL engine for connecting with SQL interfaces. (Default 6597)
SQL_PORT XXXX
- Default date handling
c‑treeACE SQL can handle dates in a variety of formats. Change the default date handling of a c‑treeACE SQL database from US, to UK, or ISO. Supported options are US_DFLT_DATE, UK_DFLT_DATE, and ISO_DFLT_DATE.
SETENV TPE_DFLT_DATE=UK_DFLT_DATE
- Case sensitivity
Case sensitivity of database searches can be modified at the database level. Use this option if your application is not case sensitive.
Note: This option must be in effect when the database is created as it becomes a permanent part of the database definition.
SQL_OPTION DB_CASE_INSENSITIVE
- Owner File Names
Allow multiple users to create tables with the same name. This option appends the owner name to the physical table name.
SQL_OPTION OWNER_FILE_NAME
- Partial Index Segments
Allow the use of imported c‑tree indexes containing partial segments in keys. Be sure to view the previous article regarding certain limitations with this feature.
SQL_OPTION PARTIAL_SEG
These options are placed in the ctsrvr.cfg configuration file. You will need to restart your server for changes to take effect. Also, certain options are part of the database definition and are only enabled at database creation time. Thus, you will need to re-create any databases for those options as indicated above.
SQL Explorer Tool
The easiest way to interact with c‑treeACE SQL is the graphical SQL Explorer Tool. This Windows based utility can connect to any c‑treeACE SQL server regardless of the platform or environment making for easy central management.
Command Line Tools
Database management is also possible using a command line utility. The c‑treeACE SQL change database utility, ctsqlcdb, allows you to add, create or drop databases. You can even copy (rename) a database. This copy option is useful for backup purposes.
ctsqlcdb <command> <dbname> [<servername>]
where <command> is:
- –add – Add an existing database to a c‑treeACE SQL Server. This updates the session dictionary, ctdbdict.fsd. If you delete this file for any reason, you will need to add your databases back to the server.
- -create – Creates a new c‑treeACE SQL database. This will create a new directory <dbname>.dbs. Tables and indexes associated with this database are created in this directory. The system tables for the database are located in the \SQL_SYS\<dbname>.fdd file.
- -drop – Drops a database from the c‑treeACE SQL Server, removing the entry from the ctdbdict.fsd session dictionary. Note that dropping a database does not delete the database and it can be added to the server (or another server) later.
- -copy – Copies a database to a newly named database and adds it to the ctdbdict.fsd session dictionary. This makes no changes to the original database.
Built In Stored Procedures
Numerous built in stored procedures make many administrative tasks quick and simple. Here is a listing of the most commonly used procedures. A full listing can be found in the c‑treeACE SQL Operations Guide.
- fc_get_dblist()
Returns a result set of 1 column (VARCHAR(1024)) listing all current database names.
- fc_create_db( dbname VARCHAR(1024) )
Creates a new c‑treeACE SQL database named dbname.
- fc_add_db( dbname VARCHAR(1024) )
Adds an existing c‑treeACE SQL database to the list of registered databases.
- fc_get_fcproclist()
Returns a result set of 1 column (VARCHAR(1024)) listing the available stored procedure names.
- fc_create_user( username VARCHAR() )
Creates a new c‑treeACE SQL user.
- fc_get_userlist()
Returns the current list of c‑treeACE SQL defined users.
- fc_set_debug()
Sets the debug level of the c‑treeACE SQL database engine for advanced diagnostics.