Are class queries only for SELECT?
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? )
Comments
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.
Thanks for the clarification, @Ashok Kumar T !
What can I say - it's not obvious (that it is only for SELECT), and it's a pity as the "Query" functionality that works great for SELECT can also be leveraged with INSERT, DELETE and UPDATE.
Sounds like a good candidate for the Ideas portal :)
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.
Thanks @Robert Cemper !
I this case Query was a slightly better alternative to me vs Embedded SQL (more readable SQL). So…
But knowing once again that everything is possible is always good, especially a few days before New Year! :)