Embedded Python Stored Procedure

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:

For testing the stored procedure can be run from a terminal:

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:

Helper classe qHandle. This is initialize at Execute and updated during record retrieval

Helper class for filling rows from Python with readable names:

Hope this example is useful to explore some new ideas and possabilities with Embedded Python.