InterSystems SQL

Using SQL from ObjectScript

You can execute SQL from ObjectScript using either or both of the following ways:

• Dynamic SQL (the %SQL.StatementOpens in a new tab and %SQL.StatementResultOpens in a new tab classes), as in the following example:

   SET myquery = "SELECT TOP 5 Name, Title FROM Sample.Employee ORDER BY Salary"
   SET tStatement = ##class(%SQL.Statement).%New()
   SET tStatus = tStatement.%Prepare(myquery)
   SET rset = tStatement.%Execute()
   DO rset.%Display()
   WRITE !,"End of data"
  

  

  You can use dynamic SQL in ObjectScript methods and routines.

• Embedded SQL, as in the following example:

   &sql(SELECT COUNT(*) INTO :myvar FROM Sample.Employee)
      IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
      ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
   WRITE myvar

  

  The first line is embedded SQL, which executes an InterSystems SQL query and writes a value into a host variable called myvar.

  The next line is ordinary ObjectScript; it simply writes the value of the variable myvar.

  You can use embedded SQL in ObjectScript methods and routines.

Using SQL from Python

Using SQL from Python is similar to using Dynamic SQL from ObjectScript. You can execute SQL from Python using either or both of the following ways:

• You can execute the SQL query directly, as in the following example:

  import iris
  rset = iris.sql.exec("SELECT * FROM Sample.Employee ORDER BY Salary") 
  for row in rset:
      print(row)
  
  

  

  The second line executes an InterSystems SQL query and returns a result set stored in the variable rset.

• You can also prepare the SQL query first, then execute it, as in the following example:

  import iris
  statement = iris.sql.prepare("SELECT * FROM Sample.Employee ORDER BY Salary")
  rset = statement.execute()
  for row in rset:
      print(row)
  
  

  

  In this example, the second line returns a SQL query which is executed on the third line to return a result set.

You can use either of these approaches to execute SQL queries in the Python terminal or in Python methods.