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

Using a Configured DSN

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).

You can augment a DSN-based connection string with additional keyword values. These are specific to the database driver you are using, and the names of these additional keywords must be found from other documentation. For example, some databases require a login and password, which can be added to the connection string as:

"DSN=MyDatabase; UID=John; PWD=Lightning"

Some databases require two logins, one for the server, and another for the DBMS. In this case, both can be specified:

"DSN=MyDatabase; UID=John; PWD=Lightning; UIDDBMS=JQR; PWDDBMS=Thunder"

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.

Directly accessing the Database File

You can also access your database directly, without configuring a DSN. To do this, at a minimum you need to specify the ODBC driver name and the database location in the connection string.

The driver name can be discovered by evaluating

SqlDriverInfo("")

from within Analytica.

Many drivers use the keyword DBQ for specifying the file path to the database. Some database drivers (examples are Microsoft Access and Excel) store the database in a single file, so the path specifies the actual file. Other drivers (e.g., Paradox, Text driver) store their database in many files in a single directory, in which case DBQ locates the directory. Here are some example of connection strings:

"Driver=Microsoft Access Driver (*.mdb); DBQ=C:\MyData\MyDatabase.mdb"
"Driver=Microsoft Excel Driver (*.xls); DBQ=" & CurrentModelDirectory() & "\SourceData.xls"
"Driver=Microsoft Paradox Driver (*.db); DBQ=C:\MyData\MyDatabaseDirectory"
"Driver=Microsoft Text Driver (*.txt; *.csv); DBQ="C:\MyData\MyCsvTables"

Note that when specifying the location, you must use absolute path names. The ODBC drivers do not know anything about Analytica's current directories. You may find CurrentModelDirectory and CurrentDataDirectory functions useful for computing full path names when your data source is in the same directory as your model, or a subdirectory.

SQL Syntax

Databases contain tables, each table having a name. The simplest and most common SQL statement is one that retrieve all the data from a given table:

"SELECT * FROM tableName"

ODBC to read Excel data

ODBC can be used to read data from Excel spreadsheets, at least in a rudimentary way. When using it in this fashion, you should have only a single table in each Sheet, and the first row of the sheet should contain the column names. With this layout, you can use sql statements such as "Select * from [Sheet1]".

I don't know whether the Excel driver supports SQL extensions to query spreadsheets in a more general fashion. If you know of any documentation on this, please post it here.

Library

Database Functions

Additional Help

  • 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.

See Also

Comments


You are not allowed to post comments.