cgi again

Q&A's, tips, howto's
Locked
jazper
Posts: 92
Joined: Thu Dec 10, 2009 8:26 am
Location: South Africa

cgi again

Post by 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 all

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

(print "Content-type: text/html\n")
(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
Posts: 64
Joined: Sun May 06, 2012 2:04 am
Location: China
Contact:

Re: cgi again

Post by bairui »

Try:

Code: Select all

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

(print "Content-type: text/html\n")
(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
Posts: 92
Joined: Thu Dec 10, 2009 8:26 am
Location: South Africa

Re: cgi again

Post by 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
Posts: 92
Joined: Thu Dec 10, 2009 8:26 am
Location: South Africa

Re: cgi again

Post by 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
Posts: 607
Joined: Fri Apr 08, 2005 7:13 pm
Location: Front Royal, Virginia

Re: cgi again

Post by 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 put your popcorn away -- that code looks good to me! :))
(λx. x x) (λx. x x)

cormullion
Posts: 2038
Joined: Tue Nov 29, 2005 8:28 pm
Location: latiitude 50N longitude 3W
Contact:

Re: cgi again

Post by cormullion »

You're right, there is a small error in the 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 ... asy-steps/). I'm sure that getting all the punctuation and case right is half the battle...

bairui
Posts: 64
Joined: Sun May 06, 2012 2:04 am
Location: China
Contact:

Re: cgi again

Post by 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
Posts: 92
Joined: Thu Dec 10, 2009 8:26 am
Location: South Africa

Re: cgi again

Post by 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
Posts: 92
Joined: Thu Dec 10, 2009 8:26 am
Location: South Africa

Re: cgi again

Post by jazper »

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

xytroxon
Posts: 296
Joined: Tue Nov 06, 2007 3:59 pm
Contact:

Re: cgi again

Post by xytroxon »

bairui wrote:Try:

Code: Select all

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

(print "Content-type: text/html\n")
(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
"Many computers can print only capital letters, so we shall not use lowercase letters."
-- Let's Talk Lisp (c) 1976

cormullion
Posts: 2038
Joined: Tue Nov 29, 2005 8:28 pm
Location: latiitude 50N longitude 3W
Contact:

Re: cgi again

Post by cormullion »

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

xytroxon
Posts: 296
Joined: Tue Nov 06, 2007 3:59 pm
Contact:

Re: cgi again

Post by 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
"Many computers can print only capital letters, so we shall not use lowercase letters."
-- Let's Talk Lisp (c) 1976

xytroxon
Posts: 296
Joined: Tue Nov 06, 2007 3:59 pm
Contact:

Re: cgi again

Post by 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 all

; get POST data if present, use CONTENT_LENGTH variable
; if available
...
    (begin
        (set 'inline (read-line))
        (when inline 
        (set 'params (get-vars inline)))
    )
...
-- xytroxon
"Many computers can print only capital letters, so we shall not use lowercase letters."
-- Let's Talk Lisp (c) 1976

Locked