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 looks up information contained in the 'SUN_SQLIO_FILE' system table. The <sql> attribute named 'file_name={filename}' overrides this schema field for a <sql> PLB OPEN or PREPARE AFILE operation.
key_info	Text column that contains AFILE key specifications that are the same as specified for an AFILE PREPARE instruction. Additionally, explicit column names can be specified instead of field/column position values. For example, the following AFile key specification format is allowed when using a column name. See SQLIO Schema File Format for more details. "U,COLNAME,x20-25"
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. Note that SQL column names cannot be any of the following reserved SQL words. Otherwise, the SQL database engines can give unexpected errors. SQL Reserved Words that should not be used because they can cause SQL construct conflicts are ALL, AND, AS, ASC, AVG, BY, COUNT, CREATE, DELETE, DESC, DISTINCT, DROP, FROM, GROUP, HAVING, IN, INSERT, INTO, IS, LIKE, MAX, MIN, NOT, NULL, OR, ORDER, SELECT, SET, SQL, SUM, TABLE, UPDATE, USER, VALUES, and WHERE.
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. For Oracle, this type works the same as type 0. 2 - Char data type that is not case sensitive and trailing spaces dropped. For Oracle, this type works the same as type 0. 3 - Char data type. 4 - Char data type with binary collating sequence. For Oracle, this type works the same as type 3. 5 - Char data type that is not case sensitive. For Oracle, this type works the same as type 3. 6 - Integer data type. 7 - Decimal(length) or Decimal(length-1,scale). 8 - Real data type. 9 - Date (SQL_DATE) (9.5B) 10 - Time (SQL_TIME) (9.5B) 11 - DateTime (SQL_DATETIME) (9.5B) 12 - Identity (SQL_IDENTITY (9.5B) 13 - SQL_NCHAR_T - This data type is a NCHAR SQL type where any trailing blank UTF16 characters are truncated. The NCHAR SQL type for the SQL Server specifies that the National Character Set is to be used and data is stored as UTF16 characters in the SQL tables.(9.6) 14 - SQL_NCHAR - This data type is a NCHAR SQL type for the SQL Server that specifies that the National Character Set is to be used and data is stored as UTF16 characters in the SQL tables. (9.6) 15 - SQL_VARCHAR2 - This data type is the VARCHAR2 SQL type for the Oracle Server. This SQL type allows variable length data in a table column. 16 - SQL_VARCHAR2_T - This data type is the VARCHAR2 SQL type for the Oracle Server. This SQL type allows variable length data in a table column and trailing spaces are dropped. 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. 4. The SQLIO operations use an implicit column named 'sun_recno' that has unique values to support simulation of all PL/B IO instructions. If an end-user SQL table has an implicit column with unique values like the 'sun_recno' column, the <sql_type> value of 12 allows the user defined column name to replace the implicit 'sun_recno' column for all SQL operations. This SQL data type is identified as a SQL_IDENTITY type. Multiple columns are not supported. 5. The SQL NCHAR and NCHAR_T data types are being implemented to allow data using national characters sets (like Hebrew) to be stored and retrieved from the SQL tables using PL/B READ and WRITE instructions. 6. When the SQL NCHAR or NCHAR_T data type is specified for a column, the field/variable data to be written by a PL/B WRITE operation must be given as UTF16 characters. In this case, all data must be two bytes for each character (e.g, A 10 character NCHAR field requires a DIM size of 20 bytes). 7. When a NCHAR or NCHAR_T data field is being retrieved using a SQLIO READ instruction, the ABSON must be used. In this case, the SQL NCHAR data is stored into the PL/B DIM variables as UTF16 data. The actual data transferred from the record into and back from the SQL table column with a NCHAR/NCHAR_T is not modified in any way. Therefore, the NCHAR/NCHAR_T fields must be read using the ABSON control to insure the UTF16 is unchanged when it is stored into a DIM variable. 8. When declaring a NCHAR or NCHAR_T SQL type in the schema definitions, the data size must be specified as the PL/B DIM size required to hold all of the two byte UTF16 characters. This means that a DIM size of ten (10) characters is required for five (5) NCHAR characters stored as UTF16 characters. Thus, schema definition size for a SQL NCHAR/NCHAR_T must be specified as a multiple of two (e.g.the NCHAR schema size is 10 characters that is required for a DIM variable size that holds 5 UTF16 characters). 9. ISAM PL/B instructions are supported when using the SQL NCHAR and NCHAR_T data types. 10. When using the NCHAR/NCHAR_T SQL types, the following errors can occur as follows: I86 subcode 24 - The filed size of a SQL_NCHAR or SQL_NCHAR_T column is not a multiple of two when the schema column is created. I86 subcode 44 - A file with a SQL_NCHAR_T or SQL_NCHAR column is opened or prepared on an unsupported driver. The National character set support is not available on all database engines. I86 subcode 45 - An AAM file with a SQL_NCHAR_T or SQL_NCHAR column in the key specification is opened or prepared. The AAM key specifications can not include a field/column using these data types. 11. When using the SQL_NCHAR or SQL_NCHAR_T data type, the following limitations are defined: a. Only the SQL SERVER is supported. An I86 subcode 44 error will occur for SQLite and MySQL database engines using these data types. b. New Sunbelt DBFILE drivers are required to support these SQL data types. The sunwado.dll and sunwodbc.dll drivers have been modified to support these SQL data types. c. The new SQL types cannot be used in AAM key specifications. d. Any PL/B instructions that are used for SQLIO operations to process data for these data types can not be used for non-SQLIO operations (ie native disk I/O). This means that normal native disk I/O does not support UTF16 data required by the SQL_NCHAR and SQL_NCHAR_T data types.
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_col	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. For all SQL tables created and maintained via PLB instructions using the SQLIO runtime support, there is always a 'sun_recno' implicit column that has unique values that simulate PL/B IO instructions. SQLIO also supports a native SQL table with a prime key to be used when there is no 'sun_recno' implicit column. This allows a 'native' SQL table to be accessed via SQLIO PL/B instructions by using the prime key column data. To use a prime key in place of the 'sun_recno', the SQLIO schema settings require that a value of 101 must be assigned for the column <unique_col> definition value. This type of SQLIO access does not allow direct file operations for FPOSIT, REPOSIT, READ, UPDATE, WRITE, DELETE, WEOF, and POSITEOF instructions including random and sequential operations.
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	See below.

Format Masks (9.5B)

Columns schema definitions for SQLIO support a 'format_mask' definition.

The SQL_DATE 'format_mask' must include the following special characters to identify the mask format:

Mask	Meaning
zz or yy or yyyy	2 year or 4 digit year
mm	2 digit month
dd	2 digit day

The 'zz' year is a two character mask field that has the year encoded to represent a four digit number value for a year. The high order (first) character of the 'zz' mask can be a numeric character zero to nine or it can be an uppercase alpha character 'A' to 'Z'. The low order (second) character of the 'zz' mask can only be a numeric character zero to nine. Both of the 'zz' characters are used to calculate a four digit year.

Example of data for 'zz' encoded character mask:

Encoded Data	Calculated Year
00	1900
...
99	1999
A0	2000
...
A9	2009
B0	2010

The default SQL_DATE format_mask is 'mm-dd-yy'

The SQL_TIME 'format_mask' must include the following special characters to identify the mask format to be used:

Mask	Meaning
hh	2 digit hours
mm	2 digit minutes
ss	2 digit seconds

The default SQL_TIME format_mask is 'hh:mm:ss'

The SQL_DATETIME 'format_mask' must include the following special characters to identify the mask format to be used:

Mask	Meaning
yy or yyyy	2 year or 4 digit year
mm	2 digit month
dd	2 digit day
hh	2 digit hours
nn	2 digit minutes
ss	2 digit seconds

The default SQL_DATETIME format_mask is 'yyyymmddhhnnss'

Example:

<sql_type>9</sql_type>

<format_mask>mm/dd/yy</format_mask>

Standard SQL formats are defined as follows:

Mask	Meaning
Date	yyyy-mm-dd
Time	hh:mm:ss
DateTime	yyyy-mm-dd hh:mm:ss

These formats are the required andexpected formats that are used to perform actual SQL operations. When the runtime performs SQL operations for the date, time, and datetime data types, appropriate conversions are performed using the 'format_mask' to insure proper operations.

When using the new data types.

Columns are created as special date, time , or datetime columns in MySql or SqlServer.
Columns are created as a char type but they are treated in a standard way in SQLite.
A WRITE operation causes the PL/B data to be transformed using the 'format_mask' into the standard SQL format.
A READ operation converts data from the standard SQL format into the format specified by the 'format_mask'.

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 looks up information contained in the 'SUN_SQLIO_FILE' system table. The <sql> attribute named 'file_name={filename}' overrides this schema field for a <sql> PLB OPEN or PREPARE IFILE operation.
key_info	Text column that contains one or more comma separated columns names. This is not the numeric key specification used on a PREPARE instruction.
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.
description	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.