mySQL the well known open DBMS is now available as a Genexus application platform. Java, C#, VB, VFP and C/SQL will support this DBMS in Yi version. mySQL is a full featured DBMS and
GeneXus fully supports it (no known limitation exists, at the time of this writing, that makes any Genexus feature not to be implemented on mySQL). mySQL applications can have:
- Transactional integrity
- Row level locking
- Referential integrity
- Optimized reorganizations (using alter table add/drop/change column)
- Blob datatype
mySQL Engines
mySQL supports different engines to manage data store and indexes. The engine Genexus uses is
InnoDB. The option is generated at the moment of table reorganization/creation. The most important reason we chose InnoDB is row level locking support. In version 3.23 in Linux, InnoDB is disabled by default. To enable it, the minimum required setting is innodb_data_file_path=ibdata1:10M:autoextend in the file /etc/my.cnf. There are other parameters, but this is enough to enable it. Without this setting transactional integrity doesn't work. From 4.0 version and on, InnoDB is enabled by default. More info
here.
Versions and requirements
Tests were made using version 3.23.58 of mySQL, and also version 4.0. Version 3.23 is the minor version we know it works correctly with GX. We have tested it in Java using the MySQL Connector/J JDBC driver, available for download in mySQL download page. With JDBC driver 3.0, you need the SUN virtual machine (SUN JDK 1.4 or higher is required). If you use 2.0 version, you can also use Microsoft virtual machine.
Downloads
Server :
download (version 4.0, Production Release)
Graphical administration tools:
You can download some of them in mySQL web site
here
MySQL Connector/J JDBC driver download:
Sun JDK 1.4.2
here
Genexus Configuration
Java generator: Just select 'mySQL' in the 'Model Properties' dialog. Then in 'DBMS Options' dialog, JDBC, select 'mySQL JDBC Driver (Type 4)'. Configure the properties for database name, server, user and password, and then configure the 'Execution Options', including the JDBC driver ( mysql-connector-java-3.0.11-stable-bin.jar in the case of the 3.0 version ) in 'Classpath'. You must select 'Sun SDK' for 'Platform'.
To use NLS (national language support), use a custom URL in the DBMS Options in order to set some additional connection properties:
Example: "jdbc:mysql://myserver:3306/mydatabase?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8"
Nullvalue
The GX nullvalue for dates datatype is '1000-01-01', and for datetimes datatype is '1000-01-01 00:00:00' (they are the minimum values that mySQL can store for each datatype).
Joins
The default join type is Outer.
Mapping between GX and mySQL datatypes
| GX datatype |
|
mySQL datatype |
| |
| N(X) |
X<5 |
smallint |
| N(X) |
X>=5 X<7 |
mediumint |
| N(X) |
X>=7 X<10 |
int |
| N(X) |
X>=10 |
bigint |
| N(X,Y) |
|
numeric(X,Y) |
| CHAR(X) |
X<=255 |
(national)char |
| CHAR(X) |
X>255 |
(national)mediumtext |
| VCHAR(X) |
X<=255 |
(national)varchar |
| VCHAR(X) |
X>255 |
(national)mediumtext |
| LVCHAR(X) |
X<=255 |
(national)char |
| LVCHAR(X) |
X>255 |
(national)mediumtext |
| DATE |
|
date |
| DATETIME |
|
datetime |
national - This option is used when the DBMS option 'Enable national language support' is set to 'Yes'.
mySQL limits
| Maximum Table name length |
64 |
| Maximum Index name length |
64 |
| Maximum Column name length |
64 |
Known errors and current limitations
- The Start and Step properties of autonumber attributes are not working
- National Language Support is not working