AddConnection

(ODBC Manager Library)

Description:	Called to get the object value of an ODBC connection. If the connection does not exist, then a new ODBC DSN connection will be created and explicitly added to the ODBC Manager's internal list of DSN's
Returns:	The object value of an ODBC connection
Usage:	Script or steady state.
Function Groups:	ODBC
Related to:	BuildDelete | BuildInsert | BuildSelect | BuildUpdate | ConvertToDbDate | ConvertToDbTime | ConvertToDbTimeStamp | ConvertToVTSDate | ConvertToVTSTime | ConvertToVTSTimeStamp | CopyRecords | DBDropList | DBGridList | ErrMessage | ExecuteQuery | ExecuteQueryCached | FlushCache | FormatBatchQuery | GetConnList | ResultFormat | StatsWin | TableSynch | Transaction | TransactionCached
Format:	\ODBCManager.AddConnection(DSN[, UserName, Password, ConnectTimeout, ExecutionTimeout, IdleTime, CheckBackupTime, DisconnectOnError, HandleError, UseDriverTimeout, MirrorToDisk, DBType, PaceRecovery, PaceRecoveryRate, RollbackOnConnect, ReconnectDelay])
Parameters:

DSN

Required. Text identifying the database to connect to. This can be a DSN (data source name) or it could be a connection string containing a fileDSN parameter such as: "filedsn = C:\VTScada\Access.dsn; dbq = c:\testdb1.mdb"

UserName

Optional. An optional parameter providing the user name for database access. If both the Username and Password are missing from the function call, a dialog box will prompt the operator to provide values for these parameters.

Password

Optional parameter for providing the password, if required, for database access. See note for UserName.

ConnectTimeout

Optional numeric parameter specifying the length of time the module will wait for a valid database connection to be made. Defaults to 30 if missing or invalid

ExecutionTimeout

Optional parameter specifying in seconds, the length of time the method will wait for a database command to execute. The parameter, UseDriverTimeout must be true for this parameter to have any effect. Defaults to 60.

IdleTime

Optional parameter specifying in seconds, the length of time will wait with no database communication before closing the connection. Defaults to 600 if missing or invalid

CheckBackupTime

Optional numeric parameter specifying the length of time between checks for cache files left behind failed log attempts. Defaults to 300 if missing or invalid

DisconnectOnError

Optional Boolean parameter indicating whether the ODBC connection should be terminated after any execution error occurs. Defaults to FALSE (0) if missing or invalid.

HandleError

Optional parameter that can be any of: • an error handle object, • an array of errors, • a Boolean value. TRUE to disconnect or false to indicate no disconnect. Defaults to false, meaning 'no disconnect'.

UseDriverTimeout

Optional Boolean value which may be set to true if ODBC driver in use supports timeouts. If so, then the execution timeout limit (ExecutionTimeout) is handed to the ODBC functions. Defaults to FALSE (0) if missing or invalid.

MirrorToDisk

Optional Boolean value. If true, this specifies that every write should go to disk. Defaults to FALSE (0) if missing or invalid.

DBType

Optional numeric value, indicating the type of this DB connection. Defaults to 0 if missing or invalid. DBType Meaning 0 MS SQL 1 MS Access 2 Oracle 3 MySQL 4 SyBase

PaceRecovery

Optional, Boolean value. Set to TRUE (non-zero) to pace the recovery of logs using the value in the following parameter. Defaults to TRUE (1) if missing or invalid.

PaceRecoveryRate

Optional. Any numeric expression that will set the time in seconds for recovery of logs. The preceding parameter, PaceRecovery must be set to true. Defaults to 0.1 if missing or invalid.

RollbackOnConnect

Optional. Any Boolean expression. If set to TRUE (non-zero) a rollback will be done on connect. Defaults to TRUE (1) if missing or invalid.

ReconnectDelay

Optional numeric expression to set the time to wait for reconnect on a failed connect. Defaults to 5 if missing or invalid.

Comments:

This module is a member of the ODBCManager Library, and must therefore be prefaced by \ODBCManager\, as shown in "Format" above. The only way to ensure that a long running (or faulty) query does terminate is to set appropriate values for both the parameters ExecutionTimeout and UseDriverTimeout. Modules executing queries are designed to be run as launched modules and will remain active until complete.

Example:

( {Parms for a subroutine that will connect to a database }
  TargetDSN     { Data source name                        };
  UserName      { User name for the database (if req'd)   };
  Password      { Password                                };
  QueryTimeout  { Max time to wait                        };
  DBType        { What type of database                   };
  ODBCConn      { OBDC connection object                  };
)
[
  Err           { HandleError                             };
]
Main [
  If Watch(1);
  [
    ODBCConn = \ODBCManager.AddConnection(TargetDSN,
                                          UserName,
                                          Password,
                                          Invalid,
                                          QueryTimeout,
                                          Invalid,
                                          Invalid,
                                          1 { Disconnect on error },
                                          Err,
                                          Invalid,
                                          Invalid,
                                          DBType );
 ...