Welcome to jBASE's new documentation site! Many answers to your questions can be found by searching the Knowledgebase or viewing the jBASE Documentation. We also have a Google Group for peer discussion about jBASE. If you are unable to find the information you are looking for, jBASE Support will be glad to assist in resolving your technical problems. Enjoy and please provide comments and feedback .

How can we help you?

INTRODUCTION TO ODBC

Introduction

The jBASE ODBC Connector is an ODBC driver implementing the Open Database Connectivity (ODBC) 3.0 API. This driver release supports a driver-manager based and Unicode compliant interface, featuring support for transactions and calling stored procedures. The ODBC Connector is only available to Windows platforms but SQL requests may be issued against a remote jBASE instance running on other platforms.

jAgent is a jBASE component responsible for accepting and processing incoming client requests. As shown in this diagram, jAgent must be running to accept and dispatch SQL requests to the jBASE Server. jAgent, as well as ODBC, use TCP socket connections to communicate between each other and therefore need to be configured to use the same TCP port. More details about jAgent may be found in the jAgent user guide.

The ODBC Driver Manager is a system component which on Windows is part of the MDAC (Microsoft Data Access Components) package and automatically included with the latest Windows operating systems. Odbcad32.exe is the ODBC Data Source Administrator and odbc32.lib/ odbccp32.lib are import libraries to be used by client applications.


Assumptions

Fore more information about the ODBC API and how to use it, refer to

http://support.microsoft.com/kb/110093

If the ODBC driver is to be used to develop client applications accessing a jBASE instance, the following prerequisite knowledge is required:

  • C
  • General DBMS knowledge
  • jBASE and concepts of Multivalue databases
  • Secure Sockets Layer (SSL) protocol

System requirements

The ODBC Connector is supported on the following platforms:

  • jBASE 5.x for Windows

The following system components must be installed prior to installing the ODBC Connector:

  • Visual C++ runtime components -  version 8.0.50727.762 or higher

These runtime libraries can be downloaded from Microsoft and are supplied with the following package:

            Visual C++ 2005 SP1 Redistributable Package (x86)

Note that these runtime components are already included in:

            Microsoft .NET Framework 2.0 SP1 or higher

Installation of the Visual C++ 2005 SP1 redistributable package is not required if this version of .NET has already been installed.


Configuration

Package contents

The ODBC Connector is included in jBASE but may also be deployed on Windows operating systems which do not have jBASE installed. A self-contained installer (from now on mentioned as ODBC installer) is available which installs and registers the ODBC driver. Below is the list of release packages containing the ODBC Connector:

The ODBC installer deploys the following files:

  • jodbc.dll                        jBASE ODBC driver library
  • libjODBCSetup.dll         jBASE ODBC Setup library
  • jODBCManager.exe      jBASE ODBC Manager
  • libjcf.dll                JCF (TAFCjBASE Communication Foundation) library
  • ACE.dll                         ACE (Adaptive Communication Environment) libraries
  • ACE_SSL.dll               
  • libeay32.dll                   OpenSSL libraries
  • ssleay32.dll                 
  • icuuc40.dll                    ICU (International Components for Unicode) libraries
  • icudt40.dll
  • msvcr71.dll                   MS VC7 runtime library required by the OpenSSL libraries

Note:  The ODBC installer copies the ODBC driver and setup libraries under the Windows\System32 directory.

ODBC Setup library is a separate library implementing the ODBC Setup API required by the driver manager to display graphical components such as connection dialogs. Connection dialogs enables users to configure DSNs inside Windows’ ODBC Manager (odbcad32.exe) or input connection details when requested by the driver.

 jBASE ODBC Manager is a small console application used to register the ODBC libraries with the system, create/remove DSNs or test a connection to a remote jBASE instance. Please run the below command to obtain more information on how to use this utility.

  jODBCManager –h


Installing the driver

The ODBC installer will automatically register the ODBC driver and ODBC Setup libraries. The registration process is required to inform the ODBC Manager about the location of the ODBC driver libraries. The ODBC installer will also add the installation directory to the global user environment variable PATH. This is necessary since the jBASE ODBC driver depends on other shared libraries as e.g. JCF, ACE, etc.

Configuring DSNs

ODBC applications usually obtain the connection details from DSNs which may be configured via Microsoft’s ODBC Data Source Administrator (also known as ODBC Manager / odbcad32.exe or Control Panel Administrative Tools Data Sources (ODBC)). Please refer to ODBC Manager’s user documentation for further details on how to configure DSNs.

If the installation has been successful, the following driver should appear:

jBASE ODBC Driver 

Adding a DSN for this driver will present the following dialog:

Most of the parameters shown in the previous picture refer to the connection details required by the remote jAgent instance. Server specifies the IP/machine name and Port specifies the TCP port of the remote jAgent instance.

Please refer to the jAgent user guide for more information about the available options. Pressing the test button will attempt to establish a connection to the server using the provided user/account credentials.

Connection strings

Connection strings, as defined by the ODBC SQL CLI, specify the connection parameters supplied to the server when attempting to establish a connection. All connection attributes are defined in a single string separated by semicolons. jBASE ODBC Connector accepts the following attributes:

DSN=<Data source name>                                
DRIVER=jBASE ODBC Driver                                       (Name of ODBC driver, must be jBASE ODBC Driver)
DESCRIPTION=<DSN description>                              
SERVER=<Server address>
PORT=<jAgent port number>
UID=<User ID>
ACCOUNT=<jBASE Account>                                       (User authentication will be used if this field is blank)
PWD=<User/Account Password>
SCHEMA=<Schema name>                                          (Not used. Reserved for future use)
SSL=<Enable SSL encryption>                                      (1 = Enable SSL, 0 = Disable SSL [default])
SSL_CERTIFICATE=<Path to SSL certificate file>
SSL_KEY=<Path to SSL public key file>
COMPRESSION=<Enable data compression>               (1 = Enable comp., 0 = Disable comp. [default])
COMP_THRESHOLD=<Compression threshold>            (Compression threshold, default = 1024 byte)

USER_CATALOG=<location of catalog file>                   (Created using sqlcatman)

DSN, DRIVER, UID and PWD are attributes defined by the SQL CLI, all other attributes are jBASE ODBC Connector specific attributes. Attributes highlighted in bold are mandatory attributes.

Example:

DSN=T24;SERVER=127.0.0.1;PORT=20002;UID=test;PWD=test

DSN=T24;SERVER=127.0.0.1;PORT=20002;UID=test;PWD=test;USER_CATALOG=c:\db\myCatalog


Developing Client Applications

ODBC CLI is an API written in C but other frameworks like e.g. .NET provide ODBC wrapper classes. The following example is written in Visual Basic .NET and uses the Microsoft.Data.Odbc module read data from a jBASE file:

Imports System
Imports Microsoft.Data.Odbc
Module Module1
    Sub Main()
        Dim connectionString As String = "DSN=T24;UID=test;PWD=test"
        Dim SQL As String = "SELECT * FROM ODBCTEST"
        Dim conn As New OdbcConnection(connectionString)
        Dim cmd As New OdbcCommand(SQL)
        cmd.Connection = conn
        conn.Open()
        Dim reader As OdbcDataReader = cmd.ExecuteReader()
        While reader.Read()
            Console.Write(("ID:" + reader.GetString(0).ToString()))
            Console.Write(" ,")
            Console.Write(("NAME:" + reader.GetString(1).ToString()))
            Console.Write(" ,")
            Console.WriteLine(("AGE:" + reader.GetInt32(2).ToString()))
        End While
        reader.Close()
        conn.Close()
    End Sub
End Module

SQLGetFunction() provides a list of supported SQL CLI methods.

Stored procedures are supported via the ODBC CALL statement and provide way of calling jBASE subroutines.


Troubleshooting

  • Error while installing the ODBC driver:

In order to obtain more information about the error, open a command prompt to

List installer options:

jodbc.msi /?    

 

Install driver with verbose logging:

jodbc.msi /lv jodbc_install.log

Ensure that the necessary system components listed in the System Requirements section are installed.

  • When adding a DSN, the jBASE ODBC driver does not appear in the list of ODBC drivers:

The ODBC driver has not been registered correctly. Use the command line jODBCManager application located in the installation directory to re-install the ODBC driver. Ensure that the files driver libraries supplied to jODBCManager exist in the specified directories.

  • Error while attempting to Add or Configure a DSN:

“The setup routines for the jBASE ODBC Driver could not be loaded due to system error 126: Could not load the setup or translator library

Check if the following file exists: C:\Windows\system32\libjODBCSetup.dll

“The setup routines for the jBASE ODBC Driver could not be loaded due to system error 126: Component not found in the registry

The ODBC driver has not been registered correctly. Use the jODBCManager as described above to re-install the driver.

 

  • Error while attempting to test the connection:

Specified driver could not be loaded due to system error …”

Check that the installer has added the installation directory to the SYSTEM defined PATH environment variable since the ODBC driver has to be able to access the runtime libraries located in the installation directory. Ensure that the PATH length does not exceed the maximum length. Also try to prefix the installation directory instead of having it appended to the end of the PATH and check the result of this action. If this helps, edit the PATH to find which particular directory is causing the ODBC driver to fail. Use the jODBCManager to run the same connection test from the command line.


ODBC Quick Start

Was this article helpful?