SQL

The following functions enable access to remote MYSQL databases:

Functions	Description
mysqlConnect	connect to a MYSQL database
mysqlQuery	run a SQL query (query passed as a string)
mysqlQueryTXB	run a SQL query (query passed as a text buffer)
mysqlSecure	set secure transport options for next SQL connection

The following functions enable access to local SQLITE databases:

Functions	Description
sqliteConnect	open a SQLITE database
sqliteQuery	run a SQL query (query passed as a string)
sqliteQueryTXB	run a SQL query (query passed as a text buffer)

Attention

SQL management may not be available on some targets. Please refer to OEM instructions for further details about available features.

For the usage of MYSQL function blocks the library libmysql.dll (MySQL Connector/C for Windows x86/32 bit) has to be downloaded from https://downloads.mysql.com and manually installed into the installation folder.

Connect to a MYSQL database

Inst_mysqlConnect (EN, HOST, USER, PASS, PORT, DB);

outDb := Inst_mysqlConnect.HDB;

outErrCode := Inst_mysqlConnect.Err;

outErrText := Inst_mysqlConnect.ErrS;

Inputs

Input	Data type	Description
EN	BOOL	Connect to or disconnect from a database (see notes).
HOST	STRING	URL of a MYSQL server.
USER	STRING	User name for login.
PASS	STRING	Password name for login.
PORT	DINT	Ethernet port number.
DB	STRING	Name of the database.

Outputs

Output	Data type	Description
HDB	DINT	Handle of the connection or 0 if not connected.
ERR	DINT	Code of the last SQL error or 0 if OK.
ERRS	STRING	Description of the last error.
STATE	STRING	Description of the last MySQL state report code.

Notes:

The connection is established on a rising edge of the EN input. It is disconnected on a falling edge of EN.

All string variables are limited to 255 characters. Any character may be entered. Use the ‘$’ prefix for special characters, such as explained in the Online-Help. Refer to MYSQL documentation for a description of error codes

Set secure transport options for next MYSQL connection

OK := mysqlSecure (KEY, CERT, CA, CAPATH, CIPHER);

Inputs

Input	Data type	Description
KEY	STRING	Pathname of the key file.
CERT	STRING	Pathname of the certificate file.
CA	STRING	Pathname of the certificate authority file.
CAPATH	STRING	Pathname to a directory that contains trusted SSL CA certificates in PEM format.
CIPHER	STRING	List of permissible ciphers to use for SSL encryption.

Outputs

Output	Data type	Description
OK	BOOL	TRUE if successful.

Notes:

All string variables are limited to 255 characters.

Open a SQLITE database

Inst_sqliteDatabase (EN, PATH);

outDb := Inst_sqliteDatabase.HDB;

outErrCode := Inst_sqliteDatabase.Err;

outErrText := Inst_sqliteDatabase.ErrS;

Inputs

Input	Data type	Description
EN	BOOL	Open or close a database (see notes).
PATH	STRING	Pathname of the database file.

Outputs

Output	Data type	Description
HDB	DINT	Handle of the connection or 0 if not connected.
ERR	DINT	Code of the last SQL error.
ERRS	STRING	Description of the last error.
EXTERR	DINT	Extended SQLite error code.

Notes:

The database is opened on a rising edge of the EN input. It is created if not existing. It is closed on a falling edge of EN.

All strings are limited to 255 characters. If the file specified by PATH does not exist, then the database is open for creation.

Run a SQL query

Inst_Query (EN, HDB, QUERY, RESULT); // for mysqlQuery or sqliteQuery blocks

Inst_Query (EN, HDB, QUERYTXB, RESULT); // for mysqlQueryTXB or sqliteQueryTXB blocks

IsReady := Inst_Qyery.Ready;
NnuberOfRows := Inst_Qyery.NRow;
NnuberOfColumns := Inst_Qyery.NCol;
Overflow := Inst_Qyery.Over;

outErrCode := Inst_Query.Err;

outErrText := Inst_Query.ErrS;

Inputs

Input	Data type	Description
EN	BOOL	Trigger a query (see notes).
HDB	DINT	Handle of a database (from respectively mysqlConnect or sqlliteDatabase block).
QUERY	STRING	SQL query passed as a string.
QUERYTXB	STRING	Handle of a text buffer containing the SQL query.
RESULT	STRING	Array of strings filled on output with the result of the query.

Outputs

Output	Data type	Description
DONE	BOOL	TRUE during 1 cycle when the query is finished (either OK or with error).
NROW	DINT	Full number of rows returned by the query.
NCOL	DINT	Full number of columns returned by the query.
OVER	BOOL	TRUE if the RESULT array is too small, so some returned cells were lost.
ERR	DINT	Code of the last SQL error.
ERRS	STRING	Description of the last error.

More outputs for MySQL:

Output	Data type	Description
STATE	STRING	Description of the last MySQL state report code.

More outputs for SQLite:

Output	Data type	Description
EXTERR	DINT	Extended SQLite error code.

Notes:

The query is launched on a a rising edge of the EN input, if the READY output is TRUE. YOU CAN RUN AT MOST ONE QUERY AT A TIME. The end of the query is indicated by a TRUE state of DONE output (pulse). Possible error is reported on ERR and ERRS outputs.

If you know the number of columns expected, you can pass to RESULT a bi-dimensional array in form [rows, columns]. If you pass a single dimension array, then columns of the first row come first.

Error codes

Value	Meaning
0	No error
-1	Invalid handle of database
-2	System is busy. One query is already in execution, concurrent query impossible.
> 0	Error code from SQL server. Refer to MYSQL or SQLITE documentation.

SQL

IEC 61131-3 Automation platform > Programming - Reference guide > Advanced operations > SQL

Created with the Personal Edition of HelpNDoc: Generate Kindle eBooks with ease