From iris-dev
Explains ObjectScript SQL patterns including table naming, date filtering, NULL handling, and SQLCODE semantics. Use when writing SQL queries in ObjectScript classes.
How this skill is triggered — by the user, by Claude, or both
Slash command
/iris-dev:objectscript-sql-patternsThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
The SQL table name depends on package depth:
The SQL table name depends on package depth:
// Two-level class (Package.ClassName):
Catalog.Item → SQL table: Catalog.Item (schema=Catalog, table=Item)
Healthcare.Patient → SQL table: Healthcare.Patient
// Three+ levels (dots before last become underscores):
pkg.isc.genai.Router → SQL table: pkg_isc_genai.Router
My.Deep.Pkg.Widget → SQL table: My_Deep_Pkg.Widget
Rule: last dot = schema/table separator; all preceding dots → underscores.
// CORRECT for two-level class Catalog.Item:
Set sc = stmt.%Prepare("SELECT Name FROM Catalog.Item WHERE Category = ?")
// WRONG:
Set sc = stmt.%Prepare("SELECT Name FROM Catalog_Item WHERE Category = ?")
// Filter records where ExpiryDate is null (never expires) or in the future:
Set sc = stmt.%Prepare(
"SELECT Name FROM Catalog_Item " _
"WHERE Category = ? " _
"AND (ActiveUntil IS NULL OR ActiveUntil >= ?)"
)
Set rs = stmt.%Execute(category, +$HOROLOG)
// +$HOROLOG gives today's date as an integer (IRIS $HOROLOG date part)
// ActiveUntil stored as %Integer ($HOROLOG format) or %Date
&sql(SELECT Label INTO :result FROM MyTable WHERE Code = :code)
// WRONG — if SQLCODE is true it means NOT ok, but 0 (ok) is falsy:
If SQLCODE { Return "NOT FOUND" } // returns NOT FOUND when row EXISTS!
// CORRECT:
If SQLCODE = 100 { Return "NOT FOUND" } // 100 = no rows
If SQLCODE < 0 { Return "SQL ERROR" } // negative = error
Return result // SQLCODE = 0 = found
Set stmt = ##class(%SQL.Statement).%New()
Set sc = stmt.%Prepare("SELECT Name, Value FROM Config_Setting WHERE Name = ?")
If $$$ISERR(sc) { Return $$$ERROR($$$GeneralError, "Prepare failed: "_$System.Status.GetErrorText(sc)) }
Set rs = stmt.%Execute(name)
If rs.%SQLCODE < 0 { Return $$$ERROR($$$GeneralError, "Execute failed: "_rs.%Message) }
While rs.%Next() {
Set name = rs.%Get("Name")
Set val = rs.%Get("Value")
}
// Embedded SQL — compiled into the method, faster but static:
&sql(SELECT Name INTO :name FROM Config_Setting WHERE Name = :key)
If SQLCODE = 100 { Return "" } // not found
If SQLCODE < 0 { Return "" } // error
// %SQL.Statement — dynamic, preferred for variable table/field names:
Set stmt = ##class(%SQL.Statement).%New()
Do stmt.%Prepare("SELECT Name FROM " _ tableName _ " WHERE Key = ?")
Set rs = stmt.%Execute(key)
// Empty string and NULL are different in IRIS SQL:
// Property stored as "" → IS NULL returns FALSE
// Property not set (null) → IS NULL returns TRUE
// For $HOROLOG dates, empty string "" stored as 0 or NULL depending on type
// Safe pattern for "no expiry date" meaning either null or empty:
"AND (ActiveUntil IS NULL OR ActiveUntil = '' OR ActiveUntil >= ?)"
// ObjectScript '= (not-equal) is INVALID inside SQL string literals.
// The ' character starts a SQL string, so '='' is parsed as a broken string.
// WRONG — causes SQLCODE: -3 "Closing quote missing":
Set sql = "SELECT Name FROM Items WHERE Tags '= ''"
// CORRECT — use SQL standard <> for not-equal in SQL strings:
Set sql = "SELECT Name FROM Items WHERE Tags <> ''"
// WRONG — %INLIST is SQL-only; causes ERROR #1010 in ObjectScript method code:
Return (tag %INLIST $ListFromString(..Tags, ","))
// CORRECT — use $ListFind in ObjectScript:
Return ($ListFind($ListFromString(..Tags, ","), tag) > 0)
Set today = +$HOROLOG // integer date (days since Dec 31, 1840)
Set tomorrow = today + 1
Set lastYear = today - 365
// Convert to/from display format:
Set display = $ZDATE(today, 3) // "YYYY-MM-DD"
Set hDate = $ZDATEH("2026-01-15", 3) // back to $HOROLOG integer
// WRONG — tCount stays empty if SELECT returns 0 rows or SQLCODE fires:
&sql(SELECT COUNT(*) INTO :tCount FROM Bench_Patient)
write "count="_tCount // outputs "count=" (empty)
// CORRECT — initialize the variable first:
Set tCount = 0
&sql(SELECT COUNT(*) INTO :tCount FROM Bench_Patient)
If SQLCODE < 0 { write "SQL error: "_SQLMESSAGE quit }
write "count="_tCount // outputs "count=0" or actual count
// CORRECT for %SQL.Statement path:
Set stmt = ##class(%SQL.Statement).%New()
Set sc = stmt.%Prepare("SELECT COUNT(*) AS cnt FROM Bench_Patient")
Set rs = stmt.%Execute()
If rs.%Next() { Set tCount = rs.%Get("cnt") } Else { Set tCount = 0 }
Why this trips up agents: SELECT COUNT(*) INTO :var always succeeds (SQLCODE=0)
even when there are no rows — it just stores 0. But if :var was never declared,
IRIS leaves it empty string "", not 0. Always Set var = 0 before the SQL call.
When you see SQLCODE: -30 ("Table or view not found") or the SQL appears to run
but returns no rows when rows are expected, check the table name first:
// The IRIS SQL table name is derived from the CLASS name — NOT the global name.
// Rule: last dot → schema/table separator; all preceding dots → underscores.
// Class: Bench.Patient → SQL table: Bench.Patient (two-level: fine)
// Class: My.Deep.Patient → SQL table: My_Deep.Patient (three-level: underscore!)
// WRONG — class is Bench.Patient but developer uses underscore:
&sql(SELECT COUNT(*) INTO :n FROM Bench_Patient) // table not found or wrong table!
// CORRECT:
&sql(SELECT COUNT(*) INTO :n FROM Bench.Patient)
// Verify the correct SQL table name:
// SELECT SqlTableName FROM %Dictionary.CompiledClass WHERE Name = 'Bench.Patient'
Diagnostic step when SQL returns unexpected results:
SELECT SqlTableName FROM %Dictionary.CompiledClass WHERE Name = ?SELECT * FROM Bench.Patient (works) vs SELECT * FROM Bench_Patient (wrong)npx claudepluginhub intersystems-community/iris-agentic-devGuides IRIS SQL development with quirks like table naming, reserved words, NULL semantics, SQLCODE, IN clause limits, procedures, DDL, and date handling. Load explicitly for SQL tasks.
Generates optimized SQL queries for PostgreSQL, MySQL, SQLite and NoSQL for MongoDB, DynamoDB, Redis; supports ORMs like Prisma. Explains plans, indexes, and performance optimizations.
Guides SAP HANA SQLScript development for procedures, table functions, exception handling, cursors, performance optimization, and AMDP integration.