Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ Application Development Guide/ ECPG-based Development/ Running SQL Commands/ Using Cursors
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Using Cursors

To retrieve a result set holding multiple rows, an application has to declare a cursor and fetch each row from the cursor.

  1. Declare a cursor. 

    EXEC SQL DECLARE c CURSOR FOR select * from tb1;

  2. Open a cursor. 

    EXEC SQL OPEN c;

  3. Fetch a row of data from a cursor. 

    EXEC SQL FETCH 1 in c into :a, :str;

  4. Close a cursor. 

    EXEC SQL CLOSE c;

For details about how to use cursors, see DECLARE. For details about the FETCH command, see FETCH.

A complete example is as follows: 
#include <string.h>
#include <stdlib.h>

int main(void)
{
exec sql begin declare section;
    int *a = NULL;
    char *str = NULL;
exec sql end declare section;

    int count = 0;
    exec sql connect to postgres ;
    exec sql set autocommit to off;
    exec sql begin;
    exec sql drop table if exists tb1;
    exec sql create table tb1(id int, info text);
    exec sql insert into tb1 (id, info) select generate_series(1, 100000), 'test';
    exec sql select count(*) into :a from tb1;
    printf ("a is %d\n", *a);
    exec sql commit;

    // Define a cursor.
    exec sql declare c cursor for select * from tb1;
    // Open the cursor.
    exec sql open c;
    exec sql whenever not found do break;
    while(1) {
        // Capture data.
        exec sql fetch 1 in c into :a, :str;
        count++;
        if (count == 100000) {
            printf("Fetch res: a is %d, str is %s", *a, str);
        }
    }
    // Close the cursor.
    exec sql close c;
    exec sql set autocommit to on;
    exec sql drop table tb1;
    exec sql disconnect;

    ECPGfree_auto_mem();
    return 0;

}
Parent topic: Running SQL Commands