SQLIO Schema File Format

9.5

A SQLIO schema file is implemented as a XML file format that contains the basic record and column layout used to access the SQL database engine tables.

XML Schema Tag Type Descriptions

<SqlioSchema>

This XML tag is the root tag identifier for a SQLIO schema definition file.

<Database> Schema Record

This XML tag identifies a record that contains database schema information for the Sun_Sqlio_Databases system table. The element tag references in the XML <Database> record directly relate to the column descriptions that are documented for the Sun_Sqlio_Databases system table. The element tag names are as follows:

<name></name>

Text data that has the user defined name that is used as a reference to name a database connection.

<description></description>

Text data to describe a database connection that provides information about the intended use of a database connection.

<driver></driver>

This XML tag element contains a numeric value that defines a driver number that identifies the type of SQL connection interface. The data for this tag is a numeric value from one (1) to nnn that specifies the SQL database connection interface type. If this keyword is not specified, the default value is one. The allowed values are as follows:

1 = SQLite native connection

2 = DBFILE connection

<sql_type></sql_type>

This XML tag element contains a numeric value that defines the type of SQL syntax used for this database connection definition. If this XML tag element is not specified, the default value is one. This XML tag element must be specified to ensure proper execution of SQL statements when performing the PL/B IO instructions relative to a specific SQL database engine type. The allowed values are as follows:

1 = SQLite language type

2 = SQL Server language type

3 = MYSQL language type

4 = ORACLE language type

<host></host>

This XML tag element defines the SQL database source that is connected when executing SQLIO operations for the database referenced using the name specified in the <name> XML tag. The <host> string is unique to the SQL database engine that is connected. The explicit behavior invoked by the <host> data is the same as described for the {host} parameter in the DBCONNECT instruction.

<user_name></user_name>

This XML tag element defines the username used when making the connection. The username may or may not be required to make a connection depending on the SQL database engine configuration. The explicit behavior invoked by the <user_name> data is the same as described for the {user} parameter in the DBCONNECT instruction.

<pass></pass>

This XML tag element defines the password for the user_name that makes the connection. The explicit behavior invoked by the <pass> data is the same as described for the {pass} parameter in the DBCONNECT instruction.

<conn></conn>

This XML tag element defines additional logininformation that may be needed to make the connection. The explicit behavior invoked by the <conn> data is the same as described for the {conn} parameter in the DBCONNECT instruction.

<ext></ext>

This XML tag element defines optional extension data that specifies some unique parameters used during the connection process. The explicit behavior invoked by the <ext> data is the same as described for the {ext} parameter in the DBCONNECT instruction.

<flags></flags>

This XML tag element defines any special settings used to make the connection. The explicit behavior invoked by the <flags> is the same as described for the {flags} parameter in the DBCONNECT instruction.

<Afile> Schema Record

The <Afile> XML record has schema information for the Sun_Sqlio_Afile system table. The element tag references in the XML <Afile> record directly relate to the column descriptions that are documented for the Sun_Sqlio_Afile system table. The element tag names are as follows:

<name></name>

This XML tag element has text data that provides the PL/B AAM file name as specified in a PL/B OPEN instruction.

<file_name></file_name>

This XML tag element has text data that identifies the TXT file name associated with the AAM file. This schema data looks up information contained in the SUN_SQLIO_FILE system table.

<key_info></key_info>

This XML tag element has text data that contains the AFILE key specifications the same as specified for an AFILE PREP instruction. Additionally, explicit column names to be specified instead of field/column position values. For example,

if the key_info is "U,COLNAME,x20-25"

then

U - If there is no SQL column named 'U', this key parameter specifies non-case sensitive searches.

COLNAME - This is a column name. Otherwise, the key data is processed by the runtime as a key specification and gives an appropriate key specification error. If a schema column name is found, the column starting and ending positions are used for the AAM key.

x20-25 - In this case, the 'x' is ignored for SQLIO usage. The '20-25' defines the field position to be used for the AAM key.

<description></description>

This XML tag element has text data to describe the end-user application use for this AFILE schema table.

<IFile> Schema Record

The <Ifile> XML record has schema information for the Sun_Sqlio_Ifile system table. The element tag references in the XML <Ifile> record directly relate to the column descriptions that are documented for the Sun_Sqlio_Ifile system table. The element tag names are as follows:

<ifile_name></ifile_name>

This XML tag element has text data that provides the PL/B IFILE file name as specified in a PL/B OPEN instruction.

<index_name></index_name>

This XML tag element has text data that contains the SQL object name used in a CREATE INDEX SQL statement for this <Ifile> record definition. If this XML tag element is not provided, the default action by the runtime is to use the data from the name column after the four characters '.', '/', '\', and ':' are converted to the '_' character.

<file_name></file_name>

This XML tag element has text data that identifies the TXT file name associated with the ISI file. This schema data looks up information contained in the SUN_SQLIO_FILE system table.

<key_info></key_info>

This XML tag element has text data that contains one or more comma separated column names.

<unique_idx></unique_idx>

This XML tag element contains a numeric value that specifies whether the index key column or combined columns for the key make a unique SQL key value. A value of zero (0) specified by this XML tag element indicates that the key is not unique. A value of one (1) indicatates that the key unique.

<upper_case></upper_case>

This XML tag element contains a numeric value that specifies case sensitivity when matching keys. A value of zero indicates that case sensitivity is used. A value of one indicates that the key matching is not case sensitive.

<no_create></no_create>

This XML tag element contains a numeric value that indicates whether a CREATE INDEX SQL statement should be executed on a PL/B PREPARE. A value of zero indicates that it should be execute. A value of one indicates that it should not be executed.

Note: Depending on the operations of the SQL database, performance may be improved by not having a SQL INDEX on smaller SQL TABLES. The 'no_create' column provides a means of optionally eliminating a SQL INDEX for some scenarios.

<description></description>

This XML tag element has text data to describe the end-user application use for this IFILE schema table.

<File> Schema Record

This XML tag identifies a record that contains database schema information for the Sun_Sqlio_File and Sun_Sqlio_Columns system tables. The XML tag elements specified in the <File> record definition are directly related to the columns defined for the Sun_Sqlio_File and Sun_Sqlio_Columns. The element tag names are as follows:

<name></name>

This XML tag element has text data that defines the name of the column within a SQL table.

Note that SQL column names cannot be any of the following reserved SQL words. Otherwise, the SQL database engines can give unexpected errors.

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	WHERE

<table_name></table_name>

This XML tag element has text data that contains the associated relational database table name. If this XML tag element is not provided, the default is to use the data from the <name> tag element after the four characters '.', '/', '\', and ':' are converted to the '_' character.

<description></description>

This XML tag element has text data to describe the end-user application use for this FILE schema definition.

<column></column>

This XML tag is an embedded record tag that contains the column schema definitions that identifies a PL/B field with a record. The XML tag elements in this record tag correspond directly to the columns as found in the Sun_Sqlio_Columns system table. The tag elements for <column> are defined as follows:

<name></name>

This XML tag element has text data that defines the name of the column within a SQL table.

<description></description>

This XML tag element has text data that contains user information to describe this column.

<type></type>

This XML tag element is an integer value that specifies the PL/B type for a SQL column within a SQL table. The allowed 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></offset>

This XML tag element is an integer value that specifies a zero based offset of the column data in the PL/B record.

<length></length>

This XML tag element is an integer value that specifies the length of the column data in the PL/B record.

<scale></scale>

This XML tag element is an integer value 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></sql_type>

This XML tag element is an integer value that specifies the SQL type. The allowed values are defined as follows:

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).

10 - Time (SQL_TIME).

11 - DateTime (SQL_DATETIME).

12 - Identity (SQL_IDENTITY).

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 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. (10.7)

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. (10.7)

Note:

1.All types are case sensitive unless they are documented to be not case sensitive.

2. If the 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></zero_fill>

This XML tag element is an 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></unique_col>

This XML tag element is an 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.

When the prime key access is being used, the column <sql_type> data type can either be a character SQL data type or it can be a numeric SQL data type. If the column <sql_type> is specified as a character data type for the prime key column, the data is processed to provide PL/B language simulation where a low key value is all blanks and a high key value is all 'z' characters. If the column <sql_type> is specified as a numeric data type for the prime key column, the data is processed to provide PL/B language simulation where a low key value is zero and the high key value is 4,294,967,295 (ie 0xFFFFFFFF ).

When using 'native' SQL tables with a SQL prime key that is composed of multiple columns, the <unique_col> definition value of each column combined to make the SQL primary key must be specified as value 101 for the first column used, 102 for the second column used, and etc.

Example SQLIO schema to use Prime Key column as character SQL type:

<Column>

<name>CUSTID</name>

<description>customer 4 character id</description>

<offset>1</offset>

<length>5</length>

<type>0</type>

<scale>0</scale>

<sql_type>1</sql_type>

<nullable>0</nullable>

<zero_fill>1</zero_fill>

<unique_col>101</unique_col>

<format_mask></format_mask>

</Column>

Example SQLIO schema to use Prime Key column as numeric SQL type:

<Column>

<name>PRODID</name>

<offset>1</offset>

<length>3</length>

<type>0</type>

<scale>0</scale>

<sql_type>6</sql_type>

<nullable>1</nullable>

<zero_fill>0</zero_fill>

<unique_col>101</unique_col>

<format_mask></format_mask>

</Column>

Example of multi-column prime key:

<Column>

<name>CUSTID</name>

<description>4 character id prime key part 1</description>

<offset>1</offset>

<length>5</length>

<type>0</type>

<scale>0</scale>

<sql_type>1</sql_type>

<nullable>0</nullable>

<zero_fill>1</zero_fill>

<unique_col>101</unique_col>

<format_mask></format_mask>

</Column>

<Column>

<name>COMPANYID</name>

<description>4 character id prime key part 2</description>

<offset>6</offset>

<length>5</length>

<type>0</type>

<scale>0</scale>

<sql_type>1</sql_type>

<nullable>0</nullable>

<zero_fill>1</zero_fill>

<unique_col>102</unique_col>

<format_mask></format_mask>

</Column>

<nullable></nullable>

This XML tag element is an integer column that specifies the nullable state. By default, the value of zero is the nullable state value which causes use of the 'not null' attribute when SQLIO performs a SQL CREATE TABLE operation including this column. If the <nullable> tag value is set to non-zero, the 'null' attribute is enabled which means that row items in this column can be empty. See the SQLIO PLB Language Changes section for more information when using a PLB WRITE instruction that detects a column with the <nullable> state enabled.

<format_mask></format_mask>

The description of the format masking can be found in the description of the format_mask found under the Sun_Sqlio_Columns administrative table found in the SQLIO Administrative Tables topic. (9.6B)