Chapter 3 DynaScript Predefined Objects
The query
object
has these methods:
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 will be 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 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 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:
resultSetTag
The
tag surrounding the full result set. If no tag is specified, SYBRESULTSET
is
used.
rowTag
The
tag for defining a row. If no tag is specified, SYBROW
is used.
columnTag
The
tag for defining a column. If no tag is specified, the column's
label is used.
useCDATA
Instructs
Dynamo to encode characters using CDATA to escape special XML characters.
If use_CDATA is not specified, characters are encoded using
the ampersand method.
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 © 1999 Sybase, Inc. All rights reserved. |
| |