ODIMYS001 - DATE and DATETIME data types

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

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

MySQL provides the following data type to store dates :

• DATE = for year, month, day storage.
• TIME = for hour, minute, second storage.
• DATETIME = for year, month, day, hour, minute, second storage.
• TIMESTAMP = automatically updated when row is touched.

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, MySQL can convert quoted strings to datetime data according the ISO datetime format ( YYYY-MM-DD hh:mm:ss' ).

Date arithmetic:

• INFORMIX supports date arithmetic on DATE and DATETIME values. The result of an arithmetic expression involving dates/times is a number of days when only DATEs are used and an INTERVAL value if a DATETIME is used in the expression.
• In MySQL, the result of an arithmetic expression involving DATE values is an INTEGER representing a number of days.
• INFORMIX automatically converts an integer to a date when the integer is used to set a value of a date column. MySQL does not support this automatic conversion.
• Complex DATETIME expressions ( involving INTERVAL values for example ) are INFORMIX specific and have no equivalent in MySQL.

Solution :

MySQL has the same DATE data type as INFORMIX ( year, month, day ). So you can use MySQL DATE data type for Informix DATE columns.

MySQL TIME data type can be used to store INFORMIX DATETIME HOUR TO SECOND values. The database interface makes the conversion automatically.

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

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

ODIMYS003 - Reserved words

SQL object names like table and column names cannot be SQL reserved words in MySQL.

Solution :

Table or column names which are MySQL reserved words must be renamed.

ODIMYS004 - ROWIDs

MySQL does not have an equivalent for the Informix ROWID pseudo-column.

Solution :

Warning: ROWIDs are not supported. You must review the code using ROWIDs and use primary key columns instead.

ODIMYS005 - SERIAL data type

INFORMIX SERIAL data type and automatic number production :

• The table column must be of type SERIAL.
• To generate a new serial, no value or zero value is given to the INSERT statement :
     INSERT INTO tab1 ( c ) VALUES ( 'aa' )
     INSERT INTO tab1 ( k, c ) VALUES ( 0, 'aa' )
• After INSERT, the new SERIAL value is provided in SQLCA.SQLERRD[2].
• INFORMIX allows you to insert rows with a value different from zero for a serial column. Using an explicit value will automatically increment the internal serial counter, to avoid conflicts with future INSERT statements 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

MySQL AUTO_INCREMENT column definition option:

• In CREATE TABLE, you specify a auto-incremented column with the AUTO_INCREMENT attribute
• Auto-incremented columns have the same behavior as INFORMIX SERIAL columns
• You define a start value with ALTER TABLE tabname AUTO_INCREMENT = value
• The column must be the primary key

Solution :

The INFORMIX SERIAL data type is emulated with MySQL AUTO_INCREMENT option. After an insert, sqlca.sqlerrd[2] holds the last generated serial value.

Warning : AUTO_INCREMENT columns must be primary keys. This is handled automatically when you create a table in a BDL program.

ODIMYS006 - Outer joins

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

SELECT ... FROM a, OUTER(b)
 WHERE a.key = b.akey

SELECT ... FROM a, OUTER(b,OUTER(c))
 WHERE a.key = b.akey
   AND b.key1 = c.bkey1
   AND b.key2 = c.bkey2 

MySQL 3.23 supports the ANSI outer join syntax :

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

SELECT ...
  FROM cust LEFT OUTER JOIN order
                 LEFT OUTER JOIN item
                 ON order.key = item.ordno
            ON cust.key = order.custno
 WHERE order.cdate > current date

See the MySQL reference for a complete description of the syntax.

Solution :

The MySQL interface can convert most INFORMIX OUTER specifications to ANSI outer joins.

Prerequisites :

1. In the FROM clause, the main table must be the first item and the outer tables must figure from left to right in the order of outer levels.
      Example which does not work : "FROM OUTER(tab2), tab1".
2. The outer join in the WHERE part must use the table name as prefix.
      Example : "WHERE tab1.col1 = tab2.col2".

Restrictions :

1. 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".
2. Statements composed by 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 side 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.

ODIMYS007a - Database concepts

Most BDL applications use only one database entity (in the meaning of INFORMIX). But the same BDL application can connect to different occurrences of the same database schema, allowing several users to connect to those different databases.

Like INFORMIX servers, MySQL can handle multiple database entities. Tables created by a user can be accessed without the owner prefix by other users as long as they have access privileges to these tables. 

Solution :

Create a MySQL database for each INFORMIX database. 

ODIMYS008a - 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 handle data consistency and concurrency. When a process changes database information with UPDATE, INSERT or DELETE, an exclusive lock is set on the touched rows. The lock remains active 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 :

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

Defaults :

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

MySQL

When data is modified, exclusive locks are set and held until the end of the transaction. For data consistency, MySQL uses a locking mechanism. Readers must wait for writers as in INFORMIX.

Control :

• No lock wait mode control is provided.
• Isolation level : SET TRANSACTION ISOLATION LEVEL ...
• Explicit exclusive lock : SELECT ... FOR UPDATE

Defaults :

• The default isolation level is Read Committed.
• The default locking granularity is per table (pre page when using BDB tables).

Solution :

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

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

ODIMYS008b - 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 ... FROM tab WHERE ... FOR UPDATE
   OPEN cc
   FETCH cc <-- lock is acquired
   ...
   CLOSE cc <-- lock is released

Warning : MySQL locking mechanism depends upon the transaction manager. The default locking granularity is per table when you use the default non-transactional configuration.

Solution :

Review the program logic.

ODIMYS009 - Transactions handling

INFORMIX and MySQL handle transactions in a similar manner.

INFORMIX native mode (non ANSI) :

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

MySQL :

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

Solution :

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

Warning : MySQL does not support transactions by default. You must set the server system parameter table_type=InnoDB.

Regarding the transaction control instructions, the BDL applications do not have to be modified in order to work with MySQL, as long as you have a transaction manager installed with MySQL.

ODIMYS010 - Handling SQL errors when preparing statements

The MySQL connector is implemented with the MySQL libmysqlclient API. This library does not provide a way to send SQL statements to the database server during the BDL PREPARE instruction, like the INFORMIX interface. The statement is sent to the server only when opening the cursors or when executing the statement.

Therefore, when preparing a SQL statement with the BDL PREPARE instruction, no SQL errors can be returned if the statement has syntax errors or if a column or a table name does not exist in the database.

However, a SQL error will occur after the OPEN or EXECUTE instructions.

Solution :

Check that your BDL programs do not test STATUS or SQLCA.SQLCODE variable just after PREPARE instructions.

Change the program logic in order to handle the SQL errors when opening the cursors (OPEN) or when executing SQL statements (EXECUTE).

ODIMYS011 - CHARACTER data types

As INFORMIX, MySQL 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.

MySQL CHAR and VARCHAR  both have a limit of 255 characters. You can define CHAR and VARCHAR columns with a length greater than 255, but the native data type will automatically be changed to TEXT.

Warning : MySQL automatically creates a TEXT data type when using a size that exceeds 255 characters.

Solution :

The database interface supports character string variables in SQL statements for input (BDL USING) and output (BDL INTO).

ODIMYS016 - Defining database users

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

MySQL users must be registered in the database. They are created with the GRANT SQL instruction:

   $ mysql -u root -pmanager --host orion test

   mysql> GRANT ALL PRIVILEGES ON * TO mike IDENTIFIED BY 'pswd';

Solution :

According to the application logic (is it a multi-user application ?), you have to create one or several MySQL users.

ODIMYS017 - 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 be dropped with the DROP TABLE command. There is no name conflict when several users create temporary tables with the same name.

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.

MySQL support temporary tables with the following syntax:

Solution :

Review the code and use native SQL syntax to create temporary tables. 

ODIMYS018 - 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 MySQL provides the SUBSTR( ) function, to extract a substring from a string expression :

    SELECT .... FROM tab1 WHERE SUBSTRING(col1,2,3) = 'RO'
    SELECT SUBSTRING('Some text',6,3) ...   -- 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 MySQL :
    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 MySQL :
    SELECT ... FROM tab1 ORDER BY col1[1,3]
is converted to :
    SELECT ... FROM tab1 ORDER BY SUBSTRING(col1,1,(3-1+1))

ODIMYS019 - Name resolution of SQL objects

INFORMIX uses the following form to identify a SQL object :

  [database[@dbservername]:][{owner|"owner"}.](identifier|"identifier")

With MySQL, an object name takes the following form :

  [database.]identifier

Solution :

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

ODIMYS020 - Database object name delimiters

INFORMIX identifies database object names with double quotes, while MySQL does not use the double quotes as database object identifiers.

Solution :

Check your programs for database object names having double quotes:

     WHERE "tabname"."colname" = "a string value"

should be written as follows:

     WHERE tabname.colname = 'a string value'

ODIMYS021 - NUMERIC data types

INFORMIX supports several data types to store numbers :

Solution :

MySQL supports the following data types to store numbers :

Warning : MySQL DECIMALs have a maximum range like DOUBLEs.

ODIMYS024 - MATCHES and LIKE in SQL conditions

INFORMIX supports MATCHES and LIKE in SQL statements. MySQL supports the LIKE statement as in INFORMIX, plus the ~ operators that are similar but different from the INFORMIX MATCHES operator.

MATCHES allows brackets to specify a set of matching characters at a given position :
   ( col MATCHES '[Pp]aris' ).
   ( col MATCHES '[0-9][a-z]*' ).
In this case, the LIKE statement has no equivalent feature.

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

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

Solution :

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

ODIMYS025 - INFORMIX specific SQL statements in BDL

The BDL compiler supports several INFORMIX specific SQL statements that have no meaning when using MySQL:

(removed a sentence as unnecessary)

• CREATE DATABASE
• DROP DATABASE
• START DATABASE (SE only)
• ROLLFORWARD DATABASE
• SET [BUFFERED] LOG
• CREATE TABLE with special options (storage, lock mode, etc.)

Solution :

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

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

MySQL does not support insert cursors.

Solution :

Insert cursors are emulated by the MySQL database interface.

ODIMYS031 - Cursors WITH HOLD

INFORMIX closes opened cursors automatically when a transaction ends unless the WITH HOLD option is used in the DECLARE instruction. In MySQL, opened cursors using SELECT statements without a FOR UPDATE clause are not closed when a transaction ends. Actually, all MySQL cursors are 'WITH HOLD' cursors unless the FOR UPDATE clause is used in the SELECT statement.

Warning : Cursors declared FOR UPDATE and using the WITH HOLD option cannot be supported with MySQL because FOR UPDATE cursors are automatically closed by MySQL when the transaction ends.

Solution :

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

Warning : Since MySQL automatically closes FOR UPDATE cursors when the transaction ends, opening cursors declared FOR UPDATE and WITH HOLD option results in an SQL error that does not normally appear with INFORMIX, in the same conditions. Review the program logic in order to find another way to set locks.

ODIMYS032 - UPDATE/DELETE WHERE CURRENT OF <cursor>

INFORMIX allows positioned UPDATEs and DELETEs with the "WHERE CURRENT OF <cursor>" clause, if the cursor has been DECLARED with a SELECT ... FOR UPDATE statement.

Solution :

Warning : WHERE CURRENT OF is not supported by MySQL.

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

ODIMYS036 - INTERVAL data type

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

MySQL provides an INTERVAL data type, but it is totally different from the INFORMIX INTERVAL type. For example, you specify a INTERVAL literal as follows :

    25 years 2 months 23 days

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.

ODIMYS039 - Data storage concepts

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

Storage concepts are quite similar in INFORMIX and in MySQL, but the names are different.

ODIMYS046 - 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 instructions insert rows from a text file into a database table.

MySQL does not provide LOAD and UNLOAD instructions, but provides external tools like SQL*Plus and SQL*Loader.

Solution :

LOAD and UNLOAD instructions are supported.

Warning : There is a difference when using MySQL DATE columns; DATE columns created in the MySQL database are similar to INFORMIX DATETIME YEAR TO SECOND columns. In LOAD and UNLOAD, all MySQL DATE columns are treated as INFORMIX DATETIME YEAR TO SECOND columns and thus will be unloaded with the "YYYY-MM-DD hh:mm:ss"  format.
The same problem appears for INFORMIX INTEGER and SMALLINT values which are stored in an MySQL database as NUMBER(?) columns. Those values will be unloaded as INFORMIX DECIMAL(10) and DECIMAL(5) values, that is, with a trailing dot-zero ".0".

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 MySQL database is not supported.

Warning : UNLOAD instructions based on SELECT statements using date expressions such as "WHERE ?-datecol>?" are not supported because of database server limitations. A syntax error would be raised in this case.

ODIMYS100 - Data type conversion table