Question
· 3 hr ago

Are class queries only for SELECT?

#ObjectScript #SQL #InterSystems IRIS

Hi developers!

There is a neat feature of ObjectScript classes - Query element, which allows you to write in a clear SQL (without any & or ()), pass parameters to it and call it from ObjectScript as do QueryNameFunc() or via Call SQLProcedureName via SQL, .e.g.

Query MyQuery(p as %String) as %SQLQuery [SQLProc]

{ 

SELECT * FROM MyTable 

WHERE Name=:p

}

All works fine, but when I tried to use the same for a DELETE statement see the following error:

SELECT expected, DELETE found ^ DECLARE Q1 CURSOR FOR DELETE

Is the Query element for SELECT only? What am I doing wrong? )

Discussion (2)1
Log in or sign up to continue
Ashok Kumar T · 2 hr ago

Hi @Evgeny Shvarov 

Class queries (%SQLQuery) are designed specifically for SELECT operations (retrieving data) rather than for data modification (INSERT, UPDATE, DELETE). This is because the class compiler translates the query definition into ObjectScript code that implements Cursor Logic, which consists of three distinct segments:

  • The Execute Logic: Prepares the SQL statement and opens a cursor to manage the result set.
  • The Fetch Logic: Performs the actual 'read' from the database globals to retrieve rows one by one.
  • The Close Logic: Cleans up memory and closes the cursor once the data is exhausted.

Because this framework and its constraints, it does not support INSERT, UPDATE, or DELETE.

0 0