Running SQL from ObjectScript

Sample Data Setup

To test the queries and methods in this guide, you may wish to create a demo table, you can do this with the following persistent class. Save and compile the class below in your InterSystems IRIS instance. For instructions on creating a demo instance of InterSystems IRIS and setting up your development environment, see: Get InterSystems IRIS Community with Docker and Setting Up Your Development Environment in VS Code.

        Class sample.Person Extends %Persistent{

  Property PersonId As %Integer;
  
  Property FirstName As %String;
 
  Property LastName As %String;
 
  Property Age As %Integer;

ClassMethod Populate(pId as %Integer, pFirstName As %String, pLastName As %String, pAge As %Integer ) as %Status
{
  // Instantiate Person
  set person = ##class(sample.Person).%New()

  // Set Properties
  set person.PersonId = pId
  set person.FirstName = pFirstName
  set person.LastName = pLastName
  set person.Age = pAge
  
  // Save to Database
  set status = person.%Save()
  // Return status
  return status
}

}
        
    

After compiling the class, you can run the following in the terminal:

        do ##class(sample.Person).Populate(1, "Peter", "Parker", 17)
do ##class(sample.Person).Populate(2, "Diana", "Prince", 25)
do ##class(sample.Person).Populate(3, "Clark", "Kent", 30)
do ##class(sample.Person).Populate(4, "Natasha", "Romanoff", 28)
do ##class(sample.Person).Populate(5, "Bruce", "Wayne", 42)
        
    

Embedded SQL

You can run SQL queries and transactions using ObjectScript in several ways. The simplest is Embedded SQL, this is only suitable for simple queries:

        Class packagename.SQLBasics {

ClassMethod BasicEmbeddedSQL()
{
    new PersonID, FirstName, LastName, Age

    &sql(
        SELECT PersonID, FirstName, LastName, Age
        INTO :PersonID, :FirstName, :LastName, :Age
        FROM sample.Person
        WHERE PersonID = 1
        )

    write "The first entry in the Person table is: ", FirstName, LastName, !
    // outputs The first entry in the Person table is: PeterParker
}
}
        
    

For more complicated queries with embedded SQL, you can define and use a cursor. However, it is recommended to use Dynamic SQL, as shown below instead. To read more about Cursor-based embedded SQL, see the documentation.

Dynamic SQL

If you need to create a query at run-time which involves a variable, you might be best using dynamic SQL. A basic example of this is shown below:

        ClassMethod BasicDynamicSQL(numOutput as %Integer, minAge as %Integer) As %Status{
  
  // Create SQL Query as string. Replace variables with ?
  set myquery = "SELECT TOP ? FirstName, LastName FROM sample.Person WHERE Age > ?"
 
  // Create %SQL.Statement object  
  set tStatement = ##class(%SQL.Statement).%New()
 
  // Prepare Query
  set qStatus = tStatement.%Prepare(myquery)
  
  // If query preparation fails, write error 
  if qStatus'=1 {
	   write "%Prepare failed:" 
	   do $System.Status.DisplayError(qStatus) 
	   quit
   }
  // Execute the query, passing in parameters for placeholder ?
  set rset = tStatement.%Execute(numOutput, minAge)
  
  do rset.%Display()
  
  write !,"End of data" 
}
        
    

It is important to use ? placeholders and the Statement.%Prepare() function for security, as this prevents injection of malicious code in the placeholders. We can use this function as follows:

        do ##class(packagename.SQLBasics).BasicDynamicSQL(3, 20)
// Outputs: 
// FirstName       LastName
// Diana   Prince
// Clark   Kent
// Natasha Romanoff
        
    

Query Components

You can use a query component within an InterSystems IRIS class to run a query:

        Class packagename.SQLQuery Extends %RegisteredObject
{
  Query ByName (pStartsWith As %String) As %SQLQuery [ sqlProc ] 
  {
      SELECT PersonID, FirstName, LastName, Age
      FROM sample.Person
      WHERE (Name %STARTSWITH :pStartsWith)
  }
}
        
    

And the following class method in our SQLBasics class:

        ClassMethod UseQueryByName(name As %String)
{
    // Create Statement object
    set statement = ##class(%SQL.Statement).%New()
    
    // Prepare Statement 
    set status = statement.%PrepareClassQuery("packagename.SQLQuery", "ByName")
    
    // Handle Errors from Prepare status
    if $$$ISERR(status){
        do $system.OBJ.DisplayError(status)
        quit
    }
    
    // Execute statement
    Set rs = statement.%Execute(name)
      
    // Iterate over rows
    While rs.%Next() {

        // Write properties
        write !, "ID: ", rs.%Get("ID")
        write !, " Name: ", rs.%Get("Name")
        write !, " Age: ", rs.%Get("Age")
        write !
    }

    Do rs.%Close()
}
        
    

This method of running a SQL query in ObjectScript can be effective for creating complex queries and having control of the execution or run-time.

You can also use the query direct as a function by adding Func to the query name. This may be preferred for simple, pre-defined queries, like querying all the values in a small table.

        ClassMethod UseQueryByNameFunction(name As %String)
  {
    set rs = ##class(packagename.SQLQuery).ByNameFunc()
    While rs.%Next(){
      write !
      write rs.%Get("ID")_"  "_rs.%Get("Name")_"  "_rs.%Get("Age")
    }
    do rs.%Close()
  }
        
    

Run an SQL File

If you have a file containing SQL commands, you can run it from ObjectScript with the following command:

DO $SYSTEM.SQL.Schema.ImportDDL("c:\InterSystems\mysqlcode.txt",,"IRIS")

Checking Status with SQLCODE

Anytime SQL is run from ObjectScript, a built-in variable called SQLCODE is set to an Integer. This is set to:

0 : successful Completion
100 : no data is retrieved (without error). Either the query retrieved no results, or the final result has already been retrieved.
< 0 : Error Code

SQLCODE can be used in error handling. To read more about SQLCODE usage, see the relevant documentation or to learn about other possible error codes and error messages, see SQL Error Messages Documentation.