How To Open a Unix ODBC connection and use it

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:

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

The resulting code will be:

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:

If something are wrong, will display that could connect.

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

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:

The resulting code will be:

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.

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.

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

The resulting code will be:

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.

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:

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:

The resulting code will be:

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.

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

ODBC Example 6: manage transactions and determine afected rows

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