After a few weeks wrestling with the MySQL protocol, I've finally managed to produce a reasonable attempt at a client for Gambit (and probably other Scheme's).
This is a pure Scheme implementation rather than using Gambit's C interface to glue the existing MySQL C API into Gambit. The reason for this is that Gambit's power, and in my view fantastic potential as a web platform, comes from it's lightweight threads which allows Gambit to process thousands of concurrent requests in a single OS thread. This is all acheived by an internal schedular, continuations and very careful use of non-blocking calls on all I/O functions. Using the C API effectively takes the OS thread out of Gambit and into the C code and therefore all of those lovely threads stop executing until your call is completed - seriously bad news for a web application.
This library is now around 80% complete and supports the following features :-
- Multiple concurrent connections to MySQL,
- Authentication using both the old and new authentication methods,
- Switching Schema's,
- Dynamic queries, updates, deletes etc.
- Prepared statement creation,
- Executing prepared statements (queries and updates) with bind variables for most datatypes,
- Closing prepared statements,
- Closing connections.
There are a few limitations with the driver, i.e. features that aren't implemented yet :-
- Not all datatypes are supported yet in prepared statements e.g. floats, doubles, sets, enums and one or two others. Some of these can easily be avoided e.g. by using DECIMAL instead of FLOAT or DOUBLE.
- BLOB's and large character fields are not yet supported in either dynamic or prepared statements.
- Large result sets requiring supplementary fetches aren't supported as I haven't implemented the additional fetch function.
The library is in the Gambit Dumping Grounds. Here's an example of how you use it :-
(with-connection (sql-connect "localhost" 3306 "test" "fred" "fred")
;;(mysql-execute "drop table test")
(mysql-execute "create table test (id integer PRIMARY KEY AUTO_INCREMENT, name varchar(50), inserted datetime, amount decimal(10,2) DEFAULT -4.5)")
(pp (sql-execute "select * from test"))
;; Prepared Statements
(let ((stmt1 (sql-prepare-statement "insert into test (name, amount, inserted) values (?, ?, sysdate())"))
(stmt2 (sql-prepare-statement "select id, name, amount, inserted from test where amount > ? order by id")))
(pp (sql-execute-prepared-statement stmt1 '("Fred" 10.5)))
(pp (sql-execute-prepared-statement stmt1 '("Joe" 5.5)))
(pp (sql-execute-prepared-statement stmt2 '(4.5)))
;; Close connection
Result sets are returned as a list of lists i.e. a list of column values for each row in a list for the result set. The first row in the list is the column headings. For dynamic statements, the column data is always a string regardless of the SQL type because this is the way that MySQL sends it and I haven't implemented any type conversions yet. Prepared statements return binary types so I've mapped some of these e.g. strings and numbers into their respective Scheme types.
I would strongly recommend using prepared statements rather than dynamic statements for performance reasons and also for their natural resistance to SQL injection attacks in web applications.
Once the library is finished, I'll create a persistence layer that builds on top of the prepared statements to give a framework similar to ActiveRecord in Rails or Hibernate in Java.