SIMPOL Documentation

Chapter 25. Using Reports in SIMPOL
Prev Part X. Programming Data-Aware Form Programs Next

Chapter 25. Using Reports in SIMPOL

Table of Contents

In this chapter the four report engines will be discussed: sql1.sml, reportlib.sml, graphicreportlib.sml, and quickreportlib.sml. The second makes use of the first one, and both the Quick Report and Graphic Report engines make use of the the report engine, so much of what will be written about that engine applies to all of them.

[Note]Note

This chapter only discusses using the report engines programmatically.

Using the sqlq1 Type Directly

The sqlq1 type is where the true work for all the report engines takes place. This is the SQL92 engine. This engine supports a subset of SQL92 that is related to retrieving data from the database. It has a selectclause and a whereclause property. To run a query, there must be at least one column named in the selectclause. Then, the prepare() method must be called and assuming it did not generate an error, the results can be retrieved by calling the getrow() method until it returns .false. When using the various report types, you do not normally call the getrow() method, instead you would call the run() method. This will carry out the report, which will call the various event handling functions to produce the desired result.

The sqlq1 type is not normally used directly, though it can be quite handy. The drilldown() function from the drilldown.sml as well as the filter functionality for unlinked dataform1detailblock types both use this type.

Using SQL92 in SIMPOL

The SQL92 syntax supported in the sqlq1 type is:

  • [TABLE_NAME.]COLUMN_NAME, [TABLE_NAME.]COLUMN_NAME AS

  • AND OR

  • = > < >= <= <>

  • [NOT] LIKE '' ESCAPE

  • unary +, unary -, +, -, *, /, || (string concatenation)

  • POSITION( <string> IN <string> )

  • EXTRACT( YEAR | MONTH | DAY | HOUR | MINUTE | SECOND FROM <date-time-or-datetime>)

  • CHAR[ACTER]_LENGTH( <string> )

  • UPPER( <string> )

  • LOWER( <string> )

  • SUBSTRING( <string> FROM <start-position> [ FOR <length> ] )

  • TRIM( [ [ LEADING | TRAILING | BOTH ] [ <trim-char> ] FROM ] <string-to-trim> )

  • CAST( <value-expression> AS <data-type> )

  • ABS( <numeric-expression> )

  • CURRENT_DATE

  • CURRENT_TIME

  • CURRENT_TIMESTAMP

The COLUMN_NAME can be surrounded by double quote characters ("). This can be useful if the field name in the table contains one or more spaces (not recommended).

Here are some additional notes about working with dates, times, and datetimes:

  • Dates must be supplied in the format yyyy-mm-dd when expecting to evaluate them

  • Times must be supplied in the format hh:mm:ss[.ssssss] not all decimal places required

  • Datetimes must be supplied in the format yyyy-mm-dd hh:mm:ss[.ssssss]

To evaluate a date, time, or datetime, it needs to be prefaced by the appropriate operator:

  • DATE('2010-01-26')

  • TIME('23:21:55')

  • TIMESTAMP('2010-01-26 23:21:55')

The following key words are supported: AND, AS, BOTH, CHAR_LENGTH, CHARACTER_LENGTH, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DATE, DAY, ESCAPE, EXTRACT, FOR, FROM, HOUR, IN, LEADING, LIKE, LOWER, MINUTE, MONTH, NOT, OR, POSITION, SECOND, SUBSTRING, TIME, TIMESTAMP, TRIM, TRAILING, UPPER, YEAR.

For more information regarding the syntax of SQL92, see the numerous resources on the Internet. The following document is the most complete resource I have found to date: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt.

Prev Up Next
printform1 Summary Home Working with report1