Setting Up TSQLConnection
Go Up to How To Perform Database Procedures
In order to describe a database connection in sufficient detail for TSQLConnection to open a connection, you must identify both the driver to use and a set of connection parameters the are passed to that driver.
Identifying the driver
The driver is identified by the DriverName property, which is the name of an installed dbExpress driver, such as ASA, ASE, Datasnap, Db2, Firebird, Informix, InterBase, MSSQL, MSSQL9, MySQL, Odbc, and Oracle. These databases use Dynalink drivers, which are partially written in Delphi. For Dynalink drivers, the driver name is associated with these files:
- The dbExpress driver. This is a dynamic-link library with a name like dbx*.dll. For example, the Interbase driver DLL begins with "dbxint" and the Oracle driver DLL begins with "dbxora".
- The dynamic-link library provided by the database vendor for client-side support.
The relationship between these files and the database name is stored in a file called dbxdrivers.ini, which is updated when you install a dbExpress driver. Typically, you do not need to worry about these files because the SQL connection component looks them up in dbxdrivers.ini when given the value of DriverName. When you set the DriverName property, TSQLConnection automatically sets the LibraryName and VendorLib properties to the names of the associated dlls. Once LibraryName and VendorLib have been set, your application does not need to rely on dbxdrivers.ini. (That is, you do not need to deploy dbxdrivers.ini with your application unless you set the DriverName property at run time.)
Specifying connection parameters
The Params property is a string list that lists name-value pairs. Each pair has the form
Name is the name of the parameter, and
Value is the value you want to assign.
The particular parameters you need depend on the database server you are using. However, one particular parameter,
Database, is required for all servers. Its value depends on the server you are using. For example, with InterBase,
Database is the name of the .gdb file, with ORACLE it is the entry in TNSNames.ora, while with DB2 it is the client-side node name.
Other typical parameters include the
User_Name (the name to use when logging in),
Password (the password for
HostName (the machine name or IP address of where the server is located), and
TransIsolation (the degree to which transactions you introduce are aware of changes made by other transactions). When you specify a driver name, the
Params property is preloaded with all the parameters you need for that driver type, initialized to default values. You can also add other connection parameters to the
Params string list, according to your application's needs. For example, to provide Multiple Active Result Set (MARS) support to a SQL connection using the MSSQL driver, you must add a key called
Mars_Connection and set its value to True.
Another useful parameter for a SQLite database connection is
FailIfMissing is set to True, the database connection will fail if the database does not exist. Otherwise, if
FailIfMissing is False, the database is created, if it does not exist.
The ColumnMetaDataSupported connection property for a SQLite database indicates whether the sqlite3 library was compiled with the SQLITE_ENABLE_COLUMN_METADATA C-preprocessor symbol defined. By default, this property is True for Windows, and False for MacOS.
Params is a string list, at design time, you can double-click the Params property in the Object Inspector to edit the parameters using the String List editor. At run time, use the Params.Values property to assign values to individual parameters.
Naming a connection description
Although you can always specify a connection using only the DatabaseName and Params properties, it can be more convenient to name a specific combination and then just identify the connection by name. You can name dbExpress database and parameter combinations, which are then saved in a file called dbxconnections.ini. The name of each combination is called a connection name.
Once you have defined the connection name, you can identify a database connection by simply setting the ConnectionName property to a valid connection name. Setting ConnectionName automatically sets the DriverName and Params properties. Once ConnectionName is set, you can edit the Params property to create temporary differences from the saved set of parameter values, but changing the DriverName property clears both Params and ConnectionName.
One advantage of using connection names arises when you develop your application using one database (for example, Local InterBase), but deploy it for use with another (such as ORACLE). In that case, DriverName and Params will likely differ on the system where you deploy your application from the values you use during development. You can switch between the two connection descriptions easily by using two versions of the dbxconnections.ini file. At design time, your application loads the DriverName and Params from the design-time version of dbxconnections.ini. Then, when you deploy your application, it loads these values from a separate version of dbxconnections.ini that uses the "real" database. However, for this to work, you must instruct your connection component to reload the DriverName and Params properties at run time. There are two ways to do this:
- Set the LoadParamsOnConnect property to True. This causes TSQLConnection to automatically set DriverName and Params to the values associated with ConnectionName in dbxconnections.ini when the connection is opened.
- Call the LoadParamsFromIniFile method. This method sets DriverName and Params to the values associated with ConnectionName in dbxconnections.ini (or in another file that you specify). You might choose to use this method if you want to then override certain parameter values before opening the connection.
Using the Connection Editor
The relationships between connection names and their associated driver and connection parameters is stored in the dbxconnections.ini file. You can create or modify these associations using the Connection Editor.
To display the Connection Editor, double-click the TSQLConnection component. The Connection Editor appears, with a drop-down list containing all available drivers, a list of connection names for the currently selected driver, and a table listing the connection parameters for the currently selected connection name.
You can use this dialog to indicate the connection to use by selecting a driver and connection name. Once you have chosen the configuration you want, click the Test Connection button to check that you have chosen a valid configuration.
In addition, you can use this dialog to edit the named connections in dbxconnections.ini:
- Edit the parameter values in the parameter table to change the currently selected named connection. When you exit the dialog by clicking OK, the new parameter values are saved to dbxconnections.ini.
- Click the Add Connection button to define a new named connection. A dialog appears where you specify the driver to use and the name of the new connection. Once the connection is named, edit the parameters to specify the connection you want and click the OK button to save the new connection to dbxconnections.ini.
- Click the Delete Connection button to delete the currently selected named connection from dbxconnections.ini.
- Click the Rename Connection button to change the name of the currently selected named connection. Note that any edits you have made to the parameters are saved with the new name when you click the OK button.