Procedure documentationExample: Executing an SQL Statement and Navigating in the Result Set Locate this document in the navigation structure

 

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

  1. Create a new table customer1 and fill it with data:

    Syntax Syntax

    1. 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.
  2. Execute the following SQL statement:

    Syntax 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.

  3. 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 Syntax

      1. print cursor.next ()
      2. (1,)
      End of the code.
    2. Next data record:

      Syntax Syntax

      1. print cursor.next ()
      2. (2,)
      End of the code.
    3. Previous data record:

      Syntax Syntax

      1. print cursor.previous ()
      2. (1,)
      End of the code.
    4. Five data records forwards:

      Syntax Syntax

      1. print cursor.relative (5)
      2. (6,)
      End of the code.
    5. One data record back:

      Syntax Syntax

      1. print cursor.relative (-1)
      2. (5,)
      End of the code.
    6. Third data record:

      Syntax Syntax

      1. print cursor.absolute (3)
      2. (3,)
      End of the code.
    7. Third-to-last data record:

      Syntax Syntax

      1. print cursor.absolute (-3)
      2. (8,)
      End of the code.
    8. First data record:

      Syntax Syntax

      1. print cursor.first ()
      2. (1,)
      End of the code.
    9. Last data record:

      Syntax Syntax

      1. print cursor.last ()
      2. (10,)
      End of the code.
    10. Current data record:

      Syntax Syntax

      1. print cursor.current ()
      2. (10,)
      End of the code.
  4. Iterate through the entire result set and display all data records:

    Syntax Syntax

    1. for row in cursor: print row
    2. (1,)
      (2,)
      (3,)
      (4,)
      (5,)
      (6,)
      (7,)
      (8,)
      (9,)
      (10,)
    End of the code.