SQLIO - Administrative Tables

9.5

The following list describes the administrative tables used by the SQLIO functions:

Sun_Sqlio_Afile

This SQLIO system table contains the schema information that associates a PLB program AFILE file reference to a SQL data table name. In addition, this table contains the AFILE key information that allows the runtime to identify the SQL data table columns associated with the key information. The columns defined for this system table are:

Column	Use
id	Integer unique row identification value.
name	Text column that contains the PLB AAM file name as specified in a PLB OPEN instruction.
file_name	Text column that identifies the TXT file name associated with the AAM file. The data in this column is used to look up information contained in the 'SUN_SQLIO_FILE' system table.
key_info	Text column that contains AFILE key specifications same as specified for an AFILE PREP instruction.
description	Text column for user information as desired.

Sun_Sqlio_Alias

This SQLIO system table contains information that allows a PLB program file name reference to be substituted by another program file name reference. After all substitution actions, the final file name reference must exist in the SUN_SQLIO_FILE, SUN_SQLIO_IFILE or SUN_SQLIO_AFILE system table exactly as specified by the final name. The columns defined for this system table are:

Column	Use
id	Integer unique row identification value.
name	Text column that is the file name specified in the PLB instruction.
alias_name	Text column that contains a name string that replaces the name column data.
description	Text column for user information as desired.

Sun_Sqlio_Columns

This SQLIO system table contains all column definitions defined for all PLB files to be processed using SQLIO operations. The information contained in this table identifies the expected fields and position in a record for a data file in a PLB application. The columns defined for this system table are:

Column	Use
id	Integer unique row identification value.
file_id	Integer unique row identification value of the associated Sun_Sqlio_File row.
name	Text column containing the name of the column.
description	Text column for user information as desired.
type	Integer column that specifies the PL/B type for a column. The values can be: 0 - PL/B data type is a DIM variable 1 - PL/B data type is a FORM variable. 2 - PL/B data type is an INTEGER variable.
offset	Integer column that specifies the zero based offset of the column in the PL/B record.
length	Integer column that specifies the length of the column in the PL/B record.
scale	Integer column that specifies the number of fractional digits in a field. The scale value is only applicable for a PL/B FORM variable type.
sql_type	Integer column that specifies the SQL type. This can be one of: 0 - Char data type with trailing spaces dropped. 1 - Char data type with binary collated sequence and trailing spaces are dropped. 2 - Char data type that is not case sensitive and trailing spaces dropped. 3 - Char data type. 4 - Char data type with binary collating sequence. 5 - Char data type that is not case sensitive. 6 - Integer data type. 7 - Decimal(length) or Decimal(length-1,scale). 8 - Real data type. Note: 1. All types are case sensitive unless they are documented to be not case sensitive. 2. If binary collated is not specified in the type description, the database default collating sequence is used. 3. If the ordering of row data relative to the keys being used give unexpected results, the SQL data type should be reviewed to make sure that the proper collating sequence is being applied.
zero_fill	Integer column that defines how the data for a SQL numeric column is to be filled when translating the data to a PL/B record field. A value of one causes the PL/B record field to be zero filled to the left of the column data. Otherwise, the field will be blank filled to the left of the column data.
unique_co	Integer column that specifies that the column only contains unique values. On a CREATE TABLE, a value of one will cause the 'unique' attribute to be used.
nullable	Integer column that specifies the nullable state. On a CREATE TABLE, a value of zero will cause the 'not null' attribute to be used.
format_mask	Reserved for future use.

Sun_Sqlio_Databases

This SQLIO system table contains information that define end-user database references that allows a connection to a specified SQL Database Engine.

Column	Use
id	Integer unique row identification value.
name	Text column that stores the user defined name for this database connection information.
description	Text column for user information as desired.
driver	Integer column that contains the same information as described by the PLB_SQLIO_DRIVER keyword for the INI configuration file.
sql_type	Integer column that contains the same information as described by the PLB_SQLIO_SQLTYPE keyword for the INI configuration file.
host	Text column that contains the same information as described by the PLB_SQLIO_HOST keyword for the INI configuration file.
user_name	Text column that contains the same information as described by the PLB_SQLIO_USER keyword for the INI configuration file.
pass	Text column that contains the same information as described by the PLB_SQLIO_PASS keyword for the INI configuration file.
conn	Text column that contains the same information as described by the PLB_SQLIO_CONN keyword for the INI configuration file.
ext	Text column that contains the same information as described by the PLB_SQLIO_EXT keyword for the INI configuration file.
flags	Text column that contains the same information as described by the PLB_SQLIO_FLAGS keyword for the INI configuration file.

Sun_Sqlio_File

This SQLIO system table contains the schema information that associates a PLB program FILE file reference to a SQL data table name.

Column	Use
file_id	Integer unique row identification value.
file_name	Text column that contains the PLB FILE file name as specified in a PLB OPEN instruction.
table_name	Text column that contains the associated relational database table name. If not provided, the default will be the value from the name column with the four characters '.', '/', '\', and ':' converted to the '_' character.
description	Text column for user information as desired.

Sun_Sqlio_Ifile

This SQLIO system table contains the schema information that associates a PLB program IFILE file reference to a SQL data table name. In addition, this table contains the IFILE key information that allows the runtime to identify the SQL data table columns associated with the key information. The columns defined for this system table are:

Column	Use
id	Integer unique row identification value.
name	Text column that contains the PLB ISI file name as specified in a PLB OPEN instruction.
index_name	Text column that contains the SQL object name used in a CREATE INDEX SQL statement. If not provided, the default will be the value from the name column with the four characters '.', '/', '\', and ':' converted to the '_' character.
file_name	Text column that identifies the TXT file name associated with the ISI file. The data in this column is used to look up information contained in the 'SUN_SQLIO_FILE' system table.
key_info	Text column that contains one or more comma separated columns names.
unique_idx	Integer column that specifies whether the index key column or combined columns for the key make a unique value. A value of zero indicates that the key is not unique. A value of one is unique.
upper_case	Integer column that specifies case sensitivity when matching keys. A value of zero is case sensitive. A value of one is case insensitive.
no_create	Integer column that indicates if a CREATE INDEX SQL statement should be executed on a PL/B PREPARE. A value of zero indicates that it should be execute, and a value of one indicates that it should not be executed. Note: Depending on the operations of the SQL database, omitting the index on smaller SQL TABLES may provide better performance. The 'no_create' column provides a means of optionally eliminating a SQL INDEX for some scenarios.
decription	Text column for user information as desired.

Sun_Sqlio_Version

This SQLIO system table contains the schema version information.

Column	Use
id	Integer unique row identification value.
schema_version	Integer column that contains a version number that identifies the schema system table formats that are being used.