IQuery interface (deprecated)
IQuery is deprecated and is provided for backward compatibility only. New applications should use the java.sql.Statement interface from the JDBC Core API. Note that IQuery is independent of a database connection, whereas JDBC's Statement interface is derived from a connection.
The IQuery interface represents a flat query. IQuery provides methods for specifying and obtaining the criteria used to select data from a data source. The AppLogic uses IQuery member methods to specify all parts of the SQL SELECT statement, including the SELECT, FROM, GROUP BY, HAVING, ORDER BY, and WHERE clauses.
To run a flat query, the AppLogic performs the following steps:
Creates an IQuery object using createQuery( ) in the AppLogic class (deprecated).
Specifies query criteria using methods in the IQueryinterface.
Executes the query, passing the loaded IQuery object to executeQuery( ) in the IDataConn interface (deprecated).
Processes the result set using methods in the IResultSet interface (deprecated).
The AppLogic can also use IQuery methods to obtain information about query criteria when the criteria are unknown. Before executing the query on the data source, the AppLogic can evaluate and, if necessary, dynamically change the query criteria.
To create an instance of the IQuery interface, use the createQuery( ) method in the AppLogic class, as shown in the following example:.
IQuery qry;
qry = createQuery();
Package
com.kivasoft
Methods
Example 1
// Create the flat query object
IQuery qry=createQuery();
// Set up the query
qry.setTables("CTLcust");
qry.setFields("CustomerID, Customer");
qry.setWhere("Customer"+"="+String.valueOf(custId));
// Execute the query
IResultSet rs=conn.executeQuery(0, qry, null, null);
// Check for a non empty result
if((rs!=null)&&(rs.getRowNumber()>0))
return result("Sorry, this user ("+
firstName+" "+lastName+") already exists");
. . . process the result set . . .
Example 2
// Create a database connection
IDataConn conn;
if((conn = openBugDB())==null) return 0;
// Pull all the parms we need from the valIn
if((wantedUser = valIn.getValString("WantedUser"))==null)
return result("Missing information from form.");
// Create the flat query object
IQuery qry = createQuery();
// Set up query conditions
qry.setTables("CTLusers");
qry.setFields("Name, Password, UserType, AccessLevel, Email, ExtID");
query.setWhere("UserID"+"="+wantedUser.hashCode());
. . . execute the query . . .
Related Topics
createQuery( ) in the AppLogic class (deprecated)
executeQuery( ) in the IDataConn interface (deprecated)
IResultSet interface (deprecated)
getFields( )
Returns a comma-separated list of arbitrary SQL expressions or columns to be included in the result set of the query.
Syntax
public IBuffer getFields()
Usage
In a SQL SELECT statement, the first clause specifies the SELECT keyword as well as the list of columns to be retrieved in the result set.
Use getFields( ) when the requested columns in a query are unknown, such as when using a query from another source. The AppLogic can analyze this list to determine the names of the columns as well as the order in which they will appear in the result set. Before executing or re-executing the query, the AppLogic can evaluate and, if necessary, dynamically change columns and column order in the query by calling setFields( ).
Tips
Return Value
An IBuffer object that contains a comma-separated list of columns that the query defines for the result set, starting with the first column and proceeding sequentially, left to right.
Example
// Get the columns specified in the query's SELECT clause
String qryFields;
qryFields = Util.toString(qry.getFields());
Related Topics
createQuery( ) in the AppLogic class (deprecated)
getGroupBy( )
Returns the GROUP BY clause of the query.
\Syntax
public IBuffer getGroupBy()
Usage
In a SQL SELECT statement, the GROUP BY clause specifies rows to summarize into aggregate rows using column functions (such as SUM or MAX) or column names.
Use getGroupBy( ) when the GROUP BY clause of the query is unknown, such as when using a query from another source. Before executing the query, the AppLogic can evaluate and, if necessary, dynamically change the GROUP BY clause by calling setGroupBy( ).
Tips
Return Value
An IBuffer object that contains the GROUP BY clause of the query, or null for failure.
Example
// Obtain the GROUP BY clause specified in the query
String qryGroup;
qryGroup = Util.toString(qry.getGroupBy());
Related Topics
createQuery( ) in the AppLogic class (deprecated)
getHaving( )
Returns the HAVING clause of the query.
\Syntax
public IBuffer getHaving()
Usage
In a SQL SELECT statement, the HAVING clause specifies which of the aggregate rows returned by the GROUP BY clause are selected for the result set.
Use getHaving( ) when the HAVING clause of the query is unknown, such as when using a query from another source. Before executing the query, the AppLogic can evaluate and, if necessary, dynamically change the HAVING clause by calling setHaving( ).
Tips
Return Value
An IBuffer object that contains the HAVING clause of the query, or null for failure.
Example
// Obtain the HAVING clause specified in the query
String qryHaving;
qryHaving = Util.toString(qry.getHaving());
Related Topics
createQuery( ) in the AppLogic class (deprecated)
getOrderBy( )
Returns the ORDER BY clause of the query.
\Syntax
public IBuffer getOrderBy()
Usage
In a SQL SELECT statement, the ORDER BY clause specifies one or more columns by which rows in the result set are sorted, as well as whether they appear in ascending or descending ASCII order.
Use getOrderBy( ) when the ORDER BY clause of the query is unknown, such as when using a query from another source. Before executing the query, the AppLogic can evaluate and, if necessary, dynamically change the ORDER BY clause by calling setOrderBy( ).
Rule
Some database vendors have restrictions on the ordering and usage of ORDER BY clauses. Read your database vendor's documentation carefully and test queries to ensure that they return the desired results.
Tips
Return Value
An IBuffer object that contains the ORDER BY clause of the query, or null for failure.
Example
// Obtain the ORDER BY clause specified in the query
String qryOrder;
qryOrder = Util.toString(qry.getOrderBy());
Related Topics
createQuery( ) in the AppLogic class (deprecated)
getSQL( )
Returns the SQL pass-through statement associated with the query.
Syntax
public IBuffer getSQL()
\Usage
Use getSQL( ) when the query string is unknown, such as when using a query from another source. Before executing the query, the AppLogic can dynamically change the SQL statement by calling setSQL( ).
Rule
If a query is set using setSQL( ) as well as the setXXXX( ) methods, the setSQL( ) string will be executed, not the string specified by setXXXX( ).
Tips
Return Value
An IBuffer object that contains the SQL pass-through statement of the query (as a single concatenated string), or null for failure.
Example
// Obtain the SELECT statement specified in the query
String qrySelect;
qrySelect = Util.toString(qry.getSQL());
Related Topics
createQuery( ) in the AppLogic class (deprecated)
getTables( )
Returns a comma-separated list of tables in the FROM clause of the query.
\Syntax
public IBuffer getTables()
Usage
In a SQL SELECT statement, the FROM clause specifies one or more source tables, views, or table aliases to search in the query. In Netscape Application Builder, the AppLogic can obtain table names only.
Use getTables( ) when the FROM clause of the query is unknown, such as when using a query from another source. Before executing the query, the AppLogic can evaluate and, if necessary, dynamically change the FROM clause by calling setTables( ).
Tips
Return Value
An IBuffer object that contains the FROM clause of the query, or null for failure.
Example
// Obtain the FROM clause specified in the query
String qryFrom;
qryFrom = Util.toString(qry.getTables());
Related Topics
createQuery( ) in the AppLogic class (deprecated)
getWhere( )
Returns the WHERE clause of the query.
\Syntax
public IBuffer getWhere()
Usage
In a SQL SELECT statement, the WHERE clause specifies the search condition and determines which rows in the table are selected for the result set.
Use getWhere( ) when the WHERE clause of the query is unknown, such as when using a query from another source. Before executing the query, the AppLogic can evaluate and, if necessary, dynamically change the WHERE clause by calling setWhere( ).
Tips
Return Value
An IBuffer object that contains the WHERE clause of the query, or null for failure.
Example
// Obtains the WHERE clause specified in the query
String qryWhere;
qryWhere = Util.toString(qry.getWhere());
Related Topics
createQuery( ) in the AppLogic class (deprecated)
setFields( )
Specifies the list of columns and computed fields to be included in the result set of the query. Required method when writing a query.
\Syntax
public int setFields(
String szFields)
szFields.
List of field names, separated by commas, or an asterisk (*) to include all fields. Extra whitespace characters are ignored. Use the AS keyword to specify field aliases. Defaults to all fields (*).
Usage
In a SQL SELECT statement, the first clause specifies the SELECT keyword as well as the list of columns and computed fields to be retrieved in the result set. The AppLogic can specify field aliases using the AS keyword in the setFields( ) parameter list.
A computed field is the result of an expression using either of the following kinds of expressions:
Rules
Tip
For computed fields, use the AS keyword so that the AppLogic can process the column in the result set by alias name.
Return Value
GXE.SUCCESS if the method succeeds.
Example 1
// Create the flat query object
IQuery qry = createQuery();
// Set up query conditions
qry.setTables("CTLusers");
// Specify list of fields to retrieve in the result set
qry.setFields("Name, Password, UserType, AccessLevel, Email, ExtID as
ID");
query.setWhere("UserID"+"="+wantedUser.hashCode());
query.setOrderBy("Name");
. . . execute the query . . .
Example 2
// Specify result set fields, specifying alias for calculation
qry.setFields("ProdID, ProdPrice * ProdQty AS ExtPrice");
Example 3
// Parentheses specify mathematical operator precedence
qry1.setFields("(product.price * invoice.quantity) / 100 as total,
invoice.id");
qry2.setFields("invoice.id, product.price * 0.06 as tax");
Example 4
// COUNT and SUM aggregate functions and alias name for result
qry1.setFields("COUNT(invoice.id) as totalSold");
qry2.setFields("SUM(city.population) as urbanPopulation");
Related Topics
createQuery( ) in the AppLogic class (deprecated)
setGroupBy( )
Specifies the GROUP BY clause of the query, determining how rows are grouped and calculated.
\Syntax
public int setGroupBy(
String szGroupBy)
szGroupBy.
GROUP BY clause of the query, using standard SQL syntax.
Usage
In a SQL SELECT statement, the GROUP BY clause specifies rows to combine using column functions (such as SUM or MAX) or column names. Such groupings are called aggregate rows, which are single rows in a result set that combine data from a group of database rows with one or more column values in common.
Rules
Return Value
GXE.SUCCESS if the method succeeds.
Example
IQuery query=createQuery();
qry.setTables("CTLinvoice,CTLcust,CTLinvprod,CTLcatalog");
qry.setFields("FirstName, LastName, BillAddr1, BillAddr2,
BillCity, BillState, BillZip,
count(*) as Visits",
sum(Quantity) as ProductsBought,
sum(quantity*price) as AmountSpent,
quantity/count(*) as ProdsPerVisit,
sum(quantity*price/count(*) as spentPerVisit")
qry.setWhere("Customer=CustomerId and Invoice=InvoiceId and
Product=ProductId and
datePlaced >= start and datePlaced"<=end");
// Group customers by first name
qry.setGroupBy("FirstName);
qry.setOrderBy("Visits desc");
. . . run query . . .
Related Topics
createQuery( ) in the AppLogic class (deprecated)
setHaving( )
Specifies the HAVING clause of the query, determining which aggregate rows qualify for inclusion in the result set.
\Syntax
public int setHaving(
String szGroupBy)
szGroupBy.
HAVING clause of the query, using standard SQL syntax.
Usage
The HAVING clause is used in conjunction with the aggregate functions (SUM, AVG, and so on) and the GROUP BY clause. In a SQL SELECT statement, the HAVING clause specifies a condition that determines which aggregate rows are selected for the result set. The HAVING clause restricts the number of aggregate rows retrieved in the result set. If unspecified, all aggregate rows will be retrieved.
Rules
Tips
Return Value
GXE.SUCCESS if the method succeeds.
Example
// Set condition on aggregate rows to an
// average amount > 1000
qry.setHaving("AverageAmt > 1000");
Related Topics
createQuery( ) in the AppLogic class (deprecated)
setOrderBy( )
Specifies the ORDER BY clause of the query, determining how rows are sorted in the result set.
\Syntax
public int setOrderBy(
String szOrderBy)
szOrderBy.
ORDER BY clause of the query, using standard SQL syntax. Supports the ASC and DESC keywords for sorting.
Usage
In a SQL SELECT statement, the ORDER BY clause specifies one or more columns by which rows in the result set are sorted. The AppLogic can also specify whether records appear in ascending (the default) or descending ASCII order using the ASC and DESC keywords, respectively.
Rules
Return Value
GXE.SUCCESS if the method succeeds.
Example
IQuery qry=createQuery();
qry.setTables("CTLinvoice,CTLcust,CTLinvprod,CTLcatalog");
qry.setFields("FirstName, LastName, BillAddr1, BillAddr2,
BillCity, BillState, BillZip,
count(*) as Visits",
sum(Quantity) as ProductsBought,
sum(quantity*price) as AmountSpent,
quantity/count(*) as ProdsPerVisit,
sum(quantity*price/count(*) as spentPerVisit")
qry.setWhere("Customer=CustomerId and Invoice=InvoiceId and
Product=ProductId and
datePlaced >= start and datePlaced <= end");
qry.setGroupBy("FirstName");
// Sorts rows in result set by number of visits
// in descending order
qry.setOrderBy("Visits desc");
. . . run query . . .
Related Topics
createQuery( ) in the AppLogic class (deprecated)
setSQL( )
Specifies the SQL statement to be passed directly to the data source.
\Syntax
public int setSQL(
String szSQL)
szSQL.
SQL statement, using standard SQL syntax, to execute on the target data source. Specify a single, concatenated string. Do not use semicolon (;) characters or other vendor-specific statement delimiters.
Usage
The AppLogic can use setSQL( ) as an alternative to using other Netscape Application Builder methods, such as constructing queries, inserting, updating, and deleting rows, and managing transactions. The AppLogic can also use setSQL( ) to run specialized SQL statements, such Data Definition Language (DDL) commands, Data Control Language (DCL) commands, and so on.
Rules
Tip
To determine whether a column is defined as NOT NULL, use getNullsAllowed( ) in the IColumn interface (deprecated).
Return Value
GXE.SUCCESS if the method succeeds.
Example 1
// Pass SELECT statement to setSQL()
IQuery qry = createQuery();
qry.setSQL("SELECT empName, empSalary FROM employees WHERE empSalary >
10000");
IResultSet rs=conn.executeQuery(0, query, null, null);
if(rs!=null) rs.flush();
System.out.println("Writing to database");
Example 2
// Pass INSERT command to setSQL()
qry.setSQL("INSERT into employees (empSalary) values (29)");
Example 3
// Pass UPDATE command to setSQL()
qry.setSQL("UPDATE employees SET empSalary = 12000 WHERE empSalary =
10000");
Example 3
// Pass DELETE command to setSQL()
qry.setSQL("DELETE FROM employees WHERE empSalary < 10000");
Related Topics
createQuery( ) in the AppLogic class (deprecated)
Vendor documentation regarding SQL programming for the specific data source that is the target of the SQL statement.
setTables( )
Specifies the FROM clause of the query, identifying one or more tables to be queried. Required method when writing a query.
\Syntax
public int setTables(
String szTables)
szTables.
List of table names separated by commas. Whitespace characters are ignored.
Usage
In a SQL SELECT statement, the FROM clause specifies one or more source tables, views, or table aliases to search in the query. In Netscape Application Builder, the AppLogic can specify table names only.
Rules
Example 1
// Specify invoice, customer, invoice, and catalog tables
IQuery qry=createQuery();
qry.setTables("CTLinvoice,CTLcust,CTLinvprod,CTLcatalog");
qry.setFields("FirstName, LastName, BillAddr1, BillAddr2,
BillCity, BillState, BillZip,
count(*) as Visits",
sum(Quantity) as ProductsBought,
sum(quantity*price) as AmountSpent,
quantity/count(*) as ProdsPerVisit,
sum(quantity*price/count(*) as spentPerVisit")
qry.setWhere("Customer=CustomerId and Invoice=InvoiceId and
Product=ProductId and
datePlaced >= start and datePlaced <= end");
qry.setGroupBy("FirstName");
Example 2
// Specify same table, using as keyword, for different users
qry.setTables("customer, customer as cust2")
Related Topics
createQuery( ) in the AppLogic class (deprecated)
setWhere( )
Specifies the WHERE clause of the query, determining which rows qualify for inclusion in the result set.
\Syntax
public int setWhere(
String szWhere)
szWhere.
WHERE clause of the query, using standard SQL syntax.
Usage
In a SQL SELECT statement, the WHERE clause specifies the search condition and determines which rows in the table are selected for the result set. The WHERE clause restricts the number of rows retrieved in the result set. If unspecified, all rows in the source table will be retrieved.
Rules
Tip
To improve AppLogic performance, be sure to specify a HAVING or WHERE clause to avoid retrieving rows unnecessarily, especially for large tables.
Return Value
GXE.SUCCESS if the method succeeds.
Example 1
// Obtain user info from the user database
IQuery query=createQuery();
qry.setTables("CTLcust");
qry.setFields("FirstName, LastName, BillAddr1, BillAddr2,
BillCity, BillState, BillZip");
qry.setWhere("CustomerId"+"="+cusId+" and
"+ "Password"+"='"+password+"'");
IResultSet rs=conn.executeQuery(0, qry, null, null);
if((rs==null)||(rs.getRowNumber()==0))
. . . process result set . . .
Example 2
// Specify WHERE clause for two different queries
qry1.setWhere("urbanPopulation >= 1000000");
qry2.setWhere("region = 'West' or region = 'East'");
Related Topics
createQuery( ) in the AppLogic class (deprecated)
|
Previous
