Preparing and executing SQL statements with variable input in Python

To prepare and execute an SQL statement that includes variable input, use the ibm_db.prepare, ibm_db.bind_param, and ibm_db.execute functions. Preparing a statement improves performance because the database server creates an optimized access plan for data retrieval that it can reuse if the statement is executed again.

To prepare and execute an SQL statement that includes parameter markers:

1. Call the ibm_db.prepare function, passing the listed arguments:

   connection

   A valid database connection resource that is returned from the ibm_db.connect or ibm_db.pconnect function.

   statement

   A string that contains the SQL statement, including question marks (?) as parameter markers for column or predicate values that require variable input. This string can include an XQuery expression that is called by the XMLQUERY function.

   options

   Optional: A dictionary that specifies the type of cursor to return for result sets. You can use this parameter to request a scrollable cursor for database servers that support this type of cursor. By default, a forward-only cursor is returned.

   If the function call succeeds, it returns a statement handle resource that you can use in subsequent function calls that are related to the query.

   If the function call fails (returns False), you can use the ibm_db.stmt_error or ibm_db.stmt_errormsg function to retrieve diagnostic information about the error.

2. Optional: For each parameter marker in the SQL string, call the ibm_db.bind_param function, passing the listed arguments. Binding input values to parameter markers ensures that each input value is treated as a single parameter, which prevents SQL injection attacks.

   stmt

   The prepared statement that is returned by the call to the ibm_db.prepare function.

   parameter-number

   An integer that represents the position of the parameter marker in the SQL statement.

   variable

   The value to use in place of the parameter marker.

3. Call the ibm_db.execute function, passing the listed arguments:

   stmt

   A prepared statement that is returned from ibm_db.prepare.

   parameters

   A tuple of input parameters that match parameter markers that are contained in the prepared statement.

   For more information about the ibm_db API, see https://github.com/ibmdb/python-ibmdb/wiki/APIs.

import ibm_db
conn = ibm_db.connect("database","username","password")
sql = "SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE EMPNO > ? AND EMPNO < ?"
stmt = ibm_db.prepare(conn, sql)
max = 50
min = 0
# Explicitly bind parameters
ibm_db.bind_param(stmt, 1, min)
ibm_db.bind_param(stmt, 2, max)
ibm_db.execute(stmt)
# Process results

# Invoke prepared statement again using dynamically bound parameters
param = max, min, 
ibm_db.execute(stmt, param)