Helpful Examples

FairCom DB SQL - Microsoft SQL Server Integration

1. Start FairCom DB SQL as a Windows service. If both FairCom DB SQL and SQL Server are on the same machine, they will use a shared memory protocol. Since Windows Vista, both Microsoft SQL Server and FairCom DB SQL must be started as Windows services to establish a Named Pipe connection.
2. Install and set up an ODBC “System Data Source” on the machine running the MS SQL Server. The “User Data Source” type is not applicable for a linked database.
3. Create the “account” table in the FairCom DB SQL database.

create table "admin"."account" (
	"id" integer not null,
	"person_id" integer,
	"balance" float (8),
	"obs" varchar (128), 
primary key ("id")
);
insert into "admin"."account" values('1','1','99.23','None');
insert into "admin"."account" values('2','2','12.11',NULL);
insert into "admin"."account" values('3','1','73.34','Secondary');
insert into "admin"."account" values('4','3','155.84','Primary');
insert into "admin"."account" values('5','3','12.19',NULL);
insert into "admin"."account" values('6','4','0.18','None');
commit work;

4. Set up FairCom DB SQL as a Linked Server in Microsoft SQL Server. Using the Microsoft SQL Server Management Studio (SSMS), execute the following steps.
   1. In the “Object Explorer”, right click on “Server Objects / Linked Servers” and select the “New Linked Server” option.
   2. Enter a “Linked Server name”, select the “OLE DB Provider for ODBC drivers” as the provider, “product name” and the “System Data Source” name created in item 2.
      
   3. Click the “Security” option and add a map to the remote (FairCom DB) authentication. After clicking “Add”, select the authentication option on the “Local Login” and enter your FairCom DB SQL User ID and Password in the “Remote User” and “Remote Password” boxes.
      
   4. Click the “Server Options” page, enable “RPC” and “RPC Out” options, and confirm.
   5. Right click on the “CTREESQL” linked server and select the “Test Connection” option.
   6. Check that the “account” FairCom DB SQL table is present in the linked server.
5. Query a FairCom DB SQL table in SSMS. Execute the following query in SSMS.

select * from OPENQUERY(CTREESQL, 'select * from account where id > 2')
select * from CTREESQL..admin.account where id > 2

6. Create Microsoft SQL data. Execute the following commands.

CREATE TABLE [dbo].[person](
	[id] [int] NOT NULL,
	[name] [char](32) NOT NULL,
 CONSTRAINT [PK_person] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
) ON [PRIMARY]
GO
insert into person values(1, 'Mary')
insert into person values(2, 'Rick')
insert into person values(3, 'Jack')
insert into person values(4, 'Julia')

7. Execute a join between Microsoft SQL and FairCom DB SQL tables with the following query.

select p.name, a.id, a.balance, a.obs
from person p, OPENQUERY(CTREESQL, 'select * from account') a
where a.person_id = p.id
order by p.name
select p.name, a.id, a.balance, a.obs
from person p, CTREESQL..admin.account a
where a.person_id = p.id
order by p.name

8. Create a view with a table in Microsoft SQL Server and another in FairCom DB SQL with the following commands.

CREATE VIEW [dbo].[account_view]
AS
SELECT     p.name, a.id, a.balance, a.obs
FROM       dbo.person AS p INNER JOIN
OPENQUERY(CTREESQL, 'select * from account') AS a ON p.id = a.person_id
The view can be executed as any ordinary SQL Server statement:
select * from account_view where name = 'Mary'

9. Create a FairCom DB SQL table in SSMS. Execute the following commands.

exec ('create table "admin"."holiday" (
   "id" integer not null,
	"description" varchar (32),
	"hol_month" integer not null,
	"hol_day" integer not null,
primary key ("id")
)') at CTREESQL
GO
exec ('insert into "admin"."holiday" values (1, ''Christmas'', 12, 25)') at CTREESQL
GO
exec ('insert into "admin"."holiday" values (2, ''New Year'', 1, 1)') at CTREESQL
GO
select * from OPENQUERY(CTREESQL, 'select * from holiday')

10. Create a similar table in Microsoft SQL Server. Execute the following commands.

CREATE TABLE [dbo].[holiday](
	[id] [int] NOT NULL,
	[description] [varchar](32) NULL,
	[hol_month] [int] NOT NULL,
	[hol_day] [int] NOT NULL,
 CONSTRAINT [PK_holiday] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
) ON [PRIMARY]
GO
insert into holiday values(1, 'Christmas', 12, 25)
GO
insert into holiday values(2, 'New Year', 1, 2)
GO
select * from holiday

11. Create queue table for “holiday” in Microsoft SQL Server. This table will store the modifications to be replicated to the “linked server”. Execute the following commands.

select * into holiday_queue from holiday where 1 = 2
GO
alter table holiday_queue add action char(1)
GO
alter table holiday_queue add prev_id integer
GO

12. Create triggers for “holiday” in Microsoft SQL Server. To create triggers for Insert, Update and Delete operations to populate the “holiday_queue” table, execute the following commands.

CREATE TRIGGER holidayINS ON holiday
AFTER INSERT
AS
INSERT holiday_queue
SELECT *, 'I', NULL
FROM   inserted
GO
CREATE TRIGGER holidayDEL ON holiday
AFTER DELETE
AS
INSERT holiday_queue
SELECT *, 'D', id
FROM   deleted
GO
CREATE TRIGGER holidayUPD ON holiday
AFTER UPDATE
AS
INSERT holiday_queue
SELECT *, 'U', (select id from deleted)
FROM   inserted
GO

13. Create a Stored Procedure to sync “linked server” table. To create stored procedures that reads the “holiday_queue” rows and execute the actions in the “linked server” table, execute the following commands.

-----------------------------------------------------
-- This stored procedure retrieves the actions queued
-- and "replicates" the modifications in the linked
-- server
-----------------------------------------------------
CREATE PROCEDURE usp_sync_linkedsrv
AS
DECLARE @err_message nvarchar(255)
-------------------------
-- holiday replication --
-------------------------
DECLARE @id int
DECLARE @description varchar(32)
DECLARE @hol_month int
DECLARE @hol_day int
DECLARE @action char(1)
DECLARE @previd int
-- declare cursor for reading all the holiday events
DECLARE holiday_queue_cursor CURSOR FOR
	SELECT * FROM holiday_queue
	FOR UPDAT
	
-- open cursor
OPEN holiday_queue_cursor
-- retrieve the data from the cursor
FETCH FROM holiday_queue_cursor 
      INTO @id, @description, @hol_month, @hol_day, @action, @previd
WHILE @@FETCH_STATUS = 0
BEGIN
	IF @action = 'I'
	
-- process the INSERT event	
		INSERT CTREESQL..admin.holiday (
			id, 
			description, 
			hol_month, 
			hol_day )
		VALUES (
			@id, 
			@description, 
			@hol_month, 
			@hol_day )
			
	ELSE
	BEGIN
		IF @action = 'U'
		
-- process the UPDATE event	
			UPDATE CTREESQL..admin.holiday 
			SET 	id = @id, 
				description = @description, 
				hol_month = @hol_month, 
				hol_day = @hol_day
			WHERE	id = @previd
			
		ELSE
		BEGIN
		
-- process the DELETE event	
			IF @action = 'D'
				DELETE CTREESQL..admin.holiday 
				WHERE	id = @previd
				
			ELSE
			BEGIN
			
				SET @err_message = 'Invalid action: ' + @action
				RAISERROR (@err_message,10, 1)

			END
		END
	END
-- remove the current event from the queue
	DELETE FROM holiday_queue WHERE CURRENT OF holiday_queue_cursor
-- retrieve the next event
	FETCH NEXT FROM holiday_queue_cursor 
	      INTO @id, @description, @hol_month, @hol_day, @action, @previd
END
-- close cursor
CLOSE holiday_queue_cursor
-- deallocate cursor
DEALLOCATE holiday_queue_cursor
GO

14. Create a Job to execute the linked server table sync. To create and schedule a Job for calling the “usp_sync_linkedsrv” stored procedure created in the previous item to replicate the table changes from the Microsoft SQL Server to FairCom DB SQL every 10 seconds, execute the following commands.

exec msdb.dbo.sp_add_job 
	   @job_name = 'CTREESQL replication',
	   @enabled=1
GO
exec msdb.dbo.sp_add_jobstep 
       @job_name = 'CTREESQL replication',
       @step_name = 'Check for changes to be replicated',
       @subsystem = 'TSQL',
       @command = 'exec dbo.usp_sync_linkedsrv',
       @database_name = 'ctreeTest'
GO
exec msdb.dbo.sp_add_schedule 
       @schedule_name = 'CTREESQL replication schedule',
       @enabled = 1,
       @freq_interval = 1,
       @freq_type = 4,
       @freq_subday_type = 2,
       @freq_subday_interval = 10
GO
exec msdb.dbo.sp_attach_schedule
       @job_name = 'CTREESQL replication',
       @schedule_name = 'CTREESQL replication schedule'
GO
exec msdb.dbo.sp_add_jobserver 
       @job_name = 'CTREESQL replication',
       @server_name = 'ENRICO-PC'
GO

Connecting to Microsoft 64-bit SQL Server 2005 using a 32 bit ODBC driver

• First configure your 32-bit driver as described in this article:
  Configuring 32-bit ODBC Drivers on 64-bit Windows Versions
• Using the SQL Server Import and Export Wizard, you will get a pop-up screen that is asking for a Data Source.
• You will not see the 32-bit DSN in the drop down menu. Select ".Net Framework Data Provider for ODBC". On most systems it's probably the top one in the list, however, you need to scroll up to see it as the list usually displays in the middle.
• Enter further connection information on the resulting dialog screens.
• Under NamedConnection String there is a field for "dsn". In this field put the name of the DSN created in Step 1.
• Click on Next and follow the rest of the instructions to copy the desired data.

ctKEEPOPEN File Mode to Retain Cached Server Data

The final close of a c-tree file causes all existing file contents (data records or index nodes) to be flushed from cache to disk. A final close refers to a file close that causes the user count of the file to drop to zero. For applications opening and closing files during operation, it is often times advantageous for a file to remain cached for best performance. Many applications simply open a file at startup within a "private" c-tree connection and then close the file at application end.

It is possible to have the file's data persist in cache even after a final close by including ctKEEPOPEN bit in the splval member of the data file’s XCREblk. In this case, the final close leaves the file open (without any user attached to the file) and any cached data will be retained. When the file is again reopened, the data is immediately available for use saving expensive I/O time reloading data and index cache pages. This frees the application from having to maintain any state knowledge of the file.

The file is always closed when the server terminates protecting any data in cache. To force a ctKEEPOPEN file close, call the CloseCtFileByName() c-tree API function:

CloseCtFileByName(pTEXT filnam, pTEXT fileword)

It is possible to use the ctKEEPOPEN flag on the create, and then close and reopen the file shared, but only if the file’s creation is not pending commit. In this case, the sequence would be like:

XCREblk xcreblk[] = {
      {ctMEMFILE, 0, 0, 104857600, 0,0,0,0,0,0, ctKEEPOPEN, 0,0,0,0,0},
      {ctMEMFILE, 0, 0, 104857600, 0,0,0,0,0,0, ctKEEPOPEN, 0,0,0,0,0}
   };

   if (CreateIFileXtd8(&vcustomer,NULL,NULL,0,NULL,NULL,xcreblk))
   {
      ctrt_printf("\nCould not create file %d with error %d.\n",
                  isam_fil,isam_err);
   }
   else
   {
      CloseIFile(&vcustomer);
      if ((eRet = OpenIFileXtd(&vcustomer,NULL,NULL,NULL)) != 0)
      {
         ctrt_printf("\nUNEXPECTED ERROR %d ON REOPEN.\n",eRet);
      }
      else
      {
         ctrt_printf("\nFile created.");
      }
    }

Notification Example

ctntfy reads input file ctntfy.in, which contains a list of servers and files to monitor.

Below is an example of an input file showing three servers and two files to monitor and their corresponding target files to update.

;Server id		Server name
1				FAIRCOM1@localhost
2				FAIRCOM2@localhost
3				FAIRCOM3@localhost
;Source server id	Source file	Actions	Target server id	Target file
1			vcusti			ADU		2		vcusti
1			custmaster.dat	ADU		2		custmaster.dat
1			vcusti			ADU		3		vcusti
1			custmaster.dat	ADU		3		custmaster.dat
2			vcusti			ADU		1		vcusti
2			custmaster.dat	ADU		1		custmaster.dat
2			vcusti			ADU		3		vcusti
2			custmaster.dat	ADU		3		custmaster.dat
3			vcusti			ADU		1		vcusti
3			custmaster.dat	ADU		1		custmaster.dat
3			vcusti			ADU		2		vcusti
3			custmaster.dat	ADU		2		custmaster.dat

ctntfy creates a thread for each entry in the list of files to monitor. Each thread connects to the source and target server and opens the source and target files, then enables monitoring of the source file and reads queue entries as they arrive and processes them. The add processing has been verified to work with non-TRANPROC and TRANPROC files.

This example doe not hand delete and update requests as changes to the queue information must be provided for the utility to be able to find the record to delete or update in the target file. A suggestion for how this can be accomplished is the following: if a key that does not allow duplicates is available, include with a delete or update queue entry the key number and key value that can be used to find the record to delete or update in the target table.

Keep a CTUSER Library Open

CT_USER can execute whatever user code is placed into it. Often an input parameter is used as a command string indicating what action to take. To allow the library to remain open for performance reasons, simply add the following two command string options:

1. ctuserload: When CT_USER is called with this option, call dlopen() on your shared library. Save the handle (probably a global variable) such that you can unload the library later.
2. ctuserunload: when CT_USER is called with this option, call dlclose() on your shared library (using the handle you saved when the ctuserload option was specified).

This has the effect of keeping the CT_USER shared library loaded because the system maintains a reference count and so the dlclose() call only unloads a shared library when the number of closes matches the number of opens.

Utility to Search Logs for Open Transactions

FairCom developers have encountered situations where it is necessary to determine the existence of open transactions pending with FairCom DB. On such situation is cases where a single open transaction has resulted in the number of transaction logs to accumulate beyond the capacity of the I/O system, causing a FairCom DB failure.

Diagnosing recent FairCom DB replication enhancements is another situation. FairCom DB replication facilities are transaction log based. That is, contents of the transaction logs can be read and applied to a remote server through the FairCom DB Replication SDK. During development of a replication enabled application, it can prove useful to determine which open transactions are pending.

The FairCom DB Open Transaction Search utility, ctodmp, was created to search and identify open transactions pending in c-tree transaction logs.

ctodmp is very similar to ctldmp except that instead of dumping the contents of transaction logs, ctodmp searches for unmatched transaction begin and end entries. Specifically, ctodmp looks for TRANBEG, TRANEND and TRANABT entries as well as searching checkpoints for open (and/or vulnerable) transactions. ctodmp provides a list unmatched entries. These unmatched entries are specified by transaction number, location in the log files, and user thread handle, and are classified as:

• BEG - found TRANBEG, but no TRANEND or TRANABT
• END - found TRANEND, but no TRANBEG
• ABT - found TRANABT, but no TRANBEG
• CHK - found transaction in checkpoint, but no subsequent TRANEND or TRANABT. This is ambiguous, since the transaction may have already committed, but still be awaiting related buffer flushes or a TRANABT is not part of the log set scanned by ctodmp. The user thread handle is for the checkpoint thread, not the actual user.

FairCom DB OEM Installation Notes

FairCom Database Engine

The FairCom Database Engine is designed for ease of deployment and administration. In many cases, a deployment can simply copy the server executable and configuration, start the server process and walk away for unattended operation. Described below are the specific files and options available for the deployment process to successfully install the FairCom database engine as part of an OEM total package deployment.

Required Executable Files to Copy

The required FairCom server files will be found in the \server directory. The files listed below are the only required files for a FairCom database engine installation. These can be placed together in any single directory with appropriate permissions.

• faircom.exe
• ctreedbs.dll
• CTSRES.DLL
• ctsrmc.dll
• Ctree.SqlSP.dll (required for SQL .NET Stored Procedures)
• ctsrvrxxxxxxxx.lic - License file

FairCom Configuration File

A FairCom configuration file provides directives for various server options. This file typically resides in the same directory as the server executable, however, it may be located elsewhere and pointed to by either a command line option when starting the server, or an environment variable (refer to the FairCom Database Engine Server Administrator's Guide for details).

• ctsrvr.cfg

FairCom Administrator Utilities

The following executables are provided for the FairCom administrator. These can be located in a central secured location to protect the integrity of the FairCom installation.

• ctcpvf.exe - generates the master server password for encryption.
• ctcfgset.exe - creates an encrypted server configuration file

Many other administrator utilities are available, including statistics monitoring, administrator utility, and backup and restore utilities. These are located in the \tools directory of the FairCom installation. The suite of GUI based utilities is also available for distribution.

FairCom Java Environment

FairCom SQL Stored Procedures and Triggers require a functioning Java environment. This is not supplied with the FairCom installation and should be obtained and installed independently. Java 6 (1.6) is the recommended Java framework. Three (3) server configuration environment settings specified in ctsrvr.cfg enable these features by providing the paths for the required Java components.

• SETENV CLASSPATH=<directory path>\ctreeSQLSP.jar
• SETENV JVM_LIB=<directory path>\jre\bin\server\jvm.dll
• SETENV JAVA_COMPILER=<directory path>\bin\javac.exe

To create stored procedures requires the full Java SDK with the javac.exe compiler. To execute stored procedures and triggers requires only the Java run-time engine (JRE).

The ctreeSQLSP.jar file is required for creating or executing Java stored procedures and triggers and will be found in the \bin\ace\sql\classes directory of the default FairCom DB installation. This file can be installed wherever appropriate for your installation and is referenced with the above configuration.

FairCom as a Windows Service

There are several options available to install the FairCom database engine as a Windows Service.

• Use the Windows Service Control manager utility, sc.exe, provided with your Windows installation. For example:

>sc <windows UNC> create "FairCom DB Database Engine" binPath= c:\FairCom\faircom.exe start= auto DisplayName= "FairCom DB Database Engine"

• Create a custom service with these details. The Description and Display strings can be modified to match your application requirements.

[HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\faircom.exe]

FairCom ADO.NET Data Provider

The FairCom DB ADO.NET Driver integrates directly into the Microsoft Visual Studio Development environments.

Files Installed:

• All files in /csharp.sql.ado.net/ado.net

Registry Keys

The following Windows registry keys are are required to integrate the FairCom SQL ADO.NET Data Provider into Visual Studio.

[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\14.0_Config\DataProviders\{0EBAAB6E-CA80-4b4a-8DDF-CBE6BF058C77}]

@=".NET Framework Data Provider for FairCom"

"Codebase"="C:\\Users\\Craig\\Desktop\\FairCom-DB.windows.64bit.v12.0.0.113.201103.ACE\\FairCom-DB.winX64.v12.0.0.113\\drivers\\csharp.sql.ado.net\\ctree.visualstudio.data.dll"

"Description"="Provider_Description, Ctree.VisualStudio.Data.Resources"

"DisplayName"="Provider_DisplayName, Ctree.VisualStudio.Data.Resources"

"InvariantName"="Ctree.Data.SqlClient"

"ShortDisplayName"="Provider_ShortDisplayName, Ctree.VisualStudio.Data.Resources"

"Technology"="{77AB9A9D-78B9-4ba7-91AC-873F5338F1D2}"

[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\14.0_Config\DataProviders\{0EBAAB6E-CA80-4b4a-8DDF-CBE6BF058C77}\SupportedObjects]

[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\14.0_Config\DataProviders\{0EBAAB6E-CA80-4b4a-8DDF-CBE6BF058C77}\SupportedObjects\DataConnectionProperties]

@="Ctree.VisualStudio.Data.FcDataConnectionProperties"

[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\14.0_Config\DataProviders\{0EBAAB6E-CA80-4b4a-8DDF-CBE6BF058C77}\SupportedObjects\DataConnectionSupport]

@="Ctree.VisualStudio.Data.FcDataConnectionSupport"

[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\14.0_Config\DataProviders\{0EBAAB6E-CA80-4b4a-8DDF-CBE6BF058C77}\SupportedObjects\DataConnectionUIControl]

@="Ctree.VisualStudio.Data.FcDataConnectionUIControl"

[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\14.0_Config\DataProviders\{0EBAAB6E-CA80-4b4a-8DDF-CBE6BF058C77}\SupportedObjects\DataObjectSupport]

[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\14.0_Config\DataProviders\{0EBAAB6E-CA80-4b4a-8DDF-CBE6BF058C77}\SupportedObjects\DataSourceInformation]

@="Ctree.VisualStudio.Data.FcDataSourceInformation"

[HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\14.0_Config\DataProviders\{0EBAAB6E-CA80-4b4a-8DDF-CBE6BF058C77}\SupportedObjects\DataViewSupport]

GAC (Global Assembly Cache)

Register the \drivers\charp.sql.ado.net\Ctree.Data.SqlClient into the .NET Framework GAC using the GACUTIL.EXE utility provided by Microsoft.

.NET Framework machine.config Update

Make the following additions to the machine.config .NET framework configuration file found in the following Windows directory:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\CONFIG

Add the following XML tags:

• Into <configuration><system.data><DbProviderFactories>
  • <add name="FairCom Data Provider" invariant="Ctree.Data.SqlClient" description=".Net Framework Data Provider for FairCom" type="Ctree.Data.SqlClient.CtreeSqlClientFactory, Ctree.Data.SqlClient, Version=N/A, Culture=neutral, PublicKeyToken=0ce73727dc1039a8" />

FairCom ODBC Driver

The FairCom ODBC Driver installer registers the driver into the Windows ODBC Administrator and create a data source. Below are specific registry entries required for installation of the driver:

Driver

[ HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI ]

"FairCom ODBC Driver"

• Driver: \drivers\sql.odbc\ctodbc9.dll
• Setup: \drivers\sql.odbc\ctsetup9.dll

Data Source

[ HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\FairCom ODBC Driver ]

"FairCom ODBC Driver"

• Driver: "<FairCom ODBC Driver" (references the driver registry entry above)
• Description: "FairCom ODBC Driver"
• Password: "ADMIN"
• User ID: "ADMIN"
• Database: "ctreeSQL"
• Host: "localhost"
• Service: "6597"

FairCom JDBC Driver

The FairCom DB SQL JDBC driver is a full type 4 (native Java) JDBC driver, and as such, is platform independent. The FairCom DB SQL JDBC Driver jar file is located in the following FairCom DB installation directory:

• \drivers\sql.jdbc\ctreeJDBC.jar

This file can be located anywhere in an application installation and is referenced solely with an appropriate Java CLASSPATH specification. For example:

• CLASSPATH=.<path to FairCom DB install>\drivers\sql.jdbc\ctreeJDBC.jar\ctreeJDBC.jar

The FairCom JDBC Driver file requires no changes to any other system component.

Linux systemd Scripts to Start and Stop FairCom DB

On Unix/Linux systems, a script can be used to start and stop the FairCom Server. The script can be used to integrate FairCom DB as a startup service in systems where SystemD is used to manage the startup service. Some of the most recent Linux distributions using SystemD include RedHat 7, CentOS 7, Fedora, and Raspbian.

If the script is placed in the correct directory (e.g., /lib/systemd/system on CentOS 7), the systemctl system tool can be used to manage FairCom DB as system service. For example, to enable the ctree.service at startup, use the following command:

systemctl enable ctree.service

Because the FairCom DB Unix/Linux packages are distributed as .tar.gz archives, we are not able to control where the files will be extracted, so the sample script shown below may need to be edited to match the paths used on your system. Similarly, the paths will need to be edited if you are using this on a FairCom RTG system.

The systemd tool must launch the service as root. A User= keyword may be used to start the service as the specified user. In the sample script shown below, uncomment #User=myuser line and change myuser to the desired user name if you want to start the service as a different user.

The ctstop-auto command works ONLY if the default credentials are set. If you have changed these credentials, you would need to modify the script to use an authorization file with the ctstop command. Create the authorization file with the ctcmdset utility and add ‑1 filename to the ctstop command (in the line that begins ExecStop) in the script below. Note: Avoid including a password directly in the script as this practice is against security policy in most production environments.

The sample below, called ctree.service, demonstrates how to implement such a script:

[Unit]
Description=ctree database
After=multi-user.target network.target

[Service]
#Uncomment the next line if you want to start the service as a different use than "root"
#User=myuser
Environment=LD_LIBRARY_PATH=/opt/FairCom/linux.v2.6.x86.32bit/bin/ace/sql
WorkingDirectory=/opt/FairCom/linux.v2.6.x86.32bit/bin/ace/sql
ExecStart=/opt/FairCom/linux.v2.6.x86.32bit/bin/ace/sql/ctreesql
ExecStop=/opt/FairCom/linux.v2.6.x86.32bit/tools/cmdline/admin/client/ctstop -auto
Restart=on-abort

[Install]
WantedBy=multi-user.target

Adding FairCom DB JDBC to a Third-Party JDBC Tool

The FairCom DB JDBC API can be used with third-party applications such as ReadyAPI application from SmartBear. The steps below explain how to install and configure the FairCom DB JDBC Driver to work with this application.

You will need to know how to load a new JDBC Driver within the ReadyAPI application, as explained on their website: https://support.smartbear.com/readyapi/docs/testing/data-driven/drivers/install/index.html

Steps 1 and 2 from the link shown above are accomplished by copying the ctreeJDBC.jar file to the ReadyAPI bin/ext directory, such as:

C:\Program Files\SmartBear\ReadyAPI-2.4.0\bin\ext

Make sure you have Administrator privileges to be able to copy there.

You can find the ctreeJDBC.jar file in your FairCom DB PRO package in this default location: \FairCom\v11.5.0\winX64\lib\sql.jdbc

Once you restart the ReadyAPI, you can configure the FairCom DB JDBC Driver as shown below:

This corresponds to the following FairCom DB JDBC connection string:

jdbc:ctree://<host>[:portnumber]/<dbname>[?param=value[&param=value]...]

The image below shows a select * from one of the FairCom DB sample tables: