This section describes functions which access relational databases using ODBC (Open Database Connectivity). ODBC is an architecture which isolates applications from databases; it permits multiple connections to multiple databases, possibly from different vendors. It is based on SQL (Structured Query Language), a language whose purpose is to issue queries and transactions to relational databases. Databases which support SQL and ODBC include Oracle(R) and Microsoft Access(R) (commercial), MySQL(R) (commercial or GPL), and Postgresql (open source).
This documentation assumes you have a basic knowledge of SQL. Even if you do not, the examples should help you to get started. On the client computer, you should have ODBC installed (this is the case on Windows computers), and a working database with enough access authorization to connect and issue at least queries.
Get or set autocommit mode.
b = odbcautocommit(c) odbcautocommit(c, b)
autocommit(c,b) enables the autocommit mode of connection c if b is true, or disables it is false. By default, autocommit is enabled.
With a single input argument, odbcautocommit gives the current autocommit mode as a boolean value.
When autocommit is enabled, SQL transactions which modify the database, such as insert, update and delete, are committed immediately. When it is disabled, they must be committed explicitly with odbccommit, or canceled with odbcrollback.
SQLSetConnectAttr, SQLGetConnectAttr
Commit changes.
odbccommit(c) odbccommit
odbccommit(c) commits last changes made to connection c in an atomic operation. It is not required if autocommit mode is enabled.
Without input argument, odbccommit commits changes of all connections.
SQLEndTran
Connect to a database.
c = odbcconnect(database) c = odbcconnect(database, username) c = odbcconnect(database, username, password)
odbcconnect(database,username,password) establishes a connection to a database specified by a DSN (Data Source Name). All input arguments are strings. If the username and password are omitted, the values specified in ODBC for the DSN are used. The result is an identifier which should be used in all other ODBC calls. The connection is closed with odbcdisconnect.
c = odbcconnect('test')
c =
0
f = odbcexecdirect(c, 'select * from person;')
f =
0
count = odbcrowcount(f)
count =
17
numresultcols = odbcnumresultcols(f)
numresultcols =
3
list = odbcfieldname(f)
list =
{'name','birthdate','country'}
fieldname1 = odbcfieldname(f, 1)
fieldname1 =
name
odbcfetch(f);
row = odbcrow(f)
row =
{'Joe', [1949,3,24], 'UK'}
odbcfetch(f,'last');
row_field1 = odbcrow(f, 1)
row_field1 =
Bob
odbcfreestmt(f);
odbcdisconnect(c);
SQLAllocHandle, SQLSetEnvAttr, SQLSetConnectAttr, SQLGetDiagField
Disconnect from a database.
odbcdisconnect(c)
odbcdisconnect(c) closes the connection c.
SQLDisconnect, SQLFreeHandle
Execute an SQL statement.
odbcexecdirect(c, cmd) s = odbcexecdirect(c, cmd)
odbcexecdirect(c,cmd) executes the SQL statement given by the string cmd, using connection c. With an output argument, it keeps the data structures necessary to retrieve data, for instance the table which results from a SELECT statement, until a call to odbcfreestmt.
SQLExecDirect, SQLAllocHandle, SQLFreeHandle
odbcfetch, odbcgetnumtable, odbcfreestmt
Fetch a row in the result of a query.
odbcfetch(s) odbcfetch(s, mode) odbcfetch(s, mode, i)
odbcfetch(s) fetches the next row in the result of odbcexedirect identified by s. Values can then be retrieved with odbcrow.
odbcfetch(s,mode) fetches a row according to string mode ('absolute' and 'relative' require a scalar integer as third input argument):
| Mode | Meaning |
|---|---|
| 'next' | next row |
| 'prior' | previous row |
| 'first' | first row |
| 'last' | last row |
| 'absolute' | row i (first=1) |
| 'relative' | advance by i (next=1) |
SQLFetchScroll
odbcexecdirect, odbcrow, odbcgetnumtable
Get the description of a column.
strct = odbcfielddescr(s, i) list = odbcfielddescr(s)
odbcfielddescr(s,i) gets the description of column i (first=1) for the statement result s. The result is a structure with the following fields:
| Field | Value |
|---|---|
| name | column name |
| datatype | data type |
| size | data size |
| decimaldigits | number of decimal digits |
| nullable | true if can be the null value |
Data type is one of the following strings: 'CHAR', 'VARCHAR', 'LONGVARCHAR', 'WCHAR', 'WVARCHAR', 'WLONGVARCHAR', 'DECIMAL', 'NUMERIC', 'SMALLINT', 'INTEGER', 'REAL', 'FLOAT', 'DOUBLE', 'BIT', 'TINYBIT', 'BIGINT', 'BINARY', 'VARBINARY', 'LONGVARBINARY', 'DATE', 'TIME', 'TIMESTAMP', 'INTERVAL MONTH', 'INTERVAL YEAR', 'INTERVAL YEAR TO MONTH', 'INTERVAL DAY', 'INTERVAL HOUR', 'INTERVAL MINUTE', 'INTERVAL SECOND', 'INTERVAL DAY TO HOUR', 'INTERVAL DAY TO MINUTE', 'INTERVAL DAY TO SECOND', 'INTERVAL HOUR TO MINUTE', 'INTERVAL HOUR TO SECOND', 'INTERVAL MINUTE TO SECOND', 'GUID'.
odbcfielddescr(s) gets the description of all columns as a list of structures.
SQLDescribeCol, SQLNumResultCols
Get the name of a column.
name = odbcfieldname(s, i) list = odbcfieldname(s)
odbcfieldname(s,i) gets the name of column i (first=1) for the statement result s. The result is a string.
odbcfieldname(s) gets the names of all columns as a list of strings.
SQLDescribeCol, SQLNumResultCols
odbcfielddescr, odbcexecdirect
Free the data structure allocated for a statement.
odbcfreestmt(s)
odbcfreestmt(s) releases the data structures retained by odbcexecdirect. Afterwards, s is invalid.
SQLFreeHandle
Get numeric columns as a matrix.
matrix = odbcgetnumtable(s, columns)
odbcgetnumtable(s,columns) gets the value of the numeric columns specified in vector columns (1=first). All rows are fetched. The result is a double array.
SQLNumResultCols, SQLColAttributes, SQLFetchScroll, SQLSetPos, SQLGetData
Number of columns in a result.
n = odbcnumresultcols(s)
odbcnumresultcols(s) gives the number of columns in result s.
SQLNumResultCols
Cancel changes.
odbcrollback(c) odbcrollback
In non-autocommit mode, odbcrollback(c) cancels all the changes made with connection c since the previous odbccommit or odbcrollback command. In autocommit mode, it has no effect.
SQLEndTran
Get the data of a fetched row.
data = odbcrow(s, i) list = odbcrow(s)
odbcrow(s,i) gets the value of column i (1=first) of the last row fetched for result s. The result is a character string, a real number, or a row vector of real numbers for date or time values.
odbcrow(s) gets the values of all columns in a list.
SQLNumResultCols, SQLColAttributes, SQLSetPos, SQLGetData
Number of rows.
n = odbcrowcount(s)
odbcrowcount(s) gives the number of rows in result s.
SQLRowCount
odbcexecdirect, odbcfetch, odbcgetnumtable
Description of the columns as a table.
s = odbctables(c) s = odbctables(c, catalog) s = odbctables(c, catalog, schema) s = odbctables(c, catalog, schema, name) s = odbctables(c, catalog, schema, name, type)
odbctables(c) gets the description of the tables defined for connection c. Its result is a statement identifier which can be used by functions odbcfetch, odbcrow, and odbcfreestmt.
With additional input arguments, odbctables(s,catalog,schema,name,type) specifies the catalog name, the schema name, and the list of types. These three arguments are strings whose default value is empty and means "everything". The list of types is a comma-separated list of types in a single string, such as 'TABLE,VIEW'. Permitted types include TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, and SYNONYM.
SQLTables