Question
Alicia Watkins · Nov 1, 2021

SQL Method to use in a Mumps routine

Hi there,

Does anyone have an example of the method used to run an SQL query and generate the view in a Mumps routine.   I have several SQL queries created from a vendor that need to be converted and the corresponding view referenced.  I cannot seem to find any examples in IDX routines or in the  IRIS/Cache documentation.  Any help is appreciated.

-Alicia

Product version: IRIS 2021.1
0
0 268
Discussion (5)1
Log in or sign up to continue

not so clear what you mean by MUMPS routine.
if it is an .INT routine you just create an object from one of the ResultSet classes and execute the query
if it is a .MAC routine you may also use Embedded SQL which I don't prefer so much. 

And BTW. what is an IDX routine ???

What do expect by a VIEW? It is just a kind of store SQL statement.

hi...a mumps routine is basically object script that has been around for years and predates Cache/Ensemble etc.  IDX routines are from the Vendor  (Athena).  Do you have example of .int code where the SQL method is getting called.  Once the method is executed, where can the view be referenced?

Thnks

callSQL ;
 ; compose an SQL Statement
 set query="SELECT something FROM TABLE_A JOIN TABLE_B ON ...........WHERE ...."  
 set rs=##class/%ResultSet.%NEW()
 set sc=rs.Prepare(query)
 if 'sc write $system.OBJ.DisplayError(sc)
 set sc=rs.Execute()
 if 'sc write $system.OBJ.DisplayError(sc)
 while rs.%Next() {
    ;consume the result set  row by row
}

OK yo can write this in .INT, in .MAC, in Methods .....

and SORRY:
IDX is still just 3 chars to me, as Athena is 6 chars. (or the Greek Godess of Wisdom)

hi..thanks for the code reference.  One more question, what if the query is long.  Can it still be set to a variable then called within the routine.    I can try to test that too.  I just wondered if you had executed a long query within a routine.

Thanks

The SQL statement you pass is an ordinary string.
So with LongString enable it is about 3,4MB  and of course I have never tried to write so
much in a single string. That's boring and hard to type.
If you mean running long it's probably just useful on the command line or background.
an ordinary connection to over ODBC/JDBC or HTTP in browser will most likely time out. 
What type of LONG are you referring to ?