Database query commands - IntelByteBuffer

Commands

Format definitions covered in this document are:

Copy Query : TWGDbCopyQueryCmd

The IPC command TWGDbCopyQueryCmd is sent to the server to copy an existing query.

Command code:  0x00040008

Input parameters:

Parameter 0 of the Copy Query command is a byte array in the following format:
Field Data type Values or meaning
Header length (0 - 3) 32-bit long (4 bytes) 22
Query ID (4 - 11) 64-bit long (8 bytes) Persistent ID for a query
Query display name offset (12 - 15) 32-bit long (4 bytes) Pointer to query display name
Query Format(16 - 17) 16-bit short (2 bytes) 1 for JOIN format, 2 for REPORT format
Query Graphic Offset (18 - 21) 32-bit long (4 bytes) Pointer to query graphic
Query Display Name compressed unicode string Query display name
Query Graphic compressed unicode string Query graphic

Return Codes:
Code Meaning
0 Success
3 Exception
300 Query not found

Output parameters:

After copying the query, the server sends back the query ID for the new query if the return code is 0 from the command.

The output parameter, which contains the query ID to return, is:
Field Data type
Query ID (0 - 7) 64 bit long (8 Bytes)

Run Query: TWGDbRunQueryCmd

The IPC command TWGDbRunQueryCmd is for retrieving data from database tables based on an existing query. The query should be created before sending the command.

Command code: 0x00040002

Input parameters:

Parameter 0 of the Run Query command is a byte array in the following format:
Field Data type Values or meaning
Header Length (0 - 3) 32 bit long (4 bytes) 28
Query ID (4 - 11) 64 bit long (8 bytes) Persistent ID for a query
Managed Object IDs Offset (12 - 15) 32 bit long (4 bytes) Pointer to Managed Object IDs
Language Offset (16 - 19) 32 bit long (4 bytes) Pointer to language
Country Offset (20 - 23) 32 bit long (4 bytes) Pointer to country
Variant Offset (24 - 27) 32 bit long (4 bytes) Pointer to variant
Number of managed Object ID values 32 bit long
managed Object ID value 1 32 bit long
managed Object ID value 2 32 bit long
etc.... each 32 bit long
managed Object ID value n 32 bit long
Language compressed unicode string Language used in translations
Country compressed unicode string The country in which the Language is spoken
Variant Compressed unicode string Vendor and browser specific code. See Java class 'Locale' for more information.

Managed Object IDs - Stores list of managed object IDs starting from element 28. The default is empty, which means return all query results.

Return Codes:
Code Meaning
0 Success
3 Exception
300 Query not found

Output parameters:

If the return code is 0 from the command, the result set that is a byte array is sent back. See Query Results for more information about the byte array format of the result sets.

Delete Query: TWGDbDelQueryCmd

The IPC command TWGDbDelQueryCmd is sent to server to delete an existing query.

Command code: 0x00040006

Input parameters:

Parameter 0 of the Delete Query command is a byte array in the following format:
Field Data type Values or meaning
Header Length (0 - 3) 32 bit long (4 bytes) 12
Query ID (4 - 11) 64 bit long (8 bytes) Persistent ID for a query

Return Codes:
Code Meaning
0 Success
3 Exception
300 Query not found

Output parameters:  None

Get Tables: TWGDbGetTablesCmd

The IPC command TWGDbGetTablesCmd is to get information about database tables.

Command code: 0x00040009

Input parameters:

The byte array format is listed in following table:
Field Data type Values or meaning
Header Length (0 - 3) 32 bit long (4 bytes) 32
DB Version Offset (4 - 7) 32 bit long (4 bytes) Pointer to Database Version
Database Name Offset (8 - 11) 32 bit long (4 bytes) Pointer to Database Name
Table Name Offset (12 - 15) 32 bit long (4 bytes) Pointer to Table Name
Language Offset (16 - 19) 32 bit long (4 bytes) Pointer to Language
Country Offset (20 -23) 32 bit long (4 bytes) Pointer to Country
Variant Offset (24 - 27) 32 bit long (4 bytes) Pointer to Variant
DB Version compressed unicode string Database Version
Database Name compressed unicode string Name of database which the requested tables are in.
Table Name compressed unicode string Table Name or "*" which means to get information about all tables
Language compressed unicode string Language used in translations
Country compressed unicode string The country in which the language is spoken
Variant compressed unicode string Vendor and browser specific code. See Java class 'Locale' for more information.

Return Codes:
Code Meaning
0 Success
3 Exception

Output parameters:

If the return code is 0 from the command, parameters 0-n (where n is the number of tables requested) stores table information.

Change Query: TWGDbChangeQueryCmd

The IPC command TWGDbChangeQueryCmd is to modify an existing database query.

Command code: 0x00040007

Input parameters:

Two parameters which contain information about Change Query command.

The 0 parameter of the change query command is a byte array in the following format:
Field Data Type Values or meaning
Header Length (0 - 3) 32 bit long (4 bytes) 12
Query ID (4 - 11) 64 bit long (8 bytes) Persistent ID for a query

Parameter 1 contains new definitions about the query to be changed. Parameter 1 is a byte array in the query parms format.

Return Codes:
Code Meaning
0 Success
3 Exception
300 Query not found

Output parameters:  None

Create Query: TWGDbCreateQueryCmd

The TWGDbCreateQueryCmd has two functions. One creates a query and saves it in the database (the command code is 0x00040004). The other function creates a query and runs it without saving it in the database (the command code is 0x00040005).

Command code:  0x00040005

Input parameters:

Two parameters contain information about the command.

Input parameter 0 of the Create Query command is a byte array in the following format:
 
Field Data type Values or meaning
Header Length (0 - 3) 32 bit long (4 bytes) 16
Language Offset (4 - 7) 32 bit long (4 bytes) Pointer to language
Country Offset (8 - 11) 32 bit long (4 bytes) Pointer to country
Variant Offset (12 - 15) 32 bit long (4 bytes) Pointer to variant
Language compressed unicode string Language used in translations
Country compressed unicode string The country in which the Language is spoken
Variant compressed unicode string vendor and browser specific code. See Java class 'Locale' for more information.

Parameter 1 contains the definition about the new query to be created. Parameter 1 is a byte array in the query parms format.

Return Codes
Code Meaning
0 Success
3 Exception

Output parameters:

If the return code is 0 from the command, the output parameter containing the query ID to return is a byte array in the following format:
 
Field Data type
Query ID (0 - 7) 64 bit long (8 Bytes)

 

Command code: 0x00040004 (the command to create a query and save it)

Input parameters:

This command has only one input parameter.

The input parameter of this Create Query command is a byte array in the query parms format.

Return Codes:
Code Meaning
0 Success
3 Exception
4 Database Error

Output parameters:

If the return code is 0 from the command, a result set that is a byte array is sent back. See Result Set for information about the result set byte array format.

Get Queries: TWGDbGetQueriesCmd

The IPC command TWGDbGetQueriesCmd is for getting information about existing queries.

Command code: 0x00040003

Input parameters:

Parameter 0 of the Get Queries command is a byte array in the following format:
Field Data type Values or meaning
Header Length (0 - 3) 32 bit long 32
Query ID (4 - 11) 64 bit long Persistent ID for a query
Table Classes Offset (12 - 15) 32 bit long Pointer to a list of table class names
Database Name Offset (16 - 19) 32 bit long Pointer to database name
Language Offset (20 - 23) 32 bit long Pointer to language
Country Offset (24 - 27) 32 bit long Pointer to country
Variant(28 - 31) 32 bit long Pointer to variant
Table Classes compressed unicode string A list of table class names
Database Name compressed unicode string Database name
Language compressed unicode string Language used in translations
Country compressed unicode string The country in which the Language is spoken
Variant compressed unicode string vendor and browser specific code. See Java class 'Locale' for more information.

Return Codes:
Code Meaning
0 Success
3 Exception

Output parameters:

If the return code is 0 from the command, parameters 0-n (where n is the number of tables requested) stores query reply information.

Query Parms: TWGDbQueryParms

The TWGDbQueryParms class contains the parameters to create a query. It is used in the create query and Change Query commands. The byte array format is listed in the following table:
Field Data type Values or meaning
Header Length (0 - 3) 32 bit long 58
Database Name Offset (4 - 7) 32 bit long Pointer to database name
ID String Offset (8 - 11) 32 bit long Pointer to ID string
Query Display Name Offset (12 - 15) 32 bit long Pointer to query display name
Query Graphic Offset (16 - 19) 32 bit long Pointer query graphic
User ID Offset (20 - 23) (not used now) 32 bit long Pointer to user ID
Column Filter Offset (24 - 27) 32 bit long Pointer to column filter
Select Fields Offset (28 - 31) 32 bit long Pointer to select fields
Sort Fields Offset (32 - 35) (not used now) 32 bit long Pointer to sort fields
Resource Bundle Name Offset (36 - 39) 32 bit long Pointer to resource bundle name
Resource Bundle Key Offset (40 - 43) 32 bit long Pointer to resource bundle key
Is Default Query (44 - 45) 16 bit short 1 for default query, 0 for user query
Is Distinct Rows (46 - 47) 16 bit short 1 for distinct rows, 0 for not distinct rows
Is Saved Query (48 - 49) 16 bit short 1 for saved query, 0 for non-saved query
Is Translate Value(50 - 51) 16 bit short 1 for translating the value, 0 for not
Query Format(52 - 53) 16 bit short 1 for JOIN format, 2 for REPORT format
Database Version Offset (54 - 57) 32 bit long Pointer to database version
Database Name compressed unicode string Database name
ID String compressed unicode string Unique ID string for a default query
Query Display Name compressed unicode string Query display name
Query Graphic compressed unicode string The name of the icon for a query
Column Filter data type 32 bit long DataValue Interface
Offset to the number of values for the filter (number of values precedes the values) 32 bit long offset from start of buffer
Offset to the filter table name 32 bit long offset from start of buffer
Offset to the filter column name 32 bit long offset from start of buffer
Offset to the filter operator 32 bit long offset from start of buffer
The filter number of values 32 bit long
The filter value 1 matches filter data type
The filter value 2 matches filter data type
etc.... matches filter data type
The filter value n matches filter data type
The filter table name compressed unicode string
The filter column name compressed unicode string
The filter operator compressed unicode string
Select Fields compressed unicode string A list of the full names of selected columns in the query.
Resource Bundle Name compressed unicode string Resource bundle base class name for a default query
Resource Bundle Key compressed unicode string Resource bundle key for default query to get the localized query name
Database Version compressed unicode string Database version
Select Fields contains the column fields to select for a query. Each column field is separated by ":" in the format. For example, a query contains 3 columns. The first column-qualified name is table1.column1. The second column-qualified name is table2.column2. The third column-qualified name is table3.column3. The value to represent the select fields in the array is table1.column1:table2.column2:table3.column3.

Query Reply: TWGDbQueryReply

Class TWGDbQueryReply contains information about a query. The byte array format is listed in the following table:
 
Field Data type Values or meaning
Header Length (0 - 3) 32 bit long 68
Query ID (4 - 11) 64 bit long Persistent ID for a query
Is Default Query(12 - 13) 16 bit short 1 for default query. 0 for user query.
Is Saved Query(14 - 15) 16 bit short 1 for saved query, 0 for non saved query
Is Translate Value(16 - 17) 16 bit short 1 for translating the value, 0 for not
Query Format(18 - 19) 16 bit short 1 for JOIN format, 2 for REPORT format
Query Display Name Offset (20 - 23) 32 bit long Pointer to Query Display Name
ID String Offset (24 - 27) 32 bit long Pointer to ID string
User ID Offset (28 - 31) 32 bit long Pointer to User ID
Query Graphic Offset (32 - 35) 32 bit long Pointer to query graphic
Tables Used Offset (36 - 39) 32 bit long Pointer to tables used
Column Tokens Offset (40 - 43) 32 bit long Pointer to column tokens
Real Column Names Offset (44 - 47) 32 bit long Pointer to real column names
Column Types Offset (48 - 51) 32 bit long Pointer to column types
Column Length Offset (52 - 55) 32 bit long Pointer to column length
Full Column Names Offset (56 - 59) 32 bit long Pointer to full column names
Column Display Names Offset (60 - 63) 32 bit long Pointer to column display names
Column Filter Offset (64 - 67) 32 bit long Pointer to column filter
Query Display Name compressed unicode string Displayable name for a query
ID String compressed unicode string Unique ID string for a default query
User ID compressed unicode string The user ID for a query
Query Graphic compressed unicode string The name of the icon for a query
Tables Used compressed unicode string A list of table tokens used in a query. A colon (:) separates each token.
Column Tokens compressed unicode string A list of the token names of the selected columns in a query. A colon(:) separates each name.
Real Column Names compressed unicode string A list of the real names of the selected columns in a query. A colon (:) separates each name.
Column Types   A list of the data type names of the selected columns in the query.
Column Length   A list of the lengths of the selected columns in the query. If a column is not a character column, 0 is set for the length.
Qualified Column Names(format: table_name:column_name) compressed unicode string A list of the full names of selected columns in the query. A colon (:) separates each name.
Column Display Names compressed unicode string A list of column display names selected in the query. A colon (:) separates each name.
Column Filter data type 32 bit long DataValue Interface
Offset to the number of values for the filter (number of values precedes the values) 32 bit long offset from start of buffer
Offset to the filter table name 32 bit long offset from start of buffer
Offset to the filter column name 32 bit long offset from start of buffer
Offset to the filter operator 32 bit long offset from start of buffer
The filter number of values 32 bit long
The filter value 1 matches filter data type
The filter value 2 matches filter data type
etc.... matches filter data type
The filter value n matches filter data type
The filter table name Compressed unicode string
The filter column name Compressed unicode string
The filter operator Compressed unicode string

Column types:
 
Structure for column types Data type
Number of columns(N) 32 bit long
First column type 32 bit long
Second column type 32 bit long
Third to (n-1) column type 32 bit long
Last column type 32 bit long
Column type can be any of the following types:

Column Length:
 
Structure for column length Data type
Number of columns (N) 32 bit long (4 bytes)
First column length 32 bit long (4 bytes)
Second column length 32 bit long (4 bytes)
Third to (n-1) column 32 bit long (4 bytes)
Last column length 32 bit long (4 bytes)

Table Reply: TWGDbTableReply

Class TWGDbTableReply contains information about a database table. It is requested by a get tables command.

The byte array format is listed in following table.
 
Field Data type Values
Header Length (0 - 3) 32 bit long (4 bytes) 32
Database Version Offset (4 - 7) 32 bit long (4 bytes) Pointer to database version
Table Token Offset (8 - 11) 32 bit long (4 bytes) Pointer to table token
Table Real Name Offset (12 - 15) 32 bit long (4 bytes) Pointer to Table Real Name
Table Display Name Offset (16 - 19) 32 bit long (4 bytes) Pointer to table display name
Table Graphic Offset (20 - 23) 32 bit long (4 bytes) Pointer to table graphic
Column Tokens Offset (24 -2 7) 32 bit long (4 bytes) Pointer to column tokens
Column Real Names Offset (28 - 31) 32 bit long (4 bytes) Pointer to column real names
Database Version compressed unicode string Database version
Table Token compressed unicode string Table token name
Table Real Name compressed unicode string Real table name used in database
Table Display Name compressed unicode string Table display name
Table Graphic compressed unicode string Graphic name of the table
Column Tokens structure Token names of all the columns in the table
Column Real Names structure Display names of all the columns in the table

Column Tokens
Structure for column tokens Data type
Number of columns(N) 32 bit long
First column token compressed unicode string
Second column token compressed unicode string
Third to (n-1) column tokens compressed unicode string
Last column token compressed unicode string

Column Real Names
Structure for column real names Data type
Number of columns(N) 32 bit long
First column real name compressed unicode string
Second column real name compressed unicode string
Third to (n-1) column real names compressed unicode string
Last column real names compressed unicode string

Result Set: TWGDbResultSet

Class TWGDbResultSet contains a set of rows returned by a query and information about the columns of the result set.

Information about the result set is contained in two parameters. One parameter contains rows and the other parameter contains column information.

The byte array format of parameter 0 of the result set is as follows:
 
Field Data type Values or meaning
Header Length (0 - 3) 32 bit long 20
Query ID (4 - 11) 64 bit long Persistent ID for a query
Size (12 -15) 32 bit long Number of columns in the result set
Number of Rows (16 - 19) 32 bit long Number of rows in the result set
Buffer Structure for Rows

The byte array format of parameter 1 of the result set is as follows:
 
Field Data type Values or meaning
Number of Columns (0 - 3) 32 bit long Number of columns in the result set
Columns structure Columns in the result set. Each column information is stored in the buffer one by one.

Query Results: TWGDbQueryResults

Class TWGDbQueryResults is the query result set container. There might be one result stored (for JOIN_FORMAT queries) or multiple results stored (one per table as in REPORT_FORMAT queries).

Depending on the query format, the result sets have different numbers of arrays and structure. But they have a common subset to hold information about the query ID and the query format. Following is the common byte array structure(parameter 0):
 
Field Data type Values or meaning
Header Length (0 - 3) 32 bit long 18
Query ID (4 -11) 64 bit long Persistent ID for a query.
Query Format (12 - 13) 16 bit short 1 for JOIN format, 2 for REPORT format
Number of Query Results (14 - 17) 32 bit long It only applies to REPORT format. It is the number of query results.
For JOIN format, the query results contains information about a result set. Because a result set needs two arrays to hold its information, the result set needs three arrays for JOIN format. So parameters 1 and 2 of the query results are parameters 0 and 1 of a result set.

For REPORT format, the query results contains number of query results. Each query result needs two arrays to hold its information. So the query results needs 1 + (number of query result)*2 arrays for REPORT format. Parameters 1 to n of the query results are byte arrays in query result format.

Row: TWGDbRow

Class TWGDbRow holds the values for selected database table columns. The number of values in a row is equal to number of table columns. The type of a value in a row is the type of the corresponding column. The size of the array for a row depends on the number of values the array contains.

The following table lists the possible column values in a row and their corresponding data type.
Column Value Data type
32 bit long Integer
32 bit float Real
64 bit double Double
compressed unicode string Char, Varchar
8 bit short Smallint
64 bit long Date, Datetime

Query Column: TWGDbQueryColumn

Class TWGDbQueryColumn describes information about a column in the result set of a query.

The byte array format of a query column is as follows:
 
Field Data type Values or meaning
Header Length (0 - 3) 32 bit long 44
Column ID (4 - 7) 32 bit long Column number
Column Type (8 - 11) 32 bit long Column type.
Column Length (12 - 15) 32 bit long The number of characters in the column. This applies only to String type columns (VARCHAR or CHAR)
Is Filter Column(16 - 17) 16 bit short 1 for filter column, 0 for not.
Is Mapped Column(18 - 19) 16 bit short 1 for mapped column, 0 for not.
Table Token Offset (20 - 23) 32 bit long Pointer to the token name for the table that the column is in
Column Token Offset (24 - 27) 32 bit long Pointer to column token
Table Display Name Offset (28 - 31) 32 bit long Pointer to the display name for the table that the column is in
Real Column Name Offset (32 - 35). 32 bit long Pointer to the real column name
Column Display Name Offset (36 - 39) 32 bit long Pointer to column display name
Version Offset (40 - 43) 32 bit long Pointer to database version
Table Token compressed unicode string The token name for the table that the column is in
Column Token compressed unicode string Column token name
Table Display Name compressed unicode string The display name for the table that the column is in
Real Column Name compressed unicode string The real column name
Column Display Name compressed unicode string Column display name
Version compressed unicode string Database version.

Query Result: TWGDbQueryResult

Class TWGDbQueryResult contains a set of rows returned by a query and information about columns. It applies only to a REPORT format query.

Information about the query result is contained in two arrays. One array contains rows and the other array contains column information.

Parameter 0 of the query result contains a row in the following byte array format:
 
Field Data type Values or meaning
Header Length (0 - 3) 32 bit long 12
Number of Column IDs Offset (4 - 7) 32 bit long Pointer to array containing the column IDs in its super query.
Number of MOIDs(8 - 11) 32 bit long Number of MOIDs in the query result.
Number of Column IDs 32 bit long Number of Column IDs
Column IDs 32 bit long array. Each element in the array is 32 bit long. Array containing the column IDs in its super query.
MOID 1 32 bit long First MOID
Number of Rows with MOID 1 32 bit long Number of Rows with MOID 1
Rows with MOID 1
MOID2 32 bit long Second MOID
Number of Rows with MOID 2 32 bit long Number of Rows with MOID2
Rows with MOID2
... Third MOID to n-1 MOID
n Last MOID
Number of Rows with MOID n Number of Rows with MOID n
Rows with MOID n
NOTE: MOID stands for Managed Object ID

Parameter 1 of the query result contains column information in the following byte array format:
 
Field Data type Values or meaning
Number of Columns(0 - 3) 32 bit long Number of columns in the result set
Columns structure Columns in the result set. Each column information is stored in the buffer one by one.