MSCASEINSENSITIVE=X
The database components such as table, view, attribute, index, reference, constraint and stored procedure, created after above setting was made, support Case Insensitivity.
This setting must be done before the database components are created.
2.2. Using Empress Dynamic/Embedded SQL in Persistent Stored Modules
Empress Persistent Stored Modules (PSM) are a set of dynamically loadable
libraries that can contain user-defined functions, procedures, aggregate
functions, operators (together they are referred to as routines)
and triggers. User-defined procedures storable as database objects
are commonly called "stored procedures". In Empress Version 8.62,
user can write routines using Empress Dynamic SQL and Embedded SQL
Interface (DSQL/ESQL).
2.2.1 How To Compile PSM Routines Written By DSQL/ESQL
Routine written in DSQL/ESQL can be precompiled with Empress utility
"empesql" and, then compiled with "emppsmcc" to generate dynamic link
library.
2.2.2 New Reserved Cursor Names Used in Triggers
The following two reserved cursor names:
emptrig_cursor_old
emptrig_cursor_new
for triggers are introduced in Empress Version 8.62. These cursors can be used to access the old/new inserted/updated/deleted record values.
In Empress trigger, the FETCH syntax is defined as follows:
EXEC SQL FETCH | emptrig_cursor_old | [attr_list]
| emptrig_cursor_new |
INTO | value_list |
| SQL descriptor desc_name |
Note:
For reserved cursors, user cannot declare, open, close, drop and use fetch options, such as next, prior, absolute <position>, related <position> to operate them.
The UPDATE syntax is as follows:
EXEC SQL UPDATE |<table>| SET |attr-list VALUES variable-list |
|CURRENT| |attr = variable {,attr = variable}|
WHERE CURRENT OF emptrig_cursor_new;
Note:
For UPDATE command, user can assign table name or use keyword "current" to assign the current table. If the table name is assigned, the table check will be executed.
"emptrig_cursor_old" cannot be used in UPDATE command. If UPDATE command is used in "after trigger", it will not have any effect.
The reserved cursors can not be used in a regular DSQL/ESQL program. The following message will be produced:
"fetch/update trigger cursor and abort trigger operation only supported in trigger procedure"
EXEC SQL TRIGGER ABORT;
It can only be used in a trigger procedure, otherwise the following message will be produced:
"fetch/update trigger cursor and abort trigger operation only supported in trigger procedure"
EXEC SQL MEMORY ALLOCATE <variable> '(' | constant | ')'
| <variable> |
EXEC SQL MEMORY REALLOCATE <variable> '(' | constant | ')'
| <variable> |
The following statement can be used to free memory.
EXEC SQL MEMORY FREE <variable> {, <variable>}
EXEC SQL INIT;
EXEC SQL DATABASE is "db";
EXEC SQL EXIT;
To change a database in PSM routines and triggers a "db:table" construct can be used.
A Role generally refers to a title or a set of duties, which is a named bundle of zero or more privileges. Roles can be created and dropped using Standard SQL statements.
Roles allow to collect users into a single unit against which privileges can be applied.
In regard to "user", it is either an actual person or an application program that has access to SQL-data. Users are outside schemas. The methods used to create, drop and map it to actual person/application programs are implementation-defined. Empress doesn't provide special commands to create, drop and map user.
Following ANSI SQL-99 standard, Empress supports "create role", "grant role", "revoke role" and "drop role" commands.
The syntax for "create role" command is:
create role ROLE;
The "create role" statement specifically defines ROLE to be a Role rather than a User. This ROLE cannot be a defined user in the database schema, such as dba, user, PUBLIC, etc.
The syntax for "grant role" command is:
grant ROLE {, ROLE } to GRANTEE {, GRANTEE } [ WITH ADMIN OPTION ]
The "grant role" statement grants the use of one or more ROLEs to one or more GRANTEEs, including (optionally) PUBLIC. The grantor of the ROLEs must, of course, hold these ROLEs as grantable ROLEs (that is, WITH ADMIN OPTION).
The option WITH ADMIN OPTION clause defines grantable ROLES.
The syntax for "revoke role" command is:
revoke [ ADMIN OPTION FOR ] ROLE {, ROLE } FROM GRANTEE {, GRANTEE }
{ RESTRICT | CASCADE }
The "revoke role" statement revokes the use of one or more ROLEs from one or more GRANTEEs.
The ADMIN OPTION FOR clause allows grantor to revoke only the grantability of a ROLE.
If the "revoke role" statement specifies CASCADE, it cascades down to revoke roles that would otherwise be abandoned. If the "revoke role" statement includes RESTRICT, the revoke succeeds only if the role being revoked has no dependent roles.
The syntax for "drop role" command is:
drop role ROLE;
Only creator of ROLE can drop it. For each user/role, it was granted use of the role, RDBMS will call the following revoke command.
revoke ROLE from GRANTEE restrict.
For example, if user joe enters the following sequence of commands:
joe: create role SUPPORT;
joe: grant SUPPORT to peter with admin option;
joe: grant SUPPORT to TECH;
joe: drop role SUPPORT;
where peter is another user and SUPPORT and TECH are roles. "drop role" command will implicitly invoke the following two commands:
revoke SUPPORT from TECH restrict;
revoke SUPPORT from peter restrict;
However, if peter granted SUPPORT role to another user before joe tried to drop the SUPPORT role, "drop role" command will fail since it cannot remove dependant roles.
Roles can be viewed using "display role" command. The syntax is:
display role [ROLE] [all] [| into | FILE];
| onto |
This command displays role[s] in a database and it can be used in Empress interactive SQL. If ROLE is not specified, the information on all database roles will be displayed. More details can be displayed with keyword "all".
The output of a display command may be redirected from the terminal into a new file or appended to an existing file by specifying "into" or "onto", respectively, and a filename.
For further information on "display role" information, enter:
help display;
For further information on database roles, enter:
help role;
select attr1 = "ABC", attr2 from tableX
where attr1 = "ABC" will cause the first column in the result set to have a value "ABC".
For example,
select attr1 from t1
union all
select attr2 from t2
Assuming attr1 is defined as char(10) and attr2 is defined as char (20), the above task can be achieved in Empress Version 8.60 by modifying the command into the following syntax:
select attr1 convert to char(20) from t1
union all
select attr2 from t2
In Empress Version 8.62, Empress will conduct the conversion automatically.
The conversion is conducted for the following four data type categories.
Numeric
Char/Text
Bulk
Date/Time/Microtimestamp
Empress does not support cross-category conversion. Cross-category conversion
can be achieved by explicitly using "CONVERT TO" functionality.
2.7 New Variables
In the version 8.62, several new variables are introduced. Here is the
list of them:
All new variables are documented in
[Volume A5: Database Administrator's Guide]
2.8 User Defined Lock Granularity
In Empress Version 8.62, new user defined lock granularity is introduced
via the LOCK LEVEL command. The LOCK LEVEL command sets the level of
locking on a table. The new extensions for RECORD and GROUP LOCK LEVEL
allow for "n" granularity.
New syntax:
LOCK LEVEL [ON] table [IS] |TABLE |;
|GROUP [(n)] |
|RECORD [(n)] |
|NULL |
where:
For example:
If 100 consecutive records are updated in a transaction in a table with RECORD level locking, until a transaction is committed or rolled back, there will be 100 record locks in the lock manager.
If a RECORD (5) level locking is used in the above case, approximately 20 locks will be present in the lock manager before a transaction is committed or rolled back.
Choosing a right value for lock granularity (i.e. a number of locks in a
"PAGE") will be a trade-of between performance and concurrency.
2.9 Triggers On Select Statements
In addition to the triggers on DELETE, UPDATE and INSERT statements,
Empress RDBMS introduces a unique functionality of triggers on SELECT
statements. A SELECT trigger is a procedural processing element, stored
in the database and executed automatically by the Empress RDBMS engine
when applications or users perform SELECT commands on the database.
Empress RDBMS supports both the "row-level" and "statement-level" SELECT triggers.
The ability to fire a trigger on SELECT statements gives Empress a competitive advantage and it can be used to achieve the following objectives:
The new SELECT trigger command syntax is adapted to the standard "create trigger" command.
create trigger TRIGGER | before |
| after |
| delete |
| insert |
| update [ of ( ATTR_NAME {, ATTR_NAME}) |
| select [ of ( ATTR_NAME {, ATTR_NAME}) |
{ , | delete | }
| insert |
| update [ of ( ATTR_NAME {, ATTR_NAME}) |
| select [ of ( ATTR_NAME {, ATTR_NAME}) |
on TABLE [for each row [when CONDITION]]
execute PROCEDURE_NAME;
A database trigger is a procedure without parameter which is associated with a table. Empress RDBMS automatically executes a trigger when a specified SQL statement is issued against the table.
The trigger can be specified to fire either before the statement/operation (INSERT, DELETE, UPDATE) is attempted on a row/record or after the operation has been completed. In the case of the SELECT trigger however, it can be specified to fire only after the SELECT operation has been completed.
UPDATE and SELECT trigger can specify the corresponding attribute lists. If no attribute is specified, it means all attributes of a table.
The "when CONDITION" is associated with "for each row" option, if "for each row" is not assigned in create trigger command, the "when CONDITION" cannot be assigned. Once the "when condition" is assigned for "for each row" option, only in the case when the accessed row satisfies the condition, the trigger will be fired.
The CONDITION in "when" clause is similar to that in the "where" clause, except that all attributes must be in the table associated with the trigger and qualified by either "new." or "old.". Subqueries are not supported.
To remove a trigger, "drop trigger" command is used.
Syntax:
drop trigger TRIGGER;
For further information on display trigger information, enter:
help display;
For further information on enable/disable trigger or change priority of trigger(s), enter:
help alter;
In version 8.62 Empress made its ODBC/JDBC clients multi-thread capable.
In previous Empress versions Empress JDBC driver, for example, could be used in threads but the operations done in threads would be serialized. In version 8.62 both ODBC and JDBC client/server drivers could be used in threads achieving true parallelism.
Empress ODBC/JDBC Local Access drivers are thread-safe (i.e. could be used in threaded application), but they don't provide for a true parallelism.
On a multiprocessor system, threading allows an application to perform more than one independent computation at the same time, providing for a true parallelism. A computation intensive threaded application running on a two processor architecture can achieve great performance improvement compared to a single threaded version of the same application. However, database applications are usually not computation intensive but I/O intensive and threading might not lead to a performance improvement.
Furthermore, threading has its cost. Despite powerful advantages of threading, there are some clear drawbacks, such as: an introduction of computing overhead (thread synchronization and scheduling) and a requirement for strong programming discipline when developing applications. Multi-thread applications are also significantly harder to debug.
Database application programmers should take a decision "to thread or not to thread" in their applications very carefully.
To acquire true parallelism in Empress database applications (using
ODBC or JDBC client/server drivers), one should issue concurrent SQL
operations (e.g. issue concurrent queries from different threads) by
starting up multiple connections and by associating those connections
with each thread.
2.11 Empress JAVA Interactive SQL
Empress JAVA Interactive SQL is a graphical JAVA SQL client program configured as a Stand-alone JDBC program, that allows users to view the structure of a local Empress database using JDBC , browse data in Empress tables and issue SQL commands on Empress tables. This allows users in local mode to set up and define Empress databases, request information from Empress databases and administer Empress databases using a graphical JAVA environment.
Empress JAVA Interactive SQL can be invoked by using "empjavasql" command.
2.12 Support for ANSI SQL-99 SET Operations
In version 8.62 Empress application programming interfaces support ANSI SQL-99 UNION, UNION ALL, EXCEPT, EXCEPT ALL, INTERSECT and INTERSECT ALL operations, which are based on the well-known union, difference and intersection operations of the SET Theory.
For more information please see [Volume A4 SQL Reference]
2.13 Support for ANSI SQL-99 Outer Joins
In version 8.62 Empress application programming interfaces also support ANSI SQL-99 Outer Joins, such as:
LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN
For more information please see [Volume A4 SQL Reference]
Empress import utility empimpt, in the version V8.62, is capable of importing database table(s) from an export file created in a different codeset from a native codeset of the version invoked by empimpt.
empimpt includes the codeset option and also displays available translatable code sets.
Empress utility empvers, in the version V8.62, also prints the native codeset information.
For example:
empimpt -codeset SJIS db1.exp db2
will treat data in db1.exp export file as stored in codeset SJIS in order to translate it into the native codeset (e.g. EUC-JP) of the version invoked by empimpt.
For more information please see "man" pages for Empress import utility empimpt (i.e. man empimpt, or empimpt -help)
There are several new reserved keywords introduced in version 8.62. Additional reserved keywords are:
?
ADMIN
AUTHORIZATION
BINARY
CACHE
CASCADE
CAST
CHAR
CHARACTER
CIPHER
CONCAT
CURRENT
DAY
DBA
DYNAMIC
ENCRYPTED
ENDVALUE
FALSE
INCREMENT
LARGE
MASTERS
MINUS
MINUTE
MONTH
NATIONAL
NCHAR
NEXTVALUE
NOLOCK
NOSHMEM
OBJECT
OJ
POSITION
REFERENCE
REPLICATES
RESTRICT
RESULT
ROLE
SCHEMA
SECOND
SEQID
SEQUENCE
SETS
SQL_TSI_DAY
SQL_TSI_FRAC_SECOND
SQL_TSI_HOUR
SQL_TSI_MINUTE
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_SECOND
SQL_TSI_WEEK
SQL_TSI_SECOND
SQL_TSI_YEAR
STARTVALUE
SUBSET
TIME
TIMESTAMP
TIMESTAMPADD
TIMESTAMPDIFF
TRUE
TYPE
VARYING
WEEK
WITHOUT
YEAR
{
{D
{FN
{OJ
{TS
}
For a complete list of all reserved keywords, please refer to our
Reserved Keywords document.
The following reserved keywords are made obsolete:
CENTER
CENTRE
INPUT
LEFTRIGHT
MOVE
PRECISION
SHARE
A significant amount of work in version 8.62 was done to increase user's capability to handle reserved keywords.
For example, although SIN, COS and LOG are reserved words for built-in mathematical functions, one could use them as attribute names in CREATE TABLE command:
CREATE TABLE TABLEX (SIN INT, COS INT, LOG CHAR(10));
This was not possible in previous Empress versions.
In the version V8.62, Empress JDBC Category 2 drivers support Java Data Sources and Java Naming and Directory Interface (JNDI).
The JDBC 2.0 standard extension API introduced the concept of data sources (javax.sql.DataSource Interface). Data Sources are standard, general-use objects for specifying databases or other resources to use. Data sources can optionally be bound to Java Naming and Directory Interface (JNDI) entities so that users can access databases by logical names, for convenience and portability.
Empress has implemented the DataSource interface by using a "Basic implementation" defined in Sun Microsystems JDBC specification.
An alternative to the DriverManager facility, a DataSource object has properties that can be modified when necessary. If the data source is moved to a different server or to a different location on the same server, or the port number of the Empress Connectivity Server has changed, all these properties could be modified in the Data Source object. The benefit is that because the data source's properties can be changed, user's Java application accessing that data source does not need to be changed.
This functionality is a more powerful alternative to the previous JDBC DriverManager facility. For further information about data sources and JNDI, refer to the Sun Microsystems specification for the JDBC package.
Java applications using Java Data Sources and JNDI would have to include (i.e. import) the following two groups of methods:
import javax.naming.*;
import javax.sql.DataSource;
With V8.62 Microsoft Windows ODBC applications can use Empress ODBC Local Access Driver with ODBC Driver Manager. This opens a door to usage of Empress ODBC Local Access Driver with 3rd party ODBC compliant tools such as Microsoft Visual Basic or Microsoft Access.
In the version V8.62, both ODBC and JDBC Local Access Drivers support multiple connections. Previous versions of those drivers could handle only one connection at the time.
A new Empress ODBC Data Source Name Setup allows testing a connection to a Data Source with supplied parameters. In this way users can spot connection problems before they run the application.
In previous Empress ODBC driver versions, whenever ODBC driver is upgraded user needed to update a Data Source with the association to a new driver. With V8.62 Empress provides standard (default) labels for ODBC Drivers so that Data Sources won't require a change when ODBC Driver is upgraded.
New labels ("Name" in "ODBC Data Source Administrator") used for Empress ODBC drivers are:
Empress ODBC Local Access Interface [Default] Empress ODBC Interface [Default]
Empress V8.62 ODBC driver supports several Microsoft .NET technologies. Empress V8.62 ODBC driver can be used in .NET environment to access Empress database through OLEDB.NET provider or ODBC.NET, a data provider that is an add-in component to the Microsoft .NET framework.
In version 8.62 Empress introduces a new scrollable cursor functionality in Dynamic SQL Interface (i.e. SQL Precompiler). The changes in the syntax for DECLARE CURSOR (SENSITIVE, INSENSITIVE, ASENSITIVE and SCROLL options) and FETCH (FIRST, LAST, ABSOLUTE, RELATIVE options) commands were made in order to acquire additional capability.
The similar functionality is also available in Empress V8.62 ODBC interface.