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).
There are a couple of advantages to using a DSN to specify your data source. First, the wizard in Windows makes it relatively easy to select the appropriate driver and configure the parameters used to access the data source. Second, the connectionString parameter in your model remains very simple. And third, the model is not reliant on the precise location of the database, so that if someone else uses your model on a different machine, the connection string can remain valid. On the downside, it does mean that whenever you share your model with someone else, they must configure a DSN before using it.
It is also possible to specify the location of your database in the connectionString parameter without first setting up a DSN. When doing this, you must at minimum specify the ODBC driver to use and the location of the database.
(insert examples here)
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