Native MySQL Connector: Programming with HFSQL functions

WINDEV, WEBDEV AND WINDEV MOBILE
ONLINE HELP

Version:

Home | Sign in | English

Help

Overview

Importing the structure of data files

Programming

Tip in Linux

See also

Native MySQL Connector: Programming with HFSQL functions

Available only with this kind of connection

Overview

To use a MySQL database with the Native Connector (also called "Native Access"), the structure of MySQL tables must be imported into the WINDEV or WEBDEV analysis.

Differences compared to SQL programming: The queries are not required to handle the MySQL data. Indeed, the HFSQL functions can be directly used to read data, perform searches, implement filters, ... However, the structure of MySQL files must be imported into the WINDEV or WEBDEV analysis.

Remark: The queries run by HExecuteQuery and HExecuteSQLQuery are automatically "corrected" in order to be compatible with MySQL.

Remark: From version 19, HFSQL is the new name of HyperFileSQL.

Importing the structure of data files

The import steps

To perform this import:

Open the data model editor ("Project .. Load the analysis"). Start the data model editor: click among the quick access buttons.
In the data model editor, select "Structures of files .. Import the descriptions of files/tables".On the "Analysis" pane, in the "Creation" group, expand "Import" and select "Import the descriptions of files/tables".
The wizard starts. A connection to the MySQL database is automatically established and associated with the data files imported into the analysis. This connection will have to be used to handle the MySQL data file by programming.

In the different wizard screens, choose the following options:

Data source: External database.
The data in current format will be used by the application.
Select "Native Access for MySQL", the data source, the user name and password. The "Properties" button is used to access the system properties.
When the connection is established, all you have to do is select the tables that will be imported into the WINDEV or WEBDEV analysis. These tables can be handled by programming with the HFSQL functions of WLanguage. These tables will be displayed in blue in the data model editor.

In the different wizard screens, choose the following options:

Type of database: MySQL.
Characteristics of the connection: Data server, User and password, database.
The application will access the data in the current format.
When the connection is established, all you have to do is select the tables that will be imported into the WINDEV or WEBDEV analysis. These tables can be handled by programming with the HFSQL functions of WLanguage. These tables will be displayed in blue in the data model editor.

Taking into account the evolutions of the MySQL database

To take into account the evolutions of tables imported into the data model editor, select "Structure of files .. Synchronize the analysis with the external databases". To take into account the evolutions of the tables imported into the data model editor, on the "Analysis" pane, in the "Analysis" group, expand "Synchronization" and select "Update the analysis from the external databases".

A wizard starts, allowing you to:

analyze the differences for the imported tables,
analyze the differences for all the MySQL tables.

Programming

1. Establishing the connection

To establish the connection to the MySQL database, call HOpenConnection and specify the name of the connection to open. The connection name was defined when importing the structure of files into the analysis.

Caution: If you are using another syntax, the name of the database to use must be specified.

To modify some parameters of this connection (user name or password for example), call HConnect.

Remarks:

If you try to read the file directly (HReadFirst, ...), the connection associated with the file description in the analysis will be automatically opened.
You have the ability to set the "Server Port", "Unix Socket" and "Client Flag" parameters for the connection to the MySQL server. To do so, add the corresponding strings into the extended information of the connection (HDescribeConnection or HOpenConnection).
Example 1: "Server Port=3306;" to connect on port 3306 of the MySQL server.
Example 2: "Server Port=3306; Client Flag=0;"
...
See the documentation about MySQL (keyword = 'mysql_real_connect') for more details.
The management mode of the Unicode items can be configured during the connection. To do so, add "WD Unicode Support" to the extended information of the connection (HDescribeConnection or HOpenConnection).
- If "WD Unicode Support = 0", the management of the Unicode format is disabled for the connection. All the values will be converted (if necessary) by the server into the default character set. In this case, the performance is improved but the Unicode items are not supported.
- If "WD Unicode Support = 1" (default value), the management of the Unicode format is enabled for the connection. All the exchanges with the server will be performed in Unicode. The possible conversions are managed by the Native Connector.
The size of the cache can be specified in the optional information of the connection with the following keyword "WD Cache Size". In this case, the parameter specified in ..CtCacheSize is ignored.
Example:

"WD Cache Size = 10000; Server Port=3306;"

2. Running queries

HExecuteQuery and HExecuteSQLQuery are used to run queries on the current database.

HExecuteQuery: is used to run a preset query with the query editor.
HExecuteSQLQuery: is used to run a query by specifying the SQL code of query and the name that will be assigned to this SQL code.

// Initialize the "Customer_84" query
HExecuteQuery(CUSTOMER_84, "MYSQLDatabase", hQueryWithoutCorrection)
HExecuteSQLQuery(CustomerQry, "MYSQLDatabase", hQueryWithoutCorrection, ...
"SELECT NAME FROM CUSTOMER")

3. Retrieving the result

The result is browsed by the standard read functions: HReadFirst, HReadNext, HReadSeek, ...

In this version, the records cannot be locked by the lock options of these functions. Only the locks performed by SQLLock are supported (see Native MySQL Connector: managing locks and transactions).

4. Closing the connection

HCloseConnection is used to close the connection to the database when all the necessary queries have been run.

Tip in Linux

The errors not being displayed in windows or pages in Linux, the errors must be returned to the console by using dbgStandardOutput.

For example:

IF NOT HOpenConnection(My_Connection) THEN
dbgStandardOutput(HErrorInfo(hErrFullDetails))
END