headers
Shlomi Fish | 22 Dec 2006 10:33
Picon
Gravatar

Placeholders and/or Quoting in CLSQL

Hi all!

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

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?

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)
(Continue reading)

Permalink | Reply | 0 comments |
headers
Edi Weitz | 28 Dec 2006 00:26
Picon
Favicon

Re: Placeholders and/or Quoting in CLSQL

On Fri, 22 Dec 2006 11:33:35 +0200, Shlomi Fish <shlomif@...> 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
(Continue reading)

Permalink | Reply | 0 comments |
headers
Shlomi Fish | 28 Dec 2006 14:56
Picon
Gravatar

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@...> 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
(Continue reading)

Permalink | Reply | 0 comments |
headers
Edi Weitz | 28 Dec 2006 16:19
Picon
Favicon

Re: Placeholders and/or Quoting in CLSQL

On Thu, 28 Dec 2006 15:56:36 +0200, Shlomi Fish <shlomif@...> wrote:

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

I understand where you're heading at, but Lisp is not a scripting
language.  The usual way to work in Lisp is to start your "image" and
keep it running for a /long/ time.  You /can/ use it as a scripting
language using command line switches, but that's not how it's meant to
be.

> And a good idea would also be to find a way to lose all the
> unnecessary information messages that SBCL emits.

Most of them aren't really unnecessary... :)

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

Right.  Or if the database supports it, but nobody has written it yet,
it's not available as well... :)

(I think this is the case for Oracle.)

> 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.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Kevin Rosenberg | 28 Dec 2006 16:45
Favicon

Re: [CLSQL-Devel] Placeholders and/or Quoting in CLSQL

Edi Weitz wrote:
> 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.

Hi Edi,

Because I'm not prepared to document and support prepared statements
until it is supported by at least MySQL. I think the top-level API may
change as I implement prepared statements across database
engines. Further, the postgresql support for prepared statements has
not performance increase over native SQL. So, I don't see a big rush
to document and support the nascent feature.
> --- 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
>  <at>  <at>  -22,7 +22,7  <at>  <at> 
>  (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."

That's a reasonable patch for people who want to directly call
SQL-OUTPUT (which has never been me).
(Continue reading)

Permalink | Reply | 0 comments |
headers
Edi Weitz | 28 Dec 2006 17:08
Picon
Favicon

Re: [CLSQL-Devel] Placeholders and/or Quoting in CLSQL

On Thu, 28 Dec 2006 08:45:34 -0700, Kevin Rosenberg <kevin@...> wrote:

> That's a reasonable patch for people who want to directly call
> SQL-OUTPUT (which has never been me).

It was meant as a necessary patch for SQL (not SQL-OUTPUT) which is
exported and documented.  Without this patch it will break (see
original message) if you for example call

  (CLSQL:SQL "\\")

With the patch, it will use the default database.  It will still
break, if you don't have a connection, but I think it's better than
nothing.
Permalink | Reply | 0 comments |
headers
Kevin Rosenberg | 28 Dec 2006 19:06
Favicon

Re: [CLSQL-Devel] Placeholders and/or Quoting in CLSQL

Edi Weitz wrote:
> With the patch, it will use the default database.  It will still
> break, if you don't have a connection, but I think it's better than
> nothing.

I agree and have planned on committing the patch. It would be nice to
also have the concept of a fallback "typical" database where SQL would
output the sql statements that a "typical" database would require if
*default-database* is nil and no explicit database was passed to SQL.

--

-- 
Kevin Rosenberg
kevin@...
Permalink | Reply | 0 comments |
headers
Edi Weitz | 28 Dec 2006 19:13
Picon
Favicon

Re: [CLSQL-Devel] Placeholders and/or Quoting in CLSQL

On Thu, 28 Dec 2006 11:06:32 -0700, Kevin Rosenberg <kevin@...> wrote:

> I agree and have planned on committing the patch. It would be nice
> to also have the concept of a fallback "typical" database where SQL
> would output the sql statements that a "typical" database would
> require if *default-database* is nil and no explicit database was
> passed to SQL.

Shouldn't that "typical" database be the SQL standard, SQL-92 or
something like that?  I'm not an expert in these things but if this
standard covers things like escaping of special characters it's
probably a good idea.
Permalink | Reply | 0 comments |
headers
Kevin Rosenberg | 28 Dec 2006 20:28
Favicon

Re: [CLSQL-Help] Placeholders and/or Quoting in CLSQL

Edi Weitz wrote:
> Shouldn't that "typical" database be the SQL standard, SQL-92 or
> something like that?  I'm not an expert in these things but if this
> standard covers things like escaping of special characters it's
> probably a good idea.

Yes, that'd be a good choice for the unspecified database.

--

-- 
Kevin Rosenberg
kevin@...
Permalink | Reply | 0 comments |
headers
Quillian Rutherford | 30 Dec 2006 10:05
Picon

Reconnection question

Occasionally I'll have a database connection go away (using MySql), and I would like to reconnect and continue on.  In other languages I'd normally find something like:

if(!db_connection->open()){
  db_connect->reconnect();
}
 
Or some such.  In clsql, I haven't figured out exactly how I'd do something similar (or different bu with the same outcome).  I've tried to restart like so (with a disconnect for testing):

(defun handle-database-error (condition)
  (let ((*connect-if-exists* :warn-new))
        (format t "Trying to reconnect ~%")
      (clsql:reconnect :database (clsql:sql-error-database condition))))


(defun urfiwrapper (row)
        (handler-bind ((clsql-sys:sql-connection-error#'handle-database-error))
            (clsql:update-records-from-instance row)
          (invoke-restart 're-urfi)))

(defaction update-task ((ptd present-tasklist-data-component) row)
        (db-con)
        (db-disconnect)
            (restart-case (urfiwrapper row)
              (re-urfi (row) (urfiwrapper row))))


But this seems to be incorrect.  Essentially I just want to retry my query (an update in this case) after trying to reconnect, if possible.  Any hints as to what I should actually be doing here?

_______________________________________________
CLSQL-Help mailing list
CLSQL-Help@...
http://lists.b9.com/mailman/listinfo/clsql-help
Permalink | Reply | 0 comments |

Gmane