Retrieving data using SELECT

When you execute a SELECT statement, the PreparedStatement.executeQuery method returns a ResultSet object. The ResultSet class contains methods for navigating within a result set and methods to update data using the ResultSet.

For more information about ResultSet objects, see ResultSet class [UltraLite for M-Business Anywhere].

Example

In the following code, the results of a query are accessed as a ResultSet. When first assigned, the ResultSet is positioned before the first row. The ResultSet.moveFirst method is then called to navigate to the first record in the result set.

var MyResultSet;
var PrepStmt;
PrepStmt = conn.prepareStatement("SELECT ID, Name FROM customer", null );
MyResultSet = PrepStmt.executeQuery( null );
MyResultSet.moveFirst();

Example

The following code demonstrates how to obtain column values for the current row. The example uses character data; similar methods are available for other data types.

The getString method uses the following syntax: MyResultSetName.getString( Index ) where Index is the ordinal position of the column name in your SELECT statement.

if ( MyResultSet.getRowCount() == 0 ) {
} else {
  alert( MyResultSet.getString(1) );
  alert( MyResultSet.getString(2) );
  MyResultSet.moveRelative(0);
}

For more information about navigating a result set, see SQL result set navigation.

Perform a select statement

The procedure uses a SELECT statement to retrieve information from the database. The results of the query are assigned to a ResultSet object.

Declare a PreparedStatement object.
var OrderStmt;

Assign a prepared statement to your PreparedStatement object.

OrderStmt = Connection.prepareStatement( 
   "SELECT order_id, disc, quant, notes, status, c.cust_id, 
   cust_name, p.prod_id, prod_name, price 
  FROM ULOrder o, ULCustomer c, ULProduct p 
  WHERE o.cust_id = c.cust_id 
  AND o.prod_id = p.prod_id 
  ORDER BY "order_id", "order_query_stmt" );

The second parameter is a persistent name that provides cross-page JavaScript object persistence.

Execute the query.

OrderResultSet = OrderStmt.executeQuery( "order_query" );

For more information about how to use queries, see the CustDB sample code in C:\Documents and Settings\All Users\Documents\SQL Anywhere 12\Samples\UltraLiteForMBusinessAnywhere\CustDB\custdb.js.

Discuss this page in DocCommentXchange.