Re: Placeholders and/or Quoting in CLSQL

Hi!

Thanks for your reply.

On Thursday 28 December 2006 01:26, Edi Weitz wrote:
> 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"?  

Amarok is a music player for the KDE environment:

http://amarok.kde.org/

An Amarok script receives input from Amarok representing events (such as a 
song change, or a volume change), and can be used to customise it by reacting 
to these events. See:

http://amarok.kde.org/wiki/Scripts

> 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.
>

Well, actually I'm using the "--load" parameter. But you're right. I'd like to 
know a way in which I can tell SBCL to execute a Lisp script and to then 
terminate. This is similar to doing "perl myscript.pl" from the command line. 
And a good idea would also be to find a way to lose all the unnecessary 
information messages that SBCL emits.

> > 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.
>

Thanks!

I assume that what you mean is that if the database backend doesn't support 
prepared statements, then PREPARE-SQL and friends are not available. For the 
record, Perl's DBI's prepare does make use of such a facility in the database 
if it's available, but can also emulate it in the Perl level, if it's not.

> 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
>

I've looked at the CLSQL:SQL function from the first link, but as I 
demonstrated below, it didn't work.

> > 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.sqlit
> >e")
> >
> >                 :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.  

I Formatted it as best as I could at the time, but I now see several problems 
with it.

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

OK.

> > 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")
>

This should work, but what if databases differ in their quoting syntax? I'd 
like to get a way to make a backend-specific quoting.

> > 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.sqlit
> >e")
> >
> >                 :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.
>

OK. Does it mean that if I do (clsql:sql "My string" my-database-handle), it 
will work?

Regards,

	Shlomi Fish

> 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."

---------------------------------------------------------------------
Shlomi Fish      shlomif-ik1l9ssToec+JF/[email protected]
Homepage:        http://www.shlomifish.org/

Chuck Norris wrote a complete Perl 6 implementation in a day but then
destroyed all evidence with his bare hands, so no one will know his secrets.