Pyngres Reference

Because pyngres is only a wrapper around the Actian OpenAPI the primary documentation for understanding the OpenAPI will always be the OpenAPI User Guide.

As of this writing the OpenAPI is at version 11, which supports all versions of Ingres up to Ingres 12.x.

Tip

On-line documentation doesn’t suite everyone. You may prefer to download the PDF documentation set from the Actian Electronic Software Distribution site. (If you are not already a community member you will have to register.)

The following is the additional information relevant to using Pyngres to invoke OpenAPI functions from Python.

There are three versions of pyngres. The first is a base version in which all the OpenAPI functions are used exactly as they are in C code. Using the base version means all the asynchronous functions must be waited using IIapi_wait() in a busy-wait loop.

The pyngres.blocking version makes the functions look and behave as if they are actually synchronous. For many simple applications this will be perfectly suitable, reducing visual clutter and eliminating the risk of forgetting a necessary busy-wait loop.

Finally the pyngres.asyncio version presents the OpenAPI functions as awaitable coroutines. This unlocks the power of Python to run concurrent Ingres tasks without the complexity of multi-threading, and to mix Ingres database operations freely with awaitable functions from other asyncio packages, such as GUI frameworks.

Working with Asynchronous Functions

Because an Ingres or Vector client is communicating with the database server over a network, possibly even a wide area network, there will be delays between invoking OpenAPI functions and the server completing even the simplest requests such as making a database connection or returning a row from a cursor. Far worse, the request may execute a very long-running query.

Many small delays or one very long one would interfere with the responsiveness of a GUI framework. To allow applications to be written that give the end user the best experience the OpenAPI operates asynchronously. OpenAPI functions return immediately whether the requested action has been completed or not. The application has to somehow determine, later, that the request has been completed.

Using pyngres and Busy-Wait Loops

pyngres supports two mechanisms for determining that a request is completed. Either the OpenAPI function is passed a callback function to execute on completion, or the application runs a so-called busy-wait loop to repeatedly examine a completion flag until it is set.

Callbacks are described elsewhere. This section concentrates on busy-wait loops.

Assume pyngres has been imported as shown below, defining py as a shorthand for its namespace:

import pyngres as py

inp = py.IIAPI_INITPARM()
inp.in_timeout = -1
inp.in_version = py.IIAPI_VERSION
py.IIapi_initialize(inp)
envHandle = inp.in_envHandle

All OpenAPI busy-wait loops invoke IIapi_wait(). It returns control to the OpenAPI so that it can set the completion flag if the operation is in fact complete. To get ready to call it, first allocate an IIAPI_WAITPARM control block then set its wt_timeout to 0, which means don’t wait, return immediately, no matter what. Functions that allocate OpenAPI control blocks complete before returning. There is no need to check for completion. This control block can be set up at any time before it is needed.

Note

A wt_timeout of 0 means the application will spin in a “tight” loop. It may be preferable to allow a non-zero wait if there is nothing else to be done in the busy-wait loop.

wtp = py.IIAPI_WAITPARM()
wtp.wt_timeout = 0

Later, allocate a control block for the function to be invoked. In this example the function is IIapi_connect(), which takes an IIAPI_CONNPARM control block. The only important value to notice here is co_timeout which is set to -1, meaning no timeout.

dbname = 'sandbox'
target = dbname.encode()
cop = py.IIAPI_CONNPARM()
cop.co_target = target
cop.co_connHandle = envHandle
cop.co_type = py.IIAPI_CT_SQL
cop.co_timeout = -1

With both control blocks allocated and initialized the OpenAPI function can be called to initiate the request. Because it is asynchronous it returns immediately even though the request may take several seconds to complete over a slow channnel.

The application then loops to repeatedly check the co_genParm.gp_completed flag. If the flag is not set control is passed back to the OpenAPI by calling IIapi_wait(). Control returns immediately to the application because wt_timeout is set to 0. The loop is iterated as often as necessary until the OpenAPI marks the request as completed.

py.IIapi_connect(cop)
while not cop.co_genParm.gp_completed:
    py.IIapi_wait(wtp)

In this description of the process no useful work is shown being done in the busy-wait loop, but the application is free to do anything, like update a display or advance a spinner, before doing the next check for completion.

The approach shown above is faithful to the way OpenAPI functions are called in C programs. If one is using pyngres to learn to use the OpenAPI, or to prototype something and the ultimate intention is a C implementation, it is the recommended approach.

But if the ultimate objective is to develop an application with Python rather than C, it is better to use pyngres.blocking, and even better to use pyngres.asyncio.

Using pyngres.blocking

Using pyngres.blocking is almost identical to what is shown above, except the OpenAPI functions behave as if they are synchronous so no busy-wait loop is required. In fact the loop is still executed but it is done automatically. The code is more concise but the application does not get the opportunity to do useful work while waiting for completion. It will will be less responsive. There are many applications where that trade-off is acceptable: batch scripts being one example.

The code would resemble this:

import pyngres.blocking as py

inp = py.IIAPI_INITPARM()
inp.in_timeout = -1
inp.in_version = py.IIAPI_VERSION
py.IIapi_initialize(inp)
envHandle = inp.in_envHandle

dbname = 'sandbox'
target = dbname.encode()
cop = py.IIAPI_CONNPARM()
cop.co_target = target
cop.co_connHandle = envHandle
cop.co_type = py.IIAPI_CT_SQL
cop.co_timeout = -1

py.IIapi_connect(cop)

...

Using pyngres.asyncio

pyngres.asyncio is the most powerful way to harness the OpenAPI. It seamlessly presents the OpenAPI functions as asyncio awaitable coroutines.

Tip

The usefulness of asyncio is hard to overstate. Every Python programmer would benefit from familiarity with it. One excellent resource is Python Asyncio: The Complete Guide, maintained by Jason Brownlee. The content is also available as a book entitled Python Asyncio Jump-Start, ISBN-13: 979-8361197620.

This example initiates a connection to a database. While it is waiting for the connection to be completed it displays a rotating “spinner”. The database connection is requested by the connect() coroutine/task and the spinner is animated by the spin() coroutine/task. The spinner runs until the connection is established, at which point the connect() task sets the connected event. The spin() task terminates its loop when the connected event is set and then displays “connected”.

Download

import asyncio
import pyngres.asyncio as py

async def spin(connected):
    '''display a spinning rotor until connected'''

    rotor = '-\\|/'
    i = 0
    while not connected.is_set():
        i = (i+1) % 4
        output = rotor[i] + ' ' + '\b\b' 
        print(output, end='', flush=True)
        await asyncio.sleep(0.10)
    print('connected')

async def connect(connected):
    '''connect to an Actian database'''

    print('connecting...')
    inp = py.IIAPI_INITPARM()
    inp.in_timeout = -1
    inp.in_version = py.IIAPI_VERSION
    py.IIapi_initialize(inp)
    envHandle = inp.in_envHandle

    dbname = 'loathing::sandbox'
    target = dbname.encode()
    
    cop = py.IIAPI_CONNPARM()
    cop.co_target = target
    cop.co_connHandle = envHandle
    cop.co_type = py.IIAPI_CT_SQL
    cop.co_timeout = -1

    await py.IIapi_connect(cop)

    connected.set()

async def main():
    '''start the tasks to run concurrently'''

    connected = asyncio.Event()
    spinner = spin(connected)
    connector = connect(connected)
    await asyncio.gather(spinner,connector)

asyncio.run(main())
quit()

Note

This is a whimsical example just to show that Python is capable of doing other things while waiting for the OpenAPI to complete. The real utility of cooperative multitasking using asyncio is being able to run multiple Ingres sessions simultaneously, or interacting with a message queueing service such as PyZMQ, or reacting instantly to mouse movments and button-clicks in a GUI, all while interacting with the Actian DBMS.

OpenAPI Status Codes

Pyngres Constant	Value
IIAPI_ST_SUCCESS	0
IIAPI_ST_MESSAGE	1
IIAPI_ST_WARNING	2
IIAPI_ST_NO_DATA	3
IIAPI_ST_ERROR	4
IIAPI_ST_FAILURE	5
IIAPI_ST_NOT_INITIALIZED	6
IIAPI_ST_INVALID_HANDLE	7
IIAPI_ST_OUT_OF_MEMORY	8

Note

IIAPI_ST_ERROR indicates an error such as an SQL syntax error or a reference to a non-existent database asset. The DBMS session remains usable and the application can continue.

IIAPI_ST_FAILURE indicates the session has failed. It could indicate an OpenAPI protocol error or it could indicate the connection to the DBMS is no longer be usable.

Function Reference

Most but not all OpenAPI functions are asynchronous.

If using pyngres.asyncio, most but not all of the asynchronous functions are awaitable.

Note

IIapi_catchEvent() is asynchronous but not awaitable. Use asyncio or multuthreading to run it in a busy-wait loop in a concurrent task. Refer to the Catching DBEvents example.

Note

IIapi_wait() is not present in pyngres.asyncio. It would serve no purpose. If for some reason it is needed, import both pyngres.asyncio and pyngres then invoke it using pyngres.IIapi_wait()

For complete information on the OpenAPI functions refer to OpenAPI Functions in the OpenAPI User Guide.

Function	Behaviour
`IIapi_setConnectParam()`	asynchronous, awaitable
`IIapi_abort()`	asynchronous, awaitable
`IIapi_autocommit()`	asynchronous, awaitable
`IIapi_batch()`	asynchronous, awaitable
`IIapi_cancel()`	asynchronous, awaitable
`IIapi_catchEvent()`	asynchronous, not awaitable
`IIapi_close()`	asynchronous, awaitable
`IIapi_commit()`	asynchronous, awaitable
`IIapi_connect()`	asynchronous, awaitable
`IIapi_convertData()`	synchronous
`IIapi_disconnect()`	asynchronous, awaitable
`IIapi_formatData()`	synchronous
`IIapi_getColumnInfo()`	synchronous
`IIapi_getColumns()`	asynchronous, awaitable
`IIapi_getCopyMap()`	asynchronous, awaitable
`IIapi_getDescriptor()`	asynchronous, awaitable
`IIapi_getErrorInfo()`	synchronous
`IIapi_getEvent()`	asynchronous, awaitable
`IIapi_getQueryInfo()`	asynchronous, awaitable
`IIapi_initialize()`	synchronous
`IIapi_modifyConnect()`	asynchronous, awaitable
`IIapi_position()`	asynchronous, awaitable
`IIapi_prepareCommit()`	asynchronous, awaitable
`IIapi_putColumns()`	asynchronous, awaitable
`IIapi_putParms()`	asynchronous, awaitable
`IIapi_query()`	asynchronous, awaitable
`IIapi_registerXID()`	synchronous
`IIapi_releaseEnv()`	synchronous
`IIapi_releaseXID()`	synchronous
`IIapi_rollback()`	asynchronous, awaitable
`IIapi_savePoint()`	asynchronous, awaitable
`IIapi_scroll()`	asynchronous, awaitable
`IIapi_setConnectParam()`	asynchronous, awaitable
`IIapi_setDescriptor()`	asynchronous, awaitable
`IIapi_setEnvParam()`	synchronous
`IIapi_terminate()`	synchronous
`IIapi_wait()`	asynchronous, not awaitable
`IIapi_xaCommit()`	asynchronous, awaitable
`IIapi_xaStart()`	asynchronous, awaitable
`IIapi_xaEnd()`	asynchronous, awaitable
`IIapi_xaPrepare()`	asynchronous, awaitable
`IIapi_xaRollback()`	asynchronous, awaitable

Pyngres Callback Helper Functions

Pyngres provides additional Python-specific helper functions to simplify the use of OpenAPI callbacks to Python functions.

Note

The helper functions are pyngres extensions. They are not part of the Actian OpenAPI.

Callbacks are required in order to handle DBEvent notifications and trace messages sent by the DBMS (e.g. I/O traces, transaction log traces, query execution plans, etc.). They can also be used to notify the application when asynchronous OpenAPI functions have completed a requested operation.

See Using Callbacks.

`IIapi_callback()`

A decorator factory used to declare Python callback functions for use by the OpenAPI. An OpenAPI-compliant callback is expected to take two argument; a pointer to a closure and a pointer to an optional OpenAPI parmBlock. The closure can be any Python object.

@IIapi_callback
def my_callback(closure,parmBlock=None):
    '''callback function'''
    ...

`IIapi_getCallbackPtr()`

A function to return a c_void_p pointer to an OpenAPI-compliant Python callback function defined using the IIapi_callback() decorator.

callback = IIapi_getCallbackPtr(my_callback)
...

`IIapi_getClosurePtr()`

A function to return a c_void_p pointer to Python object to use as a callback closure.

closure = IIapi_getClosurePtr(my_closure)
...

`IIapi_getClosure()`

Return the Python object pointed to by a ctypes.c_void_p pointer.

closure = IIapi_getClosure(ptr,closureClass)

ptr is expected to be a ctypes.c_void_p pointer. closureClass is the class of the instance to which the pointer refers. The returned object will be an instance of that class.

Pyngres Reference

Working with Asynchronous Functions

Using pyngres and Busy-Wait Loops

Using pyngres.blocking

Using pyngres.asyncio

OpenAPI Status Codes

Function Reference

Pyngres Callback Helper Functions

IIapi_callback()

IIapi_getCallbackPtr()

IIapi_getClosurePtr()

IIapi_getClosure()

`IIapi_callback()`

`IIapi_getCallbackPtr()`

`IIapi_getClosurePtr()`

`IIapi_getClosure()`