Genexus mySQL manual

Unofficial Content
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

Another one: DBManager

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