jBASE ODBC

Open Database Connectivity (ODBC) is an open standard Application Programming Interface (API) for accessing a database. By using ODBC statements in a program, you can access files in a number of different common databases.

In addition to the ODBC software, you need a separate module or driver to access each database.

jBASE ODBC Connector Architecture

jAgent on jBASE Database Server

Setting up jBASE ODBC

This section describes the system requirements, Installer package, how to install and manage the jBASE ODBC Driver.

Configuring DSN

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)).

Use the following steps to add a DSN for jBASE connectivity:

Click Add in the ODBC Data Source Administrator, select jBASE ODBC Driver, and click Finish.

In the jBASE ODBC Data Source screen, specify the Data Source Name and Connection details.

Parameter	Use
Server	IP address or hostname of the machine on which the jBase_agent or tafc_agent is running.
Port	Port number on which the jBase_agent or tafc_agent is listening.
User	User login ID
Password	Login Password

Start the jbase_agent or tafc_agent on the jBASE Server machine to listen on the port defined in the last step.

Click the Test button to see if the connection can be established from the client machine to the jBASE Server.

Extracting Data from jBASE Server

You can extract data from jBASE server using any ODBC compliant application, such as Microsoft Excel, Crystal Reports, Microsoft Access, etc. When MS Excel is used, the following steps needs to be performed:

In MS Excel, navigate to Data > From Other Sources and select From Microsoft Query.

Select the DSN that was created earlier and click OK.

You will see all the jBASE tables available in the current directory where jBASE_Agent is running.

Select the file or table from which the data need to be imported. Also, select the fields of the table that are to be exported.

Select a field for sorting in the Sort by option and click Next.

Click Finish.

Click OK on the Import Data dialog box to produce the output in Table format.

You will see the jBASE table data exported and displayed in excel.

Additional Connection Properties

In R12, two additional properties are included in jBASE ODBC driver. They are

Timeout – To set the connection timeout for jAgent running on jBASE Server. If this property is not set, then the default connection timeout of jAgent will be used.
Env.Variables – To set the JEDIFILEPATH on jBASE Server. This will help you to query any table available in the specified path. By default, the query looks for tables or files in the default JEDIFILEPATH or the tables or files in the current directory, where jAgent is running.

These properties can be defined using the DSN configuration options in ODBC Data Source Admin.

jBASE Locale Configuration

Locales can be configured for the user id through the JBASE_LOCALE environment variable. The jlocales command displays a full list of available locales from the command line.

Configured locales are only applicable when executing an application in international mode or the JBASE_I18N environment variable is configured. The locale is based on the underlying OS locale configuration and the configured locale for the user id has no effect.

As configuration of the international mode is on an account basis, the state of international mode can change on execution of a LOGTO. If configuring an account with international mode false, then the JBASE_I18N environment variable will be unset as the result of the LOGTO.

Developing Client Applications

ODBC CLI is an API written in C but other frameworks like .NET provide ODBC wrapper classes. The following Visual Basic .NET examples use .NET’s Microsoft.Data.Odbc module.

Example 2

Creates a table with 100 records, followed by a SELECT. A DSN named Temenos Transact is required.

Imports System
Imports Microsoft.Data.Odbc
Module Module1
    Sub Main()
        Dim TimeStart, TimeEnd AsDate
        TimeStart = Date.Now()
 
        REM Open a connection
        Dim conn AsNew OdbcConnection("DSN=T24;UID=test;PWD=test")
        conn.Open()
 
        REM Create table
        Dim createCmd AsNew OdbcCommand("CREATE TABLE MY_TEST_TABLE(ID INTEGER, NAME VARCHAR(255), AGE SMALLINT, CREDIT_SCORE INTEGER, BALANCE DOUBLE, PRIMARY KEY(ID))", conn)
        Try
            createCmd.ExecuteNonQuery()
        Catch e As Exception
            REM Drop and create table
            Dim dropCmd AsNew OdbcCommand("DROP TABLE MY_TEST_TABLE", conn)
            dropCmd.ExecuteNonQuery()
            createCmd.ExecuteNonQuery()
        EndTry
 
        REM Insert records
        Dim insertCmd AsNew OdbcCommand("INSERT INTO MY_TEST_TABLE(ID, NAME, AGE, BALANCE, CREDIT_SCORE) VALUES (?, ?, 30, 345, 876.67)", conn)
        insertCmd.Prepare()
        insertCmd.Parameters.Add("@ID", OdbcType.Int)
        insertCmd.Parameters.Add("@NAME", OdbcType.VarChar, 255)
        Dim i AsInteger
        For i = 1 To 100 Step 1
            insertCmd.Parameters("@ID").Value = i
            insertCmd.Parameters("@NAME").Value = "Customer " & i.ToString()
            insertCmd.ExecuteNonQuery()
        Next
 
        REM Select all records
        Dim selectCmd AsNew OdbcCommand("SELECT ID, NAME, AGE, BALANCE, CREDIT_SCORE FROM MY_TEST_TABLE ORDER BY ID", conn)
        Dim reader As OdbcDataReader = selectCmd.ExecuteReader()
        While reader.Read()
            Console.Write(("ID: " + reader.GetInt32(0).ToString()))
            Console.Write(", ")
            Console.Write(("NAME: " + reader.GetString(1)))
            Console.Write(", ")
            Console.Write(("AGE: " + reader.GetInt32(2).ToString()))
            Console.Write(", ")
            Console.Write(("BALANCE: " + reader.GetInt32(3).ToString()))
            Console.Write(", ")
            Console.WriteLine(("CREDIT SCORE: " + reader.GetDouble(4).ToString()))
        EndWhile
        reader.Close()
 
        REM Close connection
        TimeEnd = Date.Now()
        Console.WriteLine("Elapsed:   {0} seconds", DateDiff(DateInterval.Second, TimeStart, TimeEnd))
        conn.Close()
    EndSub
EndModule

NOTE: 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.s

Enabling TABLEFILE functionality for jODBC

The previous jDP functionality allowed a list of files accessible to the current datasource to be specified in the connections string. Similar functionality is now provided via the TABLEFILE functionality.

To use:

Create and populate a catalog file on the jAgent server in the normal way.
When creating a new data source, specify the full path of the of the catalog file in either the advanced options dialog of odbcad32 or the USER_CATALOG parameter of the connection string. For example,
```
jODBCManager -add="DSN=MyTestjODBC;SERVER=localhost;UID=test;USER_CATALOG=c:\data\myCatalog"
```

Bookmark Name	Actions

BACK

In this topic

Add Bookmark View Bookmark

Previous Docs

Feedback