Chapter 7. Interaction with SQL Databases

Chapter 7. Interaction with SQL Databases
Prev		Next

The SQL library performs automatic conversion between scheme and database types. Since the exact conversion table depends on the vendor, a mechanism is provided to add support for other vendors though vendor-specific modules.

Note

This library is not yet complete, but it covers the most frequently used functionalities. It should be considered useful but immature; in particular, a few names and function signatures are subject to change.

JDBC Functions

Requires: (import sql/jdbc)
Located in: siscweb-sql.jar

This module provides functions to load drivers, obtain connections, and execute procedures within a transactional context.

procedure: (jdbc/load-driver name) => #t/#f
Loads the JDBC driver specified by name. It is not necessary to use this function when obtaining a connection from a JDBC DataSource, or when using a plug-in, vendor-specific module. Returns a boolean representing the success of the operation.

(jdbc/load-driver "org.hsqldb.jdbcDriver")

procedure: (jdbc/get-connection datasource) => connection
Obtains a connection from the specified JDBC datasource.

(import util/jndi) (jdbc/get-connection (jndi/lookup "java:/comp/env/jdbc/myDS"))

procedure: (jdbc/open-connection url username password) => connection
Opens and returns a connection to the database specified by the url parameter, logging in with the given username and password. The url is in the same jdbc:subprotocol:subname format as in the Java JDBC API.

(jdbc/open-connection "jdbc:hsqldb:file:/tmp/my-db" "sa" "")

procedure: (jdbc/close-connection connection) => #!void
Closes the specified connection.

procedure: (jdbc/get-vendor connection) => vendor-name
Returns the vendor name, in lowercase, of the database to which connection points.

procedure: (jdbc/call-with-connection connection proc) => value procedure: (jdbc/call/conn connection procedure) => value
Invokes procedure with the given connection. When the procedure terminates or fails, the connection is automatically close()ed. The passed procedure must accept a connection as its only argument. The value returned will be that returned by procedure.

(jdbc/call/conn (jdbc/get-connection (jndi/lookup "java:/comp/env/jdbc/myDS")) (lambda (conn) (sql/execute conn "SELECT SYSDATE FROM DUAL")))

procedure: (jdbc/call-with-transaction connection proc) => value
procedure: (jdbc/call/tran connection procedure) => value
Invokes procedure within a transactional context. The transaction is committed if procedure returns normally, and rolled back in case of failure or invocation of an escape continuation. The passed procedure must accept connection as its only argument. The value returned will be that returned by procedure.

(jdbc/call/conn (jdbc/get-connection (jndi/lookup "java:/comp/env/jdbc/myDS")) (lambda (conn) (jdbc/call/tran conn (lambda (conn) (sql/execute-update conn "UPDATE my_table SET my_field = 0")))))

Running Queries

Requires: (import sql/query)
Located in: siscweb-sql.jar

This module provides functions to execute queries and map through result sets.

Note

JDBC ResultSets are translated to lazy lists (in the sense of srfi-45) of SISC hashtables. When the last element of the list is fetched (or in case of error during fetching), the underlying JDBC ResultSet and Statement are automatically closed. In case of premature escape from the jdbc/call/conn context, intentional or not, the JDBC ResultSet and Statement are closed only upon garbage collection. This can be a particularly insidious problem when using pooled connections, and will be fixed in the future.

procedure: (sql/execute connection sql-query [value] ...) => number/resultset
Executes sql-query through the given connection.
A number of optional values can be specified; these will be bound in order to the placeholders in the query. Date, time and timestamps from srfi-19 can be used to bind to SQL DATE, TIME and TIMESTAMP types.
Returns the number of rows updated in case of an INSERT/UPDATE/DELETE query statement, and a result set in case of a SELECT statement. Result sets are lazy lists in the sense of srfi-45. Each element of the list is a hashtable (field-name => value). Multiple result sets are not supported.
          
(sql/execute conn "SELECT * FROM my_table WHERE id = ?" 12345)
            
        

procedure: (sql/execute-query connection sql-query [value] ...) => resultset
Runs sql-query and returns a result set.

procedure: (sql/execute-update connection sql-query [value] ...) => number
Runs sql-query and returns the number of updated rows.

syntax: (sql/map-row procedure ((variable field-name) ...) body) => list
syntax: (sql/for-each-row procedure ((variable field-name) ...) body) => undefined
These two macros facilitate writing code that loops through a resultset. Like their correspectives map and for-each, the former collects results into a list, while the latter is to be used for side-effects.
For each row in the resultset, a Scheme variable is bound to the value of the indicated field-name in the resultset, where field-name is either a string or a symbol.
In the case of sql/map-row, the value returned by the last expression in body is collected into the returned list.

(sql/for-each-row (sql/execute-query conn "SELECT id, name FROM person") ((id 'id) (name 'name)) (display (format "~a:~a\n" id name)))

Importing this module registers type conversions between Oracle and Scheme. At this time numbers, strings (but not CLOBs), and time types are supported. BLOBs are returned as such, but two functions are provided to use them as containers of Java objects.

procedure: (oracle/read-blob blob) => |java.lang.Object|

Deserializes a Java object from blob.

            
;; returns an alist (id => object)
(sql/map-row (sql/execute-query "SELECT id, obj FROM my_table")
    ((id 'id)
     (blob 'obj))
  `(,id . ,(oracle/read-blob blob)))

procedure: (oracle/write-blob blob java-object) => undefined
Serializes java-object into blob. Complex Scheme values can also be serialized by java-wrapping them. The blob has to be first inserted using the Oracle EMPTY_BLOB() function, and then read back with a SELECT query.

(sql/for-each-row (sql/execute-query "SELECT id, obj FROM my_table WHERE id = ?" 12345) ((blob 'obj)) (oracle/write-blob blob (java-wrap (lambda (x) (* x x)))))