|
[MYSQL] |
Open MySQL/MariaDB Database Link |
OPEN (chan [, fileopt] [, OPT=params])"[MYSQL]database [ ; [ table ] [ ;params ]]"
|
File tag clause to inform PxPlus that it will be opening a direct link to a MySQL/MariaDB database server (not a PxPlus data file). Note: | |||||||
|
chan |
Channel or logical file number to open. | ||||||
|
database |
Name of the database to open (string expression). | ||||||
|
fileopt |
File options. Supported options include:
| ||||||
|
params |
Open parameters (see OPT= Parameters). All parameters are semi-colon separated and can be contained in the OPEN pathname and/or the OPT= string. | ||||||
|
table |
Name of the table to open. If the table name is not supplied, either SQL statements sent to the database must be created by the application (see Using SQL Directly Within PxPlus) or table and/or column information must be retrieved (see Retrieving DB2/ODB Table and Column Information). |
The [MYSQL] tag is used as a prefix in an OPEN statement to denote that PxPlus is to route all file I/O requests to an external MySQL/MariaDB database server. Once you open a channel for [MYSQL] use, you can use it just like any other channel (i.e. for file I/O). It remains open until you close it.
There are two basic modes of operation when using the MySQL/MariaDB connection to a database - Emulation and Direct. Emulation mode is used when a table name is provided in the OPEN command. Direct mode is chosen when no table name is provided.
In Emulation mode, the MySQL/MariaDB interface will map all standard READ, WRITE and REMOVE directives, along with KEY functions, into SQL commands for the specified table. In this mode, the MySQL/MariaDB table will emulate a normal PxPlus Keyed or Indexed file. This mode allows most existing applications to use MySQL/MariaDB tables in lieu of native PxPlus files. In Emulation mode, PxPlus will not pass NULL fields to the database but instead use strings with no characters.
In Direct mode, the application is responsible for creating and submitting SQL commands directly to the database. The WRITE RECORD directive is used to send SQL commands to the MySQL/MariaDB server. The READ or READ RECORD directives can be used to return the results (if any). The results of any SQL command will be returned as a logical PxPlus data record with field separators (SEP) between each field.
(The native MySQL interface was added in PxPlus v7.00.)
The OPEN parameters for connecting to a MySQL/MariaDB server are listed below. Most of the parameters can also be specified in the PxPlus INI file in the "[MySql]" section.
|
BUSYRETRY=Y | N |
If set to N (or 0), the system will not retry SQL commands that return data busy statuses. Normally, you would set this to N if your MySQL/MariaDB server automatically waits for busy records. Default is Y - retry requests that return Data busy statuses. |
|
CURSORCLOSE=Y | N |
Setting this to Y will force the system to close all "open" statements whenever a Commit is issued. |
|
Date format mask applying to all date fields in table. This can be a combination of Y M D with any other characters. Example: To convert dates to 4-character year, month and day: DATEFMT=YYYYMMDD Other characters are inserted as is. DATEFMT=YY/MM/DD with a date of March 1, 2004 would be returned as 04/03/01. | |
|
DATE_CONVERT=Y | N |
If set to Y, the system will convert database columns with DATE format to the standard system date format as opposed to using the native ASCII date format used by MySQL/MariaDB. Default is Y. |
|
DATETIME_CONVERT=Y | N |
If set to Y, the system will convert database columns with DATETIME format to the standard system date format as opposed to using the native ASCII date format used by MySQL/MariaDB. Default is Y. |
|
DEBUG=Y | N |
String to append to SQL statement along with program name and line number for debugging purposes. This must indicate the comment character(s) appropriate to MySQL/MariaDB. |
|
EXTROPT=xxxxx |
Controls the format of the SELECT statement used to process an EXTRACT. By default, PxPlus will generate the lock clause based on the setting of the 'XI' system parameter. If XI is On, the system generates: SELECT * FROM table LOCK IN SHARE MODE WHERE... If XI is Off, the system generates: SELECT * FROM table FOR UPDATE WHERE... When EXTROPT=text, then text is substituted in place of FOR UPDATE/LOCK IN SHARE MODE. In addition, if the first character of text is $, then the remaining characters of text are placed at the end of the SELECT statement rather than after the filename. This allows for different variations of SQL to be supported. |
|
IND=fld |
Identifies a column that contains a sequential number starting at 0. This is used to emulate an indexed file. |
|
IOPROG=xxxx |
Specifies the name of an embedded IO program to use with this connection. |
|
KEY=fld, fld, fld, .. |
Identifies fields that make up the key(s). For named keys, enter *NAME:keyname. OPEN(chan)"[MYSQL]dsn;table;KEY=field,field,*NAME:keyname" Use the :D option to indicate that the key segment is to be sorted in descending order. KEY=KeyFld1,KeyFld2:D,KeyFld3 |
|
KEYDATA=fld |
Identifies a column that represents the key. This is used to emulate an external key where the data is not duplicated in the data. |
|
Reformats the contents of a date column to and from the Sage MAS 90/Sage MAS 200 date format. | |
|
NONUMADJ=Y | N |
Set to Y (or 1) tells the system not to reserve additional space in internal buffers for the sign and decimal points of numeric data. |
|
NOSTRIP |
Keeps trailing spaces. (Default) |
|
NULLPADKEY= Y | N |
Set to Y (or 1) to force keys to be padded to full length with the null character, $00$. |
|
PORT=nnnn |
Identifies the port number on the server that will be used to connect to. If not specified, the MySQL/MariaDB default port number will be used. |
|
PREFETCH=Y | N |
Controls the fetching of data from the server. If set to N (default), data records "selected" from the database will be returned as read. When set to Y (or 1) and more than one record has been selected (read next), the data will be fetched from the server in advance of the actual READ directive and cached on the workstation thereby improving performance. |
|
PREPARE=Y | N |
Set to Y (or 1) to use prepared statements. Prepared statements are pre-compiled SQL that may improve performance. |
|
PSWD=xxx or |
Specifies the password for the user when connecting to the database server. Warning!
|
|
REC=fld, fld, fld |
Provides the column names, type and size. This is typically done to improve performance. If this information is not provided, then PxPlus must query the database for this information. |
|
RECDATA=fld |
Identifies a column to return as the full record. This can be used for variant records, which use complex rules to identify the record type. |
|
SERVER=xxx or |
Identifies the IP address or name of the database server to connect to. |
|
SHARED |
Sets all tables to share a single connection to the database server. (Default) |
|
Enables verification that the SSL/TLS certificate came from a trusted vendor, ensuring the certificate's hostname matches, and that it has not expired. By default, SSL/TLS certificates are not verified. Enable this option only when connecting to a database using SSL/TLS. This option does not work with self-signed certificates. (The SSLVERIFY option was added in PxPlus 2024 Update 1.) | |
|
STDDATE |
Overrides the date formatting on individual columns. |
|
STRIP |
Removes trailing spaces from field. |
|
Controls how SELECT statements that retrieve single records will be generated. Due to an error in the processing of the SELECT statement on some MySQL/MariaDB servers to retrieve the proper first row of a SELECT, a sub-query had to be used. Current versions of MySQL/MariaDB properly support the LIMIT n option. When omitted or set to N (Default), the LIMIT n option is used to restrict the data returned to a single record. When set to Y (or 1), a sub-query is constructed and used. | |
|
TEXTMAX=nnn |
Overrides maximum size for text fields. (Default is 8192 bytes.) |
|
TIMESTAMP_CONVERT=Y | N |
If set to Y, the system will convert database columns with TIMESTAMP format to the standard system date format as opposed to using the native ASCII date format used by MySQL/MariaDB. Default is N. |
|
TOP=n |
Specifies use of the TOP clause in SELECT statements (limits the number of rows to return in a result set). If TOP=n is non-zero, then the KEF( ) / KEL( ) functions issue a "SELECT ... LIMIT 1" SQL statement, which improves system performance. If TOP=n > 0, then the system will issue a "SELECT ... LIMIT n" to reduce the data transferred. TOP=-1 indicates the driver supports SELECT ... LIMIT option, but normal reading should not use it. Default is 0 (not supported). |
|
TYP=xxxx |
Sets identifier for different variant records. See ODB/OCI/DB2 Record Processing. |
|
UNIQUE |
Sets the OPEN to be on a unique connection to the database server. |
|
USER=xxxx |
Specifies login name. |
|
Set to 1, Y or y to validate the data. This option can be included in the respective INI section by database type. Date validation for database connections will only be included if the database OPEN specifies a date format using the DATEFMT= or MAS90DATE option. For additional data validation information, see IOLIST directive. (The VALIDATE option was added in PxPlus 2017.) | |
|
If set to Y (or 1), the system will internally issue a WAIT 0 when doing long SELECT directives. This will allow a graphical display to update periodically. Default is Y. |
Use of Global String Variables as Option Values
In database connection options, you can provide the value following the = (equals sign) in global string variables. When opening the connection to the database, the system will check to see if the value following the = (equals sign) starts with a % and ends with a $ and is the name of a valid global string variable. If so, the value is replaced with the contents of that global variable.
For example, the option USER=%UserName$ would be dynamically changed by the EXE on the OPEN to replace the text %UserName$ with whatever is in that global variable.
(Support to allow the use of global variables for defining database Link/Prefix files was added in PxPlus 2023 Update 1.)
READ Read Data from File
READ RECORD Read Record from File
SELECT Select/Query From ... Where
WRITE Add/Update Data in File
WRITE RECORD Write Record
OPEN Open a File for Processing
IOLIST Specify Variable List
[ADO] Microsoft SQL Server Interface
[DB2] DB2 Support
[OCI] Connect to Oracle Server
[ODB] Open Database
Database Export Utility