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

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:

Syntax Syntax

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])

End of the code.

Execute the following SQL statement:
Syntax
1. ```
cursor = session.sql ('SELECT cno FROM customer1 ORDER BY cno')
```
End of the code.
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:
1. Next data record (first data record in the result set):
  Syntax
  1. print cursor.next ()
  2. (1,)
  End of the code.
2. Next data record:
  Syntax
  1. print cursor.next ()
  2. (2,)
  End of the code.
3. Previous data record:
  Syntax
  1. print cursor.previous ()
  2. (1,)
  End of the code.
4. Five data records forwards:
  Syntax
  1. print cursor.relative (5)
  2. (6,)
  End of the code.
5. One data record back:
  Syntax
  1. print cursor.relative (-1)
  2. (5,)
  End of the code.
6. Third data record:
  Syntax
  1. print cursor.absolute (3)
  2. (3,)
  End of the code.
7. Third-to-last data record:
  Syntax
  1. print cursor.absolute (-3)
  2. (8,)
  End of the code.
8. First data record:
  Syntax
  1. print cursor.first ()
  2. (1,)
  End of the code.
9. Last data record:
  Syntax
  1. print cursor.last ()
  2. (10,)
  End of the code.
10. Current data record:
  Syntax
  1. print cursor.current ()
  2. (10,)
  End of the code.
Iterate through the entire result set and display all data records:
Syntax
1. ```
for row in cursor: print row
```
2. ```
(1,)
(2,)
(3,)
(4,)
(5,)
(6,)
(7,)
(8,)
(9,)
(10,)
```
End of the code.