Special File Command Tags

[MYSQL]

Open MySql Native Database Link

Format

OPEN (chan [, fileopt] [, OPT=params])"[MYSQL]database [ ; [ table ] [ ;params ]]" 

Where:

[MYSQL]

File tag clause to inform PxPlus that it will be opening a direct link to a MySql database server (not a PxPlus data file).

Note:
The square brackets around MYSQL are mandatory.

chan

Channel or logical file number to open.

database

Name of the database to open (string expression).

fileopt

File options. Supported options include:

ERR=stmtref

Error transfer

IOL=iolref

Default IOList

REC=string$

Record prefix (REC=VIS(string$) can also be used)

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).

Important Note:
To use the MySql interface, you must have either the MySql database or the MySql client installed on a workstation, and the libmysql.dll file must be locatable by the PATH environment variable.

Description

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 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 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 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 table will emulate a normal PxPlus Keyed or Indexed file. This mode allows most existing applications to use MySql 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 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.)

OPT= Parameters

The OPEN parameters for connecting to a MYSQL 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 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.

DATEFMT=xxx

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. 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. 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 the MySql.

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.

MAS90DATE

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 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
PASSWORD=
xxx

Specifies the password for the user when connecting to the database server.

Warning!
It is not advisable to place this in the INI file since anyone with access to it will be able to read this password.

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.

See ODB/OCI/DB2 Record Processing.

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
HOST=
xxx

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)

STDDATE

Overrides the date formatting on individual columns.

STRIP

Removes trailing spaces from fields

SUBQUERY=

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 servers to retrieve the proper first row of a SELECT, a sub-query had to be used. Current versions of MySql 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. 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.

VALIDATE=Y | N

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.)

WAITZERO=Y | N

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.)

See Also

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