Modula-2 MySQL Interface

For ISO Modula-2

By Frank Schoonjans (frank.schoonjans@ugent.be)

Using the Modula-2 MySQL library, applications developed in Modula-2 can connect to a MySQL server and use the functions defined in the MySQL API (Application Programming Interface) to build powerfull database applications. These can, but need not, be web applications.

In order to use MySQL, the MySQL server must be installed on your system. If it is not, you can download it from the MySQL AB website.

Modula-2 MySQL download

Stony Brook project (Windows) mysql.zip. Contains a Modula-2 interface for libmySQL.dll, the client library that ships with the MySQL database server (libmySQL.dll is also included in the zip file).

All software is provided 'as-is', without any express or implied warranty. In no event will the authors be held liable for any damages arising from the use of the software described on or downloaded from this website.

Modula-2 MySQL Sample application

This simple sample application illustrates the use of the MySQL API. The sample application is included in the download. It requires that you have installed MySQL on your system.

Connecting to the MySQL server

Before we can execute any of the other API functions, we must establish a connection to the MySQL database engine, by using the mysql_real_connect() function. As a first parameter this function requires a structure of type MYSQL which must be initialised by means of the procudure mysql_init().



PROCEDURE InitMySQL(VAR mysql : MYSQL;
                    host,user,password,dbase : ARRAY OF CHAR;
                    port: CARDINAL; unix_socket: ARRAY OF CHAR;
                    clientflag: CARDINAL) : BOOLEAN;
BEGIN
    WriteString('Connecting to MySQL ... '); WriteLn;
    mysql_init(mysql);
    IF mysql_real_connect(mysql,host,user,password,dbase,
                          port,unix_socket,clientflag) = NIL THEN
       Error(mysql,"InitMySQL");
       RETURN FALSE;
    END;
    (* ... *)
    RETURN TRUE;
END InitMySQL;


When the function mysql_real_connect() returns NIL, an error has occurred, and we call the following error procedure:

PROCEDURE Error(mysql: MYSQL; errstr : ARRAY OF CHAR);
BEGIN
    WriteString("MySQL error - err#");
    WriteCard(mysql_errno(mysql),1); WriteString(" - ");
    WriteString(errstr); WriteString(": "); WriteLn;
    WriteStrP(mysql_error(mysql));
    WriteLn;
END Error;


The mysql_error() and several other MySQL procedures return pointers to strings (type PCHAR),
so we use the following Modula-2 procedure to display these strings:

PROCEDURE WriteStrP(P : PCHAR);
TYPE StrP = POINTER TO ARRAY [0..1023] OF CHAR;
BEGIN
    WriteString(P:StrP^);
END WriteStrP;


Create database

After we have established a connection to the server, we can create a database using
the following MySQL query: CREATE DATABASE databasename

The query is executed by means of the mysql_query() procedure, illustrated in the following example:

CONST
    Database  = "testDB";

PROCEDURE CreateDataBase(VAR mysql : MYSQL; database : ARRAY OF CHAR): BOOLEAN;
VAR query : ARRAY [0..255] OF CHAR;
BEGIN
    query := "CREATE DATABASE ";
    Append(database,query);
    IF mysql_query(mysql,query)=0 THEN
       WriteString("Create Database "); WriteString(database);
       WriteString(" OK"); WriteLn;
       RETURN TRUE;
    ELSE
       Error(mysql,"Create DataBase");
       RETURN FALSE;
    END;
END CreateDataBase;


Select database
Next, we use the procedure mysql_select_db to select the database.

PROCEDURE SelectDataBase(VAR mysql : MYSQL; database : ARRAY OF CHAR): BOOLEAN;
BEGIN
    IF mysql_select_db(mysql,database) = 0 THEN
       WriteString("Select Database "); WriteString(database);
       WriteString(" OK"); WriteLn;
       RETURN TRUE;
    ELSE
       Error(mysql,"Select Database");
       RETURN FALSE
    END;
END SelectDataBase;


Create table

We now must create a table in the database by sending a create table query to the MySQL engine:

CREATE TABLE tablename [(create_definition),...)]

In our example program, we use the following procedure to create the table:

PROCEDURE CreateTable(VAR mysql : MYSQL; table : ARRAY OF CHAR;
                      columns : ARRAY OF CHAR): BOOLEAN;
VAR query : ARRAY [0..255] OF CHAR;
BEGIN
    query := "CREATE TABLE ";
    Append(table,query);
    Append(" ",query);
    Append(columns,query);
    IF mysql_query(mysql,query)=0 THEN
       WriteString("Create table "); WriteString(table);
       WriteString(" OK"); WriteLn;
       RETURN TRUE;
    ELSE
       Error(mysql,"Create table");
       RETURN FALSE;
    END;
END CreateTable;


The procedure is used as follows:

    CreateTable(msql,"people","(name VARCHAR(20), age INT, email VARCHAR(30) )")



This will create a table named "people" in the selected database.
The table contains 3 columns for name (text), age (integer) and email (text).

Adding data to the table

Data are added to the table by sending a query to the MySQL engine.
In our example, this query takes the following form: INSERT INTO tablename VALUES (name,age,email)

This is illustrated in the following procedure:

PROCEDURE AddData;
VAR name,email,agestr : ARRAY [0..30] OF CHAR;
    age   : CARDINAL;
    query : ARRAY [0..80] OF CHAR;
BEGIN
    WriteString("Enter data. Empty Name = end"); WriteLn;
    WriteLn;
    LOOP
      WriteString("Name  : "); ReadString(name); SkipLine;
        IF name[0]=CHR(0) THEN RETURN END;
      WriteString("Age   : "); ReadCard(age); SkipLine;
        CardToStr(age,agestr);
      WriteString("Email : "); ReadString(email); SkipLine;
      WriteLn;
      query:="INSERT INTO ";
      Append(Table,query);
      Append(" VALUES (",query);
      Append("'",query); Append(name,query); Append("'",query);
      Append(",",query);
      Append("'",query); Append(agestr,query); Append("'",query);
      Append(",",query);
      Append("'",query); Append(email,query); Append("'",query);
      Append(")",query);
      IF mysql_query(msql,query) # 0 THEN
         Error(msql,"Add data query failed"); WriteLn;
      END;
    END;
END AddData;


Retrieving information from the database

First we illustrate how we can list all data from a table in the database,
by means of the query SELECT * FROM tablename (where * means that we want to retrieve all columns):

PROCEDURE ListAll;
VAR query : ARRAY [0..80] OF CHAR;
BEGIN
    query:="SELECT * FROM "+Table;
    List(query);
END ListAll;


This procedure simply creates the query string and calls our procedure List:

PROCEDURE List(query : ARRAY OF CHAR);
VAR recbuf : PMYSQL_RES;
    rowbuf : MYSQL_ROW;
BEGIN
    IF mysql_query(msql,query) # 0 THEN
       Error(msql,"Query failed");
       RETURN;
    END;
    WriteLn;

    recbuf := mysql_store_result(msql);
    IF recbuf=NIL THEN
       WriteString('Query returned nil result.'); WriteLn;
       RETURN
    END;
    WriteString('Number of records returned  : ');
        WriteCard(mysql_num_rows(recbuf),0); WriteLn;
    WriteString('Number of fields per record : ');
        WriteCard(mysql_num_fields(recbuf),0); WriteLn;

    WriteLn;
    rowbuf := mysql_fetch_row(recbuf);
    WHILE (rowbuf <>NIL) DO
       WriteString('Name: ');  WriteString(rowbuf^[0]^); WriteString(" - ");
       WriteString('Age: ');   WriteString(rowbuf^[1]^); WriteString(" - ");
       WriteString('Email: '); WriteString(rowbuf^[2]^);
       WriteLn;
       rowbuf := mysql_fetch_row(recbuf);
    END;
    WriteLn;
    WriteString('Freeing memory occupied by result set...'); WriteLn;
    mysql_free_result(recbuf);
END List;


First the query is send to the MySQL engine.
If the mysql_query function was successful, we use the mysql_store_result
function to store the result of the query in the variable recbuf of type PMYSQL_RES.
This variable is used in the subsequent procedures mysql_num_rows() and mysql_num_fields()
to obtain the number of records returned, and the number of fields per record.
Next we retrieve the rows from the database table by the function mysql_fetch_row().
This function takes a parameter of type PMYSQL_RES and returns a variable of type MYSQL_ROW which
is actually a pointer to an array of pointers to strings which in our example we display on the screen:

    rowbuf := mysql_fetch_row(recbuf);
    WHILE (rowbuf <>NIL) DO
       WriteString('Name: ');  WriteString(rowbuf^[0]^); WriteString(" - ");
       WriteString('Age: ');   WriteString(rowbuf^[1]^); WriteString(" - ");
       WriteString('Email: '); WriteString(rowbuf^[2]^);
       WriteLn;
       rowbuf := mysql_fetch_row(recbuf);
    END;

Finally we must free the memory allocated to recbuf by calling mysql_free_result(recbuf).

Searching the database table

We can search the table with a query SELECT * FROM tablename WHERE field=search_spec.

In the example application we ask for a name and build the query string in the following procedure
(which calls the List procedure described above):

PROCEDURE Search;
VAR name  : ARRAY [0..30] OF CHAR;
    query : ARRAY [0..80] OF CHAR;
BEGIN
    WriteString("Enter name : ");
    ReadString(name); SkipLine;
    query:="SELECT * FROM "+Table+" WHERE name='";
    Append(name,query);
    Append("'",query);
    List(query);
END Search;



Closing the connection
Finally we must close the connection to the MySQL engine.

    mysql_close(msql);




MySQL documentation

Web sites

MySQL AB home page

MySQL Reference Manual

MySQL C API overview


Books


 

MySQL Reference Manual
by Michael Widenius, et al.
This book covers issues at all stages in MySQL use, from installation
problems through performance tuning. Offers a thorough comparison to MySQL with
SQL standards and other databases.

Order from: amazon.com - amazon.ca - amazon.co.uk - amazon.de - amazon.fr








 

MySQL Cookbook
by Paul DuBois
This book provides solutions for typical MySQL dilemmas range from simple ways to find all
records that contain a given string, to more difficult problems, such as finding matching/non-matching records in two tables. 

Order from: amazon.com - amazon.ca - amazon.co.uk - amazon.de - amazon.fr




Reviews
MySQL 4 review in PC magazine.