Step 1: Search front end.
Step 2:
Script "booksearch.asp" takes this input and assembles and runs this PICK ACCESS sentence:
Chapter 4 - PixieWeb Documentation:
Example: " Book Catalogue" introduces:
"Book Catalogue" is one of the demos running on our website.
http://mail.thetotal.co.nz:7301
The Library Supply Company of New Zealand is a large print book specialist. CEO Tom Dignan set me an interesting challenge early in the "PixieWeb" project: an enquiry which would produce a list of titles: then on clicking on a title, the user would get a screen of details for that book. Web sites do this all the time with trees of static htm documents, but here we have 5000 books which is way too many htm files to write and maintain. So we want the PICK database to dynamically present its up-to-date info to the user on demand.
The 2 PICK files ("tables" to you non-PICKers) of interest are authors and
books.
Both use the product code ie book code as their ItemID
What happens:
Step 1: Search front end.
Step 2:
Script "booksearch.asp" takes this input and assembles and runs this PICK
ACCESS sentence:
SORT authors BY lauthor BY fauthor BY title WITH title = "[love]" AND WITH lauthor = "[smith]" title fauthor lauthor (c
Points to note in the handling of the response:
It is possible to get very large responses from such a query open to the Public. Some trickster or innocent is going to enter a single "e" in a field, triggering a response of thousands of lines! So we have devised the ExecutePage .... ContinuePage methods to manage the flow of data. This involves coding a Do .. Loop inside another Do .. Loop. The outer loop brings in the pages and the inner loop works through the (usually) 25 lines of each page.
Result from the "love" .. "smith" enquiry:
Each Title is output as a hyperlink referring to the "drill-down" script
"booksearch2.asp"
The code line that does this is:
Response.Write "<A
HREF=""booksearch2.asp?authors=" & sCode & """>"
Clicking on "Old Lover's Ghost" therefore causes this call to be made to
the server:
booksearch2.asp?itemid=aa9952
On arrival at the server, the "GET method line" ie the data you send after
the "?" is analysed by booksearch2.asp with this line of code:
itemid =
Request.QueryString("itemid")
And booksearch2.asp then uses this itemid when querying PICK file "books",
and delivers this output:
Note the use of a "table-within-a-table" to present the text beside the picture in this way. Basic HTML lacks the tab and column capability you are used to in word processing, and most screen layout and positioning is instead done with tables, using invisible borders if necessary.
And that picture... What we do is keep the pictures on the WebServer and name the picture file after the ItemID. In this case the user sees the picture file named "aa9952.jpg".
This app, like all our early apps, has 2 sub-folders branching off from "wwwroot":
"librarysupply" is set up with user permission "read" and contains the
passive .htm documents. It has a sub-folder "images" for the available
pictures.
"librarysupply_asp" is set up with user permission "execute" but NOT
"read". It contains the .asp scripts. That means that any .asp
scripts that show pictures or other media must refer to media files in other
readable folders, eg "../librarysupply/images/aa9952.jpg"
Here then are the scripts: Booksearch.asp , Booksearch2.asp
<HTML>
<HEAD>
<TITLE>Booksearch Results</TITLE>
</HEAD>
<BODY>
<!-- Note the use of HTML start-page and end-page tags at beginning and
end of this file. It saves us having to write them into each output block -->
<%
'-- BookSearch.asp orig. John Calder JPC 12.11.1998
' ASP Visual Basic script to handle I/O
' to direct TCL conversations with D3 via Pixie object
' Includes code to add "Internet Style" front end
' to PICK ACCESS search engine
'
'-- 990113 JPC Add ExecutePage method to give better handling
' of vague queries that return responses of excessive size
'-- 981219 JPC Rewrite to match interface of the new PixieWeb.dll
Dim Pixie
Set Pixie = Server.CreateObject("PixieWeb.clsAgent")
Pixie.TimeOut = 15
Pixie.Port = 23
Pixie.Host = "xxx.xxx.xxx.xxx"
Call Pixie.Connect ("user")
Call Pixie.ExecuteET("xxxxxx", ":")
Call Pixie.ExecuteET("xxxxxx", "<<< Pick Systems")
Call Pixie.ExecuteET("term mm-mon", ":")
Call Pixie.ExecuteET("echo off",":")
'-- Note function Searchstring at end of this file
title = Trim(Request.Form("title"))
fauthor = Trim(Request.Form("fauthor"))
lauthor = Trim(Request.Form("lauthor"))
'-- Check for dummy entry
If title = "" And fauthor = "" And lauthor = "" Then
s = "<H2> INCOMPLETE ENTRY, please try again with something typed "
s = s & "into at least one of the search fields...</H2>"
s = s & "<P><A HREF=""../librarysupply/booksearch.htm"">Try again</A>"
s = s & "<P><A HREF=""../default.htm"">"
s = s & "Back to Total Home Page</A>"
Response.Write s
Else
b = False 'sentence AND flag
s = "SORT pmu BY lauthor BY fauthor BY title WITH "
If title > "" Then
s = s & "title = " & SearchString(title)
b = True
End If
If fauthor > "" Then
If b Then s = s & " AND WITH "
s = s & "fauthor = " & SearchString(fauthor)
b = True
End If
If lauthor > "" Then
If b Then s = s & " AND WITH "
s = s & "lauthor = " & SearchString(lauthor)
End If
Query = s & " title fauthor lauthor (c)"
'-- DEBUG with next line, take out comment mark to activate
' Response.Write "<p><PRE>DEBUG.PRINT: " & vbCrLf & Query & "</PRE><P>"
Call PickQuery(Query)
End If
'---END OF Script-level Program -------------------------------------------------
Sub PickQuery(Query)
'-- Page Header
Response.Write "<table width=""100%"" border=""3"" "
Response.Write " bordercolor=""#800080"" cellpadding=""3""> "
Response.Write "<tr>"
Response.Write "<td width=""65%"" bgcolor=""#008080"" border=""0"">"
Response.Write "<font size=4 color=""#FFFFFF"">Library Supply Company</font></td>"
Response.Write "<td width=""35%"" bgcolor=""#008000"" align=right border=""0"">"
Response.Write "<font size=4 color=""#FFFFFF"">Catalogue Search Results</font></td>"
Response.Write "</tr></table>"
s = "<p><font size=""4"">Click on any Title for more details, or "
s = s & "<a href=""../librarysupply/booksearch.htm"">Try another Search</a></p>"
Response.Write s
Response.Write "<hr>"
'-- 990112 using a table for improved presentation
Response.Write "<table border=""2"" bgcolor=""#FFFFFF"" width=""100%"" "
Response.Write " bordercolor=""#800080"" cellpadding=""3""> "
Response.Write "<tr>"
Response.Write "<td width=""15%"" bgcolor=""#008080""><font color=""#FFFFFF"">ID Code</font></td>"
Response.Write "<td width=""48%"" bgcolor=""#008080""><font color=""#FFFFFF"">Title</font></td>"
Response.Write "<td width=""37%"" bgcolor=""#008080""><font color=""#FFFFFF"">Author</font></td>"
Response.Write "</tr>"
Call Pixie.ExecutePage (Query, Chr(0) & "[40", 25)
Call Pixie.ResponseCleanup
sResponse = Pixie.Response
'-- DEBUG with next line, take out comment mark to activate
' Response.Write "<pre>DEBUG.PRINT first page response:<BR>" & sResponse & "</pre>"
iPos = 2
Do
If Pixie.State = "ERROR" Then
s = "<H3>ERROR in database communication,"
s = s & " please try again ... </H3>"
s = s & "<P><A HREF=""../librarysupply/booksearch.htm"">"
s = s & "Try again</A>"
s = s & "<P><A HREF=""../default.htm"">"
s = s & "Back to The Total Home Page</A>"
Response.Write s
'-- DEBUG with next line, take out comment mark to activate
' Response.Write "<P>DEBUG - raw Response:<BR>" & sResponse
Exit Do
Else
iPos = 2
Do
n = iPos + 2
iPos = Instr(n, sResponse, vbCrLf)
If iPos = 0 Then Exit Do
'-- DEBUG with next line, take out comment mark to activate
' Response.Write "<BR>DEBUG.PRINT: s = " & s & "<BR> n = " & n & " : iPos =" & iPos
'-- Divide PICK ACCESS response into its separate lines
s = Mid(sResponse, n, iPos - n)
If left(s,1) = "*" Then s = Mid(s,2)
i40x = Instr(s, "[40")
If i40x > 0 Then
'-- We have reached the end of the output
Response.Write vbCrLf
s = Mid(s, i40x + 5 )
Response.Write "Results: " & s
'-- typical example of output is:
' "Results: 4 items listed out of 4644 items."
Exit Do
End If
'-- Split PICK ACCESS line into column values
' the spacing-across values come from test-running
' the PICK ACCESS query on a terminal
sCodeDisplay = Left(s, 10)
sCode = Trim(sCodeDisplay)
'-- Condition checks against odd ACCESS output lines
If Len(sCode) > 4 And Left(sCode, 1) <> "=" Then
sTitle = Trim(Mid(s, 12, 25))
sFauthor = Trim(Mid(s, 38, 20))
sLauthor = Trim(Mid(s, 59, 20))
' Write table row
Response.Write "<tr>"
Response.Write "<td width=""15%"">" & sCodeDisplay& "</td>"
'-- Use of GET technique for drill-down
Response.Write "<td width=
""48%"">"Response.Write"<AHREF=""booksearch2.asp?itemid=" & sCode & """>"
Response.Write """" & sTitle & """</A>"
Response.Write "</td>"
'-- Write author
Response.Write "<td width=""37%"">" & sFauthor & " " & sLauthor & "</td>"
Response.Write "</tr>"
End If
Loop
End If
Select Case Pixie.State
Case "TCL"
'Have reached the end of the list
Exit Do
Case "ERROR"
'Do Nothing, allow to loop around for
'start of loop to handle it, see above
Case Else
Call Pixie.ContinuePage
Call Pixie.ResponseCleanup
sResponse = Pixie.Response
End Select
Loop
Response.Write "</table></font>"
'-- Standard bottom-of-page
s = "<p>"
s = s & "<a HREF=""../pixieinfo/default.htm"">PixieWeb active pages</a>"
s = s & " "
s = s & "<a HREF=""../utilities/jpclinks.htm"">Downloads</a>"
s = s
& " "
s = s & "<a HREF= ""../totproducts.htm"">Products</a>"
s = s & " "
s = s &"<a
HREF = ""../totprices.htm"">Prices</a>"
s = s & " "
s = s & "<a
HREF = ""../default.htm"">Back to homepage</a></p><hr>"
s = s &
"<p>Pixie Partners<br>Middle Floor, " s= s
& "2163
Great North Rd, New Lynn, Auckland<br> "
s =
s & "P.O.
Box 15865, New Lynn,<br>"
s = s
& "Telephone: +64-9-8209331, Fax: +64-9-8281406<br>"
s = s & "email: <a
HREF = "mailto:sales@pixieware.com ">"
s = s & "sales@pixieware.com</a></p>"
Response.Write s
End Sub '-- End of Sub OutputRoutine
'-------------------------------------------------------
Function SearchString(s)
'-- Function to interpret search strings entered in "internet style" for
' use in PICK ACCESS sentences
' Interprets spaces-between-words as OR, "+" as AND, also supports
' explicit use of AND and OR.
s = Trim(s)
'-- Filtering out any multiple spaces
iPos = 1
Do
iPos = Instr(iPos, s, " ")
If iPos = 0 then Exit Do
s = Left(s, iPos ) & Mid(s, iPos + 2)
Loop
'-- AND OR + in searches
iPos = 1
Do
iPos = Instr(iPos, s, " AND ", 1)
If iPos = 0 then Exit Do
s = Left(s, iPos - 1) & "]""" & " AND " & """[" & Mid(s, iPos + 5)
iPos = iPos + 5
Loop
iPos = 1
Do
iPos = Instr(iPos, s, "+")
If iPos = 0 then Exit Do
'/ Allow for spaces around the + sign
If Mid(s, iPos-1, 1) = " " Then j = 2 Else j = 1
If Mid(s, iPos+1, 1) = " " Then k = 2 Else k = 1
s = Left(s, iPos - j) & "]""" & " AND " & """[" & Mid(s, iPos + k)
Loop
iPos = 1
Do
iPos = Instr(iPos, s, " OR ", 1)
If iPos = 0 then Exit Do
s = Left(s, iPos - 1) & "]""" & " OR " & """[" & Mid(s, iPos + 4)
iPos = iPos + 4
Loop
'-- Treat unprocessed space, ie no speech marks either side, as "OR"
iPos = 1
Do
iPos = Instr(iPos, s, " ", 1)
If iPos = 0 then Exit Do
If Mid(s, iPos-1, 1) <> """" And Mid(s, iPos+1, 1) <> """" then
s = Left(s, iPos-1) & "]""" & " OR " & """[" & Mid(s, iPos+1)
iPos = iPos + 4
Else
iPos = iPos + 1
End If
Loop
'-- and now for the final wrap-it-up ...
SearchString = """[" & s & "]"""
End Function
%>
</body></html>
<HTML>
<HEAD>
<TITLE>Selected Book Details</TITLE>
</HEAD>
<BODY>
<%
'-- Booksearch2.asp is "drill-down" follow-on to Booksearch.asp
'-- 981219 JPC Rewrite to match interface of the new PixieWeb.dll
Dim Pixie
Set Pixie = Server.CreateObject("PixieWMA.clsAgent")
Pixie.TimeOut = 15
Pixie.Port = 23
Pixie.Host = "xxx.xxx.xxx.xxx"
Call Pixie.Connect("user")
Call Pixie.ExecuteET("xxxxxx", ":")
Call Pixie.ExecuteET("xxxxxx", "<<< Pick Systems")
Call Pixie.ExecuteET("term mm-mon", ":")
Call Pixie.ExecuteET("echo off",":")
itemid = Request.QueryString("itemid")
s = "LIST books WITH a0 = """ & itemid & """ title fauthor author isbn edition pub.yr price (hn)"
'-- DEBUG with next line, take out comment mark to activate
'-- Response.Write "<p><PRE>DEBUG.PRINT: " & vbCrLf & s & "</PRE><P>"
Call Pixie.ExecuteET(s, Chr(0) & "[40")
If Pixie.State = "ERROR" Then
s = "<H2> ERROR in database communication..."
s = s & "<P><CENTER>""" & Pixie.Response & """</CENTER>"
s = s & "<P><A HREF=""../librarysupply/booksearch.htm"">Try again</A>"
s = s & "<P><A HREF=""../default.htm"">"
s = s & "Back to Total Home Page</A></H2>"
Response.Write s
Else
Call OutputRoutine
End If
'---END OF Script-level Program --------------------------------------------
Sub OutputRoutine
'-- Output of results back from Pick
'-- Page Header
Response.Write "<table width=""100%"" border=""3"" "
Response.Write " bordercolor=""#800080"" cellpadding=""3""> "
Response.Write "<tr>"
Response.Write "<td width=""50%"" bgcolor=""#008080"" border=""0"">"
Response.Write "<font size=4 color=""#FFFFFF"">Library Supply Company</font></td>"
Response.Write "<td width=""50%"" bgcolor=""#008000"" align=right border=""0"">"
Response.Write "<font size=4 color=""#FFFFFF"">Catalogue Search: Title Details</font></td>"
Response.Write "</tr></table>"
Response.Write "<a href=""booksearch.htm"">Try another Search</a></strong></p>"
Response.Write "<hr>"
Call Pixie.ResponseCleanup
sResponse = Pixie.Response
sResponse = Mid(sResponse, Instr(sResponse, "books"))
'-- analyse sResponse
n2 = Instr(sResponse, vbcrlf)
s = Left(sResponse, n2 - 1)
ItemID = Trim(Mid(s, 15))
Do
n1 = n2 + 2
n2 = Instr(n1, sResponse, vbcrlf)
s = Mid(sResponse, n1, n2 - n1)
sID = Trim(Left(s, 15))
sData = Trim(Mid(s, 16))
Select Case sID
Case "TITLE": Title = sData
Case "FNAME": FName = sData
Case "AUTHOR": SName = sData
Case "ISBN NUMBER...": ISBN = sData
Case "ED": Edition = sData
Case "PUB.YR": Published = sData
Case "LIST.PRICE": ListPrice = sData
Case "" : Exit Do
End Select
Loop
'-- Table within table
' Start outside table: Details on left: Picture on right
' 1st Row is headers
Response.Write "<table width=""100%"" border=""3"" "
Response.Write " bordercolor=""#800080"" cellpadding=""3""> "
Response.Write "<tr>"
Response.Write "<td width=""60%"" bgcolor=""#008080"" border=""0"">"
Response.Write "<font size=4 color=""#FFFFFF"">Book Details</font></td>"
'-- Row 1 col 2
Response.Write "<td width=""40%"" bgcolor= ""#008080""border=""0""align=center >"
Response.Write "<font size=4 color=""#FFFFFF"">Picture if available</font></td>"
'-- Row 2
Response.Write "<tr>"
'-- Col 1 has table within a cell
Response.Write "<td width=""60%"">"
Response.Write "<table border=""2"" width=""100%"">"
Response.Write "<tr>"
Response.Write "<td width=""30%"">"
Response.Write "Item ID</td>"
Response.Write "<td width=""70%"">"
Response.Write ItemID & "</td></tr>"
Response.Write "<tr>"
Response.Write "<td width=""30%"">"
Response.Write "Title</td>"
Response.Write "<td width=""70%"">"
Response.Write Title & "</td></tr>"
Response.Write "<tr>"
Response.Write "<td width=""30%"">"
Response.Write "Author" & "</td>"
Response.Write "<td width=""70%"">"
Response.Write FName & " " & SName & "</td></tr>"
Response.Write "<tr>"
Response.Write "<td width=""30%"">"
Response.Write "ISBN" & "</td>"
Response.Write "<td width=""70%"">"
Response.Write ISBN & "</td></tr>"
Response.Write "<tr>"
Response.Write "<td width=""30%"">"
Response.Write "Edition" & "</td>"
Response.Write "<td width=""70%"">"
Response.Write Edition & "</td></tr>"
Response.Write "<tr>"
Response.Write "<td width=""30%"">"
Response.Write "Published" & "</td>"
Response.Write "<td width=""70%"">"
If Published > "" Then Response.Write "19" & Published & "</td></tr>"
Response.Write "<tr>"
Response.Write "<td width=""30%"">"
Response.Write "List Price" & "</td>"
Response.Write "<td width=""70%"">"
Response.Write ListPrice & "</td></tr>"
Response.Write "</table>"
Response.Write "<td width=""40%"" align=center><img src=""../librarysupply/images/" & ItemID & ".jpg"">"
Response.Write "</table>"
Response.Write "</tr></table>"
'-- DEBUG with next 3 lines, take out comment marks to activate
'Response.Write "<pre>"
'Response.Write "Debug Raw Output<BR><BR>"
'Response.Write sResponse & "</pre>"
'-- Standard bottom-of-page
s = "</center></div><p>"
s = s & "<a HREF=""../pixieinfo/default.htm"">PixieWeb active pages</a>"
s = s & " "
s = s & "<a HREF=""../utilities/jpclinks.htm"">Downloads</a>"
s = s & " "
s = s & "<a HREF=""../totproducts.htm"">Products</a>"
s = s & " "
s = s & "<a HREF=""../totprices.htm"">Prices</a>"
s = s & " "
s = s & "<a HREF=""../default.htm"">Back to home page</a></p><hr>"
s = s & "<p>The TOTAL Computer Company, 1<sup>st</sup> Floor, "
s = s & "Van Leer Packaging House, Clark Street, New Lynn, Auckland, "
s = s & "P.O. Box 15865, New Lynn,<br>"
s = s & "Telephone: +6-9-827 8775, Fax: +64-9-827 5899,<br>"
s = s & "email: <a HREF=""mailto:total@thetotal.co.nz"">"
s = s & "total@thetotal.co.nz</a></p>"
Response.Write s
End Sub
'-- End of Sub OutputRoutine
%>
</body></html>
Next: Chapter 5 , DATABASIC programs as backservers for "PixieWeb".
Appendix: summarises Pixie object properties and methods.
Chapter 1 Chapter
2 Chapter 3
** Top ** Chapter 5
Chapter 6 Chapter
7 Chapter 8
Appendix
Site Map