RE: Dbi.Mysql using parameters error - beginner simple problem

Hi,

I think it is clear that you have made the mistake of somehow including the
word format as part of the string you want formatted. You probably meant to
write something like:

(defun get-user (db first second)
  (sql (format nil "select UserName, Pwd from Login where UserName = '~a'
and Pwd = '~a'"
         first second)
       :db db)
  (mysql-insert-id db))

Now the format evaluates to the correct sql:

"select UserName, Pwd from Login where UserName = 'Mark' and Pwd = 'Peg'"

You seem to have made a less severe error in your function add-user, letting
the format statement include :db db (check the parenthesises(?)).

I don't understand how the rest of your functions work, but this part seems
pretty clear.

By the way if you want to access databases from Lisp, I would recommend you
to look at our library GSQL. It extends Lisp to be a database language
including SQL and lets you write things like:

(gsql:select '(UserName Pwd)
             :from 'Login
             :where `(and (= (UserName ,first))
                          (= (Pwd ,second))))

and

(gsql:insert-row 'Login
                 '(UserName Pwd)
                 `(,first ,second))

It interfaces through ODBC and we also provide a full SQL database in Lisp.

Programming database access in SQL using Lisp syntax really extends the
power of Lisp to database programming. Not to use this option really takes
away a huge potential advantage for Lisp programmers. Some examples:

- The Lisp editor pretty-printer makes SQL more readable
- You can easily test and develop much more powerful SQL statements
- You can program-generate SQL on the fly

You can see more on

http://www.inthan.com/aweb/crm01/Lisp/index.html

That Web site is developed entirely in Lisp (ACL) using AllegroServe and our
own application for creating Web Applications (with Login validation).
(Currently it is only accessible using IE5).

Greger Lindell



-----Original Message-----
From: peggymark [mailto:[email protected]] 
Sent: Wednesday, September 21, 2005 06:27
To: [email protected]
Subject: Dbi.Mysql using parameters error - beginner simple problem


Dear Friends,
First Thanks for taking your valuable time.
quick look  so you don't have to read the whole email if it is an 
obvious error-
The error message is as follows: Error: Illegal keyword given: Mark. 
[condition type: program error]

  this function gives the previous error.  

 (defun get-user (db first second)
    (sql " format nil select UserName, Pwd from Login where UserName = 
'~a' and Pwd = '~a'"
              first second
             :db db)
     (mysql-insert-id db))

I call the function this way: (get-user db 'Mark 'Peg)

The error message is as follows: Error: Illegal keyword given: Mark. 
[condition type: program error]
Any resources for code examples or documentation on using dbi.mysql??


 I have Allegro CL installed in my Linux Machine and have tried to use 
Allegro Serve with MySQL to create a web application. All is going ok so 
far but... (ha ha) Here is my problem, I have two functions one for 
inserting and one for looking up a login user through MySQL. The insert 
works but the lookup won't accept my parameters for the select statement.
   Here are the two functions with the function calls i make to lisp in 
emacs. Can you see where the problem is?? i use dbi.mysql and put in 
this function call (setq db (connect :user "markandeya" :password 
"service" :database "CallMaint")).
 this connects me to my Mysql database and is working.

this function works
(defun add-user (db first second)
     (sql (format nil "insert into Login (UserName, Pwd) values('~a','~a')"
            first  second
             :db db))
     (mysql-insert-id db))

i call the function this way: (add-user db 'Mark 'Peg)
 

  this function gives errors  
 (defun get-user (db first second)
    (sql " format nil select UserName, Pwd from Login where UserName = 
'~a' and Pwd = '~a'"
              first second
             :db db)
     (mysql-insert-id db))

I call the function this way: (get-user db 'Mark 'Peg)

The error message is as follows: Error: Illegal keyword given: Mark. 
[condition type: program error]

Thank you for any help or suggestions on database programming through Mysql.
Markandeya