Help Center/ GaussDB/ Developer Guide(Centralized_8.x)/ Application Development Guide/ ECPG-based Development/ Querying the Result Set
Updated on 2024-08-20 GMT+08:00
View PDF
Share

Querying the Result Set

  • The SELECT statement that returns the result of a single row can be directly executed using EXEC SQL. For details, see Executing SQL Statements.

    Example:

    /* Create a table and insert data. */
EXEC SQL CREATE TABLE test_table (number1 integer, number2 integer);
EXEC SQL INSERT INTO test_table (number1, number2) VALUES (2, 1);

/* The query result is in a single row. :num is the host variable. */
EXEC SQL SELECT number1 INTO :num FROM test_table WHERE number2 = 1;
  • To process a multi-row result set, you must use a cursor. For details, see Using Cursors. (In special cases, an application can fetch multiple rows of results at a time and write them to the host variable of the array type. For details, see Host Variables with Non-primitive Types.)
    Example: 
        /* Create a table and insert data. */
    EXEC SQL CREATE TABLE test_table (number1 integer, number2 integer);
    EXEC SQL INSERT INTO test_table (number1, number2) VALUES (2, 1);
    EXEC SQL INSERT INTO test_table (number1, number2) VALUES (3, 1);
    EXEC SQL INSERT INTO test_table (number1, number2) VALUES (4,1);
    EXEC SQL INSERT INTO test_table (number1, number2) VALUES (5, 1);

/* Define the host variable. */
EXEC SQL BEGIN DECLARE SECTION;
    int v1;
    int v2;
EXEC SQL END DECLARE SECTION;

    /* Declare a cursor. */
    EXEC SQL DECLARE test_bar CURSOR FOR SELECT number1, number2 FROM test_table ORDER BY number1;
    /* Open the cursor. */
    EXEC SQL OPEN test_bar;
    /* When the cursor reaches the end of the result set, exit the loop. */
    EXEC SQL WHENEVER NOT FOUND DO BREAK;
    /* Obtain the query result set. */
    while(1)
    {
        EXEC SQL FETCH NEXT FROM test_bar INTO :v1, :v2;
        printf("number1 = %d, number2 = %d\n",v1,v2);
    }
    /* Close the cursor. */
    EXEC SQL CLOSE test_bar;
Parent topic: ECPG-based Development