ODIMSV001 - DATE and DATETIME data types

INFORMIX provides two data types to store dates and time information:

• DATE = for year, month and day storage.
• DATETIME = for year to fraction(1-5) storage.

Microsoft SQL Server provides two data type to store dates :

• DATETIME = for year, month, day, hour, min, second, fraction(3) storage.
• SMALLDATETIME = for year, month, day, hour, minutes storage.

String representing date time information :

INFORMIX is able to convert quoted strings to DATE / DATETIME data if the string contents matches environment parameters (i.e. DBDATE, GL_DATETIME). As in INFORMIX, Microsoft SQL Server can convert quoted strings to DATETIME data. The CONVERT( ) SQL function allows you to convert strings to dates.

Date time arithmetic:

• INFORMIX supports date arithmetic on DATE and DATETIME values. The result of an arithmetic expression involving dates/times is a number of days when only DATEs are used and an INTERVAL value if a DATETIME is used in the expression.
• INFORMIX automatically converts an integer to a date when the integer is used to set a value of a date column. Microsoft SQL Server does not support this automatic conversion.
• Complex DATETIME expressions ( involving INTERVAL values for example ) are INFORMIX specific and have no equivalent in Microsoft SQL Server.
• Microsoft SQL Server does not allow direct arithmetic operations on datetimes; the date handling SQL functions must be used instead (DATEADD & DATEDIFF).
• The SQL Server equivalent for YEAR(), MONTH(), DAY(), WEEKDAY() is the DATEPART(<part>,<date>) function.
• SQL Server uses a different basis for the day of the week. In SQL Server, Sunday is day 7 and Monday is day 1 while INFORMIX defines Sunday as the day 0 (zero) and Monday as 1.

Solution :

SQL Server DATETIME type can be used for INFORMIX DATE type, because the database interface automatically sets the time part to midnight (00:00:00.0). But you must be very careful since manual modifications of the database might set the time part, for example :
    UPDATE table SET date_col = GETDATE()
After this kind of update, when columns have date values with a time part different from midnight, some SELECT statement might not return all the expected rows.

INFORMIX DATETIME with any precision from YEAR to FRACTION(3) can be stored in SQL Server DATETIME columns. The database interface makes the conversion automatically. Missing date or time parts default to 1900-01-01 00:00:00.0. For example, when using a DATETIME HOUR TO MINUTE with the value of "11:45", the SQL Server DATETIME value will be "1900-01-01 11:45:00.0".

Warning : The fraction part of an SQL Server DATETIME is an approximate value.  For example, when you insert a datetime value with a fraction of 111, the database actually stores 110. This may cause problems because INFORMIX DATETIMEs with a fraction part are exact values with a precision up to 5 digits.

Warning : Using integers as a number of days in an expression with dates is not supported by SQL Server. Check your code to detect where you are using integers with DATE columns.

Warning : It is strongly recommended to use BDL variables in dynamic SQL statements instead of quoted strings representing DATEs. For example :
   LET stmt = "SELECT ... FROM customer WHERE creat_date >'", adate,"'"
is not portable; use a question mark place holder instead and OPEN the cursor USING adate :
   LET stmt = "SELECT ... FROM customer WHERE creat_date > ?"

Warning : Review the program logic if you are using the INFORMIX WEEKDAY()  function because SQL Server uses a different basis for the days numbers ( Monday = 1 ).

Warning : SQL Statements using expressions with TODAY / CURRENT /  EXTEND must be reviewed and adapted to the native syntax.

ODIMSV003 - Reserved words

Microsoft Transac-SQL does not allow you to use reserved words as database object names ( tables, columns, constraint, indexes, triggers, stored procedures, ...).  An example of a common word which is part of SQL Server grammar is 'go'.

Solution :

Database objects having a name which is a Transac-SQL reserved word must be renamed.

All BDL application sources must be verified. To check if a given keyword is used in a source, you can use UNIX 'grep' or 'awk' tools. Most modifications can be automatically done with UNIX tools like 'sed' or 'awk'.

ODIMSV004 - ROWIDs

When creating a table, INFORMIX automatically adds a "ROWID" column of type integer (applies to non-fragmented tables only). The ROWID column is auto-filled with a unique number and can be used like a primary key to access a given row.

Microsoft SQL Server tables have no ROWIDs.

Solution :

If the BDL application uses ROWIDs, the program logic should be reviewed in order to use the real primary keys (usually, serials which can be supported).

All references to SQLCA.SQLERRD[6] must be removed because this variable will not hold the ROWID of the last INSERTed or UPDATEd row when using the Microsoft SQL Server interface.

ODIMSV005 - SERIAL data type

INFORMIX SERIAL data type and automatic number production :

• The table column must be of type SERIAL.
• To generate a new serial, no value or a zero value is given to the INSERT statement :
     INSERT INTO tab1 ( c ) VALUES ( 'aa' )
     INSERT INTO tab1 ( k, c ) VALUES ( 0, 'aa' )
• After INSERT, the new SERIAL value is provided in SQLCA.SQLERRD[2].

INFORMIX allows you to insert rows with a value different from zero for a serial column. Using an explicit value will automatically increment the internal serial counter, to avoid conflicts with future INSERTs that are using a zero value :
    CREATE TABLE tab ( k SERIAL );  --> internal counter = 0
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 1
    INSERT INTO tab VALUES ( 10 );  --> internal counter = 10
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 11
    DELETE FROM tab;                --> internal counter = 11
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 12

Microsoft SQL Server IDENTITY columns :

• When creating a table, the IDENTITY keyword must be specified after the column data type:
     CREATE TABLE tab1 ( k integer identity, c char(10) )
• You can specify a start value and an increment with "identity(start,incr)".
     CREATE TABLE tab1 ( k integer identity(100,2), ...
• A new number is automatically created when inserting a new row :
     INSERT INTO tab1 ( c ) VALUES ( 'aaa' )
• To get the last generated number, Microsoft SQL Server provides a global variable :
     SELECT @@IDENTITY
• To put a specific value into a IDENTITY column, the SET command must be used:
     SET IDENTITY_INSERT tab1 ON
     INSERT INTO tab1 ( k, c ) VALUES ( 100, 'aaa' )
     SET IDENTITY_INSERT tab1 OFF

INFORMIX SERIALs and MS SQL Server IDENTITY columns are quite similar; the main difference is that MS SQL Server does not allow you to use the zero value for the identity column when inserting a new row.

This problem cannot be resolved with triggers because Microsoft SQL Server does not support row-level triggers (INSERT Triggers are fired only once per INSERT statement).

Solution :

To emulation Informix serials, you can use IDENTITY columns (1) or insert triggers based on the SERIALREG table (2). The first solution is faster, but does not allow explicit serial value specification in insert statements; the second solution is slower but allows explicit serial value specification.

Warning: The second emulation based on triggers is provided to simplify the conversion to SQL Server. We strongly recommend you to use native IDENTITY columns instead.

With the following fglprofile entry, you define the technique to be used for SERIAL emulation :

   dbi.database.<dbname>.ifxemul.datatype.serial.emulation = {native|regtable}

The 'native' value defines the IDENTITY column technique and the 'regtable' defines the trigger technique.

This entry must be used with :

   dbi.database.<dbname>.ifxemul.datetype.serial = {true|false}

If this entry is set to false, the emulation method specification entry is ignored.

Warning : When no entry is specified, the default is SERIAL emulation enabled with 'native' method (IDENTITY-based).

1. Using IDENTITY columns

In database creation scripts, all SERIAL[(n)] data types must be converted by hand to INTEGER IDENTITY[(n,1)] data types.

Tables created from the BDL programs can use the SERIAL data type : When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the database interface automatically converts the "SERIAL[(n)]" data type to "INTEGER IDENTITY[(n,1)]".

In BDL, the new generated SERIAL value is available from the SQLCA.SQLERRD[2] variable. This is supported by the database interface which performs a "SELECT @@IDENTITY".

Warning : By default (see SET IDENTITY_INSERT), MS SQL Server does not allow you to specify the IDENTITY column in INSERT statements; you must convert all INSERT statements to remove that column from the list, for example:
   INSERT INTO tab ( col_serial, col_data, ... ) VALUES ( 0, <data|var>, ... )
Must be converted to :
   INSERT INTO tab ( col_data, ... ) VALUES ( <data|var>, ... )

Warning : SELECT * FROM table INTO TEMP with original table having an IDENTITY column is not supported: The database driver must convert the Informix SELECT INTO TEMP statement into a SELECT INTO #tab + INSERT (see temporary tables) because ODBC does not allow SQL parameters in DDL statements. As MS SQL Server does not allow you to insert a row by giving the identity column, the INSERT statement fails.

2. Using triggers with the SERIALREG table

First, you must prepare the database and create the SERIALREG table. You can generate the CREATE TABLE instruction with the gsrl_msv tool provided in the adaptation kit.

In database creation scripts, all SERIAL[(n)] data types must be converted to INTEGER data types and you must create one trigger for each table by using the gsrl_msv tool.

Tables created from the BDL programs can use the SERIAL data type.  When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the database interface automatically converts the "SERIAL[(n)]" data type to "INTEGER" and creates the insert triggers.

Warning : This serial emulation is only supported with SQL Server 2000 and higher, because it is implemented with INSTEAD OF triggers.

Warning : SQL Server does not allow you to create triggers on temporary tables. Therefore, you cannot create temp tables with a SERIAL column when using this solution.

Warning : SELECT ... INTO TEMP statements using a table created with a SERIAL column do not automatically create the SERIAL triggers in the temporary table. The type of the column in the new table is INTEGER.

Warning : SQL Server triggers are not automatically dropped when the corresponding table is dropped. Database administrators must be aware of this behavior when managing schemas.

Warning : INSERT statements using NULL for the SERIAL column will produce a new serial value, instead of using NULL :
   INSERT INTO tab ( col_serial, col_data ) VALUES ( NULL, 'data' )
This behavior is mandatory in order to support INSERT statements which do not use the serial column :
   INSERT INTO tab ( col_data ) VALUES ( 'data' )
Check if your application uses tables with a SERIAL column that can contain a NULL value.

Warning : The serial production is based on the SERIALREG table which registers the last generated number for each table. If you delete rows of this table, sequences will restart at 1 and you will get unexpected data.

ODIMSV006 - Outer joins

The syntax of OUTER joins is quite different in INFORMIX and Microsoft SQL Server :

In INFORMIX SQL, outer tables are defined in the FROM clause with the OUTER keyword :

Microsoft SQL Server supports the ANSI outer join syntax :

SELECT ... FROM cust LEFT OUTER JOIN order
                     ON cust.key = order.custno

SELECT ...
  FROM cust LEFT OUTER JOIN order
                 LEFT OUTER JOIN item
                 ON order.key = item.ordno
            ON cust.key = order.custno
 WHERE order.accepted = 1

Remark: The old way to define outers in SQL Server looks like the following :

SELECT ... FROM a, b WHERE a.key *= b.key

See the SQL Server reference manual for a complete description of the syntax.

Solution :

The Microsoft SQL Server interface can convert simple INFORMIX OUTER specifications to Microsoft SQL Server ANSI outer joins.

Prerequisites :

1. The outer join in the WHERE part must use the table name as prefix.
      Example : "WHERE tab1.col1 = tab2.col2 ".
2. Additional conditions on outer table columns cannot be detected and therefore are not supported :
      Example : "... FROM tab1, OUTER(tab2) WHERE tab1.col1 = tab2.col2 AND tab2.colx > 10".
3. Statements composed of 2 or more SELECT instructions using OUTERs are not supported.
     Example : "SELECT ... UNION SELECT" or "SELECT ... WHERE col IN (SELECT...)"

Remarks :

1. Table aliases are detected in OUTER expressions.
      OUTER example with table alias : "OUTER( tab1 alias1)".
2. In the outer join, <outer table>.<col> can be placed on both right or left sides of the equal sign.
      OUTER join example with table on the left : "WHERE outertab.col1 = maintab.col2 ".
3. Table names detection is not case-sensitive.
      Example : "SELECT ... FROM tab1, TAB2 WHERE tab1.col1 = tab2.col2".
4. Temporary tables are supported in OUTER specifications.

ODIMSV007a - Database concepts

As in INFORMIX, an SQL Server engine can manage multiple database entities. When creating a database object like a table, Microsoft SQL Server allows you to use the same object name in different databases.

ODIMSV008a - Data consistency and concurrency management

Data consistency involves readers which want to access data currently modified by writers and concurrency data access involves several writers accessing the same data for modification. Locking granularity defines the amount of data concerned when a lock is set (row, page, table, ...).

INFORMIX

INFORMIX uses a locking mechanism to manage data consistency and concurrency. When a process modifies data with UPDATE, INSERT or DELETE, an exclusive lock is set on the affected rows. The lock is held until the end of the transaction. Statements performed outside a transaction are treated as a transaction containing a single operation and therefore release the locks immediately after execution. SELECT statements can set shared locks according to the isolation level. In case of locking conflicts (for example, when two processes want to acquire an exclusive lock on the same row for modification or when a writer is trying to modify data protected by a shared lock), the behavior of a process can be changed by setting the lock wait mode.

Control :

• Isolation level : SET ISOLATION LEVEL TO ...
• Lock wait mode : SET LOCK MODE TO ...
• Locking granularity : CREATE TABLE ... LOCK MODE {PAGE|ROW}
• Explicit locking : SELECT ... FOR UPDATE

Defaults :

• The default isolation level is read committed.
• The default lock wait mode is "not wait".
• The default locking granularity is per page.

SQL Server

As in INFORMIX, SQL Server uses locks to manage data consistency and concurrency. The database manager sets exclusive locks on the modified rows and shared locks when data is read, according to the isolation level. The locks are held until the end of the transaction. When multiple processes want to access the same data, the latest processes must wait until the first finishes its transaction or the lock timeout occurred. The lock granularity is at the row or table level. For more details, see SQL Server's Documentation, "Accessing and Changing Data", "Locking".

Control :

• Lock wait mode : SET LOCK_TIMEOUT <milliseconds> (returns error 1222 on time out).
• Isolation level : SET TRANSACTION ISOLATION LEVEL ...
• Locking granularity : Automatic: Row, Page or Table level (See Dynamic Locking).
• Explicit locking : SELECT ... FROM UPDLOCK tabname ... FOR UPDATE

Defaults :

• The default isolation level is Read Committed ( readers cannot see uncommitted data; no shared lock is set when reading data ).

Solution :

For portability, it is recommended that you work with INFORMIX in the read committed isolation level, to make processes wait for each other (lock mode wait) and to create tables with the "lock mode row" option.

See INFORMIX and SQL Server documentation for more details about data consistency, concurrency and locking mechanisms.

When using SET LOCK MODE and SET ISOLATION LEVEL instructions, the database interface sets automatically the native database session options.

ODIMSV008b - SELECT FOR UPDATE

A lot of BDL programs use pessimistic locking in order to avoid several users editing the same rows at the same time.

  DECLARE cc CURSOR FOR
        SELECT ... FOR UPDATE
  OPEN cc
  FETCH cc <-- lock is acquired
  CLOSE cc <-- lock is released

• A transaction must be started before opening cursors declared for update.
• The row must be fetched in order to set the lock.
• The lock is released when the transaction ends (if the cursor is not declared "WITH HOLD") or when the cursor is closed.

Microsoft SQL Server allows individual and exclusive row locking by using the (UPDLOCK) hint after the table names in the FROM clause :

   SELECT ... FROM tab1 (UPDLOCK) WHERE ...

The FOR UPDATE clause is not mandatory; the (UPDLOCK) hint is important.

• Individual locks are acquired when fetching the rows.
• When the cursor (WITH HOLD) is opened outside a transaction, locks are released when the cursor is closed.
• When the cursor is opened inside a transaction, locks are released when the transaction ends.

SQL Server's locking granularity is at the row level, page level or table level (the level is automatically selected by the engine for optimization).

To control the behavior of the program when locking rows, INFORMIX provides a specific instruction to set the wait mode :

   SET LOCK MODE TO { WAIT | NOT WAIT | WAIT seconds }

The default mode is NOT WAIT. This as an INFORMIX specific SQL statement.

Solution :

SELECT FOR UPDATE statements are well supported as long as they are used inside a transaction. Avoid cursors declared WITH HOLD.

Warning : SQL Server locks the rows when you open the cursor. You will have to test SQLCA.SQLCODE after doing an OPEN.

Warning : The database interface is based on an emulation of an Informix engine using transaction logging. Therefore, opening a SELECT ... FOR UPDATE cursor declared outside a transaction will raise an SQL error -255 (not in transaction).

Warning : The SELECT FOR UPDATE statement cannot contain an ORDER BY clause if you want to perform positioned updates/deletes with WHERE CURRENT OF.

Warning : Cursors declared with SELECT ... FOR UPDATE using the "WITH HOLD" clause cannot be supported with SQL Server.

You must review the program logic if you use pessimistic locking because it is based on the NOT WAIT mode which is not supported by SQL Server.

ODIMSV009 - Transactions handling

INFORMIX and Microsoft SQL Server handle transactions in a similar manner.

INFORMIX native mode (non ANSI) :

• Transactions are started with "BEGIN WORK".
• Transactions are validated with "COMMIT WORK".
• Transactions are canceled with "ROLLBACK WORK".
• Statements executed outside of a transaction are automatically committed.
• DDL statements can be executed (and canceled) in transactions.

Microsoft SQL Server :

• Transactions are started with "BEGIN TRANSACTION [name]".
• Transactions are validated with "COMMIT TRANSACTION [name]".
• Transactions are canceled with "ROLLBACK TRANSACTION [name]".
• Transactions save points can be placed with "SAVE TRANSACTION [name]".
• Microsoft SQL Server supports named and nested transactions.
• Statements executed outside of a transaction are automatically committed.
  This behavior can be changed with "SET IMPLICIT_TRANSACTION ON".
• DDL statements are not supported in transactions blocks.

Transactions in stored procedures : avoid using transactions in stored procedure to allow the client applications to handle transactions, according to the transaction model.

Solution :

INFORMIX transaction handling commands are automatically converted to Microsoft SQL Server instructions to start, validate or cancel transactions.

Regarding the transaction control instructions, the BDL applications do not have to be modified in order to work with Microsoft SQL Server.

ODIMSV011 - CHARACTER data types

As in INFORMIX, Microsoft SQL Server provides the CHAR and VARCHAR data types to store character data.

INFORMIX CHAR type can store up to 32767 characters and the VARCHAR data type is limited to 255 characters.

Microsoft SQL Server CHAR and VARCHAR both have a limit of  8000 characters.

Microsoft SQL server provides the TEXT data type to store large character strings. Only the LIKE operator can be used for searches.  TEXT columns cannot be used in classic comparison expressions (as col = 'value').

Solution :

The database interface supports character string variables in SQL statements for input (BDL USING) and output (BDL INTO) up to  the 8000 characters limit defined by Microsoft SQL Server for CHAR and VARCHAR data types.

Warning : Check that your database schema does not use CHAR or VARCHAR types with a length exceeding the SQL Server limit.

Warning : TEXT values cannot be used as input or output parameters in SQL statements and therefore are not supported.

ODIMSV012 - Constraints

Constraint naming syntax :

Both INFORMIX and Microsoft SQL Server support primary key, unique, foreign key, default and check constraints. But the constraint naming syntax is different : SQL Server expects the "CONSTRAINT" keyword before the constraint specification and INFORMIX expects it after.

UNIQUE constraint example :

Warning : SQL Server does not produce an error when using the Informix syntax of constraint naming

The NULL / NOT NULL constraint :

Warning : Microsoft SQL Server creates columns as NOT NULL by default, when no NULL constraint is specified (colname datatype {NULL | NOT NULL}). A special option is provided to invert this behavior: ANSI_NULL_DFLT_ON. This option can be enabled with the SET command.

Solution :

Constraint naming syntax :

The database interface does not convert constraint naming expressions when creating tables from BDL programs. Review the database creation scripts to adapt the constraint naming clauses for Microsoft SQL Server.

The NULL / NOT NULL constraint :

Warning : Before using a the database, you must check the "ANSI NULL Default" option in the database properties if you want to have the same default NULL constraint as in INFORMIX databases.

ODIMSV013 - Triggers

INFORMIX and Microsoft SQL Server provide triggers with similar features, but the programming languages are totally different.

Warning : Microsoft SQL Server does not support "before" triggers.

Warning : Microsoft SQL Server does not support row-level triggers.

Solution :

INFORMIX triggers must be converted to Microsoft SQL Server triggers "by hand".

ODIMSV014 - Stored procedures

Both INFORMIX and Microsoft SQL Server support stored procedures, but the programming languages are totally different :

• INFORMIX stored procedures must be written in SPL.
• Microsoft SQL Server stored procedures must be written in Transac-SQL.

Solution :

INFORMIX stored procedures must be converted to Microsoft SQL Server "by hand".

ODIMSV016a - Defining database users

INFORMIX users are defined at the operating system level, they must be members of the 'informix' group, and the database administrator must grant CONNECT, RESOURCE or DBA privileges to those users.

Before a user can access an SQL Server database, the system administrator (SA) must add the user's login to the SQL Server Login list and add a user name for that database. The user name is a name that is assigned to a login ID for the purpose of allowing that user to access a specified database. Database users are members of a user group; the default group is 'public'.

Microsoft SQL Server offers two authentication modes : The SQL Server authentication mode, which requires a login name and a password, and the Windows NT authentication mode, which uses the security mechanisms within Windows NT when validating login connections. With this mode, user do not have to enter a login ID and password - their login information is taken directly from the network connection.

Warning: SQL Server 2000 supports only Windows NT authentication by default. If you want to use SQL Server authentication, you must change a parameter in the server properties.

Solution :

Both SQL Server and Windows NT authentication methods can be used to allow BDL program users to connect to Microsoft SQL Server and access a specific database.

See SQL Server documentation for more details on database logins and users.

ODIMSV016b - Setting privileges

INFORMIX and Microsoft SQL Server user privileges management are quite similar.

Microsoft SQL Server provides user groups to grant or revoke permissions to more than one user at the same time.

ODIMSV017 - Temporary tables

INFORMIX temporary tables are created through the CREATE TEMP TABLE DDL instruction or through a SELECT ... INTO TEMP statement. Temporary tables are automatically dropped when the SQL session ends, but they can also be dropped with the DROP TABLE command. There is no name conflict when several users create temporary tables with the same name.

Remark : BDL reports create a temporary table when the rows are not sorted externally (by the source SQL statement).

INFORMIX allows you to create indexes on temporary tables. No name conflict occurs when several users create an index on a temporary table by using the same index identifier.

Microsoft SQL Server provides local (SQL session wide) or global (database wide) temporary tables by using the '#' or '##' characters as table name prefix. No 'TEMP' keyword is required in CREATE TABLE, and the INTO clause can be used within a SELECT statement to create and fill a temporary table in one step :

    CREATE TABLE #temp1 ( kcol INTEGER, .... )
    SELECT * INTO #temp2 FROM customers WHERE ...

Solution :

INFORMIX temporary tables instructions are converted to generate native SQL Server temporary tables .

Warning : Microsoft SQL Server does not support scroll cursors based on a temporary table.

ODIMSV018 - Substrings in SQL

INFORMIX SQL statements can use subscripts on columns defined with the character data type :
    SELECT ... FROM tab1 WHERE col1[2,3] = 'RO'
    SELECT ... FROM tab1 WHERE col1[10]  = 'R'   -- Same as col1[10,10]
    UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
    SELECT ... FROM tab1 ORDER BY col1[1,3]

.. while Microsoft SQL Server provides the SUBSTR( ) function, to extract a substring from a string expression :
    SELECT .... FROM tab1 WHERE SUBSTRING(col1,2,2) = 'RO'
    SELECT SUBSTRING('Some text',6,3) FROM DUAL       -- Gives 'tex'

Solution :

You must replace all Informix col[x,y] expressions by SUBSTRING(col,x,y-x+1).

Warning : In UPDATE instructions, setting column values through subscripts will produce an error with Microsoft SQL Server :
    UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
is converted to :
    UPDATE tab1 SET SUBSTRING(col1,2,3-2+1) = 'RO' WHERE ...

Warning : Column subscripts in ORDER BY expressions are also converted and produce an error with Microsoft SQL Server :
    SELECT ... FROM tab1 ORDER BY col1[1,3]
is converted to :
    SELECT ... FROM tab1 ORDER BY SUBSTRING(col1,1,3-1+1)

ODIMSV019 - Name resolution of SQL objects

INFORMIX uses the following form to identify an SQL object :
  [database[@dbservername]:][{owner|"owner"}.](identifier|"identifier")

With Microsoft SQL Server, an object name takes the following form :
  [[database.]owner.]identifier

Object names are limited to 30 chars in SQL Server and cannot start with one of the following characters : @ (local variable) # (temp object).

To support double quotes as string delimiters in SQL Server, switch OFF the database option "Use quoted identifiers" in the database properties panel. But quoted table and column names are not supported when this option is OFF .

Solution :

Switch OFF the database option "Use quoted identifiers" to support double quoted strings.

Check for single or double quoted table or column names in your source and remove them.

ODIMSV020 - String delimiters

The ANSI string delimiter character is the single quote ( 'string' ). Double quotes are used to delimit database object names ("object-name").

Example : WHERE "tabname"."colname" = 'a string value'

As in INFORMIX, Microsoft SQL Server allows you to use double quotes as string delimiters, if the QUOTED_IDENTIFIER session option is OFF.

    SET QUOTED_IDENTIFIER OFF

Remark : This problem concerns only double quotes within SQL statements. Double quotes used in BDL string expressions are not subject of SQL compatibility problems.

Solution :

The Microsoft SQL Server database interface sets the QUOTED_IDENTIFIER session option to OFF after a database connection has been established and automatically converts any escaped string delimiters to two double quotes or two single quotes.

ODIMSV021 - NUMERIC data types

Microsoft SQL Server offers numeric data types which are quite similar to INFORMIX numeric data types. The table below shows general conversion rules for numeric data types :

Solution :

In BDL programs :

When creating tables from BDL programs, the database interface automatically converts INFORMIX data types to corresponding Microsoft SQL Server data types.

Database creation scripts :

• SMALLINT and INTEGER columns do not have to use another data type in SQL Server.
• For DECIMALs, check the precision limit. Always use a precision and a scale.
• Convert MONEY columns to DECIMAL(p,s) columns. Always use a precision and a scale.
• Convert SMALLFLOAT columns to REAL columns.
• Since FLOAT precision is ignored in INFORMIX, convert this data type to FLOAT(15).

ODIMSV022 - Getting one row with SELECT

With INFORMIX, you must use the system table with a condition on the table id :

   SELECT user FROM systables WHERE tabid=1

With SQL Server, you can omit the FROM clause to generate one row only:

   SELECT user

Solution :

Check the BDL sources for "FROM systables WHERE tabid=1" and use dynamic SQL to resolve this problem.

ODIMSV024 - MATCHES and LIKE in SQL conditions

INFORMIX supports MATCHES and LIKE in SQL statements, while Microsoft SQL Server supports the LIKE statement only.

The MATCHES operator of INFORMIX uses the star, question mark and square braces wildcard characters.
The LIKE operator of SQL Server offers the percent, underscore and square braces wildcard characters.

The following substitutions must be made to convert a MATCHES condition to a LIKE condition :

• MATCHES keyword must be replaced by LIKE.
• All '*' characters must be replaced by '%'.
• All '?' characters must be replaced by '_'.

Solution :

Warning : SQL statements using MATCHES expressions must be reviewed in order to use LIKE expressions.

ODIMSV025 - INFORMIX specific SQL statements in BDL

The BDL compiler supports several INFORMIX specific SQL statements that have no meaning when using Microsoft SQL Server.

Examples :

• CREATE DATABASE dbname IN dbspace WITH BUFFERED LOG
• START DATABASE (SE only)
• ROLLFORWARD DATABASE
• CREATE TABLE ... IN dbspace WITH LOCK MODE ROW

Solution :

Review your BDL source and remove all static SQL statements which are INFORMIX specific.

ODIMSV028 - INSERT cursors

INFORMIX supports insert cursors. An "insert cursor" is a special BDL cursor declared with an INSERT statement instead of a SELECT statement. When this kind of cursor is open, you can use the PUT instruction to add rows and the FLUSH instruction to insert the records into the database.

For INFORMIX database with transactions, OPEN, PUT and FLUSH instructions must be executed within a transaction.

Microsoft SQL Server does not support insert cursors.

Solution :

Insert cursors are emulated by the Microsoft SQL Server database interface.

ODIMSV030 - Very large data types

Both INFORMIX and Microsoft SQL Server provide special data types to store very large texts or images.

Microsoft SQL Server recommends the following conversion rules :

Solution :

Very large character data types are not supported yet by the Microsoft SQL Server database interface.

ODIMSV031 - Cursors WITH HOLD

INFORMIX automatically closes opened cursors when a transaction ends unless the WITH HOLD option is used in the DECLARE instruction.

Microsoft SQL Server does not close cursors when a transaction ends.

Solution :

BDL cursors that are not declared "WITH HOLD" are automatically closed by the database interface when a COMMIT WORK or ROLLBACK WORK is performed by the BDL program.

ODIMSV033 - Querying system catalog tables

As in INFORMIX, Microsoft SQL Server provides system catalog tables (sysobjects,syscolumns,etc) in each database, but the table names and their structure are quite different.

Solution :

Warning : No automatic conversion of INFORMIX system tables is provided by the database interface.

ODIMSV034 - Syntax of UPDATE statements

INFORMIX allows a specific syntax for UPDATE statements :

    UPDATE table SET ( <col-list> ) = ( <val-list> )

or

    UPDATE table SET table.* = myrecord.*
    UPDATE table SET * = myrecord.*

Solution :

Static UPDATE statements using the above syntax are converted by the compiler to the standard form :

    UPDATE table SET column=value [,...]

ODIMSV035 - The LENGTH() function

INFORMIX provides the LENGTH() function:

Microsoft SQL Server has a equivalent function called LEN().

Do not confuse LEN() with DATALEN(), which returns the data size used for storage.

Both Informix and SQL Server ignore trailing blanks when computing the length of a string.

Solution :

You must adapt the SQL statements using LENGTH() and use the LEN() function.

Warning: If create a user function in SQL Server as follows:

You must qualify the function with the owner name:

ODIMSV036 - INTERVAL data type

INFORMIX's INTERVAL data type stores a value that represents a span of time. INTERVAL types are divided into two classes : year-month intervals and day-time intervals.

SQL Server does not provide a data type corresponding to the INFORMIX INTERVAL data type.

Solution :

Warning: The INTERVAL data type is not well supported because the database server has no equivalent native data type. However, you can store into and retrieve from CHAR columns BDL INTERVAL values.

ODIMSV039 - Data storage concepts

An attempt should be made to preserve as much of the storage information as possible when converting from INFORMIX to Microsoft SQL Server. Most important storage decisions made for INFORMIX database objects (like initial sizes and physical placement) can be reused in an SQL Server database.

Storage concepts are quite similar in INFORMIX and in Microsoft SQL Server, but the names are different.

The following table compares INFORMIX storage concepts to Microsoft SQL Server storage concepts :

ODIMSV040 - National characters data types

INFORMIX offers the NCHAR and NVARCHAR data types to store strings in a localized character set.

Unicode in SQL Server :

Microsoft SQL Server translates the bit patterns in char, varchar, and text columns to characters using the definitions in the code page installed with SQL Server. Client computers use the code page installed with the operating system to interpret character bit patterns. There are many different code pages. Some characters appear on some code pages, but not on others. Some characters are defined with one bit pattern on some code pages, and with a different bit pattern on other code pages. When you build international systems that must handle different languages, it becomes difficult to pick code pages for all the computers that meet the language requirements of multiple countries. It is also difficult to ensure that every computer performs the correct translations when interfacing with a system using a different code page.

The Unicode specification addresses this problem by using 2 bytes to encode each character. There are enough different patterns (65,536) in 2 bytes for a single specification covering the most common business languages. Because all Unicode systems consistently use the same bit patterns to represent all characters, there is no problem with characters being converted incorrectly when moving from one system to another.

In Microsoft SQL Server, these data types support Unicode data:

• nchar
• nvarchar
• ntext

Note The n prefix for these data types comes from the SQL-92 standard for National (Unicode) data types.

Use of nchar, nvarchar, and ntext is the same as char, varchar, and text, respectively, except:

• Unicode supports a wider range of characters.
• More space is needed to store Unicode characters.
• The maximum size of nchar and nvarchar columns is 4,000 characters, not 8,000 characters like char and varchar.
• Unicode constants are specified with a leading N: N’A Unicode string’.

Solution :

Warning : National character data types are not supported yet.

ODIMSV041 - Executing SQL statements

The database driver for Microsoft SQL Server is based on ODBC. The ODBC driver implementation provided with SQL Server uses system stored procedures to prepare and execute SQL statements (You can see this with the Profiler).

Some Transac-SQL statements like SET DATEFORMAT have a local execution context effect (for example, when executed in a stored procedure, it is reset to the previous values when procedure execution is finished).

To support such statements in BDL programs, the database driver uses the SQLExecDirect() ODBC API function when the SQL statement is not a SELECT, INSERT, UPDATE or DELETE. This way the SET statement is executed 'directly', without using the system stored procedures. The result is that the SET statement has the expected effect (i.e. a permanent effect).

However, if the SQL statement uses parameters, the ODBC driver forces the use of system stored procedures to execute the statement.

See the MSDN for more details about system stored procedures used by Microsoft APIs.

ODIMSV046 - The LOAD and UNLOAD instructions

INFORMIX provides two SQL instructions to export / import data from / into a database table: The UNLOAD instruction copies rows from a database table into an text file and the LOAD instruction inserts rows from an text file into a database table.

Warning : Microsoft SQL Server has LOAD and UNLOAD instructions, but those commands are related to database backup and recovery. Do not confuse with INFORMIX commands.

Solution :

LOAD and UNLOAD instructions are supported.

Warning : The LOAD instruction does not work with tables using emulated SERIAL columns because the generated INSERT statement holds the "SERIAL" column which is actually a IDENTITY column in SQL Server. See the limitations of INSERT statements when using SERIALs.

Warning : In Microsoft SQL Server, INFORMIX DATE data is stored in DATETIME columns, but DATETIME columns are similar to INFORMIX DATETIME YEAR TO FRACTION(3) columns. Therefore, when using LOAD and UNLOAD, those columns are converted to text data with the format "YYYY-MM-DD hh:mm:ss.fff".

Warning : In Microsoft SQL Server, INFORMIX DATETIME data is stored in DATETIME columns, but DATETIME columns are similar to INFORMIX DATETIME YEAR TO FRACTION(3) columns. Therefore, when using LOAD and UNLOAD, those columns are converted to text data with the format "YYYY-MM-DD hh:mm:ss.fff".

Warning : When using an INFORMIX database, simple dates are unloaded with the DBDATE format (ex: "23/12/1998"). Therefore, unloading from an INFORMIX database for loading into a Microsoft SQL Server database is not supported.

ODIMSV047 - Case sensitivity

In INFORMIX, database object names like table and column names are not case sensitive :

CREATE TABLE Customer ( Custno INTEGER, ... )
SELECT CustNo FROM cuSTomer ...

In SQL Server, database object names and character data are case-unsenstive by default :

CREATE TABLE Customer ( Custno INTEGER, CustName CHAR(20) )
INSERT INTO CUSTOMER VALUES ( 1, 'TECHNOSOFT' )
SELECT CustNo FROM cuSTomer WHERE custname = 'techNOSoft'

The installation program of SQL Server allows you to customize the sort order. The sort order specifies the rules used by SQL Server to collate, compare, and present character data. It also specifies whether SQL Server is case-sensitive.

Solution :

Select the case-sensitive sort order when installing SQL Server.

ODIMSV051 - Setup database statistics

INFORMIX provides a special instruction to compute database statistics in order to help the optimizer find the right query execution plan :

UPDATE STATISTICS ...

Microsoft SQL Server offers a similar instruction, but it uses different clauses :

UPDATE STATISTICS ...

See SQL Server documentation for more details.

Solution :

Centralize the optimization instruction in a function. See the ODILIB.BDL source as example.

ODIMSV052 - String concatenation operator

INFORMIX concatenation operator is the double pipe ( || ) :

     SELECT firstname || ' ' || lastname FROM employee

Microsoft SQL Server concatenation operator is the plus sign :

     SELECT firstname + ' ' + lastname FROM employee

Solution :

The database interface detects double-pipe operators in SQL statements and converts them to a plus sign automatically.

ODIMSV053 - The ALTER TABLE instruction

INFORMIX and MS SQL Server use different implementations of the ALTER TABLE instruction. For example, INFORMIX allows you to use multiple ADD clauses separated by comma. This is not supported by SQL Server :

INFORMIX :
     ALTER TABLE customer ADD(col1 INTEGER), ADD(col2 CHAR(20))

SQL Server :
     ALTER TABLE customer ADD col1 INTEGER, col2 CHAR(20)

Solution :

Warning : No automatic conversion is done by the database interface. There is even no real standard for this instruction ( that is, no common syntax for all database servers). Read the SQL documentation and review the SQL scripts or the BDL programs in order to use the database server specific syntax for ALTER TABLE.

ODIMSV100 - Data type conversion table