To increase efficiency of processing SQL queries against Windows Server services, emissary RT: Servers makes use of an in-memory caching system. This cache (if enabled in the Data Source options) creates a snapshot of the respective service, greatly increasing performance. This cache is initially built when the ODBC connection is established, and is maintained for the lifetime of the connection. If the snapshot is disabled, emissary RT: Servers will rescan and update its cache (if necessary) when it executes a SQL query. This ensures all data is 100% up-to-date.

When making use of emissary RT: Servers in custom applications, as the cache is built at connection time and is maintained for the lifetime of the connection, it is important to reuse the ODBC connection when possible. This can be more challenging in a web application environment, and may require changes to both the web server configuration and API used. An example includes PHP's odbc_pconnect function and a compatible Apache configuration (non-CGI mode), which creates a persistent connection across each request (for the session lifetime).

When executing queries containing related tables (either via JOIN or appropriate WHERE clauses), emissary RT: Servers is optimized for predicates comparing the equality of foreign keys to primary keys. E.g. "SELECT * FROM DHCPScope LEFT JOIN DHCPLease ON DHCPScope.ID = DHCPLease.ScopeID", "SELECT * FROM DHCPScope, DHCPLease WHERE DHCPScope.ID = DHCPLease.ScopeID", "SELECT * FROM DNSZone JOIN DNSDomain ON DNSZone.ID = DNSDomain.ZoneID JOIN DNSRecord ON DNSDomain.ID = DNSRecord.DomainID", etc. Predicates may contain additional expressions, as long as OR operators do not allow for potential additional matches in the join. Any non-optimized predicate with valid syntax may be used, but performance will degrade significantly, as the system must internally perform a full cross join.

Additionally, all tables are indexed against their ID column, and will perform significantly faster with WHERE clauses that select for specific IDs, via inline values and/or parameters. As with related table optimization above, WHERE clauses optimized for ID indices may contain additional expressions, as long as OR operators do not allow for potenital additional matches.

The execution time involved for a SQL query is dependent on the number of records in the service being queried, the speed of the underlying hardware hosting and querying the services, how many services are being queried, etc. Because it may be desired to execute a query that may take a significant time to process, emissary RT: Servers provides an interactive mode that shows both a progress meter, and allows the cancelation of a query. Note - canceling an INSERT, UPDATE or DELETE query is not ACID compliant - changes are made to a service in real-time, and are not automatically rolled back. Please construct a corresponding SELECT query for testing before executing any potentially destructive INSERT, UPDATE or DELETE queries.

SELECT Statement
 

Note: SELECT statements used with a FROM command will retrieve data from the table(s) specified in the table_expression (see below). Usage without a FROM command will return a single row, executing any specified expressions in the select_expression (see below). At least one select_expression is required.

UPDATE Statement
 

Note: UPDATE statements used with an ORDER BY command will control the order in which file operations are performed. This can be useful if the order of updating filenames may otherwise cause a name collision with pre-existing files. If the SET command includes any expressions with column names, the value of the field in the currently updating row will be used. LIMIT will constraint which files are updated from the total UPDATE rowset.

INSERT Statement
 

Note: INSERT statements may use either syntax shown above. If the first syntax is used without specifying column names, the number of VALUES/VALUE expressions specified must equal the number of columns in the table. For read only fields, the value specified is ignored.

DELETE Statement
 

Note: DELETE statements used with an ORDER BY command will control the order in which file operations are performed. LIMIT will constraint which files are deleted from the total DELETE rowset.

Select Expressions
 

Note: Table and column names may be delineated using the ` character.

Table Expressions
 

Note: Table and column names may be delineated using the ` character. Comma separated tables specified after the first table in a table_expression before JOIN commands will be treated as CROSS JOINed tables.

General Expression Operators and Functions
 

SQL Types

CHAR, VARCHAR, LONG VARCHAR, BINARY, SMALLINT, INTEGER, FLOAT, DOUBLE, TIMESTAMP

Miscellaneous ODBC Support

"{d '1995-01-15'}" style date literals, unnamed parameters via '?' literals, single prepare/multiple execution model with parameter updating, thread-safety. Contact Synthetic Dreams regarding any further ODBC support questions.

Usage Notes

1. INSERT and DELETE statements run against the 'DHCPScope' table will create and delete DHCP scopes on the target server, respectively
2. As noted, the 'IP' and 'Mask' fields must be specified, at minimum, for INSERT statements. Any non read-only field may also be specified in INSERT statements to be applied to newly created scopes
3. DELETE statements run against the 'DHCPScope' table will delete corresponding entries in the 'DHCPLease' table

Usage Notes

1. INSERT and DELETE statements run against the 'DNSRecord' table will create and delete DNS records on the target server, respectively
2. As noted, the 'DomainID', 'Type', 'Name', and 'Data' fields must be specified, at minimum, for INSERT statements. Any non read-only field may also be specified in INSERT statements to be applied to newly created records
3. The appropriate DomainID must exist to create record
4. The 'data' field must be formatted as records appear in the zone file. E.g. "192.168.0.100" for an "A" record, "10 mail.example.com" for an "MX" record, "example.com." for a "CNAME" record, etc