Data retrieval: 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.

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 Navigation with SQL.

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

To perform a select statement

  1. Declare a PreparedStatement object.

    var OrderStmt;

  2. 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 der_id", "order_query_stmt" );

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

  3. Execute the query. 

    OrderResultSet = OrderStmt.executeQuery( "order_query" );

For more information about how to use queries, see the CustDB sample code in samples-dir\UltraLiteForMBusinessAnywhere\CustDB\custdb.js.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0