How To Open a Unix ODBC connection and use it

This its a work in progress, Spanish documentation its up to date

This document describes how to connect to any database, using the ODBC Component Documentation software layer that is independent of the database type.

Overall process

An object of type Connection is created, assigning the necessary properties (database type, place where it is, user, password, etc.), and the method calls Open to open it. When you call Open the database is active and ready to query.

Communication query can be made using the Exec method with the object connection open and active, SQL queries are performed, but there are other more specific methods like Create, Edit or Delete for making SQL to database.

The result are represented as an object Result whose properties depend on the type of database handler Type, such as the affected or returned rows depending on the type of SQL query performed.

The object Result is the one that is finally accessed for the data manage from the database, and it is iterable, that means is crossed according to the amount of rows returned (if it was a SQL DDL) or affected (if it was a SQL DML), in each iteration has an array but where each element is of a own type of specification depend if was queried or manipulated.

Unless you can handle multiple connections, you can locate the code to connect on a module; It is possible to open a call from any part of the project in your classes and modules, but if they use multiple connections in different instances it is advisable to use classes, especially to handle data transfer between different databases need map Database Datatype Mapping

Requirements

  1. You should be sure to have all the database components gb.db installed and all packages marked with ODBC Component Documentation installed.

  2. This is not an example about SQL statements.

  3. Access to the database and privileges depend on the ODBC component and the DSN, please consult ODBC Component Documentation and ensure they work.

  4. The component gb.db has been installed and compiled with support for ODBC Component Documentation, and is selected for the project.

Simple ODBC Examples: connect, query, manage

ODBC Example 1: Connecting an odbc Database

Parameters of this example connect in a module, but always specialize in if multiple handles connections. This example targets a document called "odbctest" refer to the ODBC Component Documentation documentation.

1 Create a new module in your project, named MdbMain.

2 Create the "Main" entry in the module, this will have the code for connection:

       PUBLIC SUB Main()

      END

3 Create the connection code with a variable "$con" of type Connection and the code that configures the properties;

                DIM $con = New Connection
                try $con.Close()          ' Cierra la conexion el try permite falle sin error
                $con.Type = "odbc"        ' Define el tipo de Conexion
                $con.Host = "odbctest"    ' Nombre de la conexcion ODBC definida en el sistema o por el usuario actual: el DSN
                $con.Login = "dba"        ' Usuario para la conexion la mayoria de los odbc no permite definirla en el odbc.ini
                $con.Password = "sql"     ' Clave de Usuario de la conexcion, la mayoria de los odbc no lo permite en el odbc.ini
                $con.Open()               ' Activamos y Abrimos la conexion, el try es para que permita un error

The resulting code will be:

       PUBLIC SUB Main()

                DIM $con AS New Connection
                try $con.Close()          ' Cierra la conexion el try permite falle sin error
                $con.Type = "odbc"        ' Define el tipo de Conexion
                $con.Host = "odbctest"    ' Nombre de la conexcion ODBC definida en el sistema o por el usuario actual: el DSN
                $con.Login = "dba"        ' Usuario para la conexion la mayoria de los odbc no permite definirla en el odbc.ini
                $con.Password = "sql"     ' Clave de Usuario de la conexcion, la mayoria de los odbc no lo permite en el odbc.ini
                $con.Open()           ' Activamos y Abrimos la conexion, el try es para que permita un error

                PRINT "Conectado? : " & $con.Opened ' Devolvera TRUE o FALSE segun si "try $con.Open()" fue exitoso en "conectarodbc()"
       END

This will print TRUE o FALSE depending of success connection, as documentation said on ODBC .

4 Verifications:

If cannot see it the "T" or " " must or can test if the ODBC DSN are working:

    isql odbctest dba sql -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
%
If something are wrong, will display that could connect.

Please refers to the ODBC documentation, testing DSN connections for furter mayor details.

  1. Note that here our the connection object $ con is handled globally in the whole module.

  2. Note that the Name property is not defined because connections ODBC Component Documentation are not used.

  3. Note that the Database property is not defined because inODBC Component Documentation connections is defined according to the type used.

  4. For the purposes of this example, a DSN called "odbctest" is defined and it is used in the Host

ODBC Example 2: Execute a simple DDL SQL Creation Statement

For the purposes of this example, the previous steps are assumed and a DSN named "odbctest" is defined and the code from ODBC example 1 above is used

4 Using the module and the previous connection code, insert in the process "Main" *after* the "PRINT" statement query:

       $con.Exec("CREATE TABLE IF NOT EXISTS tabla1 ( columna1 TEXT, columna2 TEXT)")

The resulting code will be:

       PUBLIC SUB Main()

                DIM $con AS New Connection
                try $con.Close()          ' Cierra la conexion el try permite falle sin error
                $con.Type = "odbc"        ' Define el tipo de Conexion
                $con.Host = "odbctest"    ' Nombre de la conexcion ODBC definida en el sistema o por el usuario actual: el DSN
                $con.Login = "dba"        ' Usuario para la conexion la mayoria de los odbc no permite definirla en el odbc.ini
                $con.Password = "sql"     ' Clave de Usuario de la conexcion, la mayoria de los odbc no lo permite en el odbc.ini
                $con.Open()               ' Activamos y Abrimos la conexion, el try es para que permita un error
                PRINT "Conectado? : " & $con.Opened ' Devolvera TRUE o FALSE segun si "try $con.Open()" fue exitoso en "conectarodbc()"

                $con.Exec("CREATE TABLE IF NOT EXISTS tabla1 ( columna1 TEXT, columna2 TEXT)")
       END

This will create a new table in the database, regardless of whether the table already exists or not, by the "IF NOT EXISTS" part, but it will not guarantee the same columns if the existing table is different.

  1. Note that here we execute a statement and we do not worry about the actual obtaining of the result in the handle.

  2. For the purposes of this example we have defined a DSN called "odbctest" and this is used in the property Host

  3. Gambas has a special type of treatment of the query results, represented in a object which is called Result, see next.

ODBC Example 3: Execute a simple data manipulation or SQL DML statement

For the purposes of this example, previous steps are assumed and a DSN named "odbctest" is defined and the code of the previous ODBC example 2 is used Gambas has a special type of treatment with the query results, which is called Result.

5 Define a new variable in the main thread "rs" of type Result, which abstracts the handling of data with respect to results.

       DIM rs AS Result

6 Using the module and the previous connection code, insert in the "Main" process *after* the "Exec" of "insert" statement the following code:

       rs = $con.Exec("INSERT INTO tabla1 ( columna1, columna2) VALUES ( 'pepe', 'pablo')")
       PRINT "registro insertados "

The resulting code will be:

       PUBLIC SUB Main()

         DIM rs AS Result
         DIM $con AS New Connection

           try $con.Close()          ' Cierra la conexion el try permite falle sin error
           $con.Type = "odbc"        ' Define el tipo de Conexion
           $con.Host = "odbctest"    ' Nombre de la conexcion ODBC definida en el sistema o por el usuario actual: el DSN
           $con.Login = "dba"        ' Usuario para la conexion la mayoria de los odbc no permite definirla en el odbc.ini
           $con.Password = "sql"     ' Clave de Usuario de la conexcion, la mayoria de los odbc no lo permite en el odbc.ini
           $con.Open()           ' Activamos y Abrimos la conexion, el try es para que permita un error
           PRINT "Conectado? : " & $con.Opened ' Devolvera TRUE o FALSE segun si "try $con.Open()" fue exitoso en "conectarodbc()"

           $con.Exec("CREATE TABLE IF NOT EXISTS tabla1 ( columna1 TEXT, columna2 TEXT)")

           rs= $con.Exec("INSERT INTO tabla1 ( columna1, columna2) VALUES ( 'pepe', 'pablo')")
           PRINT "registro insertados "
  
     END

This will create a new record in the newly created table, regardless of whether the table exists or if the record exists, since a primary key was never defined.

  1. The property Count get rows count only if are a SQL DML, for rest of connection types retrieve TRUE as (-1) o FALSE

  2. The Result object handles the results, if there affected registers or not independent of Count result.

  3. For the purposes of this example we have defined a DSN called "odbctest" and this is used in the property Host

ODBC Example 4: GEt a resulset data manipulation or SQL DML statement

For the purposes of this example, the previous steps are retested and a DSN named "odbctest" is defined and the code from ODBC example 3 above is used Gambas has a special type of treatment with the query results, which is called Result.

7 Using the module and the previous connection code, insert in the process "Main" *after* the last "PRINT" statement the code:

       rs = $con.Exec("SELECT columna1, columna2 FROM tabla1 ")

7 This gets the records query on the object Result, to access each row and its columns is iterated on it with the following code:

       rs = $con.Exec("SELECT columna1, columna2 FROM tabla1 ")
       WHILE (rs.Available)
             PRINT "registro encontrado: valor columna1:" & rs!columna1 & ", valor columna2:" & rs["columna2"]
             rs.MoveNext()
       WEND

The resulting code will be:

       PUBLIC SUB Main()

         DIM rs AS Result
         DIM $con AS New Connection

           try $con.Close()          ' Cierra la conexion el try permite falle sin error
           $con.Type = "odbc"        ' Define el tipo de Conexion
           $con.Host = "odbctest"    ' Nombre de la conexcion ODBC definida en el sistema o por el usuario actual: el DSN
           $con.Login = "dba"        ' Usuario para la conexion la mayoria de los odbc no permite definirla en el odbc.ini
           $con.Password = "sql"     ' Clave de Usuario de la conexcion, la mayoria de los odbc no lo permite en el odbc.ini
           $con.Open()           ' Activamos y Abrimos la conexion, el try es para que permita un error
           PRINT "Conectado? : " & $con.Opened ' Devolvera TRUE o FALSE segun si "try $con.Open()" fue exitoso en "conectarodbc()"

           $con.Exec("CREATE TABLE IF NOT EXISTS tabla1 ( columna1 TEXT, columna2 TEXT)")

           rs= $con.Exec("INSERT INTO tabla1 ( columna1, columna2) VALUES ( 'pepe', 'pablo')")
           PRINT "registro insertados "

         rs = $con.Exec("SELECT columna1, columna2 FROM tabla1 ")
         WHILE (rs.Available)
               PRINT "registro encontrado: valor columna1:" & rs!columna1 & ", valor columna2:" & rs["columna2"]
               rs.MoveNext()
         WEND
  
     END

The output will show "registro insertados" and after showing a line for each record in the table found, note that the access to the values of the columns can be done in two different ways. NOTE : This will create another record in the newly created table, regardless of whether the table exists or not, or if the record exists, since a primary key was never defined.

  1. The property Count brings quantities when it is a SQL DML, otherwise it only returns TRUE as (-1) or FALSE

  2. The object Result has the result, if there are rows inserted/affected or not using Count.

  3. For the purposes of this example we have defined a DSN called "odbctest" and this is used in the property Host

See also

Advanced ODBC examples: tranform data, procedures, manage data and handle errors

See the spanish documentation for Advanced ODBC examples for procedures, data manipulation and handle errors.

ODBC Example 5: Handling Multiple Connections and Errors

See the spanish documentation for Advanced ODBC examples for procedures, data manipulation and handle errors.

ODBC Example 6: manage transactions and determine afected rows

See the spanish documentation for Advanced ODBC examples for procedures, data manipulation and handle errors.

ODBC Example 7: Handling huge amount of rows in grid objects

Due some ODBC limitations this way of fill thousand of rows over a simple grid object could not be possible by standar gambas way, due the method need to know how many rows have the result object to fill dinamically the grid object.

For handle thousand of rows in a control grid you can see the Gambas Extended project at gitlab: https://venenux.github.io/gambasex/exdocu.html

See also