<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="../../style.css">
<title>
Gambas Documentation - ODBC Component Documentation
</title>
</head>
<div class="path">&nbsp;<a href="../help.html"><img class="flag" valign="middle" src="../../img/lang/en.png" alt="Home"></a>&nbsp;/&nbsp;<a href="../doc.html">doc</a>&nbsp;/&nbsp;odbc&nbsp;</div>
<div>
<div class="title">
ODBC Component Documentation
</div>
The <a href="../def/gambas.html">Gambas</a> ODBC <a href="../def/component.html">component</a> uses the API provided by the <a href="http://www.unixodbc.org">unixODBC</a> driver manager, having <a href="http://www.unixodbc.org">unixODBC</a> installed and working is a prerequisite to use the odbc functions in a <a href="../def/gambas.html">Gambas</a> application.
The <a href="../def/gambas.html">Gambas</a> ODBC <a href="../def/component.html">component</a> relay on the configuration made in the <a href="http://www.unixodbc.org">unixODBC</a> 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 <a href="http://www.unixodbc.org">unixODBC</a> 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.
<p>
Usually the <a href="../def/linux.html">Linux</a> distributions do not install the <a href="http://www.unixodbc.org">unixODBC</a> 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 <a href="http://www.unixodbc.org">unixODBC</a> 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 <a href="http://www.unixodbc.org">unixODBC</a> package yourself you can download the source code from the <a href="http://www.unixodbc.org">unixODBC</a> web site <a href="http://www.unixodbc.org">http://www.unixodbc.org</a> and follow the compilation and installation instructions that comes with the package.
<p>
To enable the <a href="../def/gambas.html">Gambas</a> ODBC <a href="../def/component.html">component</a>, you need to compile <a href="../def/gambas.html">Gambas</a> after the <a href="http://www.unixodbc.org">unixODBC</a> package installation. If you've compiled <a href="../def/gambas.html">Gambas</a> before the <a href="http://www.unixodbc.org">unixODBC</a> 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 <a href="../def/gambas.html">Gambas</a> environment, if all worked fine the ODBC component should be enabled and installed.
<p>
<h2>unixODBC Configuration</h2>
<p>
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.
<p>
<table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C1E6;">
<tr>
<td><a href="../../image/doc/odbc/:odbc-adm" target="_blank"><img src="../../image/doc/odbc/:odbc-adm-small"></a></td>
</tr>
<tr>
<th>The unixODBC Configuration Gui</th>
</tr>
</table>
<p>
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.
<p>
<table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C1E6;">
<tr>
<td><a href="../../image/doc/odbc/:driver-prop" target="_blank"><img src="../../image/doc/odbc/:driver-prop-small"></a></td>
</tr>
<tr>
<th>Driver Properties</th>
</tr>
</table>
<p>

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.
<p>
<table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C1E6;">
<tr>
<td><a href="../../image/doc/odbc/:dsn-tab" target="_blank"><img src="../../image/doc/odbc/:dsn-tab-small"></a></td>
</tr>
<tr>
<th>Data Sources</th>
</tr>
</table>
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.
<p>
<table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C1E6;">
<tr>
<td><a href="../../image/doc/odbc/:dsn-prop" target="_blank"><img src="../../image/doc/odbc/:dsn-prop-small"></a></td>
<td><a href="../../image/doc/odbc/:dsn-prop-i" target="_blank"><img src="../../image/doc/odbc/:dsn-prop-i-small"></a></td>
</tr>
<tr>
<th>DSN for MySQL </th>
<th>DSN for System i DB2 </th>
</tr>
</table>
<p>
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.
<p>
<h2>unixODBC Configuration Examples</h2>
<p>
Example of the odbcinst.ini located in the /etc directory. This file contains all the drivers configuration and the Driver Manager's configuration options
<p>
<h3>Examples</h3>
<pre class="example">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		        =</pre>
<p>

<h2>odbc.ini</h2>
<p>
System wide odbc.ini located in the /etc directory
<p>
<pre class="example">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</pre>
<p>
<h2>.odbc.ini</h2>
<p>
User's odbc configuration located in the user's home directory .odbc.ini
<p>
<pre class="example">.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</pre>
<p>

<h2>Gambas Example</h2>
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
<p>

<table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C1E6;">
<tr>
<td><a href="../../image/doc/odbc/:gambas-ide" target="_blank"><img src="../../image/doc/odbc/:gambas-ide-small"></a></td>
</tr>
<tr>
<th>Gambas</th>
</tr>
</table>
<p>
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 !
<p>
<table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C1E6;">
<tr>
<td><a href="../../image/doc/odbc/:example-db" target="_blank"><img src="../../image/doc/odbc/:example-db-small"></a></td>
</tr>
<tr>
<th>Database example application</th>
</tr>
</table>
After the connection you can run any SQL commands.
<p>

<h2>Blob Example</h2>
<p>
Following I'll quote an example program that I used to insert image files into a <a href="../comp/gb+db/blob.html">Blob</a> field
<p>
<div class="gray"><font size="-2"><b>Blob Example</b></font></div><div class="box">
<pre class="code">' Gambas class file

PRIVATE $hConn AS Connection

PUBLIC SUB Form_Open()
 $hConn = NEW Connection
END
</pre>
<p>
The Connect button runs this function:
<pre class="code">PUBLIC SUB btConnect_Click()

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

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

    ...

  ENDIF

END
</pre>
This function is called by a button, show a <a href="../comp/gb+form/dialog.html">dialog</a> Box for the file selection and then insert into the table's blob field the file contents
<pre class="code">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 = [&quot;*&quot;, &quot;All Files&quot;]
  Dialog.Title = &quot;Select the file to insert into the BLOB field&quot;

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

  IF NOT $hConn.Opened THEN $hConn.Open

  hResult = DB.Create(&quot;table&quot;)	' 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
</div>

</div>
</body>
</html>

