Question
· 10 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 (4)2
Log in or sign up to continue
Ashok Kumar T · 9 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
Robert Cemper · 2 hr ago

What you describe is a Basic Class Query. Slightly advanced to embedded SQL
BUT inside a Custom Class Query 
https://docs.intersystems.com/supplychain20251/csp/docbook/DocBook.UI.Page.cls?KEY=GOBJ_queries_userqueries

You have Exec, and Fetch methods. 
They can mask whatever you like as long as you feed the formal requirements.
Define the following class methods in the same class:

  • querynameExecute — This method must perform any one-time setup.
  • querynameFetch — This method must return a row of the result set; each subsequent call returns the next row.
  • querynameClose — This method must perform any cleanup operations.

Where queryname is the name of the query.

Each of these methods accepts an argument (qHandle), which is passed by reference. You can use this argument to pass information among these methods.

So you can mask your DELETE (implemented by embedded SQL) or any other way
It's not the standard way, but nothing prevents you as long as the formalism is served.

0 0