Artful Code

The Mysql module has been written from scratch utilizing some of the more recent features of newLisp, such as FOOP and reference returns. One of its major design goals was to simplify use as well as broaden the features of the standard MySQL module, while at the same time allowing the creation of new, anonymous instances at run-time.

The Mysql module differs from the distribution standard module in several important ways. Most obviously, it uses FOOP wrappers for MySQL types. It also requires clients to free results instances; in the standard module, only the base MYSQL instance itself must be freed (using MySQL:close-db).

The significance of this is that it is much simpler to create multiple connections (without having to duplicate the entire context at compile time). Result sets are completely independent of each other, and several may be maintained in any state at once. This also means that a spawned process may be given its own Mysql instance to use without having to worry about other processes’ instances interfering. Using the standard module, the entire context would need to be cloned at compile time and given a static symbol reference (e.g., (new 'MySQL 'db)) in order to run multiple instances or connections to a server.

Moreover, because this module uses unpack and MySQL C API accessor functions, there is no need for the client to calculate member offsets in MySQL compound types. So long as newLisp was compiled for the same target as the libmysqlclient library (both are 32 bit or both are 64 bit), everything should work out of the box. Additionally, MySQL errors are now checked in the connect and query functions and re-thrown as interpreter errors. Instead of checking for nil returns and a using MySQL:error to get the error message, standard error  handling with the catch function may be used.

Several convenience functions and macros have been defined in the mysql context for common operations, including connecting to the database and iterating over results without having to worry about catching errors and managing memory. SQL statements may be passed as strings or a list containing a format string and its parameters, which will type-checked and automatically be escaped as needed.

(mysql:on-connect '("localhost" "user" "secret" "my_database")
  (lambda (db err)
    (if err
      (println "Error! " err)
      (mysql:row-iter db "SELECT * FROM some_table" true
        (lambda (row)
          (println row))))))

This module has been tested with MySQL version 5 and 5.1 and newLisp version 10.0.1. It requires newLisp 10.0 or later. You can download it from the Artful Code newLisp module repository.