Re: Placeholders and/or Quoting in CLSQL

On Fri, 22 Dec 2006 11:33:35 +0200, Shlomi Fish <shlomif-ik1l9ssToec+JF/[email protected]> wrote:

> I'm trying to translate the following Amarok script I wrote in Perl
> into Lisp:
>
> http://www.kde-apps.org/content/show.php?content=49151

What is an "Anorak script"?  From your code below it looks like you're
always calling SBCL from the shell with the --eval option.  That
doesn't look like a good idea to me - it's definitely not how you
typically work in Lisp.

> Now in the script I used SQL with placeholders:
>
> <<<<<<<<
> $statement = $dbh->prepare("SELECT * FROM people WHERE first_name = ?");
>
> $results = $statement->execute("Natan");
>>>>>>>>>
>
> However, I'm unable to find anything similar in CLSQL. Is there
> anything like that?

There's PREPARE-SQL and friends for databases like PostgreSQL which
support prepared statements.  (See doc/ref-prepared.xml which for
reasons unknown to me isn't part of the released documentation.)  This
is different from Perl's prepare, though, which is (IIRC) about
preparation on the Perl side and not necessarily on the database side.

If you're looking for a way to create SQL statements without thinking
about quoting look at the symbolic SQL syntax:

  http://clsql.b9.com/manual/ref-syntax.html
  http://www.lispworks.com/documentation/lw50/LWUG/html/lwuser-276.htm
  http://www.lispworks.com/documentation/sql-tutorial/index.html#section-4.1

> An alternative would be to quote the values into SQL strings using
> the database-safe quoting mechanism. From what I understood from the
> documentation, that's what the (clsql:sql) function does. However,
> the following code:
>
> <<<<<<<<<<<<
> (require 'clsql)
>
> (let*
>   (
>    (db 
> (clsql:connect '("/home/shlomi/.kde/share/apps/amarok/scripts-data/per-song-volume.sqlite")
>                 :database-type :sqlite3)
>        ))
>   (print db)
>   (print (clsql:query "SELECT path, volume FROM songs_volumes" :database 
> db :field-names nil))
>   (print (clsql:sql :database db "Hello 'Hi Ho ' Please ' Got"))
>   )
>>>>>>>>>>>>>

Aside: You should seriously consider to format your Lisp code like
everybody else does.  Code written in your style won't work with Lisp
IDEs like SLIME and it looks butt-ugly to experienced Lispers and will
thus reduce their willingness to help you.  Look for example here:

  http://www.lisp.org/table/style.htm   [see "Use whitespace appropriately"]
  http://norvig.com/luv-slides.ps

> generates the following error:
>
> <<<<<<<<<<<
> debugger invoked on a SIMPLE-ERROR:
>   Error during processing of --eval option (LOAD #P"db2.lisp"):
>
>   A CLSQL lisp code error occurred: No type conversion to SQL for 
> SQLITE3-DATABASE is defined for DB NULL.
>>>>>>>>>>>>

That's because of the second argument (DB) you fed into the SQL
function.  You probably thought that you're supposed to provide a
keyword argument (which makes sense, see below), but you aren't.

Try this instead:

  (clsql:sql "Hello 'Hi Ho ' Please ' Got")

> Which means I cannot quote according to a database handle. On the
> other hand the following code:
>
> <<<<<<<<<<<<
> (require 'clsql)
>
> (let*
>   (
>    (db 
> (clsql:connect '("/home/shlomi/.kde/share/apps/amarok/scripts-data/per-song-volume.sqlite")
>                 :database-type :sqlite3)
>        ))
>   (print db)
>   (print (clsql:query "SELECT path, volume FROM songs_volumes" :database 
> db :field-names nil))
>   (print (clsql:sql "Hello \\'Hi Ho \\' Please ' Got"))
>   )
>>>>>>>>>>>>>
>
> Generates the following error at (clsql:sql):
>
> <<<<<<<<<
> debugger invoked on a SIMPLE-ERROR:
>   Error during processing of --eval option (LOAD #P"db2.lisp"):
>
>   There is no applicable method for the generic function
>     #<STANDARD-GENERIC-FUNCTION CLSQL-SYS:DATABASE-TYPE (2)>
>   when called with arguments
>     (NIL).
>>>>>>>>>>
>
> So it seems I cannot use the cl:sql function with backslashes (which
> I may need).

Looks like the SQL function as it is now is bogus because it really
doesn't make much sense without specifying a database.  Below is a
patch which tries to at least use the default database (so it should
work in your case), but I think the SQL function itself should be
changed.

HTH,
Edi.



--- clsql-3.7.8/sql/expressions.lisp.orig       2006-12-27 23:51:31.000000000 +0100
+++ clsql-3.7.8/sql/expressions.lisp    2006-12-27 23:54:33.000000000 +0100
@@ -22,7 +22,7 @@
 (defvar *sql-stream* nil
   "stream which accumulates SQL output")
 
-(defun sql-output (sql-expr &optional database)
+(defun sql-output (sql-expr &optional (database *default-database*))
   "Top-level call for generating SQL strings. Returns an SQL
   string appropriate for DATABASE which corresponds to the
   supplied lisp expression SQL-EXPR."