CATQUERY

Programming Guide

Concatenates a full result row to a Panther variable or file

Synopsis

DBMS [WITH CURSOR cursor] CATQUERY TO pantherVar
	[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, Panther uses the default SELECT cursor.
TO pantherVar
Name of destination Panther variable.
TO FILE file
Name of destination text file. If the file already exists, it is overwritten when the SELECT is executed. In Panther 5.50 and later file can be in quotes.
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. You can 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 Panther 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 Panther not use a heading. This is the default for a catquery to a Panther 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.
Panther attempts to format the column values by searching for Panther 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 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 Panther variable, Panther concatenates a result row and writes it to pantherVar when the SELECT is executed. If pantherVar is an LDB or onscreen array, the result rows are written to the array occurrences. If there are more result rows than occurrences in pantherVar, use 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 pantherVar has two or more occurrences, the heading is put in the first occurrence of pantherVar.

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. In Panther 5.50 and later, you can also quote file.
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 is not output.
Since all result rows are written to the file, the 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
return

// 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
return

// 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 QUERY 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 QUERY SELECT * FROM titles

DBMS ALIAS
DBMS FORMAT
return