You want to retrieve and display data from your database using ODBC.
The general steps for developing an ODBC Klip are the same as Klips using any other supported types of data source. That is:
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.
Important Notes
For troubleshooting tips, see Why doesn't my ODBC Klip display any data?
To decide, consider the following:
In the examples below, the following example strings will be used. For your ODBC Klip, be sure to use the correct syntax that is appropriate for your database server setup.
<html>
<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>
</html>2. Skip to the Stylesheet section below.
var odbc = 0;
function onRefresh()
{
var query_resp = 0;
if (!odbc)
{
// Set the connection string using the syntax that is appropriate for your particular server setup
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() );
odbc.disconnect ();
}
// 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() );
odbc.disconnect ();
}
// Return the XML data for Klipfolio Dashboard to parse using <style> settings
return Engines.Data.process (query_resp);
}<html>
<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'/>
</html>
<style>
z:row::attribute(<name of column header 1>) {
...
}
z:row::attribute(<name of column header 2>) {
...
}
...
z:row {
type: item;
definition: all;
}
</style>
<style>
z:row::attribute(ProductID) {
itemcol: 1;
name: "id";
}
z:row::attribute(OrderQty) {
type: number;
itemcol: 2;
name: "orders";
}
z:row::attribute(StockedQty) {
type: number;
itemcol: 3;
name: "instock";
}
z:row {
type: item;
definition: all;
}
</style>Note that the attribute names are case-sensitive.
Also, note the use of the name property in each column, e.g. name: "id";. This is not a required setting; however, if you ever want to refer to any of the columns, such as from a column that displays the results of a mathematical formula, for specifying which columns to use in a chart or from the klipscript, you can use these names.
(For more on getting data from complex XML, also see the how-to article, Extracting data from inside an XML tag.)
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);.
Your query string would look something like this:
XML:
<query>EXEC dbo.uspGetEmployeeManagers 6</query>
or
Javascript:
odbc.setQuery( "EXEC dbo.uspGetEmployeeManagers 6" );
Your connectionstring would look something like the following. (Refer to your database server's documentation for the appropriate syntax for your particular server setup):
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.
