Connecting to an ODBC database is a three-step process:

    .env = ODBC_AllocEnvironment();
    .conn = .env.AllocConnection();
	
    /* Attempt to connect. */
    ret = .conn.Connect ("DSN name", "user name", "password");
    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO)
        error (.conn.GetDiagRec());

If the connection fails, the return value will be something other than SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. You can query the database for details of the failure by calling the GetDiagRec method of the ODBCConnection.

The tutorial code connects to an existing database table. If you would like the script create a table for you, please refer to Section 3.2.6, “Creating a Database Table” below.

ODBC tables are mapped as classes into Gamma. This means that most interactions with the database will be through convenient method calls rather than SQL queries. Gamma is able to automatically determine the structure of a table in your database and to create a class from it:

tableclass = .conn.ClassFromTable (#ClassName, nil, "table_name");

This statement will look up the table named table_name in your database, and create a class whose name is ClassName from it. It will also return the class definition into the variable tableclass.

Gamma attempts to determine the primary key field from your table. Some databases, such as Microsoft Access, do not provide a facility to do this. In that case, you need to assign the primary key for the table:

.conn.SetPrimaryKey (tableclass, "id");

The resulting class will have a number of special data members, like __table, the name of the table, as well as a data member for each column of the database table.

Once you have mapped your database table to a Gamma class, you can query the database by constructing a select SQL call:

allrows = .conn.QueryToClass (tableclass, string ("select * from ", tableclass.__table));

This statement will query all of the rows in the table attached to tableclass, and return them as an array in allrows. Each element of the array will be a class instance.

To insert a row, first create a member of the class associated with the table, and then use ODBCConnection.Insert to insert it:

local   timestamp;
local   record;

record = new (tableclass);
timestamp = localtime (clock());
timestamp = format ("%d-%02d-%02d %02d:%02d:%02d",
        timestamp.year + 1900, timestamp.mon + 1,
        timestamp.mday, timestamp.hour, timestamp.min,
        timestamp.sec);
record.ptname = "point name";
record.ptvalue = point_value;
record.pttimestamp = timestamp;
record.ptquality = point_quality;
.conn.Insert (record);

This code creates a record to be inserted, assigns a value to each column in the record, and then inserts it into the database. Gamma will attempt to convert the values in each column to the type required by the database. If an error occurs, the Insert method will throw an error that can be handled by a try/catch block.

You can also insert rows by constructing your own SQL statement and submitting it using the ODBCConnection.QueryAndStore method.

To update an existing row in a database, you must know the primary key for the row you wish to update. Normally you find this key by performing a query on the database:

local   result = .conn.QueryToClass (tableclass, string
                                      ("select * from ",
                                       tableclass.__table,
                                       " where name = '",
                                       "the_point_name", "'"));
local   record = result[0];

The primary key column of the record will identify the row in the database. You can now modify the record:

record.ptvalue = new_point_value;

and update the record in the database:

.conn.Update (record);

If an error occurs, the Update method will throw an error that can be handled by a try/catch block.

In most cases, you will create the database table using your database management software. This gives you a more convenient interface to the creation process. Different databases use different syntax to create a table. However, if you need to create a table within your script, there are two options:

The CreateTable method helps to construct the query, but it still requires you to understand the SQL syntax of table creation for your database.

.conn.CreateTable ("table name",
                   "id int PRIMARY KEY IDENTITY",
                   "ptname VARCHAR(20) NOT NULL",
                   "ptvalue DOUBLE NOT NULL",
                   "pttimestamp DATETIME NOT NULL",
                   "ptquality VARCHAR(20) NOT NULL"
                   );

The CreateTable arguments consist of the table name followed by any number of definitions for the columns in the table. The column definitions depend on the database being used. In particular, the primary key field (in this example, id) is very different from one database to another. The primary key must be integer, unique and auto-incrementing. In the case of Microsoft Access, you must issue an additional query to create a primary key:

.conn.CreateTable ("table_name",
                   "id COUNTER",
                   "ptname VARCHAR(20) NOT NULL",
                   "ptvalue DOUBLE NOT NULL",
                   "pttimestamp DATETIME NOT NULL",
                   "ptquality VARCHAR(20) NOT NULL"
                   );
.conn.QueryAndStore ("create unique index p_id on table_name (id)
                      with primary disallow null");

The alternative to using ODBCConnection.CreateTable is to construct your own complete SQL statement and submit it to the database using ODBCConnection.QueryAndStore:

.conn.QueryAndStore ("create table table_name
                      (id COUNTER, ptname VARCHAR(20) NOT NULL,
                                   ptvalue DOUBLE NOT NULL,
                                   pttimestamp DATETIME NOT NULL,
                                   ptquality VARCHAR(20) NOT NULL)");
.conn.QueryAndStore ("create unique index p_id on table_name (id)
                      with primary disallow null");

If any call to CreateTable or QueryAndStore generates an error, then the error will be thrown. You can catch the error using a try/catch block within your script.