Execute an SQL Statement (No Parameters, No Result Rows)

Execute an SQL statement that takes no parameters and returns no rows. For example SQL to CREATE or DROP a table, view, or index. Or to SET an isolation level or other session attribute.

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.

Download

 1import asyncio
 2import pyngres.asyncio as py
 3...
 4
 5# assume a session was started and connHandle is set. Also assume
 6# a transaction is already in progress and tranHandle has a value, or
 7# tranHandle is set to None, indicating the start of a new transaction
 8
 9async def exec_SQL():
10    '''execute an SQL statement with no parameters and returns no rows'''
11
12    # encode the query so the DBMS can understand it
13    query = (
14        "CREATE TABLE foo " 
15        "( name char(25), shipments integer, weight float )" )
16    queryText = query.encode()
17
18    # set up the parameter block; set connHandle and tranHandle
19    qyp = py.IIAPI_QUERYPARM()
20    qyp.qy_connHandle = connHandle
21    qyp.qy_queryType = py.IIAPI_QT_QUERY    # indicate query execution
22    qyp.qy_queryText = queryText
23    qyp.qy_tranHandle = tranHandle
24
25    # send the SQL to the DBMS
26    await py.IIapi_query(qyp)
27    # errors sending the SQL to the server are reported here
28    status = qyp.qy_genParm.gp_status
29    if not status == py.IIAPI_ST_SUCCESS:
30        print(f'IIapi_query() error, {status=}')
31        quit()
32
33    tranHandle = qyp.qy_tranHandle
34    stmtHandle = qyp.qy_stmtHandle
35
36    # get the query status; syntax errors and references to
37    # non-existent database assets are reported here
38    gqp = py.IIAPI_GETQINFOPARM()
39    gqp.gq_stmtHandle = stmtHandle
40    await py.IIapi_getQueryInfo(gqp)
41    status = gqp.gq_genParm.gp_status
42    if not status == py.IIAPI_ST_SUCCESS:
43        print(f'IIapi_getQueryInfo() error, {status=}')
44        quit()
45
46    # close the query to free the statement handle
47    clp = py.IIAPI_CLOSEPARM()
48    clp.cl_stmtHandle = stmtHandle
49    await py.IIapi_close(clp)
50    status = clp.cl_genParm.gp_status
51    if not status == py.IIAPI_ST_SUCCESS:
52        print(f'IIapi_close() error, {status=}')
53        quit()
54
55...

Download