Using ODBC to retrieve data from your database

Problem:

You want to retrieve and display data from your database using ODBC.

Solution:

Note: This article uses advanced features that are only available in the business version of Klipfolio Dashboard. Add <enterprise>true</enterprise> in your <setup> block in order to try them in Klipfolio Personal Dashboard.

The general steps for developing an ODBC Klip are the same as Klips using any other supported types of data source. That is:
0. Turn on Developer Mode by following the steps described in the Klip Developer Guide.
1. Specify the location of the source (connectionstring and query) using XML or JavaScript.
2. Write the <style> section that matches tags in the returned XML data. Klipfolio Dashboard uses this information to display the data in your Klip. The tags you use here is case-sensitive.

Note: Your stylesheet must match your own data. Connectionstring, query, and the <style> block must all be updated when using Sample ODBC Klip using XML and Sample ODBC Klip using JavaScript.

In this Cookbook article, you will find the following sections:

• Should I use XML or JavaScript?
• XML example
• JavaScript example
• Stylesheet example
• How can I tell what the returned XML data looks like?
• What if I'm using a stored procedure?
• What if I'm using Active Directory?

For troubleshooting tips, see Why doesn't my ODBC Klip display any data?

Should I use XML or JavaScript?

To decide, consider the following:
XML:

• You simply add <odbc>, <connectionstring> and <query> parameters in the <contentsource> block. Klipfolio Dashboard uses that information to get the data from your database in XML, and displays it according to your stylesheet.
• Use XML parameters if your connection string and query string will remain static throughout the use of the Klip.
• If you run into problems and are unsure what is causing the Klip to not work as expected, it is best to use JavaScript, as it offers ways for you to output errors and debug statements into the Debug Window.

JavaScript:

• You write the entire steps from setting the connection string and query to getting the returned data from your database in XML format using Engines.ODBC and Engines.ODBC.Response APIs.
• Use JavaScript if you want to set your connection string and/or query dynamically. This would be useful when, for example, you want to ask your users to supply their own query strings through a search textfield.

In the examples below, these sample strings will be used:

• connection string: Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
• query: SELECT * FROM Production.WorkOrder WHERE ProductID < 400

XML example

1. Specify the connection string and query for your database in the <contentsource> block:

<contentsource>
    <odbc>
        <connectionstring>Provider=sqloledb;Data Source=myServerAddress;
                   Initial Catalog=myDataBase;User Id=myUsername;
                   Password=myPassword;</connectionstring>
        <query>SELECT * FROM Production.WorkOrder WHERE ProductID < 400</query>
    </odbc>
</contentsource>

JavaScript example

1. Leave the <contentsource> block empty or remove it entirely, as it will not be used.
2. Add <klipscript> to get the XML version of the data from your database as follows:

var odbc = 0;
function onRefresh()
{
    var query_resp = 0;
 
    if (!odbc)
    {
        // Set the connection string
        odbc = Engines.ODBC.newDataSource ("Provider=sqloledb;Data Source=myServerAddress;
                Initial Catalog=myDataBase; User Id=myUserName;Password=myPassWord;");
    }
 
    // If connection to the database is successful, proceed with the query
    if (odbc.connect ())
    {
        // Set up the database object with a query
        odbc.setQuery ("SELECT * FROM Production.WorkOrder WHERE ProductID < 400");    
 
        // Send the query to the database
        if (odbc.sendQuery ())
        {
            // If the query is successfully sent, get the response object in XML format
            query_resp = odbc.response.xml;
 
            // traceln( query_resp);
        }
 
        // If sending of the query to the database fails, output the error
        else {
            traceln( "Could not send the query: " + odbc.getLastError() );    
        }
 
        // Disconnect from the database
        odbc.disconnect ();
    }    
 
    // If connection to the database fails, output the error from the ODBC driver in the Debug Window
    else {
        traceln( "Could not connect to database: " + odbc.getLastError() );            
    }
    
    // Return the XML data for Klipfolio Dashboard to parse using <style> settings
    return Engines.Data.process (query_resp);
}

Stylesheet

Whether you use <contentsource> or <klipscript>, the returned data in XML format looks exactly the same: each row is formatted as <z:row> with columns listed as attributes within the <z:row> tag. For example:

<z:row WorkOrderID='88' ProductID='3' OrderQty='4600'
     StockedQty='4600' ScrappedQty='0' StartDate='2001-07-04T00:00:00'
     EndDate='2001-07-14T00:00:00' DueDate='2001-07-15T00:00:00'
     ModifiedDate='2001-07-14T00:00:00'/>
<z:row WorkOrderID='89' ProductID='324' OrderQty='1148'
     StockedQty='1148' ScrappedQty='0' StartDate='2001-07-04T00:00:00'
     EndDate='2001-07-14T00:00:00' DueDate='2001-07-15T00:00:00'
     ModifiedDate='2001-07-14T00:00:00'/>

Your stylesheet will then follow this format:

<style>
    z:row::attribute(<name of column header 1>) {
        ...
    }
    z:row::attribute(<name of column header 2>) {
        ...
    }
    ...
    z:row {
        type: item;
        definition: all;
    }
</style>

For example, using the above sample data, the stylesheet block would look like this:

<style>
    z:row::attribute(ProductID) {
        itemcol: 1;
    }
    z:row::attribute(OrderQty) {
        type: number;
        itemcol: 2;
    }
    z:row::attribute(StockedQty) {
        type: number;
        itemcol: 3;
    }
    z:row {
        type: item;
        definition: all;
    }
</style>

Note that the attribute names are case-sensitive.

How can I tell what the returned XML data looks like?

If you are unsure what the returned XML data looks like, or would like to make sure you are writing your <style> section correctly by taking a look at it, use the JavaScript way to set the connectionstring and query. This way, you can output the returned data to the Debug Window by commenting in traceln( query_resp);.

What if I'm using a stored procedure?

Your query string should look something like this:
XML:
<query>EXEC dbo.uspGetEmployeeManagers 6</query>
or
Javascript:
odbc.setQuery( "EXEC dbo.uspGetEmployeeManagers 6" );

What if I'm using Active Directory?

Your connectionstring should look like this:
XML:
Provider=sqloledb; Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;
or
JavaScript:
Engines.ODBC.newDataSource("Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;");

Klipfolio Dashboard automatically detects the user name and password.

For example Klips using the above settings, try Sample ODBC Klip using XML and Sample ODBC Klip using JavaScript.