Comparing a column value with a literal value

You can pass a Gosu literal as a comparison value to predicate methods. PolicyCenter generates and sends SQL that treats the literal value as an SQL query parameter to the relational database. Use the DBFunction.Constant static method to specify literal values to treat as SQL literals in the SQL query that PolicyCenter submits to the relational database.

If your query builder code uses the same Gosu literal in all invocations, replace the Gosu literal with a call to the DBFunction.Constant method. Using this method to coerce a Gosu literal to an SQL literal can improve query performance. To improve query performance, you must compare the literal value to a column that is in an index, and one of the following:

  • Your query builder code always passes the same literal value.
  • Your literal value is either sparse or predominant in the set of values in the database column.
Important: Use the DBFunction.Constant method with caution. If you are unsure, consult your database administrator for guidance.

Differences between Gosu literals and database constants

Gosu literal in a query example 1

The following Gosu code uses the Gosu literal "Los Angeles" to select addresses from the application database. 

uses gw.api.database.Query

// Query for addresses by using a Gosu literal to select addresses in Los Angeles.
var query = Query.make(Address)
query.compare(Address#City, Equals, "Los Angeles")

var result = query.select()

for (address in result) {
  print("public ID " + address.PublicID + " with city " + address.City)
}

The code specifies a literal value for the predicate comparison, but the SQL query that the code generates uses the value "Los Angeles" as a prepared parameter.

SQL literal in a query example 2

In contrast, the following Gosu code uses the DBFunction.Constant method to force the generated SQL query to use the Gosu string literal "Los Angeles" as an SQL literal. 

uses gw.api.database.Query
uses gw.api.database.DBFunction

// Query for addresses by using an SQL literal to select addresses in Los Angeles.
var query = Query.make(Address) // Query for addresses.
query.compare(Address#City, Equals, DBFunction.Constant("Los Angeles"))

var result = query.select()

for (address in result) {
  print("public ID " + address.PublicID + " with city " + address.City)
}

Constant method signatures

The DBFunction.Constant method has these signatures:

Constant(value String) : DBFunction 
Constant(value java.lang.Number) : DBFunction
Constant(dataType IDataType, value Object) : DBFunction