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 :py:func:`!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. .. _asynchronous-functions: 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 :ref:`elsewhere `. This section concentrates on busy-wait loops. Assume ``pyngres`` has been imported as shown below, defining ``py`` as a shorthand for its namespace: .. code-block:: python 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 :py:func:`!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 :py:attr:`!IIAPI_WAITPARM` control block then set its :py:attr:`!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 :py:attr:`!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. .. code-block:: python 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 :py:func:`!IIapi_connect()`, which takes an :py:attr:`!IIAPI_CONNPARM` control block. The only important value to notice here is :py:attr:`!co_timeout` which is set to -1, meaning no timeout. .. code-block:: python 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 :py:attr:`!co_genParm.gp_completed` flag. If the flag is not set control is passed back to the OpenAPI by calling :py:func:`!IIapi_wait()`. Control returns immediately to the application because :py:attr:`!wt_timeout` is set to 0. The loop is iterated as often as necessary until the OpenAPI marks the request as completed. .. code-block:: python 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: .. code-block:: python 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. .. INCLUDE:: brownlee-tip.rst 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 :func:`!connect` coroutine/task and the spinner is animated by the :func:`!spin` coroutine/task. The spinner runs until the connection is established, at which point the :func:`!connect` task sets the :attr:`!connected` event. The :func:`!spin` task terminates its loop when the :attr:`!connected` event is set and then displays "connected". :download:`Download <./examples/connect-async.py>` .. LITERALINCLUDE:: ./examples/connect-async.py .. 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:: :attr:`!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. :attr:`!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:: :func:`!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 :ref:`Catching DBEvents ` example. .. NOTE:: :func:`!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 :py:func:`!pyngres.IIapi_wait()` For complete information on the OpenAPI functions refer to *OpenAPI Functions* in the `OpenAPI User Guide`_. .. table:: :widths: auto ============================== ======================= Function Behaviour ============================== ======================= :func:`!IIapi_setConnectParam` asynchronous, awaitable :func:`!IIapi_abort` asynchronous, awaitable :func:`!IIapi_autocommit` asynchronous, awaitable :func:`!IIapi_batch` asynchronous, awaitable :func:`!IIapi_cancel` asynchronous, awaitable :func:`!IIapi_catchEvent` asynchronous, **not awaitable** :func:`!IIapi_close` asynchronous, awaitable :func:`!IIapi_commit` asynchronous, awaitable :func:`!IIapi_connect` asynchronous, awaitable :func:`!IIapi_convertData` **synchronous** :func:`!IIapi_disconnect` asynchronous, awaitable :func:`!IIapi_formatData` **synchronous** :func:`!IIapi_getColumnInfo` **synchronous** :func:`!IIapi_getColumns` asynchronous, awaitable :func:`!IIapi_getCopyMap` asynchronous, awaitable :func:`!IIapi_getDescriptor` asynchronous, awaitable :func:`!IIapi_getErrorInfo` **synchronous** :func:`!IIapi_getEvent` asynchronous, awaitable :func:`!IIapi_getQueryInfo` asynchronous, awaitable :func:`!IIapi_initialize` **synchronous** :func:`!IIapi_modifyConnect` asynchronous, awaitable :func:`!IIapi_position` asynchronous, awaitable :func:`!IIapi_prepareCommit` asynchronous, awaitable :func:`!IIapi_putColumns` asynchronous, awaitable :func:`!IIapi_putParms` asynchronous, awaitable :func:`!IIapi_query` asynchronous, awaitable :func:`!IIapi_registerXID` **synchronous** :func:`!IIapi_releaseEnv` **synchronous** :func:`!IIapi_releaseXID` **synchronous** :func:`!IIapi_rollback` asynchronous, awaitable :func:`!IIapi_savePoint` asynchronous, awaitable :func:`!IIapi_scroll` asynchronous, awaitable :func:`!IIapi_setConnectParam` asynchronous, awaitable :func:`!IIapi_setDescriptor` asynchronous, awaitable :func:`!IIapi_setEnvParam` **synchronous** :func:`!IIapi_terminate` **synchronous** :func:`!IIapi_wait` asynchronous, **not awaitable** :func:`!IIapi_xaCommit` asynchronous, awaitable :func:`!IIapi_xaStart` asynchronous, awaitable :func:`!IIapi_xaEnd` asynchronous, awaitable :func:`!IIapi_xaPrepare` asynchronous, awaitable :func:`!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 :ref:`callback-example`. :func:`!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. .. CODE:: @IIapi_callback def my_callback(closure,parmBlock=None): '''callback function''' ... :func:`!IIapi_getCallbackPtr` ----------------------------- A function to return a :attr:`!c_void_p` pointer to an OpenAPI-compliant Python callback function defined using the :func:`!IIapi_callback` decorator. .. CODE:: callback = IIapi_getCallbackPtr(my_callback) ... :func:`!IIapi_getClosurePtr` ----------------------------- A function to return a :attr:`!c_void_p` pointer to Python object to use as a callback closure. .. CODE:: closure = IIapi_getClosurePtr(my_closure) ... :func:`!IIapi_getClosure` -------------------------- Return the Python object pointed to by a :attr:`!ctypes.c_void_p` pointer. .. CODE:: none closure = IIapi_getClosure(ptr,closureClass) :attr:`!ptr` is expected to be a :attr:`!ctypes.c_void_p` pointer. :attr:`!closureClass` is the class of the instance to which the pointer refers. The returned object will be an instance of that class.