Call Stored Procedure via a SELECT instead of a CALL

Question

Question

Abbad Minhas · Nov 28, 2019

I have the following class:

Class temp.test7 [ Owner = {PHOENIXDBUSER} ]
{
ClassMethod GetTenAssets() As %Status [ ReturnResultsets, SqlProc ]
{
s sc=$$$OK s sql="SELECT TOP 10 * FROM Data_Assets.Asset" 
s sqlStatement=##class(%SQL.Statement).%New()
s sc=sqlStatement.%Prepare(sql) 
if $$$ISOK(sc) {
s result=sqlStatement.%Execute()
d %sqlcontext.AddResultSet(result)
s %sqlcontext.%SQLCODE=result.%SQLCODE
s %sqlcontext.%ROWCOUNT=result.%ROWCOUNT
s %sqlcontext.%Message=result.%Message
} 
else {
s %sqlcontext.%SQLCODE=-460,%sqlcontext.%Message=##class(%SYSTEM.Status).GetErrorText(sc)
}
q sc
}
}

I am able to successfully call this stored procedure using:

CALL temp.test7_GetTenAssets()

however I would ideally want to be able to call this stored procedure using a SELECT statement:

SELECT * FROM temp.test7_GetTenAssets()

I am aware that documentation explicitly mentions using 'CALL' to execute a stored procedure. I, however, need to use a SELECT statement to align with how some of the existing components of the Cache application I am working with, are built.

If 'CALL' is the only way to call this stored procedure then why is it possible to call a class query i.e. Query Get(x As %String) As %Query (ROWSPEC = "FundCode:%String ...") [ SqlProc ] using a SELECT? I wonder which super class do class queries extend that allows them to be Stored Procedures and still be executed via a SELECT. Does any one know?

Any help will be greatly appreciated.

Discussion (11)1

Log in or sign up to continue

Abbad Minhas · Dec 4, 2019

Hi @Dan Pasco

This greatly helps my understanding. It would be nice if this information formed part of the documentation for Cache itself and could be elaborated upon.

Essentially to use SELECT, function must implement Table-Valued Function (TVF) interface. Is there any specific documentation relative to TVF you are able to point me towards please?

0 0

score 0 · Answer 1 · 2019-11-28T11:42:11-05:00

Perhaps you could expand on what you are trying to achieve. Where are you making the call from, odbc, objectscript, terminal? Which components are you working with?

One option may be to embed the call to the stored procedure in objectscript code,

   SET a=7,b="A",c=99
   &sql(CALL MyProc(:a,:b,:c))

https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_call

Does that give you what you are looking for?

score 0 · Answer 2 · 2019-11-28T12:08:59-05:00

Hi Rupert,

I hope you are well and enjoying robotics.

Thanks for coming back to me.

Your recommended approach, though hugely appreciated, won't work for me I'm afraid.

In answer to your questions:

I am making my call from object script, as part of an already built component within a financial cache application that explicitly uses a SELECT statement
Changing this component to use CALL instead of a SELECT is not possible due to dependencies and legacy code.

Essentially question I am asking is why some stored procedures can only be called via CALL and why can some be called by both CALL and SELECT (such as in the case of class queries)?

I get a feeling this has something to do with SQL-Invokable-Routine (SIR) - see @Dan Pasco comment here but there is almost nothing in the documentation or any code for me to go further on.

score 0 · Answer 3 · 2019-12-04T06:01:26-05:00

Thanks @Benjamin De Boe

I believe we are on a high enough version which should cater for this.

w $zv

Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2017.2.2 (Build 865U) Mon Jun 25 2018 10:50:02 EDT

score 0 · Answer 4 · 2019-12-04T06:47:49-05:00

and I guess for those that don't want to dig deeper, an easier approach to using SELECT * FROM..., is to use custom class queries.

score 1 · Answer 5 · 2019-11-28T12:31:45-05:00

https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=GSQL_procedures

I believe this page explains why, and I will also try my best to condense information:

Stored Procedures that return a single value are classified as Stored Functions. These can be SELECTed but not Stored Procedures.
Class Queries are SQL-Invokable-Routines (SIRs), but they have additional ClassMethods defined that allow it to be SELECTed. This is namely the <<name>>EXECUTE() and <<name>>FETCH() functions. This is reflected in the CREATE QUERY documentation that warns if EXECUTE() and FETCH() are not defined, SQLCODE -46 is thrown. ( https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_createquery)

score 0 · Answer 6 · 2019-12-04T06:02:55-05:00

Abbad Minhas · Dec 4, 2019

Thanks for your response @Kevin Chan

0 0

score 0 · Answer 7 · 2019-12-04T10:46:40-05:00

Abbad Minhas · Dec 4, 2019

Thanks very much @Dan Pasco. Very useful responses.

0 0

score 1 · Answer 8 · 2019-11-28T12:43:44-05:00

Hi,

Yes, SIR :) SQL Invokable routine. A procedure is invoked by CALL and a function is invoked using the function syntax. The primary difference between the two is in which statements you use to invoke the SIR. CALL invokes procedures, other DML statements can be used to invoke functions. The standard states that for every SQL Function there is an implicit procedure. That means that every SQL Function can also be invoked by CALL. The reverse is not necessarily true. IRIS SQL (and Caché SQL) does not enforce that restriction as even a procedure can be invoked using function syntax, as long as it returns a value. The issue with us doing that is that we do not restrict output-directed arguments and we do not restrict a Result Set Sequence (RSS) - strict SQL does not allow either in an SQL Function. Just be careful - invoking such a procedure (with output directed parameters and/or returnresultsets) as a function will ignore all but the return value.

That said, where the SQL Function is located in a DML statement matters. In order for an SQL Function to be referenced in the FROM clause of a SELECT statement it must implement the Table-Valued Function (TVF) interface. Queries do that (I don't recall in which version we added that capability) and %SQL.CustomQuery does. Refer to the class documentation for %SQL.CustomQuery for more information. If you still have questions then let me know!

Defining a class method as a stored procedure will not work as it does not implement the TVF interface.

-Dan

score 2 · Answer 9 · 2019-12-04T08:14:11-05:00

Perhaps this link will help - https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_from.

Look specifically for the paragraph on table-valued functions.

What isn't especially clear in this document is how to implement a custom query. The simplest and most portable way to implement a custom query is to extend %SQL.CustomQuery. Another way is to define a class query whose type is %Query, define a ROWSPEC, and implement the various functions. It is much simpler to extend %SQL.CustomQuery. There should be good documentation in that class.

There was a bug in 2017.1 that caused some problems with the seamless projection of a %SQL.CustomQuery subclass as an SQL function. That bug has since been fixed but I do not know if it is fixed in 2017.2.2.

If you need more information, please let me know.

-Dan

score 0 · Answer 10 · 2019-11-29T05:00:30-05:00

Besides the elaborate earlier answers about the actual interfaces under (or at) the hood, maybe a simple question to ask is which version you're on. If I'm not mistaking, that SELECT option was only added some 5 years ago and because the word "legacy" shows up on this page, I thought it's worth checking too ;-)