/ doc / odbc
ODBC Component Documentation
The Gambas ODBC component uses the API provided by the unixODBC driver manager, having unixODBC installed and working is a prerequisite to use the odbc functions in a Gambas application. The Gambas ODBC component relay on the configuration made in the unixODBC driver manager, so before to connect to a database you must configure the database driver and the data source according the database you want to connect to. The configuration is made thought the unixODBC driver manager GUI or editing the odbc.ini file using a text editor. You'll find some example of both the configuration methods further in this document.

Usually the Linux distributions do not install the unixODBC package during the standard installation procedure, you have to search it in the application list and then install it manually. For example in the OpenSuse distribution the unixODBC package is divided into two parts , the basic unixODBC package that provide all the libraries and the basic functionality (but that does not provide the GUI part), and the package that contains the GUI part (you have to search and install the GUI part as a separate package). If you prefer to compile the unixODBC package yourself you can download the source code from the unixODBC web site http://www.unixodbc.org and follow the compilation and installation instructions that comes with the package.

To enable the Gambas ODBC component, you need to compile Gambas after the unixODBC package installation. If you've compiled Gambas before the unixODBC package installation, you must compile it again. At the compilation's end you'll see all the modules that are configured and enabled in the Gambas environment, if all worked fine the ODBC component should be enabled and installed.

## unixODBC Configuration

After the unixODBC installation you have to add, in the DataManager, a driver for each database you want to connect to . Some driver's installation procedure add the driver in the unixODBC configuration automatically, some other doesn't so you'll have to add it manually. In the following image you'll see the ODBCConfig GUI that shows, in the Drivers tab, two different drivers, one is used to connect to the System i DB2 and the other is used to connect to a MySQL database.

The unixODBC Configuration Gui

You can use the Add button to add a new driver for a specific database. In the configuration panel you have to specify the driver's name, the description and the path for both the driver share library and the driver DSN configuration share library. The first shared library is used to connect to the database, the second shared library is used when configuring the data source name (DSN). In the following image you'll see the ODBC driver for the System i DB2 configuration.

Driver Properties

Once you've configured the driver you want to use, you have to add a Data Source. In the Data Source tab you have to specify all the parameters needed to connect to the database, all the parameters are database specific and may vary from database to atabase. The following image shows the DSN tab with two data source configured.

Data Sources

As you can see in the following two images the configuration of the two DSN are different because the parameters needed are database specific, the first is about a MySQL database (that is running locally), the second is about a System i DB2 running on a remote server.

DSN for MySQL | DSN for System i DB2
--- | ---

You must have one Database driver configured for each Database type and one Data Source for each Database you want to use. For example, if you have two MySQL DB running one on remote system A and one on the remote system B, you'll have only one Database driver configured (MySQL driver) but two different Data Source, one Data source will describe the connection (and all the relative parameters) to system A and one Data source will describe the connection (and all the relative parameters) for system B.

## unixODBC Configuration Examples

Example of the odbcinst.ini located in the /etc directory. This file contains all the drivers configuration and the Driver Manager's configuration options

### Examples

    odbcinst.ini

    [iSeries Access ODBC Driver]
    Description		= iSeries Access for Linux ODBC Driver
    Driver			= /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
    Setup			= /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so
    NOTE1			= If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
    NOTE2			= the following Driver64/Setup64 keywords will provide that support.
    Driver64		= /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
    Setup64		        = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
    Threading		= 2
    DontDLClose		= 1
    UsageCount		= 1

    [MySQL]
    Description		=
    Driver			= /usr/lib/odbc/libmyodbc.so
    Driver64		= /usr/lib
    Setup			= /usr/lib/odbc/libodbcdrvcfg1S.so
    Setup64		        = /usr/lib
    UsageCount		= 1
    CPTimeout		=
    CPReuse		        =

    [ODBC]
    Trace			= No
    TraceFile		= /tmp/sql.log
    ForceTrace		= No
    Pooling			= No

    [Firebird]
    Description		=
    Driver			= /usr/lib/firebird/libOdbcFb32.so
    Driver64		= /usr/lib/firebird/libOdbcFb32.so
    Setup			= /usr/lib/firebird/libOdbcFb32.so
    Setup64		        = /usr/lib/firebird/libOdbcFb32.so
    UsageCount		= 1
    CPTimeout		=
    CPReuse		        =

## odbc.ini

System wide odbc.ini located in the /etc directory

    odbc.ini

    [MySQL_DB_test]
    Description			= MySQL
    Driver				= MySQL
    Host				= localhost
    Database			= TestDB
    Port				=

    [Numeric_DB]
    Description			= Firebird
    Driver				= firebird
    Dbname			        = localhost:
    Client				=
    User				= User_ID
    Password			= Password
    Role				=
    CharacterSet			= NONE
    ReadOnly			= No
    NoWait				= No
    Dialect				= 3
    QuotedIdentifier		= Yes
    SensitiveIdentifier		= No
    AutoQuotedIdentifier		= No
    UseSchemaIdentifier		= 0 - Set null field SCHEMA
    LockTimeoutWaitTransactions	= 0
    SafeThread			= Yes

## .odbc.ini

User's odbc configuration located in the user's home directory .odbc.ini

    .odbc.ini

    [System_i]
    Description		= iSeries Access ODBC Driver
    Driver			= iSeries Access ODBC Driver
    System			= 9.71.196.44
    UserID			=
    Password		=
    Naming			= 0
    DefaultLibraries        = QGPL,qiws
    Database		=
    ConnectionType	        = 0
    CommitMode		= 2
    ExtendedDynamic   	= 1
    DefaultPkgLibrary	= QGPL
    DefaultPackage		= A/DEFAULT(IBM),2,0,1,0,512
    AllowDataCompression	= 1
    MaxFieldLength	        = 32
    BlockFetch		= 1
    BlockSizeKB		= 128
    ExtendedColInfo	        = 0
    LibraryView		= 0
    AllowUnsupportedChar	= 0
    ForceTranslation	= 0
    Trace			= 0

    [mySQL_test]
    Description		= MySQL
    Driver			= MySQL
    Host			= localhost
    Database		= BlobDB
    Port			=

    [NumericFB]
    Description			= fbtest
    Driver				= firebird
    Dbname			        = localhost:/home/bortolan/fbtest/fbtest.fbd
    Client				=
    User				= bortolan
    Password			= Password
    Role				=
    CharacterSet			= NONE
    ReadOnly			= No
    NoWait				= No
    Dialect				= 3
    QuotedIdentifier		= Yes
    SensitiveIdentifier		= No
    AutoQuotedIdentifier		= No
    UseSchemaIdentifier		= 0 - Set null field SCHEMA
    LockTimeoutWaitTransactions	= 0
    SafeThread			= Yes

## Gambas Example

Gambas comes with a lot of example applications, the Database example can be used to connect to a Database also using an ODBC driver. You need to configure a DSN in the Driver Manager before. select the Database example application, run the application and then fill the required fields in order to connect to the database

Gambas

Select the type of the database from the list, chose odbc, in the host name insert the DSN configured in the unixODBC driver manager. The Database field is not used for the ODBC connection. Provide the user and the password needed to connect to the Database. You're ready to connect !

Database example application

After the connection you can run any SQL commands.

## Blob Example

Following I'll quote an example program that I used to insert image files into a Blob field

Blob Example

    ' Gambas class file

    PRIVATE $hConn AS Connection

    PUBLIC SUB Form_Open()
     $hConn = NEW Connection
    END

The Connect button runs this function:

    PUBLIC SUB btConnect_Click()

      WITH $hConn
        .Type = "odbc"			<<< The ODBC database type
        .Host = tbDatasource.Text		<<< Text Box with the Data Source Name
        .Login = tbUser.Text		<<< The user used for the connection
        .Password = tbPassword.Text	<<< The user's password
        .Name = ""				<<< Not used in the ODBC connection
      END WITH

      $hConn.Open
      IF ($hConn.Opened = FALSE) THEN
    	Message.Error("Error opening the connection")
    	PRINT $hconn.Error
      ELSE

        ...

      ENDIF

    END

This function is called by a button, show a dialog Box for the file selection and then insert into the table's blob field the file contents

    PUBLIC SUB select_and_insert_into_blob_field_a_file()

      DIM hFile AS File
      DIM sLine AS String
      DIM outs AS blob
      DIM path AS String
      DIM hResult AS Result

      Dialog.Filter = ["*", "All Files"]
      Dialog.Title = "Select the file to insert into the BLOB field"

      IF Dialog.OpenFile() THEN RETURN
      path = Dialog.path
      sLine = File.Load(Dialog.Path)

      IF NOT $hConn.Opened THEN $hConn.Open

      hResult = DB.Create("table")	' Table name
      hResult!blob = sLine		' blob is the name of the blob field in the table
      hResult!filename = path       ' filename is a string field in the table
      hResult.Update

      $hConn.Commit
      $hConn.Close
    ...

    END