Chapter 3 DynaScript Predefined Objects
Describes a connection to a database.
To use a connection's property:
connection.propertyName
To use a connection's method:
connection.MethodName( parameter )
Dynamo requires two types of connections:
When you create a Dynamo Web site, your Dynamo
connection becomes the default for your user connection as well.
This connection information is encapsulated in the <default> connection
object.
You can use Sybase Central to change the default connection associated with a script. For a detailed explanation, see "Working with connections" in the PowerDynamo User's Guide.
If your database content is stored in one or more other data sources, you can then create more connection objects to manage those sources.
There are two ways to create connection objects. To create a permanent user connection that is added to the Connections folder, use this format:
For an ODBC connection:
site.CreateConnection( connName, description, dataSource [,userName, password, ODBC, connectParameters] )
For an Open Client connection:
site.CreateConnection( connName, description, server, userName, password, Open Client [,database] )
The preferred method of creating a permanent user connection, however, is to use Sybase Central to create it from within a script. For additional information, see "Creating, deleting, modifying, and testing User connections" in the PowerDynamo User's Guide.
For a temporary connection that lasts only as long as your script is executing, use the following:
To create an ODBC connection:
X = new Connection ( name, description, datasource [,userName, password, ODBC, connectParameters] );
To create an Open Client connection:
X = new Connection ( name, description, server, userName, password, Open Client [,database] );
If a connection is not successfully created, null is returned. Use site.GetState, site.GetErrorInfo, and site.GetErrorCode to retrieve information about the state of the connection.
Open Client connections are automatically set to CHAINED = ON which is equal to autoCommit=false. Use autoCommit property to set chained mode to off (autoCommit=true).
When stored procedures are created they inherit
the mode of the connection through which they were created. Stored
procedures that are created through an Open Client connection will
have their mode set to chained by default. Once a stored procedure
has been created it is possible to change its mode, so that it may
be used by a connection regardless of mode, with the command: sp_procxmode
stored_procedure_name, "anymode"
Substitute
your procedure name for the "stored_procedure_name".
If there is no connection associated with a
script (the connection shown in the document properties is "no
connection"), then there will not be a connection
object.
The site object allows you to manipulate your Web site connections. For information on CreateConnection, DeleteConnection or GetConnection, see "site object".
This script creates a permanent ODBC connection called myConn. Once created, this connection is visible from the Connections folder in Sybase Central:
<!--SCRIPT /* creates a permanent ODBC connection */ site.CreateConnection ( "myConn","My new ODBC connection","Godzilla","dba","sql","ODBC" ); -->
connection.autoCommit
If true, each SQL statement executed using the connection is automatically committed immediately after it is executed.
Open Client connections are automatically set to CHAINED = ON which is equal to autoCommit=false. Use autoCommit property to set chained mode to off (autoCommit=true).
This example adds $1000 to account 999999999. The insert is executed and committed immediately:
<!--SCRIPT connection.autoCommit = true; query = connection.CreateQuery( "insert into savings values ( '999999999', '1000' )" ); -->
connection.connected
This property is read-only.
Uses a Boolean value to indicate whether a connection has been made to the database.
This example displays the return. True indicates a successful connection, false indicates no connection has been made.
<!--SCRIPT document.writeln( connection.connected ); connection.Connect(); document.writeln( connection.connected ); connection.Disconnect(); document.writeln( connection.connected ); -->
This example has an output of:
false true false
connection.connectionType
This property is read-only.
The type of connection (string). One of:
This example displays the connection type for a script's default connection object:
<!--SCRIPT // This script displays the type of connection // being used. document.Write( "This is an " ); document.Write( connection.connectionType ); document.WriteLn( " connection." ); -->
connection.connectParameters
Connection parameters for an ODBC data source (string).
This example lists the connection parameters for the ODBC data source:
<!--SCRIPT document.WriteLn( connection.connectParameters ); -->
This example has the following output:
autostop = no
connection.database
The name of the database to which an Open Client connection (string) is made.
This example changes the database to which "myConnection" will connect.
<!--SCRIPT myConn = site.GetConnection ( "myConnection" ); myConn.database = "newDatabaseName"; -->
connection.databaseType
This property is read-only.
The type of database to which you are connecting; for example, Adaptive Server Enterprise.
This example displays the type of database that is currently connected to:
<!--SCRIPT document.WriteLn( connection.databaseType ); -->
connection.dataSource
Name of an ODBC data source (string).
This example changes the data source that "MyConnection" uses to connect to "PowerDynamo Demo":
<!--SCRIPT myConn = site.GetConnection ( "MyConnection" ); myConn.datasource = "PowerDynamo Demo"; -->
connection.dataSourceList
This property is read-only.
Names of all available ODBC data sources (array of strings).
This example lists all the available ODBC data sources:
<!--SCRIPT
list = connection.dataSourceList;
i = 0;
while( exists( list[i] ) ) {
document.writeln( list[i] );
i++;
}
-->
This example might have the following output:
SQL Anywhere Sample PowerDynamo Demo
connection.description
Description associated with the connection (string).
This example displays the description associated with the connection:
<!--SCRIPT document.WriteLn( connection.description ); -->
If the connection includes a description, this example might have output similar to:
Connection to the sample site.
connection.isolationLevel
The isolation level for the database that you are connected to. You must be connected to set the isolation level. You can set this value with an integer or a string but it is always retrieved as a string. The possible values are:
If you set the isolation level to a value that the database does not recognize, the old isolation value remains.
This example displays the current isolation level and then resets the isolation level to 3:
<!--SCRIPT
myConn=site.GetConnection("MyConnection")
myConn.Connect();
document.WriteLn( "The isolation level = " + myConn.isolationLevel )
myConn.isolationLevel = 3;
document.WriteLn( "The isolation level = " + myConn.isolationLevel )
-->
connection.name
Name of the connection object (string). The
name of the default connection is <default>
.
This example displays the name of the connection object. The Connections folder in Sybase Central lists all connection object names.
<!--SCRIPT /* display the name of a connection object */ document.WriteLn( connection.name ); -->
This example has an output of:
<default>
connection.password
User password for the data source (string).
This example changes the password for "MyConnection" to "secret":
<!--SCRIPT myConn = site.GetConnection ( "MyConnection" ); myconn.password = "secret"; -->
connection.server
Server name for the Open Client connection (string). This property can be used only when you are using an Open Client connection.
This example displays the server name to which the connection object is connected:
<!--SCRIPT /* display the server name */ document.WriteLn( connection.server ); -->
This example has an output of:
myserver
connection.simulateCursors
Allows support for movement through a query result set for individual connections. This property is useful when working with Adaptive Server Enterprise connections.
[Adaptive Server Enterprise]
The cursor support of 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
an Adaptive Server Enterprise connection you simulate the cursor
abilities of Adaptive Server Anywhere using connection.simulateCursors.
To simulate cursors for all connections indefinitely,
modify your Registry as follows: 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."
Using simulated cursors may cause a decline
in performance. When possible, use MoveNext
instead.
This example allows MovePrevious
to
be used with an Adaptive Server Enterprise connection:
<!--SQL
select lname from customer
-->
<!--script
connection.simulateCursors = true;
SQL.MoveLast()
while( SQL.MovePrevious() ) {
document.writeln( SQL.GetValue(1) );
}
-->
connection.userId
User name for the ODBC data source (string).
This example gets the connection "Temp" and changes the password and user ID.
<!--SCRIPT // change the password and userId of the "Temp" // connection object myConn=site.GetConnection ( "Temp" ); myConn.password = "Top"; myConn.userId = "Secret"; -->
connection.Commit ( )
Commits the transaction.
Boolean
This example moves 100 dollars from a checking account to a savings account. If the money is moved successfully, a commit is performed:
<!--SCRIPT
success = false;
query = connection.CreateQuery( "select amount from savings where id = '99999999'" );
if( query != null ) {
balance = query.GetValue(1) - 100;
query.Close();
query = connection.CreateQuery( "update savings set amount = '" + balance + "' where id = '99999999'" );
if( query != null ) {
query.Close();
query = connection.CreateQuery( "select amount from chequing where id = '99999999'" );
if( query != null ) {
balance = query.GetValue(1) + 100;
query.Close();
query = connection.CreateQuery( "update chequing set amount = '" + balance + "' where id = '99999999'" );
if( query != null ) {
query.Close();
success = true;
}
}
}
}
if( success ) {
connection.Commit();
} else {
connection.Rollback();
}
-->
connection.Connect ( )
Connects to a connection object.
Boolean. Indicates whether the connection was successful or not.
This example connects to the myConn connection:
<!--SCRIPT myConn = site.CreateConnection( "myConn","My new ODBC connection", "Dynamo demo","dba","sql","ODBC" ); myConn.Connect(); -->
connection.CreateComponent (packageName, componentName )
This method is for calling EAServer components. connection.CreateComponent
returns
a component object representing the object referenced within the
package.
The component object, or null
This example calls an EAServer component and uses its setMessage method:
<!--SCRIPT myJagComponent = myJaguarconnection.CreateComponent( "Demo_Components", "SharedMsg" ); myQueryObject = myJagcomponent.setMessage( message ); -->
connection.CreateQuery ( [ SQLStatement, [doExecute ]] )
Executes a query containing a sqlStatement
and returns a query object (equivalent to performing myQuery.SetSQL
and myQuery.Execute
).
You can use CreateQuery method in these ways:
connection.CreateQuery()
creates
an "empty" query object. You can then call the SetSQL
and Execute
methods
at a later time.
connection.CreateQuery("select * from
product")
creates and executes a query.
connection.CreateQuery("select * from
product", false)
creates a query object and
sets the SQL statement, but does not execute it. You can then call
the Execute
method at a
later time.
A query object
This example queries the sample database (using the connection "sample") for a list of all the employees and then displays the list in alphabetical order:
<!--SCRIPT
// Create a query to display first and last name
// of employees in alphabetical order
myConn=site.GetConnection( "sample" );
if ( myConn != null ) {
myQuery = myConn.CreateQuery( "select emp_lname, emp_fname from employee order by emp_lname, emp_fname" );
if ( myQuery.GetErrorCode != 0 )
while( myQuery.MoveNext() ) {
lastName = myQuery.GetValue(1);
firstName = myQuery.GetValue(2);
document.WriteLn( lastName + ", " + firstName );
}
}
-->
This example has the following output:
Ahmed, Alex Barker, Joseph Barletta, Irene Bertrand, Jeannette Bigelow, Janet Blaikie, Barbara Braun, Jane Breault, Robert Bucceri, Matthew Butterfield, Joyce Chao, Shih Lin Charlton, Doug Chin, Philip Clark, Alison Cobb, Matthew Coe, Kristen Evans, Scott ...
Disconnects a connection object from a database.
connection.Disconnect ( )
This example disconnects from a connection called myConn:
<!--SCRIPT myConn = site.CreateConnection( "myConn","My new ODBC connection", "Dynamo demo","dba","sql","ODBC" ); myConn.Disconnect(); -->
connection.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 0 if the query executes without error:
<HTML> <TITLE>sample.stm</TITLE> <BODY> <H1></H1> <!--SQL SELECT customer.fname, customer.lname, customer.phone, customer.id FROM DBA.customer customer --> <!--SCRIPT queryError = connection.GetErrorCode(); document.WriteLn( queryError ); --> </BODY> </HTML>
connection.GetErrorInfo( )
A description of the error.
String
This example displays information explaining why the query did not work correctly:
<HTML> <TITLE>sample.stm</TITLE> <BODY> <H1></H1> <!--SQL SELECT customer.fname, customer.lname, customer.phone, customer.id FROM DBA.customer customeer --> <!--SCRIPT queryState = connection.GetState(); document.WriteLn( queryState ); queryInfo = connection.GetErrorInfo(); document.WriteLn( queryInfo ); --> </BODY> </HTML>
connection.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 connection:
<HTML> <TITLE>sample.stm</TITLE> <BODY> <H1></H1> <!--SQL SELECT customer.fname, customer.lname, customer.phone, customer.id FROM DBA.customer customeer --> <!--SCRIPT queryState = connection.GetState(); document.WriteLn( queryState ); queryInfo = connection.GetErrorInfo(); document.WriteLn( queryInfo ); --> </BODY> </HTML>
connection.GetSupportedMoves ( )
Provides a list of Move
methods
that can be called for queries using that particular connection.
This example displays the available Move methods
for the connection
connection:
<!--SCRIPT
moves = connection.GetSupportedMoves();
i = 0;
while( exists( moves[i] ) ) {
document.writeln( moves[i] );
i++;
}
-->
If this was a connection to Adaptive Server Anywhere, the output would be:
MoveFirst MoveNext MovePrevious MoveRelative Move MoveLast
connection.Rollback ( )
Performs a rollback on the transaction.
Boolean
This example moves 100 dollars from a checking account to a savings account. If the money is not moved successfully, a rollback is done:
<!--SCRIPT
success = false;
query = connection.CreateQuery( "select amount from savings where id = '99999999'" );
if( query != null ) {
balance = query.GetValue(1) - 100;
query.Close();
query = connection.CreateQuery( "update savings set amount = '" + balance + "' where id = '99999999'" );
if( query != null ) {
query.Close();
query = connection.CreateQuery( "select amount from chequing where id = '99999999' );
if( query != null ) {
balance = query.GetValue(1) + 100;
query.Close();
query = connection.CreateQuery( "update chequing set amount = '" + balance + "' where id = '99999999'" );
if( query != null ) {
query.Close();
success = true;
}
}
}
if( success ) {
connection.Commit();
} else {
connection.Rollback();
}
-->
| Copyright © 2001 Sybase, Inc. All rights reserved. |
| |