cgi again

[jazper]

I have never got the example in the User Guide to work.  I can get it to list a result set if I manually insert rows into the Elements Sqlite3 database, but nothing gets written via code.  Lately, I have been basing an attempt to write to a simple user, email database, from a webform.  This is what I wrote: it's an adaptation of a "quick and dirty" script that someone posted to talk to a sqlite3 database.

Code Select Expand

#!/usr/bin/newlisp
;;; to create table, used this -- CREATE TABLE UsrTbl(Id INTEGER PRIMARY KEY, UserName TEXT, UserEmail TEXT)

(print "Content-type: text/htmln")
(load (append (env "NEWLISPDIR") "\modules\cgi.lsp"))
(load (append (env "NEWLISPDIR") "\modules\sqlite3.lsp"))

(println
[text]
<br>
<form action="UPDTDB2.cgi" method="POST">
<pre>
User name:<input type="text" name="username"><br>
Email    :<input type="text" name="useremail"><br>
<input type="submit" value="Go">
</pre>
</form>
<br>
[/text])

(define (rslt 'sql-text)
(set 'sqlarray (sql3:sql sql-text))    ; results of query
(if sqlarray
(map println sqlarray)
(println (sql3:error) " query problem ")))

(define (insrt 'sql-text)
(set 'qry (sql3:sql sql-text))
(if qry
(println "ok")
(println (sql3:error))))

(set 'database "JCDB.db")
(sql3:open database)

(println {<br>})
(set 'name (CGI:get "UserName"))
(set 'email (CGI:get "UserEmail"))
(set 'sqlstr (string "INSERT INTO usrtbl VALUES('','" name "','" email "');" ))
(insrt sqlstr)

(println {<br>Hit the [back] button on your browser to got back<br>})

(exit)

;;; eof ;;

Once again, it list things that I put in manually, but will not insert values.  Can anyone spot what I am doing wrong?  No error message is posted.

[bairui]

Try:

Code Select Expand

#!/usr/bin/newlisp
;;; to create table, used this -- CREATE TABLE UsrTbl(Id INTEGER PRIMARY KEY, UserName TEXT, UserEmail TEXT)

(print "Content-type: text/htmln")
(module "cgi.lsp")
(module "sqlite3.lsp")

(println
 [text]
 <br>
 <form action="UPDTDB2.cgi" method="POST">
 <pre>
 User name:<input type="text" name="UserName"><br>
 Email    :<input type="text" name="UserEmail"><br>
 <input type="submit" value="Go">
 </pre>
 </form>
 <br>
 [/text])

(define (rslt sql-text)
 (set 'sqlarray (sql3:sql sql-text))    ; results of query
 (if sqlarray
  (map println sqlarray)
  (println (sql3:error) " query problem ")))

(define (insrt sql-text)
 (set 'qry (sql3:sql sql-text))
 (if qry
  (println "ok")
  (println (sql3:error))))

(set 'database "JCDB.db")
(sql3:open database)

(println {<br>})
  (set 'name (CGI:get "UserName"))
  (set 'email (CGI:get "UserEmail"))
  (set 'sqlstr (string "INSERT INTO UsrTbl VALUES(NULL,'" name "','" email "');" ))
(insrt sqlstr)

(println {<br>Hit the [back] button on your browser to got back<br>})

(exit)

  ;;; eof ;;

I am not familiar enough with either newLISP or cgi to claim which of the small changes I made were absolutely necessary versus the ones which merely felt right stylistically. No doubt Cormullion or RickyBoy will be along soon to show us what real style looks like. :-) I for one will have my popcorn ready for that too.

[jazper]

Thanks for your input.  I will give those changes a try, and report back. I too, am making a note to buy some popcorn ...

[jazper]

Well, Bairui, what can I say?  You nailed my funnies there, and it works fine now.  Apart from removing the single quote where I had a symbol as an argument, you also used a NULL for the AUTOINCREMENT.  In the past, I have successfully used an empty string, but NULLS as well.  I just forgot.  Did you make any other changes?  The above is all I saw.



Thanks so much.  With this as a barebones, I can go forward.  Much appreciated.

Cheers!

[rickyboy]

I'm not a CGI expert either, but I think those CGI parameters are indeed case sensitive.



Cormullion could tell you off the top of his head, though.



And http://tinypic.com/r/2e5qg5z/3">put your popcorn away -- that code looks good to me!  :))

[cormullion]

You're right, there is a small error in the http://en.wikibooks.org/wiki/Introduction_to_newLISP/More_examples#Using_a_SQLite_database">sqlite example on Wikibooks - the A% near the end should have single quotes... Now fixed.



Honestly I know very little about CGI or Sqlite, , other than my clumsy scribblings on the subject (https://newlisper.wordpress.com/2008/02/12/a-newlisp-cgi-web-page-in-10-easy-steps/">//https://newlisper.wordpress.com/2008/02/12/a-newlisp-cgi-web-page-in-10-easy-steps/). I'm sure that getting all the punctuation and case right is half the battle...

[bairui]

o_O what am I gonna do with all this popcorn?!



Thanks, RickyBoy & Cormullion - it was the case sensitivity issues I was unsure about. I was fairly sure that the NULL was necessary - it seemed so in standalone tests of sqlite3.lsp. I don't think I made any other changes, jazper, apart from the aesthetic (load ...) -> (module "...").



FWIW, I tested sqlite3.lsp in the console and a standalone script before introducing the cgi layer. Primarily I did this because I always like to limit the number of variables when testing/fixing something, but a very real secondary in this case was my utter lack of cgi experience.



Mmm... popcorn...

[jazper]

Thanks again, Bairui.  You did spot some other errors:  they were corrected in the code you posted.  I had used (single quoted) symbols for arguments when defining the two queries "insrt" and "rslt".  The modules had been working the way I loaded them before, but your code looks so much better.  It was those and the NULL that made the difference, though I tested that with "null" lower case, which also worked.



Speaking about the "Elements" example (thanks to Cormullion for the latter fix) I never get even as far as the "like: query with ' A% ' as mentioned.  I can't get the elements to load into Sqlite3, so the question of using queries never arises.  



I will give that another try, though.  Now that I have had a rest, I may spot something I have done wrong.  So far, I have actually copied the code in verbatim, and re-checked a hundred times.  But, a slip can happen.

[jazper]

Thanks too, to RickyBoy.  Popcorn is back in the cupboard, waiting for next time

[xytroxon]

Try:

Code Select Expand

#!/usr/bin/newlisp
;;; to create table, used this -- CREATE TABLE UsrTbl(Id INTEGER PRIMARY KEY, UserName TEXT, UserEmail TEXT)

(print "Content-type: text/htmln")
(module "cgi.lsp")
(module "sqlite3.lsp")

(println
 [text]
 <br>
 <form action="UPDTDB2.cgi" method="POST">
 <pre>
 User name:<input type="text" name="UserName"><br>
 Email    :<input type="text" name="UserEmail"><br>
 <input type="submit" value="Go">
 </pre>
 </form>
 <br>
 [/text])

(define (rslt sql-text)
 (set 'sqlarray (sql3:sql sql-text))    ; results of query
 (if sqlarray
  (map println sqlarray)
  (println (sql3:error) " query problem ")))

(define (insrt sql-text)
 (set 'qry (sql3:sql sql-text))
 (if qry
  (println "ok")
  (println (sql3:error))))

(set 'database "JCDB.db")
(sql3:open database)

(println {<br>})
  (set 'name (CGI:get "UserName"))
  (set 'email (CGI:get "UserEmail"))
  (set 'sqlstr (string "INSERT INTO UsrTbl VALUES(NULL,'" name "','" email "');" ))
(insrt sqlstr)

(println {<br>Hit the [back] button on your browser to got back<br>})

(exit)

  ;;; eof ;;

This code hangs up when run from Win 7 command line.



-- xytroxon

[cormullion]

There might be a log option you can use with the http server option. Does this help?

[xytroxon]

Okay, I've isolated the problem to the cgi.lsp module.



I use CGI:url-translate in an old program that now hangs up too.



-- xytroxon

[xytroxon]

Okay, it's in the way cgi.lsp initializes itself...



Since this code was NOT being run from a server, (read-line) is waiting for input that never arrives.

Code Select Expand


; get POST data if present, use CONTENT_LENGTH variable
; if available
...
    (begin
        (set 'inline (read-line))
        (when inline
        (set 'params (get-vars inline)))
    )
...


-- xytroxon