Monday May 29, 2017

       
      SteelArrow Basics
      SteelArrow Tags
  SteelArrow Functions
  SteelArrow Objects
  Advanced Topics
  WAS Overview
   
   
       
      Overview
  Code Samples
  Site Search
   
   
   


Get SteelArrow!     
  Online Documentation    
 
   
 
  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.
TEXTOnly the text (ignores HTML tags).
COUNT(HTMLTAG)The total number of specified HTMLTAG.
entityA 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:
 
MSGNOMessage number assigned by server
DATEMessage date
FROMName and email address of sender
TOName and email address of receiver
CCName and email address of add'l receivers
SUBJECTMessage subject
BODYMessage body
ATTACHMENTMessage 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#).

 





Copyright © 1998-2004 Tomahawk Technologies Inc.
Privacy Policy