After executing a statement that
returns one or more result sets, use one of the functions available
in the ibm_db API to iterate through the returned rows.
If
your result set includes columns that contain large data (such as
BLOB or CLOB data), you can retrieve the data on a column-by-column
basis to avoid large memory usage.
Before you begin
You
must have a statement resource that is returned by either the ibm_db.exec_immediate or ibm_db.execute function
that has one or more associated result sets.
Procedure
To fetch data from a result set:
- Fetch data from a result set by calling one of the fetch
functions.
Table 1. ibm_db fetch
functionsFunction |
Description |
ibm_db.fetch_tuple |
Returns a tuple, which is indexed by
column position, representing a row in a result set. The columns are 0-indexed. |
ibm_db.fetch_assoc |
Returns a dictionary, which is indexed
by column name, representing a row in a result set. |
ibm_db.fetch_both |
Returns a dictionary, which is indexed
by both column name and position, representing a row in a result set. |
ibm_db.fetch_row |
Sets the result set pointer to the
next row or requested row. Use this function to iterate through a
result set. |
These functions accept the listed arguments:
- stmt
- A valid statement resource.
- row_number
- The number of the row that you want to retrieve from the result
set. Specify a value for this parameter if you requested a scrollable
cursor when you called the ibm_db.exec_immediate or ibm_db.prepare function.
With the default forward-only cursor, each call to a fetch method
returns the next row in the result set.
- Optional: If you called the ibm_db.fetch_row function,
for each iteration through the result set, retrieve a value from a
specified column by calling the ibm_db.result function. You
can specify the column by passing either an integer that represents
the position of the column in the row (starting with 0) or a string
that represents the name of the column.
- Continue fetching rows until the fetch method returns False,
which indicates that you have reached the end of the result set.
For more information about the ibm_db API, see http://code.google.com/p/ibm-db/wiki/APIs.
Example
Example 1: Fetch rows from a result set by calling
the ibm_db.fetch_both function
import ibm_db
conn = ibm_db.connect("database","username","password")
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
print "The ID is : ", dictionary["EMPNO"]
print "The Name is : ", dictionary[1]
dictionary = ibm_db.fetch_both(stmt)
Example 2: Fetch
rows from a result set by calling the ibm_db.fetch_tuple function
import ibm_db
conn = ibm_db.connect("database","username","password")
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
tuple = ibm_db.fetch_tuple(stmt)
while tuple != False:
print "The ID is : ", tuple[0]
print "The name is : ", tuple[1]
tuple = ibm_db.fetch_tuple(stmt)
Example 3: Fetch
rows from a result set by calling the ibm_db.fetch_assoc function
import ibm_db
conn = ibm_db.connect("database","username","password")
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_assoc(stmt)
while dictionary != False:
print "The ID is : ", dictionary["EMPNO"]
print "The name is : ", dictionary["FIRSTNME"]
dictionary = ibm_db.fetch_assoc(stmt)
Example 4: Fetch columns from a result set
import ibm_db
conn = ibm_db.connect("database","username","password")
sql = "SELECT * FROM EMPLOYEE"
stmt = ibm_db.exec_immediate(conn, sql)
while ibm_db.fetch_row(stmt) != False:
print "The Employee number is : ", ibm_db.result(stmt, 0)
print "The last name is : ", ibm_db.result(stmt, "LASTNAME")
What to do next
When
you are ready to close the connection to the database, call the
ibm_db.close function.
If you attempt to close a persistent connection that you created with
ibm_db.pconnect,
the close request returns True, and the connection remains available
for the next caller.