FairCom SQL for PHP - Quick Start Guide

Note: FairCom provides several ways for accessing the FairCom Database Engine from PHP:

  • A native PHP API for interfacing with PHP 5.x.x. It does not support later versions of PHP.
  • FairCom DB ODBC for interfacing with any version of PHP.
  • A PHP PDO (PHP Data Object) driver for supporting PHP 7 and later with the PDO extension. 
    This is the preferred way to access PHP. See FairCom DB PHP PDO ReadMe.

These are all discussed in the PHP Developer's Guide.

In this Book

This book contains the PHP developer tutorials for the FairCom DB PHP 5 API. It also includes a section on accessing PHP using ODBC.

In a few simple steps and about 15 minutes, you’ll be navigating your data.

  1. Install and start up the FairCom Database Engine. See Installing FairCom Products.
  2. Optionally read the overviewIntroduction to the FairCom DB PHP API.
  3. Run the tutorials:

Tip: To view, edit, and run the source code for the tutorials, you can go directly into the <faircom>\drivers\php.sql\tutorials folder that is installed with the product. Each tutorial has been thoroughly tested on each operating system, interpreter, and compiler to ensure you have an excellent experience.

Resources

c-tree Database API for Other Languages

In addition to its SQL APIs, FairCom provides navigational ("NAV") APIs for high performance. FairCom allows you to use NAV APIs with the language of your choice:

 

Introduction to the FairCom PHP API

PHP (Hypertext PreProcessor) is a widely-used scripting language that is especially suited for web development. FairCom’s FairCom DB PHP module allows you to access the powerful FairCom DB SQL database from your PHP projects.

The FairCom DB package provides two types FairCom DB PHP drivers, each in its own directory:

drivers\php.sql - This is the preferred way to access FairCom DB from PHP

  • These drivers support PHP 7 using the PDO (PHP Data Object) extension.
  • See FairCom DB PHP PDO for documentation.

drivers\php.sql - This is provided for PHP 5

  • These drivers support PHP 5.
  • Instructions are provided for using the FairCom DB ODBC interface to connect to later versions of PHP (PHP 4, PHP 5, and PHP 7).
  • See FairCom DB PHP for documentation.

Resources:

  • Using PHP 5 with the sql.php API - Using the native PHP API designed specifically for interfacing with PHP 5.x.x. It does not support later versions of PHP.
  • Using PHP with ODBC - An older method of accessing the c-tree database from PHP is via ODBC. This method should be considered deprecated.
  • Tutorials - Learn first-hand how easy it is to use c-treeACE PHP in your PHP 5 application.
  • c-treeACE PHP (http://docs.faircom.com/doc/php) - View the manual online for a list of c-treeACE PHP functions and programming techniques.
  • Administrator's Guide (https://docs.faircom.com/doc/ctserver/) - Learn about configuration, monitoring, backups, etc.

Tools

  • Database Integrity Utilities (https://docs.faircom.com/tutorials/tools) - Command-Line administrative tools
  • Web Browser-Based Tools (https://docs.faircom.com/doc/browser_tools/) - Conveniently run these graphical tools from your web browser

 

Using PHP 5 with the SQL PHP API

The SQL PHP 5 API supplied with FairCom DB is a native PHP API designed specifically for interfacing FairCom DB with PHP 5.x.x. It does not support later versions of PHP.

The SQL PHP API is provided for environments that cannot use the PDO extension/PHP 7. Note that the preferred method of interfacing FairCom DB with PHP is to use the PDO extension, which supports all recent versions of PHP. See Using PHP 7 with the PDO Extension.

If you want to interface PHP 5.x.x. with FairCom DB using the sql.php API, follow the procedures in the appropriate part of this chapter:

As with all other tutorials in the c-tree series, each of these database programming tutorials is implemented with four simple code procedures: Initialize(), Define(), Manage(), and Done().

No matter which FairCom interface language you use, FairCom follows this same high-level flow in all tutorials. This makes it easy for developers to "cross-over" from one language interface to another as these basic concepts apply to all.

Initialize()

Every language requires some form of initial "logon" or "connection" procedure to establish a session with the database. This is done in the Initialize() stage of the program.

Define()

Database definitions (DDL), Table/File schema definitions, Index definitions, Table/File creation, and Table/File open operations are all addressed in the Define() stage of the program.

Manage()

This stage of the program is where the database is operated on, as in managing your data. Adding/Reading/Updating/Deleting records/rows are handled in this stage of the program.

Done()

When the program ends, the database session should be closed. This stage handles the necessities to "de-init", by closing Tables/Files and issuing any required "logoff" or "disconnect" type procedures.

Presented here are tutorials that follow the "Initialize(), Define(), Manage(), and Done()" approach.

You can also view similar tutorials for all supported languages online

 

Using the SQL PHP 5 API and Microsoft IIS on Windows

To install and execute the SQL PHP tutorials with Microsoft IIS, you will need to set up the SQL PHP extension as described in the following sections:

 

Install Microsoft IIS

To install Internet Information Service (IIS) web server on Microsoft Windows, follow these instructions:

Note: This section shows typical procedures using Windows 7 as an example. Some of the dialog and labels may look different in other versions of Windows.

  1. Windows 7: Open the Windows Control Panel (Usually Start Menu > Control Panel) and select Programs:
  2. Windows 7: In the program menu, select Turn Windows features on and off:
  3. Windows 10: Open the Start Menu and press the Settings gear icon to bring up the “Windows Settings” window.
  4. Windows 10: In the search box, search for "Turn Windows features on or off" and select the application when it appears under the search box.
    The following steps are for Windows 7 and 10
  5. In the Windows Features window that appears, tick the box next to Internet Information Services.
  6. Expand Internet Information Services > World Wide Web Services > Application Development Features and check the box next to the CGI item:
  7. Click OK to begin the installation of IIS.
  8. Once the installation is complete, verify that IIS is functional by entering http://localhost into a browser address bar. A “Microsoft Internet Information Services” web page should be displayed.

 

Compile PHP and the SQL PHP 5 Extension (Windows)

To compile PHP, and to compile and install the SQL PHP 5 extension on Windows, follow the steps in this section.

These instructions assume you are using Microsoft Visual C++ 11.0 (Visual Studio 2012) and PHP 5.5 or 5.6. If you are using an earlier version of PHP, some of these instructions will have to be modified. Please refer to this webpage for details: https://wiki.php.net/internals/windows/stepbystepbuild

Step 1: Build PHP 5 and the SQL PHP extension

  1. Download the PHP 5.5 or 5.6 source code from https://www.php.net/downloads. For PHP version 5 files, you will have to click the “Old archives” link. Do not download the Windows ZIP version of the files, only the tar files contain the source code we need. The tar files are available in three different compression formats: bz2, gz, and xz. For example, if you chose the bz2 compression format, your file will have a name like like "php-5.6.40.tar.bz2". Be sure you choose version 5.5 or 5.6, making a note of which PHP option you chose.
  2. Download the PHP 5 Binary Tools from https://windows.php.net/downloads/php-sdk/. The archive files have names like “php-sdk-binary-tools-YYYYMMDD.zip”. Note that you must use a decompression software such as 7zip or WinZIP to unzip the files.
  3. Download the PHP 5 dependencies. Still at the website from step 2, click into the “archives” folder and download the archive that corresponds to the version of PHP you downloaded in step 1 (again, must be PHP 5.5 or 5.6). The archive files are named “deps-<php version>-<vc version>-<architecture>”. Be sure to select a bit depth that matches your version of the FairCom product you are using. If your FairCom product is 32-bits, choose the "x86" version. If your FairCom product is 64-bits, choose the "x64" version.
  4. Create the following directory: c:\php-sdk
  5. Extract the contents of the binary tools ZIP archive (from step 2) into this directory. You should end up with the following three directories:
    • C:\php-sdk\bin
    • C:\php-sdk\script
    • C:\php-sdk\share

 

  1. Open a Developer Command Prompt / Native Tools Command Prompt window for Microsoft Visual Studio 2012. Typically, this is available from the Start menu, following a path similar to "Microsoft Visual Studio 2012 > Open VS2012 x64 Native Tools Command Prompt". Warning - the Developer Command Prompt Start menu entries which do not specify a bit depth actually open the 32-bit version of the Developer Command Prompt. For more details, see the Microsoft Developer Command Prompt (https://docs.microsoft.com/en-us/cpp/build/building-on-the-command-line) web page.
  2. In the Developer Command Prompt window, move to the C:\php-sdk folder that you created in step 4 using the commands below.|
    C:
    cd \php-sdk
  3. Execute the following batch file, which will create some folders:
    bin\phpsdk_buildtree.bat phpdev
  4. Using Windows Explorer, navigate to the “C:\php-sdk\phpdev” folder that just got created in step 8, and make a copy of the “vc9” folder, and rename that copy to “vc11”.
  5. Open the PHP source code tar file you downloaded in step 1. This should show a single folder, (“php-5.6.40” in this example). Drag that PHP folder into the “C:\php-sdk\phpdev\vc11\x64” (or x86) folder (depending on your bit depth). This should result in that “vc11\x64” (or "vc11\x86") folder having two children - “deps” and a new folder with a name like “php-5.6.40”.

    This new PHP folder contains the source code of PHP 5.
  6. Open the dependencies file you downloaded in step 3. This should show a single “deps” folder. Extract it into the “C:\php-sdk\phpdev\vc11\x64” (or x86) folder.
  7. Back in the Developer Command Prompt window, Set up the build environment variables by running the following command:
    bin\phpsdk_setvars.bat
  8. Change directory to the location of your PHP source code, using something like the following.:
    cd phpdev\vc11\x64\php-5.6.40
    or
    cd phpdev\vc11\x86\php-5.6.40
  9. Run the following based on the FairCom product you are using (where <basedir> is the full path of your FairCom product c-treeACE installation directory):
    1. c-treeACE
buildconf --force --add-modules-dir=<basedir>\sdk\sql.php
c-treeRTG
buildconf --force --add-modules-dir=<basedir>\Driver\sql.php
c-treeEDGE
FairCom-DB
FairCom-EDGE
FairCom-RTG
	buildconf --force --add-modules-dir=<basedir>\drivers\php.sql
    2. You should get a message about adding the specified folder to the module search path.
  10. Enable the c-treeACE PHP extension and turn off Thread Safety by running the following command:
    configure --disable-all --enable-cli --enable-cgi --enable-object-out-dir=.. --disable-ipv6 --disable-zts --with-ctsql=shared,<basedir>
    Where <basedir> is the full path of your FairCom product c-treeACE or c-treeRTG installation directory.
    In the printout that follows, there should be no warnings or error messages, and it should end by telling you to “Type 'nmake' to build PHP”.
    You know this step worked correctly if you see something like the following in the output, which indicates the our PHP SQL extension was found and enabled:
    Enabling extension __________\drivers\php.sql\src
    The last box of configuration information that is printed should have “Thread Safety” shown as “No”. That is the purpose of the “--disable-zts” flag (“Zend Thread Safety”). If “Thread Safety” is still shown as “Yes”, then add the “--disable-maintainer-zts” flag as well. Using IIS + FastCGI, and this combination requires thread safety to be turned off.
  1. To build PHP, run the following, still in the Developer Command Prompt:
    nmake clean (in case you need to recompile)
    nmake
    It is likely that there will be many compiler warnings as PHP is built. There isn’t anything we can do about that.
    If everything goes well, it should end with a message about the build being complete.
  2. Still in the Developer Command Prompt, make sure a new “Release” folder has appeared in the “working folder”:
    dir ..
    The folder should be called “Release”. If there is no “Release” folder, but there is a “Release_TS” folder, it means that Thread Safety was not turned off in step 17. The “Release” folder should contain the “php.exe” application you just built in step 19. Now copy one of the INI file templates from the current folder into that Release folder, using the following command:
    copy php.ini-development ..\Release\php.ini
    Make a note of this “Release” folder’s full path, because you will need it in subsequent steps of these instructions, and you will need it in step 4 of the “Configure Microsoft IIS” instructions.
  3. Use a text editor to open the php.ini file you just created and search for the “extension_dir” lines..
  4. Un-comment one of them (by removing the leading semicolon) and then change the directory to be the Release folder that contains this INI file and “php.exe”…
  5. Find the section of the INI file that contains the commented-out list of extensions and find the line for “php_mysql.dll”
  6. Copy that line, remove the leading semicolon, and change two letters to make it “php_ctsql.dll”:
    Note that the examples shown are from PHP 5.6.40 and PHP 5.5.38 on Windows 10. The “extension” lines in other versions of PHP might look different. That is why we suggest making a copy of the existing php_mysql line and then editing the copy to refer to php_ctsql.
  7. Save your changes to the INI file.
  8. Copy the “ctsqlapi.dll” file from the appropriate FairCom product folder to the “Release” folder that contains the INI file you just edited and “php.exe”. You can find “ctsqlapi.dll” in the following folder, where <basedir> is the full path of your FairCom product installation directory:
    1. c-treeACE
<basedir>\bin\sql.direct\ 
c-treeRTG
<basedir>\bin\sql.direct\
c-treeEDGE
FairCom-DB
FairCom-EDGE
FairCom-RTG
<basedir>\drivers\ctree.drivers\lib\
  9. Back in the Developer Command Prompt, verify that PHP built correctly and the extension loads correctly by first changing into the Release folder and then running the PHP version command:
    1. cd ..\Release
php -v
    2. It should print some version information. There should be no warnings or error messages. If there is an error about being unable to load dynamic library 'php_ctsql', it means that something probably went wrong with steps 20-26..
  10. At this point, you can test the c-tree PHP SQL tutorials from the command line as follows:
c-treeACE
php -f <basedir>\sdk\sql.php\tutorials\PHP_Tutorial1.php
c-treeRTG
php -f <basedir>\Driver\sql.php\tutorials\PHP_Tutorial1.php
c-treeEDGE
FairCom-DB
FairCom-EDGE
FairCom-RTG
php -f <basedir>\drivers\php.sql\tutorials\PHP_Tutorial1.php

It should print the HTML source code of a web page. If an “ctsql_connect() - SQL ERROR: [-20212] - Error in Network Daemon” error is shown, it means the c-tree server is not running. Start the c-tree server and re-do this step. Here is what the correct output of this command should look like:

<html>
<head>
<title>PHP Tutorial 1</title>
</head>
<body>
<h4>INIT</h4>
Logon to server...<br>
<h4>DEFINE</h4>
Create table...<br>
<h4>MANAGE</h4>
Delete records...<br>
Add records...<br>
Display records...<br>
<TABLE border=1>
<TR><TH>Number</TH><TH>Name</TH></TR>
<TR><TD>1000</TD><TD>Bryan Williams</TD></TR>
<TR><TD>1001</TD><TD>Michael Jordan</TD></TR>
<TR><TD>1002</TD><TD>Joshua Brown</TD></TR>
<TR><TD>1003</TD><TD>Keyon Dooling</TD></TR>
</TABLE>
<h4>DONE</h4>
Logout...<br>
</body>
</html>

If you wish, you can repeat this step for each of the four PHP SQL tutorials.

Additional details about how to build PHP can be found at this URL: https://wiki.php.net/internals/windows/stepbystepbuild

Note that the instructions above do not follow that web page exactly.

 

Configure Microsoft IIS

To configure IIS for PHP, follow these steps:

Note: This section shows typical procedures using Windows 7 as an example. Some of the dialog and labels may look different in other versions of Windows.

  1. Open the Start Menu, select Run, and execute InetMgr.exe:
  2. In the InetMgr main window, double-click Handler Mappings:
  3. In the Handler Mappings window, click Add Module Mapping... on the right-side Actions menu:
  4. Complete the dialog window with the following:
    Request Path: *.php
    Module: FastCgiModule
    Executable: This needs to be filled in with the full path to the “php-cgi.exe” executable that is in the “Release” folder from step 20 of the previous section. An example path would be “C:\php-sdk\phpdev\vc11\x64\Release\php-cgi.exe”, but it will likely be different on your machine. 
    Name: PHP
  5. Click OK and then Yes on the "Add Script Map" message box that appears. Finally, close the InetMgr dialog.
  6. Restart your computer to ensure everything is configured.

Note: it is not mandatory to restart your computer at this time, however, it is advised to ensure IIS is properly configured. 

 

Run the SQL PHP Tutorials with Microsoft IIS

c-treeACE PHP SQL includes a set of tutorials intended for use with the PHP 5 extension.To install and execute the SQL PHP tutorials with Microsoft Internet Information Service (IIS), be sure to install IIS and PHP 5.x.x as described in the previous sections. Next, you will need to copy the tutorials files into the IIS root directory as shown in this section.

Configure the FairCom DB PHP Extension

  1. Copy the contents of the c-tree PHP SQL “tutorials” directory into the C:\inetpub\wwwroot directory. Note that this will require Administrator privileges. The files should come from the following location, where <basedir> is your c-tree installation folder:
    c-treeACE: <basedir>\sdk\sql.php\tutorials
    c-treeRTG: <basedir>\Driver\sql.php\tutorials
    c-treeEDGE, FairCom-DB, FairCom-EDGE, FairCom-RTG: <basedir>\drivers\php.sql\tutorials
  2. Open your favorite web browser and connect to http://localhost. You should see the index.htm page with hyperlinks to the three tutorials to be executed:
    Note that on some systems, you might have to use this URL instead: http://localhost/index.htm
  3. Click on the tutorial hyperlinks to execute the tutorials. The first tutorial looks like this:

Note that if it shows the following error, it usually means the FairCom Database Engine is not running on your machine. The most likely cause is that the c-tree evaluation license times out after 3 hours and shuts down the server. The solution is to restart the server. See the Setup section for how to do this.

Logon to server...

ctsql_connect() - SQL ERROR: [-20212] - Error in Network Daemon

 

Using the SQL PHP 5 API on Linux and Unix

To install and execute the SQL PHP tutorials with Apache on Unix/Linux systems, you will need to set up PHP 5.x.x as described in the following sections:

 

Configure PHP 5

PHP is included in many common Linux and Unix distributions. The latest version of PHP 5 can be downloaded from the PHP website.

To configure PHP 5 to run FairCom Edge PHP follow these steps:

Note: These instructions are not valid for CentOS versions 6, 7, and 8. If followed, they will render PHP non-operational on those systems. If you are installing on one of those CentOS versions, see Instructions for CentOS (page).

Before following these steps, you should have first compiled and installed the c-tree PHP extension (ctsql.so) under your version of PHP, (following these instructions: Compile PHP and the SQL PHP 5 Extension (Windows)).

  1. Open php.ini with your favorite text editor. In most installations you will find the server (SAPI) version of php.ini in /etc/php5/apache2/php.ini.
    Note that in Linux installations of PHP, there is often a separate php.ini file for SAPI (PHP served from a web server), and for CLI (PHP run from the Command Line). If you intend to test things from the command line (step 7, below), be sure to make the following changes to the CLI version of php.ini (usually /etc/php/cli/php.ini) as well as the SAPI version of php.ini.
  2. Search for extension_dir.
    • If the extension_dir line is commented out (; on the beginning of the line) leave it that way as the “make install” step during the build should have copied “ctsql.so” into the default extension folder.
    • If this line is not commented, it's better to copy ctsql.so into your default PHP extensions directory.
  3. Search for extensions area and add extension=ctsql.so to configure the FairCom DB SQL PHP module into your PHP:

    Note that your copy of php.ini might have the extensions end with “.so” and it might not, depending on the version of PHP you are using. For this reason, we recommend that you follow the pattern of the other extensions already listed in the file. For example, find the existing “mysql” extension line, copy it, remove the leading semicolon, and then change two letters to make it say “ctsql”
  4. Save changes to php.ini and close your text editor. If desired, change the other php.ini file as well.
  5. It is important the libctsqlapi.so file must be in one of the following folders:
    • Its original location when the PHP module was built
    • In a directory part of the library path
      The ctsql module you just built should already know the location of libctsqlapi.so, because the build added the libctsqlapi.so directory to the ctsql.so module library path. Note that later, in deployment situations, you might need to put libctsqlapi.so in a more generic place (IE: you may have built ctsql.so on a different machine)
  6. Make sure the previous steps worked by launching PHP, as follows:
    php -v
    No error messages or warnings should be printed.
  7. If you modified the CLI version of php.ini in steps 1-4, you can run the tutorials from the command line, as follows:
    php -f ../tutorials/PHP_Tutorial1.php
    If you wish, you can repeat this step for each of the four PHP tutorials.
    It should print the HTML source code of a web page. If an “ctsql_connect() - SQL ERROR: [-20212] - Error in Network Daemon” error is shown, it means the c-tree server is not running. Start the c-tree server and re-do this step.
    If an “PHP Fatal error: Call to undefined function ctsql_connect()...” error is shown, it probably means that you did not edit the CLI version of php.ini in steps 1-4.
    Testing from the command line allows you to see the error messages, even if PHP fails to launch (because it is misconfigured). Note that the tutorial files do not need to be copied to the web server's documents directory in order to do this test.
  8. From the root shell restart your web server with the following command:
    /etc/init.d/apache2 restart
    It should respond with: "Forcing reload of web server (apache2)... waiting ."

 

Instructions for CentOS

The instructions for installing and configuring PHP 5 on CentOS 6, 7, 8, are slightly different than the instructions for most flavors of Linux/Unix. Be sure to use these procedures if you are installing on one of these CentOS versions.

These instructions assume you are using a version of PHP 5 from remirepo (https://rpms.remirepo.net).

Note: on CentOS 8, PHP 7 is provided by the default repositories. If you prefer to have the very latest version of PHP, though, the Remi repository makes that available. 

Make sure your installed version of php-devel is exactly the same version as your PHP 5: First install the version of PHP 5 you desire using the following:

yum-config-manager --enable remi-php__ 
sudo yum install php.....

Then execute the following to install the files which are needed for building PHP extensions:

sudo yum install php-devel

  1. Edit file /etc/php.ini and add a single line: 
    extension=ctsql.so
    Do not change the “extension_dir” entry.
  2. Some builds of CentOS do NOT include the static version of libc, which is needed for building the ctsql.so library. If the following build instructions give a link error "cannot find -lstdc+", you need to install the static version of libc, as follows: 
    sudo yum install glibc-static libstdc+-static
  3. Build and install the c-tree PHP extension (ctsql.so) under your version of PHP, (following these instructions: Compile the PHP Extension (page )). Be sure it has the same permissions as the other .so files in that folder: /usr/lib64/php/modules/
  4. It is important the libctsqlapi.so file must be in one of the following folders:
    - Its original location when the PHP module was built
    - In a directory part of the library path
    The ctsql module you just built should already know the location of libctsqlapi.so, because the build added the libctsqlapi.so directory to the ctsql.so module library path. So, unless you have renamed or deleted the folder, you should be OK. Note that later, in deployment situations, you might need to put libctsqlapi.so in a more generic place (IE: you may have built ctsql.so on a different machine).
  5. Make sure the previous steps worked by launching PHP, as follows:
    php -v
    No error messages or warnings should be printed.
  6. At this point, you should run at least one of the tutorials from the command line, as follows:
    php -f ../tutorials/PHP_Tutorial1.php
    It should print the HTML source code of a web page. If an “ctsql_connect() - SQL ERROR: [-20212] - Error in Network Daemon” error is shown, it means the c-tree server is not running. Start the c-tree server and re-do this step. Here is what the correct output of this command should look like:
<html>
<head>
<title>PHP Tutorial 1</title>
</head>
<body>
<h4>INIT</h4>
Logon to server...<br>
<h4>DEFINE</h4>
Create table...<br>
<h4>MANAGE</h4>
Delete records...<br>
Add records...<br>
Display records...<br>
<TABLE border=1>
<TR><TH>Number</TH><TH>Name</TH></TR>
<TR><TD>1000</TD><TD>Bryan Williams</TD></TR>
<TR><TD>1001</TD><TD>Michael Jordan</TD></TR>
<TR><TD>1002</TD><TD>Joshua Brown</TD></TR>
<TR><TD>1003</TD><TD>Keyon Dooling</TD></TR>
</TABLE>
<h4>DONE</h4>
Logout...<br>
</body>
</html>

If you wish, you can repeat this step for each of the four PHP tutorials.
Testing from the command line allows you to see the error messages, even if PHP fails to launch (because it is misconfigured). Note that the tutorial files do not need
to be copied to the web server's documents directory in order to do this test.

  1. Restart your web server. For example, with Apache you would use the following command: 
    sudo systemctl restart httpd

 

Compile the SQL PHP 5 Extension (Linux/Unix)

The following procedures in this section are used to compile the c-tree PHP native driver:

Requirement:

  • PHP4 or PHP5 development package. This contains the files needed to build PHP extensions.
    To install on CentOS, use: yum install php-devel
    To install on Ubuntu, use: apt-get install php5-dev
  • Perl
  • Autoconf
  • Automake
  • GNU m4
  • C++ Compiler

Step 1: Create Configuration Script

Move to the drivers/php.sql/src directory under your FairCom product installation directory.

Run the phpize command to create the configuration script based on the contents of the "config.m4" file:

phpize

Step 2: Configure and Build

Run the configure script to create the makefile containing the instructions to build the FairCom DB PHP extension with the original PHP driver:

./configure

In the text that is printed, you should see the following line:

checking whether to enable c-tree SQL support... yes, shared

Build FairCom DB PHP extension using make. Note that if you change versions of PHP, you should do a make clean first, before doing make:

make

It should say “Build complete” when done. The extension will be created in the modules folder: drivers/php.sql/src/modules/ctsql.so

Step 3: Install the Extension

Install the FairCom DB PHP extension into your PHP installation extensions directory by running the following command:

sudo make install

If the folder extension was successfully installed, you should see the following:

Installing shared extensions:     /usr/lib/php5/20121212/

Be sure the ctsql.so file has the same permissions as the other .so files in the install folder.

Note: Make sure php.ini is configured to load extensions from the PHP installation extensions directory. This is described in the "configure PHP 5" section (above).

Note: If you have a configure script with installed Autotools, follow these steps:

>libtoolize

>aclocal

>autoreconf -i

>phpize

>./configure --with-ctsql

 

Run the SQL PHP Tutorials with Linux/Unix Apache

To execute the SQL PHP tutorials, copy the tutorials files into a directory accessible to your web server (here we use the web server root directory) as shown below:

  1. Open a root shell and move to /var/www/ and copy the contents of the php.sql/tutorials/ directory in your installation directory:
debian:~# cd /var/www/
debian:/var/www# cp -v <faircom>\drivers/php.sql/tutorials/* .

(You will need to adjust the path based on your installation directory.)

  1. Make sure FairCom DB SQL is running.
  2. Open your favorite web browser and connect to http://localhost/index.htm (make sure you are using a proper URL if you did not use the web server root directory). You should see the index.htm page with hyperlinks to the three tutorials to be executed:
  3. Click the tutorials hyperlinks to execute the tutorials:

 

Tutorials

As with all other tutorials in the c-tree series, each of these database programming tutorials is implemented with four simple code procedures: Initialize(), Define(), Manage(), and Done().

No matter which FairCom interface language you use, FairCom follows this same high-level flow in all tutorials. This makes it easy for developers to "cross-over" from one language interface to another as these basic concepts apply to all.

Initialize()

Every language requires some form of initial "logon" or "connection" procedure to establish a session with the database. This is done in the Initialize() stage of the program.

Define()

Database definitions (DDL), Table/File schema definitions, Index definitions, Table/File creation, and Table/File open operations are all addressed in the Define() stage of the program.

Manage()

This stage of the program is where the database is operated on, as in managing your data. Adding/Reading/Updating/Deleting records/rows are handled in this stage of the program.

Done()

When the program ends, the database session should be closed. This stage handles the necessities to "de-init", by closing Tables/Files and issuing any required "logoff" or "disconnect" type procedures.

Presented here are tutorials that follow the "Initialize(), Define(), Manage(), and Done()" approach.

You can also view similar tutorials for all supported languages online.

 

Introductory Tutorial

php.sql\tutorials\PHP_Tutorial1.php

This tutorial will take you through the basic use of the FairCom DB PHP Interface.

As with all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done() !

Tutorial #1: Introductory - Simple Single Table

We wanted to keep this program as simple as possible. This program does the following:

  • Initialize() - Connects to the FairCom Database Engine.
  • Define() - Defines and creates a "customer master" (custmast) table/file.
  • Manage() - Adds a few rows/records; Reads the rows/records back from the database; displays the column/field content; and then deletes the rows/records.
  • Done() - Disconnects from FairCom Database Engine.

Note our simple PHP script:

<?php
   print("<html>\n");
   print("<head>\n");
   print("\t<title>PHP Tutorial 1</title>\n");
   print("</head>\n");
   print("<body>\n");
   //
   // Implementation of the concept of "init, define, manage and you're done..."
   //
   $session = Initialize();
   Defines($session);
   Manage($session);
   Done($session);

We suggest opening the source code with your own editor.

Continue now to review these four steps.

 

Init

First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.

Below is the code for Initialize():

   //
   // Initialize()
   //
   // Perform the minimum requirement of logging onto the c-tree Server
   //
   function Initialize() {
      print("\t<h4>INIT</h4>\n");
      // connect to server
      print("\t\tLogon to server...<br>\n");
      $ses = ctsql_connect("localhost:ctreeSQL", "admin", "ADMIN");
      if (!$ses)
         Handle_Error("ctsql_connect()");
      return ($ses);
   }

 

Define

Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.

Below is the code for Define():

   //
   // Define()
   //
   // Create the table for containing a list of existing customers
   //
   function Defines($ses) {
      print("\t<h4>DEFINE</h4>\n");
      // create table
      print("\t\tCreate table...<br>\n");
      $qry = ctsql_query(
         "CREATE TABLE custmast ( 
            cm_custnumb CHAR(4), 
            cm_custzipc CHAR(9), 
            cm_custstat CHAR(2), 
            cm_custrtng CHAR(1), 
            cm_custname VARCHAR(47), 
            cm_custaddr VARCHAR(47), 
            cm_custcity VARCHAR(47))",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE TABLE)");
      if (!ctsql_commit($ses))
         Handle_Error("ctsql_commit()");
   }

 

Manage

Manage() provides data management functionality for your application and/or process.

Below is the code for Manage():

   //

   // Manage()

   //

   // This function performs simple record functions of add, delete and gets

   //

   function Manage($ses) {

      print("\t<h4>MANAGE</h4>\n");

      

      // delete any existing records

      Delete_Records($ses);

      // populate the table with data

      Add_Records($ses);

      // display contents of table

      Display_Records($ses);

   }

   //

   // Delete_Records()

   //

   // This function deletes all the records in the table

   //

   function Delete_Records ($ses) {

      print("\t\tDelete records...<br>\n");

      $qry = ctsql_query("DELETE FROM custmast", $ses);

      if (!$qry)

         if (100 == ctsql_errno())

            return;

         else

            Handle_Error("ctsql_query(DELETE)");

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Add_Records()

   //

   // This function adds records to a table in the database from an

   // array of strings

   //

   function Add_Records ($ses) {

      print("\t\tAdd records...<br>\n");

      $data = array(

         "('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",

         "('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",

         "('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",

         "('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"

      );

      foreach ($data as $values) {

         $qry = ctsql_query("INSERT INTO custmast VALUES $values", $ses);

         if (!$qry)

            Handle_Error("ctsql_query(INSERT)");

      }

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Display_Records()

   //

   // This function displays the contents of a table.

   //

   function Display_Records ($ses) {

      print("\t\tDisplay records...<br>\n");

      $qry = ctsql_query("SELECT cm_custnumb \"Number\", cm_custname \"Name\" FROM custmast", $ses);

      if (!is_resource($qry))

         Handle_Error("ctsql_query(SELECT)");

      else {

         print("\t\t<TABLE border=1>\n\t\t\t<TR><TH>Number</TH><TH>Name</TH></TR>\n");

         while ($values = ctsql_fetch_row($qry)) {

            print("\t\t\t<TR>");

            foreach ($values as $content) {

               if (is_null($content))

                  print ("<TD>NULL</TD>");

               else

                  print ("<TD>$content</TD>");

            }

            print("</TR>\n");

         }

         print("\t\t</TABLE>\n");

         if (ctsql_errno())

            Handle_Error("ctsql_fetch_row()");

         ctsql_free_result($qry);

      }

   }

 

Done

When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.

Below is the code for Done():

   //
   // Done()
   //
   // This function handles the housekeeping of closing, freeing,
   // disconnecting and logging out of the database
   //
   function Done ($ses) {
      print("\t<h4>DONE</h4>\n");
      // logout
      print("\t\tLogout...<br>\n");
      ctsql_close($ses);
   }

 

Relationships

php.sql\tutorials\PHP_Tutorial2.php

Now we will build some table/file relationships using the FairCom DB PHP Interface.

This tutorial will advance the concepts introduced in the first tutorial by expanding the number of tables. We will define key columns/fields and create specific indexes for each table to form a relational model database.

Like all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done() !

Tutorial #2: Relational Model and Indexing

Here we add a bit more complexity, introducing multiple tables, with related indices in order to form a simple "relational" database simulating an Order Entry system. Here is an overview of what will be created:

  • Initialize() - Connects to the FairCom Database Engine.
  • Define() - Defines and creates the "custmast", "custordr", "ordritem" and the "itemmast" tables/files with related indexes.
  • Manage() - Adds some related rows/records to all tables/files. Then queries the database.
  • Done() - Disconnects from FairCom Database Engine.

Note our simple PHP script:

<?php
   print("<html>\n");
   print("<head>\n");
   print("\t<title>PHP Tutorial 2</title>\n");
   print("</head>\n");
   print("<body>\n");
   //
   // Implementation of the concept of "init, define, manage and you're done..."
   //
   $session = Initialize();
   Defines($session);
   Manage($session);
   Done($session);

We suggest opening the source code with your own editor.

Continue now to review these four steps.

 

Init

First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.

Below is the code for Initialize():

   //
   // Initialize()
   //
   // Perform the minimum requirement of logging onto the c-tree Server
   //
   function Initialize() {
      print("\t<h4>INIT</h4>\n");
      // connect to server
      print("\t\tLogon to server...<br>\n");
      $ses = ctsql_connect("localhost:ctreeSQL", "admin", "ADMIN");
      if (!$ses)
         Handle_Error("ctsql_connect()");
      return ($ses);
   }

 

Define

Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.

Below is the code for Define():

   //
   // Define()
   //
   // Create the tables
   //
   function Defines($ses) {
      print("\t<h4>DEFINE</h4>\n");
      Create_CustomerMaster_Table($ses);
      Create_CustomerOrders_Table($ses);
      Create_OrderItems_Table($ses);
      Create_ItemMaster_Table($ses);
      if (!ctsql_commit($ses))
         Handle_Error("ctsql_commit()");
   }
   //
   // Create_CustomerMaster_Table()
   //
   // Create the table CustomerMaster
   //
   function Create_CustomerMaster_Table ($ses) {
      print("\t\ttable CustomerMaster<br>\n");
      $qry = ctsql_query(
         "CREATE TABLE custmast ( 
            cm_custnumb CHAR(4), 
            cm_custzipc CHAR(9), 
            cm_custstat CHAR(2), 
            cm_custrtng CHAR(1), 
            cm_custname VARCHAR(47), 
            cm_custaddr VARCHAR(47), 
            cm_custcity VARCHAR(47))",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE TABLE)");
      $qry = ctsql_query(
         "CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE INDEX)");
   }
   //
   // Create_CustomerOrders_Table()
   //
   // Create the table CustomerOrders
   //
   function Create_CustomerOrders_Table ($ses) {
      print("\t\ttable CustomerOrders<br>\n");
      $qry = ctsql_query(
      "CREATE TABLE custordr (
         co_ordrdate DATE,
         co_promdate DATE,
         co_ordrnumb CHAR(6),
         co_custnumb CHAR(4))",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE TABLE)");
      $qry = ctsql_query(
         "CREATE UNIQUE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE INDEX)");
      $qry = ctsql_query(
         "CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE INDEX)");
   }
   //
   // Create_OrderItems_Table()
   //
   // Create the table OrderItems
   //
   function Create_OrderItems_Table ($ses) {
      print("\t\ttable OrderItems<br>\n");
      $qry = ctsql_query(
      "CREATE TABLE ordritem (
         oi_sequnumb SMALLINT,
         oi_quantity SMALLINT,
         oi_ordrnumb CHAR(6),
         oi_itemnumb CHAR(5))",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE TABLE)");
      $qry = ctsql_query(
         "CREATE UNIQUE INDEX oi_ordrnumb_idx ON ordritem (oi_ordrnumb, oi_sequnumb)",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE INDEX)");
      $qry = ctsql_query(
         "CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE INDEX)");
   }
   //
   // Create_ItemMaster_Table()
   //
   // Create the table ItemMaster
   //
   function Create_ItemMaster_Table ($ses) {
      print("\t\ttable ItemMaster<br>\n");
      $qry = ctsql_query(
      "CREATE TABLE itemmast (
         im_itemwght INTEGER,
         im_itempric MONEY,
         im_itemnumb CHAR(5),
         im_itemdesc VARCHAR(47))",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE TABLE)");
      $qry = ctsql_query(
         "CREATE UNIQUE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE INDEX)");
   }

 

Manage

Manage() provides data management functionality for your application and/or process.

Below is the code for Manage():

   //

   // Manage()

   //

   // Populates table and performs a simple query

   //

   function Manage($ses) {

      print("\t<h4>MANAGE</h4>\n");

      

      // populate the tables with data

      Add_CustomerMaster_Records($ses);

      Add_CustomerOrders_Records($ses);

      Add_OrderItems_Records($ses);

      Add_ItemMaster_Records($ses);

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

      // perform a query:

      // list customer name and total amount per order

      // name               total

      // @@@@@@@@@@@@@      $xx.xx

      // for each order in the CustomerOrders table

      //    fetch order number

      //    fetch customer number

      //    fetch name from CustomerMaster table based on customer number

      //    for each order item in OrderItems table

      //       fetch item quantity

      //       fetch item number

      //       fetch item price from ItemMaster table based on item number

      //    next

      // next

      $qry = ctsql_query(

               "SELECT cm_custname \"Name\", SUM(im_itempric * oi_quantity) \"Total\"" .

               "FROM custmast, custordr, ordritem, itemmast " .

               "WHERE co_custnumb = cm_custnumb AND co_ordrnumb = oi_ordrnumb AND oi_itemnumb = im_itemnumb " .

               "GROUP BY cm_custnumb, cm_custname",

               $ses);

      if (!is_resource($qry))

         Handle_Error("ctsql_query(SELECT)");

      else {

         print("\t\t<TABLE border=1>\n\t\t\t<TR><TH>Name</TH><TH>Total</TH></TR>\n");

         while ($values = ctsql_fetch_row($qry)) {

            print("\t\t\t<TR>");

            foreach ($values as $content) {

               if (is_null($content))

                  print ("<TD>NULL</TD>");

               else

                  print ("<TD>$content</TD>");

            }

            print("</TR>\n");

         }

         print("\t\t</TABLE>\n");

         if (ctsql_errno())

            Handle_Error("ctsql_fetch_row()");

         ctsql_free_result($qry);

      }

   }

   //

   // Add_CustomerMaster_Records()

   //

   // This function adds records to table CustomerMaster from an

   // array of strings

   //

   function Add_CustomerMaster_Records ($ses) {

      print("\t\tAdd records in table CustomerMaster...<br>\n");

      $data = array(

         "('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",

         "('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",

         "('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",

         "('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"

      );

      Delete_Records($ses, "custmast");

      foreach ($data as $values) {

         $qry = ctsql_query("INSERT INTO custmast VALUES $values", $ses);

         if (!$qry)

            Handle_Error("ctsql_query(INSERT)");

      }

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Add_CustomerOrders_Records()

   //

   // This function adds records to table CustomerOrders from an

   // array of strings

   //

   function Add_CustomerOrders_Records ($ses) {

      print("\t\tAdd records in table CustomerOrders...<br>\n");

      $data = array(

         "('09/01/2002','09/05/2002','1','1001')",

         "('09/02/2002','09/06/2002','2','1002')"

      );

      Delete_Records($ses, "custordr");

      foreach ($data as $values) {

         $qry = ctsql_query("INSERT INTO custordr VALUES $values", $ses);

         if (!$qry)

            Handle_Error("ctsql_query(INSERT)");

      }

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Add_OrderItems_Records()

   //

   // This function adds records to table OrderItems from an

   // array of strings

   //

   function Add_OrderItems_Records ($ses) {

      print("\t\tAdd records in table OrderItems...<br>\n");

      $data = array(

         "(1,2,'1','1')",

         "(2,1,'1','2')",

         "(3,1,'1','3')",

         "(1,3,'2','3')"

      );

      Delete_Records($ses, "ordritem");

      foreach ($data as $values) {

         $qry = ctsql_query("INSERT INTO ordritem VALUES $values", $ses);

         if (!$qry)

            Handle_Error("ctsql_query(INSERT)");

      }

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Add_ItemMaster_Records()

   //

   // This function adds records to table ItemMaster from an

   // array of strings

   //

   function Add_ItemMaster_Records ($ses) {

      print("\t\tAdd records in table ItemMaster...<br>\n");

      $data = array(

         "(10,19.95,'1','Hammer')",

         "(3,  9.99,'2','Wrench')",

         "(4, 16.59,'3','Saw')",

         "(1,  3.98,'4','Pliers')"

      );

      Delete_Records($ses, "itemmast");

      foreach ($data as $values) {

         $qry = ctsql_query("INSERT INTO itemmast VALUES $values", $ses);

         if (!$qry)

            Handle_Error("ctsql_query(INSERT)");

      }

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Delete_Records()

   //

   // This function deletes all the records in a tables

   //

   function Delete_Records ($ses, $table) {

      print("\t\tDelete records...<br>\n");

      $qry = ctsql_query("DELETE FROM $table", $ses);

      if (!$qry)

         if (100 == ctsql_errno())

            return;

         else

            Handle_Error("ctsql_query(DELETE)");

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

 

Done

When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.

Below is the code for Done():

   //
   // Done()
   //
   // This function handles the housekeeping of closing, freeing,
   // disconnecting and logging out of the database
   //
   function Done ($ses) {
      print("\t<h4>DONE</h4>\n");
      // logout
      print("\t\tLogout...<br>\n");
      ctsql_close($ses);
   }

 

Record/Row Locking

php.sql\tutorials\PHP_Tutorial3.php

Now we will explore row/record locks using the FairCom DB PHP Interface.

The functionality for this tutorial focuses on inserting/adding rows/records, then updating a single row/record in the customer master table under locking control. The application will pause after a LOCK is placed on a row/record. Another instance of this application should then be launched, which will block, waiting on the lock held by the first instance. Pressing the <Enter> key will enable the first instance to proceed. This will result in removing the lock thereby allowing the second instance to continue execution. Launching two processes provides a visual demonstration of the effects of locking and a basis for experimentation on your own.

As with all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and you’re Done() !

Tutorial #3: Locking

Here we demonstrate the enforcement of data integrity by introducing record/row "locking".

  • Initialize() - Connects to the FairCom Database Engine.
  • Define() - Defines and creates a "customer master" (custmast) table/file.
  • Manage() - Adds a few rows/records, manipulates them, and displays the results.
  • Done() - Disconnects from FairCom Database Engine.

Note our simple PHP script:

<?php
   print("<html>\n");
   print("<head>\n");
   print("\t<title>PHP Tutorial 3</title>\n");
   print("</head>\n");
   print("<body>\n");
   //
   // Implementation of the concept of "init, define, manage and you're done..."
   //
   $session = Initialize();
   Defines($session);
   Manage($session);
   Done($session);

We suggest opening the source code with your own editor.

Continue now to review these four steps.

 

Init

First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.

Below is the code for Initialize():

   //
   // Initialize()
   //
   // Perform the minimum requirement of logging onto the c-tree Server
   //
   function Initialize() {
      echo "<h4>INIT</h4>\n";
      // connect to server
      $ses = ctsql_connect("localhost:ctreeSQL", "admin", "ADMIN");
      if (!$ses)
         Handle_Error("ctsql_connect()");
      return ($ses);
   }

 

Define

Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.

Below is the code for Define():

   //
   // Define()
   //
   // Create the table for containing a list of existing customers
   //
   function Defines($ses) {
      echo "<h4>DEFINE</h4>\n";
      $qry = ctsql_query(
         "CREATE TABLE custmast ( 
            cm_custnumb CHAR(4), 
            cm_custzipc CHAR(9), 
            cm_custstat CHAR(2), 
            cm_custrtng CHAR(1), 
            cm_custname VARCHAR(47), 
            cm_custaddr VARCHAR(47), 
            cm_custcity VARCHAR(47))",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE TABLE)");
      $qry = ctsql_query(
         "CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)",
         $ses);
      if (!$qry)
         Handle_Error("ctsql_query(CREATE INDEX)");
      if (!ctsql_commit($ses))
         Handle_Error("ctsql_commit()");
  }

 

Manage

Manage() provides data management functionality for your application and/or process.

Below is the code for Manage():

   //

   // Manage()

   //

   // This function performs simple record functions of add, delete and gets

   //

   function Manage($ses) {

      echo "<h4>MANAGE</h4>\n";

      

      // delete any existing records

      Delete_Records($ses);

      // populate the table with data

      Add_CustomerMaster_Records($ses);

      // display contents of table

      Display_Records($ses);

      // update a record under locking control

      Update_CustomerMaster_Record($ses);

      // display again after update and effects of lock

      Display_Records($ses);

   }

   //

   // Delete_Records()

   //

   // This function deletes all the records in the table

   //

   function Delete_Records ($ses) {

      echo "Delete records...<br>\n";

      $qry = ctsql_query("DELETE FROM custmast", $ses);

      if (!$qry)

         if (100 == ctsql_errno())

            return;

         else

            Handle_Error("ctsql_query(DELETE)");

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Add_CustomerMaster_Records()

   //

   // This function adds records to table CustomerMaster from an

   // array of strings

   //

   function Add_CustomerMaster_Records ($ses) {

      echo "Add records...<br>\n";

      $data = array(

         "('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",

         "('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",

         "('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",

         "('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"

      );

      foreach ($data as $values) {

         $qry = ctsql_query("INSERT INTO custmast VALUES $values", $ses);

         if (!$qry)

            Handle_Error("ctsql_query(INSERT)");

      }

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Display_Records()

   //

   // This function displays the contents of a table.

   //

   function Display_Records ($ses) {

      echo "Display records...<br>\n";

      $qry = ctsql_query("SELECT cm_custnumb \"Number\", cm_custname \"Name\" FROM custmast", $ses);

      if (!is_resource($qry))

         Handle_Error("ctsql_query(SELECT)");

      else {

         print("<TABLE border=1><TR><TH>Number</TH><TH>Name</TH></TR>\n");

         while ($values = ctsql_fetch_row($qry)) {

            print("<TR>");

            foreach ($values as $content) {

               if (is_null($content))

                  print ("<TD>NULL</TD>");

               else

                  print ("<TD>$content</TD>");

            }

            print("</TR>\n");

         }

         print("</TABLE>\n");

         if (ctsql_errno())

            Handle_Error("ctsql_fetch_row()");

         ctsql_free_result($qry);

      }

   }

   //

   // Update_CustomerMaster_Records()

   //

   // Update one record under locking control to demonstrate the effects

   // of locking

   //

 

Done

When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.

Below is the code for Done():

   //
   // Done()
   //
   // This function handles the housekeeping of closing, freeing,
   // disconnecting and logging out of the database
   //
   function Done ($ses) {
      echo "<h4>DONE</h4>\n";
      // logout
      ctsql_close($ses);
   }

 

Transaction Processing

php.sql\tutorials\PHP_Tutorial4.php

Now we will discuss transaction processing as it relates to the FairCom DB PHP Interface.

Transaction processing provides a safe method by which multiple database operations spread across separate tables/files are guaranteed to be atomic. By atomic, we mean that, within a transaction, either all of the operations succeed or none of the operations succeed. This "either all or none" atomicity ensures that the integrity of the data in related tables/files is secure.

Like all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done()!

Tutorial #4: Transaction Processing

Here we demonstrate transaction control.

  • Initialize() - Connects to the FairCom Database Engine.
  • Define() - Defines and creates our four tables/files.
  • Manage() - Adds rows/records to multiple tables/files under transaction control.
  • Done() - Disconnects from FairCom Database Engine.

Note our simple PHP script:

<?php
   print("<html>\n");
   print("<head>\n");
   print("\t<title>PHP Tutorial 4</title>\n");
   print("</head>\n");
   print("<body>\n");
   //
   // Implementation of the concept of "init, define, manage and you're done..."
   //
   $session = Initialize();
   Defines($session);
   Manage($session);
   Done($session);

We suggest opening the source code with your own editor.

Continue now to review these four steps.

 

Init

First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.

Below is the code for Initialize():

   //
   // Initialize()
   //
   // Perform the minimum requirement of logging onto the c-tree Server
   //
   function Initialize() {
      print("\t<h4>INIT</h4>\n");
      // connect to server
      print("\t\tLogon to server...<br>\n");
      $ses = ctsql_connect("localhost:ctreeSQL", "admin", "ADMIN");
      if (!$ses)
         Handle_Error("ctsql_connect()");
      return ($ses);
   }

 

Define

Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.

Below is the code for Define():

   //

   // Define()

   //

   // Create the tables

   //

   function Defines($ses) {

      print("\t<h4>DEFINE</h4>\n");

      // delete tables ...

      Delete_Tables($ses);

      // ...and re-create them with constraints

      Create_CustomerMaster_Table($ses);

      Create_CustomerOrders_Table($ses);

      Create_OrderItems_Table($ses);

      Create_ItemMaster_Table($ses);

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Create_CustomerMaster_Table()

   //

   // Create the table CustomerMaster

   //

   function Create_CustomerMaster_Table ($ses) {

      print("\t\ttable CustomerMaster<br>\n");

      $qry = ctsql_query(

         "CREATE TABLE custmast ( 

            cm_custnumb CHAR(4) PRIMARY KEY, 

            cm_custzipc CHAR(9), 

            cm_custstat CHAR(2), 

            cm_custrtng CHAR(1), 

            cm_custname VARCHAR(47), 

            cm_custaddr VARCHAR(47), 

            cm_custcity VARCHAR(47))",

         $ses);

      if (!$qry)

         Handle_Error("ctsql_query(CREATE TABLE)");

   }

   //

   // Create_CustomerOrders_Table()

   //

   // Create the table CustomerOrders

   //

   function Create_CustomerOrders_Table ($ses) {

      print("\t\ttable CustomerOrders<br>\n");

      $qry = ctsql_query(

      "CREATE TABLE custordr (

         co_ordrdate DATE,

         co_promdate DATE,

         co_ordrnumb CHAR(6) PRIMARY KEY,

         co_custnumb CHAR(4),

         FOREIGN KEY (co_custnumb) REFERENCES custmast)",

         $ses);

      if (!$qry)

         Handle_Error("ctsql_query(CREATE TABLE)");

   }

   //

   // Create_OrderItems_Table()

   //

   // Create the table OrderItems

   //

   function Create_OrderItems_Table ($ses) {

      print("\t\ttable OrderItems<br>\n");

      $qry = ctsql_query(

      "CREATE TABLE ordritem (

         oi_sequnumb SMALLINT,

         oi_quantity SMALLINT,

         oi_ordrnumb CHAR(6),

         oi_itemnumb CHAR(5),

         FOREIGN KEY (oi_itemnumb) REFERENCES itemmast,

         FOREIGN KEY (oi_ordrnumb) REFERENCES custordr)",

         $ses);

      if (!$qry)

         Handle_Error("ctsql_query(CREATE TABLE)");

   }

   //

   // Create_ItemMaster_Table()

   //

   // Create the table ItemMaster

   //

   function Create_ItemMaster_Table ($ses) {

      print("\t\ttable ItemMaster<br>\n");

      $qry = ctsql_query(

      "CREATE TABLE itemmast (

         im_itemwght INTEGER,

         im_itempric MONEY,

         im_itemnumb CHAR(5) PRIMARY KEY,

         im_itemdesc VARCHAR(47))",

         $ses);

      if (!$qry)

         Handle_Error("ctsql_query(CREATE TABLE)");

   }

 

Manage

Manage() provides data management functionality for your application and/or process.

Below is the code for Manage():

   //

   // Manage()

   //

   // Populates table and perform a simple query

   //

   function Manage($ses) {

      print("\t<h4>MANAGE</h4>\n");

      

      // populate the tables with data

      Add_CustomerMaster_Records($ses);

      Add_ItemMaster_Records($ses);

      Add_Transactions($ses);

      // display the orders and their items

      Display_CustomerOrders($ses);

      Display_OrderItems($ses);

   }

   //

   // Delete_Tables()

   //

   // This function removes all existing tables

   //

   function Delete_Tables ($ses) {

      $qry = ctsql_query("DROP TABLE ordritem", $ses);

      if (!$qry)

         Handle_Error("ctsql_query(DROP TABLE)");

      $qry = ctsql_query("DROP TABLE custordr", $ses);

      if (!$qry)

         Handle_Error("ctsql_query(DROP TABLE)");

      $qry = ctsql_query("DROP TABLE custmast", $ses);

      if (!$qry)

         Handle_Error("ctsql_query(DROP TABLE)");

      $qry = ctsql_query("DROP TABLE itemmast", $ses);

      if (!$qry)

         Handle_Error("ctsql_query(DROP TABLE)");

   }

   //

   // Add_CustomerMaster_Records()

   //

   // This function adds records to table CustomerMaster from an

   // array of strings

   //

   function Add_CustomerMaster_Records ($ses) {

      print("\t\tAdd records in table CustomerMaster...<br>\n");

      $data = array(

         "('1000','92867','CA','1','Bryan Williams','2999 Regency','Orange')",

         "('1001','61434','CT','1','Michael Jordan','13 Main','Harford')",

         "('1002','73677','GA','1','Joshua Brown','4356 Cambridge','Atlanta')",

         "('1003','10034','MO','1','Keyon Dooling','19771 Park Avenue','Columbia')"

      );

      foreach ($data as $values) {

         $qry = ctsql_query("INSERT INTO custmast VALUES $values", $ses);

         if (!$qry)

            Handle_Error("ctsql_query(INSERT)");

      }

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Add_ItemMaster_Records()

   //

   // This function adds records to table ItemMaster from an

   // array of strings

   //

   function Add_ItemMaster_Records ($ses) {

      print("\t\tAdd records in table ItemMaster...<br>\n");

      $data = array(

         "(10,19.95,'1','Hammer')",

         "(3,  9.99,'2','Wrench')",

         "(4, 16.59,'3','Saw')",

         "(1,  3.98,'4','Pliers')"

      );

      foreach ($data as $values) {

         $qry = ctsql_query("INSERT INTO itemmast VALUES $values", $ses);

         if (!$qry)

            Handle_Error("ctsql_query(INSERT)");

      }

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Add_Transactions()

   //

   // Add an Order and associated Items "as a transaction" to their

   // respective tables.  A transaction is committed or aborted if the

   // customer number on the order is confirmed valid.  Likewise each

   // item in the order is verified to be a valid item.

   //

   function Add_Transactions ($ses) {

      print("\t\tAdd transaction records...<br>\n");

      $orders = array(

         array(

            'ordrdate' => '09/01/2002',

            'promdate' => '09/05/2002',

            'ordrnumb' => '1',

            'custnumb' => '1001'

         ),

         array(

            'ordrdate' => '09/02/2002',

            'promdate' => '09/06/2002',

            'ordrnumb' => '2',

            'custnumb' => '9999' // bad customer number

         ),

         array(

            'ordrdate' => '09/22/2002',

            'promdate' => '09/26/2002',

            'ordrnumb' => '3',

            'custnumb' => '1003'

         )

      );

      $items = array(

         array(

            'ordrnumb' => '1',

            'sequnumb' => '1',

            'quantity' => '2',

            'itemnumb' => '1'

         ),

         array(

            'ordrnumb' => '1',

            'sequnumb' => '2',

            'quantity' => '1',

            'itemnumb' => '2'

         ),

         array(

            'ordrnumb' => '2',

            'sequnumb' => '1',

            'quantity' => '1',

            'itemnumb' => '3'

         ),

         array(

            'ordrnumb' => '2',

            'sequnumb' => '2',

            'quantity' => '3',

            'itemnumb' => '4'

         ),

         array(

            'ordrnumb' => '3',

            'sequnumb' => '1',

            'quantity' => '2',

            'itemnumb' => '3'

         ),

         array(

            'ordrnumb' => '3',

            'sequnumb' => '2',

            'quantity' => '2',

            'itemnumb' => '99' // bad item number

         )

      );

      foreach ($orders as $order) {

         // add order record

         $qry = ctsql_query("INSERT INTO custordr VALUES (

            '$order[ordrdate]',

            '$order[promdate]',

            '$order[ordrnumb]',

            '$order[custnumb]')",

            $ses);

         if (!$qry)

            Handle_Error("ctsql_query(INSERT)");

         foreach ($items as $item) {

            // process order items

            if ($item[ordrnumb] == $order[ordrnumb]) {

               $qr2 = ctsql_query("INSERT INTO ordritem VALUES (

                  $item[sequnumb],

                  $item[quantity],

                  '$item[ordrnumb]',

                  '$item[itemnumb]')",

                  $ses);

               if (!$qr2)

                  Handle_Error("ctsql_query(INSERT)");

            }

         }

      }

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Delete_Records()

   //

   // This function deletes all the records in a tables

   //

   function Delete_Records ($ses, $table) {

      print("\t\tDelete records...<br>\n");

      $qry = ctsql_query("DELETE FROM $table", $ses);

      if (!$qry)

         if (100 == ctsql_errno())

            return;

         else

            Handle_Error("ctsql_query(DELETE)");

      if (!ctsql_commit($ses))

         Handle_Error("ctsql_commit()");

   }

   //

   // Display_CustomerOrders()

   //

   // This function displays the contents of CustomerOrders table.

   //

   function Display_CustomerOrders ($ses) {

      print("\t\tCustomerOrders Table...<br>\n");

      $qry = ctsql_query("SELECT co_ordrnumb \"Order\", co_custnumb \"Customer\" FROM custordr", $ses);

      if (!is_resource($qry))

         Handle_Error("ctsql_query(SELECT)");

      else {

         print("\t\t<TABLE border=1>\n\t\t\t<TR><TH>Order</TH><TH>Customer</TH></TR>\n");

         while ($values = ctsql_fetch_row($qry)) {

            print("\t\t\t<TR>");

            foreach ($values as $content) {

               if (is_null($content))

                  print ("<TD>NULL</TD>");

               else

                  print ("<TD>$content</TD>");

            }

            print("</TR>\n");

         }

         print("\t\t</TABLE>\n");

         if (ctsql_errno())

            Handle_Error("ctsql_fetch_row()");

         ctsql_free_result($qry);

      }

   }

   //

   // Display_OrderItems()

   //

   // This function displays the contents of OrderItems table.

   //

   function Display_OrderItems ($ses) {

      print("\t\tOrderItems Table...<br>\n");

      $qry = ctsql_query("SELECT oi_ordrnumb \"Order\", oi_itemnumb \"Item\" FROM ordritem", $ses);

      if (!is_resource($qry))

         Handle_Error("ctsql_query(SELECT)");

      else {

         print("\t\t<TABLE border=1>\n\t\t\t<TR><TH>Order</TH><TH>Item</TH></TR>\n");

         while ($values = ctsql_fetch_row($qry)) {

            print("\t\t\t<TR>");

            foreach ($values as $content) {

               if (is_null($content))

                  print ("<TD>NULL</TD>");

               else

                  print ("<TD>$content</TD>");

            }

            print("</TR>\n");

         }

         print("\t\t</TABLE>\n");

         if (ctsql_errno())

            Handle_Error("ctsql_fetch_row()");

         ctsql_free_result($qry);

      }

   }

 

Done

When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.

Below is the code for Done():

   //
   // Done()
   //
   // This function handles the housekeeping of closing, freeing,
   // disconnecting and logging out of the database
   //
   function Done ($ses) {
      print("\t<h4>DONE</h4>\n");
      Delete_Tables($ses);
      if (!ctsql_commit($ses))
         Handle_Error("ctsql_commit()");
      // logout
      print("\t\tLogout...<br>\n");
      ctsql_close($ses);
   }

 

Additional Resources and Functionality

Be sure to see the developer's guide for .NET and Java stored procedures:

Diving Deeper into the FairCom DB API "NAV" APIs

The introductory tutorials have offered a glimpse into the ease and flexibility of this interface. The FairCom Database Engine interfaces include a rich array of features for nearly any data management need. The following features can be found in the developers' guides:

  • Logging in
  • Databases
  • Tables
  • Fields
  • Field Types
  • Indexes
  • Records
    • Inserting
    • Bulk Inserting
    • Updating
    • Deleting
  • Navigating Records
    • Finding
    • Bookmarking
    • Filtering
      • By Query
      • By Partial Key
      • By Index Range
      • By Server-Side Batch Process
  • Locking
  • Transactions
  • Metadata resources

To learn more about this API, see the developers' guides.

Additional APIs

The FairCom Database Engine provides a variety of APIs, such as Low-Level and the FairCom DB API API for C. In fact, the entire FairCom DB API API for C is available directly as function calls within the object-oriented FairCom DB API API for C#.

To learn more about FairCom APIs, see FairCom Database Engine Interfaces.

sql faircom

Was this article helpful?

Give feedback about this article

Related Articles