SQL Data Source

Introduction

SQL data source is used to load data from live SQL databases.

Setup

Before using SQL database as a data source for Davisor Chart, a database connection must be configured. There are two ways to configure the database connection, using direct JDBC connection or using pooled JDBC 2.0 data source. SQL data source can also access a prepared result set directly.

Direct Connection

For direct connection following attributes are needed: url (db url), driver (name of JDBC driver class), user (db user name) and password (db password).

  datasource="jdbc:mysql://db.davisor.com/chart,org.gjt.mm.mysql.Driver,chart,chart"

Pooled DataSource

The J2EE platform specification requires J2EE application servers to make the DataSource (javax.sql.DataSource) implementation (a connection pool for JDBC connections) available. The JDBC DataSource is configured as an JNDI resource (refer to your application server's manual for how this is done). Normally, DataSource configuration includes a JNDI name, a JDBC driver name, a database URL, and a database user name and password. When the resource is configured properly, it can be used by the application using the JNDI name.

The defined resource must be "introduced" to the application that will use the resource. This is done by adding <resource-ref> block to "web.xml" or "ejb-jar.xml" file, depending on the Chart usage model. Here we have named the DataSource with the JNDI name: "jdbc/chart".

A typical setup (in web.xml):

<resource-ref>
  <resource-ref-name>jdbc/chart</resource-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

A typical setup when using Davisor Chart EJB (in ejb-jar.xml):

<session>
  <ejb-name>chart</ejb-name>
  ...
  <resource-ref>
    <resource-ref-name>jdbc/chart</resource-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
</session>

Please see the XML files that comes with the download package for more examples.

Prepared ResultSet

SQL data source can also access ready result sets. This can be done in a JSP page, for example.

The result set should be passed to the data source with a keyword matching the source attribute. In a JSP page, this is done by adding the result set to the request object.

The following readies a result set

<%
String expression = "SELECT * FROM MOBITONESTOCK";

Class.forName("org.hsqldb.jdbcDriver");
Connection conn = DriverManager.getConnection("jdbc:hsqldb:/tmp/mobitone/mobitone","sa","");

Statement st = null;
ResultSet rs = null;

st = conn.createStatement();  
rs = st.executeQuery(expression);

request.setAttribute("myresultset", rs); 
%>

and the result set is selected by setting the source attribute:

<data>
   <sql source="myresultset"/>
</data>

When using this approach, one should excplicitely close the database connection:

<%
st.close();
conn.close();
%>

Examples

When database connection is configured a SQL select statement can be used to load data from the database for the chart. Here is a typical select query for a transaction database.

<data>
  <sql source="jdbc/chart" query="SELECT SUM(price) as priceSum, type from mobitoneCall GROUP BY type ORDER BY priceSum DESC"/>
</data>

Sum of call prices grouped by call type.

Show XML Edit Chart