One of the most powerful tools in Legato is the ability to utilize an ODBC Connector to pull information from a database. ODBC (Open Database Connectivity) is a standard interface included in Windows that lets you connect to any type of database. Other applications (such as GoFiler through Legato) can talk to the database using this ODBC interface as an intermediary. This week’s blog post will be discussing ODBC, what it is, how to set it up, and what it can do for you. Next week we will explore an example where Legato actually uses it.
Before you can actually use this ODBC connector with Legato, it first has to be set up. The application for administering database connections is called odbcad32.exe, and it is in your SysWOW64 folder in your Windows installation folder. So it’s usually at "C:\Windows\SysWOW64\odbcad32.exe". There is a very similar application by the same name in the System32 folder of Windows, but that is for 64 bit ODBC connections, and Legato only supports the 32 bit ODBC connector. So make sure you use the one in the SysWOW64 folder. Once you open it, you can add, remove, or configure connections to different databases.
Depending on the database to which you’re trying to connect, you will likely need to download and install its ODBC drivers. MySQL, a very popular database format, hosts its drivers here: https://dev.mysql.com/downloads/connector/odbc/. Other popular database formats include PostgreSQL (available here: https://odbc.postgresql.org/) and MS SQL (available here: https://www.microsoft.com/en-us/download/details.aspx?id=36434), although there are of course a lot of other different database types out there. You need to find out what database you’re trying to access, find the driver for it, and install it so that you can set up a connection to it using the ODBC.
Once you have your driver installed, there may be a couple of different login options when you connect to the database. These are the options that the connector will use to actually talk to the database.
Legato doesn’t have access to these options, just to the connector itself, so you can create a lower permission user to connect to the database if you don’t want your script to have access to the full database. The common parameters it will ask for when setting up a new connection are:
1) Data Source Name: This is going to be the name of the connection you create. You will pass this to a Legato function when you actually want to connect to a database using Legato.
2) Description: A brief description of the connection that is going to be established.
3) TCP/IP Server and Port: The actual address of the SQL server to which you’re connecting.
4) User: This is the username of the SQL user that the connection use to log into the database. This is not a Windows username or any other system user.
5) Password: The password for the SQL user name above.
6) Database: The name of the database in the SQL server that you are accessing.
Depending on the driver you’re using, there may be a lot of other settings for things like SSL Tunneling, encryption, etc. Our example next week will involve a connection to an Excel file (which should be supported by default, so no new connection is required), so it has some different options. It still asks for a data source name and description, but it also lets you select a workbook to which the ODBC connector will connect. Excel connections are generally read only, but the same Legato functions apply to an Excel file as to a more complex SQL database.
