Database Scripting

Sambar Server Documentation

Database Scripting

Database Scripting Overview
The following instructions are an overview of database scripting interfaces available in the Sambar Server. The database scripting described below is a subset of Sambar Scripting tags available for use with the Sambar Server. The scripting works by allowing HTML designers to embed SQL statements and simple macros into HTML files. These are processed by the Sambar Server database interfaces which handle database queries to generate dynamic HTML output. You must enable the Sambar Server database interfaces prior to using the Sambar database scripting macros.

Template Structure
Using the Sambar Server scripting interfaces, a typical set of forms might provide data entry for a SQL query on one page, and then query a database using the form input on a subsequent page. The following is an example of a page that requests a lastname argument as input:

<HTML> <BODY> <FORM method=post action=page2.stm> <INPUT type=text name="lastname"> <INPUT type=submit> </FORM> </BODY> </HTML>

The argument lastname provided in the input form is then passed to page2.stm where a database is queried for all employees with a corresponding last name:

<HTML> <BODY> <TABLE border=1> <RCQtest sql="SELECT fname,lname,phone from employees where lname = 'RC$lastname'" format="<TR><TD>%s<TD>%s</TD><TD>%s</TD><TR>" > </TABLE> </BODY> </HTML>

In the page2.stm template, a connection from cache test is used to query for the matching employees. The Sambar Server captures the input from the first page and stores the variables passed as arguments (lastname in this case). These variables can be accessed in another Sambar Server template by simply using the macro of the form: RC$variable. The employees found as a result of the query are displayed in a table using the format directive (see below).

Querying a database
The syntax for a database query is as follows:

<RCQcachename sql="sql-query">

Any SQL statement that the ODBC data source supports can be used in the Sambar Server database scripting interface. This includes INSERT, UPDATE and DELETE statements as well as stored procedures from Microsoft and Sybase (i.e. exec sp_who).

You can use variable references anywhere except in the optional format value to modify the query arguments. Variables names must be limited to alpha-numeric characters when used in embedded strings as in the following:

<RCQcachename sql="SELECT custid,custname from customers where custname like 'RC$name%'">

and

<RCQcachename sql="SELECT * from RC$data where cod = 'RC$desc'">

Important! Note that when a tag is embedded in another tag (i.e. RC$name in the above example), the tag designators ( < > ) are not used. Also, if the string specified by the sql parameter exceeds 2000 bytes, it will not be evaluated for embedded tags -- it will be executed as-is (2000 bytes is the maximum buffer size).

Another significant feature is that when one tag is embedded in a SQL query string, the tag is checked for embedded single-quote (') characters. If found, the single-quote character is "escaped" with a second single-quote character (as specified by the ODBC specification). Note: the included tag must be immediately preceded by a single quote as in the above example.

Retrieving results
There are two means of retrieving results from a database query. The easiest is to use the format argument:

<RCQtest sql="SELECT name,phone from employees" format="Name: %s Phone: %s<BR>" >

If present, the format argument is used with sprintf to iterate through each row of the result set and format it for the client. All arguments, regardless of their type are cast to their character string equivalent, so the sprintf argument %s is the only valid parameter. The maximum length of any script value in the Sambar Server is 4096 with the exception of the format output which can be up to 8192 bytes when fully formatted. Warning! Failure to provide the proper format argument could result in a server crash!

The maxrows argument can be used to indicate the maximum number of rows to process. This allows you to limit the size of a result set. The following HTML limits the employees returned to the first 100 rows (or less):

<RCQtest sql="SELECT name,phone from employees" format="Name: %s Phone: %s<BR>" maxrows=100 >

A second optional argument, rowstart can be used to indicate the first row to start displaying. If specified, the rows numbering less that rowstart are discarded.

The number of rows affected by the query (in the case of UPDATE, INSERT, DELETE), or the number of rows retrieved and displayed with the format tag are stored and available immediately after the query is executed in the RCErowsaffected script tag. This rowsaffected variable can be used to determine if a query returned any rows. Using an if/then statement such as: <RCif RCErowsaffected > 0> you can conditionally display a message when no rows are returned.

The second mechanism that can be used to retrieve result rows is the RCFcachename script tag. This fetch directive can be used to get the next row of a result set from a query. The individual column data for the current row can then be accessed by referencing the variables RCDcachename.1, RCDcachename.2 etc. (from 1 to the number of available columns in the result set). Note that RCFcachename is required after a SQL query in order to populate the result set variables with the first row. When using the format argument, no fetch is required since the data is implicitly retrieved and formatted.

<RCQtest sql="SELECT name,phone from employees" > <RCwhile RCFtest = 1> Name: <RCDtest.1> Phone: <RCDtest.2><BR> <RCendwhile>

The RCwhile loop retrieves as long as the fetch command RCFtest returns 1. The fetch mechanism returns 1 if data was retrieved and 0 if there are no more result sets. Important! RCwhile loops may not be nested and may not loop over more than 4000 bytes of HTML and/or scripting content!

The fetch mechanism can be useful when more control is necessary in processing the results. For example, the following query eliminates any references to "Bob Smith" from the phone list.

<RCQtest sql="SELECT name,phone from employees" > <RCwhile RCFtest = 1> <RCif RCDtest.1 ! "Bob Smith"> Name: <RCDtest.1> Phone: <RCDtest.2><BR> <RCendif> <RCendwhile>

If the maxrows argument is provided to the initial query, it will apply to the fetch commands as well.

In the following simple example, the query is known to return only a single result, so a single fetch command is issued.

<RCQtest sql="SELECT SUM(netcount) from netstats" > <RCFtest> Network Count: <RCDtest.1>

Finally, the showerror argument can be used in the query to indicate whether query failures (ODBC errors) should be displayed within the HTML page. By default, all errors are reported. The following illustrates how to execute a query and ignore any errors:

<RCQtest sql="SELECT foo from bar" showerror=false> <RCFtest> Foo is: <RCDtest.1>

Closing a Query/Connection
When a database request is processed the results are returned to the client browser and the connection is automatically returned to the cache pool when the results have been processed. Any requests that have not finished processing when the template is finished (i.e. not all rows retrieved) processing are terminated automatically.

Multiple queries
If you wish to use more than one data source within a page, each datasource must come from a separate connection cache. The only exception to this is if all results from the first query are processed prior to the second query being executed.

Specifying Username/Password
By default, the username and password for the DBMS connection are retrieved from the config/dbconfig.ini file. However, the RCQ directive can take an optional username and password parameters which will be used to override the cache configuration. Several users requested this feature to enhance DBMS security. The limitation of using this feature is that the connection will not be cached for subsequent use (each query results in a new connection handle being allocated). A second configuration option Require Authentication results in requiring a username and password to be specified via RCQ in order for the connection to be used.

<RCQtest username="foo" password="bar" sql="SELECT name,phone from employees" >

Important! Specifying the username and password for the connection results in the connection being setup and torndown for each request. This can be desirable for both security reasons or to eliminate hanging issues that result when using ODBC connections to DBase and Excel files.

Example Scripting
The following example from Martin Stannard illustrates how database scripting might be used to query companies from a database table:

<!-- Page1.htm -->
<HTML><BODY>
<H1>Query</H1>

<form action=page2.htm method=POST>
<input type=text name=compname>
<input type=submit>
</form>

</BODY></HTML>

<!-- Page2.htm -->
<HTML><BODY>
<H1>Results</H1>

<table border=1>
<RCQmydb sql="SELECT company, comments from jobs where company = 'RC$compname'"
    format="<tr><td>%s</td><td>%s</td></tr>">
</table>

</BODY></HTML>

The RCQvalue has to correspond to the name you have given the entry in the dbconfig.ini file.

Text-to-HTML Data
The column fetch script element RCD retrieves the column data from the database. Very often the data will then be presented in an HTML page; so an alternative RCH retrieval argument can be used to escape any invalid HTML characters for presentation. This is the equivalent to: RC@txt2html(RCDcachename.1). Important! when the RCH column fetcher is used, the content will always be truncated at 1000 bytes. Error Handling
Error handling through the Sambar Scripting interface is very limited. In the event of a SQL failure, the argument RC$dbmserror is set with the error string. For robust error handling, the Sambar Server CScript interface is recommended.