Generate Text

The Generate Text tool can be used to generate any text that contains table and/or column names. You specify a “skeleton text” specification – AQT will substitute (and repeat) the table and column names at the places you specify.

This can be used for generating:

lists of table or column names (for importing into reports or spread-sheets)
SQL statements or utility statements involving a list of tables
program source
HTML source for displaying / editing a table

This tool is accessed by Tools > Generate Text from the Database Explorer. The Generate Text tool operates on the contents of the Object Display and Details Display of the Database Explorer, so make sure these are populated.

Generate Text is a very powerful tool. Once you become familiar with how it works, it can save you a large amount of time.

The best way of understanding how this works is by an example. In the text window type Table name is $2 then click on Generate. The following text will be generated:

Table name is Cust_Orders

Table name is Customer_Contact

Table name is Customer_Details

Table name is Order_Details

Table name is Order_Line

Table name is Part_Details

Notice that where you coded $2 the table name has been substituted, and this line has been repeated for every table in the table list.

For other example of the use of this, click on the Examples drop-down list in the Generate Text Window, and select one of the items. These will show you the types of things you can generate with this utility (though they may not necessarily be examples of working code).

The substitutional parameters you can specify are as follows. These relate to the values displayed in the grids in the Database Explorer:

Parameter	Description	Note
$1	Schema name	value in Database Explorer
$2	Table name	value in middle grid
$3	Column name	value in right grid
$4 - $9	Type, Length, Scale etc	other columns in right grid
$-3	Column names with underscores replaced with minus signs	For COBOL users only. This will give you the Cobol host variables names.
$0	Number of table/column names to be generated.
$n	Sequential number of the table/column name
$fn	A function	see the following Functions section for options
<t>	Tab character

Text generation options

There are a couple of options for the way the text is generated. The examples demonstrate the use of this. Alternatively you can try them to see their effect.

Repeat What?

This option is only relevant when your text consists of more than one line. It specifies what to repeat when you code, say, a $3 in a line – just the line containing the $3, or the entire text.

Delimit Generated Names With

Set the delimiter to be used when a parameter is substituted and repeated. Options are:

Options	Result	Notes
newline	each item is placed on a new line
comma plus newline	when using this option you won't get a comma after the last item	This can be useful when generating a comma-delimited set of names. It is preferable to use this rather than putting a comma in your skeleton text
comma	actually comma plus blank
tab		Useful if you are generating a list of names to be pasted into Excel.
none

Functions

You can use the $fn keyword to specify functions to do simple string manipulation and conditional processing. The syntax of this is:

$fn(function, parm1, parm2, parm3, parm4)

The number of parms required depends on the function. Strings do not need to be in quotes.

The following functions have been implemented:

$fn(lcase,x)	converts x to lower case
$fn(ucase,x)	converts x to upper case
$fn(scase,x)	converts x to sentence case
$fn(trim,x)	removes leading and trailing blanks
$fn(repl,x,y,z)	replaces all occurrences of string y in x to z
$fn(min,x,i)	returns the minimum of x and i
$fn(max,x,i)	returns the maximum of x and i
$fn(left,x,i)	returns the left-most i characters of x
$fn(right,x,i)	returns the right-most i characters of x
$fn(mid,x,i,j)	returns a substring from x, starting at i, length j
$fn(instr,x,y,z,w)	returns z if x contains string y, otherwise returns w
$fn(ifblank,x,y,z)	returns y if x is blank, otherwise returns z
$fn(ifnum,x,y,z)	returns y if x is a numeric data type, otherwise returns z
$fn(ifeq,x,y,z,w)	returns z if x = y, otherwise returns w
$fn(decode,x,comp1,val1,comp2,val2,val3)	if x=comp1, returns val1. if comp2=val2, returns val2. Up to 8 sets of value can be compared. If no values matched, returns the final value. Example: $fn(decode,$4,varchar,c,integer,i,datetime,d,other)
$fn(after,x,y,spos,n)	Searches string x for the nth occurrence of string y and returns the text after this occurrence of string y. spos gives the start position for the search. If negative, the search will be done from right to left starting from the end of the string. spos is optional, if not given a value of 1 will be used. n is optional and defaults to 1. If string y is not found anywhere in string x, all of string x is returned. If string y is found, but occurs less than n times, the last position where string y is found will be used. Examples: $fn(after,$2,"_") returns text after the first "_" $fn(after,$2,"_",-1,2) returns text after 2nd "_" from right
$fn(before,x,y,spos,n)	Similar to after, except that the text before the location of the search text is returned
<delim>spec	This allows you to change the delimiter while your script is running. spec can take values <cr> (new line), <b> (blank) <tab> tab. Example: <delim><cr>

Notes:

in these examples, i and j are numeric. If a non-numeric value is passed it will be interpreted as zero.
$ifnum needs some clarification. It doesn't check whether x is numeric, but whether x equals one of: Integer, Smallint, Bigint, Long, Double, Number (eg. a string representing one of the numeric data types). Normally x would be $4.
if one of your parameters (x, y, z etc) contains either a comma or a bracket then AQT will get very confused and probably produce the wrong result. Enclose the parameter in single or double quotes.
if a parameter is omitted, it is assumed to be a null string. For example $fn(repl,$3,_) will remove all _'s from the column name.
functions can be nested - for example $fn(trim,$fn(lcase,$3))
additional functions are easily implemented – please let us know if you want any others.
Click on Examples in the Generate Text window to see examples using these functions.

Save / Retrieve

These options can be used to Save/Retrieve your text generation specification.

Font

This allows you to set the Font in the text box to non-proportional (eg. fixed width) font. This is useful if you want to line up multiple lines (such as when you are generating code for Cobol).

Generate to File

Use this if you want to output the text generation directly to a file. This is useful if your output is large (> 64 kb), as text larger than this cannot be displayed in the result text-box.