docs.intersystems.com
Home  /  Application Development: Core Topics  /  Using InterSystems SQL  /  Introduction to InterSystems SQL


Using InterSystems SQL
Introduction to InterSystems SQL
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


InterSystems SQL provides uncompromising, standard relational access to data stored within an InterSystems IRIS Data Platform™ database.
InterSystems SQL offers the following benefits:
You can use InterSystems SQL for many purposes including:
Architecture
The core of InterSystems SQL consists of the following components:
Features
InterSystems SQL includes a full set of standard, relational features. These include:
Note:
We continue to add support for additional features within InterSystems SQL. If you require a feature that is not supported within this release, please feel free to check with the InterSystems Worldwide Response Center (WRC) to see if it will be included in a newer release.
SQL-92 Compliance
The SQL-92 standard is imprecise with regard to arithmetical operator precedence; assumptions on this matter differ amongst SQL implementations. InterSystems SQL supports two system-wide alternatives for SQL arithmetic operator precedence:
InterSystems SQL supports the complete entry-level SQL-92 standard with the following exceptions:
Extensions
InterSystems SQL supports a number of useful extensions. Many of these are related to the fact that InterSystems IRIS provides simultaneous object and relational access to data.
Some of these extensions include:
Interoperability
InterSystems SQL supports a number of ways to interoperate relationally with other applications and software tools.
JDBC
InterSystems IRIS includes a standards-compliant, level 4 (all pure Java code) JDBC client.
The InterSystems JDBC driver offers the following features:
You can use InterSystems JDBC with any tool, application, or development environment that supports JDBC. If you encounter problems or have questions about compatibility, contact the InterSystems InterSystems Worldwide Response Center (WRC).
ODBC
The C-language call level interface for InterSystems SQL is ODBC. Unlike other database products, the InterSystems ODBC driver is a native driver — it is not built on top of any other proprietary interface.
The InterSystems ODBC driver offers the following features:
You can use InterSystems ODBC with any tool, application, or development environment that supports ODBC. If you encounter problems or have questions about compatibility, contact the InterSystems InterSystems Worldwide Response Center (WRC).
Embedded SQL
Within ObjectScript, InterSystems SQL supports Embedded SQL: the ability to place an SQL statement within the body of a method (or other code). Using Embedded SLQ, you can query a single record, or define a cursor and use that to query multiple records. Embedded SQL is compiled; it is either compiled at the same time as the ObjectScript routine (the default), or you can defer Embedded SQL compilation until runtime.
Embedded SQL is quite powerful when used in conjunction with the object access capability of InterSystems IRIS. For example, the following method finds the Object ID of the Product with a given SKU code and uses it to create an in-memory object instance:
ClassMethod FindBySKU(sku As %String)
{
    &sql(SELECT %ID INTO :id FROM Product WHERE SKU = :sku)
        
    If (SQLCODE = 0) {
        // ask the product to display details about itself
        Set product = ##class(Product).%OpenId(id)
        Do product.DisplayDetails()
    }
}
For more details, see the chapter Using Embedded SQL.”
Dynamic SQL
As part of its standard library, InterSystems IRIS provides an %SQL.Statement class that you can use to execute dynamic (that is, defined at runtime) SQL statements. You can use Dynamic SQL within ObjectScript methods. For example, the following method queries for a specified number of people born in the 21st century. The query selects all people born after December 31, 1999, orders the selected records by date of birth, then selects the top x records:
ClassMethod Born21stC(x) [ language = cache ]
{
  SET myquery=2
  SET myquery(1) = "SELECT TOP ? Name,%EXTERNAL(DOB) FROM Sample.Person "
  SET myquery(2) = "WHERE DOB > 58073 ORDER BY DOB"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(.myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute(x)
  DO rset.%Display()
  WRITE !,"End of data"
 }
When you prepare a query, an optimized version of that query is stored as a cached query. This cached query is executed for subsequent invocations of the query, avoiding the overhead of re-optimizing a query each time it is executed.
For more details, see the chapter Using Dynamic SQL.”
Limitations
Note the following limitations of InterSystems SQL: