Overview
The online documentation contains a reference Defining and Using Class Queries -
Customizing Stored Procedures with ObjectScript directly has been useful to access NoSQL storage and external messaging via integration, to present output in tabular format.
For example: An application that already uses 90% SQL interaction from a front end, can then also extend this access to the other 10% of required platform functionality, via the same SQL access.
The purpose of this article is to explore how to achieve the same effect via Embedded Python methods.
Figure 1: Stored Procedure as a SQL gateway to other platform functionality
Demonstration
For this example the following NoSQL storage was defined:
| ^alwo.IndexBook("A",1)="abc" ^alwo.IndexBook("A",2)="def" ^alwo.IndexBook("B")="" ^alwo.IndexBook("C")="" ^alwo.IndexBook("D",1)="gef" ^alwo.IndexBook("E",1)="ijk" ^alwo.IndexBook("E",2)="lmn" |
For testing the stored procedure can be run from a terminal:
|
GBI>Do $SYSTEM.SQL.Shell() [SQL]GBI>>call alwo.PyProcTest_GetNotes('A') Dumping result #1 |
As the tab ( first key ) "A" was supplied, data from sub-nodes are expanded and returned as records.
Other nodes that either are not selected, or don't contain data are returned as "0" records.
Code concepts
When a query ( GetNotes ) is implemented in a class, the query content can be achieved with only SQL.
When compiled three class methods get generated:
- GetNotesExecute
- GetNotesFetch
- GetNotesClose
There are many scenarios where data is not SQL:
- NoSQL globals
- Parameterized Interaction with an external system to retrieve and consolidate data
By implementing these three methods directly, it is possible to control access and give tabular responses for a wide range of platform capabilities.
There are a couple of interaction variations:
Cache all response data up front
- The GetNotesExecute method would access resources to build up a response in a temporay global.
This could be useful for a consistent view on data which may involving locking update access for a brief period.
The GetNotesFetch method would get repeatedly called returing records from the temporary data
The GetNotesClose method would tidy up and delete the temporary data
Dynamic response data
The GetNotesExecute method is called. This doesn't do much besides initiating a qHandle context available for the Fetch method
The GetNotesFetch method is called. Each time a new record is dynamically retrieved
The GetNotesClose method has little or no tidy up required
This is the approach used in the code example given.
Paging Opportunities and such
Depending on the scenario, filling batches of return records dynamically, may be used to reduce the need to run a "full query" where only a slice-area of return records was needed.
The code
The execute method has a $C(0) expression. This is simply to match a Null string, which is different from an empty string.
A null string may be passed in when a stored procedure was invoked with an empty string argument.
Method GetNotesFetch acts as an objectscript wrapper for GetNotesFetchPy where the real work is happening. The rationale is the expecation of the calling framework to leverage ByRef arguments, and the wrapper bridges this.
The code is an example of navigating and retrieval of NoSQL data via Python code.
The Python implementation uses a try-except block to trap python code runtime issues and propergates this error information detail in the normal way back to the client application. This can be activated by uncommenting the line starting "#x=10/0".
For example trapped error returned to client:
| [SQLCODE: <-400>:<Fatal error occurred>] [%msg: <Python general error 'alwo.PyProcTest::GetNotesFetchPy:Traceback (most recent call last): File "PyProcTest", line 21, in GetNotesFetchPy ZeroDivisionError: division by zero '>] |
/// Ref: Defining and Using Class Queries
/// https://docs.intersystems.com/iris20232/csp/docbook/DocBook.UI.Page.cls?...
Class alwo.PyProcTest [ Abstract ]
{
/// <example>
/// do $SYSTEM.SQL.Shell()
/// call alwo.PyProcTest_GetNotes('D')
/// </example>
Query GetNotes(tabName As %String) As %Query(ROWSPEC = "Tab:%String,NoteId:%Integer,NoteText:%String") [ SqlName = PyProcTest_GetNotes, SqlProc ]
{
}
/// ObjectScript due to ByRef signature
ClassMethod GetNotesExecute(ByRef qHandle As %Binary, tabName As %String = "") As %Status
{
set qHandle=##class(alwo.PyNote.GetNotes.qHandle).%New()
// Note that an empty string passed from SQL statement may appear as the null character $C(0) instead of empty string ""
set:tabName'=$C(0) qHandle.selectedTab=tabName // may be empty string
Quit $$$OK
}
/// ObjectScript due to ByRef signature
ClassMethod GetNotesFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = GetNotesExecute ]
{
set refRow=##class(alwo.PyNote.GetNotes.Row).%New()
set status=..GetNotesFetchPy(.qHandle,.refRow)
if qHandle.atEnd {
set AtEnd=1
} else {
// repack output row to $List format
set Row=$ListBuild(refRow.Tab,+refRow.NoteId,refRow.NoteText)
}
Quit status
}
/// Access to tabular view of global 2 keys deep with data at level 2 nodes
/// <example>
/// zwrite ^alwo.IndexBook
///
/// ^alwo.IndexBook("A",1)="abc"
/// ^alwo.IndexBook("A",2)="def"
/// ^alwo.IndexBook("B")=""
/// ^alwo.IndexBook("C")=""
/// ^alwo.IndexBook("D",1)="gef"
/// ^alwo.IndexBook("E",1)="ijk"
/// ^alwo.IndexBook("E",2)="lmn"
/// <example>
///
/// Required output
/// <example>
/// | Tab | NoteId | NoteText
/// --------------------------
/// | A | 1 | abc
/// | A | 2 | def
/// | B | 0 |
/// | C | 0 |
/// | D | 1 | gef
/// | E | 1 | ijk
/// | E | 2 | lmn
/// --------------------------
/// </example>
ClassMethod GetNotesFetchPy(qHandle As alwo.PyNote.GetNotes.qHandle, pRow As alwo.PyNote.GetNotes.Row) As %String [ Language = python ]
{
import iris
import traceback
ret=iris.cls('%SYSTEM.Status').OK()
try:
# based on the existance of defined nodes then iterate
gname="^alwo.IndexBook"
gIterator=iris.gref(gname)
# Iterate on Key1 "Tab name" when Key2 "NoteId" was previously set to empty
if (None==qHandle.currentPage) or (""==qHandle.currentPage):
qHandle.currentTab=gIterator.order([qHandle.currentTab])
# change of tab context
if (None==qHandle.currentTab) or (qHandle.currentTab==""): # no records
qHandle.atEnd=True
return ret
# default open first tab if has values
if qHandle.selectedTab==None or qHandle.selectedTab=="":
qHandle.selectedTab=qHandle.currentTab
pRow.Tab=qHandle.currentTab
#x=10/0 # uncomment to demonstrate ZeroDivisionError handling
# Iterate on Key2 "NoteId"
if (qHandle.selectedTab==qHandle.currentTab):
qHandle.currentPage=gIterator.order([qHandle.currentTab, qHandle.currentPage])
if (qHandle.currentPage!=None) and (qHandle.currentPage!=""):
pRow.NoteId=qHandle.currentPage
pRow.NoteText=gIterator.get([qHandle.currentTab, qHandle.currentPage])
# checks if current record was the last one
next=gIterator.order([qHandle.currentTab, qHandle.currentPage])
if (None==next) or (""==next):
qHandle.currentPage=None # causes iterate on Key1 on next method invocation
except Exception:
pErrorMessage='alwo.PyProcTest::GetNotesFetchPy:'+(traceback.format_exc())
return iris.cls('%SYSTEM.Status').Error(2603,pErrorMessage)
return ret
}
/// ObjectScript due to ByRef signature
ClassMethod GetNotesClose(ByRef qHandle As %Binary) As %Status
{
set qHandle=""
Quit $$$OK
}
}
Helper classe qHandle. This is initialize at Execute and updated during record retrieval
Class alwo.PyNote.GetNotes.qHandle Extends %RegisteredObject
{
Property currentTab As %String;
Property currentPage As %String;
Property selectedTab As %String;
Property atEnd As %Integer [ InitialExpression = 0 ];
}
Helper class for filling rows from Python with readable names:
Class alwo.PyNote.GetNotes.Row Extends %RegisteredObject
{
Property Tab As %String;
Property NoteId As %String;
Property NoteText As %String;
}
Hope this example is useful to explore some new ideas and possabilities with Embedded Python.
Top comments (0)