Skip to main content

ODBC tutorial

ODBC quick start tutorial for C programmers

This tutorial is for C developers who want to use ODBC in their C applications to interact with FairCom servers.

Note

The <root> directory for this driver is <faircom>\drivers\sql.odbc\

  1. Install and start up the FairCom server if it is not currently running.

  2. Optionally read the FairCom ODBC API introduction.

  3. Get started immediately with the source code found in the <faircom>\drivers\sql.odbc\tutorials directory, or proceed with the following instructions.

FairCom ODBC API introduction

Open Database Connectivity (ODBC) from Microsoft is a standard for client applications accessing data from a variety of data sources through a single interface. Users of applications supporting ODBC merely select a new database from a point-and-click menu to connect transparently to that data source.

ODBC is the standard mechanism for client applications to access data from various sources through a single interface. Users of applications supporting ODBC merely select a new database from a point-and-click menu to connect transparently to that data source.

To become accessible from ODBC client applications, database environments must provide software called a driver on the client system where the application resides. The driver translates the standard ODBC function calls into calls the data source can process and returns data to the application. Each data source provides a driver on the client system for applications to use to access data from that source.

The FairCom ODBC Driver extends this plug-and-play interoperability to the FairCom Database Engine. It allows any Microsoft Windows tool or application that supports ODBC to easily use c-tree as a data source. With it, applications based on tools such as Visual Basic can include FairCom DB SQL as a data source.

Configure the ODBC data source

If you are running on Linux, you do not need to install the ODBC driver.

Installations on Microsoft Windows require the installation of the FairCom ODBC driver. If you installed FairCom using the Windows .msi file installer, this was done for you. If you installed using a ZIP file, you need to install it.

  1. Determining if the FairCom ODBC Driver Is Installed.

    1. From the Windows Control Panel select "System and Security", then select "Windows tools".

    2. Select ODBC Data Sources from the list.

      The "ODBC Data Source Administrator" window should appear.

    The FairCom ODBC Driver should be listed in the in the "Drivers" tab.

  2. Install the ODBC Driver if it is not installed.

    1. Run <faircom>\tools\setup\FairComConfig.

    2. Select "Install ODBC driver" and click the Configure button to install it.

Once you have configured the FairCom ODBC Driver, you may need to exit and then restart the ODBC Data Sources administrative tool to refresh to make sure the FairCom ODBC Driver is installed.

Compile the tutorials

The source code for the tutorials is in the <root>\tutorials directory.

Select from the following methods to compile the tutorial source code.

Microsoft Visual Studio IDE Compile

FairCom provides Microsoft Visual Studio solutions for Microsoft Visual Studio 2015, 2017, 2019, and 2022. You can find these solutions under the <root>\tutorials\IDEProjects directory inside the named "Microsoft Visual Studio” folders.

This tutorial requires the Microsoft Windows SDK to be installed on your computer.

If the FairCom Database Engine is not already running on your machine, start the server.

How to Use the Visual Studio Environment

  1. Load the Tutorials.sln file that corresponds to your version of Visual Studio by double-clicking the .sln file.

  2. To build these samples, use the Build menu of Microsoft Visual Studio and select the Build Solution item:

    Picture1.svg

  3. To run the tutorial, choose Start Debugging in the Debug menu or simply press F5.

  4. To fully observe how this tutorial interacts with the FairCom server, step through the code with the debugger and execute it step-by-step using Step Into from the Debug menu or simply press F11.

    Picture2.svg

By default, the Visual Studio debugger will start the first tutorial project and display text similar to the following.

INIT
  Logon to server...
DEFINE
  Open table... 
  Add fields... 
  Create table...
MANAGE 
  Delete records... 
  Add records... 
  Display records...  
    1000    Bryan Williams   
    1001    Michael Jordan   
    1002    Joshua Brown   
    1003    Keyon Dooling
DONE 
  Close table... 
  Logout...
Press <ENTER> key to exit . . .

When you are finished with a tutorial you can select a different tutorial using the Solution Explorer. Right-click the desired project and select Set as StartUp Project, then step into the code as described above.

Picture3.svg

Command-Line Compile - Windows

Compile and run the tutorials from the Microsoft Visual Studio Developer Command Prompt window (also called the Native Tools Command Prompt window).

This section is written for a Microsoft Windows command line. The principles are similar for other environments that provide .NET support, such as Linux/Unix.

Prerequisites

Compile the tutorials

  1. Open the appropriate Visual Studio Developer Command Prompt.

    1. There are different developer command prompts for compiling on x86 and x64 platforms. You must run the appropriate one for your platform.

      1. On a 64-bit platform, press the WINDOWS START key.

      2. Start typing x64 native tools command prompt

      3. Press ENTER.

        cl1.svg

        Tip

        To open the command prompt, you typically only need to type x64 and press ENTER.

      4. Windows opens the 64-bit Visual Studio command prompt.

        cl2.svg

    2. Note

      If you open the generic Visual Studio Developer Command Prompt, the compiler defaults to 32 bits, which causes compiles to fail on a 64-bit computer.

      You can tell the difference between the generic and x64 versions of the command prompt by looking at the window title. The x64 title is "x64 Native Tools Command Prompt…". The generic title is "Developer Command Prompt…"

      CL3.svg

    3. For more information on opening the Visual Studio command prompt see:

      1. Visual Studio Developer Command Prompt and Developer PowerShell

      2. Developer command prompt shortcuts.

  2. In the Developer Command Prompt window, move to the <root>\tutorials\cmdline directory and execute the BuildTutorials.bat file.

    This batch file identifies your version of the Microsoft C compiler and sets the internal paths accordingly. It then compiles the tutorials included for this driver.

    After the tutorials have been built, the batch file runs them one at a time. It prompts you to press <Enter> before running each tutorial.

    Each tutorial should run successfully without error. If you see an error, you can look it up in the Error Code Reference.

The tutorial should look similar to the following text when running:

INIT
  Logon to server...
DEFINE
  Open table...
  Add fields...
  Create table...
MANAGE
  Delete records...
  Add records...
  Display records...
    1000    Bryan Williams
    1001    Michael Jordan
    1002    Joshua Brown
    1003    Keyon Dooling
DONE
  Close table...
  Logout...
Press <ENTER> key to exit . . .

Execute tutorials directly

You can run the tutorials individually by executing the newly built executables located in the <root>\tutorials\cmdline directory.

Running in Debug Mode

Open the Microsoft Visual Studio IDE Compile section to view, run, and debug the code.

Command-Line IDE Compile - Windows

FairCom provides a way to compile and run tutorials from a batch file that invokes the Visual Studio IDE to compile them.

The instructions are identical to the section Microsoft Visual Studio IDE Compile except the BuildTutorials.bat batch file is located in the <root>\tutorials\IDEProjects directory.

Execute tutorials directly

You can run the tutorials individually by running the newly built executable files located in the <root>\tutorials\IDEProjects\Microsoft Visual Studio ___\Debug directory that corresponds to the version of Visual Studio you have on your computer.

Command-Line Compile - Linux

This section explains the steps to compile and run tutorials from the Linux shell.

Compile

To compile the NAV API tutorials from the Linux command line, follow these steps:

  1. CD into the <root>/tutorials/cmdline directory.

  2. Execute make:

    >make

  3. If make works, it prints...

    The <driver> tutorials are ready to run.

    Type 'make run' to run the tutorials.

Note

On systems running a glibc version older than 2.17, the tutorials need to be linked with librt by adding -lrt to the SYSLIBS macro in the makefile. If you are running a system with glbic older than 2.17, edit the makefile and uncomment -lrt.

Run

If the FairCom Database Engine is not already running on your machine, start the server.

To run the tutorials from the Linux command line, follow these steps from the same folder you compiled from:

  1. Type the following:

    >make run

  2. The make file will run the tutorial(s).

  3. A prompt will ask you to press <Enter> after each tutorial is run.

If you see an error, you can look it up in the Error Code Reference.

When running, the tutorial should look similar to the following text:

INIT
  Logon to server...
DEFINE
  Open table...
  Add fields...
  Create table...
MANAGE
  Delete records...
  Add records...
  Display records...
    1000    Bryan Williams
    1001    Michael Jordan
    1002    Joshua Brown
    1003    Keyon Dooling
DONE
  Close table...
  Logout...
Press <ENTER> key to exit . . .

Tutorial walkthrough

This quick start has four programs to demonstrate how to create tables and indexes; insert, update, delete, and lock records; query across multiple tables; and process multiple operations as a single atomic transaction.

The tutorials can be found in the <faircom>\drivers\sql.odbc\tutorials directory.

Tutorial1: delete, insert, and display records in a table

Tutorial1 shows basic record management functions.

  1. Initialize()

    Connects to the FairCom Database Engine.

  2. Define()

    Creates a customer master table named custmast.

  3. Manage()

    1. Deletes preexisting records.

    2. Adds records.

    3. Reads and displays the records.

  4. Done()

    Disconnects from FairCom Database Engine.

Tutorial2: Relationships

Tutorial2 establishes some table relationships.

This tutorial creates a simple order system with customer, order, order details, and product tables. It inserts records in the tables and runs a query that lists each customer's name and the total amount of each order.

tut2.svg

  1. Initialize()

    Connects to the FairCom Database Engine.

  2. Define()

    Creates "custmast", "custordr", "ordritem" and the "itemmast" tables with related indexes.

  3. Manage()

    1. Adds records to each of these tables.

    2. Lists each customer's name and the total amount of each order.

  4. Done()

    Disconnects from FairCom Database Engine.

Tutorial3: lock and update records

Tutorial3 shows how to lock records so that other concurrent processes cannot interfere with them.

It organizes the work into four functions:

  1. Initialize()

    Connects to the FairCom Database Engine.

  2. Define()

    Creates a custmast table with a primary index.

  3. Manage()

    1. Deletes pre-existing records.

    2. Adds records.

    3. Reads and displays the records.

    4. Updates a record under locking control.

  4. Done()

    Releases resources and disconnects from the FairCom Database Engine.

Tutorial4: Use transactions

Tutorial4 shows how to process transactions in a simple order system with customer, order, order detail, and product tables. With transaction processing, related updates are either all completed or none of them are completed which ensures data integrity.

  1. Initialize()

    Connects to the FairCom Database Engine.

  2. Define()

    Creates "custmast", "custordr", "ordritem" and the "itemmast" tables with related indexes.

  3. Manage()

    1. Deletes existing records and inserts new records in the custmast and itemmast tables.

    2. Adds an order and associated items as a transaction if the customer and items are valid. 

    3. Reads and displays the newly inserted records from the custordr table.

    4. Reads and displays the newly inserted records from the ordritem table.

  4. Done()

    Disconnects from FairCom Database Engine which releases resources.

Troubleshooting

  • cl.exe not found! Please set environment for Visual C/C++ compiler.

    The most common cause of this error is running the BuildTutorials.bat file from a DOS shell or normal Windows command shell rather than from the Visual Studio Developer Command Prompt window.

  • error in tcp bind 10060

    SQLConnect() - SQL ERROR: [-20212] - [FairCom][ODBC FairCom Driver 11.8.0.419(Build-200402)][ctreeSQL]

    -20212 Error in Network Daemon

    The most common cause of this error is that the FairCom Database Engine is not running on your machine. Remember that the FairCom evaluation license times out after 3 hours, so the server might have exited and needs to be re-started. See start the server..

  • SQLConnect() - SQL ERROR: [0] - [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    This error is caused by the FairCom ODBC Driver not being installed. See Configure the ODBC data source

  • SQLConnect() - SQL ERROR: [0] - [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

    This error is caused by launching the Visual Studio Developer Command Prompt with the wrong bit depth (32 vs 64 bits). Refer to the top of this section for instructions on launching the correct Developer Command Prompt window.

See also