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.
;;; 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.
Try:
;;; 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
Thanks for your input. I will give those changes a try, and report back. I too, am making a note to buy some popcorn ...
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!
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
You're right, there is a small error in the
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/). I'm sure that getting all the punctuation and case right is half the battle...
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...
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
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.
Thanks too, to RickyBoy. Popcorn is back in the cupboard, waiting for next time
Quote from: "bairui"
;;; 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
There might be a log option you can use with the http server option. Does this help?
Okay, I've isolated the problem to the
I use
-- xytroxon
Okay, it's in the way
Since this code was NOT being run from a server, (read-line) is waiting for input that never arrives.
; get POST data if present, use CONTENT_LENGTH variable
; if available
...
(begin
(set 'inline (read-line))
(when inline
(set 'params (get-vars inline)))
)
...
-- xytroxon