Writing SQL Statements in Lisp
Ramza Brown
on that. What are some approaches? This is what I have so far to take
a generic list and append commas.
[5]> (append (mapcar #'(lambda (elem) (concatenate 'string elem ","))
(butlast (list "A" "B" "C")))
(list "C"))
("A," "B," "C")
[6]>
Harald Hanche-Olsen
FORMAT is your friend:
CL-USER> (format nil "~{~A~^,~}" '("A" "b" "c" "d" "E"))
"A,b,c,d,E"
(Wow, I did that without looking in the HyperSpec. Spooky.)
--
* Harald Hanche-Olsen <URL:http://www.math.ntnu.no/~hanche/>
- Debating gives most of us much more psychological satisfaction
than thinking does: but it deprives us of whatever chance there is
of getting closer to the truth. -- C.P. Snow
Kent M Pitman
Rather than what you wrote, perhaps:
(format nil "~{~A~^, ~}" '("A" "B" "C"))
=> "A, B, C"
Useful tips to know:
* ~{...~} is iteration [Those are curly braces, not parens, in case
your screen font is as bad as mine and it's hard to tell.]
* ~^ is exit from iteration when no more tokens left
By making text follow the exit test, you get that text (comma and space
in this case) only if there are more elements following.
Though this will not address the issue of quotation. e.g., you might have
a table or field name with spaces in it and need it to be quoted like SQL
quotes it, rather than like Lisp quotes it.
(defun make-sql-string-list (list)
(with-output-to-string (str)
(loop for first-time = t then nil
for element in list
unless first-time do (write-string ", " str)
do (print-sql-string element str))))
where you'll define your own PRINT-SQL-STRING appropriately for the dialect
of SQL you're using.
Pascal Bourguignon
?
The simpliest is to use FORMAT:
(let ((columns '(id name salary))
(table 'employees)
(clause '((5000 < salary) and (salary < 10000))))
(format nil "select ~{~A~^,~} from ~A where ~A" columns table clause))
--> "select ID,NAME,SALARY from EMPLOYEES where ((5000 < SALARY) AND (SALARY < 10000))"
--
__Pascal Bourguignon__ http://www.informatimago.com/
Small brave carnivores
Kill pine cones and mosquitoes
Fear vacuum cleaner
Kent M Pitman
> Ramza Brown <berlin...@gmail.com> writes:
>
> > I want to take a list of field names and generate a SQL statement
> [...]
> The simpliest is to use FORMAT:
>
> (let ((columns '(id name salary))
> (table 'employees)
> (clause '((5000 < salary) and (salary < 10000))))
> (format nil "select ~{~A~^,~} from ~A where ~A" columns table clause))
It might be useful to use ~@[ WHERE ~A~] rather than just WHERE ~A
since if the clauses are empty you usually drop the WHERE clause.
Kalle Olavi Niemitalo
> I want to take a list of field names and generate a SQL statement
> based on that. What are some approaches?
(lambda (strings)
(with-output-to-string (out)
(when strings
(loop do (princ (pop strings) out)
while strings
do (write-char #\, out)))))
(lambda (strings)
(format nil "~{~A~^,~}" strings))
Should you also put quotes around the column names, to avoid
confusing SQL if they contain spaces or other token separators?