Unexpected output of embedded SQL program

Question

I'm trying to implement an absolute basic toy example using ECPG to run an embedded SQL query in C. This is my code:

int main() {

    EXEC SQL WHENEVER NOT FOUND DO BREAK;
    EXEC SQL BEGIN DECLARE SECTION;
    char v_country_iso2[2], v_name[32];
    EXEC SQL END DECLARE SECTION;
    
    // Connect to database
    EXEC SQL BEGIN DECLARE SECTION;
    const char *target = "exampledb@localhost";
    const char *user = "demo";
    const char *password = "topsecret";
    EXEC SQL END DECLARE SECTION;
    EXEC SQL CONNECT TO :target USER :user USING :password;
    
    // Declare cursor
    EXEC SQL DECLARE c CURSOR FOR
    SELECT country_iso2, name FROM cities LIMIT 3;
    
    // Open cursor
    EXEC SQL OPEN c;
    
    // Loop through cursor and display results
    for (;;) {
        EXEC SQL FETCH NEXT FROM c INTO :v_country_iso2, :v_name;
        printf(">>> Country (ISO-2): %s, City: %s\n", v_country_iso2, v_name);
    }
    
    // Clean-up (close cursor, commit, disconnect)
    EXEC SQL CLOSE c;
    EXEC SQL COMMIT;
    EXEC SQL DISCONNECT;
    
    return 0;   
}

The code compiles and runs just fine. However, the output is the following

>>> Country (ISO-2): JPTokyo, City: Tokyo
>>> Country (ISO-2): IDJakarta, City: Jakarta
>>> Country (ISO-2): INDelhi, City: Delhi

when the expected output is:

>>> Country (ISO-2): JP, City: Tokyo
>>> Country (ISO-2): ID, City: Jakarta
>>> Country (ISO-2): IN, City: Delhi

It seems that v_country_iso2 is a concatenation of the country code and the city name, and I have no idea where and why this happens. I'm sure the data is correct in the database table. Also, if I only fetch the country code with SELECT country_iso2 FROM cities LIMIT 3; then I get indeed only the country code.

I'm using a PostgreSQL database with a simple table cities with just for columns including country_iso2 and name.

I'm sure that I'm making a really stupid mistake somewhere, but just can see it. I haven't touched C/C++ in ages, but the code looks simple enough.

C strings require a 0 terminator, so char[2] will not hold a 2-character country code. — stark, Jul 03 '23 at 14:27
You can either print chars with %c or make a 3-char string and add a 0. — stark, Jul 03 '23 at 14:33
@stark Indeed, when I change the code to `v_country_iso2[32]` I get the expected results. However, I don't know why and, admittedly, I don't understand your comment. Can you elaborate on why `v_country_iso2[2]` caused the unexpected output? Please feel free to submit as answer so I can submit it. — Christian, Jul 03 '23 at 14:35

score 1 · Accepted Answer · answered Jul 03 '23 at 14:42

1

The problem is caused by printf with %s format. When you pass an unterminated string, printf keeps printing outside the bounds of the array. In your case it happens to print the following string in memory but it could just as easily crash. To fix this increase the size and after reading 2 chars from the SQL add the terminator before printing the string.

v_country_iso2[2] = '\0';

Note that you should most likely do this for city as well.

answered Jul 03 '23 at 14:42

stark

12,615
3
33
50

I think I found an alternative. Instead as a `char` I can do `VARCHAR v_country_iso2[2]` and the write `v_country_iso2.arr` in the `printf` statement. This seems to avoid any potential issues with overwriting the `\0` terminator. For example, if I do `v_country_iso2[1]` I will get indeed just the first character of the country code. – Christian Jul 03 '23 at 15:26

Unexpected output of embedded SQL program

1 Answers1