T2.3: Uses SQL in Applications

Knowledge Review - InterSystems IRIS SQL Specialist

1. Defines InterSystems IRIS connections (JDBC, ODBC, xDBC)

Key Points

  • JDBC: Standards-compliant, level 4 pure Java driver for Java applications
  • ODBC: C-language call level interface supporting Windows and UNIX platforms
  • xDBC: Collective term for JDBC, ODBC, and other database connectivity interfaces
  • Connection URL format: jdbc:IRIS://host:port/namespace for JDBC connections
  • DSN (Data Source Name): Logical connection configuration for ODBC access

Detailed Notes

Overview

InterSystems IRIS provides multiple database connectivity options to support diverse application environments and programming languages.

JDBC (Java Database Connectivity)

Implemented as a standards-compliant, level 4 pure Java driver.

Features:

  • High performance
  • Native Unicode support
  • Thread-safety

Connection URL format: `jdbc:IRIS://host:port/namespace`

Driver loading:

  • DriverManager class
  • IRISDataSource class
  • Compatible with any tool, application, or development environment supporting JDBC

ODBC (Open Database Connectivity)

Provides a C-language call level interface that is ODBC 3.5 compliant.

Driver characteristics:

  • Native driver (not built on proprietary interface)
  • Optimal performance and portability

Platform support:

  • Windows: Uses Microsoft's driver manager
  • UNIX: Uses iODBC or unixODBC driver managers

Connection configuration:

  • Typically configured through Data Source Names (DSNs)
  • DSNs store: driver location, server address, port, namespace, optional login credentials

Common Features

Both JDBC and ODBC drivers offer:

  • High performance
  • Native Unicode support
  • Thread-safety
  • Suitable for enterprise production environments

xDBC

The term xDBC collectively refers to these database connectivity interfaces, providing flexible options for applications to access InterSystems IRIS data relationally.

Documentation References

2. Understands prepare and execute steps for dynamic SQL

Key Points

  • %SQL.Statement class: Primary class for executing dynamic SQL statements
  • %Prepare() method: Validates SQL, creates cached query, generates metadata
  • %Execute() method: Executes prepared statement with optional input parameters
  • Cached queries: Prepared statements stored for reuse across multiple executions
  • Input parameters: Use ? placeholders or :varname host variables for values

Detailed Notes

Overview

Dynamic SQL in InterSystems IRIS uses the %SQL.Statement class to execute runtime-defined SQL statements through a two-step prepare-and-execute workflow.

%Prepare() Method

Takes an SQL statement as a string or subscripted array and performs:

  • Validates the syntax
  • Checks that referenced tables exist
  • Creates a cached query for performance optimization
  • Generates metadata about the statement

Return value:

  • %Status value where success equals 1
  • Failure returns an encoded error beginning with 0

Query cache behavior:

  • Automatically searches cache for identical prepared statements
  • If found, no new cached query is created
  • Important: Never place prepare statements inside loops

%Execute() Method

Executes the prepared statement and returns a %SQL.StatementResult object.

Result properties:

  • %SQLCODE
  • %Message
  • %ROWCOUNT
  • %ROWID

For SELECT queries:

  • Generates a result set
  • Traverse using %Next(), %Display(), or %GetRow()

Input Parameters

Two methods for specifying input parameters:

  • ? placeholders: Values passed as arguments to %Execute() in sequential order
  • Host variables: Prefixed with : to reference ObjectScript variables

Benefit: Same query can be executed multiple times with different parameter values without re-preparing

Alternative Methods

  • %ExecDirect(): Combines prepare and execute in a single method call
  • $SYSTEM.SQL.Prepare() / $SYSTEM.SQL.Execute(): Procedural alternatives without object instantiation

Documentation References

3. Prevents SQL injection through parameter usage

Key Points

  • Never concatenate user input: Avoid building SQL with string concatenation
  • Use ? input parameters: Placeholder approach for literal values in queries
  • Host variables with :name: Reference ObjectScript variables safely
  • Automatic type handling: Parameters are properly escaped and typed
  • Best practice note: Always validate input variables before SQL execution

Detailed Notes

Overview

SQL injection is a critical security vulnerability that occurs when untrusted user input is directly concatenated into SQL statements, allowing attackers to manipulate query logic or access unauthorized data.

Prevention Method

InterSystems IRIS prevents SQL injection through parameterized queries using the %SQL.Statement prepare-and-execute pattern.

Key principle: Never concatenate user-supplied values directly into SQL strings

? Parameter Placeholders

  • SQL statement contains placeholders like "WHERE Age > ? AND Name = ?"
  • Actual values passed as arguments to %Execute() in sequential order
  • Database engine automatically handles:
  • Proper escaping
  • Type conversion
  • Value substitution
  • User input is treated strictly as data, never as executable code

Host Variables

  • Prefixed with : to reference ObjectScript variables
  • Substituted when statement executes
  • Note: Variables must be declared PUBLIC in ProcedureBlock methods

Parameter Verification

  • %GetImplementationDetails(): Lists all input parameters in a prepared query for verification

Parameter Limitations

  • Must take literal values or expressions that resolve to literals
  • Cannot be used for column names or aliases
  • Maximum limits: 255 for explicit parameters, 380 with variable-length arrays

Best Practice

Always confirm that input variables contain appropriate values before inserting them into SQL code, even when using parameterized queries.

Documentation References

4. Uses explicit transaction control (START TRANSACTION, COMMIT, ROLLBACK)

Key Points

  • START TRANSACTION: Initiates explicit transaction, increments $TLEVEL to 1
  • COMMIT: Commits all work, releases locks, resets $TLEVEL to 0
  • ROLLBACK: Undoes uncommitted work, releases locks, restores database state
  • %COMMITMODE: Controls automatic transaction behavior (EXPLICIT, IMPLICIT, NONE)
  • Transaction isolation: READ COMMITTED, READ UNCOMMITTED, READ VERIFIED levels

Detailed Notes

Overview

InterSystems IRIS provides explicit transaction control through SQL commands that manage data consistency and integrity across multiple database operations.

START TRANSACTION

  • Immediately initiates a transaction
  • Increments $TLEVEL transaction level counter from 0 to 1
  • Must be concluded with either COMMIT or ROLLBACK regardless of current commit mode setting

COMMIT

  • Completes the transaction by making all changes permanent
  • Releases all locks established during the transaction
  • Resets $TLEVEL to 0
  • Important: Work committed cannot be rolled back
  • Supports optional WORK keyword for SQL-92 compatibility

ROLLBACK

  • Undoes all uncommitted work performed during the transaction
  • Releases locks
  • Decrements $TLEVEL
  • Restores database to state before transaction began
  • Supports optional WORK keyword for SQL-92 compatibility

%COMMITMODE Parameter

Controls automatic transaction behavior:

  • IMPLICIT (default): Auto-commits each operation
  • EXPLICIT: Requires manual commits
  • NONE: No automatic transaction processing

ISOLATION LEVEL

Determines whether queries can see uncommitted changes from concurrent transactions:

  • READ COMMITTED: Queries only see committed data
  • READ UNCOMMITTED: Allows reading uncommitted changes for maximum performance
  • READ VERIFIED: Re-checks conditions to handle concurrent updates

Nested Transactions

  • InterSystems SQL does not support nested transactions
  • Issuing START TRANSACTION when a transaction is already in progress has no effect and returns no error
  • SAVEPOINT statements: Establish named points within transactions for partial rollback

Transaction Status

  • $TLEVEL system variable: Checks current transaction level
  • %INTRANSACTION statement: Checks transaction status

ObjectScript Compatibility

  • TSTART/TCOMMIT/TROLLBACK commands are fully compatible with SQL transaction commands
  • Exception: TSTART supports nested transactions while START TRANSACTION does not

Documentation References

Exam Preparation Summary

Critical Concepts to Master:

  1. Connection Types: Understand differences between JDBC (Java, connection URL) and ODBC (C-language, DSN-based)
  2. Dynamic SQL Workflow: Master the prepare-execute pattern with %SQL.Statement
  3. SQL Injection Prevention: Always use parameterized queries, never concatenate user input
  4. Transaction Control: Know when to use START TRANSACTION, COMMIT, and ROLLBACK
  5. Input Parameters: Understand both ? placeholders and :varname host variables

Common Exam Scenarios:

  • Identifying correct JDBC connection URL format for a given server/namespace
  • Determining when to use %Prepare() vs %ExecDirect()
  • Recognizing vulnerable SQL code that concatenates user input
  • Selecting appropriate %COMMITMODE for transaction requirements
  • Choosing correct ISOLATION LEVEL for data consistency needs

Hands-On Practice Recommendations:

  • Write Java and ODBC connection code to InterSystems IRIS
  • Practice dynamic SQL with %SQL.Statement, %Prepare(), and %Execute()
  • Test parameterized queries with multiple input parameters
  • Implement transactions with START TRANSACTION, COMMIT, ROLLBACK
  • Experiment with different isolation levels and observe behavior
  • Compare IMPLICIT, EXPLICIT, and NONE commit modes

Report an Issue