Version: 1.05 beta
Original location:
Source: mysql.lsp
Package definition: mysql.qwerty
A new MySQL module to replace the distribution standard module (requires newlisp 10).
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.
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.
Changelog
1.05
• Mysql:query now checks if client mistakenly sent single, non-list, argument for format-args
1.04
• fixed error in documentation example
• changed Mysql:query to allow lists as format parameters
• backward-incompatible change to Mysql:query parameter list
• added Mysql:coerce-type as an independent function
1.03
• fixed truncation bug when inserting binary data in Mysql:query
1.02
• field types are now correctly distinguished when MySQL is compiled with 64-bit pointers
• refactored MysqlResult:get-row
1.01
• fixed invalid function in Mysql:tables, Mysql:fields, and Mysql:databases
1.0
• initial release
Known bugs
• None (at the moment); please let me know if you find any!
example: • Imperative usage
(setf db (Mysql)) ; initialize Mysql instance
(:connect db "localhost" "user" "secret" "my_database") ; connect to a server
(setf result (:query db "SELECT * FROM some_table")) ; evaluate a query
(setf rows (:fetch-all result)) ; generate a result
(:close-db db) ; free the database
• Functional usage with the 'mysql context
(mysql:on-connect '("localhost" "user" "secret" "my_database")
(lambda (db err)
(if err (throw-error err))
(mysql:row-iter db "SELECT * FROM some_table" nil
(lambda (row)
(println row)))))
- § -
Mysql
syntax: (Mysql)
Returns a new Mysql instance that can safely be used in tandem with other
Mysql instances.
- § -
:connect
syntax: (:connect Mysql-instance str-host str-user str-pass str-db int-port str-socket)
parameter: Mysql-instance - an instance of the Mysql class
parameter: str-host - the hostname to connect to
parameter: str-user - a MySQL username
parameter: str-pass - str-user's password
parameter: str-db - the database to initially connect to
parameter: int-port - (optional) port number of the MySQL server
parameter: int-str - (optional) socket file to connect through
Connects an initialized Mysql instance to a database. Returns true if
successful logging in, nil if not.
example: (setf db (Mysql))
(:connect db "localhost" "user" "secret" "my-database")
=> true
- § -
:close
syntax: (:close Mysql-instance)
parameter: Mysql-instance - an instance of the Mysql class
Closes the connection and frees any memory used. This does not free the memory
used by results sets from this connection.
- § -
:error
syntax: (:error Mysql-instance)
parameter: Mysql-instance - an instance of the Mysql class
Returns the last error message as a string or nil if there is none.
- § -
:coerce-type
syntax: (:coerce-type Mysql-instance object)
parameter: Mysql-instance - an instance of the Mysql class
parameter: object - a newLisp object
Coerces object into something safe to use in a SQL statement. Lists are
converted into MySQL lists (e.g. '("foo" "bar" "baz") to
(foo, bar, baz)) and string values are escaped. This is a helper
function for Mysql:query.
- § -
:query
syntax: (:query Mysql-instance str-statement [lst-format-args])
parameter: Mysql-instance - an instance of the Mysql class
parameter: str-statement - a SQL statement to execute
parameter: lst-format-args - format arguments to the SQL statement
Executes str-statement. Throws an error if the statement fails with the
reason. If the statement returns results, a MysqlResult class instance is
returned. Otherwise, returns the number of affected rows.
If lst-format-args is specified, all parameters are escaped (as
necessary) to generate safe, valid SQL. No quoting of values is required in
the format string; quotes are inserted as needed. To generate a
NULL in the SQL statement, pass nil or the string "NULL".
example: (:query db "SELECT name, employee_id FROM employees")
=> (MysqlResult 1069216)
(:query db "DELETE FROM employees WHERE fired = 1")
=> 14
(:query db '("SELECT id FROM employees WHERE name = %s" '("Johnson, John")))
; SQL generated: SELECT id FROM employees WHERE name = 'Johnson, John'
=> (MysqlResult 1069216)
- § -
:insert-id
syntax: (:insert-id Mysql-instance)
parameter: Mysql-instance - an instance of the Mysql class
Returns the id of the last inserted row when the target table contains
an AUTOINCREMENT field.
- § -
:affected-rows
syntax: (:affected-rows Mysql-instance)
parameter: Mysql-instance - an instance of the Mysql class
Returns the number of rows affected by the most recent query.
- § -
:escape
syntax: (:escape Mysql-instance str-value)
parameter: Mysql-instance - an instance of the Mysql class
parameter: str-value - the string to escape
Escapes a string to assure safety for use in a SQL statement.
- § -
:databases
syntax: (:databases Mysql-instance)
parameter: Mysql-instance - an instance of the Mysql class
Returns a list of the databases on this server.
- § -
:tables
syntax: (:tables Mysql-instance str-database)
parameter: Mysql-instance - an instance of the Mysql class
parameter: str-database - (optional) the database to query for tables
Returns a list of tables available on this server. If str-database is
provided, the list of tables will be limited to that database.
- § -
:fields
syntax: (:fields Mysql-instance str-table)
parameter: Mysql-instance - an instance of the Mysql class
parameter: str-table - the table to display
Returns metadata about the fields in str-table. The data is the result
of a SHOW FIELDS query.
- § -
MysqlResult
syntax: (MysqlResult int-pointer)
parameter: int-pointer - a pointer to a MYSQL_RES struct
Objects of this class are returned by Mysql:query as a result of queries
that generate result sets. This class is not generally instantiated directly
by the client.
- § -
:free
syntax: (:free MysqlResult-instance)
parameter: MysqlResult-instance - an instance of the MysqlResult class
Frees the memory used by a result. Must be called for each MysqlResult
generated, even if unused.
- § -
:num-rows
syntax: (:num-rows MysqlResult-instance)
parameter: MysqlResult-instance - an instance of the MysqlResult class
Returns the number of results in this result.
- § -
:fields
syntax: (:fields MysqlResult-instance)
parameter: MysqlResult-instance - an instance of the MysqlResult class
Returns a list of MysqlField instances corresponding to the columns in
this result.
- § -
:fetch-row
syntax: (:fetch-row MysqlResult-instance as-assoc)
parameter: MysqlResult-instance - an instance of the MysqlResult class
parameter: as-assoc - (optional) whether to return results as a list or association list
Returns one row from this result. If as-assoc is true, the results will
be returned as an association list (true by default). If this is the final row
in the result set, the MysqlResult instance is automatically freed.
- § -
:fetch-all
syntax: (:fetch-all MysqlResult-instance as-assoc)
parameter: MysqlResult-instance - an instance of the MysqlResult class
parameter: as-assoc - (optional) whether to return results as a list or association list
Returns all rows from this result. If as-assoc is true, the results
will be returned as an association list (true by default).
- § -
MysqlField
syntax: (MysqlField int-pointer)
parameter: int-pointer - a pointer to a MYSQL_FIELD struct
Objects of this class are returned by MysqlResult:fields. It is used
internally in generating result rows. This class is not generally
instantiated directly by the client.
- § -
:name
syntax: (:name MysqlField-instance)
parameter: MysqlField-instance - an instance of the MysqlField class
Returns the name of this field (or its alias).
- § -
:table
syntax: (:table MysqlField-instance)
parameter: MysqlField-instance - an instance of the MysqlField class
Returns this field's table (or its alias).
- § -
:type
syntax: (:type MysqlField-instance)
parameter: MysqlField-instance - an instance of the MysqlField class
Returns this field's type.
- § -
mysql:on-connect
syntax: (mysql:on-connect list-credentials fn-callback)
parameter: list-credentials - a list of parameters to pass to Mysql:connect
parameter: fn-callback - a function to call with the database connection
Connects to a MySQL server using list-credentials and calls
fn-callback using the Mysql instance as the first argument. If an
error occurred attempting connection, the error string is passed as the
second parameter. The minimum contents of list-credentials must be
'(str-host str-username str-password str-database).
The connection is automatically freed when mysql:on-connect returns.
example: (mysql:on-connect '("localhost" "user" "secret" "my_database")
(lambda (db err)
(if err
(println "Error! " err)
(println "Success! " db))))
- § -
mysql:row-iter
syntax: (mysql:row-iter Mysql-instance str-sql bool-as-assoc fn-callback)
parameter: Mysql-instance - a connect instance of the Mysql class
parameter: str-sql - a sql statement
parameter: bool-as-assoc - flags whether or not to pass rows as regular or association lists
parameter: fn-callback - a function to call for each row returned by the query
Iterates over the results of a query, passing a row at a time to
fn-callback. The MysqlResult is automatically freed. The return value
of mysql:row-iter is the result of the last call to fn-callback.
Note that each row is called with MysqlResult:fetch-row to avoid building
intermediate lists.
example: (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))))))
- § -
mysql:row-map
syntax: (mysql:row-map Mysql-instance str-sql bool-as-assoc fn-callback)
parameter: Mysql-instance - a connect instance of the Mysql class
parameter: str-sql - a sql statement
parameter: bool-as-assoc - flags whether or not to pass rows as regular or association lists
parameter: fn-callback - a function to apply to each row returned by the query
Maps fn-callback over each row returned by querying Mysql-instance
with str-sql. Memory used by the MysqlResult is automatically freed.
Returns a list of the result of applying fn-callback to each row.
example: (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 first))))
- § -
mysql:reduce-results
syntax: (mysql:reduce-results Mysql-instance str-sql bool-as-assoc fn-callback)
parameter: Mysql-instance - a connect instance of the Mysql class
parameter: str-sql - a sql statement
parameter: bool-as-assoc - flags whether or not to pass rows as regular or association lists
parameter: fn-callback - a function to be applied in reducing the results of the query
Reduces the results of the query by applying fn-callback successively
to slices of the list of rows from the left. On the first call to
fn-callback, the arguments will be a number of rows equal to the number of
parameters that fn-callback accepts. On each subsequent call, the first
parameter will be replaced by the result of the previous call. See the
apply function for a more detailed description of the mechanics of apply/reduce. The return
value is the result of the final application of fn-callback.
example: (mysql:on-connect '("localhost" "user" "secret" "my_database")
(lambda (db err)
(if err
(println "Error! " err)
(mysql:row-reduce db "SELECT * FROM some_table" true
(lambda (row-1 row-2)
(+ (if (list? row-1) (first row-1) row-1) (first row-2)))))))
- ∂ -
generated with newLISP
and newLISPdoc