WHENEVER SQLERROR of postgresql causes INTO assignment to fail

240 Views Asked by At

This is a piece of ecpg code. When using fetch to traverse the data, because of the use of WHENEVER SQLERROR DO sqlerr(), if an error occurs, it will be judged by the sqlerr method that it is a 22002 error, and the error will be ignored and the traversal will continue

int main(int argc, char *argv[]) {
    ....
    EXEC SQL DECLARE mt_cur CURSOR WITH HOLD FOR  
            SELECT
                C1, C2, C3
            FROM MYTABLE,
            ORDER BY C1;
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL CLOSE mt_cur;
    EXEC SQL WHENEVER SQLERROR DO sqlerr();
    EXEC SQL OPEN mt_cur;
    EXEC SQL WHENEVER NOT FOUND DO break;

    while (1) {
        EXEC SQL FETCH mt_cur INTO :v1, :v2, :v3;
        if (v3 > 5 ) {
            continue;
        }
        printf("%d%d\n", v1, v3);
    }
    EXEC SQL CLOSE mt_cur;
    ....
    return (0);
}

void sqlerr() {
    if (strncmp(sqlca.sqlstate, "22002", 5) == 0) {
        return;
    }
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL ROLLBACK WORK;
    exit(1);
}

If the data in the table is like this

C1   C2     C3
1    0      2
2    (NULL) 8
3    (NULL) 9

The correct running result should be to print only one 12, but now it prints out 12, 22, 32

After analyzing the program, I think that when traversing the second row of data, the C2 column is NULL, which leads to entering the sqlerr method. After returning in sqlerr, INTO will not be executed to assign a value to v3, and it will enter the next traversal. At this time, the value of v3 is 2 assigned during the first traversal

Is my idea correct? If so, is there a way for postgresql to avoid this situation, I want the value of C3 to be INTO to v3 normally when C2 is NULL

0

There are 0 best solutions below