|
QUERYing Datasources |
|
The QUERY tag is the most powerful of all tags available to the SteelArrow
developer. With this tag, a developer is able to retrieve information from
any ODBC compliant datasource, a web server, a mail server, a news server, and
is also able to make queries against HTML text.
The QUERY tag overrides the DATASOURCE attribute to offer all of these abilities
to a web developer. By simply specifying a protocol to use, SteelArrow is able to
determine what type of query is to be made.
|
|
|
Database Query |
|
The most used of all of the supported query types is the database query. This is
also the simplest, since it simply passes standard SQL statements through to the
selected (and configured) ODBC datasource.
In order to use the database query, it is necessary to configure a system DSN in the
ODBC data manager. Once this has been accomplished, SQL statements can be passed through
to the database very easily. [Note: SteelArrow also supports DB-lib, which allows it to
communicate directly to an SQL Server. We should also note, that it is possible to setup
an SQL Server database as an ODBC datasource, so the use of DB-lib is not required.]
The following is an example of QUERY with a DSN:
|
<SAQUERY NAME=dbQuery DATASOURCE="TestDB">
SELECT * from TestTable
</SAQUERY>
<!--- The next line outputs the raw table returned in the above query --->
<SAOUTPUT VALUE=dbQuery[]>
|
Of course any SQL statement can be passed through ODBC (or DB-lib) using this functionality.
In the above example, a view of the TestTable is returned in the variable 'dbQuery'. To
iterate over this returned information, a developer can use the table navigation tags:
FIRST, LAST, NEXT,
PREV, and GOTO. Usually, a
WHILE loop is also implemented to offer simple data output.
The following is an example of outputting the data returned in the above query:
|
<SAOUTPUT SCOPE=dbQuery>
<SAWHILE COND=dbQuery.MoreData()>
#Name# #Phone# #Fax#<BR>
<SANEXT NAME=dbQuery>
</SAWHILE>
</SAOUTPUT>
|
In this example, the SAOUTPUT tag was used to simplify the data
output. The SCOPE attibute within the SAOUTPUT tells SteelArow to look within that variable for
'Name', 'Phone', and 'Fax'. If any of these variables are not found in the variable 'dbQuery',
SteelArrow will look in the global namespace for the variable. In this case, we assume that each
of the columns was in fact part of the returned data. As well, by using the SAOUTPUT we have
saved ourselves a great deal of typing since <SAOUTPUT VALUE=dbQuery.Name> would have achieved
the same output for the 'Name' column.
This functionality is available for all table objects, whether they are created by SteelArrow, or
created by the developer themself using the MakeTable function.
The QUERY tag functionality also allows for INSERT, UPDATE and DELETE type queries. These queries
do not return data, but will return TRUE or FALSE depending on the outcome of the operation.
The QUERY tag validates all data before passing it through to the database layer. In doing so, it
replaces all single quotes with two single quotes. It also replaces empty variables with the keyword
'null'; but only if they are wrapped in single quote characters and as a result are perceived to be
textual values.
|
|
|
Web Server Query |
|
The QUERY tag also supports querying another web server. To do this, simply set the URL in the
datasource attribute. The following shows an example of this functionality:
|
<SAQUERY NAME=htQuery
DATASOURCE="http://www.steelarrow.com/index.aro">
SELECT * from TITLE
</SAQUERY>
<!--- The next line outputs the raw data returned in the above query --->
<SAOUTPUT VALUE=htQuery[]>
|
In this example, the web server 'www.steelarrow.com' is accessed, and the TITLE element of the requested
page (index.aro) is returned in the variable 'dbQuery'. If no data is returned, or the web server cannot be
contacted, SteelArrow returns the message 'No data returned' in the specified variable.
SteelArrow supports a SQL like syntax for retrieving different elements from a given URL. The following
is a summary of the supported functionality:
|
SELECT *|HTMLTAG{count}|TEXT|COUNT(HTMLTAG) FROM entity
(where * represents all data (HTML tags and text)) |
|
HTMLTAG{count} | Recognized HTML tag with optional count. |
TEXT | Only the text (ignores HTML tags). |
COUNT(HTMLTAG) | The total number of specified HTMLTAG. |
entity | A sub-SELECT or HTML entity (BODY tag is assumed). |
|
SELECT COUNT(TR) FROM TABLE{1} | Number of TRs in TABLE 1 |
SELECT TEXT FROM P{3} | TEXT only from Paragraph 3 |
SELECT IMG{3} | Third IMG in document |
SELECT TD{2} FROM TABLE{3} | Table Data 2 from Table 3 |
SELECT P{2} FROM SELECT TD{3} FROM TABLE{2} |
P 2 from TD 3 within Table 2 |
For efficiency, this sae functionality is mimicked in the HTML text override of the QUERY tag. To
use this functionality, specify "html:" as the first 5 characters of the text to be used in the query.
This allows the developer to make one query on a web server, and then make numerous queries on the
data that was returned; saving system resources.
The following shows an example of this type of query:
|
<SAQUERY NAME=htQuery
DATASOURCE="http://www.steelarrow.com/index.aro">
SELECT * from BODY
</SAQUERY>
<!--- Simple string concatenation --->
<SASET NAME=htQuery VALUE="html:" & htQuery>
<SAQUERY NAME=table1 DATASOURCE=htQuery>
SELECT TABLE{1} from BODY
</SAQUERY>
<SAQUERY NAME=meta DATASOURCE=htQuery>
SELECT META from HEAD
</SAQUERY>
|
|
|
Mail & News Server Queries |
|
The QUERY tag supports requests against a mail server. This functionality is used with a POP3 server, and
can be achieved by specifying "pop3://" as the first 7 characters of the datasource. The supported functionality
allows for the retrieval and deletion of mail messages. This functionality is used in our sample application
HawkMail.
The USERNAME and PASSWORD attributes are used to validate the user on the specified server. The following is an
example of this unctionality:
|
<SAQUERY NAME=popQuery
DATASOURCE="pop3://mail.steelarrow.com"
USERNAME="test" PASSWORD="test">
</SAQUERY>
|
The data returned in the variable 'popQuery' will be structured as a table with the following columns:
|
MSGNO | Message number assigned by server |
DATE | Message date |
FROM | Name and email address of sender |
TO | Name and email address of receiver |
CC | Name and email address of add'l receivers |
SUBJECT | Message subject |
BODY | Message body |
ATTACHMENT | Message attachment(s) |
STATUS | (String) Message denoting the success or failure |
To delete a message, the query "DELETE msgno" can be used; where msgNo represents the message number returned
from the server that is to be deleted. In this case the STATUS variable is updated with a success to failure
message.
This same functionaliy (excpet DELETE) is implemented with NNTP (news). By specifying "nntp://" as the first
7 characters of your datasource, a news server can be accessed.
The news server access is different in that you may returnd 'x' number of messages from a particular newsgroup.
The followign shows an example of the news functionality:
|
<SAQUERY NAME=newsQuery DATASOURCE="nntp://ott.test">
HEADERS 10 TO 125
</SAQUERY>
<SAQUERY NAME=newsQuery DATASOURCE="nntp://ott.test">
MESSAGES 1 TO 10
</SAQUERY>
<SAQUERY NAME=newsQuery DATASOURCE="nntp://ott.test">
MESSAGE 1298
</SAQUERY>
|
One thing to note, is that in all cases, SAOUTPUT is assumed within the QUERY tag data. This means that
variables values can easily be output within the QUERY tag by wrapping the variable in '#' signs (ie. #var_name#).
|
|