Execute a SELECT Statement Returning Row(s)

Execute a SELECT statement that takes no parameters and returns one or more rows.

SELECT name, weight
FROM foo
WHERE ordernr = 71025

Note

asyncio is used in this example but it is not essential. To use busy-wait loops instead, see Working with Asynchronous Functions.

Important

qy_queryType must be set to py.IIAPI_QT_QUERY to indicate which protocol the OpenAPI must expect.

Important

The OpenAPI allocates an array of IIAPI_DESCRIPTOR for the query results. It is released when the application executes IIapi_close(). Copy any required descriptor information before doing so.

Warning

Failing to call IIapi_close() will create a memory leak.

Download

 1import asyncio
 2import pyngres.asyncio as py
 3import ctypes
 4...
 5
 6# assume a session was started and connHandle is set. Also assume
 7# a transaction is already in progress and tranHandle has a value, or
 8# tranHandle is set to None, indicating the start of a new transaction
 9
10async def exec_select():
11    '''execute a SELECT statement and receive rows'''
12
13    # encode the query so the DBMS can understand it
14    sql = (
15        "SELECT name, weight "
16        "FROM foo " 
17        "WHERE ordernr = 71025" )
18    queryText = sql.encode()
19
20    qyp = py.IIAPI_QUERYPARM()
21    qyp.qy_connHandle = connHandle
22    qyp.qy_queryType = py.IIAPI_QT_QUERY
23    qyp.qy_queryText = queryText
24    await py.IIapi_query(qyp)
25
26    tranHandle = qyp.qy_tranHandle
27    stmtHandle = qyp.qy_stmtHandle
28
29    # get result descriptors
30    gdp = py.IIAPI_GETDESCRPARM()
31    gdp.gd_stmtHandle = stmtHandle
32    await py.IIapi_getDescriptor(gdp)
33
34    # note the number of columns being returned
35    n_columns = gdp.gd_descriptorCount
36
37    # allocate a sufficiently large list of IIAPI_DATAVALUEs  
38    datavalues = (py.IIAPI_DATAVALUE * n_columns)()
39
40    # allocate buffers for name and weight
41    name = ctypes.c_buffer(25)
42    weight = ctypes.c_double()
43
44    # point to the buffers 
45    datavalues[0].dv_value = ctypes.addressof(name)
46    datavalues[1].dv_value = ctypes.addressof(weight)
47
48    # get one result row at a time from the server; loop until IIAPI_ST_NO_DATA
49    gcp = py.IIAPI_GETCOLPARM()
50    gcp.gc_columnCount = n_columns
51    gcp.gc_rowCount = 1             # number of rows to return per iteration
52    gcp.gc_datavalues = datavalues
53    gcp.gc_stmtHandle = stmtHandle
54    while True:
55        await py.IIapi_getColumns(gcp)
56        if gcp.gc_genParm.gp_status == py.IIAPI_ST_NO_DATA:
57            break
58
59        # display the result row
60        print(f'name: {name.value}, weight: {weight.value}')
61
62    # release the decriptor array allocated by the OpenAPI
63    clp = py.IIAPI_CLOSEPARM()
64    clp.cl_stmtHandle = stmtHandle
65    await py.IIapi_close(clp)
66
67    ...

Download