Programming Guide

CATQUERY

Concatenates a full result row to a Prolifics variable or file

Synopsis

DBMS [WITH CURSOR cursor CATQUERY TO prolVar 
	[SEPARATOR "text"] [HEADING [ON | OFF] ]
 
DBMS [WITH CURSOR cursor] CATQUERY TO FILE file 
	[SEPARATOR "text"] [HEADING [ON | OFF] ]
 
DBMS [WITH CURSOR cursor] CATQUERY TO FILENAME fileVar \
	[SEPARATOR "text"] [HEADING [ON | OFF] ]

Arguments

WITH CURSOR cursor
Name of declared SELECT cursor. If the clause is not used, Prolifics uses the default SELECT cursor.

TO prolVar
Name of destination Prolifics variable.

TO FILE file
Name of destination text file. If the file already exists, it is overwritten when the SELECT is executed.

TO FILENAME fileVar
Name of variable whose value is the name of the destination text file. If the file already exists, it is overwritten when the SELECT is executed. Use this variant for filenames that include spaces and/or punctuation.

SEPARATOR "text"
Specifies text to use to separate column values in a result row. The default is two blank spaces.

HEADING ON
Specifies that Prolifics put a heading at the beginning of the select results. This is the default for a catquery to a file. The heading is built using the column names or any aliases assigned to the cursor. The maximum length of a heading is 255 characters. Any additional characters are truncated.

HEADING OFF
Specifies that Prolifics not use a heading. This is the default for a catquery to a Prolifics variable.

Description

The result columns of a SELECT statement are usually mapped to individual variables. Use CATQUERY to map full result rows to a variable's occurrences or to a text file.

Prolifics attempts to format the column values by searching for Prolifics variables of the same name and using their attributes for length, precision, and date-time or currency specifications. The application can override any default formatting with the command DBMS FORMAT.

The catquery for a cursor is turned off by executing the DBMS CATQUERY command with no arguments. Closing a cursor also turns off the catquery. If a cursor is redeclared without being closed, the cursor keeps the catquery destination as the cursor's SELECT destination.

Catquery to a Variable

When the catquery destination is a Prolifics variable, Prolifics concatenates a result row and writes it to prolVar when the SELECT is executed. If prolVar is an LDB or onscreen array, the result rows are written to the array occurrences. If there are more result rows than occurrences in prolVar, use DBMS CONTINUE to fetch the additional rows.

If the clause HEADING ON is used, a heading is created by using the cursor's aliases and column names. If prolVar has two or more occurrences, the heading is put in the first occurrence of prolVar.

Catquery to a Text File

When the catquery destination is a text file, all the result rows are written to the specified text file when the SELECT is executed. Any existing file with the same name is overwritten. If a result row is longer than the page width, the row wraps to the next line.

Note: Only 1000 characters per row can be written to a file if the database column's type is defined as FT_VARCHAR. If more data output are required, consider outputting results to a report.

If the name of the file includes spaces and/or punctuation, use the FILENAME variant to name a variable that contains the file name.

If aliases have been specified for the cursor, those aliases are used as column headings in the text file. If there are no aliases, the columns' names are used. If the HEADING OFF clause is used, a heading does not print.

Since all result rows are written to the file, the DBMS CONTINUE commands should not be used with a CATQUERY TO FILE cursor while the file is open.

The file remains open until DBMS CATQUERY is reset or the cursor is closed.

Example

// Select a customer's first and last name
// and concatenate the values in the field "fullname".
 
DBMS DECLARE name_cursor CURSOR FOR \ 
	SELECT last_name, first_name FROM customers \ 
	WHERE cust_id = :+cust_id
DBMS WITH CURSOR name_cursor CATQUERY TO fullname \
	SEPARATOR "," 
DBMS WITH CURSOR name_cursor EXECUTE

// Select the maximum value from the column "cost" 
// and write it to the JPL variable "hi_cost" 
// formatting it with currency edit saved with the 
// LDB variable "money_var".
 
vars hi_cost
DBMS DECLARE max_cursor CURSOR FOR \ 
	SELECT MAX(price) FROM pricecats
DBMS WITH CURSOR max_cursor CATQUERY TO hi_cost
DBMS WITH CURSOR max_cursor FORMAT money_var
DBMS WITH CURSOR max_cursor EXECUTE
 
// Write the results of the default SELECT cursor 
// to a file with heading. Turn off ALIAS and CATQUERY
// when finished.
 
proc file_list
DBMS CATQUERY TO FILE titlelist
DBMS ALIAS title_id "Title ID", name "Title",\ 
	film_minutes "Length", pricecat "Price Category" 
DBMS SQL SELECT title_id, name, film_minutes, pricecat \ 
	FROM titles
DBMS CATQUERY
DBMS ALIAS
return 
// Write results of the default SELECT cursor
// to a named file
proc title_list
vars fname
fname = "my titles file"
DBMS CATQUERY TO FILENAME fname
DBMS SQL SELECT * FROM titles
 
DBMS ALIAS, 
DBMS FORMAT