Installed with the Agentry Server are two query constants files provided for use with SQL Database system connections: Oracle_sd.ini and SqlServer_sd.ini. Each is intended for use with the database type for which they are named. The contents of these files include a single configuration section, [Database], within which are a set of configuration options listed as key and value pairs. Within each file exist the same keys. The values for these items are different in each file.
The purpose of these values is to provide support for applications which may synchronize with the same back end system, but which may be driven by different database types. These files support query reuse between these systems by providing expressions matching a given vendors variation in support of the ANSI SQL and database-type specific functions. The contents of these files are listed next, with the value for each key listed for both files:
| key | Oracle_sd.ini Value | SqlServer_sd.ini Value |
|---|---|---|
| name | Oracle | SqlServer |
| getSystemTime | sysdate | getdate() |
| timeStampFormat | to_date('%m/%d/%Y %H:%M:%S', 'mm/dd/yyyy HH24:MI:SS') | '%m/%d/%Y %H:%M:%S' |
| dateFormat | to_date('%m/%d/%Y', 'mm/dd/yyyy') | %m/%d/%Y' |
| timeFormat | to_date('%H:%M:%S', 'HH24:MI:SS') | '%H:%M:%S' |
| tempdate | to_date('01/02/1901 12:00:00', 'mm/dd/yyyy HH24:MI:SS') | convert(DATETIME, '01/02/1901 12:00:00') |
| substring | substr | substring |
| stringcat | || | + |
| charFunction | chr | char |
| nullFunction | nvl | isNull |
| singleRow | from dual | null |
| unicodePrefix | N | N |
| terminalErrorCodes | 00028;01001;01012;03113;03114; 12203;12500;12505;12535;12571 |
0;1;2;4;5;11;53 |
| retryWithChangeErrorCodes | null | null |
| retryWithoutChangeErrorCodes | 00060 | 00060 |
| fatalWithMessageErrorCodes | null | null |
| fatalWithoutMessageErrorCodes | null | null |
<<database.keyName>>
These data tags will return the value as specified in the query constant file in use for the SQL Database system connection. The file used by a system connection is set in the [SQL-n] section of the agentry.ini file by setting the configuration option queryConstantFiles.
This file is processed by the Server at startup and data tags are created and made available to all SQL scripts processed by the Agentry Server.
<<sectionName.keyName>>
Following is a description of each of these data tags intended purpose:
| Tag Name | Description |
|---|---|
| <<database.name>> | Returns the name of the database type for which the file was created. This value should not be altered in the source file |
| <<database.getSystemTime>> | Returns the database-specific system date and time function. |
| <<database.timeStampFormat>> | Returns the database-specific date and time tokens used to format date and time values. This setting is used by the Agentry Server when expanding date and time data tags and should not be altered in the source file. This may be passed to the format parameter for date and time values within data tags. |
| <<database.dateFormat>> | Returns the database-specific date tokens used to format date and time values. This setting is used by the Agentry Server when expanding date data tags and should not be altered in the source file. This may be passed to the format parameter for date and time values within data tags. |
| <<database.timeFormat>> | Returns the database-specific time tokens used to format date and time values. This setting is used by the Agentry Server when expanding time data tags and should not be altered in the source file. This may be passed to the format parameter for date and time values within data tags. |
| <<database.tempdate>> | Returns the date and time of January 2, 1901, 12:00:01 am in the database-specific format for dates and times. This value can be used in synchronization when last update or other date and time values for data definitions contain invalid date and times. If a different date and time is necessary it can be altered in the query constants file. |
| <<database.substring>> | Returns the database-specific function for extracting a substring from a source string. |
| <<database.stringcat>> | Returns the database-specific character for concatenating string values. |
| <<database.charFunction>> | Returns the database-specific function for converting values to the character or VARCHAR data type. |
| <<database.nullFunction>> | Returns the database-specific function used to test values for null and optionally replace those values with a default. |
| <<database.singleRow>> | Returns the required FROM portion of a query to select values from nothing. |
| <<database.unicodePrefix>> | Returns the prefix to append to values to indicate they are encoded in unicode. |