.. _hello-pyngres:

Hello Pyngres
*************

This application connects to a local Ingres database called
**sandbox** then 
immediately disconnects. If it runs without error it proves
that ``pyngres`` is
correctly installed and that it can communicate with the 
database server. 

There is a link at the bottom of the page to download a runnable version
of this example.

Begin by importing ``pyngres``.

.. CODE::

    import pyngres as py

.. NOTE::

    The import process automatically 
    discovers and loads the Actian OpenAPI library. On Unix-like systems
    it uses the :attr:`II_SYSTEM` environment variable 
    to find the root of the Actian
    installation. On Windows it loads the first OpenAPI DLL it 
    discovers by searching the folders named in :attr:`PATH`.

Before the OpenAPI can be used it must be initialized by calling 
:func:`IIapi_initialize`. 
All OpenAPI functions take a single argument. The argument must be an
instance of a control block appropriate to the function. 
To initialize the OpenAPI it is first necessary to instantiate an 
:attr:`IIAPI_INITPARM` control block. In this example it is named
:attr:`inp`:

.. CODE::

    inp = py.IIAPI_INITPARM()

After any control block is allocated it needs to be initialized with 
the values to be passed to the OpenAPI function. Here the 
:attr:`in_timeout` attribute of :attr:`inp` is set to -1 (which
indicates "no timeout", wait forever to initialize), and 
:attr:`in_version` is assigned the 
local OpenAPI version number (whatever that happens to be, 
effectively requesting the most recent version):

.. CODE::

    inp.in_timeout = -1
    inp.in_version = py.IIAPI_VERSION

Call :func:`IIapi_initialize` passing its control block:

.. CODE::

    py.IIapi_initialize(inp)

Control blocks are used not only to pass values to OpenAPI functions
but also to return information to the caller. In this case the 
status of the function call is returned as :attr:`in_status`, and 
if the function was successful, a handle for the OpenAPI environment
is returned as :attr:`in_envHandle`. 


.. CODE::

    status = inp.in_status
    if status != py.IIAPI_ST_SUCCESS:
        print('could not initialize the OpenAPI')
        quit()

Note the handle returned for the environment. It will be passed to 
:func:`!IIapi_connect` later.

.. CODE::

    envHandle = inp.in_envHandle

.. IMPORTANT::

    Functions to instantiate control blocks are synchronous; they complete 
    and then return. But most
    other OpenAPI functions are asynchronous, meaning they return before
    completing the requested action.
    The application has
    to determine when the requested action has been completed.
    (See :ref:`asynchronous-functions`.)

The function to connect to the database is asynchronous. 
The simplest way to tell that any asynchronous function has finished 
is to repeatedly check the completion flag in its control block 
using a so-called "busy wait loop". The
body of the loop makes a call 
to :func:`IIapi_wait` to give the OpenAPI the 
opportunity to set the flag.

:func:`IIapi_wait` takes an instance of an :attr:`IIAPI_WAITPARM`.
In preparation for calling asynchronous functions,
instantiate one and set its :attr:`wt_timeout` to 0 (milliseconds),
meaning "don't wait", return immediately. 

..  NOTE:: 

    A :py:attr:`!wt_timeout` of 0 means the application will spin in
    a "tight" loop. iIf there is nothing to do in the busy-wait loop
    it may instead be preferable to either 
    allow a few milliseconds wait or even to wait indefinitely by
    setting :py:attr:`!wt_timeout = -1`. 

.. CODE::

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

.. IMPORTANT::

    The OpenAPI is implemented in C and expects to be called by
    applications conforming to C calling conventions using C 
    datatypes. In Python everything is an object. Python does
    not understand C datatypes and the OpenAPI does not understand
    Python objects.

    In the next piece of code  :attr:`co_target` has to be
    initialized with the name of the database to connect to. But
    :attr:`dbname` is a Python ``str``. It needs to be converted
    to the NUL-terminated array of bytes expected by C. That is
    accomplished using the :func:`str.encode` method.

Connect to the database sever by calling :func:`IIapi_connect`. It 
takes an instance of :attr:`IIAPI_CONNPARM` which has been 
initialized with appropriate values, most of which will not be explained 
here. If the connection request is successful a connection handle is
returned in :attr:`co_connHandle`. The connection handle needs to be noted for 
use later.

.. CODE::

    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)
    while not cop.co_genParm.gp_completed:
        py.IIapi_wait(wtp)

    status = cop.co_genParm.gp_status
    if status != py.IIAPI_ST_SUCCESS:
        print('could not connect to the database')
        quit()

    connHandle = cop.co_connHandle

    print('successfully connected')

The application now has a connection to an Actian database 
so it could start executing SQL. However it is sufficient that it has
demonstrated it is possible to negotiate a connection to a database.
Disconnect and 
terminate in an orderly fashion. 

Although the following OpenAPI
functions may be unfamiliar (refer to the
`OpenAPI Function Reference <https://docs.actian.com/actianingres/12.0/index.html#page/OpenAPIUser/OpenAPI_Functions.htm>`_ for details),
the way they are invoked should be entirely clear.

.. CODE::

    dcp = py.IIAPI_DISCONNPARM()
    dcp.dc_connHandle = connHandle

    py.IIapi_disconnect(dcp)
    while not dcp.dc_genParm.gp_completed:
        py.IIapi_wait(wtp)

    rep = py.IIAPI_RELENVPARM()
    rep.re_envHandle = envHandle
    py.IIapi_releaseEnv(rep)

    tmp = py.IIAPI_TERMPARM()
    py.IIapi_terminate(tmp)

    quit()

:download:`Download <./examples/hello-pyngres.py>`