SQL & ODBC

next: Web applications previous: Markup languages top: Contents

Spark has simple and powerful APIs to access and manipulate data stored in Relational databases. We can pass standard SQL queries to these APIs and get back the results formatted as Spark objects. Spark has a built-in database, which is a wrapper on SQLite. We also have an interface to ODBC, so that Spark programmers can make use of all ODBC compliant database servers. The interface for both the SQLite and ODBC libraries are same. Let us look at the SQLite library first.

To open a database connection, we need to create a database object with the db procedure. Call db-open on the new database object to actually establish the connection. Once we are done, the connection should be released using db-close.

(import (sql-sqlite))

(define book-store (db))
(db-open book-store "my-books")

;; Do database stuff here.

(db-close book-store)

db-open can take n number of optional arguments. The actual number of arguments depends on the type of database we are connecting to. For SQLite, the only required argument is the database file name.

db-execute allows the direct execution of SQL statements on an open database object:

;; Create a table to store information about books.
(db-execute book-store "CREATE TABLE books(isbn CHAR(10) PRIMARY KEY, title VARCHAR, author VARCHAR, publisher VARCHAR)")

;; Insert two rows to our new 'books' table.
(db-execute book-store "INSERT INTO books VALUES('0201485419', 'Art of Computer Programming, The, Volumes 1-3 Boxed Set (2nd Edition)', 'Donald Knuth', 'Addison-Wesley Professional')")
(db-execute book-store "INSERT INTO books VALUES('0262010771', 'Structure and Interpretation of Computer Programs', 'Hal Abelson Jerry Sussman and Julie Sussman', 'MIT Press')")

A SELECT query is executed on a statement object. The following code shows how to retrieve data from database tables:

(define stmt (db-create-statement book-store "select * from books"))
(define column-count (result-column-count stmt))
(printf "Number of columns in result: ~a~n" column-count)
(for i in (range column-count)
     (printf "~a," (result-column-name stmt i)))
(newline)

(while (result-next stmt)
       (for i in (range column-count)
            (printf "~a," (result-string stmt i)))
       (newline))

(statement-dispose stmt)

A prepared statement is a precompiled SQL query stored in the database server. These statements will contain placeholders (represented by ?) which will be replaced by actual values when the statement is executed. Prepared statements are efficient and secure replacements for ad hoc queries. (The MySQL documentation gives a good description of prepared statements. You can read it here).

Let us see how prepared statements can be used in SQLite:

;; Open a database connection to a database

;; Create a prepared statement. Note the placeholders for id and name.
(define stmt (db-create-statement mydb "select * from employee where id=? and name=?"))

;; Bind the parameters later. The parameters are identified by index, starting at 0.
(statement-bind stmt 0 2089)
(statement-bind stmt 1 "Ian")

;; Execute the query and print the results.
(statement-execute stmt)
(define col-count (result-column-count stmt))
(while (result-next stmt)
       (for i in (range col-count)
            (printf "~a," (result-string stmt i))))
(newline)

;; Destroy the statement.
(statement-dispose stmt)

;; Close database connection.

The ODBC API is similar to that of SQLite. The two major differences are:

1. The library to import is sql-odbc.
   

2. The string passed to db-open will specify the ODBC connection details.

Here is a sample code that connects to the MySQL ODBC provider:

(import (sql-odbc))

(define mydb (db))
(db-open mydb "dsn=MySQL;user=vijay;password=pswd")

;; Other database API calls.

(db-close mydb)