Qore SqlUtil Module Reference  1.6
SQL Operator Functions

Functions

hash< OperatorInfoSqlUtil::make_op (string op, auto arg)
 returns an OperatorInfo hash
 
hash< OperatorInfoSqlUtil::op_between (auto l, auto r)
 returns an OperatorInfo hash for the "between" operator with the given arguments, neither of which can be NULL or NOTHING; for use in where clauses More...
 
hash< OperatorInfoSqlUtil::op_ceq (string arg)
 returns an OperatorInfo hash for the "=" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash< OperatorInfoSqlUtil::op_cge (string arg)
 returns an OperatorInfo hash for the ">=" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash< OperatorInfoSqlUtil::op_cgt (string arg)
 returns an OperatorInfo hash for the ">" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash< OperatorInfoSqlUtil::op_cle (string arg)
 returns an OperatorInfo hash for the "<=" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash< OperatorInfoSqlUtil::op_clt (string arg)
 returns an OperatorInfo hash for the "<" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash< OperatorInfoSqlUtil::op_cne (string arg)
 returns an OperatorInfo hash for the "!=" or "<>" operator with the given argument for use in where clauses when comparing the values of two columns More...
 
hash< OperatorInfoSqlUtil::op_eq (auto arg)
 returns an OperatorInfo hash for the "=" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash< OperatorInfoSqlUtil::op_ge (auto arg)
 returns an OperatorInfo hash for the ">=" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash< OperatorInfoSqlUtil::op_gt (auto arg)
 returns an OperatorInfo hash for the ">" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash< OperatorInfoSqlUtil::op_in ()
 returns an OperatorInfo hash for the "in" operator with all arguments passed to the function; for use in where clauses More...
 
hash< OperatorInfoSqlUtil::op_in (list< auto > args)
 returns an OperatorInfo hash for the "in" operator with the given argument list as the first argument; for use in where clauses More...
 
hash< OperatorInfoSqlUtil::op_in_select (string table, hash< auto > select_hash)
 returns an OperatorInfo hash for the "in" operator with a subquery as the argument; for use in where clauses More...
 
hash< OperatorInfoSqlUtil::op_in_select (AbstractTable table, hash< auto > select_hash)
 returns an OperatorInfo hash for the "in" operator with a subquery as the argument; for use in where clauses More...
 
hash< OperatorInfoSqlUtil::op_in_select (Table table, hash< auto > select_hash)
 returns an OperatorInfo hash for the "in" operator with a subquery as the argument; for use in where clauses More...
 
hash< OperatorInfoSqlUtil::op_le (auto arg)
 returns an OperatorInfo hash for the "<=" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash< OperatorInfoSqlUtil::op_like (string str)
 returns an OperatorInfo hash for the "like" operator with the given argument for use in where clauses More...
 
hash< OperatorInfoSqlUtil::op_lt (auto arg)
 returns an OperatorInfo hash for the "<" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash< OperatorInfoSqlUtil::op_ne (auto arg)
 returns an OperatorInfo hash for the "!=" or "<>" operator with the given argument for use in where clauses when comparing column values to immediate values More...
 
hash< OperatorInfoSqlUtil::op_not (hash arg)
 returns an OperatorInfo hash for the "not" operator; for use in where clauses More...
 
hash< OperatorInfoSqlUtil::op_substr (int start, *int count, string text)
 returns an OperatorInfo hash for the "substr" operator with the given arguments; for use in where clauses More...
 
hash< OperatorInfoSqlUtil::op_substr (int start, string text)
 returns an OperatorInfo hash for the "substr" operator with the given arguments; for use in where clauses More...
 
hash< string, hash< OperatorInfo > > SqlUtil::wop_or (hash h1, hash h2)
 returns an OperatorInfo 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

◆ op_between()

hash<OperatorInfo> SqlUtil::op_between ( auto  l,
auto  r 
)

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

Example:
*list<auto> 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
an OperatorInfo hash for use in where clauses
Exceptions
BETWEEN-ERRORone or both of the required arguments are NULL or NOTHING

◆ op_ceq()

hash<OperatorInfo> SqlUtil::op_ceq ( string  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_ceq("other_name"))));
Parameters
argthe name of the column to compare against
Returns
an OperatorInfo hash for use in where clauses
See also
op_eq() for a function to be used when comparing a column against an immediate value

◆ op_cge()

hash<OperatorInfo> SqlUtil::op_cge ( string  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_cge("other_name"))));
Parameters
argthe name of the column to compare against
Returns
an OperatorInfo hash for use in where clauses
See also
op_ge() for a function to be used when comparing a column against an immediate value

◆ op_cgt()

hash<OperatorInfo> SqlUtil::op_cgt ( string  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_cgt("other_name"))));
Parameters
argthe name of the column to compare against
Returns
an OperatorInfo hash for use in where clauses
See also
op_gt() for a function to be used when comparing a column against an immediate value

◆ op_cle()

hash<OperatorInfo> SqlUtil::op_cle ( string  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_cle("other_name"))));
Parameters
argthe name of the column to compare against
Returns
an OperatorInfo hash for use in where clauses
See also
op_le() for a function to be used when comparing a column against an immediate value

◆ op_clt()

hash<OperatorInfo> SqlUtil::op_clt ( string  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_clt("other_name"))));
Parameters
argthe name of the column to compare against
Returns
an OperatorInfo hash for use in where clauses
See also
op_lt() for a function to be used when comparing a column against an immediate value

◆ op_cne()

hash<OperatorInfo> SqlUtil::op_cne ( string  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_cne("other_name"))));
Parameters
argthe name of the column to compare against
Returns
an OperatorInfo hash for use in where clauses
See also
op_ne() for a function to be used when comparing a column against an immediate value

◆ op_eq()

hash<OperatorInfo> SqlUtil::op_eq ( auto  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_eq("Smith"))));
Parameters
argthe argument for the operator
Returns
an OperatorInfo 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.

◆ op_ge()

hash<OperatorInfo> SqlUtil::op_ge ( auto  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_ge("Apple"))));
Parameters
argthe argument for the operator
Returns
an OperatorInfo hash for use in where clauses
See also
op_cge() for a function to be used when comparing two column values

◆ op_gt()

hash<OperatorInfo> SqlUtil::op_gt ( auto  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_gt("Apple"))));
Parameters
argthe argument for the operator
Returns
an OperatorInfo hash for use in where clauses
See also
op_cgt() for a function to be used when comparing two column values

◆ op_in() [1/2]

hash<OperatorInfo> SqlUtil::op_in ( )

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_in(200, 300, 500, 9))));
Returns
an OperatorInfo 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

◆ op_in() [2/2]

hash<OperatorInfo> SqlUtil::op_in ( list< auto >  args)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_in(idlist))));
Parameters
argsa list of values for the "in" operator
Returns
an OperatorInfo 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

◆ op_in_select() [1/3]

hash<OperatorInfo> SqlUtil::op_in_select ( string  table,
hash< auto >  select_hash 
)

returns an OperatorInfo hash for the "in" operator with a subquery as the argument; for use in where clauses

Example:
*list<auto> rows = table.selectRows({"where": {"name": op_in_select("other_table", {"columns": "name", "where": where_hash})}});
Returns
an OperatorInfo hash for use in where clauses

◆ op_in_select() [2/3]

hash<OperatorInfo> SqlUtil::op_in_select ( AbstractTable  table,
hash< auto >  select_hash 
)

returns an OperatorInfo hash for the "in" operator with a subquery as the argument; for use in where clauses

Example:
*list<auto> rows = table.selectRows({"where": {"name": op_in_select(other_table, {"columns": "name", "where": where_hash})}});
Returns
an OperatorInfo hash for use in where clauses

◆ op_in_select() [3/3]

hash<OperatorInfo> SqlUtil::op_in_select ( Table  table,
hash< auto >  select_hash 
)

returns an OperatorInfo hash for the "in" operator with a subquery as the argument; for use in where clauses

Example:
*list<auto> rows = table.selectRows({"where": {"name": op_in_select(other_table, {"columns": "name", "where": where_hash})}});
Returns
an OperatorInfo hash for use in where clauses

◆ op_le()

hash<OperatorInfo> SqlUtil::op_le ( auto  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_le("Zebra"))));
Parameters
argthe argument for the operator
Returns
an OperatorInfo hash for use in where clauses
See also
op_cle() for a function to be used when comparing two column values

◆ op_like()

hash<OperatorInfo> SqlUtil::op_like ( string  str)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_like("%smith%"))));
Parameters
strthe argument for the operator
Returns
an OperatorInfo hash for use in where clauses

◆ op_lt()

hash<OperatorInfo> SqlUtil::op_lt ( auto  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_lt("Zebra"))));
Parameters
argthe argument for the operator
Returns
an OperatorInfo hash for use in where clauses
See also
op_clt() for a function to be used when comparing two column values

◆ op_ne()

hash<OperatorInfo> SqlUtil::op_ne ( auto  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": op_ne("Smith"))));
Parameters
argthe argument for the operator
Returns
an OperatorInfo 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.

◆ op_not()

hash<OperatorInfo> SqlUtil::op_not ( hash  arg)

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

Example:
*list<auto> rows = table.selectRows(("where": ("name": opt_not(op_in(200, 300, 500, 9))))_;
Returns
an OperatorInfo hash for use in where clauses

◆ op_substr() [1/2]

hash<OperatorInfo> SqlUtil::op_substr ( int  start,
*int  count,
string  text 
)

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

Example:
*list<auto> 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
an OperatorInfo hash for use in where clauses

◆ op_substr() [2/2]

hash<OperatorInfo> SqlUtil::op_substr ( int  start,
string  text 
)

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

Example:
*list<auto> rows = table.selectRows(("where": ("hex": op_substr(5, "BEEF"))));
Parameters
startposition where the substring starts
textwhat substring should match
Returns
an OperatorInfo hash for use in where clauses

◆ wop_or()

hash<string, hash<OperatorInfo> > SqlUtil::wop_or ( hash  h1,
hash  h2 
)

returns an OperatorInfo 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:
*list<auto> 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 first hash of expressions to combine with "or" logic
h2the second hash of expressions to combine with "or" logic
...remaining expressions to combine with "or" logic
Returns
a hash with an OperatorInfo hash value keyed by 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