Working with forms

First Guide to SQL Anywhere Studio
PART 2. Getting Results with SQL Anywhere Studio
CHAPTER 10. Building a Dynamic Web Site with PowerDynamo

Working with forms

Instead of using the formatting repeated for each row of output, which is delimited by the  tags, you can access and program the results of queries from within DynaScript. DynaScript is an object oriented scripting language. DynaScript objects can have properties (data) associated with them as well as methods (functions that act on the properties).

The query object provides a scriptable way to work with a SQL query embedded in a Dynamo template.

In this project you will:

Create a form that asks a user to select which department they would like to display employee information for.
Create a template, containing DynaScript that queries the sample database as a result of the input provided to the form.

Create a form

From Sybase Central start the Add Template wizard.
Call the new template Department.stm and click Next.
Enter a description of Department employee list and click next.
Select inherited as the connection type and click next.
Click Next and Finish until the wizard is closed.

Open the Department.stm file and enter the following HTML. Existing code in the template should be deleted so that the following HTML is the only content:

<HTML>
<TITLE>department.stm</TITLE>
<BODY>
<H1>Department employee list</H1>
<HTML>
<P>Select the department that you would like employee information for</P>
<FORM METHOD=POST ACTION="mulchoice.stm" MULTIPLE SIZE="5">
<OL>
<INPUT TYPE="radio" NAME="choice" value="R & D">Research and Development<BR>
<INPUT TYPE="radio" NAME="choice" value="Sales">Sales<BR>
<INPUT TYPE="radio" NAME="choice" value="Finance">Finance<BR>
<INPUT TYPE="radio" NAME="choice" value="Marketing">Marketing<BR>
<INPUT TYPE="radio" NAME="choice" value="Shipping">Shipping<BR>
</OL>
<P><INPUT TYPE="submit" VALUE="Submit List"></P>
<P><INPUT TYPE="RESET" VALUE="Clear Form"></P>
</FORM>
</BODY>
</HTML>

The above HTML creates a form for which a user is required to select a category for which he would like data displayed. Once a value has been selected it will be used in the next template, mulchoice.stm, to generate results.

Save and close the template.

Create a template which contains a query and output manipulation in DynaScript

Start the Add Template wizard again and create a new template called mulchoice.stm. Create this template in the same manner that you created Department.stm.

Open the mulchoice.stm template and add the following code. Existing code in the template should be deleted so that the following code is the only content:

<HTML>
<TITLE>Selected Department</TITLE>
<BODY>
<!--SCRIPT 
    document.WriteLn("<H1> The ")
    document.WriteLn(document.value.choice)
    document.WriteLn("Group </H1>")
var selectStr = "select emp_lname, emp_fname, phone from employee, department where department.dept_id = employee.dept_id";
if(exists(document.value.choice)){
    selectStr += " and department.dept_name = " + "'" + document.value.choice + "'" ;
}
myQuery=connection.CreateQuery(selectStr);
colCount=myQuery.GetColumnCount();
document.WriteLn("<TABLE BORDER>");
for (i=1; i <= colCount; i++){
    document.WriteLn("<TH>");
    document.WriteLn(myQuery.GetColumnLabel(i));
    document.WriteLn("</TH>");
}
   
while (myQuery.MoveNext()){
    document.WriteLn("<TR>");
    for (i=1; i <= colCount; i++) {
        document.WriteLn("<TD>");
        document.Write(myQuery.GetValue(i));
        document.WriteLn("</TD>");
    }
    document.WriteLn("</TR>");
}
document.WriteLn("</TABLE>");
    
-->

</BODY>
</HTML>

Notes

The following DynaScript methods and properties are being used in the mulchoice.stm template:

document.value.variable used to pass in the argument from Department.stm
document.WriteLn(outputString) used to append the outputString to the output of the document. In the case of mulchoice.stm, document.WriteLn is being used to add HTML tags to the document and to display the query output.
connection.CreateQuery( ) executes the query and returns the query object.
query.GetColumnCount( ) returns the number of columns in the query result set.
Save and close the template.

Open Department.stm in your browser.

http://localhost/ASASite/depoartment.stm

Select a department and click Submit List.
Open and examine the templates Department.stm and mulchoic.stm. Look at each line of code individually so that you understand the relationship between what you see in your browser and the two templates that you created.

For Info For further information about DynaScript methods and properties see the PowerDynamo User's Guide.