Example: Executing an SQL Statement and Navigating in the Result Set

Context

In this example, you use methods of the SapDB_ResultSet class from the sdb.sql module. You create a table, fill it with values, execute an SQL statement, and position the cursor at different places in the result set.

Procedure

Create a new table customer1 and fill it with data:

						
session.sql ('CREATE TABLE customer1 (cno FIXED(4) PRIMARY KEY)')
insert = session.prepare ('INSERT INTO customer1 (cno) VALUES (?)')
for i in xrange (1, 11):
  insert.execute ([i])

Execute the following SQL statement:
```
						
cursor = session.sql ('SELECT cno FROM customer1 ORDER BY cno')
						
					
```
The SQL statement returns a result set. The cursor is positioned before the first data record of the result set.

Position the cursor at various places in the result set and display the respective data record:

Next data record (first data record in the result set):

								
print cursor.next ()
								
(1,)

Next data record:

								
print cursor.next ()
								
(2,)

Previous data record:

								
print cursor.previous ()
								
(1,)

Five data records forwards:

								
print cursor.relative (5)
								
(6,)

One data record back:

								
print cursor.relative (-1)
								
(5,)

Third data record:

								
print cursor.absolute (3)
								
(3,)

Third-to-last data record:

								
print cursor.absolute (-3)
								
(8,)

First data record:

								
print cursor.first ()
								
(1,)

Last data record:

								
print cursor.last ()
								
(10,)

Current data record:

								
print cursor.current ()
								
(10,)

Iterate through the entire result set and display all data records:

						
for row in cursor: print row
						
(1,)
(2,)
(3,)
(4,)
(5,)
(6,)
(7,)
(8,)
(9,)
(10,)