How do you iterate through SQL in Cache ObjectScript; and across namespaces?

I'm  not too sure about accessing different namespaces via SQL but you can use a variety of %ResultSet objects but personally I prefer using an SQL cursor with SQL open, fetch to iterate through the object.

My 'personal' reasons for preferring SQL cursors is that they are extremely fast to develop but more importantly if you add/remove indices in the target class then the class that uses the cursor will use the best available indices without requiring re-compilation unlike at least some of the %ResultSet methods.

jim

I would recommend using $NAMESPACE. You could create a simple function that can be called within your ObjectScript that sends the namespace you which to switch to as a parameter. Once the function completes and leaves the execution stack the code will be set back to the calling namespace.

GetNSMPData(P1) PUBLIC
{
new $NAMESPACE
set $NAMESPACE = P1
;
// You method for querying your data here
;
quit rValue
}

Best of Luck,
Jason

Also, using classqueries if possible, can significantly impact your execution time and maintenance  overhead.

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

All the Best!

Bhaskar

My personal preference for SQL is to make use of %SQL.Statement to manage my actual SQL queries, as it allows for *very* flexible query definitions. (You can also use the older %Library.ResultSet classes, but I strongly prefer using the newer code in %SQL.Statement.)

I also like to wrap the underlying query in an API that abstracts out some of my search options, but still allows me to control exactly what is queried.

Here is a sample of a wrapped  query of this kind:

ClassMethod PersonSearch(
  Output pIDList As %Library.ListOfDataTypes,
  Output pHasMore As %Boolean,
  pHomeState As %String = "",
  pOfficeState As %String = "",
  pHasSpouse As %Boolean = "",
  pMaxRows As %Integer = 50) As %Status
{
  Set tStatus = $$$OK
  Try {
    Set pIDList = ##class(%Library.ListOfDataTypes).%New()
    Set pHasMore = 0
    // The selected columns and other clauses (e.g. ORDER BY) can also be affected by parameters
    Set tSQL = "SELECT ID FROM Sample.Person"
    Set tArguments = 0
    Set tWHERE = ""
    If (pHomeState '= "") {
      Set tWHERE = tWHERE _ $listbuild("Home_State = ?")
      Set tArguments = tArguments + 1
      Set tArguments(tArguments) = pHomeState
    }
    If (pOfficeState '= "") {
      Set tWHERE = tWHERE _ $listbuild("Office_State = ?")
      Set tArguments = tArguments + 1
      Set tArguments(tArguments) = pOfficeState
    }
    If (pHasSpouse '= "") {
      Set tWHERE = tWHERE _ $listbuild("Spouse IS " _ $select(''pHasSpouse: "NOT ", 1: "") _ "NULL")
    }
    If (tWHERE '= "") {
       Set tSQL = tSQL _ " WHERE " _ $listtostring(tWHERE," AND ")
    }
    Set tStatement = ##class(%SQL.Statement).%New()
    Set tStatus = tStatement.%Prepare(tSQL)
    If $$$ISERR(tStatus) {
      Quit
    }
    Set tRowCount = 0
    // Use tArguments... to allow the array to be pushed out into individual parameters
    Set tRS = tStatement.%Execute(tArguments...)
    While tRS.%Next() {
      If (pMaxRows) {
        If (tRowCount = pMaxRows)) {
          Set pHasMore = 1
          Quit
        }
        Else {
          Set tRowCount = tRowCount + 1
        }
      }
      // You can reference select columns by name, but can also use variants of Get to retrieve them -- I find the name to be easy to understand
      Do pIDList.Insert(tRS.ID)
    }
    // Check for SQL errors - 0 is all OK, 100 is no more rows
    If (tRS.%SQLCODE && (tRS.%SQLCODE '= 100)) {
      Set tStatus = $$$ERROR($$$SQLCode,tRS.%SQLCODE,tRS.%Message)
      Quit
    }
  }
   Catch ex {
    Set tStatus = ex.AsStatus()
  }
  Quit tStatus

2. EnsLogViewer is a project with class query, which iterates over namespaces. It is an Ensemble Log Viewer with namespace support. Works on Ensemble 2013.1 and later.

lots of options!

If you are going to change namespaces make sure you move to the new namespace, create and use your Object and then move back.  Cache does not support using an object opened in one namespace in a different namespace.

How are you doing this now in COS?  If you are using extended global syntax you could still do that in the class definition.   If you are using Global Mapping in the Namespace you could include Package Mapping to expose the classes as well.

If you have data in 2 databases that you want to expose in one class you can create a class using Cache SQL Storage and "map" the two globals into one class def.  I can provide an example of this if needed.

As for add SQL to your application, using Embedded SQL with cursors is the fastest way to go.

If you want to use a resultset, ISC encourages you to use %SQL.Statement.  The sample code above is a great example for %SQL.Statement, it is a dynamic query that is customized based on user import. 

If the query never changes use Embedded SQL.  If you feel the need to stick with a resultset then put the SQL in a class query and execute that.  Why waste the time preparing an SQL statement over and over again if it never changes?