DbConnection

New in Analytica 6.4

(Requires Analyica Enterprise or Power Player)

DbConnection( connection, readOnly )

Opens and returns a connection to a database. «connection» is a connection string. The result is a «Database connection» object, which is a live connection to the database that can be passed as the first parameter to DbQuery or DbWrite instead of passing the connection string to those functions. The database connection remains open for the lifetime of the returned object.

Calling DbConnection prior to calling DbQuery or DbWrite adds an extra step (two function calls instead of one), but it comes with a speed advantage if you make many calls to DbQuery or DbWrite on the same database since those functions don't have to open and close the connection each time.

Generally you will define a global Variable using DbConnection, so the connection stays open (unless you invalidate its result).

The connection defaults to be read-only. To allow changes to the database, specify «readOnly» as False. An error results if pass a read-only connection to DbWrite.

Connection String Syntax

Comprehensive references for connection string syntax across different database services are at www.connectionStrings.com.

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 (accessible in Windows via

Start → Programs → Administrative Tools → Data Sources (ODBC).

Note: Don't add spaces on either side of the equal sign. Spaces on either side of the equal sign may prevent it from finding the data source. 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 examples of connection strings:

"Driver=Microsoft Access Driver (*.mdb); DBQ=C:\MyData\MyDatabase.mdb"

"Driver=Microsoft Excel Driver (*.xls); DBQ=" & CurrentModelFolder() & "\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 folders. You may find CurrentModelFolder and CurrentDataFolder functions useful for computing full path names when your data source is in the same directory as your model, or a subdirectory.

See Also

• DbQuery
• DbWrite