Qore SqlUtil Module Reference  1.3.3
 All Classes Namespaces Functions Variables Groups Pages
SQL Operator Functions

Functions

hash SqlUtil::make_op (string op, any arg)
 returns a hash with op and arg keys
 
hash SqlUtil::op_between (any l, any r)
 returns a hash for the "between" operator with the given arguments, neither of which can be NULL or NOTHING; for use in where clauses More...
 
hash SqlUtil::op_ceq (string arg)
 returns a hash for the "=" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash SqlUtil::op_cge (string arg)
 returns a hash for the ">=" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash SqlUtil::op_cgt (string arg)
 returns a hash for the ">" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash SqlUtil::op_cle (string arg)
 returns a hash for the "<=" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash SqlUtil::op_clt (string arg)
 returns a hash for the "<" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash SqlUtil::op_cne (string arg)
 returns a hash for the "!=" or "<>" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash SqlUtil::op_eq (any arg)
 returns a hash for the "=" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash SqlUtil::op_ge (any arg)
 returns a hash for the ">=" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash SqlUtil::op_gt (any arg)
 returns a hash for the ">" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash SqlUtil::op_in ()
 returns a hash for the "in" operator with all arguments passed to the function; for use in where clauses More...
 
hash SqlUtil::op_in (list args)
 returns a hash for the "in" operator with the given argument list as the first argument; for use in where clauses More...
 
hash SqlUtil::op_le (any arg)
 returns a hash for the "<=" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash SqlUtil::op_like (string str)
 returns a hash for the "like" operator with the given argument for use in where clauses More...
 
hash SqlUtil::op_lt (any arg)
 returns a hash for the "<" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash SqlUtil::op_ne (any arg)
 returns a hash for the "!=" or "<>" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash SqlUtil::op_not (hash arg)
 returns a hash for the "not" operator; for use in where clauses More...
 
hash SqlUtil::op_substr (int start, *int count, string text)
 returns a hash for the "substr" operator with the given arguments; for use in where clauses More...
 
hash SqlUtil::op_substr (int start, string text)
 returns a hash for the "substr" operator with the given arguments; for use in where clauses More...
 
hash SqlUtil::wop_or (hash h1, hash h2)
 returns a hash with a fake "_OR_" column name; the list of arguments to the function is combined such that each hash in the list generates SQL expressions combined with "and" logic, and each of those clauses is combined with "or" logic; this is for use in where clauses More...
 

Detailed Description

These are the operators that can be used in where clauses; see Where Clauses for more information:

Function Documentation

hash SqlUtil::op_between ( any  l,
any  r 
)

returns a hash for the "between" operator with the given arguments, neither of which can be NULL or NOTHING; for use in where clauses

Example:
1 *list rows = table.selectRows(("where": ("name": op_between("US", "UX"))));
Parameters
lthe lower bound for the "between" operator
rthe upper bound for the "between" operator
Returns
a where operation description hash for use in where clauses
Exceptions
BETWEEN-ERRORone or both of the required arguments are NULL or NOTHING
hash SqlUtil::op_ceq ( string  arg)

returns a hash for the "=" operator with the given argument for use in where clauses when comparing the values of two columns

Example:
1 *list rows = table.selectRows(("where": ("name": op_ceq("other_name"))));
Parameters
argthe name of the column to compare against
Returns
a where operation description hash for use in where clauses
See Also
op_eq() for a function to be used when comparing a column against an immediate value
hash SqlUtil::op_cge ( string  arg)

returns a hash for the ">=" operator with the given argument for use in where clauses when comparing the values of two columns

Example:
1 *list rows = table.selectRows(("where": ("name": op_cge("other_name"))));
Parameters
argthe name of the column to compare against
Returns
a where operation description hash for use in where clauses
See Also
op_ge() for a function to be used when comparing a column against an immediate value
hash SqlUtil::op_cgt ( string  arg)

returns a hash for the ">" operator with the given argument for use in where clauses when comparing the values of two columns

Example:
1 *list rows = table.selectRows(("where": ("name": op_cgt("other_name"))));
Parameters
argthe name of the column to compare against
Returns
a where operation description hash for use in where clauses
See Also
op_gt() for a function to be used when comparing a column against an immediate value
hash SqlUtil::op_cle ( string  arg)

returns a hash for the "<=" operator with the given argument for use in where clauses when comparing the values of two columns

Example:
1 *list rows = table.selectRows(("where": ("name": op_cle("other_name"))));
Parameters
argthe name of the column to compare against
Returns
a where operation description hash for use in where clauses
See Also
op_le() for a function to be used when comparing a column against an immediate value
hash SqlUtil::op_clt ( string  arg)

returns a hash for the "<" operator with the given argument for use in where clauses when comparing the values of two columns

Example:
1 *list rows = table.selectRows(("where": ("name": op_clt("other_name"))));
Parameters
argthe name of the column to compare against
Returns
a where operation description hash for use in where clauses
See Also
op_lt() for a function to be used when comparing a column against an immediate value
hash SqlUtil::op_cne ( string  arg)

returns a hash for the "!=" or "<>" operator with the given argument for use in where clauses when comparing the values of two columns

Example:
1 *list rows = table.selectRows(("where": ("name": op_cne("other_name"))));
Parameters
argthe name of the column to compare against
Returns
a where operation description hash for use in where clauses
See Also
op_ne() for a function to be used when comparing a column against an immediate value
hash SqlUtil::op_eq ( any  arg)

returns a hash for the "=" operator with the given argument for use in where clauses when comparing column values to immediate values

Example:
1 *list rows = table.selectRows(("where": ("name": op_eq("Smith"))));
Parameters
argthe argument for the operator
Returns
a where operation description hash for use in where clauses
See Also
op_ceq() for a function to be used when comparing two column values

This operator also handles Qore::NULL as SQL NULL values expanding them into IS NULL clause.

hash SqlUtil::op_ge ( any  arg)

returns a hash for the ">=" operator with the given argument for use in where clauses when comparing column values to immediate values

Example:
1 *list rows = table.selectRows(("where": ("name": op_ge("Apple"))));
Parameters
argthe argument for the operator
Returns
a where operation description hash for use in where clauses
See Also
op_cge() for a function to be used when comparing two column values
hash SqlUtil::op_gt ( any  arg)

returns a hash for the ">" operator with the given argument for use in where clauses when comparing column values to immediate values

Example:
1 *list rows = table.selectRows(("where": ("name": op_gt("Apple"))));
Parameters
argthe argument for the operator
Returns
a where operation description hash for use in where clauses
See Also
op_cgt() for a function to be used when comparing two column values
hash SqlUtil::op_in ( )

returns a hash for the "in" operator with all arguments passed to the function; for use in where clauses

Example:
1 *list rows = table.selectRows(("where": ("name": op_in(200, 300, 500, 9))));
Returns
a where operation description hash for use in where clauses
Note
The argument list size may be constrained depending on the database server / driver used; passing a large number of arguments to this function may be a sign of an improper application or query design; consider using a join with a temporary table instead of passing a large number of arguments to this function
hash SqlUtil::op_in ( list  args)

returns a hash for the "in" operator with the given argument list as the first argument; for use in where clauses

Example:
1 *list rows = table.selectRows(("where": ("name": op_in(idlist))));
Parameters
argsa list of values for the "in" operator
Returns
a where operation description hash for use in where clauses
Note
The argument list size may be constrained depending on the database server / driver used; passing a large number of arguments to this function may be a sign of an improper application or query design; consider using a join with a temporary table instead of passing a large number of arguments to this function
hash SqlUtil::op_le ( any  arg)

returns a hash for the "<=" operator with the given argument for use in where clauses when comparing column values to immediate values

Example:
1 *list rows = table.selectRows(("where": ("name": op_le("Zebra"))));
Parameters
argthe argument for the operator
Returns
a where operation description hash for use in where clauses
See Also
op_cle() for a function to be used when comparing two column values
hash SqlUtil::op_like ( string  str)

returns a hash for the "like" operator with the given argument for use in where clauses

Example:
1 *list rows = table.selectRows(("where": ("name": op_like("%smith%"))));
Parameters
strthe argument for the operator
Returns
a where operation description hash for use in where clauses
hash SqlUtil::op_lt ( any  arg)

returns a hash for the "<" operator with the given argument for use in where clauses when comparing column values to immediate values

Example:
1 *list rows = table.selectRows(("where": ("name": op_lt("Zebra"))));
Parameters
argthe argument for the operator
Returns
a where operation description hash for use in where clauses
See Also
op_clt() for a function to be used when comparing two column values
hash SqlUtil::op_ne ( any  arg)

returns a hash for the "!=" or "<>" operator with the given argument for use in where clauses when comparing column values to immediate values

Example:
1 *list rows = table.selectRows(("where": ("name": op_ne("Smith"))));
Parameters
argthe argument for the operator
Returns
a where operation description hash for use in where clauses
See Also
op_cne() for a function to be used when comparing two column values

This operator also handles Qore::NULL as SQL NULL values expanding them into IS NOT NULL clause.

hash SqlUtil::op_not ( hash  arg)

returns a hash for the "not" operator; for use in where clauses

Example:
1 *list rows = table.selectRows(("where": ("name": opt_not(op_in(200, 300, 500, 9))))_;
Returns
a where operation description hash for use in where clauses
hash SqlUtil::op_substr ( int  start,
*int  count,
string  text 
)

returns a hash for the "substr" operator with the given arguments; for use in where clauses

Example:
1 *list rows = table.selectRows(("where": ("name": op_substr(1, 6, "Martin"))));
Parameters
startposition where the substring starts
countlength of the substring in characters
textwhat substring should match
Returns
a where operation description hash for use in where clauses
hash SqlUtil::op_substr ( int  start,
string  text 
)

returns a hash for the "substr" operator with the given arguments; for use in where clauses

Example:
1 *list rows = table.selectRows(("where": ("hex": op_substr(5, "BEEF"))));
Parameters
startposition where the substring starts
textwhat substring should match
Returns
a where operation description hash for use in where clauses
hash SqlUtil::wop_or ( hash  h1,
hash  h2 
)

returns a hash with a fake "_OR_" column name; the list of arguments to the function is combined such that each hash in the list generates SQL expressions combined with "and" logic, and each of those clauses is combined with "or" logic; this is for use in where clauses

Example:
1 *list rows = table.selectRows(("where": ("name": op_ceq("other_name")) + wop_or(("type": "C", "validation_flag": op_ne(NULL)), ("expiration_date": op_ge(date)))));
Generates:
select * from schema.table where name = other_name and ((type = %v and validation flag is not null) or (expiration_date >= %v))
Parameters
h1the hash of expressions to combine with "or" logic
...remaining expressions to combine with "or" logic
Returns
a hash with a fake column name ("_OR_" with a numeric prefix for uniqueness) for use in a where operation description hash for use in where clauses
Note
a random prefix is used so that multiple uses of the function can be used to generate a single where clause hash