On this page:

2 SQL Formatting Utility

 (require (planet jaz/mysql:1/format))
Provides a utility for formatting and properly escaping SQL data.

The format module exports a single procedure, format-sql, used to format and escape data for inclusion in SQL strings.

(format-sql form v ...)  string?
  form : string?
  v : any/c
Formats to an SQL string. form may contain the following formatting escapes:

If form contains an escape character not listed above, or if the number of vs does not match the number of escape sequences in form exn:fail:contract is raised.

Note that the escaping rules are MySQL-specific. They are not ANSI SQL.


  > (display
     (format-sql "SELECT ~c FROM foo WHERE id = ~i" 'foo-id 2))

  SELECT `foo-id` FROM foo WHERE id = 2

  > (display
     (format-sql "UPDATE foo SET date_performed = ~d WHERE desc = ~s"
                 (srfi-19:make-date 0 0 0 0 1 7 2009 #f)
                 "Say \"Goodnight\" now.\nTime to go."))

  UPDATE foo SET date_performed = '2009-07-01' WHERE desc = 'Say \"Goodnight\" now.\nTime to go.'

  > (display
     (format-sql "INSERT INTO binary_data (id, data) VALUES (~i, ~b)"
                 sql-null #"\0hello"))

  INSERT INTO binary_data (id, data) VALUES (NULL, x'0068656C6C6F')