MySQL Driver Documentation

Author: Nenad Rakocevic / SOFTINNOV
Date: 23/09/2007
Documentation version: 1.6.1
Comments: mysql_at_softinnov.com

Table of Contents

1. QuickStart
2. Important notices
      2.1. Changes in v1.2.1
      2.2. Changes in v1.2
      2.3. Changes in v1.1
      2.4. Changes in v1.0
      2.5. Changes in v0.9.9
      2.6. Changes in v0.8.5 (for pre-0.8.5 users only)
3. Introduction
4. Setup
5. Connecting to the server
6. Sending commands
      6.1. Send an SQL query
      6.2. Send an SQL query inserting REBOL's values
      6.3. Send server admin commands
7. Retrieving records
8. Closing connection
9. Compact format using READ
10. Shortcut format using SEND-SQL
11. Named Fields
12. Connection Properties
13. Types conversions
      13.1. On Reading Records
      13.2. On Inserting REBOL Values
      13.3. User-defined conversion rules
14. Beta testing
15. Todo
16. Copyright



1. QuickStart

Installation:

Uncompress the ZIP archive on your local filesystem, then :
>> do %mysql-protocol.r
MySQL protocol loaded
Usage (square brackets contain optional information, unless it's specified otherwise) :

read mysql://[[user][:pass]@]host[:port]/[database[/table]]
or

read/custom mysql://[[user][:pass]@]host[:port]/[database] ["...SQL query..."]
(last brackets are mandatory)
or

db: open mysql://[[user][:pass]@]host[:port]/database
... 
send-sql[/flat][/named][/raw] db "SQL query or server command"
...
close db
or

db: open mysql://[[user][:pass]@]host[:port]/database
...
insert db "SQL query or server command"
copy[/part] db [rows [integer!]]
    or
first db        (basically, shortcut to : copy/part db 1)
...
close db
where

user = a valid user name.
pass = a valid password or '?.
host = server address. (localhost or 127.0.0.1, if your server is local)
port = server port. (default is 3306)
database = a valid database name.
table = a valid table name in selected database.



2. Important notices


2.1. Changes in v1.2.1

Date conversion has been slightly changed to cope with MySQL zero dates special values like : '0000-00-00' and '0000-00-00 00:00:00'. There are now converted by default, to the following REBOL values : 1-Jan-0000 or 1-Jan-0000/00:00.

Note: These zero dates values are generated by MySQL when you try to insert invalid date or time values. You can also generate them by inserting a 0 value in a DATE or DATETIME field.


2.2. Changes in v1.2

The behaviour and syntax of the READ function has changed in this release. The new syntax is :

read mysql://root@localhost/
==> return the list of databases

read mysql://root@localhost/db
==> return the list of tables in database "db"

read mysql://root@localhost/db/tbl
==> return the description of table "tbl" in database "db"

read/custom mysql://root@localhost/db ["...sql query..."]
==> execute the sql query on database "db" and return the resultset.
Warning: this new READ syntax will break compatibility with previous versions.

An init SQL string can now be specified for an opened port. That init string will be executed after automatic reconnections. (Useful to set, e.g., a specific charset value). Example :

db-port/locals/init: {SET NAMES 'latin2'}


2.3. Changes in v1.1

There's now a new preferred way to query data, the SEND-SQL global function. The INSERT/COPY methods are still available for cases where finer-grained control or ressources optimization is required.

send-sql[/flat][/named][/raw] db "SQL query or server command"
New named fields mode in the driver produce recordsets with named fields. Example :

author: send-sql/named db "SELECT id, name, year FROM authors WHERE id=1"
print [author/name "is born in" author/year]
Support for multiple statements in queries has been added (separated by semicolons). Now, you can, for example (%setup.sql containing a lot of SQL statements):

send-sql db read %setup.sql


2.4. Changes in v1.0

Now MySQL servers v5 and up, are supported. The new passwords format is recommended for driver stability. If you're migrating your database from a 3.x version, please upgrade users passwords to new passwords encryption.

New flat? property producing flat recordsets of only one block. See the "Connection Properties" section for more details.


2.5. Changes in v0.9.9

This new version now handles all REBOL to SQL conversions. So, if you're using your own conversion functions, the new version can break your code or generate unwanted results (like double-escaping some characters). See the "Type conversion" section for more details.


2.6. Changes in v0.8.5 (for pre-0.8.5 users only)

0.8.5 is a big update which can break some scripts in some cases. Here are a list of the most important changes in the way the driver now behaves:



3. Introduction

This driver allow REBOL developers to fully access the MYSQL RDBMS. It supports server protocols v9 and v10, so it should work with all versions of MySQL. (at least up to v5.0.21 which is the one i use for testing.)

The driver is encapsulated into a REBOL scheme that will allow you to work with the database records as "external" series using INSERT, COPY, FIRST,... natives.

The driver public interface is close to the one proposed by REBOL/Command for various databases, but syntax and behaviour aren't strictly identical. Future MYSQL driver versions will be more compatible with the official REBOL databases API.

The driver should work correctly with all available REBOL products.



4. Setup

Unpack the ZIP archive and save the mysql-driver/ folder in your local filesystem. Installation done !

To load the driver, just DO it, in console mode or in your scripts :

>> do %mysql-protocol.r
MySQL protocol loaded
Now the driver is installed and ready to work.

You can check at anytime if the driver has been loaded by doing:

>> probe first system/schemes
[self default Finger Whois Daytime SMTP POP HTTP FTP NNTP MySQL]
                                                          ^^^^^



5. Connecting to the server

To connect to a MySQL server, use the OPEN function :

db: open mysql://[[user][:pass]@]host[:port]/database
where

 useris a valid user name.
 passis a valid password or ? (question mark).
 hostis the server address. (localhost or 127.0.0.1, if your server is local)
 portis the server port. (use this option if the server isn't listening on the default port 3306)
 databaseis an existing database name.

Example:

db: open mysql://root@localhost/mysql   
db: open mysql://dockimbel:ytrfdzx6p@10.0.0.2/test
db: open mysql://127.0.0.1/bills
OPEN returns an opened port to the server which will be used for all subsequent actions.

If the open process fails, it's most probably because you specified a wrong parameter or your user doesn't have the correct rights set. (check your server config, usually it's caused by an incorrect setup of your user's hosts rights).

If you want to avoid showing your password in the console to everyone, you can use the ? character as replacement. You will be asked to enter your password in a hidden field.

Example:

>> db: open mysql://dockimbel:?@localhost/test
Password: *********



6. Sending commands


6.1. Send an SQL query

Use the INSERT function :

insert port data
where

 portis an opened port to a MySQL server.
 datais a string containing the SQL query.

Example:

insert db "SELECT * FROM user"
insert db "INSERT INTO mybooks VALUES ('Elan', 'REBOL Official Guide')"
insert db {CREATE TABLE products (
    name        VARCHAR(100),
    version     DECIMAL(2, 2),
    released    DATE
)}
INSERT will return NONE if it's ok or else an error! value (which can be trapped with TRY).

Note: Since version 1.1, the driver supports multiple statements in SQL queries. The statements must be separated by a ; (semicolumn). Examples :

insert db {
    DROP TABLE IF EXISTS products;
    CREATE TABLE products (
        name        VARCHAR(100),
        version     DECIMAL(2, 2),
        released    DATE
    );
    INSERT INTO products VALUES ('cheyenne', '1.0', '2007-05-31');
    INSERT INTO products VALUES ('mysql', '1.1', '2007-05-01');
}
insert db read %setup.sql   ;-- execute a big SQL file


6.2. Send an SQL query inserting REBOL's values

Use the INSERT function :

insert port [data word1 word2 ...]
where

 portis an opened port to a MySQL server.
 datais a string containing the SQL query with '? characters.
 word1 word2 ...list of defined REBOL words or values

All ? characters in the SQL query string will be replaced by the supplied REBOL values. If the number of ? characters is greater than the number of supplied values, the driver will send NULL values in place of the missing values. If the number of ? is lesser, the remaining REBOL values will be ignored.

Look at "Type conversion" to see how the driver handles REBOL to SQL conversions.

Example:

insert db ["INSERT INTO mybooks VALUES (?,?)" "Elan" "REBOL Official Guide"]
author: "Elan"
title: "REBOL Official Guide"
insert db ["INSERT INTO mybooks VALUES (?,?)" author title]
tables: [books movies cds games tmp]
foreach table tables [
    insert db ["DELETE FROM ?" table]
]
INSERT will return NONE if it's ok or else an error (which can be trapped with TRY).


6.3. Send server admin commands

This feature is intended for developers who want to build an administration tool for MySQL. You should use these commands very carefully!

Use the INSERT function :

insert port [cmd arg1 arg2 ...]
where

 portis an opened port to a MySQL server.
 cmdis a valid command name.
 arg1 arg2 ...list of command arguments

List of valid commands with their arguments:

 grantRefresh grant tables
 logStart on new log file
 tablesClose all tables
 hosts Flush host cache
 statusFlush status variables
 threadsFlush status variables
 slaveReset master info and restart slave thread
 masterRemove all bin logs in the index and truncate the index

Example:

insert db [init-db "test"]  ; same as 'insert db "use test"
print insert db [statistics]
insert db [shutdown]
insert db [reload grant]
insert db [reload log grant status] ; for 'reload argument order doesn't matter.
Note: You should have the correct privileges set in the server to be allowed to use admin commands !



7. Retrieving records

To get some data from the server, you have to, first, send a query or a command with INSERT. Then, you can retrieve records with the following functions :

copy[/part] port [number]
or

first port
where

 portis an opened port to a MySQL server.
 numberis an integer, indicating the maximum number of records to retrieve.

Records are returned in a 2-dimensional block array, that looks like this:

;        COL 1  COL 2... COL n
[
        [item1 item2 ... itemn] ; ROW 1
        [...   ...   ...  ... ] ; ROW 2
        ...                     ; ...
        [...   ...   ...  ... ] ; ROW m
]
Watch out when choosing the method to get the records, they don't behave the same :

 copyWill return all the available records. The next call to COPY will return NONE.
 copy/partWill return the specified number of records. If you get less records than excepted, it's because you've reached the end of the recordset and there's no more record available.
 firstWill return the next available record. If there's no more records, FIRST will return an empty block [].

Note:

Example:

probe copy/part db 3    ; get 3 records
probe first db          ; get just 1 record
probe copy db           ; get all remaining records
foreach row copy db [probe row] ; useful for a fast screen dump
while [not empty? row: first db][probe row] ; a memory saving version



8. Closing connection

Closing the port connection is done, as always, with the CLOSE function :

close port
where

 portis an opened port to a MySQL server.

Don't forget to close the connection, even in a CGI script, because the server has allocated ressources for your connection and he has to free them. (Also, not closing a port will generate an error entry in the server logs !)



9. Compact format using READ

REBOL allows a compact format for getting data using an URL. The "miracle" is achieved by the READ function. READ can return informations from the server using the following syntax:

read mysql://[[user][:pass]@]host[:port]/[database[/table]]
The result returned by READ will vary according to the URL ending part :

READ can also be used to execute an SQL query directly using the /CUSTOM refinement. The syntax is :

read/custom mysql://[[user][:pass]@]host[:port][/database] ["...SQL query..."]
(last brackets are mandatory)
READ will open the connection to the specified MySQL server, send the given query, retrieve the resulting recordset and close the connection ! This all-in-one format can be useful in simple CGI scripts or for testing purpose from the console.

Note:

The syntax of READ has been changed in v1.2.0 breaking compatibility with previous versions.

Examples:

probe read mysql://root@localhost/
probe read mysql://root@localhost/mysql
probe read mysql://root@localhost/mysql/user
probe read/custom mysql://root@localhost/mysql ["SHOW databases"]
probe read/custom mysql://root@localhost/mysql ["SHOW status"]
probe read/custom mysql://root@localhost/mysql ["SELECT * FROM user"]
; The most powerful 1-line script ! ;-)
foreach row read/custom mysql://root@localhost/books ["SELECT * FROM authors"] [print row]



10. Shortcut format using SEND-SQL

SEND-SQL is basically a shortcut to INSERT/COPY with handy refinements, it sends the query and returns the result :

send-sql[/flat][/named][/raw] port data
where

 portis an opened port to a MySQL server.
 datais a string or a block containing the SQL query.

Refinements:

Refinement Description
/flat same as flat? property, returns a flattened records block
/named returns fields prefixed by their column names. (restricted use when combined with /flat)
/raw same as auto-conv? property, returns all fields as string! values

SEND-SQL supports all the features of the INSERT command, like prepared statements with REBOL values or server commands. This is the preferred and easier way to send queries in the general use case.

Note: There is a restriction using /flat with /named : in this case, the query must return only one record for the naming system to be able to work !

Example:

send-sql db "SELECT * FROM user"
send-sql db ["INSERT INTO mybooks VALUES (?,?)" "Elan" "REBOL Official Guide"]
print send-sql db [statistics]
send-sql db read %setup.sql
foreach row send-sql db "SELECT * FROM authors" [print row]



11. Named Fields

By default, when retrieving records, you get directly the fields values. The only way then, to access the data is to use indexing :

send-sql/flat db "SELECT id, name, year FROM authors WHERE id=1"
print [author/2 "is born in" author/3]
If you want a more friendly access to values, you can activate the "named fields" mode using /named. The example becomes :

author: send-sql/flat/named db "SELECT id, name, year FROM authors WHERE id=1"
print [author/name "is born in" author/year]
If you're using the INSERT/COPY combination, you can still get the columns names using the globally defined NAME-FIELDS function :

name-fields port record
where

 portis an opened port to a MySQL server.
 recordis a block of resulting data (must represent only one record)

So the example becomes then :

insert db "SELECT id, name, year FROM authors WHERE id=1"
author: name-fields db first db
print [author/name "is born in" author/year]



12. Connection Properties

You can get more informations about the server or the result of a query, with the following object:

port/locals ; port is an opened port to a MySQL server
Here's a list of the currently available properties :

Property Type Access Description
max-rows integer! - (not yet supported, use COPY/PART instead)
auto-conv? logic! read/write Set to ON will automatically convert types when decoding records. Set to OFF will produce records with all values of type string!
auto-ping? logic! read/write Set to ON will allow automatic detection of lost connections and will try to reconnect if needed. It works by sending a 'ping command before each call to INSERT. Set to OFF will disable the automatic 'ping and reconnection system
flat? logic! read/write Set to ON will produce 1-dimensional recordset (a flat block of all records). Set to OFF (default) will enclose each records in its own block (a block of block of records)
init string! read/write init SQL commands to execute after an automatic reconnection
matched-rows integer! read Will give the number of affected rows after an SQL INSERT or UPDATE query
conv-list block! read Description list used for type conversion
columns block! read List of columns objects for the last recordset retrieved
protocol integer! read MySQL protocol version. (set by the server)
version string! read Server version
thread-id integer! read Thread ID for the current connection
crypt-seed string! read Crypt seed used to encode passwords
capabilities block! read List of server capabilities (word! values)
error-code integer! read Last error code
error-msg string! read Last error message

Note:



13. Types conversions


13.1. On Reading Records

The following table shows the default conversions applied to each value when reading records :

MySQL Type REBOL types
decimal decimal!
tiny integer!
short integer!
long integer!
float decimal!
double string!
null none! (*)
timestamp string!
longlong string!
int24 integer!
date date! (**)
time time!
datetime date! (**)
year integer!
newdate string!
var-char string!
bit string!
new-decimal string!
enum string!
set string!
tiny-blob string!
medium-blob string!
long-blob string!
blob string!
var-string string!
string string!
geometry string!

(*) MySQL 'NULL value is handled separately at low-level in the driver. 'NULL is always converted to NONE.

(**) MySQL zero dates values are converted to 1-Jan-0000 or 1-Jan-0000/00:00.



Changing the default conversion can be done with the CHANGE-TYPE-HANDLER function.


13.2. On Inserting REBOL Values

When you use the ? character in your SQL statement, the driver will automatically do some encoding for you on REBOL values. Here is the list of the built-in conversion rules :

REBOL Datatype SQL Encoding
none! NULL
date! 'yyyy-mm-jj' or 'yyyy-mm-jj hh:mm:ss'
time! 'hh:mm:ss'
money! just removes the '$ character
string! 'sql-escaped-string'
binary! 'sql-escaped-string'
block! 'ENUM(string1, string2, ...)'

All other REBOL types will be FORM-ed !

sql-escaped-string are REBOL strings converted to SQL format. Here's a list of the characters escaped for SQL :

REBOL format SQL format Description
^@ \0 Null
^/ \n Line feed
^- \t Tabulation
^M \r Carriage return
^H \b Backspace
' \' Simple quote
" \" Double quote
\ \\ Backslash


13.3. User-defined conversion rules

You can change the default conversion rules applied to each MySQL type using the following globally-defined function :

change-type-handler port type handler
where

 portis an opened port to a MySQL server.
 typeis a valid MySQL type name. (See this table for allowed names)
 handleris a block! containing the conversion code.

The handler works like a pipe :

converted value <= [handler] <= raw value (string!)
or

new-value: handler raw-value
So you have to make a correct "piping" REBOL code, which takes a value from the right side and returns the converted value on the left side. You could make your own conversions after retrieving records with COPY, but using CHANGE-TYPE-HANDLER will ensure you the best performances, because your conversion code will be executed at low-level in a special 1-pass conversion process. This is especially true if you're retrieving a lot of records.

Example:

Convert 'short values to REBOL hex strings :

change-type-handler db 'short [to-hex to-integer]
Convert 'blob to REBOL objects :

to-obj: func [value][first reduce load to string! decompress]
change-type-handler db 'blob [to-obj]
Assuming that your objects have been stored in the following way :

to-blob: func [obj [object!]][compress mold obj]
insert db ["INSERT INTO table1 VALUES (?)" to-blob my-obj]
Note:

>> probe your-port-name/locals/conv-list



14. Beta testing

If you feel comfortable with MySQL, give a try to the driver and report me all bugs and your comments about it! If you need a better support for some features (or features i didn't implement), feel free to send me all your needs/ideas.

(If you find some bugs in this driver, you can add to your report a copy of the console output with trace/net activated.)

I would like to send a special thank to :

Moulay Semlali
Terry Brownel
Gabriele Santilli
Allen Kamp
Frank Sievertsen
Michal Kracik
Petr Krezenlok
Will Arp
for helping me debug and improve this driver. (Sorry if I missed someone)



15. Todo



16. Copyright

Copyright notice:

(C) 2001-2008 SOFTINNOV

 This software is provided 'as-is', without any express or implied
 warranty.  In no event will the author be held liable for any damages
 arising from the use of this software.

 Permission is granted to anyone to use this software for any purpose,
 including commercial applications, and to alter it and redistribute it
 freely, subject to the following restrictions:

 1. The origin of this software must not be misrepresented; you must not
 claim that you wrote the original software. If you use this software
 in a product, an acknowledgment in the product documentation would be
 appreciated but is not required.
 2. Altered source versions must be plainly marked as such, and must not be
 misrepresented as being the original software.
 3. This notice may not be removed or altered from any source distribution.

 Nenad Rakocevic
 nr@softinnov.com
If you use the MySQL driver in a product, I would appreciate *not* receiving lengthy legal documents to sign. The sources are provided for free but without warranty of any kind. The driver has been entirely written by Nenad Rakocevic; it does not include third-party code.

If you redistribute modified sources, I would appreciate that you include in the mysql-protocol.r file history information documenting your changes.


Copyright 2004-2007 SOFTINNOV All Rights Reserved.
Formatted with REBOL Make-Doc 0.9.6.1 on 13-Jul-2008 at 20:40:58