DbQuery
(Requires Analyica Enterprise or Power Player)
DbQuery( connectionString, sql )
Used to define an index Variable. The definition of the index should contain only one DbQuery() statement. ConnectionString specifies a data source (e.g., 'DSN=MyDatabase'). The sql parameter defines an SQL query.
When placed as the definition of an index Variable, DbQuery() will be evaluated as soon as the definition is complete. 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().
DbQuery() returns a sequence 1..n, where n is the number of records (rows) in the result table.
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().
New to 4.0
In Analytica 4.0 (but not earlier releases), a local index can hold the record set. This means you can create a user-defined function that bundles several database query steps. For example:
Function DbFullTable(connectionStr, sql: text)
Definition:
Index Rows := DbQuery(connectionStr, sql);
Index Cols := DbLabels(Rows);
DbTable(Rows, Cols)
This user-defined function returns the full relational table in a single call, using local indexes, Rows and Cols.
Library
Database Functions
Additional Help
- Follow a step-by-step mini-tutorial to get your first DbQuery working with a Microsoft Access database here: DbQuery/Step-by-Step querying Microsoft Access
Enable comment auto-refresher