You can access databases from Eggplant Functional
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.
The Driver Manager manages the ODBC driver and allows Eggplant Functional to load and use the ODBC driver.
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 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 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.
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.
set myODBCdb to (type:"ODBC", writable: No)