Developers

HOME DEMONSTRATIONS DOCUMENTATION SUPPORT SQL AND CHARTS DOWNLOADS

SQL and Charting

You can draw charts on the "Charts" tab of a SQL script window using the Entrance PLOT command. You can make charts almost as easily as writing a SELECT statement, and then label or paint points in those charts using SQL scripts to determine the criteria. This tutorial shows you how.

All examples work with the basic "Entrance" package, version 0.80.1 or later.

PLOT and Charts

A simple PLOT command has this form:

PLOT chart_type

        chart_series_types

SELECT

      MySQL_select_body

For example:

   PLOT LINECHART

       XLABELS, BLUE LINE, RED LINE

   SELECT label, value1, value2 from foo;

The SELECT part of a PLOT command is simply a MySQL SELECT command. Anything valid as a MySQL SELECT statement is valid here as well. Chart series types are expected to match up with columns in the result set generated by the SELECT. You can use SKIP when you want to skip one of the columns.

You can also modify other chart features using the WITH keyword. For example, to add a title, legend, and notes to our simple example we could use this script:

PLOT LINECHART

       XLABELS, BLUE LINE, RED LINE

     WITH

       TITLE "The first title line"

       NOTE  "The first note line"

       NOTE  "The second note line"

       LEGEND

   SELECT label, value1, value2 from foo;

Entrance charts can have an unlimited number of note and title lines.

The complete syntax for PLOT is online here, but these basics should be enough to get you started.

Color Overrides and "Data Painting"

Entrance color overrides make it possible for MySQL scripts to paint colors in charts. (Entrance supports color overrides for XY and Correlation charts. Entrance Pro supports them for all chart types)

For example, this script draws an XY Chart with color overrides:

  PLOT XY

    X, BLUE FILLED TRIANGLE, COLOR OVERRIDE

  SELECT x, y, highlight from foo;

If all of the entries in the "highlight" column are null or the empty string, this will produce an XY chart with blue triangles. However, we can use UPDATE commands to highlight some of the points with RED color and others with ORANGE (to make the highlights stand out more, we'll make the other triangles light blue):

  -- clear any existing highlights first

  UPDATE foo

    SET highlight = '';

  UPDATE foo 

    SET highlight = 'RED'

    WHERE x = y + 3;

  UPDATE foo 

    SET highlight = 'ORANGE'

    WHERE x = y;

  PLOT XY

    X, VERY LIGHT BLUE FILLED TRIANGLE, COLOR OVERRIDE

  SELECT x, y, highlight from foo;

The result is a chart like the one below (click here for the complete script):

To learn more about Entrance charts

PLOT command syntax is described here: http://dbentrance.com/plotsyntax.html.

Part 2 of this article describes painting data from the chart window.

Part 3 describes the use of "WHENLINES" to mark events on a time series chart.

FORWARD TO PART 2

Please note: This web page and other Entrance documentation web pages are copyrighted material and have not been released under the terms of the GPL.

MySQL is a registered trademark of MySQL AB in the United States, the European Union and other countries.

Last Modified June 2, 2007