Manual

0. Warning

This Documentation is not complete. It should help to get the program running if you have some experience with SQL. The next releases of this manual will include:

• a Step-by-Step guide through the menues
• an example with files and datasets for download

1. general Description

myTextReport needs a configuration-file for each job. This file defines all options that are needed. It can be loaded at runtime or with a command-line parameter at the shell. You can edit the configuration-file and then reload it into myTextReport to make the changes take effect.

The template-file is the source-file that will be cloned during the process. It must be in ASCII-format. Besides it's ascii-formatted data (that can be a plain text or formatted in any ASCII based language like HTML etc.) it contains SQL-statements, that must be coded in a syntax according to the definitions in the configuration-file.
Normally you would create the template file with a tool that fits to it's format first (like a WYSIWYG HTML-Editor or whatever) and then open it with an ASCII-editor to insert the myTextReport-commands.

myTextReport will read this templatefile and first search for the commands in it. The file will be stored into a list. Each list-item contains either text or a command. If the files contains a LOOP-command, then the loop will be dissolved into a secon list that is docked into the main list at the right place.

The prepared template-file will be copied then as often as defined in the configuration-file. In this copies al the sql-statements are sent to the mySQL-Server. Therefore first all the Variables that may have been defined are searched and replaced through their results. Then the other SQL-commands are run. myTextReport will ad the following to each command it finds: Limit i,1 where i reperesents the current row in the database-table. Statements that make use of variables will be run without this adition (This behaviour helps us whith some tricks when we don't want to have the LIMIT-Statement as we will see later).

The comletely solved file will now be saved to disk. Therefore the filename first has to be evaluated. Normally the target-filename will not be written into the config-file in plain text, because if more than one file is produced it would overwrite itself each time. Therefore target-filename defined in the configuration-file can contain an SQL-statement just like those in the template-file. In principle it's allowed to use all the features that can be used in the template-file, even if this normally wouldn't make much sense.
 
 

2. The configuration-File

The config-file must be edited for each job. It normally has the suffix .cfg, but you can use any other suffix if oyu like. The config-file is a ASCII-file.

2.1. Syntax

Every Item in this file is coded like this:

option='value'

Warning: myTextReprot has no apropriate Exception-Handling. The Syntax in the config-file is never checked. It may happen that the program thows runtime-errors when it comes to wrong coded entries either in the config-file or the template. Thanks to Java this normally will not cause any problems.

2.2. Entries

The cofiguration-file defines all parameters needed to do the job. These are:

2.2.1. Connecting to the Database
 

db_server='127.0.0.1'
	The IP-adress or Hostname of the mySQL-Server. If your MySQL is running on the same machine as this program, then use 127.0.0.1' or 'localhost'.
db_user='myTextReport-user'
	The username to log into the mySQL-Server.
db_database='test'
	The Database we want to connect to
db_table='test_tab1'
	The table of this database, that will be our 'main source'. That does not mean, that data from other tables is not accessible, but thorugh this main table we define which datasets have to be processed.
db_startrow='0'
	The first row of the mentioned main table, that has to be processed. Minimum value is 0
db_endrow='-1'
	The last row to process. If you want to read all rows, type -1 here.

2.2.2. Syntax Settings
 

sy_starttag='{SQL'
sy_stoptag ='SQL}'
	Thos two items define the Syntax of each command written into the template-file. For details see below.

2.2.3. Files
 

fn_template='f:\daten\template\template01_local.html'
	Full path and filename of the template-file.
fn_outputfilename='f:\daten\{SQL SELECT id_member FROM members SQL}.html'
	Full path and file-name of the target-file. Attention: As normally more than one file will be produced, it's necessary to define a SQL-command within this String. The syntax will be explained below. As you can see, the SQL-statement is surrounded by the start- and stop-tag that have been defined before.
txt_filter='html'
	Special characters in result-strings comming from the database can be converted with filters. The only filters defined yet are 'html' and 'rtf'. Any other filter-name will be ignored. The dummy-definition for no filter is 'plain'. It's quite easy to implement new filters if you need one!

 
 

3. The Template-File

In a ASCII coded template-file (thatr may be formatted in any ASCII-based description language) SQL-statements will be inserted, that can be processed by myTextReport. Thos SQL_Statements have to be surrounded by the control-chars defined in the config-file. In our example that is  {SQL and SQL}. Spaces between those control-chars and the SQL-statement is not necessary but possible.

Warning: myTextReprot has no apropriate Exception-Handling. The Syntax in the template-file is never checked. It may happen that the program thows runtime-errors when it comes to wrong coded entries either in the config-file or the template. Thanks to Java this normally will not cause any problems.
 

3.1. Simple SQL-Statements

This is a simple SQL-statements:

{SQL SELECT name FROM members SQL}

myTextReport will add Limit i,1 beforre sending it to mySQL, where i stands for the current row of the table that is processed.

A more complex statement would look like this:

{SQL SELECT name FROM members WHERE gender=1 ORDER BY NAME DESC SQL}

Not only the SELECT statment can be used:

{SQL SHOW COLUMNS FROM membersSQL}
 

3.2. Variables

It's often necessary to use variables if you wnat to define relations between to tables in a database. If you want to define a variable in a template-file, add "-VAR" and "VAR-" to the control-chars:

{SQL-VAR v_prio=SELECT prio FROM projects VAR-SQL}

When you use this variable, put it in $-characters:

{SQL SELECT string1 FROM priority where id_priority=$v_prio$SQL}

You can define a vairable at any position of a file. I prefer to define a variable short before it's first use. The definition itself will not leave any footprints in the result-files.

3.3. Using Loops
 

Whenever you want to insert mor than one result-item of a SQL-Statement, you will need a loop. This may be a simple list or a relational query. A loop can contain variables, sql-commands an text. The loop is defined through the addition of -LOOP and LOOP- to the control-char:
{SQL-LOOP
{SQL-VAR v_prj=SELECT id_project FROM projects VAR-SQL}
{SQL-TXTName: TXT-SQL}
{SQL SELECT name from members WHERE ref_id_project=$v_prj$ SQL}
{SQL-TXT Vorname: TXT-SQL}
{SQL SELECT fname from members WHERE ref_id_project=$v_prj$ SQL}
{SQL-TXT Email: TXT-SQL}
{SQL SELECT email from members WHERE ref_id_project=$v_prj$ SQL}
{SQL-TXT
TXT-SQL}
LOOP-SQL}
 

Note:

• Variables, that shall be uses within a loop, must be defined in the same loop. They are only valid inside this loop.
• Text-Elements inside of a loop are defined by the parameter TXT. That means: Every element inside a loop is marked through another pair of control-characters!
• The TXT-parameter can also be used outside of a loop, but this doesn't make much sence.