In this example, we’re accessing CUSIPs from an Excel spreadsheet and displaying them, but the data source could be something more complicated such as a SQL database. Note that Excel spreadsheets don’t offer the functionality of a true database system so updating and altering them through ODBC is not optimal. Therefore, here we will limit our queries to simply retrieving information. Our key concepts for this script are:

1. Accessing the data source through a handle to an ODBC Object and an ODBC Results Object
2. Iterating through an array of query results

Our Sample Script

    handle odbc_handle;
    handle odbc_query;
    string res_row[];
    string msg;
    
    int main() {
      
      odbc_handle = ODBCConnect("Driver={Microsoft Excel Driver (*.xls)};Dbq=T:\\cusips.xls");
      ec = GetLastError();
      if (ec != ERROR_NONE) {
         msg = GetLastErrorMessage();
         MessageBox("ODBC Connection failed: %s", msg);
         return ERROR_EXIT;
         }
      odbc_query = ODBCQuery(odbc_handle, "SELECT * FROM [cusips$]"/* WHERE [CUSIP] = 'G39637106'" */);
      res_row = ODBCFetchArray(odbc_query);
      while (ArrayGetAxisDepth(res_row) != 0) {
        msg = FormatString("%9s %-30s %s", res_row[0], res_row[1], res_row[2]);
        AddMessage(msg);
        res_row = ODBCFetchArray(odbc_query);
        }
      CloseHandle(odbc_query);
      CloseHandle(odbc_handle);
      return ERROR_NONE;
      }

We first define a few important variables: a string array to hold our results from the database and a couple handles to represent our ODBC links to the data source. Inside our main function, we can set up our ODBC connector.

      odbc_handle = ODBCConnect("Driver={Microsoft Excel Driver (*.xls)};Dbq=T:\\cusips.xls");
      ec = GetLastError();
      if (ec != ERROR_NONE) {
         msg = GetLastErrorMessage();
         MessageBox("ODBC Connection failed: %s", msg);
         return ERROR_EXIT;
      }

The SDK function ODBCConnect creates our connection to the data source, in this case our spreadsheet. As we said last week, you must first set up an ODBC connector in Windows to facilitate this bridge between Legato and the data source. The ODBCConnect function takes a string as its parameter, but this string must be formatted in such a way as tell Legato how to interface with the ODBC connector and the data source. Here, we want to use the Microsoft Excel Driver. For more information on how to format this parameter string, see MSDN and/or the Legato Documentation.

Because we’re going to be using this connection to access data via a handle, it’s important to check if there was an error, particularly given the careful parameter setup that must be provided. If there was an error, we can alert the user and exit our script. If not, we can proceed.

Once we have a valid connection, we can use it to access some data. We do this via the SDK function ODBCQuery. This function accepts a SQL query as a string. Here we want to pull all fields (columns) from the spreadsheet called “cusips” within our Excel file. The syntax around the name of the sheet is necessary for Excel sheets (i.e., [cusips$]). We could optionally look for a specific CUSIP number. This appears in the commented section of the query string that includes a WHERE clause. Again, nesting the name of the column “cusip” in brackets (“[” and “]”) is necessary for Excel but perhaps not other data source types. Use the SQL syntax and formatting supported by the data source.

      odbc_query = ODBCQuery(odbc_handle, "SELECT * FROM [cusips$]"/* WHERE [CUSIP] = 'G39637106'" */);

Now that we have data, we can do something with it. Our script iterates through the result using the ODBCFetchArray SDK function. This returns the current row inside an ODBC Legato Results object and stores it in our string array. This is just one of many methods to access the contents of the ODBC Results Object. Check the Legato Documentation for other functions to retrieve information.

      res_row = ODBCFetchArray(odbc_query);
      while (ArrayGetAxisDepth(res_row) != 0) {
        msg = FormatString("%9s %-30s %s", res_row[0], res_row[1], res_row[2]);
        AddMessage(msg);
        res_row = ODBCFetchArray(odbc_query);
        }

Using a while loop, we can move row by row through the results, printing each row line by line. When we reach a row with no contents (an empty string array), we finish by closing our handles to our ODBC objects. The results look like this:

G39637106 GOLDEN OCEAN GROUP LTD         SHS
00900T107 AIMMUNE THERAPEUTICS INC       COM
  9128307 AIR METHODS CORP               COM PAR $.06
03834A953 APPROACH RESOURCES INC         PUT
15117N404 CELSION CORPORATION            COM PAR $0.01
26152H901 DRDGOLD LIMITED                CALL
33938T104 FLEXIBLE SOLUTIONS INTL INC    COM
421906958 HEALTHCARE SVCS GRP INC        PUT
46429B900 ISHARES TR                     CALL
55276F107 MCBC HLDGS INC                 COM
615369905 MOODYS CORP                    CALL
69318J100 P C CONNECTION                 COM

This simple script demonstrates a very powerful tool. Through an ODBC connector, Legato can access other data sources and retrieve and manipulate the data inside them. This gives you the capacity to interface with databases as you need to, say to import data from a SQL database and add it to tables inside your EDGAR filings.  Later on, we can examine some more useful applications of ODBC programming in Legato.

Maggie Gardner joined Novaworks in the summer of 2016 but has been working with Legato since its release in 2015. She has over ten years experience programming in SAS, PHP and C++.

Additional Resources

Novaworks’ Legato Resources

Legato Script Developers LinkedIn Group

Primer: An Introduction to Legato