Viewing the SQL select statement for a query

The query builder APIs provide two ways to preview and record SQL SELECT statements that your Gosu code and the query builder APIs submit to the application database:

toString
Provides an approximation of the SQL Select statement before it is submitted
withLogSQL
Displays and records the exact SQL Select statement at the time it is submitted

Using toString to preview SQL SELECT statements

You may want to see what the underlying SQL SELECT statement looks like as you build up your query. Use the Gosu toString method to return an approximation of the SQL SELECT statement at given points in your Gosu code. That way you can learn how different query builder methods affect the underlying SQL SELECT statement.

For example, the following Gosu code prints the SQL Select statement as it exists after creating a query.

uses gw.api.database.Query 

var query = Query.make(Contact)
print(query.toString())

The output looks like the following:

[]
SELECT /* pc:T:Gosu class redefiner; */ FROM pc_contact gRoot WHERE gRoot.Retired = 0

The first line shows square brackets ([]) containing the list of variables to bind to the query. In this case, there are no variables. The remaining lines show the SQL statement. Note that the table for the entity type, pc_contact, has the table alias gRoot.

After you join a related entity to a query or apply a predicate, use the toString method to see what the query builder APIs added to the underlying SQL statement.

Note: The toString method returns only an approximation of the SQL statement that PolicyCenter submits to the application database. The actual SQL statement might differ due to database optimizations that PolicyCenter applies internally.

Using withLogSQL to record SQL SELECT statements

You might want to see the underlying SQL SELECT statement that the query builder API submits to the application database. Use the withLogSQL method on a query to see the statement. When you turn on logging behavior, the query builder API writes the SQL SELECT statement to the system logs, in logging category Server.Database. The API also writes the SQL statement to standard output (stdout).

For example, the following Gosu code turns logging on by calling the withLogSQL method of the query object. The SQL statement is written to the system logs at the time code starts to iterate the results. 

uses gw.api.database.Query 

var query = Query.make(Person).withLogSQL(true)
query.startsWith(Person#LastName, "A", false)
query.withLogSQL(true)          // -- turn on logging behavior here --
var result = query.select().orderBy(QuerySelectColumns.path(Paths.make(Person#LastName)))
          .thenBy(QuerySelectColumns.path(Paths.make(Person#FirstName)))

var i = result.iterator()       // -- write the SQL statement to the system logs here --
while (i.hasNext()) {
  var person = i.next()
  print (person.LastName + ", " + person.FirstName + ": " + person.EmailAddress1)
}

The SQL Select statement for the preceding example looks like the following on standard output. 

Executing sql = SELECT /* KeyTable:pc_contact; */ 
        gRoot.ID col0, gRoot.Subtype col1, gRoot.LastName col2, gRoot.FirstName col3 
        FROM pc_contact gRoot 
        WHERE gRoot.Subtype IN (?,?,?,?) AND gRoot.LastName LIKE ? AND gRoot.Retired = 0 
        ORDER BY col2 ASC, col3 ASC 
        [2 (typekey), 4 (typekey), 7 (typekey), 9 (typekey), A% (lastname)]

The statement on your system depends on your relational database and might differ from the preceding example.

Note: Writing to the system logs and to standard output does not occur when Gosu code calls the withLogSQL method. That logging occurs some time later, when PolicyCenter submits the query to the relational database.