Home >	Contents >	Index >

Expanded TOC   |    Accordion TOC   |    Annotated TOC   |    Index

ICS.SQLExp

Creates a string that can be used as part of an SQL WHERE clause.

Syntax

public String 
SQLExp(String table,
       String type,
       String verb,
       String arg,
       String column
       String exp)

Parameters

table

The table for which the statement is being executed.

type

The expression type. Specify one of the following constants:

• ICS.SQLExpAND (AND operation)
• ICS.SQLExpOR (OR operation)
• ICS.SQLExpIN (IN operation)

verb

The verb for the WHERE clause (for example, "LIKE", "=", "!=", "IN", "NOT IN").

arg

Comma-separated list of values for the expression.

column

The column for which the expression is being generated.

exp (Optional)

An optional expression; for example:

    lower(color)  

Description

The SQLExp method creates a string that can be used as part of a SQL where clause. For a description of SQL where clauses, see a SQL tutorial.

SQLExp takes a comma-separated string of items and a column name and places a relational operator between each item/column name pair. A logical operator is placed between each item/column name comparison. SQLExp deals with values of different types correctly. If you provide an argument for exp, the argument

Use the optional exp argument to specify an expression to be used as part of the where clause. The following table demonstrates some sample input parameters and the affect of exp on the resulting where clause:

Input Parameters						Resulting Value of where clause
table	type	verb	arg	column	exp
Flora	AND	=	green	color	omitted	color='green'
Flora	AND	=	green	color	color	color='green'
Flora	AND	=	green	color	lower(color)	lower(color)='green'

 

Returns

String containing the generated expression.

Example

Consider a database table named Movies that contains the following rows:

id	title	director
1000001	The General	Buster Keaton
10000003	Jules et Jim	Francois Truffaut
10000004	Seven Samurai	Akira Kurosawa
10000005	General Post	Thomas Bentley

 

The following code uses SQLExp (without an exp) to create a SQL expression that will ultimately find all titles that contain the word General:

String table = "Movies";
String type = ICS.SQLExpOR;
String verb = "LIKE";
String arg  = "General";
String column = "title";
String WhereClause = ics.SQLExp(table, type, verb, 
                                arg, column);

When executed, WhereClause will contain:

title LIKE '%General%'

The following code uses the returned WhereClause as a portion of a larger SQL statement and then invokes that SQL statement:

String SQLStart = "SELECT title,director FROM Movies WHERE ";
String SQLStatement = SQLStart + WhereClause;
String from = "Movies";
String listname = null;
int limit = 5;
boolean bCache = true;
StringBuffer errstr = new StringBuffer(128);
ics.ClearErrno();  
IList MyList = ics.SQL(from, SQLStatement, listname, 
                       limit, bCache, errstr);

MyList should now contain the rows from the Movies table that matched the query; that is, the rows whose title contained General.

See Also

literal

 

		Home >	Contents >	Index >
	FatWire JAVA Reference Copyright 2005 by FatWire Software All rights reserved.