ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Native Connectors/Native Accesses / Native MySQL Connector
  • Overview
  • Main remarks
  • Required versions
  • Using WLanguage commands in SQL queries
  • Limitations of MySQL version 3.23x
  • Managing large files
  • INSERT queries and automatic identifier
  • Creating a table with HCreation
  • Support for Unicode
  • Connection with SSL certificates
  • Main specific features
  • Locking records
  • Identical records
  • Transactions
  • SET or ENUM items
  • xBlob items
  • Array items
  • Using INSERT/UPDATE queries to insert text into a LONGBLOB item (binary memo) on MySQL
  • Partial indexing of memos and full-text indexing
  • Specific features regarding HFSQL functions and SQL functions
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Native MySQL Connector: Specific features and remarks
Native Connectors (Native Accesses)Available only with this kind of connection
Overview
The following paragraphs present the main aspects and specific features of the Native MySQL Connector.
Main remarks

Required versions

Caution: This version of the Native MySQL Connector operates with:
  • WINDEV 27 or WEBDEV 27.
  • Client layer of MySQL (LibMySQL.DLL file):
    • 32-bit client layer to use the 32-bit Native MySQL Connector (version 3.x to 5.x checked).
    • 64-bit client layer to use the 64-bit Native MySQL Connector (version 5.x checked).

Using WLanguage commands in SQL queries

WLanguage commands cannot be used in SQL queries.

Limitations of MySQL version 3.23x

The MySQL database version 3.23x does not support:
  • the Partial Fetch. The use of Partial Fetch with the SQL functions of WLanguage has the same effect as if no Partial Fetch was used.
  • the Unions.

Managing large files

When handling large files, all the records are loaded in memory. To reduce memory usage, it is recommended to:
  • use a filter (HFilter) before initializing a browse (HReadFirst / HReadLast, ...)
  • use queries whose selection conditions are selective enough
  • avoid performing searches.

INSERT queries and automatic identifier

The automatic identifiers are not supported by the INSERT queries (created through programming or in the query editor). To use an automatic identifier, call HAdd on the table.

Creating a table with HCreation

The HCreation function creates tables of type "innoDB" to allow the management of blocks and transactions. See Native MySQL Connector: Specific features for more information.

Support for Unicode

When Unicode is supported on the connection (default mode), the text items of the queries run with the hQueryWithoutCorrection constant are in Unicode format. If a Unicode string is assigned to an Ansi string, the conversion is automatically performed by WINDEV and WEBDEV by using the character set defined with ChangeCharset.
If the connection is ANSI ("WD Unicode Support = 0" in the extended information specified by HDescribeConnection or HOpenConnection), the text items of the queries run with the hQueryWithoutCorrection constant are in ANSI format.
For more details on Unicode or ANSI connections, see Programming a MySQL database with the HFSQL functions.

Connection with SSL certificates

To establish a connection with SSL certificate, you must:
  1. Install a version of the MySQL server that supports the SSL connections. For more details, see the MySQL documentation.
  2. Create the necessary certificates.
  3. Establish a connection by using the following keywords in the advanced information of the connection :
    KeywordUse
    SSL KeyPath of key file.
    SSL CertPath of certificate file.
    SSL CAPath of certification authority file.
    SSL CAPathPath of the directory containing the certificates of the recognized SLL certification authorities, in PEM format.
    SSL CipherList of authorized ciphers.
Example of SSL connection on the CRM database of the mysqlserver.domain.fr server:
MaConnexion is Connection
 
// Description de la connexion
MaConnexion.User = "root"
MaConnexion.MotDePasse = "secret"
MaConnexion.Server = "serveurmysql.domaine.fr"
MaConnexion.Database = "CRM"
MaConnexion.Provider = hNativeAccessMySQL
MaConnexion.Access = hOReadWrite
 
// Description des informations étendues pour une connexion SSL
MaConnexion.ExtendedInfo = [
SSL KEY = E:\MySSLCertificates\mysql.key;
SSL CERT = E:\MySSLCertificates\mysql_cert.pem;
SSL CA = E:\MySSLCertificates\ca-cert.pem;
SSL CAPATH = E:\MySSLCertificates\CA\
]
 
// Ouverture de la connexion
HOpenConnection(MaConnexion)
Remark: These parameters correspond to the parameters of the mysql_ssl_set function of the C API for accessing MySQL.
For more details, see Optional connection information.
Main specific features

Locking records

The lock options (hLockWrite and hLockReadWrite constants) that can be used with HFSQL functions are not supported in this version.

Identical records

The Native MySQL Connector cannot differentiate between two identical records (two records with identical values for all the items, excluding memo items).
When a record is modified or deleted, all the identical records will be modified or deleted.

Transactions

To manage the transactions, you must use a type of MySQL table that supports the BEGIN, COMMIT and ROLLBACK commands.

SET or ENUM items

The SET and ENUM items are automatically processed and imported as text memo in WINDEV and WEBDEV. Indeed, the SET and ENUM items have no equivalence in HFSQL. They contain numeric values (subscripts) that correspond to "text" values. These are the "text" values that are imported.

xBlob items

The xBlob items in MySQL are automatically processed and imported as text memo in WINDEV or WEBDEV:
  • the BLOB items are imported as binary memo items.
  • the LONGTEXT items are imported as text memo items.

Array items

The array items are not supported by the Native Connector. If you are using an array item, only the value of subscript 1 in the array will be handled by the HFSQL functions (in read/write).

Using INSERT/UPDATE queries to insert text into a LONGBLOB item (binary memo) on MySQL

To allow the management of Unicode items, the connections of the Native MySQL Connector are opened in UTF8: all the requests and the data are exchanged in UTF8 with the server.
The server converts the request and the values into the charset corresponding to each column, except for a LONGBLOB item (binary memo): in this case, the value is inserted as it is.
For a LONGBLOB item:
  • if the connection is in ANSI format, the inserted text value is in ANSI format.
  • if the connection is in UTF8 format, the inserted text value is in UTF8 format; in this case, a compatibility problem may occur.
To avoid this conversion problem in UTF8, you can:

Partial indexing of memos and full-text indexing

The partial indexing of memos as well as the full-text indexing are not taken into account:
  • when importing MySQL tables into the analysis.
  • when HCreation is used.
If these features are defined in the analysis, they will be taken into account when running the SQL queries (with the hQueryWithoutCorrection constant) that use the SQL "CREATE TABLE" and "MATCH AGAINST" functions.
Specific features regarding HFSQL functions and SQL functions
The specific features for using the HFSQL and SQL functions with the Native MySQL Connector are as follows:
LimitationsAffected functions
Multi-file query (join):
  • HAdd, HModify and HDelete are not supported.
  • control Table based on a multi-file query (join): HAdd, HModify and HDelete are not supported.
HAdd
HModify
HDelete
The searches performed on some complex queries with HReadSeek* may fail in this version.
This problem will be fixed in a forthcoming version.
HReadSeek, HReadSeekFirst, HReadSeekLast
When using the Native MySQL Connector, you must specify the "DataBase" used.
If you are using HConnect, the "DataBase" must be specified in the additional information as follows:
"initial catalog = DataBaseName"
HConnect
When creating tables with HCreation, the table names must contain no accented character (Example: "ÉlanFile").
This is a problem specific to MySQL (tested on versions 3.23.52-max-nt and 3.23.47-max-nt of the MySQL server. Execution of the SQL command "DROP TABLE", performed by HCreation to delete the table if it already exists, can cause an unexpected shutdown of the MySQL server.
See Native MySQL Connector: Specific features for more information.
HCreation
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 09/05/2023

Send a report | Local help