SQLIO PLB Language Changes

9.5

The following PLB Languages changes affect the SQLIO operation within a program.

OPEN FILE/AFILE/IFILE

PREP FILE/AFILE/IFILE

ERASE

An <sql> tag identifier may be appended to a file name for the OPEN/PREP/ERASE instructions. For an AFILE, the <sql> tag is appended to the '.aam' file name. For an IFILE, the <sql> tag is appended to the '.isi' file name.

When SQLIO support is being used, the {keyspecs} and {maxreclen} parameters in the PREPARE instruction for an AFILE or IFILE are not used and do not alter the SQLIO schema definitions. However, the {keyspecs} are still validated by normal native index key spec processing in the runtime.

When the <sql> tag identifier is detected by the runtime, the SQLIO performs the PLB OPEN/PREP/ERASE operation when there is a SQLIO schema that has been pre-defined and loaded into the default schema database for the runtime. If a SQLIO schema is not available for the requested operation, an I86 error occurs. In addition, the <sql> tag identifier can have specialized attributes that override the current SQLIO schema settings. If the <sql> attribute is the same as defined in the current schema settings, the attribute is not used. Also, an <sql> attribute does not permanently change the current schema settings. The <sql> tag attributes are defined as follows:

<Sql> Tag Attributes

Tag Attribute	This tag ...
records={rowcount}	specifies the number of rows obtained when sequential read operations are performed. The records attribute is used for READ sequential, READKS, READKP, READKG, and READKGP PLB instructions. The {rowcount} can have a value from one(1) to the maximum number of rows allowed by the SQL database engine being accessed. A invalid value results in an appropriate I86 error.
template={filename}	overrides the actual file name being used for the OPEN/PREP.
file_name={filename}	overrides the file_name field specified in the Sun_Sqlio_Afile/Sun_Sqlio_Ifile system tables.
index_name={indexname}	overrides the index_name field specified in the Sun_Sqlio_Ifile system table.
table_name={tablename}	overrides the table_name field specified in the Sun_Sqlio_file system table.
db={databasename} or database={databasename}	specifies the name of the database connection to use to open the database table. This tag overrides the SQLTABLEDB setting.
NoTxtCheck	specifies that the text file name specified is not validated against the schema data. In this case, the schema data file_name is always used without any validation

Note:

A FILE variable allows the records, template, table_name, and database attributes.
An IFILE variable allows the records, template, file_name, index_name, table_name, and database attributes.
An AFILE variable allows the records, template file_name, table_name, and database attributes.
An ERASE instruction allows the index_name, table_name, and database attributes.

Random SQLIO Access

Random access is based on using the sun_recno field in each row. This field is automatically maintained by the SQLIO system. When a random access is performed, the value is incremented by one and used to match against a rows sun_recno field in the application data table.

ERASE

Erase looks in the Sun_Sqlio_File and the Sun_Sqlio_Ifile system tables for a name that matches. If found in the Sun_Sqlio_File table, the associated table will be dropped. If found in the Sun_Sqlio_Ifile table, the associated index will be dropped.

RENAME

This is not supported for SQLIO operations.

RECORD Locking

Record locking is not support for SQLIO.

SCHEMA

A 'FLAGS={dnumnvarlit}' bit mask value is required to load a SQLIO schema file. The FLAGS bit mask value is defined as follows:

Value	Meaning
0x8	This bit mask value identifies that the schema file is formatted as a SQLIO xml file. This bit mask value may be used with the IMPORT and EXPORT keywords.

FPOSIT

The current position returned for the record position is one less than the sun_recno field of the current row in the application data table.

REPOSIT

The value given positions to the row with a sun_recno field that is one greater that the given value.

SETMODE

New SETMODE controls for use with SQLIO are:

Control	Meaning
*OPENUSESQL={sqltag}	The use of this keyword allows all filenames in OPEN statements to have the {sqltag} data appended to the name. If <sql> is not specified in the file name for a PLB OPEN instruction, the {sqltag} data will be enclosed as <sql {sqltag}> and appended to the file name.
*PREPUSESQL={sqltag}	The use of this keyword allows all filenames in PREP statements to have the {sqltag} data given appended to the name. If <sql> is not specified in the file name for a PLB PREP instruction, the {sqltag} data is enclosed as <sql {sqltag}> and appended to the file name.
*SQLSCHEMADB={dbname}	This is the name of a database connection from the Sun_Sqlio_Databases system table that is the default connection for obtaining schema information from the Sunbelt system tables.
*SQLTABLEDB={dbname}	This is the name of a database connection from the Sun_Sqlio_Databases system table that is the default connection for accessing application data tables.

GETMODE

New GETMODE controls for use with SQLIO are:

Control	Returns the current
*OPENUSESQL={sqltag}	setting that has been stored using the SETMODE *OPENUSESQL instruction.
*PREPUSESQL={sqltag}	setting that has been stored using the SETMODE *PREPUSESQL instruction.
*SQLSCHEMADB={dbname}	database name that is being used as the default connection for obtaining schema information from the Sunbelt system tables.
*SQLTABLEDB={dbname}	database name that is being used is the default connection for accessing application data tables.

WRITE

If an SQLIO Schema File Format column definition has the <File> Schema Record <nullable> tag set to a non-zero value, this allows a column item in the SQL table to be empty after a PL/B WRITE operation. In this case, the following SQLIO behaviors are implemented:

For a PL/B WRITE instruction, the SQLIO operations execute to detect when blanks are being written for all of the column data. When the column data being written is all blanks, the SQLIO action is to exclude the column from the SQL INSERT operation used to perform the PL/B WRITE operation. In this case, a SQL column item remains in a SQL empty state after the PL/B WRITE operation. This behavior is ONLY used when the <nullable> schema definition is enabled with a non-zero value.
Additionally, if a SQL column is set to enable <nullable> for a Timestamp, Date, or Time SQL column type, the SQLIO operations for a PL/B WRITE instruction execute to detect when column data is all '0' or '9' characters. When all '0' or '9' characters are detected, the column is excluded from the SQL INSERT operation used to perform the PL/B WRITE operation. In this case, a SQL column item remains in a SQL empty state after the PL/B WRITE operation. This behavior is ONLY used when the <nullable> schema definition is enabled with a non-zero value.