The SQL created through RQL may in some cases not be optimized for your needs. Should such a situation arise, you can use the atg.adapter.gsa.query.SqlPassthroughQuery class. A SqlPassthroughQuery is used with the QueryBuilder to specify the SQL statement to pass directly to the database. Note that the SQL repository cannot use the results of arbitrary queries to generate repository items. The query must return the ID column or columns in their declared order from the item descriptor’s id-column-names attribute.

Here is an example of how a SqlPassthroughQuery might be used in a page. Note that the SQL statement is sent to the database “as is.”

import atg.adapter.gsa.query.*;

GSARepository repo =
   (GSARepository)request.resolveName("/examples/TestRepository");
RepositoryView view = repo.getView("canard");
Object params[] = new Object[4];
  params[0] = new Integer (25);
  params[1] = new Integer (75);
  params[2] = "french";
  params[3] = "greek";
Builder builder = (Builder)view.getQueryBuilder();
String str = "SELECT * FROM usr_tbl WHERE (age_col > 0 AND age_col < 1
AND EXISTS (SELECT * from subjects_tbl where id = usr_tbl.id AND subject
IN (2, 3)))";

RepositoryItem[] items =
    view.executeQuery (builder.createSqlPassthroughQuery(str, params));

if (items == null)
   out.println(" Is null.");
else{
   out.println(" Is not null: " + items.length + "<p>");
   for (int i = 0; i < items.length; i++){
     out.println(items[i].toString() + "<br>");
     }
 }

Copyright © 1997, 2013 Oracle and/or its affiliates. All rights reserved. Legal Notices