I want to take a list of field names and generate a SQL statement based
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]>
From: Kent M Pitman
Subject: Re: Writing SQL Statements in Lisp
Date:
Message-ID: <upstetoxs.fsf@nhplace.com>
Ramza Brown <············@gmail.com> writes:
> I want to take a list of field names and generate a SQL statement
> based 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]>
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.
+ Ramza Brown <············@gmail.com>:
| I want to take a list of field names and generate a SQL statement
| based 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]>
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
Ramza Brown <············@gmail.com> writes:
> I want to take a list of field names and generate a SQL statement
> based 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")
?
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
From: Kent M Pitman
Subject: Re: Writing SQL Statements in Lisp
Date:
Message-ID: <uk6jmtnom.fsf@nhplace.com>
Pascal Bourguignon <···@informatimago.com> writes:
> Ramza Brown <············@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.
Ramza Brown <············@gmail.com> writes:
> 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?