Auxiliary database connections

Overview

Starting with ACR 1.87, there is support for setting up auxiliary database connections.  This support is intended for use if your module needs to connect to database servers other than the ALFA central database.

In most cases, you should use the standard ACR_SQL* APIs in acr_db_persist_i (or the ACR_Persist variable support) instead of auxiliary database connections.

Any number of database connections can be created by the API, but using a persistent connection setup at module startup time is strongly recommended (versus creating an destroying connections on the fly).  Connections do not need to be to the same server, and connections created to the ALFA central database are not synchronized with the acr_db_persist_i APIs (and should as a result be avoided).

 Usage

Auxiliary database connection functions are provided in acr_server_misc_i.nss.  The general workflow for an auxiliary connection is as so:

- First, a connection handle must be created via a call to ACR_CreateDatabaseConnection().  You must specify a MySQL connection string and, optionally, control flags (such as SCRIPT_DATABASE_CONNECTION_DEBUG to enable query logging to the server log).  This step is usually done at module startup, in a module init script. For an example of how to build a connection string, see CLRScript\ALFAMySQLDatabase.cs : ALFA.MySQLDatabaseInternal.SetupConnectionString. You should set the Max Pool Size to 1, and you will need to provide a way for script code to learn the connection settings for the database, such as via module variables set by the toolset, or hardcoded in script code (discouraged).

- Next, the connection handle (int) returned by ACR_CreateDatabaseConnection should be saved away, such as in a named local int on the module.

- When it is time to use the connection, retrieve the connection handle from where you have saved it (e.g. as a named local on the module object) and call an appropriate ACR_*DatabaseConnection API, such as ACR_QueryDatabaseConnection.  The API design mirrors that of the standard ACR_SQL* APIs, but with different API names.

API Reference (see acr_server_misc_i.nss in alfa2_acr.hak)

//! Create a database connection object.
//!  - ConnectionString: Supplies the database connection string.
//!  - Flags: Supplies connection flags.  Legal values are drawn from the
//              SCRIPT_DATABASE_CONNECTION_* family of constants.
//!  - Returns: A database connection handle, or 0 on failure.
//              The handle is used for subsequent database requests.  It
//              remains valid until explicitly destroyed via a call to the
//              ACR_DestroyDatabaseConnection function.
//
//              The handle can be saved as a module local variable, or in any
//              other convenient location where an int can be stored.
int ACR_CreateDatabaseConnection(string ConnectionString, int Flags = 0);

//! Delete a database connection created by ACR_CreateDatabaseConnection.
//!  - ConnectionHandle: Supplies the connection handle that was returned by a
//                       prior call to ACR_CreateDatabaseConnection.
//!  - Returns: TRUE on success.
int ACR_DestroyDatabaseConnection(int ConnectionHandle);

//! Execute a query.  The function is similar to ACR_SQLQuery.
//!  - ConnectionHandle: Supplies the connection handle that was returned by a
//                       prior call to ACR_CreateDatabaseConnection.
//!  - Query: Supplies the query string.
//!  - Returns: TRUE on success.
int ACR_QueryDatabaseConnection(int ConnectionHandle, string Query);

//! Fetch a rowset from the database.  The function is similar to ACR_SQLFetch.
//!  - ConnectionHandle: Supplies the connection handle that was returned by a
//                       prior call to ACR_CreateDatabaseConnection.
//!  - Returns: TRUE on success.
int ACR_FetchDatabaseConnection(int ConnectionHandle);

//! Get a column from a rowset in a database query.  The function is similar to
//  ACR_SQLGetData.
//!  - ConnectionHandle: Supplies the connection handle that was returned by a
//                       prior call to ACR_CreateDatabaseConnection.
//!  - ColumnIndex: Supplies the column index (zero for the first column).
//!  - Returns: The column value, or "" on failure.
string ACR_GetColumnDatabaseConnection(int ConnectionHandle, int ColumnIndex = 0);

//! Get the affected row count in a database query rowset.  The function is
//  similar to ACR_SQLGetAffectedRows.
//!  - ConnectionHandle: Supplies the connection handle that was returned by a
//                       prior call to ACR_CreateDatabaseConnection.
//!  - Returns: The count of affected rows.
int ACR_GetAffectedRowCountDatabaseConnection(int ConnectionHandle);

//! Escape a string for use in a database query.  The function is similar to
//  ACR_SQLEncodeSpecialChars.
//!  - ConnectionHandle: Supplies the connection handle that was returned by a
//                       prior call to ACR_CreateDatabaseConnection.
//!  - String: Supplies the string to escape.
//!  - Returns: The escaped string is returned.
string ACR_EscapeStringDatabaseConnection(int ConnectionHandle, string String);