mysql.lsp

Module index

source download

Module: mysql.lsp

MySQL v.5.x interface (tested on MySQL 5.0 and 5.1)

Version: 2.61 - addition for mysql_escape_string (Jeff Ober)
Version: 2.62 - fix for mysql_escape_string (Tim Johnson)
Version: 3.0 - module now independent of C-structure offsets
Version: 3.1 - module now senses if running 64-bit version of newLISP
Version: 3.2 - a fix when fetch-all has an empty result
Version: 3.3 - typo in fetch-all didn't delete old fetches
Version: 3.4 - documentaion error for load path
Version: 3.41 - library load path for Fedora Linux
Version: 3.42 - library load path upgraded for OpenBSD 4.9
Version: 3.43 - library load path upgraded for CentOS 6.x
Version: 3.44 - library load path upgraded for CentOS 6.x
Version: 3.45 - library load path upgraded for UBUNTU Linux 12.04
Version: 3.46 - add UTF-8 handling in documentation (Csfreebird July/2014)
Version: 3.47 changed references to /usr/ to /usr/local/
Version: 3.48 missing quote in documentation and doc for Windows 10
Author: Lutz Mueller 2003-2018, Gordon Fischer 2005, Jeff Ober 2007


This MySQL 5.x interface module has been tested on versions 5.0 and 5.1 of mysql from www.mysql.com

An alternate implementation of a MySQL module with more features is available at ArtfulCode.

Requirements

At the beginning of the program file include a load statement for the module:

 (load "/usr/local/share/newlisp/modules/mysql.lsp")
 ; or shorter
 (module "mysql.lsp")
 

A version of libmysqlclient for a specific platform is required:

on LINUX/UNIX: /usr/local/mysql/lib/libmysqlclient.so
on Mac OS X: /usr/local/mysql/lib/libmysqlclient.dylib

This library is installed when using the Mac OS X x86 installer .dmg package from http://www.mysql.com

To compile MySQL with client libraries use:

./configure --prefix=/usr/local --enable-shared

This library might be in a different location on a particular installation of MySQL or have a different name. Change accordingly in the code at the beginning.

On MS Windows 10 (64 bit) use the libmariadb.dll library available here from https://downloads.mariadb.org

The MySQL server itself may reside on a different machine on the network. The library libmysqlclient will communicate with that server. The correct connection is created using the MySQL:connect call.

At the bottom of the module file mysql51.lsp a test routine test-mysql is included to test for correct installation of MySQL.

In the MySQL:connect call of that test routine the correct parameters for the MySQL server location and user and password have to be inserted.

Functions available

 MySQL:init ................ get a database handle MYSQL
 MySQL:connect ............. connect to a database
 MySQL:query ............... execute a SQL statement
 MySQL:num-rows ............ rows in result of query
 MySQL:num-fields .......... columns in result of query
 MySQL:fetch-row ........... get row from the query result
 MySQL:fetch-all ........... get all rows from the last query
 MySQL:database ............ return all database names
 MySQL:tables .............. return all tables names
 MySQL:fields .............. return all fields in a table
 MySQL:data-seek ........... position in result for fetching
 MySQL:affected-rows ....... number of affected rows from operation
 MySQL:inserted-id ......... last value of auto increment id operation
 MySQL:escape .............. escapes SQL input string using mysql_real_escape_string
 MySQL:error ............... get error message
 MySQL:close-db ............ close database connection
 

A typical MySQL session

The following code piece outlines a typical MySQL session:

Example:

 (module "mysql.lsp") ; load the module file
 
 (MySQL:init)       ; initialize
 (MySQL:connect "192.168.1.10" "auser" "secret" "mydb") ; logon
 (MySQL:query "select ...;") ; SQL query
 (MySQL:query "insert ...;") ; SQL query
        ...
 (MySQL:close-db)

The database server is listening on IP 192.168.1.10. The program connects with username "auser" password "secret" to a database with the name "mydb". After connecting SQL statements are performed and finally the program disconnects from the server.

UTF-8 character encoding

When using newLISP compiled for UTF-8, the following statements may be necessary at the beginning:

 (MySQL:query "SET character_set_client = utf8;")
 (MySQL:query "SET character_set_connection = utf8;")
 (MySQL:query "SET character_set_results = utf8;")
 

To see all MySQL character variables:

 (MySQL:query "SHOW VARIABLES LIKE character%;")
 (dotimes (x (MySQL:num-rows)) (println (MySQL:fetch-row)))
 

§

MySQL:init

syntax: (MySQL:init)

return: true on success, nil on failure.

§

MySQL:connect

syntax: (MySQL:connect str-server str-userID str-password str-db)
parameter: str-server - The host name or IP address or 0 for localhost.
parameter: str-userID - The user ID for authentication.
parameter: str-password - The password for authentication.
parameter: str-db - The name of the database to connect to.

return: true for success or nil for failure.

Connects to a database on server and authenticates a user ID. (MySQL:init) must have been called previously.

§

MySQL:query

syntax: (MySQL:query str-sql)
parameter: str-sql - A valid SQL query string.

return: For insert queries rerturns the inserted ID else true for success or nil for failure.

Sends a SQL query string to the database server for evaluation.

§

MySQL:num-rows

syntax: (MySQL:num-rows)

return: Number of rows from last query.

§

MySQL:num-fields

syntax: (MySQL:num-fields)

return: Number of columns from last query.

§

MySQL:fetch-row

syntax: (MySQL:fetch-row)

return: A list of field elements.

Fetches a row from a previous SQL MySQL:query select statement. Subsequent calls fetch row by row from the result table until the end of the table is reached.

§

MySQL:fetch-all

syntax: (MySQL:fetch-all)

return: All rows/fields from the last query.

The whole result set from the query is returned at once as a list of row lists.

§

MySQL:databases

syntax: (MySQL:databases)

return: A list of databases.

Performs a show databases; query.

§

MySQL:tables

syntax: (MySQL:tables)

return: A list of tables in the database.

Performs a show tables; query.

§

MySQL:fields

syntax: (MySQL:fields str-table)
parameter: str-table - The name of the table.

return: A list of field description lists.

For each field name in the table a list of specifications for that field is returned. The list starts with the name for the field followed by the type size/precision and other optional field descriptions.

§

MySQL:data-seek

syntax: (MySQL:data-seek num-offset)
parameter: num-offset - The 0 based offset to position inside the data set.

return: Always true.

Positions in the result set at a zero based offset for a subsequent MySQL:fetch-row call. If the offset is out of the allowed range for the result set a subsequent fetch-row will return nil.

§

MySQL:error

syntax: (MySQL:error)

return: Text info about the last error which occured.

§

MySQL:affected-rows

syntax: (MySQL:affected-rows)

return: Number of affected rows by the last MySQL:query operation.

§

MySQL:inserted-id

syntax: (MySQL:inserted-id)

return: Last insert ID from an auto increment field.

§

MySQL:escape

syntax: (MySQL:escape str-sql)

return: escaped string

This function will escape special characters in str-sql, so that it is safe to place it in a MySQL query.

§

MySQL:close-db

syntax: (MySQL:close-db)

return: Always true.

Closes database access. For new database acess, both MySQL:init and MySQL:connect functions have to be called.

- ∂ -
generated with newLISP  and newLISPdoc