Database functions

The Database library on the Definition menu contains five functions for working with ODBC databases.

DBLabels(dbIndex)

Returns a list of the column labels for the result table. This statement can be used to define an index which can then be used as the second argument to DBTable(). The first argument, dbIndex, must be defined by a DBQuery() statement.

DBQuery(connection, sql, key)

Used to define an index variable. The definition of the index should contain only one DBQuery() statement. connection specifies a data source (e.g., 'DSN=MyDatabase') and sql defines an SQL query. The optional key parameter may specify the name of a column in the database that will be used to determine the row index values returned by DBQuery. It is best to only use key columns that contain unique values in each row.

When placed as the definition of an index variable, DBQuery() is evaluated as soon as the definition is complete, or immediately when the model is loaded. If you place it in a variable node, it will be evaluated when the result is requested, rather than when the model is loaded, provided there are no index nodes downstream that depend on it. When it is evaluated, the actual query is performed. The resulting result table is cached inside Analytica, to subsequently be accessed by DBTable() or DBLabels().

When the optional key parameter is not specified, DBQuery() returns a sequence 1..n, where n is the number of records (rows) in the result table. When key is specified, DBQuery returns a list consisting of the values from that column in the data source.

DBQuery() should appear only once in a definition, and if it is embedded in an expression, the expression must return a list with n elements.

DBQuery() processes the sql statement in read-only mode, so that the data source cannot be altered as a result of executing this statement. To alter the data source, use DBWrite().

DBTable(dbIndex, column) DBTable(dbIndex, columnList) DBTable(dbIndex, columnIndex)

DBTable() is used to get at the data within a result table. The first argument, dbIndex, must be the name of a variable (normally an index) in your Analytica model that is defined with a DBQuery() statement. If the second argument, column, is a text value, it identifies the name of a column label in the result table, in which case DBTable() returns a 1D array (indexed by dbIndex) with the data for that column. If the second argument is a list of text values (the columnList form), then DBTable() returns a 2D table with records indexed by dbIndex, and columns implicitly indexed (i.e., self-indexed/null-indexed). If the second argument is the name of an Analytica variable (usually an index) whose value evaluates to a list of text values, those text values become the column headings for a 2D table with columns indexed by columnIndex, and rows indexed by dbIndex. With this last form, columnIndex can be defined as DBLabels(dbIndex).