Installation
Database concepts
| Database concepts |
| Data storage concepts |
| Data consistency and concurrency management |
| Transactions handling |
| Defining database users |
Data dictionary
Data manipulation
BDL programming
Supported MySQL versions are 5.0 and higher, MySQL 4.1.2 drivers are still be available, but MySQL version 4.1 is de-supported by the database vendor since January 2010.
Install the MySQL Server on your computer. You can download packages from www.mysql.com.
Configure the server with the appropriate storage engine:
Note that In order to have transaction
support by default, you must use a storage engine that supports
transactional tables.
The INNODB is a storage engine supporting transactions. In order
to use this storage engine as default, set the next configuration
parameter in the my.cnf or
my.ini file:
[mysqld]
default-storage-engine = INNODB
You can also set the default table type option in the command line when
starting the engine:
mysqld_safe
--default-storage-engine=InnoDB
Consider setting the sql-mode configuration parameter to get the appropriate behavior of the MySQL server:
When the STRICT_TRANS_TABLES mode is used, you will get a -1406 error (data too long) when inserting a character string that is too large for the target column. If you don't use the STRICT_TRANS_TABLES mode, you get a -1265 warning (data truncated) when the value is too large.
Blank padding of fetched CHAR data can be controlled with the PAD_CHAR_TO_FULL_LENGTH. You can use this parameter to get CHAR values padded with blanks to their full length, but the result of the SQL LENGTH() function will be different since trailing blanks are significant for that function in MySQL.
The mysqld process must be started to listen to database client connections. See MySQL documentation for more details about starting the database server process.
Create a database user dedicated to your application,
the application administrator.
Connect as the MySQL root user and GRANT all privileges to this user:
mysql -u root
...
mysql> grant all privileges on *.*
to 'mysuser'@'localhost'
identified by 'password';
Connect as the application
administrator and create a MySQL database with the CREATE DATABASE
statement:
mysql -u mysuser
...
mysql> create database
mydatabase;
Create the application tables. Do not forget to convert INFORMIX data types to MySQL data types. See issue Data Type Conversion Table for more details. If you have a transactional-safe table handler activated by default, you do not need to specify the TYPE option.
In order to connect to MySQL, you must have a MySQL database driver "dbmmys*" in FGLDIR/dbdrivers.
The MySQL client software is required to connect to a database server. Check if the MySQL client library (libmysqlclient.*) is installed on the system. Note that the shared library version of the MySQL client library must match the libmysqlclient library version linked to the dbmmys*.so ODI driver. For example, dbmmys55x.so requires libmysqlclient.so.18 to be installed.
Make sure that the MySQL client environment variables are properly set. Check for example MYSQL_HOME (the path to the installation directory), DATADIR (the path to the data files directory), etc. See MySQL documentation for more details about client environment variables to be set.
Verify the environment variable defining the search path for the database client shared library (libmysqlclient.so on UNIX, LIBMYSQL.dll on Windows). On UNIX platforms, the variable is specific to the operating system. For example, on Solaris and Linux systems, it is LD_LIBRARY_PATH, on AIX it is LIBPATH, or HP/UX it is SHLIB_PATH. On Windows, you define the DLL search path in the PATH environment variable.
|
MySQL version |
Shared library environment setting |
| MySQL 5.0 and higher |
UNIX: Add $MYSQL_HOME/lib to LD_LIBRARY_PATH
(or its equivalent). |
To verify if the MySQL client environment is correct, you can
start the MySQL command interpreter:
$ mysql dbname
-u appadmin -p
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.
INFORMIX provides two data types to store dates and time information:
MySQL provides the following data type to store dates:
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:
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".
SQL Statements using expressions with TODAY / CURRENT / EXTEND must be reviewed and adapted to the native syntax.
Literal DATETIME and INTERVAL expressions (i.e. DATETIME ( 1999-10-12) YEAR TO DAY) are not converted.
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.
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.
MySQL does not have an equivalent for the INFORMIX ROWID pseudo-column.
Solution:
ROWIDs are not supported. You must review the code using ROWIDs and use primary key columns instead.
INFORMIX uses the TEXT and BYTE data types to store very large texts or images. MySQL provides TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB data types.
Solution:
Starting with MySQL version 5.0, the database interface can convert BDL TEXT data to LONGTEXT and BYTE data to LONG BLOB.
Genero TEXT/BYTE program variables have a limit of 2 gigabytes, make sure that the large object data does not exceed this limit.
Because MySQL CHAR and VARCHAR cannot exceed 255 bytes, we recommend that you use the MySQL TEXT type to store CHAR/VARCHAR values with a size larger than 255 bytes. When fetching TEXT columns from a MySQL database, these will be treated as CHAR/VARCHAR types by the MySQL database driver. See CHAR/VARCHAR types for more details.
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 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 supports the AUTO_INCREMENT column definition option as well as the SERIAL keyword:
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. However, SQLCA.SQLERRD[2] is defined as an INTEGER, it cannot hold values from BIGINT auto incremented columns. If you are using BIGINT auto incremented columns, you must use the LAST_INSERT_ID() SQL function.
Note that AUTO_INCREMENT columns must be primary keys. This is handled automatically when you create a table in a BDL program.
Like Informix, MySQL allows to specify a zero for auto-incremented columns,
however, for SQL portability, INSERT statements should be reviewed to remove the SERIAL column from
the list.
For example, the following statement:
INSERT INTO tab (col1,col2) VALUES (0, p_value)
can 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
can be converted to :
INSERT INTO tab VALUES rec.* -- without
braces, serial column is removed
In INFORMIX SQL, outer tables can be defined in the FROM clause with the OUTER keyword:
SELECT ... FROM a, OUTER(b) WHERE a.key = b.akeySELECT ... 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.custnoSELECT ... 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:
For better SQL portability, you should use the ANSI outer join syntax instead of the old Informix OUTER syntax.
The MySQL interface can convert most INFORMIX OUTER specifications to ANSI outer joins.
Prerequisites:
Restrictions:
Notes:
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.
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:
Defaults:
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:
Defaults:
Solution:
The SET ISOLATION TO ... INFORMIX syntax is replaced by SET SESSION TRANSACTION ISOLATION LEVEL ... in MySQL. The next table shows the isolation level mappings done by the MySQL database driver:
| SET ISOLATION instruction in program | Native SQL command |
| SET ISOLATION TO DIRTY READ | SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED |
| SET ISOLATION TO COMMITTED READ [READ COMMITTED] [RETAIN UPDATE LOCKS] |
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED |
| SET ISOLATION TO CURSOR STABILITY | SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED |
| SET ISOLATION TO REPEATABLE READ | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ |
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.
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
MySQL locking mechanism depends upon the transaction manager. The default locking granularity is per table when you use the default non-transactional configuration. You must use the InnoDB Storage Engine to get transactions and locking mechanisms.
SELECT ... FOR UPDATE is only supported since MySQL version 6.0. Locks are released at the end of the transaction.
Solution:
Check if the MySQL storage engine supports SELECT FOR UPDATE, otherwise review the program logic.
INFORMIX and MySQL handle transactions in a similar manner.
INFORMIX native mode (non ANSI):
MySQL :
Solution:
INFORMIX transaction handling commands are automatically converted to MySQL instructions to start, validate or cancel transactions.
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.
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.
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.
MySQL supports the BOOLEAN data type and stores 1 or 0 integer values for TRUE and FALSE.
Solution:
The MySQL database interface supports the BOOLEAN data type.
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)
MySQL supports the following character data types:
With MySQL version 4, CHAR/VARCHAR with a size exceeding 255 characters are silently converted to TEXT columns. With later versions, you now get an SQL error when trying to define a CHAR or VARCHAR column with a size greater than the limit. Also, before version MySQL 5.0.3, VARCHAR limit was 255 characters, starting with 5.0.3 the limit is 65535 characters.
MySQL uses Character Length Semantics to define the size of CHAR/VARCHAR columns, while INFORMIX and Genero use Byte Length Semantics.
MySQL can support multiple character sets, you can run the SHOW CHARACTER SET statement to list supported encodings. There are different configuration levels to define the character set used by MySQL to store data. The server character set defines the default for database character sets if not specified in the CREATE DATABASE command. You can even define a specific character set at the table and column level, but this is not recommended with Genero applications. The database character set is used to store CHAR and VARCHAR columns. The NCHAR and NATIONAL VARCHAR types use a predefined character which can be different from the database character set. In MySQL the national character set is UTF-8.
MySQL can automatically convert from/to the client and server characters sets. In the client applications, you define the character set with the SET NAMES instruction.
Note that by default, when fetching CHAR columns from MySQL, trailing blanks are trimmed. This does not matter as long as you fetch CHAR columns into CHAR variables, but this non-standard behavior will impact CHAR fetch into VARCHAR, or other SQL areas such as string concatenation for example. You can control the behavior of CHAR trailing blanks trimming with the PAD_CHAR_TO_FULL_LENGTH sql-mode parameter. But when this mode is used, the result of the SQL LENGTH() function will be different since trailing blanks are significant for that function in MySQL.
Solution:
INFORMIX CHAR(N) types must be mapped to MySQL CHAR(N) types. INFORMIX VARCHAR(N) or LVARCHAR(N) columns must be mapped to MySQL VARCHAR(N).
Review your database schema when using CHAR, VARCHAR and LVARCHAR columns with a size exceeding the MySQL limits: If you need to store character strings larger as the MySQL limit, you can use the MySQL text type. When using CHAR/VARCHAR/LVARCHAR types in CREATE TABLE, the SQL Translator converts to text if the size is bigger as 255. When fetching tinytext and text columns from the MySQL database, the MySQL database driver treats such columns as CHAR/VARCHAR, to emulate INFORMIX CHAR columns with a size greater than 255.
Note: For each text column fetched from MySQL, the MySQL database driver needs to allocate a temporary string buffer of 65535 bytes. Keep in mind that text columns may have be created by CREATE TABLE using CHAR/VARCHAR/LVARCHAR with size>255. The memory used by this temporary buffer is freed when freeing the cursor.
You can store single-byte or multi-byte character strings in MySQL CHAR, VARCHAR and TEXT columns.
Do not forget to properly define the client character set, which must correspond to the runtime system character set.
Keep in mind that MySQL uses Character Length Semantics regarding CHAR/VARCHAR sizes: When you define a CHAR(20) and the database character set is multi-byte, the MySQL column can hold more bytes/characters as the INFORMIX CHAR(20) type. For example, in UTF-8, you can store 20 é (e-acute) characters in MySQL CHAR(20), but Informix CHAR(20) can only store 10 of such characters, because in UTF-8, é is encoded with 2 bytes. Even if Genero uses Byte Length Semantics when you define a CHAR/VARCHAR variable, a good practice is to use the same sizes for MySQL CHAR/VARCHAR columns: You could then store more characters in the MySQL columns as the Genero variable can hold, but this is not a problem.
When extracting a database schema from a MySQL database, the schema extractor uses the size of the column in characters, not the octet length. If you have created a CHAR(10 (characters) ) column a in MySQL database using the UTF8 character set, the .sch file will get a size of 10 (bytes).
See also the section about Localization.
Constraint naming syntax:
Both INFORMIX and MySQL support primary key, unique, foreign key and default, but the constraint naming syntax is different : MySQL expects the "CONSTRAINT" keyword before the constraint specification and INFORMIX expects it after.
UNIQUE constraint example:
| INFORMIX | MySQL |
| CREATE TABLE scott.emp ( ... empcode CHAR(10) UNIQUE [CONSTRAINT pk_emp], ... |
CREATE TABLE scott.emp ( ... empcode CHAR(10) [CONSTRAINT pk_emp] UNIQUE, ... |
Primary keys:
Like INFORMIX, MySQL creates an index to enforce PRIMARY KEY constraints (some RDBMS do not create indexes for constraints). Using "CREATE UNIQUE INDEX" to define unique constraints is obsolete (use primary keys or a secondary key instead).
In MySQL, the name of a PRIMARY KEY is PRIMARY.
Unique constraints:
Like INFORMIX, MySQL creates an index to enforce UNIQUE constraints (some RDBMS do not create indexes for constraints).
When using a unique constraint, INFORMIX allows only one row with a NULL value, while MySQL allows several rows with NULL! Using CREATE UNIQUE INDEX is obsolete.
Foreign keys:
Both INFORMIX and MySQL support the ON DELETE CASCADE option. In MySQL, foreign key constraints are checked immediately, so NO ACTION and RESTRICT are the same.
Check constraints:
Check constraints are not yet supported in MySQL.
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 MySQL.
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.
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.
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:
CREATE
TEMPORARY TABLE tablename ( coldefs )
CREATE TEMPORARY TABLE tablename LIKE other-table
Solution:
In BDL, INFORMIX temporary tables instructions are converted to generate native SQL Server temporary tables.
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).
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 ...
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))
INFORMIX uses the following form to identify a SQL object:
[database[@dbservername]:][{owner|"owner"}.]identifier
With MySQL, an object name takes the following form:
[database.]identifier
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.
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'
INFORMIX supports several data types to store numbers:
| INFORMIX Data Type | Description |
| SMALLINT | 16 bit signed integer |
| INT / INTEGER | 32 bit signed integer |
| BIGINT | 64 bit signed integer |
| INT8 | 64 bit signed integer (replaced by BIGINT) |
| DEC / DECIMAL | Equivalent to DECIMAL(16) |
| DEC(p) / DECIMAL(p) | Floating-point decimal number |
| DEC(p,s) / DECIMAL(p,s) | Fixed-point decimal number |
| MONEY | Equivalent to DECIMAL(16,2) |
| MONEY(p) | Equivalent to DECIMAL(p,2) |
| MONEY(p,s) | Equivalent to DECIMAL(p,s) |
| REAL / SMALLFLOAT | 32-bit floating point decimal (C float) |
| DOUBLE PRECISION / FLOAT[(n)] | 64-bit floating point decimal (C double) |
Solution:
MySQL supports the following data types to store numbers:
| MySQL data type | Description |
| DECIMAL(p) | Stores whole numeric numbers up to p digits (not floating point) |
| DECIMAL(p,s) | Maximum precision depends on MySQL Version, see documentation. |
| FLOAT[(M,D)] | 4 bytes variable precision |
| DOUBLE[(M,D)] | 8 bytes variable precision |
| SMALLINT | 16 bit signed integer |
| INTEGER | 32 bit signed integer |
| BIGINT | 64 bit signed integer |
Note: Before MySQL 5.0.3, the maximum range of DECIMAL values is the same as for DOUBLE. Since MySQL 5.0.3, DECIMAL can store real precision numbers as in INFORMIX. However, the maximum number of digits depends on the version of MySQL, see documentation for more details. We strongly recommend that you make tests (INSERT+SELECT) to check whether large decimals are properly inserted and fetched back.
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:
Solution:
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 MySQL:
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.
MySQL does not support insert cursors.
Solution:
Insert cursors are emulated by the MySQL database interface.
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.
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.
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; in the same conditions, this does not normally appear with INFORMIX. Review the program logic in order to find another way to set locks.
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:
WHERE CURRENT OF is not supported by MySQL; review your code for occurrences.
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 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 an INTERVAL literal as follows :
25 years 2 months 23 days
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.
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.
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.
Solution:
LOAD and UNLOAD instructions are supported.
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
Solution:
SQL Interruption is not supported with MySQL; review your code for occurrences.
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.*
MySQL 6.0 does not support native scrollable cursors.
Solution:
The MySQL database driver emulates scrollable cursors with temporary files. On UNIX, the temp files are created in the directory defined by the DBTEMP, TMPDIR, TEMP or TMP environment variables (the default is /tmp). On Windows, the temp files are created with the _tempnam() MSVCRT API call.
Native scrollable cursors are normally handled by the database server (only pieces of the result-set are sent to the client application). With emulated scrollable cursors, when scrolling to the last row, all rows will be fetched into the temporary file. This can generate a lot of network traffic and can produce a large temporary file if the result-set contains a lot of rows. Additionally, programs are dependent on the file system resource allocated to the OS user (ulimit).
In case of a runtime system crash, the temporary files created for scrollable cursors are not removed automatically. Therefore, is it possible that you will find some unexpected files in the temp directory. Before removing such files, you must make sure that these files are no longer used by running processes. Recent operating systems take care of that, by removing unused temp files periodically.
| INFORMIX Data Types | MySQL Data Types |
| CHAR(n) | CHAR(n) (n>255c => TEXT(n)) |
| VARCHAR(n[,m]) | VARCHAR(n) (n>255c => TEXT(n)) |
| LVARCHAR(n) | VARCHAR(n) (n>255c => TEXT(n)) |
| NCHAR(n) | NCHAR(n) (n>255c => TEXT(n)) |
| NVARCHAR(n[,m]) | NVARCHAR(n) (n>255c => TEXT(n)) |
| BOOLEAN | BOOLEAN |
| SMALLINT | SMALLINT |
| INT / INTEGER | INTEGER |
| BIGINT | BIGINT |
| INT8 | BIGINT |
| SERIAL[(start)] | INTEGER (see note) |
| BIGSERIAL[(start)] | BIGINT (see note) |
| SERIAL8[(start)] | BIGINT (see note) |
| DOUBLE PRECISION / FLOAT[(n)] | DOUBLE |
| REAL / SMALLFLOAT | FLOAT |
| NUMERIC / DEC / DECIMAL(p,s) | DECIMAL(p,s) |
| NUMERIC / DEC / DECIMAL(p) with p<=15 | DECIMAL(p*2,p) |
| NUMERIC / DEC / DECIMAL(p) with >15 | N/A |
| NUMERIC / DEC / DECIMAL | DECIMAL(32,16) (unsupported!) |
| MONEY(p,s) | DECIMAL(p,s) |
| MONEY(p) | DECIMAL(p,2) |
| MONEY | DECIMAL(16,2) |
| DATE | DATE |
| DATETIME HOUR TO SECOND | TIME |
| DATETIME q1 TO q2 (different from above) | DATETIME (YYY-MM-DD hh:mm:ss) |
| INTERVAL q1 TO q2 | CHAR(50) |
| TEXT | MEDIUMTEXT / LONGTEXT (using <= 2Gb!) |
| BYTE | MEDIUMBLOB / LONGBLOB (using <= 2Gb!) |