Connecting to a Database (Asynchronously)

Connect to a database called sandbox on a vnode called loathing.

Note

Basic knowledge of asyncio is assumed for this example.

OpenAPI functions are invoked using await to yield to the asyncio event-loop pending completion of the functions. Other runnable tasks are allowed to execute while the OpenAPI operation is being completed in the background.

Download

 1import asyncio
 2import pyngres.asyncio as py
 3
 4async def spin(connected):
 5    '''display a spinning rotor until connected'''
 6
 7    rotor = '-\\|/'
 8    i = 0
 9    while not connected.is_set():
10        i = (i+1) % 4
11        output = rotor[i] + ' ' + '\b\b' 
12        print(output, end='', flush=True)
13        await asyncio.sleep(0.10)
14    print('connected')
15
16async def connect(connected):
17    '''connect to an Actian database'''
18
19    print('connecting...')
20    inp = py.IIAPI_INITPARM()
21    inp.in_timeout = -1
22    inp.in_version = py.IIAPI_VERSION
23    py.IIapi_initialize(inp)
24    envHandle = inp.in_envHandle
25
26    dbname = 'loathing::sandbox'
27    target = dbname.encode()
28    
29    cop = py.IIAPI_CONNPARM()
30    cop.co_target = target
31    cop.co_connHandle = envHandle
32    cop.co_type = py.IIAPI_CT_SQL
33    cop.co_timeout = -1
34
35    await py.IIapi_connect(cop)
36
37    connected.set()
38
39async def main():
40    '''start the tasks to run concurrently'''
41
42    connected = asyncio.Event()
43    spinner = spin(connected)
44    connector = connect(connected)
45    await asyncio.gather(spinner,connector)
46
47asyncio.run(main())
48quit()

Download

Start by importing pyngres.asyncio.

import pyngres.asyncio as py

Initialize the OpenAPI.

# use the latest version of the OpenAPI

inp = py.IIAPI_INITPARM()
inp.in_timeout = -1
inp.in_version = py.IIAPI_VERSION
status = inp.in_status
if status != py.IIAPI_ST_SUCCESS:
    print('could not initialize the OpenAPI')
    quit()

# note the envHandle for use when connecting
envHandle = inp.in_envHandle

Construct the target name as a NUL-terminated array of bytes (i.e. a C string) by encoding dbname (which is a Python str and hence meaningless to the OpenAPI).

# use a target of the form vnode::database

dbname = 'loathing::sandbox'
target = dbname.encode()

To participate in cooperative multitasking all OpenAPI applications have to be structured using coroutines.

# define the main coroutine

async def main(target):
    '''control the overall application'''

Allocate a control block for Iiapi_connect(). Initialize it with the database target name; the OpenAPI environment handle; the py.!IIAPI_CT_SQL constant to indicate this will be an SQL session, and set the timeout to -1 so the OpenAPI will wait indefinitely for the connection to be established.

# blah blah blah

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

Invoke IIapi_connect() by awaiting it. asyncio will hand control to any other runnable task(s) that are pending. Control will return to this task only after it becomes pending because the database connection request completed.

await py.IIapi_connect(cop)

The request can be completed successfully or it might fail for some reason, such as an invalid database name.

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

(See Fetching Error Information for an illustration of how to fully report an error.)

When the connection is successfully established the returned connection handle must be noted for future use in other OpenAPI function invokations. The OpenAPI uses connection handles to allows it to interact correctly with multiple simultaneous database connections in the same application.

connHandle = cop.co_connHandle

Once the connection is established the application can start sending SQL statements to be executed, fetching query results, and controlling the session behaviour (e.g. committing transactions, setting lockmodes, changing display formats.)