Chapter 3 DynaScript Predefined Objects

query object

Object

Provides a scriptable way to work with a SQL query embedded in a Dynamo template.

Syntax

To use a query's property:

query.propertyName

To use a query's method:

query.MethodName( parameter )

Description

Each embedded query has a name (by default, SQL ). In scripts following the embedded query, you can obtain and change information about the query by working with the query object of the same name.

You can also create a query object from within a script. To do this, use connection.CreateQuery.

Example

The following query prints out the number of rows in a result set:

<!--SQL
  SELECT id from "dba".product
-->
<!--SCRIPT
  document.WriteLn( "We stock " + SQL.GetRowCount()  + " different products." );
-->

In this example, the query has the default name of SQL . If the SQL tag explicitly specified a name, as in:

<!--SQL NAME = productQuery

then you would reference the query accordingly:

productQuery.GetRowCount()

In the script that follows the query, SQL.GetRowCount() is the GetRowCount method of the SQL query object. It takes no arguments, and returns the number of rows in the query result set.

The following complete template shows how a script can use the query object to access the results of an embedded query. The template executes a query on the product table and formats the result in a table. Although you format using a FORMATTING tag, using scripts provides more flexibility:

<HTML>
<TITLE>SQL and script example</TITLE>
<BODY>
<!--SQL
    select id, name, size, color, quantity, unit_price     from product

-->

<!--SCRIPT
    thisColumnCount = SQL.GetColumnCount();

    // Mark the start of the table
    document.Write( "<TABLE BORDER ><TR>" );

    // Write out table titles
    for ( iCol=1 ; iCol<=thisColumnCount ; iCol++ ) {
        document.Write( "<TH>" +
            SQL.GetColumnLabel( iCol ) + "</TH>");
    } 
    document.WriteLn( "</TR>" );

    // Write out table values
    while( SQL.MoveNext() ) {
        for ( iCol = 1; iCol <= thisColumnCount; iCol++ ) {
        document.Write( "<TD>" + SQL.GetValue( iCol ) + "</TD>" );
    }
    document.WriteLn( "</TR>" );
    
    //Mark the end of the table
    document.WriteLn( "</TABLE>" );
    -->
</BODY>
</HTML>

This query has the default name SQL ; Columns and rows are numbered starting at 1.

The HTML table tags used in this example are:
- <TABLE> and </TABLE> mark the start and end of the table.
- <TR> and </TR> mark the start and end of a table row.
- <TH> and </TH> mark the start and end of a table heading cell.
- <TD> and </TD> mark the start and end of a table data cell.
The while statement is used to loop over the rows of the table.
The for statement is used to loop over the columns of each row.