Connecting to Data

This tutorial describes connecting a simple Corda® image to various data sources using Java* Server Pages (JSP).

Corda Builder™ saves the static data used when creating the graph as part of the Image Template file, but Corda Embedder lets you dynamically replace data in the Image Template file with data from a live data source. Corda Server™ automatically resizes scales and other objects in the image to support the new data.

There are three types of data sources: data files, HTML tables, and databases. This tutorial relies on sample data files and data sources in <product_root>\Resources\doc_root\image_templates\examples, but the same process is applicable to other data sources.

This tutorial is divided into the following sequential tasks:

A final section, Having Corda Server Query a Database Versus Doing it Yourself, discusses optional methods of querying databases.

Important: This tutorial discusses Corda images containing graphs. However, the same process applies equally well to Corda images containing maps.

Note: This tutorial takes approximately 30 minutes.

To connect to data stored in a data file

This task connects a Corda image to a comma-separated values (CSV) file. For those unfamiliar with CSV, it is a standard data format that is exportable by most database and spreadsheet programs, including Microsoft* Excel.

Note: This same process can connect a Corda image to tab-delimited and XML-formatted files.

The sample CSV file referenced in this task is <document_root>\examples\data\medals.csv.

  1. Open the appropriate JSP file in a text editor.

    The sample JSP file created in Publishing a Corda Image is named Chart1.jsp.

  2. Locate the code between the <!-- Begin Embedder Code --> and <!--End Embedder Code --> comments. This is the Java code that generates the Corda image.

  3. Comment out the following line of code by placing two forward slashes (//) in front of it.

    //myImage.pcScript = pcScript

  4. Insert the following line of code directly after the line of code commented out in the previous step:

    myImage.loadData("graph","examples/data/medals.csv");

    The second parameter is the location of the data file to import, relative to <product_root>\Resources\doc_root\image_templates.

    Note: You can also load files from URLs. For example, to use a CSV file accessible at http://www.mycompany.com/mydata?year=2002, enter the URL as the data file location instead of examples/data/medals.csv . However, before doing this, give Corda 7 permission to read from the mycompany.com domain. For more information, see Setting Path Permissions in the Corda 7 Install and Administration manual.

  5. Save the changes to the JSP file.

    <product_root>\Server\dev_tools\sample_code\JSP\Chart1_DataFiles.jsp is a sample JSP showing this type of modification.

  6. Reload the JSP containing the Corda image to see it with data from the CSV file.

    The data in your graph should now reflect the data in the medals.csv file.

    Note: Make sure the Corda Server is running. For more information, see Starting Corda Server in the Corda 7 Install and Administration manual.

    Replacing references to examples/data/medals.csv with the relative location of another data file loads data from any CSV, tab-delimited, or XML data source. For more information about data files and data file organization, see Connecting to Data Files.

To connect to data stored in an HTML table (Screen-scraping)

HTML tables are a common way to publish data to the Web. Corda 7 can browse to any Web page, locate a table within that Web page, and connect a Corda image to data from that table. This process is known as screen-scraping.

From a technical standpoint, connecting to data stored in an HTML table is very similar to connecting to data stored in a data file.

This task connects a Corda image to the following Web page: <document_root>\examples\html\data1.html . This Web page includes a data table.

  1. Open the JSP page in a text editor.

  2. Locate the line of code inserted in Step 4 of the previous task.

  3. Change this line of code so that it reads as follows:

    myImage.loadData "graph","examples/html/data1.html","","CordaData",""

    The HTML table command is identical to the data file command except for the fourth parameter, which specifies the title of the table to use from data1.html. The HTML source code for the table in data1.html, complete with a title attribute, is as follows:

    If the table didn't have a title attribute, we can just as easily use the table number in place of the title. In this case, the fourth parameter would be the number of the table within the Web page (the first table is "1", the second is "2", and so on). Thus, the line in our sample code can also read:

    myImage.loadData("graph","examples/html/data1.html","","1");

    Note: You can also load data from Web pages by specifying a URL rather than a relative file location. For example, to use an HTML table in a Web page at http://www.mycompany.com/stats2002.html, enter the URL as the data file location instead of examples/html/data1.html . However, before doing this, give Corda 7 permission to read from the mycompany.com domain. For more information, see Setting Path Permissions in the Corda 7 Install and Administration manual.

  4. Save the changes to the JSP file.

    <product_root>\Server\dev_tools\sample_code\JSP\Chart1_HTMLTable.jsp is a sample JSP showing this type of modification.

  5. Reload the JSP containing the Corda image to see it with data from the HTML table.

    The data in the graph should now reflect the data from data1.html.

    Note: Make sure the Corda Server is running. For more information, see Starting Corda Server in the Corda 7 Install and Administration manual.

    For more information about HTML tables, and HTML table organization, see HTML Tables.

To connect to data stored in a database

This task connects to data stored in a database. There are two ways of doing this:

Using Corda Embedder is the simpler method, but a manual query gives you more control over the data, as well as special effects such as drilldown and hover text. (These advantages are explained in greater detail in Having Corda Server Query a Database Versus Doing it Yourself.)

This task demonstrates both methods to connect a Corda image to data stored in the CordaSamples ODBC database included in Corda 7. It assumes that Corda 7 and IIS Web server are running on the same machine. If this is not the case, set up the CordaSamples database on the Web server (see Setting Up the Example ODBC Data Source in the Corda 7 Install and Administration manual).

  1. Open chart1.jsp in a text editor.

  2. If you haven't already, comment out myImage.pcScript = pcScript by preceding it with two forward slashes (//).

    Also, if you added any myImage.loadData statements in previous tasks, comment those out as well.

  3. Below the lines commented out in the previous step, add the following:

    myImage.setDBQuery("graph","org.gjt.mm.mysql.Driver","jdbc:mysql://localhost/CordaSamples","","","SELECT CONCAT(y2002.city,' ',y2002.teamname) AS team, y2000.wins AS '2000',y2001.wins AS '2001',y2002.wins AS '2002' FROM y2000, y2001, y2002 WHERE y2001.teamname = y2002.teamname AND y2001.teamname = y2000.teamname ORDER BY y2002.city");

    This line specifies three pieces of information for Corda Embedder: the database driver (org.gjt.mm.mysql.Driver); the database URL (jdbc:mysql://localhost/CordaSamples); and the SQL query (the string that begins with SELECT. If the MySQL database is not loaded locally, replace localhost in the data URL with the appropriate host name or address.

    The fourth and fifth parameters—both empty strings in Step 3—are used, respectively, to specify the database user name and password, if necessary. For example:

    myImage.setDBQuery("graph","org.gjt.mm.mysql.Driver","jdbc:mysql://localhost/CordaSamples","root","password","SELECT CONCAT(y2002.city,' ',y2002.teamname) AS team, y2000.wins AS '2000',y2001.wins AS '2001',y2002.wins AS '2002' FROM y2000, y2001, y2002 WHERE y2001.teamname = y2002.teamname AND y2001.teamname = y2000.teamname ORDER BY y2002.city")

    Modify this statement to connect to another database by replacing the example values with valid values (talk to the database administrator for help determining the appropriate values). For more information about using setDBQuery, see Importing Data Directly.

  4. Save the changes to the JSP file.

    For your convenience, an example JSP very similar to the one we've been editing has been saved as in the <product_root>\Server\dev_tools\sample_code\JSP\Chart1_Database.jsp.

  5. Reload the JSP containing the Corda image to see it with data from the database.

    The data in the graph should now reflect the data in CordaSamples.

    Note: Make sure the Corda Server is running. For more information, see Starting Corda Server in the Corda 7 Install and Administration manual.

    If the database data does not load, make sure that you are specifying the correct parameters for connecting to the database, including the driver name, URL, user name, and password. If you are using Corda 7's sample database, make sure it has been installed correctly (see Setting Up the Example MySQL Database in the Corda 7 Install and Administration manual), and that the appropriate database driver is installed.

    The remaining steps in this task manually connect to Corda 7's sample database and build a PCScript string. PCScript is a scripting language that allows you to specify data and formatting options for a graph. You can send a PCScript string to Corda Server when requesting a Corda image.

  6. If necessary, Open chart1.jsp in a text editor.

  7. Remove the setDBQuery line of code inserted in Step 3 above.

  8. Uncomment //myImage.pcScript = pcScript by removing the two initial forward slashes (//).

  9. Locate the pcScript =... line at the beginning of the graph code. This line specifies the graph data.

    This string consists of one graph.setCategories() and multiple graph.setSeries() statements. The setCategories() statement populates the first row of the Data Editor, whereas each setSeries() statement populates a new row in the Data Editor. For more information, see Data Input in the Corda Builder User Guide.

    Note: This is an over-simplification of these commands, but it is generally accurate.

    For more information about PCScript, see PCScript.

  10. To see how this string works, change some of these values (as we have done in the image below) and save the file.

    The <product_root>\Server\dev_tools\sample_code\JSP\Chart1_PCScript.jsp sample file contains settings as described in this task.

  11. Refresh the JSP in a Web browser to see how the data has changed.

    The remaining steps build the same PCScript string on the fly by manually querying the database and translating the result set into PCScript.

  12. In the JSP file, remove any text from the PCScript= line, so that the variable pcScript is assigned an empty string.

  13. Immediately below the PCScript line, insert the following lines of code:

    Class.forName("org.gjt.mm.mysql.Driver");
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/CordaSamples","","");
    java.sql.Statement statement = connection.createStatement();
    ResultSet rs = statement.executeQuery("SELECT y2002.city,y2002.teamname, y2000.wins, y2001.wins, y2002.wins FROM y2000, y2001, y2002 WHERE y2001.teamname = y2002.teamname AND y2001.teamname = y2000.teamname ORDER BY y2002.city");

    This creates a database connection and makes a query. The query is returned to the ResultSet object called rs. By replacing the strings in this example with the valid database parameters, this code segment can connect to any database.

  14. Below those lines, add the following statement:

    pcScript += "graph.setCategories(2000;2001;2002)";

    This code specifies data category names.

  15. Below that line of code, add the following while loop:

    while (rs.next()) {
    pcScript += "graph.setSeries(" + rs.getString("city") + " " + rs.getString("teamname") + ";" + rs.getString("y2000.wins") + ";" + rs.getString("y2001.wins") + ";" + rs.getString("y2002.wins") + ")";
    }

    This code cycles through the ResultSet object and builds the PCScript string. In conjunction with the sample database, this code creates the following PCScript string:

    graph.setCategories(2000;2001;2002)graph.setSeries(Alpine Green Shirts;91;102;102)graph.setSeries(Cedar Hill Miners;91;116;92)graph.setSeries(Dessau Ducks;70;39;37)graph.setSeries(Monahue Warriors;71;73;72)graph.setSeries(Smallsville Serpents;82;75;102)

  16. Immediately below the top line of the page, import the java.sql library by including this statement:

    <%@ page import="java.sql.*" %>

  17. Save these changes to the JSP file.

    The <product_root>\Server\dev_tools\sample_code\JSP\Chart1_ManualDBConnection.jsp sample file contains code as described in this task.

  18. Reload the JSP file in the Web browser.

    Notice that even though this process uses a different and somewhat simpler SQL query, the resulting Corda image looks exactly like the image from Step 5 above. This is because the PCScript correctly formats the results of the query.

Having Corda Server Query a Database Versus Doing it Yourself

The key difference between these two ways of connecting to a database is pre-processing versus post-processing. Pre-processing, using setDBQuery, requires an SQL query that produces precisely the same table created using Corda Builder. This can be difficult, especially if you aren't an SQL expert.

Post-processing, or querying the database manually and then building the PCScript, reduces the importance of the SQL query by using PCScript to format the database query correctly for Corda Server. However, the PCScript used to manually format the database query results is specific to that database query—meaning that the PCScript must be modified extensively to accommodate a different query. It is possible to create a generic version of the code that works for any data set, but this defeats one of the main purposes of post-processing, the flexibility to build custom PCScript from different result sets.

More experienced Corda 7 users recognize that this flexibility is extremely important when dealing with special effects such as drilldown, hover, and notes. These effects sometimes require knowledge of the data before creating the special effect. For example, it doesn't make much sense to have a bar in a bar graph pop up a message "Warning—price is too low" without knowing what the value of the bar is. The only way to know the value of a bar is to parse through the result set manually, so context-specific special effects rely on manual SQL queries and custom PCScript.