Examples of using database schema in the Database Inbound protocol
In the Database Inbound protocol, database schema is used as the Cloverleaf message format. The Database Inbound protocol driver converts the result set that is returned from the SQL statement/stored procedure into a Cloverleaf message. This is based on the database schema.
These examples show how the Database Inbound protocol generates a Cloverleaf
message based on database schema. The Table_sanity
database schema includes visible and invisible columns.
Visible columns include ID, strFLD, intFLD, dateFLD, and flag.
The invisible column is testFLD.
Example 1: The returned result set includes all visible columns of the Table_sanity database schema
SQL statement:
select dateFLD, flag, ID, intFLD, strFLD from Table_sanity where Table_sanity.flag=0 order by Table_sanity.ID
Generated Cloverleaf message:
key01,test1,1,10/01/12,1
The field order of the generated Cloverleaf message is based on database schema’s column order; it is not based on the result set metadata’s column order.
If you set an empty database schema, then the Database Inbound protocol driver generates Cloverleaf messages based directly on the returned result set metadata.
Example 2: The returned result set includes part of the visible columns of the Table_sanity database schema
SQL statement:
select dateFLD, ID from Table_sanity where Table_sanity.flag=0 order by Table_sanity.ID
Generated Cloverleaf message:
key01, , ,10/01/12,
If the visible column that is defined in database schema cannot be retrieved from the result set, then the field value is set to empty. This is in the generated Cloverleaf message.
Example 3: The returned result set includes invisible columns that are not defined in the Table_sanity database schema
SQL statement:
select * from Table_sanity where Table_sanity.flag=0 order by Table_sanity.ID
Generated Cloverleaf message:
key01,test1,1,10/01/12,1
The Database Inbound protocol driver only retrieves visible columns that are defined in database schema from the result set. Other columns in the result set are ignored by the protocol driver.