with-db-rows

Arguments: (columns &key table db where other first norows return convert blob float allrowsvar rowvar nobind showsql) &rest body

This macro creates an sql select statement to retrieve information from the database. It then executes this sql statement and processes the results as specified by the arguments to the macro.

It is very important to note that all the rows are retrieved before the processing of the rows started. This means two things:

1. in the code that processes the row results you are free to execute other database commands over the same database connection.
2. if the desire is to short circuit the retrieval of data rows after looking at the first few rows returned then you should not use this macro. All rows are retrieved first before the first row is passed to user code.

We will use this sample table while we describe the meanings of the arguments to with-db-rows.

We create a table called "square" with 10 rows, each row holding a value and its square:

cl-user(19): (sql "create table square(val int, sq int)")
cl-user(20): (dotimes (i 10) 
                (sql (format nil 
                        "insert into square values(~d, ~d)" i (* i i))))

The columns argument to with-db-rows should be a list specifying which columns are to be returned. In the simplest case the columns are specified as Lisp symbols whose names correspond to the column names in the database.

cl-user(22): (with-db-rows ((val sq) :table "square") 
                (format t ">> ~s~%" (list val sq)))
>> (0 0)
>> (1 1)
>> (2 4)
>> (3 9)
>> (4 16)
>> (5 25)
>> (6 36)
>> (7 49)
>> (8 64)
>> (9 81)
nil

Listing a column name causes that column to be retrieved from the database. Then when the body of the with-db-rows is executed the Lisp symbols are bound to the successive values retrieved. The body is executed once for each row retrieved.

We have seen that a column specifier can be a symbol. It can also be a list of two or three items. The two item list is used when you want the lisp variable bound to have a different name than the column retrieved. This is usually done when the column retrieved is actually an expression and not a column name:

Here we ask the database to return a third value, the sum of the first two values. This value is bound to the lisp variable sum when the body is evaluated.

cl-user(24): (with-db-rows ((val sq (sum "val+sq")) :table "square") 
                (format t ">> ~s~%" (list val sq sum)))
>> (0 0 0)
>> (1 1 2)
>> (2 4 6)
>> (3 9 12)
>> (4 16 20)
>> (5 25 30)
>> (6 36 42)
>> (7 49 56)
>> (8 64 72)
>> (9 81 90)
nil

The column specifier can also contain a third item. This is useful when you want to give an alias for the column in the sql statement itself. This is rarely needed. Here's an example of getting an alias for a computed column so that it can be used in an 'order by' expression.

cl-user(29): (with-db-rows ((val sq (sum "val+sq" "ss")) 
                                :table "square" 
                                :other "order by ss desc") 
                  (format t ">> ~s~%" (list val sq sum)))
>> (9 81 90)
>> (8 64 72)
>> (7 49 56)
>> (6 36 42)
>> (5 25 30)
>> (4 16 20)
>> (3 9 12)
>> (2 4 6)
>> (1 1 2)
>> (0 0 0)
nil

In order to help the programmer understand and debug uses of with-db-rows, the macro takes a showsql argument which if true causes the macro to print out (at compile or eval time) the sql generated.

For example we can see the sql generated in the previous example

cl-user(30): (with-db-rows ((val sq (sum "val+sq" "ss")) 
                                :table "square" 
                                :other "order by ss desc" 
                                :showsql t) 
                (format t ">> ~s~%" (list val sq sum)))
sql: "select val,sq,val+sq ss from square  order by ss desc"
>> (9 81 90)
>> (8 64 72)
>> (7 49 56)
>> (6 36 42)
>> (5 25 30)
>> (4 16 20)
>> (3 9 12)
>> (2 4 6)
>> (1 1 2)
>> (0 0 0)
nil

Now we'll examine the where argument to with-db-rows. This argument can either be a string to be used as the where of the generated select statement or it can be an expression that is converted by the macro into a where clause.

First we will see an example of the where argument being a string

cl-user(34): (with-db-rows ((val sq (sum "val+sq" "ss")) 
                                :table "square" 
                                 :where "sq > 30"  
                                 :showsql t) 
                    (format t ">> ~s~%" (list val sq sum)))
sql: "select val,sq,val+sq ss from square where sq > 30 "
>> (6 36 42)
>> (7 49 56)
>> (8 64 72)
>> (9 81 90)
nil
cl-user(35): 

Here is the same where clause written using the lisp expression syntax.

cl-user(37): (with-db-rows ((val sq (sum "val+sq" "ss")) 
                            :table "square" 
                            :where (> sq 30) 
                            :showsql t) 
                (format t ">> ~s~%" (list val sq sum)))
sql: "select val,sq,val+sq ss from square where sq > 30 "
>> (6 36 42)
>> (7 49 56)
>> (8 64 72)
>> (9 81 90)
nil

One reason for using the lisp expression syntax is that it enhances readability to lisp programmers, especially for large expressions. Another reason is that the expression can use the value of lisp expressions and then the resulting sql expression is computed just before it is evaluated.

In this example instead of putting the value 30 in the expression we reference the value of the lisp variable limit. Note how the generated sql isn't a string but is instead an expression which will generate the sql statement based on the current value of the limit variable.

cl-user(38): (setq limit 30)
30
cl-user(39): (with-db-rows ((val sq (sum "val+sq" "ss")) 
                            :table "square" 
                            :where (> sq limit) 
                            :showsql t) 
                 (format t ">> ~s~%" (list val sq sum)))
sql: (format nil "select val,sq,val+sq ss from square where sq > ~a "
             (dbi.mysql::stringify-float limit))
>> (6 36 42)
>> (7 49 56)
>> (8 64 72)
>> (9 81 90)
nil
cl-user(40): 

A where clause expression is not a general Lisp expression. Instead it is very restricted.

where-clause :=  boolean-clause | simple-expression

boolean-clause := (and simple-expression simple-expression ...) |
                  (or  simple-expression simple-expression ...) 

simple-expression := (op column-spec lisp-expr) | (op column-spec string)

op := = | < | > | <= | >= | != | =text

column-spec := symbol | string

lisp-expr := [any lisp expression]

There are a few things to note about simple-expressions:

1. The expressions are always binary (compare two values, but not more than two): thus (< a b c) is not allowed.
2. The column name is always the first argument. You can use (< val 3) if val is a column name but you cannot use (> 3 val)
3. The column name can be a string or symbol: (< val 3) and (< "val" 3) are equivalent.
4. The lisp-expr can be any lisp expression. It can reference lisp variables and call lisp functions. It cannot reference the value of other columns. See the next item however.
5. If the second argument to an operator (other than =text) is a string then that string is used verbatim in the where expression. so :where (< val "sq") would generate the where clause: "where val < sq" which references the values of two columns of the table. This is also useful when comparing column values against expressions involving mysql built-in functions, such as (< time "now()-1000")
6. The =text operator is designed to do a test for string equality. If the second argument to =text is a lisp-expr then the lisp-expr is evaluated just before the sql statement is executed. The lisp-expr should return a string which is then compared for equality with the column that is the first argument to =text.
7. If the =text operator's second argument is a string then the column that is the first argument is compared against that string for equality.
8. you can check for the presence of a null in a column using (= colname :null) or (!= colname :null).

Next we'll describe the meaning of the first and norows arguments to with-db-rows. When you ask for rows from the database the case of there being no rows returned can be a special case you wish to note.

Here we see a case where we want to put an error check in for the case where there's no data in the database to satisfy our query:

cl-user(42): (with-db-rows ((val sq) 
                            :table "square" 
                            :where (= val 200) 
                            :norows (error "the table isn't complete")) 
                    (print sq))
Error: the table isn't complete

Restart actions (select using :continue):
 0: Return to Top Level (an "abort" restart).
 1: Abort entirely from this process.
[1] cl-user(43): 

The value of the norows argument is a Lisp expression which is evaluated in the case where the select returns no rows. The result of that evaluation is immediately returned from the with-db-rows macro. In the previous example the signaling of an error prevented the value from being returned

Here we do the same thing except we return the string "no rows found" when the database can't find a value to satisfy the where clause of our query:

cl-user(44): (with-db-rows ((sq) 
                             :table "square" 
                             :where (= val 200) 
                             :norows "no rows found") 
                    (print sq))
"no rows found"
cl-user(45): 

Another common case is where we expect to find just one row in the table and want to take action only on the first row. Here we want to return the value for the sq column when the val column is 3.

cl-user(46): (with-db-rows ((sq) 
                             :table "square" 
                             :where (= val 3) 
                             :first sq)
                  )
9
cl-user(47): 

Normally the body of with-db-rows is executed once for every row. Specifying a non-nil value for first causes the body to be executed only once and then the first expression to be evaluated and its value returned by with-db-rows. In the above example there is no body so all you see is the result of returning the value of the first expression. The first expression is evaluated inside the binding of the column values to the column variables (that's why sq has a value).

The expression above is a common idiom for returning a column value from the database. It is clearer than writing

(caar (sql "select sq from square where val = 3"))

Also if you cared what happened if there were no rows returned you could easily add a norows argument to with-db-rows macro call.

We've seen that both first and norows specify the value to be returned by with-db-rows. If neither first nor norows come into play, then the value of the argument return specifies the expression to evaluate and return. Note that the return expression cannot use the column variable names. It is evaluated after all the rows have been processed.

An example of using return:

cl-user(50): (with-db-rows ((val sq) 
                            :table "square" 
                            :where (> val 3) 
                            :return "all done") 
                    (format t ">> ~s~%" (list val sq)))
>> (4 16)
>> (5 25)
>> (6 36)
>> (7 49)
>> (8 64)
>> (9 81)
"all done"
cl-user(51): 

Now we'll explain the use of the allrowsvar, rowvar and nobind arguments. When the select statement returns, the list of rows is stored in a variable and then processed row by row. You can choose the local variable used to store the list of rows and the local variable that takes on successive row values. By choosing the names of these variables you can then access their values in the first, return and body expressions. Note that the return expression only has access to one of the variables - the one containing all rows.

allrowsvar should have as a value a symbol which will name the local variable that will hold the list of all row values. rowvar should have as a value a symbol which will name the local variable holding each successive row.

In this example we return the list of results just as the select statement returned them:

cl-user(53): (with-db-rows ((val sq) 
                            :table "square" 
                            :where (> val 3) 
                            :allrowsvar allofthem 
                            :first allofthem))

((4 16) (5 25) (6 36) (7 49) (8 64) (9 81))
cl-user(54):

This is slightly slower than but equivalent to

(sql "select val,sq from square where val > 3")

with-db-rows is slower because it bound the val and sq variables to the values from the first row returned. It bound them since it didn't know that their values were not going to be used.

You can tell with-db-rows that you don't want it to bind to the column variables by adding :nobind t to the argument list:

cl-user(56): (with-db-rows ((val sq) 
                            :table "square" 
                            :where (> val 3) 
                            :allrowsvar allofthem 
                            :first allofthem 
                            :nobind t))
((4 16) (5 25) (6 36) (7 49) (8 64) (9 81))
cl-user(57): 

Now we have a with-db-rows expression that's as fast as the sql expression.

with-db-rows takes the same conversion arguments as the sql function: convert, blob, and float. You can find a description of those arguments in the documentation for sql.

You can also find the documentation for the db argument in the documentation for the sql function.

The table argument has been used in all of the examples but has not yet been described in detail. If the table argument is a string then that string is used verbatim in the from clause of the sql statement. It can list a single table (as above) or a group of tables as for example "foo, bar, baz". You can also use the table-list function to build a list of table names. This is especially useful when the table names aren't known until runtime.

(table-list "foo" "bar "baz")
  returns "foo, bar, baz".

You can alias tables in sql and a table name of ("foo" :as "f") turns into "foo as f". Thus

(table-list '("foo :as "f") "bar")
  returns "foo as f, bar".

The other argument to with-db-rows takes a string which is added verbatim at the end of the sql statement.

Some examples of using with-db-rows

This does a join from two tables and returns the row results. Note that by using aliases for the tables we can write the column variables without knowing until runtime the exact names of the table we'll be reading.

(with-db-rows ((i.u_item 
                i.prodnumber
                i.version
                v.brief_desc
                i.options
                i.quantity
                v.price
                i.totalcost)
                 
               :table (table-list `(,(store-items store) :as "i")
                                  `(,(store-inventory store) :as "v"))
                 
               :where (and (= i.cartnum cartid)
                           (= i.prodnumber "v.prodnumber")
                           (= i.version "v.version"))
               :float nil
               :db db
               :allrowsvar all-ret-vals
               :first all-ret-vals
               :nobind t))

In this example we compute the tax to charge on an order. Bound outside this expression are two lisp variables: orderid and city. city is bound to a lisp string naming the city for which we want to compute the tax.

(with-db-rows (((tax "(t.rate*o.tsubtotal)/100"))
               :table (table-list
                       '("tax" :as "t")
                       `(,(store-orders store) :as "o"))
               :where (and (= o.u_order orderid)
                           (=text t.city city))
               :first tax))

In this example we want to retrieve a number of columns related to the processing of a credit card. We could just have the first argument to with-db-rows be the expression

(list u_processor ts result pnref authcode avsaddr avszip cvv2match)

but that would cause us to cons up a list that already existed and was returned by the select statement. Why not use the list returned by the select statement for this row? We do precisely that by naming the rowvar (we call the variable processor) and then we have first return that variable.

(with-db-rows ((u_processor
                ts
                result
                pnref
                authcode
                avsaddr
                avszip
                cvv2match)
               :table (store-processor store)
               :db db
               :where (= u_processor processornum)
               :nobind t
               :float nil
               :rowvar processor
               :first processor))

See mysql.htm for information on the Allegro MySQL facility.

Copyright (c) 1998-2022, Franz Inc. Lafayette, CA., USA. All rights reserved.
This page was not revised from the 10.0 page.
Created 2019.8.20.