Description

MySQL bindings for Chicken.

Author

Toby Butzon

Version

Requires

Usage

(require-extension mysql)

Download

mysql.egg

Documentation

The MySQL egg provides (most of) the functions offered by the MySQL C API (the foreign-mysql-* functions). It also provides a set of slightly more convenient Scheme functions (the mysql-* functions). Finally, a few extra functions are provided for easy of use.

Only the most often used MySQL functions are described in this document. If you really want to see the full listing, consult the mole documentation.

Please send bug reports and suggestions to toby@butzon.com.

Exceptions

Conditions of the kind (exn mysql) are signaled for error situations.

Properties
location Where the error occured - usually a procedure name.
arguments Values that contributed to the error.
message Error message.

Connections

procedure: (mysql-connect [KEYWORDS])

Connect to a MySQL server.

Returns a MySQL connection object suitable for passing to the other MySQL functions. This object is referred to as CONN when passed by all the other MySQL functions. Signals an exception when the connection fails.

Any number of the following KEYWORDS may be included:

  • host
  • user
  • passwd
  • db
  • port
  • unix-socket
  • client-flag
  • ssl - A mysql-ssl record object.
  • options - An association list of (<mysql-option> . <object>).

Note that default values are available for all of these arguments. (Consult the MySQL C API for details on how these defaults are determined.)

procedure: (mysql-connection? OBJECT)

Is the OBJECT a mysql-connection? Referred to as a CONN.

procedure: (mysql-close CONN)

Closes the connection to CONN. This frees any remaining MySQL resources from memory, but invalidates the MySQL connection object (CONN) so that it may no longer be used.

Connect Parameter Constructors
procedure: (make-mysql-options OPTION-FLAG OPTION-VALUE ...)

Returns an object suitable as the options parameter for mysql-connect.

The type of the OPTION-VALUE is dependent on the OPTION-FLAG.

procedure: (make-mysql-ssl [KEYWORD ...])

Returns a mysql-ssl record.

Any number of the following KEYWORD ... may be included:

  • key - pathname of key file.
  • certificate - pathname of certificate file.
  • certificate-authority - pathname of certificate authority file.
  • trusted-certificates - pathname of trusted certificates directory.
  • ciphers - See OpenSSL Ciphers Man Page

procedure: (mysql-ssl? OBJECT)

Is the OBJECT an mysql-ssl record?

procedure: (mysql-ssl-key-pathname MYSQL-SSL)

Returns a string.

procedure: (mysql-ssl-certificate-pathname MYSQL-SSL)

Returns a string.

procedure: (mysql-ssl-certificate-authority-pathname MYSQL-SSL)

Returns a string.

procedure: (mysql-ssl-trusted-certificates-pathname MYSQL-SSL)

Returns a string.

procedure: (mysql-ssl-ciphers MYSQL-SSL)

Returns a string.

Basic Query Operations

procedure: (mysql-query CONN SQL-STRING)

Executes SQL-STRING on the MySQL server and stores the result in memory. Signals an exception if the query fails.

procedure: (mysql-fetch-row CONN)

Fetches a row from the result set returned by the most recent call to mysql-query. If the last query failed, or if there are no more rows left in the result set, returns #f; otherwise returns a row object.

A row object is defined as a function that takes a single argument. If the argument is a number, the function returns the value of the field for which that number is the index, or #f if the index is out of range. Otherwise, the argument must be a symbol or string, in which case the function returns the value of the field for which that string (or symbol converted into a string) is the field/column name. If no such field exists, returns #f.

procedure: (mysql-field-count CONN)

Returns the number of columns for the most recent query.

procedure: (mysql-fetch-lengths CONN)

Returns a u32vector of the lengths of the columns of the current row.

procedure: (mysql-rewind CONN)

Rewinds the result set; that is, resets the pointer used by mysql-fetch-row so that the next call to it will return the first row of the result set. If there is no current result set, does nothing.

procedure: (mysql-num-rows CONN)

Returns the number of rows in the current result set. If no result set exists, returns #f.

procedure: (mysql-num-fields CONN)

Returns the number of fields in the current result set. If no result set exists, returns #f.

procedure: (mysql-affected-rows CONN)

Returns the number of rows affected by the current query.

Row Mapping

procedure: (mysql-row-fold CONN PROC INIT)

Iterates over the entire result set (regardless of any rows that may have already been returned by mysql-fetch-row), calling PROC on each row.

Returns the final accumulated value.

PROC must take three arguments: the row (as described) for mysql-fetch-row, the index of that row in the result set, starting with 1 and ending with (mysql-num-rows CONN), and the current accumulated value (initially INIT).

PROC must return a value.

procedure: (mysql-row-for-each CONN PROC)

Iterates over the entire result set (regardless of any rows that may have already been returned by mysql-fetch-row), calling PROC on each row.

PROC must take three arguments: the row (as described) for mysql-fetch-row, and the index of that row in the result set, starting with 1 and ending with (mysql-num-rows CONN).

procedure: (mysql-row-map CONN PROC)

Iterates over the entire result set (regardless of any rows that may have already been returned by mysql-fetch-row), calling PROC on each row.

Returns the list of results of the PROC invocations.

PROC must take three arguments: the row (as described) for mysql-fetch-row, and the index of that row in the result set, starting with 1 and ending with (mysql-num-rows CONN).

PROC must return a value.

procedure: (mysql-query-fold CONN QUERY PROC INIT)

Combines mysql-query and mysql-row-fold.

procedure: (mysql-query-for-each CONN QUERY PROC)

Combines mysql-query and mysql-row-for-each.

procedure: (mysql-query-map CONN QUERY PROC)

Combines mysql-query and mysql-row-map.

procedure: (mysql-foreach-row CONN BODY)

Synonym for mysql-row-for-each.

procedure: (mysql-query-foreach CONN QUERY BODY)

Synonym for mysql-query-for-each.

MYSQL_FIELD Access

procedure: (mysql-fetch-field CONN)

Returns a mysql-field-ptr to the next field, or #f.

procedure: (mysql-fetch-fields CONN)

Returns a mysql-field-ptr to the first field, or #f.

procedure: (mysql-fetch-field-direct CONN MYSQL-FIELD-NUMBER)

Returns a mysql-field-ptr to a specific field, or #f.

procedure: (mysql-field-slots MYSQL-FIELD-POINTER MYSQL-FIELD-GETTER ...)

Returns a list of MYSQL_FIELD entry values, from each MYSQL-FIELD-GETTER.

MYSQL-FIELD-POINTER is a pointer to a MYSQL_FIELD, or #f. MYSQL-FIELD-GETTER is a mysql-field-* reference function.

procedure: (mysql-fetch-field-slot-direct CONN FIELD-NUMBER MYSQL-FIELD-GETTER)

Returns a field slot value. Combines mysql-fetch-field-direct and mysql-field-slots.

procedure: (mysql-fetch-field-slot CONN MYSQL-FIELD-GETTER)

Returns a field slot value. Combines mysql-fetch-field and mysql-field-slots.

procedure: (mysql-fetch-field-slots-direct CONN FIELD-NUMBER MYSQL-FIELD-GETTER ...)

Returns a list of field slot values. Combines mysql-fetch-field-direct and mysql-field-slots.

procedure: (mysql-fetch-field-slots CONN MYSQL-FIELD-GETTER ...)

Returns a list of field slot values. Combines mysql-fetch-field and mysql-field-slots.

Slot Getters
procedure: (mysql-field-org-name MYSQL-FIELD-POINTER)

Returns string.

procedure: (mysql-field-name MYSQL-FIELD-POINTER)

Returns string.

procedure: (mysql-field-org-table MYSQL-FIELD-POINTER)

Returns string.

procedure: (mysql-field-org-table MYSQL-FIELD-POINTER)

Returns string.

procedure: (mysql-field-db MYSQL-FIELD-POINTER)

Returns string.

procedure: (mysql-field-catalog MYSQL-FIELD-POINTER)

Returns string.

procedure: (mysql-field-def MYSQL-FIELD-POINTER)

Returns string.

procedure: (mysql-field-table MYSQL-FIELD-POINTER)

Returns string.

procedure: (mysql-field-type MYSQL-FIELD-POINTER)

Returns enum enum_field_types value.

procedure: (mysql-field-charsetnr MYSQL-FIELD-POINTER)

Returns unsigned-integer.

procedure: (mysql-field-flags MYSQL-FIELD-POINTER)

Returns unsigned-integer.

procedure: (mysql-field-decimals MYSQL-FIELD-POINTER)

Returns unsigned-integer.

procedure: (mysql-field-def-length MYSQL-FIELD-POINTER)

Returns unsigned-integer.

procedure: (mysql-field-catalog-length MYSQL-FIELD-POINTER)

Returns unsigned-integer.

procedure: (mysql-field-db-length MYSQL-FIELD-POINTER)

Returns unsigned-integer.

procedure: (mysql-field-org-table-length MYSQL-FIELD-POINTER)

Returns unsigned-integer.

procedure: (mysql-field-table-length MYSQL-FIELD-POINTER)

Returns unsigned-integer.

procedure: (mysql-field-org-name-length MYSQL-FIELD-POINTER)

Returns unsigned-integer.

procedure: (mysql-field-name-length MYSQL-FIELD-POINTER)

Returns unsigned-integer.

procedure: (mysql-field-max-length MYSQL-FIELD-POINTER)

Returns unsigned-integer.

procedure: (mysql-field-length MYSQL-FIELD-POINTER)

Returns unsigned-integer.

Field Flag Testing

procedure: (mysql-field-flags-on? MYSQL-FIELD-POINTER MYSQL-FIELD-FLAG ...)

Does the field, MYSQL-FIELD-POINTER, have all of the MYSQL-FIELD-FLAG ... set?

procedure: (mysql-field-flags-off? MYSQL-FIELD-POINTER MYSQL-FIELD-FLAG ...)

Does the field, MYSQL-FIELD-POINTER, have all of the MYSQL-FIELD-FLAG ... not set?

procedure: (mysql-field-primary-key? MYSQL-FIELD-POINTER)

Is the field, MYSQL-FIELD-POINTER, a primary key?

procedure: (mysql-field-not-null? MYSQL-FIELD-POINTER)

Is the field, MYSQL-FIELD-POINTER, not null?

procedure: (mysql-field-binary? MYSQL-FIELD-POINTER)

Is the field, MYSQL-FIELD-POINTER, binary?

procedure: (mysql-field-numeric? MYSQL-FIELD-POINTER)

Is the field, MYSQL-FIELD-POINTER, numeric?

Field Type Testing

procedure: (mysql-field-type-any? MYSQL-FIELD-POINTER MYSQL-TYPE ...)

Is the field, MYSQL-FIELD-POINTER, type any of MYSQL-TYPE ...?

procedure: (mysql-field-type=? MYSQL-FIELD-POINTER MYSQL-TYPE)

Is the field, MYSQL-FIELD-POINTER, MYSQL-TYPE?

procedure: (mysql-field-type-clock? MYSQL-FIELD-POINTER)

Is the field, MYSQL-FIELD-POINTER, type time or date related?

procedure: (mysql-field-type-number? MYSQL-FIELD-POINTER)

Is the field, MYSQL-FIELD-POINTER, type a number?

procedure: (mysql-field-type-blob? MYSQL-FIELD-POINTER)

Is the field, MYSQL-FIELD-POINTER, type some kind of blob?

procedure: (mysql-field-type-string? MYSQL-FIELD-POINTER)

Is the field, MYSQL-FIELD-POINTER, type some kind of string?

procedure: (mysql-field-type-magnitude? MYSQL-FIELD-POINTER)

Is the field, MYSQL-FIELD-POINTER, type some kind of number?

procedure: (mysql-field-type-binary? MYSQL-FIELD-POINTER)

Is the field, MYSQL-FIELD-POINTER, type some kind of binary string or blob?

procedure: (mysql-field-type-text? MYSQL-FIELD-POINTER)

Is the field, MYSQL-FIELD-POINTER, type some kind of non-binary string or blob?

Character Set Access

procedure: (mysql-set-character-set CONN CHARACTER-SET-NAME)

Sets the current character set.

procedure: (mysql-character-set-name CONN)

Returns a string.

procedure: (mysql-get-character-set-info CONN)

Returns a MY-CHARSET-INFO-POINTER.

Slot Getters
procedure: (my-charset-info-name MY-CHARSET-INFO-POINTER)

Return a string.

procedure: (my-charset-info-csname MY-CHARSET-INFO-POINTER)

Return a string.

procedure: (my-charset-info-comment MY-CHARSET-INFO-POINTER)

Return a string.

procedure: (my-charset-info-dir MY-CHARSET-INFO-POINTER)

Return a string.

procedure: (my-charset-info-mbminlen MY-CHARSET-INFO-POINTER)

Return a number.

procedure: (my-charset-info-mbmaxlen MY-CHARSET-INFO-POINTER)

Return a number.

Miscellaneous Procedures

procedure: (mysql-change-user CONN [KEYWORD ...])

Changes the user identity.

Any number of the following KEYWORD ... may be included:

  • user
  • passwd
  • db

procedure: (mysql-debug DEBUG)

.

procedure: (mysql-dump-debug-info CONN)

.

procedure: (mysql-errno CONN)

.

procedure: (mysql-error CONN)

Returns a string describing the last mysql error, or #f if no error has occurred.

procedure: (mysql-escape-string CONN SQL)

Returns an escape encoded form of the SQL string.

procedure: (mysql-free-result CONN)

.

procedure: (mysql-get-client-info)

.

procedure: (mysql-get-client-version)

.

procedure: (mysql-get-host-info CONN)

.

procedure: (mysql-get-proto-info CONN)

.

procedure: (mysql-get-server-info CONN)

.

procedure: (mysql-get-server-version CONN)

.

procedure: (mysql-info CONN)

.

procedure: (mysql-insert-id CONN)

.

procedure: (mysql-kill CONN PID)

.

procedure: (mysql-list-dbs CONN LIKE)

.

procedure: (mysql-list-fields CONN TABLE WILD)

.

procedure: (mysql-list-processes CONN)

.

procedure: (mysql-list-tables CONN WILD)

.

procedure: (mysql-num-fields CONN)

.

procedure: (mysql-num-rows CONN)

.

procedure: (mysql-ping CONN)

.

procedure: (mysql-select-db CONN DB)

Returns #t if the select was successful, signals exception otherwise.

procedure: (mysql-stat CONN)

.

procedure: (mysql-store-result CONN)

.

procedure: (mysql-thread-id CONN)

.

Enumerations & Flags

Client Flags

Symbolic Value
client-compress CLIENT_COMPRESS
client-found-rows CLIENT_FOUND_ROWS
client-ignore-sigpipe CLIENT_IGNORE_SIGPIPE
client-ignore-space CLIENT_IGNORE_SPACE
client-interactive CLIENT_INTERACTIVE
client-local-files CLIENT_LOCAL_FILES
client-multi-results CLIENT_MULTI_RESULTS
client-multi-statements CLIENT_MULTI_STATEMENTS
client-no-schema CLIENT_NO_SCHEMA
client-odbc CLIENT_ODBC
client-ssl CLIENT_SSL

procedure: (mysql-client-flags-value SYMBOL ...)

Returns the or'ed value of the SYMBOL ... values.

procedure: (mysql-client-flags-symbol NUMBER)

Returns the symbol for the NUMBER.

enum enum_mysql_set_option

Symbolic Value
mysql-option-multi-statements-on MYSQL_OPTION_MULTI_STATEMENTS_ON
mysql-option-multi-statements-off MYSQL_OPTION_MULTI_STATEMENTS_OFF

procedure: (mysql-server-option-value SYMBOL ...)

Returns the or'ed value of the SYMBOL ... values.

procedure: (mysql-server-option-symbol NUMBER)

Returns the symbol for the NUMBER.

enum mysql_option

Symbolic Value
mysql-opt-connect-timeout MYSQL_OPT_CONNECT_TIMEOUT
mysql-opt-compress MYSQL_OPT_COMPRESS
mysql-opt-named-pipe MYSQL_OPT_NAMED_PIPE
mysql-init-command MYSQL_INIT_COMMAND
mysql-read-default-file MYSQL_READ_DEFAULT_FILE
mysql-read-default-group MYSQL_READ_DEFAULT_GROUP
mysql-set-charset-dir MYSQL_SET_CHARSET_DIR
mysql-set-charset-name MYSQL_SET_CHARSET_NAME
mysql-opt-local-infile MYSQL_OPT_LOCAL_INFILE
mysql-opt-protocol MYSQL_OPT_PROTOCOL
mysql-shared-memory-base-name MYSQL_SHARED_MEMORY_BASE_NAME
mysql-opt-read-timeout MYSQL_OPT_READ_TIMEOUT
mysql-opt-write-timeout MYSQL_OPT_WRITE_TIMEOUT
mysql-opt-use-result MYSQL_OPT_USE_RESULT
mysql-opt-use-remote-connection MYSQL_OPT_USE_REMOTE_CONNECTION
mysql-opt-use-embedded-connection MYSQL_OPT_USE_EMBEDDED_CONNECTION
mysql-opt-guess-connection MYSQL_OPT_GUESS_CONNECTION
mysql-set-client-ip MYSQL_SET_CLIENT_IP
mysql-secure-auth MYSQL_SECURE_AUTH
mysql-report-data-truncation MYSQL_REPORT_DATA_TRUNCATION

procedure: (mysql-option-value SYMBOL ...)

Returns the or'ed value of the SYMBOL ... values.

procedure: (mysql-option-symbol NUMBER)

Returns the symbol for the NUMBER.

enum enum_field_types

Symbolic Value
mysql-type-decimal MYSQL_TYPE_DECIMAL
mysql-type-tiny MYSQL_TYPE_TINY
mysql-type-short MYSQL_TYPE_SHORT
mysql-type-long MYSQL_TYPE_LONG
mysql-type-float MYSQL_TYPE_FLOAT
mysql-type-double MYSQL_TYPE_DOUBLE
mysql-type-null MYSQL_TYPE_NULL
mysql-type-timestamp MYSQL_TYPE_TIMESTAMP
mysql-type-longlong MYSQL_TYPE_LONGLONG
mysql-type-int24 MYSQL_TYPE_INT24
mysql-type-date MYSQL_TYPE_DATE
mysql-type-time MYSQL_TYPE_TIME
mysql-type-datetime MYSQL_TYPE_DATETIME
mysql-type-year MYSQL_TYPE_YEAR
mysql-type-newdate MYSQL_TYPE_NEWDATE
mysql-type-varchar MYSQL_TYPE_VARCHAR
mysql-type-bit MYSQL_TYPE_BIT
mysql-type-newdecimal MYSQL_TYPE_NEWDECIMAL
mysql-type-enum MYSQL_TYPE_ENUM
mysql-type-set MYSQL_TYPE_SET
mysql-type-tiny-blob MYSQL_TYPE_TINY_BLOB
mysql-type-medium-blob MYSQL_TYPE_MEDIUM_BLOB
mysql-type-long-blob MYSQL_TYPE_LONG_BLOB
mysql-type-blob MYSQL_TYPE_BLOB
mysql-type-var-string MYSQL_TYPE_VAR_STRING
mysql-type-string MYSQL_TYPE_STRING
mysql-type-geometry MYSQL_TYPE_GEOMETRY

procedure: (mysql-type-value SYMBOL ...)

Returns the or'ed value of the SYMBOL ... values.

procedure: (mysql-type-symbol NUMBER)

Returns the symbol for the NUMBER.

MYSQL_FIELD.flags Flags

Symbolic Value
not-null-flag NOT-NULL-FLAG
pri-key-flag PRI-KEY-FLAG
unique-key-flag UNIQUE-KEY-FLAG
multiple-key-flag MULTIPLE-KEY-FLAG
unsigned-flag UNSIGNED-FLAG
zerofill-flag ZEROFILL-FLAG
binary-flag BINARY-FLAG
auto-increment-flag AUTO-INCREMENT-FLAG
no-default-value-flag NO-DEFAULT-VALUE-FLAG

procedure: (mysql-field-flags-value SYMBOL ...)

Returns the or'ed value of the SYMBOL ... values.

procedure: (mysql-field-flags-symbol NUMBER)

Returns the symbol for the NUMBER.

Examples

A bulky usage might look like:

(use mysql)

(let ((db (mysql-connect host: "mysql.example.com" user: "example" passwd: "secret")))
  (mysql-query db "SHOW DATABASES")
  (do ((row (mysql-fetch-row db) (mysql-fetch-row db)))
      ((not row))
    (print "Row " idx ": " (row "Database")) )
  (mysql-close db) )

A slightly more compact version that does the same thing:

(use mysql)

(let ((db (mysql-connect host: "mysql.example.com" user: "example" passwd: "secret")))
  (mysql-query-for-each
   db "SHOW DATABASES" (lambda (row idx) (print "Row " idx ": " (row "Database"))))
  (mysql-close db) )

Data Type Conversion

All MySQL result data (except NULL) are returned as Scheme strings.

The NULL value is represented by #f.

Booleans are expressed as the Scheme strings "1" and "0".

All remaining types, including numeric types, blobs, and strings are returned as Scheme strings.

Bugs

No prepared statement support.

This is alpha quality software. Only very basic functionality has been tested so far. I look forward to providing a more complete test suite (and probably a slew of bugfixes) with the next release.

mysql-escape-string is broken when it's used for binary data.

I need to nail down the supported libmysqlclient versions. Right now there are some functions I've put off because they may or may not be supported in my target range. We'll see, soon...

License

Copyright (c) 2005 Toby Butzon.

Permission is hereby granted, free of charge, to any person obtaining a
copy of this software and associated documentation files (the "Software"),
to deal in the Software without restriction, including without limitation
the rights to use, copy, modify, merge, publish, distribute, sublicense,
and/or sell copies of the Software, and to permit persons to whom the
Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included
in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR
OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
OTHER DEALINGS IN THE SOFTWARE.