Data Access Using ODBC

Gentee Programming Language > Documentation > Libraries

This library is applied for running SQL queries on a database using ODBC. For using this library, it is required to specify the file odbc.ge (from Lib subfolder) with include command.
The queries with parameters are not supported by the current version.

The object of the odbc type provides connection to a database.
The objects of the odbcquery type are used to run SQL queries and move the cursor through a result set. This object has got the arr fields[] of odbcfield array that contains result set fields odbcfield; furthermore, the number of elements of the array equals the number of the fields.
The objects of the odbcfield type make it possible to get the required information of the field as well as the field's value (depending on the current position of the cursor in the result set).

The sequence of operations for working with the database:

create an ODBC connection to the database using the odbc.connect method;

create a new ODBC query using the odbc.newquery method. Note that several queries are likely to be created for one connection;

run a SQL query using the odbcquery.run method; the query may retrieve the result set (the SELECT command) or no data (the INSERT command, the UPDATE command etc.);

move the cursor through the result set using the following methods: odbcquery.first, odbcquery.next etc. if necessary. The access is gained to the fields through the fields array odbcquery.fields[i], where i - a field number begining from 0, or with the odbcquery.fieldbyname method;

use the odbcfield.getstr method, the odbcfield.getuintmethod etc. in order to get field values;

run the next SQL query after processing if necessary;

disconnect from the database using the ODBC method odbc.disconnect.

There are some peculiarities to keep in mind when working with ODBC drivers:
while running a SQL query with the help of multiple sequential statements of the "INSERT ..." type, only some of the query statements are being executed (there can be from 300 to 1000 statements used for the "SQL server" driver) and no error message is displayed. In this case, you had better divide such queries into several parts;
some types of drivers do not make it possible to calculate the total number of messages received by the SQL query.

Methods

SQL query methods.

Field methods

Methods

odbc.connect Create a database connection
odbc.disconnect Disconnect from a database
odbc.geterror Get the last error message.
odbc.newquery Create a new ODBC query

SQL query methods.

odbcquery.active Check whether a result set exists after the SQL query execution
odbcquery.close Close a result set
odbcquery.fieldbyname Find a field based on a specified field name
odbcquery.first Move the cursor to the first record in the result set
odbcquery.geterror Get the last error message
odbcquery.getrecordcount Get the total number of records in a result set
odbcquery.last Move the cursor to the last record in the result set
odbcquery.moveby Move the cursor to a position relative to its current position
odbcquery.next Move the cursor to the next record in the result set
odbcquery.prior Move the cursor to the prior record in the result set
odbcquery.run SQL query execution
odbcquery.settimeout Set query timeout

Field methods

odbcfield.getbuf Get the field's value as a value of the buf type (the binary data)
odbcfield.getdatetime Get the field's value as a value of the datetime type
odbcfield.getdouble Get the field's value as a floating-point number of the ulong type
odbcfield.getindex Get the field index number
odbcfield.getint Get the field's value as an integer of the int type
odbcfield.getlong Get the field's value as an integer of the long type
odbcfield.getname Get the field's name
odbcfield.getnumeric Get the field's value as a fixed point number of the numeric type
odbcfield.getstr Get the field's value as a string of the str type
odbcfield.gettype Get a type of the field's value
odbcfield.getuint Get the field's value as an unsigned integer of the uint type
odbcfield.getulong Get the field's value as an unsigned integer of the ulong type
odbcfield.isnull Determine if the field contains the NULL value

odbc.connect	Create a database connection
odbc.disconnect	Disconnect from a database
odbc.geterror	Get the last error message.
odbc.newquery	Create a new ODBC query

odbcquery.active	Check whether a result set exists after the SQL query execution
odbcquery.close	Close a result set
odbcquery.fieldbyname	Find a field based on a specified field name
odbcquery.first	Move the cursor to the first record in the result set
odbcquery.geterror	Get the last error message
odbcquery.getrecordcount	Get the total number of records in a result set
odbcquery.last	Move the cursor to the last record in the result set
odbcquery.moveby	Move the cursor to a position relative to its current position
odbcquery.next	Move the cursor to the next record in the result set
odbcquery.prior	Move the cursor to the prior record in the result set
odbcquery.run	SQL query execution
odbcquery.settimeout	Set query timeout

odbcfield.getbuf	Get the field's value as a value of the buf type (the binary data)
odbcfield.getdatetime	Get the field's value as a value of the datetime type
odbcfield.getdouble	Get the field's value as a floating-point number of the ulong type
odbcfield.getindex	Get the field index number
odbcfield.getint	Get the field's value as an integer of the int type
odbcfield.getlong	Get the field's value as an integer of the long type
odbcfield.getname	Get the field's name
odbcfield.getnumeric	Get the field's value as a fixed point number of the numeric type
odbcfield.getstr	Get the field's value as a string of the str type
odbcfield.gettype	Get a type of the field's value
odbcfield.getuint	Get the field's value as an unsigned integer of the uint type
odbcfield.getulong	Get the field's value as an unsigned integer of the ulong type
odbcfield.isnull	Determine if the field contains the NULL value