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.
Connection String Syntax
The connectionString parameter specifies the data source that you will be querying. The most common syntax for the connection string is:
- "DSN=«name»"
where «name» is the name of a system or user data source that you have previously configured in the Data Sources (ODBC) wizard (accessable in Windows via
- Start → Programs → Administrative Tools → Data Sources (ODBC).
The general syntax of the connection string has the form:
- "keyword1=value; keyword2=value"
The following table lists some keywords that are used in connection strings. The list is not comprehensive since some keywords are specific to particular ODBC drivers.
| Keyword | Description | Example value(s) |
|---|---|---|
| Driver | Selects the ODBC driver | {SQL Server} |
| Server | The database server computer name | localhost |
| Trusted_Connection | Yes | |
| Database | Name of the database | Northwind |
| ExtendedProperties | &nsbp; | |
| Integrated Security | &nsbp; | SSPI |
| DataSchema | ||
| Cache Authentication | True | |
| Prompt | Complete | |
| Connection timeout | ||
| Provider | The name of an OLE DB provider. | MSDASQL {default for ODBC}
MSDASQL.1 {may have a version number attached) SQLOLEDB |
| Location |
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
- A bug has been identified in 4.0.1.1 that causes the following message to appear incorrectly in some circumstances:
First arg, <rowIndex>, to DBLabels must be defined with a DBQuery( ) statement
The bug only occurs when the node holding the DbQuery is an index node that has just been defined. If you use a Variable node, rather than an index node, the problem does not occur, or if a model is saved and reloaded the problem does not occur. If you change the index to a variable node, you must force the definition to be re-evaluated, such as by inserting a space in the definition. If you encounter this problem when it should not be appearing, our recommendation is to save and reload the model, which will correct the problem.
Enable comment auto-refresher