Execute a Parameterized SQL Statement

Execute a parameterized SQL statement that takes arguments.

The location of a parameter in the SQL is indicated by a “ ~V “ (i.e. a blank, followed by a tilde, followed by an uppercase V, and finally another blank):

INSERT INTO foo (name,ordernr,weight) VALUES ( ~V , ~V , ~V  )

DELETE FROM foo WHERE ordernr = ~V

See Queries and Parameters in the OpenAPI User Guide for more information. Also see Setting Up Data Buffers.

Note

There are two forms of parameterized SQL. The other form, not shown here, is used with the PREPARE statement and uses a question-mark for its parameter marker.

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_parameters must be set True. Failing to set it will cause IIAPI_ST_FAILURE and E_AP0006 API function cannot be called in the current state when the application attempts to start sending arguments by calling IIapi_setDescriptor().

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 parameters but returns no rows'''
 11
 12    # encode the query so the DBMS can understand it; note the 
 13    # blank before AND after every ~V marker
 14    query = "INSERT INTO foo VALUES ( ~V , ~V , ~V )" 
 15    queryText = query.encode()
 16
 17    # set up the query parameter block; set connHandle and tranHandle
 18    qyp = py.IIAPI_QUERYPARM()
 19    qyp.qy_connHandle = connHandle
 20    qyp.qy_queryType = py.IIAPI_QT_QUERY    # indicate query execution
 21    qyp.qy_queryText = queryText
 22    qyp.qy_parameters = True                # indicate arguments will be sent
 23
 24    # send the SQL to the DBMS
 25    await py.IIapi_query(qyp)
 26    # errors sending the SQL to the server are reported here
 27    status = qyp.qy_genParm.gp_status
 28    if not status == py.IIAPI_ST_SUCCESS:
 29        print(f'IIapi_query() error, {status=}')
 30        quit()
 31
 32    tranHandle = qyp.qy_tranHandle
 33    stmtHandle = qyp.qy_stmtHandle
 34
 35    # set up the data buffers for the arguments
 36    arg_count = 3
 37    name = ctypes.c_buffer(25)
 38    name.value = b"Treefrog Brewing Co"
 39    ordernr = ctypes.c_int(71025)
 40    weight = ctypes.c_double(16.18)
 41
 42    # set up the array of argument descriptors; note that ds_columnType
 43    # is set to py.IIAPI_COL_QPARM to indicate a query parameter
 44    descriptors = (py.IIAPI_DESCRIPTOR * arg_count)()
 45    descriptors[0].ds_dataType = py.IIAPI_CHA_TYPE
 46    descriptors[0].ds_length = len(name)
 47    descriptors[0].ds_nullable = True
 48    descriptors[0].ds_columnType = py.IIAPI_COL_QPARM
 49    descriptors[1].ds_dataType = py.IIAPI_INT_TYPE
 50    descriptors[1].ds_length = py.IIAPI_I4_LEN
 51    descriptors[1].ds_nullable = True
 52    descriptors[1].ds_columnType = py.IIAPI_COL_QPARM
 53    descriptors[2].ds_dataType = py.IIAPI_FLT_TYPE
 54    descriptors[2].ds_length = py.IIAPI_F8_LEN
 55    descriptors[2].ds_nullable = True
 56    descriptors[2].ds_columnType = py.IIAPI_COL_QPARM
 57
 58    # send the description of the arguments
 59    sdp = py.IIAPI_SETDESCRPARM()
 60    sdp.sd_stmtHandle = stmtHandle
 61    sdp.sd_descriptorCount = arg_count
 62    sdp.sd_descriptor = descriptors
 63    await py.IIapi_setDescriptor(sdp)
 64    status = sdp.sd_genParm.gp_status
 65    if not status == py.IIAPI_ST_SUCCESS:
 66        print(f'IIapi_setDescriptor() error, {status=}')
 67        quit()
 68
 69    # set up the array of argument values
 70    arguments = (py.IIAPI_DATAVALUE * arg_count)()
 71    arguments[0].dv_length = len(name.value)
 72    arguments[0].dv_value = ctypes.addressof(name)
 73    arguments[1].dv_length = py.IIAPI_I4_LEN
 74    arguments[1].dv_value = ctypes.addressof(ordernr)
 75    arguments[2].dv_length = py.IIAPI_F8_LEN
 76    arguments[2].dv_value = ctypes.addressof(weight)
 77
 78    # send the arguments to substitute for the placeholders
 79    ppp = py.IIAPI_PUTPARMPARM()
 80    ppp.pp_stmtHandle = stmtHandle
 81    ppp.pp_parmCount = arg_count
 82    ppp.pp_parmData = arguments
 83    await py.IIapi_putParms(ppp)
 84    status = ppp.pp_genParm.gp_status
 85    if not status == py.IIAPI_ST_SUCCESS:
 86        print(f'IIapi_putParms() error, {status=}')
 87        quit()
 88
 89    # get the query status; SQL syntax errors and references to
 90    # non-existent database assets are reported here
 91    gqp = py.IIAPI_GETQINFOPARM()
 92    gqp.gq_stmtHandle = stmtHandle
 93    await py.IIapi_getQueryInfo(gqp)
 94    status = gqp.gq_genParm.gp_status
 95    if not status == py.IIAPI_ST_SUCCESS:
 96        print(f'IIapi_getQueryInfo() error, {status=}')
 97        quit()
 98
 99    # close the query to free the statement handle
100    clp = py.IIAPI_CLOSEPARM()
101    clp.cl_stmtHandle = stmtHandle
102    await py.IIapi_close(clp)
103    status = clp.cl_genParm.gp_status
104    if not status == py.IIAPI_ST_SUCCESS:
105        print(f'IIapi_close() error, {status=}')
106        quit()
107
108...

Download