---

5.16.2.2 Usage—DBI Connections

1. SQLite connect

   (defvar *connection*
     (dbi:connect :sqlite3
                  :database-name "/home/gt/test.sqlite3"))
   

2. MySQL connect

   (defvar *connection*
     (dbi:connect :mysql
                  :database-name "test"
                  :username "nobody"
                  :password "1234"))
   

3. Usage—with-connection

   (dbi:with-connection (conn :sqlite3 :database-name "/home/fukamachi/test.db")
     (let* ((query (dbi:prepare conn "SELECT * FROM People"))
            (query (dbi:execute query)))
       (loop for row = (dbi:fetch query)
             while row
             do (format t "~A~%" row))))
   

4. Executing a Query

   (let* ((query (dbi:prepare *connection*
                              "SELECT * FROM somewhere WHERE flag = ? OR updated_at > ?"))
          (query (dbi:execute query (list 0 "2011-11-01"))))
     (loop for row = (dbi:fetch query)
           while row
           ;; process "row".
           ))
   
   ;; Do it all at once
   (dbi:fetch-all (dbi:execute (dbi:prepare *connection* "SELECT * FROM somewhere WHERE flag = ? OR updated_at > ?")
                               (list 0 "2011-11-01")))
   

   dbi:do-sql is another option that prepares and executes a single statement. It returns the number of rows affected. It’s typically used for non-SELECT statements.

   (dbi:do-sql *connection*
               "INSERT INTO somewhere (flag, updated_at) VALUES (?, NOW())"
               (list 0))
   ;=> 1