Back to Contents

ODI Adaptation Guide For SQL SERVER 2005, 2008, 2012

Installation

Install SQL SERVER and create a database
Prepare the runtime environment

Database concepts

Database concepts
Data storage concepts
Data consistency and concurrency management
Transactions handling
Defining database users
Setting privileges

Data dictionary

BOOLEAN data type
CHARACTER data types
NUMERIC data types
DATE and DATETIME data types
INTERVAL data type
SERIAL data types
SQL Server UNIQUEIDENTIFIER data type
SQL Server ROWVERSION data type
ROWIDs
Case sensitivity
Very large data types
The ALTER TABLE instruction
Constraints
Triggers
Stored procedures
Name resolution of SQL objects
Setup database statistics
Data type conversion table

Data manipulation

Reserved words
Outer joins
Transactions handling
Temporary tables
Substrings in SQL
Name resolution of SQL objects
String delimiters
Getting one row with SELECT
MATCHES and LIKE conditions
Querying system catalog tables
Syntax of UPDATE statements
The LENGTH() function

BDL programming

Executing SQL statements
SERIAL data type
INFORMIX specific SQL statements in BDL
INSERT cursors
Cursors WITH HOLD
SELECT FOR UPDATE
The LOAD and UNLOAD instructions
SQL Interruption
Scrollable Cursors

Runtime configuration

Install SQL SERVER and create a database - database configuration/design tasks

If you are tasked with installing and configuring the database, here is a list of steps to be taken:

  1. Install the Microsoft SQL SERVER on your computer.

    Warning: Make sure that you select the correct collation when installing SQL Server: The default collation will apply to the tempdb database and will also be used for temporary tables, instead of inheriting the collation of the current database. If the default server collation does not match the collation of the current database, you will experience character set conflicts between permanent tables and temporary tables (SQL Server error message 468).

  2. Create a SQL SERVER database entity with the SQL SERVER Management Studio.

    In the database properties:

  3. Create and declare a database user dedicated to your application: the application administrator.

  4. If you plan to use SERIAL emulation based on triggers using a registration table, create the SERIALREG table and create the serial triggers for all tables using a SERIAL. See issue Serial Data Types for more details.

  5. Create the application tables. Do not forget to convert INFORMIX data types to SQL SERVER data types. See  issue Data Type Conversion Table for more details. In order to make application tables visible to all users, make sure that the tables are created with the 'dbo' owner.

Prepare the runtime environment - connecting to the database

  1. Genero BDL provides several database drivers based on different ODBC clients. The list below describes each of them:

  2. Check that the Genero distribution package has installed the SQL SERVER database driver you need (i.e. a "dbmmsv*", "dbmsnc*", "dbmftm*" or "dbmesm*" driver must exist in FGLDIR/dbdrivers.

  3. An ODBC data source must be configured to allow the BDL program to establish connections to SQL SERVER. Make sure you select the correct ODBC driver (MSV = "SQL SERVER", SNC = "SQL Native Client", FTM = "FreeTDS", ESM = "EasySoft").
    Important: When using the FTM (FreeTDS) or ESM (EasySoft) database driver, you have to define the ODBCINI and ODBCINST environment variable to point to the odbc.ini and odbcinst.ini files.

  4. Install and configure the database client software:

  5. On Windows platforms, BDL programs are executed in a CONSOLE environment, not a GUI environment. CONSOLE and GUI environments may use different code pages on your system. Start the SQL SERVER Configuration Manager to setup your client environment and make sure no wrong character conversion occurs. See Microsoft SQL SERVER documentation for more details.

  6. Set up the fglprofile entries for database connections.

    Important:  Make sure that you are using the ODI driver corresponding to the database client and server version. Because Informix features emulation are dependant from the database server version, it is mandatory to use the same version of the database client and ODI driver as the server version.

    You may want to set the logintime parameter to change the default login timeout period, increase the prefetch.rows parameter to get better performances with result sets, etc.

    With the SNC driver you might consider setting the snc.widechar parameter to false if your database columns are defined with the CHAR/VARCHAR/TEXT types (by default the driver is prepared to work with the "UNICODE" types NCHAR/NVARCHAR/NTEXT). See CHARACTER data types for more details.

DATE and DATETIME data types

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

Microsoft SQL SERVER provides two data type to store dates:

Starting with Microsoft SQL SERVER 2008, following new date data types are available:

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 Microsoft SQL SERVER

select day(0), month(0), year(0) FROM systables WHERE tabid=1;
------ ------ ------
    31     12   1899
1 Row(s) affected

select day(0), month(0), year(0)
----------- ----------- -----------
          1           1        1900
(1 row(s) affected)

Solution:

The SQL SERVER database drivers will automatically map INFORMIX date/time types to native SQL SERVER type, according the server version. Conversions are described in this table:

INFORMIX date/time type                     Microsoft SQL SERVER date/time type
 

Before SQL SERVER 2008

 Since SQL SERVER 2008

DATE

DATETIME

 DATE

DATETIME HOUR TO SECOND

DATETIME (filled with 1900-01-01)

 TIME(0)

DATETIME HOUR TO FRACTION(n)

DATETIME (filled with 1900-01-01)

 TIME(n)

DATETIME YEAR TO SECOND

DATETIME

 DATETIME2(0)

Any other sort of DATETIME type

DATETIME (filled with 1900-01-01)

 DATETIME2(n)

With SQL SERVER 2005 and lower, INFORMIX DATETIME with any precision from YEAR to FRACTION(3) is stored in SQL SERVER DATETIME columns.

For heterogeneous DATETIME types like DATETIME HOUR TO MINUTE, the database interface fills missing date or time parts 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".

Important Notes:

Reserved words

Microsoft Transact-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' (see the 'Reserved keywords' section in the SQL SERVER Documentation).

Solution:

Database objects having a name which is a Transact-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'.

You can use SET QUOTED_IDENTIFIER ON with double-quotes to enforce the use of keywords in the database objects naming, but it is not recommended.

SQL Server UNIQUEIDENTIFIER data type

SQL Server supports a special type named UNIQUEIDENTIFIER, which can be used to store "Globally Unique Identifiers" (GUIDs). UNIQUEIDENTIFIER values can be generated with the NEWID() function. When creating a table, you typically define a UNIQUEIDENTIFIER column with a DEFAULT clause where the value is produced from a NEWID() call:

    CREATE TABLE mytab ( k INT, id UNIQUEIDENTIFIER DEFAULT NEWID(), c VARCHAR(10) )

The UNIQUEIDENTIFIER type is based on the BINARY(16) SQL Server type. The Genero language does not have an equivalent type for BINARY(16). However, BINARY values can be represented as hexadecimal strings in CHAR or VARCHAR variables.

A UNIQUEIDENTIFIER value is usually represented as a GUID identifier, with the following hexadecimal format:

    XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX         (where X can be 0-9, A-F )

You typically fetch UNIQUEIDENTIFIER data into a CHAR(36) Genero variable. The resulting value will be expressed in hexadecimal string using the above format. You can then reuse that value in an SQL statement, but you have to convert the CHAR(36) hexadecimal string value back to a UNIQUEIDENTIFIER value with the CONVERT() SQL Server function, as shown in the following example:

    DEFINE pi CHAR(36)
    CREATE TABLE mytab ( k INT, i UNIQUEIDENTIFIER DEFAULT NEWID(), c VARCHAR(10) )
    INSERT INTO mytab ( k, c ) VALUES ( 1, 'aaa' )
    SELECT i INTO pi FROM mytab WHERE k = 1
    UPDATE mytab SET c = 'xxx' WHERE i = CONVERT(UNIQUEIDENTIFIER, pi)

When extracting a database schema, UNIQUEIDENTIFIER columns can be clearly distinguished from BINARY(N) columns. The fgldbsch tool will produce a CHAR(36) type code in the .sch file for UNIQUEIDENTIFIER columns.

Note that you can also exclude the UNIQUEIDENTIFIER columns from the table definition in the schema file, by using the x character at the appropriate position of the string passed with the -cv data type conversion option of fgldbsch.

SQL Server ROWVERSION data type

SQL Server provides a special type named ROWVERSION, to stamp row modifications. The ROWVERSION data type replaces the old TIMESTAMP column definition. When you define a column with the ROWVERSION, SQL Server will automatically increment the version column when the row is modified.  ROWVERSION is just an incrementing number, it does not preserve date or time information. It be used to control concurrent access to the same rows.

The ROWVERSION type is based on the BINARY(8) SQL Server type. The Genero language does not have an equivalent type for BINARY(8). Therefore, you must fetch ROWVERSION data into a CHAR(16) variable. The resulting value will be expressed in hexadecimal. You can then reuse that value in an UPDATE statement to check that the row was not modified by another process, but you have to convert the CHAR(16) hexadecimal value back to a BINARY(8) value with the CONVERT() SQL Server function, as shown in the following example:

    DEFINE pv CHAR(16)
    CREATE TABLE mytab ( k INT, v ROWVERSION, c VARCHAR(10) )
    INSERT INTO mytab VALUES ( 1, NULL, 'aaa' )
    SELECT v INTO pv FROM mytab WHERE k = 1
    UPDATE mytab SET c = 'xxx' WHERE k = 1 AND v = CONVERT(BINARY(8), pv, 2)

Note that with SQL Server 2005, the CONVERT() function does not properly transform the hexadecimal string to a binary value. Therefore, you should only use ROWVERSION as SQL parameter starting with SQL Server 2008. ROWVERSION values can however be fetched with SQL Server versions prior to 2008, for example if you have to define record variables based on the table schema, including the ROWVERSION column.

Since ROWVERSION is a synonym for BINARY(8), ROWVERSION columns cannot be clearly identified in ODBC. Therefore, the following conversion rule applies when fetching data from the server:

When extracting a database schema, ROWVERSION columns are identified as TIMESTAMP columns and can be clearly distinguished from BINARY(N) columns. The fgldbsch tool will produce a CHAR(16) type code in the .sch file for ROWVERSION or TIMESTAMP columns.

ROWIDs

When creating a table, INFORMIX automatically adds a "ROWID" integer column (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).

However, if your existing INFORMIX application depends on using ROWID values, you can use the IDENTITY property of the DECIMAL, INT, NUMERIC, SMALLINT, BIGINT, or TINYINT data types, to simulate this functionality.

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.

SERIAL data types

INFORMIX supports the SERIAL, SERIAL8 and BIGSERIAL data types to produce automatic integer sequences. SERIAL is based on INTEGER (32 bit), while SERIAL8 and BIGSERIAL can store 64 bit integers:

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:

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.

Important: The second emulation based on triggers is provided to simplify the conversion to SQL SERVER. We strongly recommend that you 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.datatype.serial = {true|false}

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

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, while BIGSERIAL[(n)] data types must be converted by hand to BIGINT 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 SCOPE_IDENTITY()". However, SQLCA.SQLERRD[2] is defined as an INTEGER, it cannot hold values from BIGINT identity columns. If you are using BIGINT IDENTITY columns, you must retrieve the last generated serial with the SCOPE_IDENTITY() SQL function.

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 the identity column from the list.
For example, the following statement:
   INSERT INTO tab (col1,col2) VALUES (0, p_value)
must be converted to:
   INSERT INTO tab (col2) VALUES (p_value)
Static SQL INSERT using records defined from the schema file must also be reviewed:
   DEFINE rec LIKE tab.*
   INSERT INTO tab VALUES ( rec.* )   -- will use the serial column
must be converted to :
   INSERT INTO tab VALUES rec.* -- without braces, serial column is removed

Since 2.10.06, SELECT * FROM table INTO TEMP with original table having an IDENTITY column is supported: The database driver converts the INFORMIX SELECT INTO TEMP to the following sequence of statements:

  1. SELECT <selection items>  INTO #table FROM ... WHERE 1=2
  2. SET IDENTITY_ INSERT #table ON
  3. INSERT INTO #table ( column-list ) SELECT <original select clauses>
  4. SET IDENTITY_ INSERT #table OFF

See also temporary tables.

2. Using triggers with the SERIALREG table

First, you must prepare the database and create the SERIALREG table as follows:

CREATE TABLE serialreg (
     tablename VARCHAR(50) NOT NULL,
     lastserial BIGINT NOT NULL,
     PRIMARY KEY ( tablename )
)

Note that the SERIALREG table and columns have to be created with lower case names, since the SQL SERVER database is created with case sensitive names, because triggers are using this table in lower case.

In database creation scripts, all SERIAL[(n)] data types must be converted to INTEGER data types, BIGSERIAL must be converted to BIGINT and you must create one trigger for each table. To know how to write those triggers,  you can create a small Genero program that creates a table with a SERIAL column. Set the FGLSQLDEBUG environment variable and run the program. The debug output will show you the native trigger creation command.

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.

Important Notes;

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.

Outer joins

The original OUTER join syntax of INFORMIX is different from Microsoft SQL SERVER outer join syntax:

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

SELECT ... FROM cust, OUTER(order)
 WHERE cust.key = order.custno
SELECT ... FROM cust, OUTER(order,OUTER(item))
 WHERE cust.key = order.custno
   AND order.key = item.ordno
   AND order.accepted = 1

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
            ON cust.key = order.custno
            LEFT OUTER JOIN item
            ON order.key = item.ordno
 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:

For better SQL portability, you should use the ANSI outer join syntax instead of the old Informix OUTER syntax.

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.

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.

 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:

Defaults:

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 or update 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 locking strategy of SQL SERVER is row locking with possible promotion to page or table locking. SQL SERVER dynamically determines the appropriate level at which to place locks for each Transact-SQL statement.

Starting with SQL Server 2005, you can enhance concurrency by turning on snapshot isolation level, to make SQL Server use a copy of the row when it is changed by a transaction. To turn this feature on, you must set the database property ALLOW_SNAPSHOT_ISOLATION ON. Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level (otherwise, snapshot isolation must be specified by every SQL Session).

Control:

Defaults:

Solution:

The SET ISOLATION TO ... in programs is converted to SET TRANSACTION ISOLATION LEVEL ... for SQL Server. The next table shows the isolation level mappings done by the database driver:

SET ISOLATION instruction in program Native SQL command
SET ISOLATION TO DIRTY READ SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ISOLATION TO COMMITTED READ
  [READ COMMITTED] [RETAIN UPDATE LOCKS]		 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET ISOLATION TO CURSOR STABILITY SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET ISOLATION TO REPEATABLE READ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

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.

When using SET LOCK MODE ... in the programs, it will be converted to a SET LOCK_TIMEOUT instruction for SQL SERVER:

SET LOC MODE instruction in program Native SQL command
SET LOCK MODE TO WAIT SET LOCK_TIMEOUT -1   (wait forever)
SET LOCK MODE TO WAIT seconds SET LOCK_TIMEOUT seconds * 1000   (wait N milliseconds)
SET LOCK MODE TO NOT WAIT SET LOCK_TIMEOUT 0   (do not wait)

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

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

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 WITH (UPDLOCK) WHERE ...

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

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:

The SQL SERVER database driver for MS SQL SERVER uses the SCROLL LOCKS concurrency options for cursors (SQL_ATTR_CONCURRENCY = SQL_CONCUR_LOCK).

This option implements pessimistic concurrency control, in which the application attempts to lock the underlying database rows at the time they are read into the cursor result set.
When using server cursors, an update lock is placed on the row when it is read into the cursor.
If the cursor is opened within a transaction, the transaction update lock is held until the transaction is either committed or rolled back; the cursor lock is dropped when the next row is fetched.
If the cursor has been opened outside a transaction, the lock is dropped when the next row is fetched.
Therefore, a cursor should be opened in a transaction whenever the user wants full pessimistic concurrency control.
An update lock prevents any other task from acquiring an update or exclusive lock, which prevents any other task from updating the row.
An update lock, however, does not block a shared lock, so it does not prevent other tasks from reading the row unless the second task is also requesting a read with an update lock.

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

Note: 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).

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

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.

Transactions handling

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

INFORMIX native mode (non ANSI):

Microsoft SQL SERVER supports named and nested transactions:

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.

Important:  If you want to use savepoints, do not use the UNIQUE keyword in the savepoint declaration, always specify the savepoint name in ROLLBACK TO SAVEPOINT, and do not drop savepoints with RELEASE SAVEPOINT.

BOOLEAN data type

INFORMIX supports the BOOLEAN data type, which can store 't' or 'f' values. Genero BDL implements the BOOLEAN data type in a different way: As in other programming languages, Genero BOOLEAN stores integer values 1 or 0 (for TRUE or FALSE). The type was designed this way to assign the result of a Boolean expression to a BOOLEAN variable.

SQL SERVER provides the BIT data type to store Boolean values.

Solution:

The SQL SERVER database interfaces converts BOOLEAN type to BIT columns and stores 1 or 0 values in the column.

CHARACTER data types

INFORMIX supports following character data types:

In INFORMIX, both CHAR/VARCHAR and NCHAR/NVARCHAR data types can be used to store single-byte or multi-byte encoded character strings. The only difference between CHAR/VARCHAR and NCHAR/NVARCHAR is for sorting: N[VAR]CHAR types use the collation order, while [VAR]CHAR types use the byte order. The character set used to store strings in CHAR/VARCHAR/NCHAR/NVARCHAR columns is defined by the DB_LOCALE environment variable. The character set used by applications is defined by the CLIENT_LOCALE environment variable. Note that INFORMIX uses Byte Length Semantics (the size N that you specify in [VAR]CHAR(N) is expressed in bytes, not characters as in some other databases)

SQL Server provides the following data types to store character data:

Note that to store large text data (LOBs), Microsoft SQL Server version 2005 introduced the VARCHAR(MAX) type as a replacement for the old TEXT type.

The use of NCHAR, NVARCHAR character types is the same as CHAR, VARCHAR, TEXT respectively, except:

Note that SQL Server uses Byte Length Semantics to define the size of CHAR/VARCHAR columns, while NCHAR and NVARCHAR sizes are expressed in character units.

SQL Server defines the character encoding for CHAR and VARCHAR columns with the database collation. The database collation can be specified when creating a new database. Character strings are always stored in the UCS-2 encoding for NCHAR/NVARCHAR columns.

Automatic charset conversion is supported by SQL Server between the client application and the server. The client charset is defined by the Windows operating system, in the language settings for non-Unicode applications.

Solution:

According to the character set used by your Genero application, you must either use CHAR/VARCHAR or NCHAR/NVARCHAR columns with SQL Server. If the charset is single-byte , you can use CHAR/VARCHAR columns. If the charset set is multi-byte or Unicode (i.e. UTF-8), you must use NCHAR/NVARCHAR columns in SQL Server. However, not all SQL Server ODI drivers support NCHAR/NVARCHAR.

See also the section about Localization.

Make sure that the regional language settings for non-Unicode applications corresponds to the locale used by Genero programs.

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

Warning: On Windows, SQL Server national character set data types are not supported with the MSV database driver: You must use the SNC database driver based on the SQL Native Client library. On UNIX, you can use the FTM or ESM driver, both support SQL Server national character set data.

Using the SNC driver

The SNC driver can work in char or in wide-char mode. The character size mode can be controlled by the following FGLPROFILE entry:

   dbi.database.<dbname>.snc.widechar = { true | false }

By default the SNC database driver works in Wide Char mode (true).

Using SNC driver in char mode:

The char mode can be used with applications defining character string columns with CHAR/VARCHAR/TEXT types. It is not mandatory (i.e. the wide-char mode could be used), but it appears that SQL Server behaves in different ways when wide-char bindings are used for CHAR/VARCHAR/TEXT columns.

When defining CHAR(n)/VARCHAR(n) columns in SQL Server, you specify n as a number of bytes, and this follows the byte-length semantics used in Genero BDL when declaring variables (i.e. SQL Server VARCHAR(10) column = Genero BDL VARCHAR(10) variable).

In char mode, the SNC driver will pass the character strings of SQL text and SQL parameters as is to SQL Server Native Client, using the current character set encoding. SQL Parameters will be bound with the SQL_C_CHAR + SQL_CHAR/SQL_VARCHAR ODBC types, and string literals in SQL statements will not get the N prefix as when using the wide-char mode.

Using SNC driver in wide-char mode:

The wide-char mode should be used for applications defining character string columns with NCHAR/NVARCHAR/NTEXT types. Such types can store Unicode characters and it is preferable that the SNC driver works in wide-char mode. By the way, the Genero BDL runtime system should also work in UTF-8 mode.

NCHAR / NVARCHAR and NTEXT SQL SERVER column data types can be used in tables. However, you must use CHAR / VARCHAR / TEXT Genero types for program variable to hold NCHAR, NVARCHAR and NTEXT data. Make sure the size of the program variables is large enough to hold all sort of UNICODE characters in the code page used by the program. In order to store the same strings as with INFORMIX databases, the SNC database driver applies the following rules:

  1. In FGL, when declaring a [VAR]CHAR(n) variable to be used as SQL parameter (in a USING clause for example), the length n is specified in bytes. The SNC database driver maps this type to an ODBC parameter as N[VAR]CHAR with a precision of n, in the meaning of characters. In SQL SERVER, the target column must be defined as N[VAR]CHAR with the same size n, to be able to store n ASCII characters like the INFORMIX equivalent UNICODE (UTF-8) column. The SQL SERVER column can actually hold more non-ASCII characters as the INFORMIX column (using byte length semantics), but this is the only way to store the same strings. 
  2. Following the above rule, when fetching rows from SQL SERVER, the SNC database driver maps an N[VAR]CHAR(n) (with n as chars) to an intermediate fetch buffer as [VAR]CHAR(n) (with n as bytes), and the program variable will certainly be defined as [VAR]CHAR(n bytes) from a database schema. As a result, if another application has inserted in the N[VAR]CHAR(n chars) column more characters as can fit in the [VAR]CHAR(n bytes) program variable, the string value will be truncated.

All string literals of an SQL statement are automatically changed to get the N prefix. Thus, you don't need to add the N prefix by hand in all of your programs. This solution makes your Genero code portable to other databases.

Character string data is converted from the current Genero BDL locale to Wide Char (i.e. WCHAR/wchar_t 16-bit Unicode = UCS-2), before is it used in an ODBC call such as SQLPrepareW or SQLBindParameter(SQL_C_WCHAR). When fetching character data, the SNC database driver converts from Wide Char to the current Genero BDL locale. The current Genero BDL locale is defined by LANG, and if LANG is not defined, the default is the ANSI Code Page of the Windows operating system. 

Using the FTM driver

When using the FTM (FreeTDS) database driver, string literals get the N prefix only if the current locale (LANG / LC_ALL) defines a multi-byte code set such as .big5 or .utf8. String literals are not touched if the locale uses a single-byte character set.

With the FTM (FreeTDS) database driver, SQL Statements are prepared with SQLPrepare(), by using the current character set. FreeTDS takes in charge the conversion from the client charset to UCS-2 before sending the SQL text to the server. ODBC SQL parameters with character string data are bound (SQLBindParameter) with the C type SQL_C_CHAR and with the SQL type SQL_W[VAR]CHAR (=UNICODE) or with SQL_[VAR]CHAR, according to the current locale. The SQL_W[VAR]CHAR type is used if the current locale is a multi-byte encoding. When using a single-byte encoding, parameters are bound with the SQL_[VAR]CHAR type. As a result, the necessary character set conversion is taken in charge by FreeTDS and is optimized when using a single-byte character set. However, it is critical to declare the correct client character set in FreeTDS configuration files. The FreeTDS client character set is defined by the "client charset" parameter in freetds.conf, or (since 0.83 only) with "ClientCharset" parameter in odbc.ini.

Using the ESM driver

When using the ESM (EasySoft) database driver, string literals get the N prefix only if the current locale (LANG / LC_ALL) defines a multi-byte code set such as .big5 or .utf8. String literals are not touched if the locale uses a single-byte character set.

When using the ESM (EasySoft) database driver, SQL Statements are prepared with SQLPrepare(), by using the current character set. EasySoft takes in charge the conversion from the client charset to UCS-2 before sending the SQL text to the server. ODBC SQL parameters with character string data are bound (SQLBindParameter) with the C type SQL_C_CHAR and with the SQL type SQL_W[VAR]CHAR  (=UNICODE) type. As a result, the necessary character set conversion is taken in charge by EasySoft. However, it is critical to declare the correct client character set in EasySoft configuration files. The EasySoft client character set is defined by the "Client_CSet" parameter in odbc.ini.

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:

INFORMIX Microsoft SQL SERVER
CREATE TABLE scott.emp (
...
empcode CHAR(10) UNIQUE
   [CONSTRAINT pk_emp],
...		 CREATE TABLE scott.emp (
...
empcode CHAR(10)
   [CONSTRAINT pk_emp] UNIQUE,
...

Warning: SQL SERVER does not produce an error when using the INFORMIX syntax of constraint naming.

The NULL / NOT NULL constraint:

Note:  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, or in the database options of SQL SERVER Management Studio.

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:

Before using a 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.

Triggers

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

Microsoft SQL SERVER does not support "BEFORE" triggers.

Microsoft SQL SERVER does not support row-level triggers.

Solution:

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

 Stored procedures

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

Solution:

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

Defining database users

Until version 11.70.xC2, INFORMIX database users must be created at the operating system level and be members of the 'informix' group. Starting with 11.70.xC2, INFORMIX supports database-only users with the CREATE USER instruction, as most other db servers. Any database user must have sufficient privileges to connect and use resources of the database; user rights are defined with the GRANT command.

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.

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.

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 ...

Unfortunately, SQL Server temporary tables are created by default with the collation of the tempdb database, instead of inheriting the collation of the current database you are connected to.

Solution:

In BDL, INFORMIX temporary tables instructions are converted to generate native SQL SERVER temporary tables.

Microsoft SQL SERVER does not support scroll cursors based on a temporary table.

You must install SQL Server with the same collation as your database, see Installation for more details.

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 tab1  -- Gives 'tex'

Solution:

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

 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 ...

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)

Name resolution of SQL objects

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

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

Object names are limited to 128 characters 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, you can 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:

As a general rule, to write portable SQL, you should only use simple database object names without any database, server or owner qualifier and without quoted identifiers.

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

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'

INFORMIX allows double quotes as string delimiters, but SQL SERVER doesn't. This is important, since many BDL programs use that character to delimit the strings in SQL commands.

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

National character strings:

With SQL SERVER, all UNICODE strings must be prefaced with an N character:

     UPDATE cust SET cust_name = N'矇閬頝' WHERE cust_id=123

If you don't specify the N prefix, SQL SERVER will convert the characters from the current system locale to the database locale. If the string is prefixed with N, the server can recognize a UNICODE string and use it as is to insert into NCHAR or NVARCHAR columns.

Solution:

The SQL SERVER database interface can automatically replace all double quotes by single quotes.

Escaped string delimiters can be used inside strings like the following:

     'This is a single quote : '''
     'This is a single quote : \''
     "This is a double quote : """
     "This is a double quote : \""

Warning: Database object names cannot be delimited by double quotes because the database interface cannot determine the difference between a database object name and a quoted string !

For example, if the program executes the SQL statement:
    WHERE "tabname"."colname" = "a string value"
replacing all double quotes by single quotes would produce :
    WHERE 'tabname'.'colname' = 'a string value'
This would produce an error since 'tabname'.'colname' is not allowed by ORACLE.

Although double quotes are replaced automatically in SQL statements, you should use only single quotes to enforce portability.

National character strings:

When using the SNC database driver, all string literals of an SQL statement are automatically changed to get the N prefix. Thus, you don't need to add the N prefix by hand in all of your programs. This solution makes by the way your Genero code portable to other databases.

With the SNC database driver, character string data is converted from the current Genero BDL locale to Wide Char (Unicode UCS-2), before is it used in an ODBC call such as SQLPrepareW or SQLBindParameter(SQL_C_WCHAR). When fetching character data, the SNC database driver converts from Wide Char to the current Genero BDL locale. The current Genero BDL locale is defined by LANG, and if LANG is not defined, the default is the ANSI Code Page of the Windows operating system. See CHARACTER data types for more details.

When using the FTM (FreeTDS) or the ESM (EasySoft) database driver on UNIX, string literals get the N prefix if the current locale is a multi-byte encoding like BIG5, EUC-JP or UTF-8. If the current locale is a single-byte encoding like ISO-8859-1, no prefix will be added to the string literals.

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 :

INFORMIX Microsoft SQL SERVER
SMALLINT SMALLINT
INTEGER (synonym: INT) INTEGER (synonym: INT)
BIGINT BIGINT
INT8 BIGINT
DECIMAL[(p[,s])] (synonyms: DEC, NUMERIC)
DECIMAL(p,s) defines a fixed point decimal where p is the total number of significant digits and s the number of digits that fall on the right of the decimal point.
DECIMAL(p) defines a floating point decimal where p is the total number of significant digits.
The precision p can be from 1 to 32.
DECIMAL is treated as DECIMAL(16).		 DECIMAL[(p[,s)] (synonyms: DEC, NUMERIC)
DECIMAL[(p[,s])] defines a fixed point decimal where p is the total number of significant digits and s the number of digits that fall on the right of the decimal point. The maximum precision is 38.
Without any decimal storage specification, the precision defaults to 18 and the scale defaults to zero:
- DECIMAL in SQL SERVER = DECIMAL(18,0) in INFORMIX
- DECIMAL(p) in SQL SERVER = DECIMAL(p,0) in INFORMIX
MONEY[(p[,s])


 SQL SERVER provides the MONEY and SMALLMONEY data types, but the currency symbol handling is quite different. Therefore, INFORMIX MONEY columns should be implemented as DECIMAL columns in SQL SERVER.
SMALLFLOAT (synonyms: REAL) REAL
FLOAT[(n)]  (synonyms: DOUBLE PRECISION)
The precision (n) is ignored.		 FLOAT(n) (synonyms: DOUBLE PRECISION)
Where n must be from 1 to 15.

Solution:

In BDL programs:

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

Warning: There is no SQL Server equivalent for the INFORMIX DECIMAL(p) floating point decimal (i.e. without a scale). If your application is using such data types, you must review the database schema in order to use SQL Server compatible types. To workaround the SQL Server limitation, the SQL Server database drivers convert DECIMAL(p) types to a DECIMAL( 2*p, p ), to store all possible numbers an INFORMIX DECIMAL(p) can store. However, the original INFORMIX precision cannot exceed 19, since SQL Server maximum DECIMAL precision is 38 (2*19). If the original precision is bigger as 19, a CREATE TABLE statement executed from a Genero program will fail with an SQL Server error 2750.

In database creation scripts:

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.

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 :

Note that the LIKE operator of SQL Server does not evaluate to true with CHAR/NCHAR columns, if the LIKE pattern is provided as a UNICODE string literal (with the N prefix) and the search pattern matches the value in the column (without an ending % wildcard for example). See the following test:

   CREATE TABLE mytable ( k INT, nc NCHAR(20) )
   INSERT INTO mytable VALUES ( 1, N'abc' )
   SELECT * FROM mytable WHERE nc = 'abc'        -- one row is returned
   SELECT * FROM mytable WHERE nc = N'abc'       -- one row is returned
   SELECT * FROM mytable WHERE nc LIKE 'abc'     -- one row is returned
   SELECT * FROM mytable WHERE nc LIKE N'abc'    -- no rows are found
   SELECT * FROM mytable WHERE nc LIKE N'abc%'   -- one row is returned

This might be an issue because the SQL Server driver will by default automatically add an N prefix before all string literals in SQL statements. See Microsoft SQL Server documentation for more details about the LIKE semantics regarding blank padding and see also CHARACTER data types for the N prefix usage: You might consider setting the snc.widechar FGLPROFILE parameter to false if you are using CHAR/VARCHAR types.

Solution:

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

Pay attention to UNICODE string prefixes N'...' in the LIKE expressions when used with CHAR/NCHAR columns. You might want to always add a % wildcard at the end of the LIKE condition, or use the equal operator when doing a query with exact values.

See also: MATCHES operator in SQL Programming.

 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:

Solution:

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

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.

Very large data types

INFORMIX and Genero support the TEXT and BYTE types. TEXT is used to store large text data, while BYTE is used to store large binary data like images or sound.

Microsoft SQL SERVER provides text, ntext and image data types to store large data, but these data types are considered as obsolete in SQL SERVER 2005 and will be removed in a future version. When using SQL SERVER 2005, Microsoft recommends to user varchar(max), nvarchar(max) and varbinary(max) data type instead. These "max" data types are not supported with the MSV database driver, since it is based on MDAC ODBC. You must use the new SNC database driver based on the SQL Native Client ODBC driver shipped with SQL SERVER 2005.

In SQL Server 2005 and 2008, the *var*(max) types have a limit of 2 gigabytes (2^31 -1 actually). Old text, ntext and image types have the same limit.

Solution:

Genero TEXT/BYTE program variables have a limit of 2 gigabytes; make sure that the large object data does not exceed this limit. This is the case with SQL Server 2005 and 2008 when using the *var*(max) types.

Note: When using a stored procedure that has SET/IF statements and produces a result set with LOBs, the LOB columns must appear at the end of the SELECT list. If LOB columns are followed by other columns with regular types, the fetching rows will fail. Using SET NOCOUNT ON in the stored procedure does not help, because the cursor type is changed from a server cursor to a default result set cursor.

When using the MSV database driver based on MDAC ODBC, the TEXT and BYTE data types of a static CREATE TABLE statement are converted to text and image SQL SERVER types.

When using the SNC, FTM or ESM database drivers, the TEXT and BYTE data types of a static CREATE TABLE statement are converted to varchar(max) and varbinary(max) SQL SERVER types.

All database drivers make the appropriate bindings to use TEXT and BYTE types as SQL parameters and fetch buffers.

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. You can change this behavior using the SET CURSOR_CLOSE_ON_COMMIT ON.

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.

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:

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

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 [,...]

The LENGTH() function

INFORMIX provides the LENGTH() function:

    SELECT LENGTH("aaa"), LENGTH(col1) FROM table

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

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

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.

Note:  If you create a user function in SQL SERVER as follows:

create function length(@s varchar(8000))
returns integer
as
begin
return len(@s)
end

You must qualify the function with the owner name:

    SELECT dbo.length(col1) FROM table

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:

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.

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 :

INFORMIX Microsoft SQL SERVER
Physical units of storage
The largest unit of physical disk space is a "chunk", which can be allocated either as a cooked file ( I/O is controlled by the OS) or as raw device (=UNIX partition, I/O is controlled by the database engine). A "dbspace" uses at least one "chunk" for storage.
You must add "chunks" to "dbspaces" in order to increase the size of the logical unit of storage.		 SQL SERVER uses "filegroups", based on Windows NT operating system files and therefore define the physical location of data.
A "page" is the smallest physical unit of disk storage that the engine uses to read from and write to databases.
A "chunk" contains a certain number of "pages".
The size of a "page" must be equal to the operating system's block size.		 As in INFORMIX, SQL SERVER stores data in "pages" with a size fixed at 2Kb in V6.5 and 8Kb in V7 and later.
An "extent" consists of a collection of continuous "pages" that the engine uses to allocate both initial and subsequent storage space for database tables.
When creating a table, you can specify the first extent size and the size of future extents with the EXTENT SIZE and NEXT EXTENT options.
For a single table, "extents" can be located in different "chunks" of the same "dbspace".		 An "extent" is a specific number of 8 contiguous pages, obtained in a single allocation.
Extents are allocated in the filegroup used by the database.
Logical units of storage
A "table" is a logical unit of storage that contains rows of data values. Same concept as INFORMIX.
A "database" is a logical unit of storage that contains table and index data. Each database also contains a system catalog that tracks information about database elements like tables, indexes, stored procedures, integrity constraints and user privileges. Same concept as INFORMIX.
When creating a "database", you must specify which "database devices" (V6.5) or "filegroup" (V7) has to be used for physical storage.
Database tables are created in a specific "dbspace", which defines a logical place to store data.
If no dbspace is given when creating the table, INFORMIX defaults to the current database dbspace.		 Database tables are created in a database based on "database devices" (V6.5) or a "filegroup" (V7), which defines the physical storage.
The total disk space allocated for a table is the "tblspace", which includes "pages" allocated for data, indexes, blobs, tracking page usage within table extents.. No equivalent.
Other concepts
When initializing an INFORMIX engine, a "root dbspace" is created to store information about all databases, including storage information (chunks used, other dbspaces, etc.). SQL SERVER uses the "master" database to hold system stored procedures, system messages, SQL SERVER logins, current activity information, configuration parameters of other databases.
The "physical log" is a set of continuous disk pages where the engine stores "before-images" of data that has been modified during processing.
The "logical log" is a set of "logical-log files" used to record logical operations during on-line processing. All transaction information is stored in the logical log files if a database has been created with transaction log.
INFORMIX combines "physical log" and "logical log" information when doing fast recovery. Saved "logical logs" can be used to restore a database from tape.		 Each database has its own "transaction log" that records all changes to the database. The "transaction log" is based on a "database device" (V6.5) or "filegroup" (V7) which is specified when creating the database.
SQL SERVER checks the "transaction logs" for automatic recovery.

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 Transact-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.

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 a text file and the LOAD instruction inserts rows from a text file into a database table.

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; note the following;

 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-insensitive 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.

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.

String concatenation operator

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

     SELECT firstname || ' ' || lastname FROM employee

The 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.

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:

 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.

SQL Interruption

With INFORMIX, it is possible to interrupt a long running query if the SQL INTERRUPT ON option is set by the Genero program. The database server returns SQLCODE -213, which can be trapped to detect a user interruption.

    MAIN
      DEFINE n INTEGER
      DEFER INTERRUPT
      OPTIONS SQL INTERRUPT ON
      DATABASE test1
      WHENEVER ERROR CONTINUE
      -- Start long query (self join takes time)
      -- From now on, user can hit CTRL-C in TUI mode to stop the query
      SELECT COUNT(*) INTO n FROM customers a, customers b
           WHERE a.cust_id <> b.cust_id
      IF SQLCA.SQLCODE == -213 THEN
         DISPLAY "Statement was interrupted by user..."
         EXIT PROGRAM 1
      END IF
      WHENEVER ERROR STOP
      ...
    END MAIN

SQL SERVER 2005 supports SQL Interruption in a similar way as INFORMIX. The db client must issue an SQLCancel() ODBC call to interrupt a query.

Solution:

The SNC and ESM database drivers support SQL interruption and return the INFORMIX error code -213 if the statement is interrupted.

Important:  Make sure you have SQL SERVER 2005 or higher installed and that you use the SNC or ESM database driver.

Scrollable Cursors

The Genero programming language supports scrollable cursors with the SCROLL keyword, as shown in the following code example: 

   DECLARE c1 SCROLL CURSOR FOR SELECT * FROM customers ORDER BY cust_name
   ...
   FETCH FIRST c1 INTO rec_cust.*
   ...
   FETCH NEXT c1 INTO rec_cust.*
   ...
   FETCH LAST c1 INTO rec_cust.*

SQL Server supports native scrollable cursors.

Solution:

All the SQL SERVER database drivers use the native SQL Server scrollable cursors by setting the ODBC statement attribute SQL_ATTR_CURSOR_SCROLLABLE to SQL_SCROLLABLE.

Data type conversion table

INFORMIX Data Types SQL SERVER Data Types (<2008) SQL SERVER Data Types (>=2008)
CHAR(n) CHAR(n) (limit = 8000b!) CHAR(n) (limit = 8000b!)
VARCHAR(n[,m]) VARCHAR(n) (limit = 8000b!) VARCHAR(n) (limit = 8000b!)
LVARCHAR(n) VARCHAR(n) (limit = 8000b!) VARCHAR(n) (limit = 8000b!)
NCHAR(n) NCHAR(n) (UNICODE, limit = 4000c!) NCHAR(n) (UNICODE, limit = 4000c!)
NVARCHAR(n[,m]) NVARCHAR(n) (UNICODE, limit = 4000c!) NVARCHAR(n) (UNICODE, limit = 4000c!)
BOOLEAN BIT BIT
SMALLINT SMALLINT SMALLINT
INT / INTEGER INTEGER INTEGER
BIGINT BIGINT BIGINT
INT8 BIGINT BIGINT
SERIAL[(start)] INTEGER (see notes) INTEGER (see notes)
BIGSERIAL[(start)] BIGINT (see notes) BIGINT (see notes)
SERIAL8[(start)] BIGINT (see notes) BIGINT (see notes)
DOUBLE PRECISION / FLOAT[(n)] FLOAT(n) FLOAT(n)
REAL / SMALLFLOAT REAL REAL
NUMERIC / DEC / DECIMAL(p,s) DECIMAL(p,s) DECIMAL(p,s)
NUMERIC / DEC / DECIMAL(p) with p<=19 DECIMAL(2*p,p) DECIMAL(2*p,p)
NUMERIC / DEC / DECIMAL(p) with p>19 N/A N/A
NUMERIC / DEC / DECIMAL DECIMAL(32,16) DECIMAL(32,16)
MONEY(p,s) DECIMAL(p,s) DECIMAL(p,s)
MONEY(p) DECIMAL(p,2) DECIMAL(p,2)
MONEY DECIMAL(16,2) DECIMAL(16,2)
DATE DATETIME DATE
DATETIME HOUR TO MINUTE DATETIME TIME(0)
DATETIME HOUR TO FRACTION(n) DATETIME TIME(n)
DATETIME YEAR TO SECOND DATETIME DATETIME2(0)
DATETIME q1 TO q2 (different from above) DATETIME DATETIME2(n)
INTERVAL q1 TO q2 CHAR(50) CHAR(50)
TEXT VARCHAR(MAX)
(TEXT with MSV driver)		 VARCHAR(MAX)
BYTE VARBINARY(MAX)
(IMAGE with MSV driver)		 VARBINARY(MAX)