New post
Article
· Sep 5, 2023 8m read

Embedded Python Stored Procedure

#Embedded Python #Globals #Interoperability #SQL #InterSystems IRIS

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 Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.

[SQL]GBI>>call alwo.PyProcTest_GetNotes('A')

Dumping result #1
Tab     NoteId  NoteText
A       1       abc
A       2       def
B       0
C       0
D       0
E       0
 
6 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0003s/4/100/0ms
          execute time(s)/globals/cmds/disk: 0.0009s/12/1,096/0ms
                                query class: %sqlcq.GBI.cls27, %Library.ProcedureContext
---------------------------------------------------------------------------

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

1. 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.

2. The GetNotesFetch method would get repeatedly called returing records from the temporary data

3. The GetNotesClose method would tidy up and delete the temporary data

Dynamic response data

1. The GetNotesExecute method is called. This doesn't do much besides initiating a qHandle context available for the Fetch method

2. The GetNotesFetch method is called. Each time a new record is dynamically retrieved

3. 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
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.

Discussion (0)1
Log in or sign up to continue