ODBC Administration

You can access databases from Eggplant Functional using the Open Database Connectivity (ODBC) API in SenseTalk. This makes data-driven testing from SenseTalk using a variety of databases possible, including but not limited to Oracle, MySQL, and Microsoft SQL Server.

Important

Once you have set up the connection using the information below, test it to ensure the connection is working before trying to define the connection in an Eggplant Functional script. This can be tested using the DSN in your ODBC Administrator or odbc.ini file.

Prerequisites

Requirements for using ODBC vary between operating systems. That said, accessing a database using ODBC requires three things:

• ODBC Driver: This allows Eggplant Functional and the target database to communicate. You must install the appropriate driver for the type of database you want to access. Install this driver on the machine where you run Eggplant Functional, and make sure it is the same version (32-bit or 64-bit) as your installation of Eggplant Functional.
• OCBC Driver Manager: This manages the ODBC driver and allows Eggplant Functional to load and use the ODBC driver. See Driver Managers below. Not necessary on Linux.
• Data Source Information: This information specifies the connection information for your specific database, and must include the server name, IP address, port, and credentials. This information is part of your Data Source Name (DSN) definition. The DSN is defined in your ODBC Administrator or odbc.ini file.

Driver Managers

The Driver Manager manages the ODBC driver and allows Eggplant Functional to load and use the ODBC driver.

Windows

The ODBC driver manager on Windows is called the ODBC Data Source Administrator. It manages and loads the ODBC drivers, and it also provides a graphical user interface (GUI) where you can configure DSNs. Windows 8, and Windows 10 provide an ODBC administrator for 64-bit applications. This administrator can be found in the following location:

• The 64-bit ODBC Administrator:C:\Windows\system32\odbcad32.exe.

You must also download the appropriate 64-bit ODBC driver for your database. To connect to MySQL, for example, you’ll need to download a 64-bit ODBC driver.

Mac

Mac operating systems do not include ODBC driver administration software, so you need to install a third-party ODBC manager in addition to any ODBC drivers. We have successfully tested ODBC Manager for Mac, which features a GUI where you can configure your DSNs.

You must install the 64-bit version of the appropriate ODBC driver for your database.

Linux

Linux releases of Eggplant Functional include an ODBC driver manager, so you don't need to install one. You must install a driver and configure a couple of files.

Download and extract or install the appropriate 64-bit ODBC driver for your target database and the operating system where Eggplant Functional is installed.

Create and configure the "odbc.ini" file (where you create DSNs) and the "odbcinst.ini" file (where you specify driver installation locations) in the /etc directory, which is where Eggplant Functional expects them to be. For more information about configuring DSNs, refer to the documentation for your ODBC driver or database.

Define the Connection

In order for SenseTalk to establish a connection to a database using ODBC, you must supply SenseTalk with a connection property list. SenseTalk always needs to know whether you're connecting to a database using ODBC or using an Excel workbook as a database, as well as its name or some other sort of identifier. A connection may also need a username and password, or other parameters. (Login credentials are almost always needed, but are not necessarily provided by the script. Sometimes the credentials can be saved in the ODBC manager, or, less commonly, a database has a default login that doesn't require credentials.) The exact details differ from one database system to another, but all relevant information must be specified.

ODBC connection information can be stored in a variable like this for ease of use:

set myDB to (type:"odbc", DSN:"DataSource1", user:"root", password:"")

Many of the details about the connection are stored by the driver manager on the local machine. The DSN or DataSourceName property key identifies which connection to use. The connection details may be managed through a separate ODBC Administrator program and ODBC Driver.

Generic Database Connection List Properties

• Type: Required. Specify the type of database you are connecting to. Specify: "ODBC" or "Excel".
• Writable: Determine whether or not Eggplant Functional has write abilities for this database. Use this property to change this setting for a database. (Write abilities are enabled by default for ODBC and disabled by default for Excel.)

ODBC-Only Connection List Properties

• DataSourceName or DSN: Required. The data source name (DSN) for your database. This is an ODBC identifier for the connection to the server and is defined in your ODBC Administrator or odbc.ini file. The odbc.ini file (and odbcinst.ini file) may be preceded by a period if they are in a user's home directory or belong to a specific user.
• Name: The name of the database (also called a schema) to be accessed on the server referenced by the DSN.
• User: Username component of the connection credentials, if required.
• Password: Password component of the connection credentials, if required.

Writable Property

ODBC database connections are configured to be able to write to the database by default. This means that any records fetched from the database will maintain a connection to the corresponding entry in the database, so making changes to those records will automatically update the database. For this reason, you may want to disable write abilities for your database, preventing any accidental overwriting of data. To disable write abilities for your database, set the writable property to No.

ODBC database write abilities also allow you to use the add record and delete record commands out of the box.

Example:

set myODBCdb to {type:"ODBC", writable: No}