MySQL provides an Open Source/Freeware SQL and ODBC compliant relational database capability. It is supported on many platforms, among which are OpenVMS and Windows-XP (many version of UNIX and Linux are also supported). Support for MySQL is available from many languages and applications.
PHP on OpenVMS and Windows has the ability to interface with MySQL.
A C and C++ API are available on both OpenVMS and Windows.
A. S. Thomas, Inc. has implemented a Fortran API to MySQL.
Documentation for MySQL can be found online at:
http://astc08/mysql/doc/html-chapter/
MySQL_Client is a simple tool that provides basic MySQL interface functions. It can be accessed using the following url:
http://astc08/scripts/mysql_client/mysql_client_098/
There are several books in the corporate library that provide a good introduction to MySQL:
Apache, MySQL, and PHP Web Development for Dummies
Provides an easily understood introduction to MySQL and PHP
Learning SQL
Provides information on MySQL and Oracle, Capabilities are compared and differences noted.
After studying various of the examples provided in the above books, the online MySQL documentation is the best source of information. The above books are incomplete with respect to much of the useful capabilities present in MySQL.
The FORTRAN API resides in the library AST$MYSQL:MYSQL_ISAM. The API consists of a C module with several entry points that allows FORTRAN programs to interface with MySQL and a set of FORTRAN routines that provide capabilities equivalent to those found in the OpenVMS RMS ISAM library.
The FOR_MYSQL.C module provides the necessary “hooks” for FORTRAN programs to interact with the MySQL server.
IERR = FOR_MYSQL_INIT(%REF(<SERVER>),
%REF(<USER>),%REF(<PASSWORD>,%REF(<DATABASE>))
Initialize MySQL Database access. This routine must be the first routine called in order establish a connection to the MySQL Server and associated database. Access rights to the database are by username and password.
|
Variable |
Description |
|
Server |
Server node address, null terminated character string, local node pass null byte. Only one server connection is supported. |
|
User |
Valid User Name, null terminated string |
|
Password |
Valid Password, null terminated string |
|
Database |
Data to access, null terminated string. Only one database may be accessed at a time. Simultaneously accessing tables (files) from different databases is not supported. |
|
IERR |
Returned status code. INTEGER*4 Success if =0, error otherwise. Returned value is MySQL error code. |
IERR = FOR_MYSQL_CLOSE()
Close connection to database and MySQL server.
IERR = FOR_MYSQL_QUERY(%VAL(LUN),%REF(<QUERY>))
Issue a query against a MySQL database. All data activity is initiated using a form of the query command.
|
Variable |
Description |
|
Lun |
Logical Unit Number used to index into query results |
|
Query |
Query, null terminated string |
|
IERR |
Returned status code. INTEGER*4 Success if =0, error otherwise. Returned value is MySQL error code. |
IERR = FOR_MYSQL_FETCH_ROW(%VAL(LUN))
Set pointer to the next row from the current query on the LUN.
IERR = FOR_MYSQL_FETCH_FIELD(%VAL(<FIELD No.>),
%VAL(<FIELD LEN>),%REF(<DATA>))
Return requested field data from the current row.
NUM_FIELDS = FOR_MYSQL_NUM_FIELDS(%VAL(LUN))
Return number of fields in row of current query.
IERR = FOR_MYSQL_FIELD_LENGTHS(%VAL(LUN),FIELD_LEN)
FIELD_LEN array contains NUM_FIELDS entries woth corresponding field lengths for row in current query.
The MYSQL_ISAM library contains the FOR_MYSQL module as well as the MYSQL version of the general FORTRAN ISAM library.
Functions are provided for handling standard file I/O. These functions perform file opening, random keyed reads, sequential keyed reads, random writes, and updates. In all cases these functions return a status of type INTEGER*2. A status return of 1 denotes a successful completion.
Each file has a unique file open function. This function has a name of the form ISOxxx, where the xxx is the file prefix. The file open function is referenced as follows:
ISTAT=ISOxxx(LUN,MODE,TYP)
where
LUN - INTEGER*2 Logical Unit Number
to use for opening the file.
MODE - LOGICAL*1 Mode of access:
S = shared
E = exclusive
TYP - LOGICAL*1 File open type:
O = Open Existing File
N = Create a New File
S = Create a Temporary File
U = Open existing file if
one exists, otherwise
create a file.
ISTAT - Status return:
1 = success
<>1 = error
File open functions may be created using the command procedure AST$COMMAND:OPENCRE.
A general purpose keyed read function is provided. This function provides for all types of random read operations and creates a collection that may be subsequently read. Care needs to be taken to insure that the resulting collection contains the desired records. The keyed read function is called as follows:
ISTAT=KEYRD(LUN,REC,RECL,KEYV,KEYL,KEYR,TYP)
where:
LUN - INTEGER*2 Logical Unit Number on
which file is open.
REC - LOGICAL*1 array to contain returned
record.
RECL - INTEGER*2 length of REC.
KEYV - LOGICAL*1 array containing the key
value to search for.
KEYL - INTEGER*2 length of KEYV.
KEYR - INTEGER*2 key of reference number.
TYP - LOGICAL*1 array search type:
EQ - exact match.
GE - greater than or equal to.
GT - greater than.
ISTAT - INTEGER*2 status return:
1 = record found
2 = record not found
-1 = record locked by another
user. (10 access intervals
must have elapsed, before this
error is returned, i.e. the
record has been held by another
user for at least 20 sec.)
-2 = Invalid Key
-3 = I/O error encountered
A general purpose sequential read function is provided. This function performs a sequential read operation of current selection. The sequential read function is called as follows:
ISTAT=KSQRD(LUN,REC,RECL)
where:
LUN - INTEGER*2 Logical Unit Number on
which file is open.
REC - LOGICAL*1 array to contain returned
record.
RECL - INTEGER*2 length of REC.
ISTAT - INTEGER*2 status return:
1 = record found
2 = End Of File
-1 = record locked by another
user. (10 access intervals
must have elapsed, before this
error is returned, i.e. the
record has been held by another
user for at least 20 sec.)
-2 = No key of reference
-3 = I/O error encountered
A general purpose write function is provided. A new record is written into the table referenced by the LUN. The write function is called as follows:
ISTAT=KEYWRT(LUN,REC,RECL)
where:
LUN - INTEGER*2 Logical Unit Number on
which file is open.
REC - LOGICAL*1 array containing the
record.
RECL - INTEGER*2 length of REC.
ISTAT - INTEGER*2 status return:
1 = success
-1 = attempt to create a
duplicate on a key with
no duplicates attribute
-2 = I/O error encountered
A general purpose update function is provided. The current record on the specified LUN is rewritten with changes (updated) into the table associated with the LUN. The update function is called as follows:
ISTAT=KEYUPD(LUN,REC,RECL)
where:
LUN - INTEGER*2 Logical Unit Number on
which file is open.
REC - LOGICAL*1 array containing the
record.
RECL - INTEGER*2 length of REC.
ISTAT - INTEGER*2 status return:
1 = success
-1 = record not currently
locked by user
-2 = attempt to modify key with
no change attribute
-3 = attempt to create a
duplicate on a key with
no duplicates attribute
-4 = I/O error encountered
A general purpose delete function is provided. This routine provides for delete operation of the current record on the table open on the LUN.
CALL RCDEL(LUN)
where:
LUN - INTEGER*2 Logical Unit Number on
which file is open.
Unlock table (release all locks held).
CALL KEYUNLOCK(LUN,DISP)
where:
LUN - INTEGER*2 Logical Unit Number on
which file is open.
DISP - Dummy Argument.
Close table and optionally delete table.
CALL KEYCLOSE(LUN,DISP)
where:
LUN - INTEGER*2 Logical Unit Number on
which file is open.
DISP - Character String, File Disposition
‘KEEP’, keep table
‘DELETE’, delete table
In order to build programs accessing MySQL on OpenVMS the following must be added to the linker commands:
$ LINK …,AST$MYSQL:MYSQL_ISAM_I/LIB,-
MYSQL_ROOT:[VMS.LIB]MYSQLCLIENT_UPPER/LIB,-
AST$LIBRARY:STRINGVAX_I/LIB