Retrieving a Template for a Data File

Description

In BBj 5.0 and higher, the SQL engine returns a template derived from the columns in the SELECT list of the SELECT statement. In some cases, this template is different from the template used to process a raw record from the data file. For example, if a SELECT statement includes a date type column, the SQL engine returns a template with that column defined as I(4), even if the actual raw data type in the dictionary is C(3). The reason is that the SQL engine always returns date values as Julian numbers for consistency. Therefore, when using a string template to define the record layout, the program should not depend on calling SQLTMPL after executing an SQL statement.

The GETTABLEINFO command in the BBj SQL engine returns a string template that perfectly matches the information defined in the data dictionary. To access this functionality, execute the GETTABLEINFO command in a normal SQL statement. The GETTABLEINFO command returns a result set that includes the string template for the specified table and the full path to the table's data file. For example, to get the string template for the CUSTOMER table from a BBj program, do the following

 

SQLOPEN(1)"ChileCompany"
SQLPREP(1)"GETTABLE INFOFOR customer"
SQLEXEC(1)

DIM A$:SQLTMPL(1)
A$ = SQLFETCH(1)
REM Print the template

PRINT A.TEMPLATE$
REM Print the full path to data file

PRINT A.FILENAME$

 

See also SQLTMPL() Function - Get SQL Result Template.