User's Guide
PART 5. The Adaptive Server Family
CHAPTER 30. Transact-SQL Compatibility
Some differences in behavior between Adaptive Server Anywhere and Adaptive Server Enterprise can be eliminated by selecting appropriate options when creating a database or, if you are working on an existing database, when rebuilding the database. Other differences can be controlled by connection level options using the SET TEMPORARY OPTION statement in Adaptive Server Anywhere or the SET statement in Adaptive Server Enterprise.
Creating a Transact-SQL-compatible databaseThis section describes choices that must be made when a database is created or rebuilt.
Here are the steps you need to take. The remainder of the section describes what the options are that .
To create a Transact-SQL compatible database from Sybase Central:One page of the Create Database wizard is named Choosing the Database Attributes. This page provides a button that sets each of the available choices to emulate Adaptive Server Enterprise..
To create a Transact-SQL compatible database using dbinit:Enter the following command at a system prompt:
dbinit -b -c -k db-name.db
To create a Transact-SQL compatible database using the CREATE DATABASE statement:Enter the following statement, for example in Interactive SQL:
CREATE DATABASE 'db-name.db' ASE COMPATIBLE
In this statement, ASE COMPATIBLE meanse compatible with Adaptive Server Enterprise.
By default, string comparisons in Adaptive Server Enterprise databases are case-sensitive, while those in Adaptive Server Anywhere are case insensitive.
When building a Adaptive Server Enterprise-compatible database using Adaptive Server Anywhere, you should choose the case-sensitive option.
If you are using Sybase Central, this option is in the Create Database wizard.
If you are using the dbinit command-line utility, specify the -c command-line switch.
When building an Adaptive Server Enterprise-compatible database using Adaptive Server Anywhere, you should choose the option to ignore trailing blanks in comparisons.
If you are using Sybase Central, this option is in the Create Database wizard.
If you are using Interactive SQL for OS/2 or Windows 3.x, this option is on the Create Database dialog box that appears when you select Create Database from the Database Tools window and click Create.
If you are using the dbinit command line utility, specify the -b command line switch.
With this option chosen, the following two strings are considered equal by both Adaptive Server Enterprise and Adaptive Server Anywhere:
'ignore the trailing blanks ' 'ignore the trailing blanks'
If this option is not chosen, the two strings above are considered different by Adaptive Server Anywhere.
A side effect of this option is that strings are padded with blanks when fetched by a client application.
Older versions of Adaptive Server Anywhere employed two system views whose names conflict with the Adaptive Server Enterprise system views provided for compatibility. These views are SYSCOLUMNS and SYSINDEXES. If you are not using Watcom SQL 4.0 and you are using Open Client or JDBC interfaces, you should create your database excluding these views. You can do this with the dbinit -k command-line switch.
If you do not use this option when creating your database, the following two statements return different results:
SELECT * FROM SYSCOLUMNS ; SELECT * FROM dbo.SYSCOLUMNS ;
To drop the system views from an existing database:Connect to the database as a user with DBA authority.
Execute the following statements:
DROP VIEW SYS.SYSCOLUMNS ; DROP VIEW SYS.SYSINDEXES
Caution
Ensure that you do not drop the dbo.SYSCOLUMNS or dbo.SYSINDEXES system view.
Setting options for Transact-SQL compatibilityAdaptive Server Anywhere database options are set using the SET OPTION statement. Several database option settings are relevant to Transact-SQL behavior.
By default, Adaptive Server Enterprise does not allow NULLs on new columns unless they are explicitly declared to allow NULLs. Adaptive Server Anywhere permits NULL in new columns by default, which is compatible with the SQL/92 ISO standard.
To make Adaptive Server Enterprise behave in a SQL/92-compatible manner, use the sp_dboption system procedure to set the allow_nulls_by_default option to true.
To make Adaptive Server Anywhere behave in a Transact-SQL-compatible manner, set the allow_nulls_by_default option to OFF. You can do this using the SET OPTION statement as follows:
SET OPTION PUBLIC.allow_nulls_by_default = 'OFF'
By default, the Adaptive Server Enterprise treatment of identifiers and of strings differs from the Adaptive Server Anywhere behavior, which matches the SQL/92 ISO standard.
The quoted_identifier option is available in both Adaptive Server Enterprise and Adaptive Server Anywhere. You should ensure that the option is set to the same value in both databases, for identifiers and strings to be treated in a compatible manner.
For SQL/92 behavior, set the quoted_identifier option to ON in both Adaptive Server Enterprise and Adaptive Server Anywhere.
For Transact-SQL behavior, set the quoted_identifier option to OFF in both Adaptive Server Enterprise and Adaptive Server Anywhere. If you choose this, you can no longer use identifiers that are the same as identifiers, enclosed in double quotes.
For more information on the quoted_identifier option, see QUOTED_IDENTIFIER option.
Transact-SQL defines a timestamp column with special properties. With the automatic_timestamp option set to ON, the Adaptive Server Anywhere treatment of timestamp columns is more similar to Adaptive Server Enterprise behavior.
With the automatic_timestamp option set to ON in Adaptive Server Anywhere (the default setting is OFF), any new columns with the TIMESTAMP data type that do not have an explicit default value defined are given a default value of timestamp.
For information on timestamp columns, see The special Transact-SQL timestamp column and data type.
Both Adaptive Server Enterprise and Adaptive Server Anywhere support the string_rtruncation option, which affects whether or not error messages are reported when an INSERT or UPDATE string is truncated. You should ensure that the option is set to the same value in each database.
For more information on the STRING_RTRUNCATION option, see STRING_RTRUNCATION option.
For more information on database options for Transact-SQL compatibility, see Transact-SQLcompatibility options.
Case-sensitivityCase sensitivity in databases refers to the following:
Data The case sensitivity of the data is reflected in indexes, in the results of queries, and so on.
Identifiers Identifiers include table names, column names, and so on.
User IDs and passwords Case sensitivity of user IDs and passwords is treated differently to other identifiers.
The case-sensitivity of Adaptive Server Anywhere data in comparisons is decided when the database is created. By default, Adaptive Server Anywhere databases are case-insensitive in comparisons, although data is always held in the case in which it is entered.
Adaptive Server Enterprise's sensitivity to the case (upper or lower) of data depends on the sort order installed on the Adaptive Server Enterprise system. Case sensitivity can be changed for single-byte character sets by reconfiguring the Adaptive Server Enterprise sort order.
Adaptive Server Anywhere does not support case-sensitive identifiers. In Adaptive Server Enterprise, the case sensitivity of identifiers follows the case sensitivity of the data.
In Adaptive Server Enterprise, user-defined data type names are case sensitive. In Adaptive Server Anywhere, they are case insensitive, with the exception of Java data types.
In Adaptive Server Anywhere, user IDs and passwords follow the case sensitivity of the data. The default user ID and password for case sensitive databases are upper case DBA and SQL, respectively.
In Adaptive Server Enterprise, the case sensitivity of user IDs and passwords follows the case sensitivity of the server.
Ensuring compatible object namesEach database object must have a unique name within a certain name space. Outside this name space, duplicate names are allowed. There are some database objects that occupy different name spaces in Adaptive Server Enterprise and Adaptive Server Anywhere.
In Adaptive Server Anywhere, indexes and triggers are owned by the owner of the table on which they are created. Index and trigger names must be unique for a given owner. For example, while the tables t1 owned by user user1 and t2 owned by user user2 may have indexes of the same name, no two tables owned by a single user may have an index of the same name.
Adaptive Server Enterprise has a less restrictive name space for index names than Adaptive Server Anywhere. Index names must be unique on a given table, but any two tables may have an index of the same name. For compatible SQL, you should stay within the Adaptive Server Anywhere restriction of unique index names for a given table owner.
Adaptive Server Enterprise has a more restrictive name space on trigger names than Adaptive Server Anywhere. Trigger names must be unique in the database. For compatible SQL, you should stay within the Adaptive Server Enterprise restriction and make your trigger names unique in the database.
The special Transact-SQL timestamp column and data typeAdaptive Server Anywhere supports the Transact-SQL special timestamp column. The timestamp column is used together with the tsequal system function to check whether a row has been updated.
Two meanings of timestamp |
To create a Transact-SQL timestamp column, create a column that has the (Adaptive Server Anywhere) data type TIMESTAMP and has a default setting of timestamp. The column can have any name, although the name timestamp is commonly used.
For example, the following CREATE TABLE statement includes a Transact-SQL timestamp column:
CREATE TABLE table_name ( column_1 INTEGER , column_2 TIMESTAMP default timestamp )
The following ALTER TABLE statement adds a Transact-SQL timestamp column to the sales_order table:
ALTER TABLE sales_order ADD timestamp TIMESTAMP default timestamp
In Adaptive Server Enterprise a column with the name timestamp and no data type specified is automatically given a TIMESTAMP data type. In Adaptive Server Anywhere you must explicitly assign the data type yourself.
If you have the AUTOMATIC_TIMESTAMP database option set to ON, you do not need to set the default value: any new column created with TIMESTAMP data type and with no explicit default is given a default value of timestamp. The following statement sets AUTOMATIC_TIMESTAMP to ON:
SET OPTION PUBLIC.AUTOMATIC_TIMESTAMP='ON'
Adaptive Server Enterprise treats a timestamp column as a user-defined data type that is VARBINARY(8), allowing NULL, while Adaptive Server Anywhere treats a timestamp column as the TIMESTAMP data type, which consists of the date and time, with fractions of a second held to six decimal places.
When fetching from the table for later updates, the variable into which the timestamp value is fetched should correspond to the way the column is described.
If you add a special timestamp column to an existing table, all existing rows have a NULL value in the timestamp column. To enter a timestamp value (the current timestamp) for existing rows, update all rows in the table such that the data does not change. For example, the following statement updates all rows in the sales_order table, without changing the values in any of the rows:
UPDATE sales_order SET region = region
In Interactive SQL, you may need to set the TIMESTAMP_FORMAT option to see the differences in values for the rows. The following statement sets the TIMESTAMP_FORMAT option to display all six digits in the fractions of a second:
SET OPTION TIMESTAMP_FORMAT='YYYY-MM-DD HH:MM:ss.SSSSSS'
If all six digits are not shown, some timestamp column values may appear to be equal: they are not.
With the tsequal system function you can tell whether a timestamp column has been updated or not.
For example, an application may SELECT a timestamp column into a variable. When an UPDATE of one of the selected rows is submitted, it can use the tsequal function to check that the row has not been changed. The tsequal function compares the timestamp value in the table with the timestamp value obtained in the SELECT. If they are the same, the row has not been changed; if they differ, the row has been changed since the SELECT was carried out.
The following is a typical UPDATE statement using the tsequal function:
UPDATE publishers SET city = 'Springfield' WHERE pub_id = '0736' AND TSEQUAL(timestamp, '1995/10/25 11:08:34.173226')
The first argument to the tsequal function is the name of the special timestamp column; the second argument is the timestamp retrieved in the SELECT statement. In Embedded SQL, the second argument is likely to be a host variable containing a TIMESTAMP value from a recent FETCH on the column.
The special IDENTITY columnTo create an IDENTITY column, use the following CREATE TABLE syntax:
CREATE TABLE table-name ( ... column-name numeric(n,0) IDENTITY NOT NULL, ... )
where n is large enough to hold the value of the maximum number of rows that may be inserted into the table.
The IDENTITY column is used to store sequential numbers, such as invoice numbers or employee numbers, which are automatically generated. The value of the IDENTITY column uniquely identifies each row in a table.
In Adaptive Server Enterprise, each table in a database can have one IDENTITY column. The data type must be numeric with scale zero, and the IDENTITY column should not allow nulls.
In Adaptive Server Anywhere, the IDENTITY column is implemented as a column default setting. Values that are not part of the sequence may be explicitly inserted into the column with an INSERT statement. Adaptive Server Enterprise does not allow INSERTs into identity columns unless the identity_insert option is set to on. In Adaptive Server Anywhere, you need to set the NOT NULL property yourself and ensure that no more than one column is an IDENTITY column. Adaptive Server Anywhere allows any numeric data type to be used as an IDENTITY column.
In Adaptive Server Anywhere the identity column is identical to the AUTOINCREMENT default setting for a column.
Retrieving IDENTITY Column Values with @@identityThe first time you insert a row into the table, a value of 1 is assigned to an IDENTITY column. On each subsequent insert, the value of the column is incremented by one. The value most recently inserted into an identity column is available in the @@identity global variable.
The value of @@identity changes each time a statement attempts to insert a row into a table.
If the statement affects a table without an IDENTITY column, @@identity is set to 0.
If the statement inserts multiple rows, @@identity reflects the last value inserted into the IDENTITY column.
This change is permanent. @@identity does not revert to its previous value if the statement fails or if the transaction that contains it is rolled back Also, the value for @@identity within a stored procedure or trigger does not affect the value outside the stored procedure or trigger.