Query constant files are configuration files containing
constant values that may be referenced by any SQL script run by
the
Agentry
Server.
Each SQL Database system connection can have its own query constant
file. By default, there are two such files installed with the Server:
- oracle_sd.ini
- sqlserver_sd.ini
As the names imply each is intended for use by a system connection
to one of the Oracle or SQL Server database systems. The values
contained in these scripts are intended to be used in making SQL
commands, either from definitions within the application or those
run from the query initialization file (SqlBe.ini),
more portable. Using the values listed within these files, a single
SQL statement may be written that will execute properly in any of
the database systems just mentioned.
The values within these files are accessible within a SQL statement
run by the Server via SDML data tags. Following is a list of the
values contained in the default versions of these files. Like all other
configuration files for the Server, the query constant files can
contain multiple sections. By default they contain the single section
[Database],
which contains all of the following options:
- name -
The name of the database type. This value should not be changed.
- getSystemTime - This value is the database
function called to return the current date and time.
- timeStampFormat - This is the format of a date
and time value such that it can be inserted into a table column
of a date and time data type. This is also the format used by the SDML
processor within the Agentry
Server when
a date and time SDML data tag is expanded. Therefore, this should
not be edited except in exceptional circumstances.
- dateFormat - This is the format of a date value
such that it can be inserted into a table column of a date or date
and time data type. This is also the format used by the SDML processor
within the Agentry
Server when
a date data tag is expanded. Therefore, this should not be edited
except in exceptional circumstances.
- timeFormat - This is the format of a time value
such that it can be inserted into a table column of a time or date
and time data type. This is also the format used by the SDML processor
within the Agentry
Server when
a time data tag is expanded. Therefore, this should not be edited
except in exceptional circumstances.
- tempdate - This is a temporary date value of
1/1/1901 12:00:00. This value is returned in the proper format for
the database type to use it as a date and time value. This date
and time value is assumed to be one that is not likely to be set
for real data. If this default value is a valid date and time for
the system it should be edited to one that is not valid.
- substring - This is the database function called
to return a range of characters from within a larger string.
- stringcat - This is the database function or
operator that will concatenate two string values.
- charFunction - This is the database function
to convert a value to a character data type.
- nullFunction - The database function called
to determine if a value is null.
- singleRow - Oracle databases require a select
statement to include a from clause. When selecting literal values,
e.g. SELECT ‘X’..., the table object DUAL is used for this purpose in
Oracle. No such object exists in SQL Server. The singleRow value
in the Oracle constant file contains the text FROM DUAL. In the
SQL Server script it contains an empty string.
- unicodePrefix - This is the value prefixed
to a unicode value, identifying it as such to the database.
- terminalErrorCodes - This is a semi-colon delimited
list of return values treated as terminal error codes by the Agentry
Server.
When such an error is returned, the Server will end the synchronization
with the Client, returning an error message.
- retryWithChangeErrorCodes - This is a semi-colon
delimited list of return values that may be received from the database
system as the result of processing a SQL statement. The values listed
here will be treated as a retry with change error for the purposes
of transaction failure handling. If this functionality is not enabled
in an implementation, this setting has no affect on the Server’s
behavior.
- retryWithoutChangeErrorCodes - This is a semi-colon
delimited list of return values that may be received from the database
system as the result of processing a SQL statement. The values listed
here will be treated as a retry without change error for the purposes
of transaction failure handling. If this functionality is not enabled
in an implementation, this setting has no affect on the Server’s
behavior.
- fatalWithMessageErrorCodes - This is a semi-colon
delimited list of return values that may be received from the database
system as the result of processing a SQL statement. The values listed
here will be treated as a fatal with message error for the purposes
of transaction failure handling. If this functionality is not enabled
in an implementation, this setting has no affect on the Server’s
behavior.
- fatalWithoutMessageErrorCodes - This is a semi-colon
delimited list of return values that may be received from the database
system as the result of processing a SQL statement. The values listed
here will be treated as a fatal without message error for the purposes
of transaction failure handling. If this functionality is not enabled
in an implementation, this setting has no affect on the Server’s
behavior.
Additional Query Constant File Sections
In
addition to the standard
[Database] section of
the query constants files, other sections may be added for application-specific
purposes, such as:
- The application extends a back end system
that may be deployed on different database systems, and certain
values or functionality is different within those systems.
- The application extends a back end system deployed in multiple
languages. As a result, certain constant values, such as Y and N
flags, are different in different languages. In this case, the values
can be represented as options here, and these would then be the
items referenced via SDML in the SQL statements. When deployed in
a different language, the constant file can be updated with the
proper language-specific value.
- Additional functions may be needed beyond those in the standard
version of the query constants file. In this case it may be considered
cleaner to add a separate section to differentiate between those
functions added after the fact, and those provided by default.
In
almost all situations the contents of this file are modified by,
or at the direction of the application developer, as the values
are referenced as a part of the application’s business logic. Changes
made at implementation time should only be performed with an understanding
of the affect such changes will have and the ways in which the configuration
options are used. Furthermore, the default values provided with
the Agentry
Server should
only be changed in the rarest of situations, particularly those
related to the formats of date and time values.
Configuring the Query Constant Files for a System Connection
It is possible for a single SQL Database system
connection to make use of multiple query constant files. It is also
possible to change the name of the file referenced for the connection.
This is accomplished by changing the configuration option queryConstantFiles in
the [SQL-n] section within the Agentry.ini file.
If
a different file name is to be used, or if multiple constant files
are needed for a system connection, the
queryConstantFiles configuration
option should be modified to reference this. For multiple file listings,
each file name is separated by a semi-colon.
-- different file name
[SQL-1]
...
queryConstantFiles=MyConstantFile_sd.ini
...
--- multiple constant files
[SQL-1]
...
queryConstantFiles=oracle_sd.ini;MyApplicationConstantFile_sd.ini
If a different file is to be used from the default
file, that file must contain the [Database] section,
including all configuration options.