Some non-standard features of earlier versions of Mimer SQL are retained for backward compatibility. Where these features have equivalents in the standard implementation, only the standard form is documented in the main body of this manual. Non-standard forms are documented below. Use of the standard forms is strongly recommended in new applications.
Non-standard Mimer SQL features which do not have equivalents in the standards are documented in the main body of the manual.
Host variables with negative indicator variables are assigned null. In previous versions of Mimer SQL, the indicator variable could be given any negative value. The accepted standards however define the value of -1 as an indicator for null and reserve other negative values for future use. Use of values other than -1 is therefore not recommended.
|
Operator |
Standard form |
Alternative form(s) |
|---|---|---|
|
string concatenation |
|| |
|
|
not equal to |
<> |
!= |
|
less than or equal to |
<= |
|
|
greater than or equal to |
>= |
|
ALTER IDENT Change Password
The ALTER IDENT USING syntax and the ALTER IDENT IDENTIFIED BY syntax to change password are deprecated and have been replaced by the SET PASSWORD syntax. The old syntax is still supported for backward compatibility.
CREATE IDENT AS OS_USER
The possibility to specify OS_USER as ident type is deprecated. The syntax is still supported for backward compatibility.
GET DIAGNOSTICS EXCEPTION INFO
The SQL-2008 standard has adjusted the GET DIAGNOSTICS syntax. The keyword EXCEPTION has been replaced with the keyword CONDITION. As a consequence the exception-clause is now called condition-clause.
The old GET DIAGNOSTICS EXCEPTION INFO syntax is still supported for backward compatibility.
JOIN Without SELECT
The way to write a join without starting with the SELECT keyword was removed from the SQL standard in 2003, and has also been removed from the Mimer SQL syntax.
So instead of just writing
table1 INNER JOIN table2 ON table1.col1 = table2.co2
specify the SELECT keyword, like:
SELECT * FROM table1 INNER JOIN table2 ON table1.col1 = table2.co2
CONNECT
The syntax of the standard CONNECT statement differs from that in earlier versions of Mimer SQL. The previous form is still supported for backward compatibility.
Backward compatibility syntax:
Note:The CONNECT statement and the standard-compliant CONNECT TO statement have different default modes for SET TRANSACTION START. CONNECT uses SET TRANSACTION START EXPLICIT, while CONNECT TO uses SET TRANSACTION START IMPLICIT.
ORDER BY Ordinal Position
Using an integer value to represent the ordinal position in an ORDER BY specification is deprecated due to changes in the SQL standards.
SELECT NULL
The use of the keyword NULL in a select list is still supported for backward compatibility but should be regarded as a deprecated feature in Mimer SQL.
Instead, specify CAST(NULL AS data-type).
Example
SELECT c1, int_col FROM t1
UNION
SELECT c1, CAST(NULL AS integer) FROM t2;
SET TRANSACTION CHANGES
Following the introduction of the SET TRANSACTION READ and SET TRANSACTION ISOLATION LEVEL options, the SET TRANSACTION CHANGES options are no longer supported. The options are currently supported for backward compatibility, but will be removed in the next version of Mimer SQL.
CREATE IDENT
The keyword USING is now used when specifying the password-string for the ident being created. The use of IDENTIFIED BY is deprecated.
The following syntax is supported for backward compatibility only:
ENTER
The keyword USING is now used when specifying the password for the program ident being entered. The use of IDENTIFIED BY is deprecated.
The following syntax is supported for backward compatibility only:
MIMER_SW
The right to create and manage databank shadows was previously granted by having EXECUTE on the program ident MIMER_SW.
This has now been replaced by the SHADOW system privilege.
MIMER_BR
The right to perform backup and restore operations was previously granted by having EXECUTE on the program ident MIMER_BR.
This has now been replaced by the BACKUP system privilege.
MIMER_SC
The right to execute the UPDATE STATISTICS statement was previously granted by having EXECUTE on the program ident MIMER_SC.
This has now been replaced by the STATISTICS system privilege.
The following functions have been deprecated in the current version of Mimer SQL.
BIT_LENGTH
The BIT_LENGTH function is no longer supported. The function OCTET_LENGTH’s return value multiplied by 8 can be used as an alternative.
See OCTET_LENGTH.
Arithmetic Functions
The functions DACOS, DASIN, DATAN, DATAN2, DCOS, DCOT, DDEGREES, DEXP, DLOG, DLOG10, DPOWER, DRADIANS, DSIN, DSQRT and DTAN are no longer supported.
In Mimer version 11 the following functions are supported: ACOS, ASIN, ATAN, ATAN2, COS, COSH, COT, EXP, LN, LOG10, POWER, SIN, SINH, SQRT, TAN and TANH.
The following scalar functions have been deprecated in the current version of Mimer SQL. They will be re-introduced in a future version with changed functionality.
CURRENT_TIME
This function, in its present form, has been removed since it’s deprecated.
The same functionality is now provided by the LOCALTIME function.
CURRENT_TIMESTAMP
This function, in its present form, has been removed since it’s deprecated.
The same functionality is now provided by the LOCALTIMESTAMP function.
The MIMER and INFO_SCHEM predefined system views on the data dictionary tables, that were supported in previous versions of Mimer SQL have now been replaced by the standard system views belonging to the schema INFORMATION_SCHEMA, which is owned by ident SYSTEM.
The FIPS_DOCUMENTATION system views have been removed.
The following SQL descriptor host variable types are deprecated:
|
Host variable |
TYPE |
|---|---|
|
MIMER_BINARY |
-13 |