Chapter 3 DynaScript Predefined Objects
Provides a scriptable way to work with a SQL query embedded in a Dynamo template.
To use a query's property:
query.propertyName
To use a query's method:
query.MethodName( parameter )
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.
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.
<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.
while
statement
is used to loop over the rows of the table.
for
statement
is used to loop over the columns of each row.
query.connection
This property is read-only.
The connection object used to execute the query.
This example displays the name of the connection used to execute the query:
<!--SQL select name from product --> <!--SCRIPT document.WriteLn( SQL.connection.name ); -->
query.cursorType
Use this property when you are executing stored
procedures in a Microsoft SQL Server database that return multiple
result sets. The cursorType
must
be set to ForwardOnly
to
avoid problems during execution.
This example executes a stored procedure that returns multiple results sets from a Microsoft SQL Server database:
<!--SCRIPT MSSQL_MultiStatement.ssc
/*
CREATE PROCEDURE test3 AS
select * into #footable from employee
select * from #footable
*/
conn = site.GetConnection( "MSSQLServer" );
// create an empty query
q = conn.CreateQuery();
q.cursorType = "ForwardOnly";
q.SetSQL( "exec test3" );
q.Execute();
if( q.GetErrorCode() != 0 ) {
document.writeln(q.GetErrorInfo());
}
while( q.MoveNext() ){
document.writeln( q.GetValue(1)+' '+q.GetValue(2)+' '+q.GetValue(3)+' '+q.GetValue(4) );
}
-->
query.stripTrailingBlanks
Use this property to strip trailing blanks
from query data. This property is quite useful when executing queries
against databases that have trailing blank characters such as Adaptive
Server Enterprise. You can set the stripTrailingBlanks
property
to true or false. The default is false.
This example executes a query against an Adaptive Server Enterprise database and strips that trailing blanks from the output:
<!--SCRIPT
SQL=connection.CreateQuery("select distinct name from product order by name" );
SQL.stripTrailingBlanks = true;
while SQL.MoveNext() {
document.WriteLn("[" +SQL.GetValue(1) + "]");
}
-->
The output from this example is:
[Baseball Cap] [Shorts] [Sweatshirt] [Tee Shirt] [Visor]
Without stripping the trailing blanks, the output might look like this:
[Baseball Cap ] [Shorts ] [Sweatshirt ] [Tee Shirt ] [Visor ]
You can also use the stripTrailingBlanks
property
in conjunction with the ResultsToXMLString
:
<!--SCRIPT
SQL=connection.CreateQuery("select distinct name from product order by name" );
SQL.stripTrailingBlanks = true;
document.WriteLn( SQL.ResultsToXMLString() );
-->
query.Close( )
Closes the active query. To reestablish the query you must execute it again.
Closing a query
A query is automatically closed when
the query object (myQuery
in
the following example) goes out of scope. Typically, this is at
the end of the script.
Boolean. This method returns true or false, indicating whether the method succeeded or not.
This example executes a query, then closes it.
<!--SCRIPT myQuery = connection.CreateQuery ( "update product set color = 'Red' where color = 'White'" ); myQuery.Close(); -->
query.Execute( )
Executes the current query object's SQL statement.
Boolean. This method returns true or false, indicating whether the method succeeded or not.
This example prepares a query using a SQL
tag
with the attribute NO_EXECUTE
, and
later executes the query from within a SCRIPT
tag:
<!--SQL NO_EXECUTE NAME=myQuery select id from "dba".product --> <!--SCRIPT myQuery.Execute(); -->
query.GetColumnCount( )
Returns the number of columns in the query result set.
This example writes out the labels for each column within the result set of the query:
<!--SCRIPT
myQuery = connection.CreateQuery( 'select lname, fname from customer' );
count = myQuery.GetColumnCount();
for( i = 1; i <= count; i++ ) {
document.WriteLn( myQuery.GetColumnLabel( i ) );
}
-->
query.GetColumnIndex(colName )
Returns the index of the column named colName [case-insensitive string] in the query, where the first column's index is 1.
Integer. If no match is found, returns false.
This example displays the index of the fname
column:
<!--SCRIPT
myQuery=connection.CreateQuery
( 'select lname, fname from customer' );
document.WriteLn( myQuery.GetColumnIndex("fname") );
-->
The output from this example is:
2
query.GetColumnLabel(colNum)
Returns the column name or column alias (as specified in the SQL query) for the colNum column. Columns are numbered starting with 1.
String. Returns the column name or alias.
This example writes out the labels for each column within the result set of the query:
<!--SCRIPT
myQuery = connection.CreateQuery( 'select lname, fname from customer' );
count = myQuery.GetColumnCount();
for( i = 1; i <= count; i++ ) {
document.WriteLn( myQuery.GetColumnLabel( i ) );
}
-->
query.GetEmpty( )
Indicates if the result set is empty.
Note
If no Move method has yet been called
on the query object, calling GetEmpty moves the cursor to the first
row of the result set.
Boolean. This method returns true if the result set is empty.
This example performs two queries and then displays one of two outputs, depending on the result of the query:
<!--SQL NAME = White
select id from product where color = 'white'
-->
<!--SQL NAME = Red
select id from product where color = 'red'
-->
<!--SCRIPT
document.WriteLn( "There are " + (White.GetEmpty()? "no" : "some" ) + " white products." );
document.WriteLn( "There are " + (Red.GetEmpty()? "no" : "some" ) + " red products." );
-->
The output from this example is:
There are some white products. There are no red products.
query.GetErrorCode( )
Returns the current error code.
Integer. Returns zero if a SQL instruction is carried out correctly, and information to which the script can respond if it fails.
This example displays a message that indicates whether the query was successfully executed or not:
<!--SQL
select id from product
-->
<!--SCRIPT
document.WriteLn( "The query" );
if ( SQL.GetErrorCode()==0 ) {
document.WriteLn( "succeeded." );
} else {
document.WriteLn( "failed." );
}
-->
query.GetErrorInfo( )
A description of the error.
String.
This example displays a message indicating why the query failed:
<!--SCRIPT myQuery=connection.CreateQuery( 'select badname from product' ); document.WriteLn( myQuery.GetErrorInfo() ); -->
The output from this example is:
[Sybase][ODBC Driver]: column 'badname' not found
query.GetRowCount( )
The number of rows in the result set of the current query.
[Adaptive Server Enterprise]
Row count is not always available
for some database servers, including Adaptive Server Enterprise.
If unavailable, a return of -1 is given.
This example displays the number of products in the query result set:
<!--SQL select id from product --> <!--SCRIPT document.WriteLn( "We stock " + SQL.GetRowCount() + " products." ); -->
query.GetState( )
Returns the current SQL state of the query.
The values returned by GetState
depend
on your database driver. For more information, see your database's
documentation.
This example displays the SQL state for the query:
<!--SQL NO_SQL_ERROR select badname from product --> <!--SCRIPT document.WriteLn( SQL.GetState() ); -->
The output is:
S0022
query.GetValue(colNum|colName)
Returns the value in the colNum [integer] or colName[string] column of the current row of the result set.
This example displays a list of first and last names.
<!--SQL
select lname, fname from customer
-->
<!--SCRIPT
while ( SQL.MoveNext() ){
document.WriteLn( SQL.GetValue(1) + "," + SQL.GetValue(2) );
}
-->
The output from this example looks something like this:
Devlin,Michaels Reiser,Beth Niedringhaus,Erin Mason,Meghan McCarthy,Laura Phillips,Paul Colburn,Kelly Goforth,Matthew Gagliardo,Jessie Agliori,Michael Ricci,Dylan ...
query.Move(rowNum )
Changes the current row to the rowNum [integer] row of a query result set.
Cursor support for some database servers
The cursor support of some database
servers, including Adaptive Server Enterprise, is not as extensive
as that of Adaptive Server Anywhere, and some of the Move
functions
are unavailable.
Move
, MoveFirst
, MoveLast
, MovePrevious
,
or MoveRelative
with these
database servers you can simulate the cursor abilities of Adaptive Server
Anywhere. To do this for all connections, modify your registry.
From your system's Registry Editor, open the following
file: HKEY_LOCAL_MACHINE\SOFTWARE\SYBASE\SYBASE
TOOLS Add the new string value name Simulate
Cursors
with the string value of "yes"
.To do this for a specific connection, use the simulateCursors
property
of the connection object.Using simulated cursors causes a decline in
performance. When possible, use MoveNext
instead
of modifying your Registry settings.
Boolean. This method returns true or false, indicating whether the method succeeded or not.
This example displays the data in the fifth row of the result set:
<!--SQL select lname, fname from customer --> <!--SCRIPT SQL.Move(5) document.WriteLn( SQL.GetValue(1) + "," + SQL.GetValue(2) ); -->
query.MoveFirst( )
Changes the current row to the first row of a query result set.
Cursor support for some database servers
The cursor support of some database
servers, including Adaptive Server Enterprise, is not as extensive
as that of Adaptive Server Anywhere, and some of the Move
functions
are unavailable.
Move
, MoveFirst
, MoveLast
, MovePrevious
,
or MoveRelative
with these
database servers you can simulate the cursor abilities of Adaptive Server
Anywhere. To do this for all connections, modify your registry.
From your system's Registry Editor, open the following
file: HKEY_LOCAL_MACHINE\SOFTWARE\SYBASE\SYBASE
TOOLS Add the new string value name Simulate
Cursors
with the string value of "yes"
.To do this for a specific connection, use the simulateCursors
property
of the connection object.Using simulated cursors will cause a decline
in performance. When possible, use MoveNext
instead
of modifying your Registry settings.
Boolean. This method returns true or false, indicating whether the method succeeded or not.
This example moves through each of the rows of a query result set and repositions at the first row.
<!--SQL
select lname, fname from customer
-->
<!--SCRIPT
while ( SQL.MoveNext() ) {
document.WriteLn( SQL.GetValue(1) + "," + SQL.GetValue(2) );
}
SQL.MoveFirst();
-->
query.MoveLast( )
Changes the current row to the last row of a query result set.
Cursor support for some database servers
The cursor support of some database
servers, including Adaptive Server Enterprise, is not as extensive
as that of Adaptive Server Anywhere, and some of the Move
functions
are unavailable.
Move
, MoveFirst
, MoveLast
, MovePrevious
,
or MoveRelative
with these
database servers you can simulate the cursor abilities of Adaptive Server
Anywhere. To do this for all connections, modify your registry.
From your system's Registry Editor, open the following
file: HKEY_LOCAL_MACHINE\SOFTWARE\SYBASE\SYBASE
TOOLS Add the new string value name Simulate
Cursors
with the string value of "yes"
.To do this for a specific connection, use the simulateCursors
property
of the connection object.Using simulated cursors will cause a decline
in performance. When possible, use MoveNext
instead
of modifying your Registry settings.
Boolean. This method returns true or false, indicating whether the method succeeded or not.
This example displays the results of a query from the end of the result set to the beginning:
<!--SQL
select lname, fname from customer
-->
<!--SCRIPT
SQL.MoveLast();
do {
document.WriteLn( SQL.GetValue(1) + "," + SQL.GetValue(2) );
} while ( SQL.MovePrevious() );
-->
The "simulateCursors property"
query.MoveNext( )
Changes the current row to the next row of a query result set.
Boolean. This method returns true or false, indicating whether the method succeeded or not.
This example displays the data in each row of a query result set.
<!--SQL
select lname, fname from customer
-->
<!--SCRIPT
while ( SQL.MoveNext() ){
document.WriteLn( SQL.GetValue(1) + "," + SQL.GetValue(2) );
}
-->
query.MovePrevious( )
Changes the current row to the previous row of a query result set. This is a method of the query object.
Cursor support for some database servers
The cursor support of some database
servers, including Adaptive Server Enterprise, is not as extensive
as that of Adaptive Server Anywhere, and some of the Move
functions
are unavailable.
Move
, MoveFirst
, MoveLast
, MovePrevious
,
or MoveRelative
with these
database servers you can simulate the cursor abilities of Adaptive Server
Anywhere. To do this for all connections, modify your registry.
From your system's Registry Editor, open the following
file: HKEY_LOCAL_MACHINE\SOFTWARE\SYBASE\SYBASE
TOOLS Add the new string value name Simulate
Cursors
with the string value of "yes"
.To do this for a specific connection, use the simulateCursors
property
of the connection object.Using simulated cursors will cause a decline
in performance. When possible, use MoveNext
instead
of modifying your Registry settings.
Boolean. This method returns true or false, indicating whether the method succeeded or not.
This example displays the results of a query from the end of the result set to the beginning:
<!--SQL
select lname, fname from customer
-->
<!--SCRIPT
SQL.MoveLast();
do {
document.WriteLn( SQL.GetValue(1) + "," + SQL.GetValue(2) );
} while ( SQL.MovePrevious() );
-->
query.MoveRelative(relativeRowNum)
Moves to a row that is relativeRowNum [integer] rows before or after the current row of a query result set.
Cursor support for some database servers
The cursor support of some database
servers, including Adaptive Server Enterprise, is not as extensive
as that of Adaptive Server Anywhere, and some of the Move
functions
are unavailable.
Move
, MoveFirst
, MoveLast
, MovePrevious
,
or MoveRelative
with these
database servers you can simulate the cursor abilities of Adaptive Server
Anywhere. To do this for all connections, modify your registry.
From your system's Registry Editor, open the following
file: HKEY_LOCAL_MACHINE\SOFTWARE\SYBASE\SYBASE
TOOLS Add the new string value name Simulate
Cursors
with the string value of "yes"
.To do this for a specific connection, use the simulateCursors
property
of the connection object.Using simulated cursors will cause a decline
in performance. When possible, use MoveNext
instead
of modifying your Registry settings.
Boolean. This method returns true or false, indicating whether the method succeeded or not.
This example displays the data in the row that is sixth from the end of the rowset and then the second last row of the rowset:
<!--SQL select lname, fname from customer --> <!--SCRIPT SQL.MoveLast(); SQL.MoveRelative(-5) document.WriteLn( SQL.GetValue(1) + "," + SQL.GetValue(2) ); SQL.MoveRelative(4); document.WriteLn( SQL.GetValue(1) + "," + SQL.GetValue(2) ); -->
query.Opened( )
Indicates whether a query object is open. The query object is opened either by calling the Execute method on the query, using the site.CreateQuery method, or by executing the query through a SQL tag.
Boolean. This method returns true if a query object is open.
This example displays whether the query was successfully opened:
<!--SQL select lname, fname from customer --> <!--SCRIPT document.WriteLn( "The query is " + ( SQL.Opened()? "open." : "closed." ) ); -->
query.Refresh( )
Reexecutes the current query and obtains a new result set. This may or may not reset the current cursor. If the cursor cannot be moved to the current row (because, for example, the row has been deleted), the cursor is moved to the first row of the result set.
To guarantee that the cursor is at the beginning
of the result set after calling Refresh
,
you must call MoveFirst
(or Move(0)
).
Boolean. This method returns true or false, indicating whether the method succeeded or not.
This example executes the same query choice twice; once via the SQL tag, and once via the refresh method:
<!--SQL select lname from customer --> <!--SCRIPT SQL.Refresh(); -->
query. ResultsToXMLString ( [resultSetTag, rowTag, columnTag, useCDATA ] )
Returns the result of a query formatted as an XML string.
The parameters for this method are:
SYBRESULTSET
is
used.SYBROW
is
used.String. The result set is returned as an XML formatted string.
This example performs a query and returns the results as an XML string:
<!--SCRIPT
queryobj = connection.CreateQuery( "select product.description, product.color from product order by product.description" );
xmlString = queryobj.ResultsToXMLString( "MYRESULTSET", "MYROW", "MYCOLUMN" );
document.writeln( xmlString );
-->
The output from this example looks similar to this (line breaks have been added for display reasons, but do not normally occur):
<MYRESULTSET> <MYROW><MYCOLUMN>Cloth Visor</MYCOLUMN><MYCOLUMN>White</MYCOLUMN></MYROW> <MYROW><MYCOLUMN>Cotton Cap</MYCOLUMN><MYCOLUMN>Black</MYCOLUMN></MYROW> <MYROW><MYCOLUMN>Cotton Shorts</MYCOLUMN><MYCOLUMN>Black</MYCOLUMN></MYROW> <MYROW><MYCOLUMN>Crew Neck</MYCOLUMN><MYCOLUMN>Black</MYCOLUMN></MYROW> <MYROW><MYCOLUMN>Hooded Sweatshirt</MYCOLUMN><MYCOLUMN>Green</MYCOLUMN></MYROW> <MYROW><MYCOLUMN>Plastic Visor</MYCOLUMN><MYCOLUMN>Black</MYCOLUMN></MYROW> <MYROW><MYCOLUMN>Tank Top</MYCOLUMN><MYCOLUMN>White</MYCOLUMN></MYROW> <MYROW><MYCOLUMN>V-neck</MYCOLUMN><MYCOLUMN>Orange</MYCOLUMN></MYROW> <MYROW><MYCOLUMN>Wool cap</MYCOLUMN><MYCOLUMN>White</MYCOLUMN></MYROW> <MYROW><MYCOLUMN>Zipped Sweatshirt</MYCOLUMN><MYCOLUMN>Blue</MYCOLUMN></MYROW> </MYRESULTSET>
query.SetSql(sqlStatement )
Sets the SQL statement in the current query object to sqlStatement [string].
Boolean. This method returns true or false, indicating whether the method succeeded or not.
This example issues two queries by reusing the same query object:
<!--SCRIPT
myQuery = connection.CreateQuery ( 'select lname, fname from customer' );
while( myQuery.MoveNext() ) {
document.WriteLn( "customer: " + myQuery.GetValue(1) + "," + myQuery.GetValue(2) );
}
myQuery.SetSQL ( 'select id from product' );
myQuery.Execute();
while ( myQuery.MoveNext() ) {
document.WriteLn( "id:"+myQuery.GetValue(1) );
}
-->
| Copyright © 2001 Sybase, Inc. All rights reserved. |
| |