Runtime configuration
Database concepts
| Database concepts |
| Data storage concepts |
| Data consistency and concurrency management |
| Transactions handling |
| Defining database users |
| Setting privileges |
Data dictionary
Data manipulation
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 |
Install SQL Server and create a database
Install the Microsoft SQL Server on your computer.
Create a SQL Server database entity with the SQL Server Management Studio.
In the database properties:
Choose the right code page / collation to get a case-sensitive database; this cannot be changed later.
Make sure the "ANSI NULL Default" option is TRUE if you want to have the same default NULL constraint as in INFORMIX (i.e. a column created without NULL constraint will allow null values, users must specify NOT NULL to deny nulls).
Make sure the "Quoted Identifiers Enabled" option is FALSE to use database object names without quotes as in INFORMIX.
Create and declare a database user dedicated to your application: the application administrator.
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 ODIMSV005 for more details.
Create the application tables. Do not forget to convert INFORMIX data types to SQL Server data types. See issue ODIMSV100 for more details.
Warning: 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
Warning: Genero FGL 2.11 provides three kind of SQL Server drivers identified by the following codes: MSV, SNC and FTM.
All drivers are based on the ODBC API.
The MSV driver works with the Microsoft Data Access Component ODBC driver (SQLSVR32.DLL), and can be used with SQL Server 2000.
The MSV driver is supported since first versions of Genero FGL, but is not available for SQL Server 2008.If you have SQL Server 2005 (or higher) it is recommended to use the SNC driver based on the new SQL Native Client ODBC driver (SQLNCLI.DLL). This is the new ODBC driver recommended by Microsoft for SQL Server 2005 and +. Note that the SNC driver is not supported in a VC++ 6 environment.
The SNC driver is supported starting from Genero FGL 2.10.If you need to connect from a UNIX platform to SQL Server, you can use the FTM driver. This driver is based on the FreeTDS client open source software (www.freetds.org). You need at least FreeTDS version 0.82.
The FTM driver is supported starting from Genero FGL 2.11.An ODBC data source must be configured to allow 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").
Warning: When using the FTM driver (FreeTDS), you have to define the ODBCINI and ODBCINST environment variable to point to the odbc.ini and odbcinst.ini files.When using an MSV or SNC driver, you must have the Microsoft SQL Server Native Client installed on the computer running Genero applications.
When using the FTM driver, you must install FreeTDS. Note that in this case, there is no need to install a driver manager like unixODBC: The FTM driver is linked directly with the libtdsodbc.so shared library. However, you must create the odbc.ini and odbcinst.ini files to defined the data source. See FreeTDS documentation for more details about the data source configuration in ODBC files.
Warning: 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.
If needed, set up the fglprofile entries for database connections.
Check that the Genero distribution package has installed the database driver you need (i.e. a "dbmmsv*", "dbmsnc*" or "dbmftm*" driver must be installed.
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; |
select
day(0), month(0), year(0) |
Solution:
The SQL Server drivers will automatically map Informix date/time types to native SQL Server type, according the 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".
Warning: SQL Server SMALLDATETIME can store dates from January 1, 1900, through June 6, 2079. Therefore, we do not recommend to use this data type.
Warning: With SQL Server 2005 and lower, the fractional second part of a SQL Server DATETIME has a precision of 3 digits while INFORMIX has a precision up to 5 digits. Do not try to insert a datetime value in a SQL Server DATETIME with a precision more than 3 digits or a conversion error could occur. You can use the MS SUBSTRING() function to truncate the fraction part of the INFORMIX datetimes or another BDL solution. The fraction part of a 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. Starting with SQL Server 2008, the DATETIME2 native type will be used. This new type can store fraction of seconds with a precision of 7 digits, so Informix DATETIME values can be stored without precision lost.
Warning: When migrating to SQL Server 2008, you must pay attention if the database has DATETIME columns used to store Informix DATETIME HOUR TO SECOND or DATETIME HOUR TO FRACTION(n) types: Before version 2008, those types were stored in SQL Server DATETIME columns (filling missing date part with 1900-01-01). The SNC driver for SQL Server 2008 maps now DATETIME HOUR TO SECOND / FRACTION(n) to a TIME data type, which is not compatible with an SQL Server DATETIME type. To solve this problem, SQL Server DATETIME columns used to store DATETIME HOUR TO SECOND/FRACTION(n) must be converted to TIME columns (ALTER TABLE).
Warning: When fetching a TIME or DATETIME2 with a precision that is greater as 5 (the 4gl DATETIME precision limit), the database interface will allocate a buffer of VARCHAR(16) for the TIME and VARCHAR(27) for the DATETIME2 column. As a result, you can fetch such data into a CHAR or VARCHAR variable.
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: Literal DATETIME and INTERVAL expressions (i.e. DATETIME ( 1999-10-12) YEAR TO DAY) are not converted.
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. Use the MS GETDATE() function to get the system current date.
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'.
Warning: 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.
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).
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.
INFORMIX SERIAL data type and automatic number production:
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.
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.
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, the following statement:
INSERT INTO tab (col1,col2) VALUES (0, p_value)
must be converted to :
INSERT INTO tab (col2) VALUES (p_value)
Since 2.10.06, SELECT * FROM table INTO TEMP with original table having an IDENTITY column are supported: The database driver converts the INFORMIX SELECT INTO TEMP to the following sequence of statements:
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 INTEGER NOT NULL,
PRIMARY KEY ( tablename )
)
Warning: 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 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.
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: When a table is dropped, all associated triggers are also dropped.
Warning: INSERT statements using
NULL for the SERIAL column will produce a new serial value, instead of using NULL:
INSERT INTO tab (col1,col2) VALUES (NULL,'data')
This behavior is mandatory in order to support INSERT statements which do not use the serial column:
INSERT INTO tab (col2) 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.
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:
SELECT ... FROM cust, OUTER(order) WHERE cust.key = order.custnoSELECT ... 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.custnoSELECT ... 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:
The Microsoft SQL Server interface can convert simple INFORMIX OUTER specifications to Microsoft SQL Server ANSI outer joins.
Prerequisites:
Remarks:
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 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 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.
Control:
Defaults:
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 in BDL, the database interface sets automatically the native database session options.
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 Genero 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.
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.
INFORMIX and Microsoft SQL Server handle transactions in a similar manner.
INFORMIX native mode (non ANSI):
Microsoft SQL Server:
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.
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 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.
See also: National character data types
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:
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, 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:
Warning: 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.
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".
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".
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.
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.
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:
In BDL, 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.
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 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)
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, 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.
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
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 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 driver,
character string data is converted from the current FGL locale to Wide Char (UTF-16), before is it used in an ODBC call such as SQLPrepareW or SQLBindParameter(SQL_C_WCHAR). When fetching character data, the snc driver converts from Wide Char to the current FGL locale. The current FGL locale is defined by LANG, and if LANG is not defined, the default is the ANSI Code Page of the system.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) |
| 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 data types to corresponding Microsoft SQL Server data types.
Database creation scripts:
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.
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 :
Solution:
Warning: SQL statements using MATCHES expressions must be reviewed in order to use LIKE expressions.
See also: MATCHES operator in SQL Programming.
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.
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.
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 driver based on the SQL Native Client ODBC driver shipped with SQL Server 2005.
Solution:
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 database driver based on SQL Native Client ODBC, the TEXT and BYTE data types of a static CREATE TABLE statement are converted to varchar(max) and varbinary(max) SQL Server types.
Both msv and snc drivers make the appropriate bindings to use TEXT and BYTE types as SQL parameters and fetch buffers.
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.
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.
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 [,...]
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.
Warning: 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
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.
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. |
INFORMIX offers the NCHAR and NVARCHAR data types to store strings in a localized character set. The only difference between CHAR/VARCHAR and NCHAR/NVARCHAR in Informix is for sorting: N type use the collation order, while normal types use the byte order.
NCHAR/NVARCHAR 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:
Use of nchar, nvarchar, and ntext is the same as char, varchar, and text, respectively, except:
Solution:
National character set data types are not supported properly with the msv driver. You must use the snc driver based on the SQL Native Client library.
With the snc driver, 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. For example, using byte length semantics and a UTF-8 code page, an NCHAR(10) value can be hold in a CHAR(40) program variable, because some UTF-8 characters can be encoded on 4 bytes. If you want to store 10 of such characters you will need 40 bytes.
When using the snc 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 driver,
character string data is converted from the current FGL locale to Wide Char (UTF-16), before is it used in an ODBC call such as SQLPrepareW or SQLBindParameter(SQL_C_WCHAR). When fetching character data, the snc driver converts from Wide Char to the current FGL locale. The current FGL locale is defined by LANG, and if LANG is not defined, the default is the ANSI Code Page of the system.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.
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: With Microsoft SQL Server versions prior to 2008, 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". However, since SQL Server 2008, INFORMIX DATE data is stored in SQL Server DATE columns, so the result of a LOAD or UNLOAD statement is equivalent when using a DATE column with SQL Server 2008.
Warning: With Microsoft SQL Server versions prior to 2008, 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". With SQL Server 2008, INFORMIX DATETIME data is stored in SQL Server DATETIME2(n<=5) or TIME(n<=5) columns. Concerning DATETIME2(n<=5) columns, the result of LOAD and UNLOAD is equivalent to INFORMIX DATETIME columns, as long as the original INFORMIX type starts with the YEAR qualifier. The text data will be "YYYY-MM-DD hh:mm:ss.<fraction-digits>", where fraction-digits depends on the precision (n) of the DATETIME2(n) column. Concerning TIME(n) columns, the type is converted to an INFORMIX DATETIME HOUR TO SECOND or FRACTION(n). The text data will be be "hh:mm:ss.<fraction-digits>", where fraction-digits depends on the precision (n) of the TIME(n) column.
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.
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.
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.
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.
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.
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 database driver supports SQL interruption and converts the SQLSTATE HY008 to the Informix error code -213.
Warning: Make sure you have SQL Server 2005 or higher installed and that you use the SNC driver.
| INFORMIX Data Types | SQL Server Data Types (<2008) | SQL Server Data Types (>=2008) |
| CHAR(n) | CHAR(n) (limit = 8000c!) | CHAR(n) (limit = 8000c!) |
| VARCHAR(n) | VARCHAR(n) (limit = 8000c!) | VARCHAR(n) (limit = 8000c!) |
| INTEGER | INTEGER | INTEGER |
| SMALLINT | SMALLINT | SMALLINT |
| FLOAT[(n)] | FLOAT(n) | FLOAT(n) |
| SMALLFLOAT | REAL | REAL |
| DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) |
| MONEY(p,s) | DECIMAL(p,s) | DECIMAL(p,s) |
| 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) |
| Other sort of DATETIME type | DATETIME | DATETIME2(n) |
| INTERVAL q1 TO q2 | CHAR(n) | CHAR(n) |
| TEXT | VARCHAR(MAX) | VARCHAR(MAX) |
| BYTE | VARBINARY(MAX) | VARBINARY(MAX) |