Multiple-row FETCH and Host Array Structure

[rynspate]

Good afternoon all,

I am attempting to capture the final lines of a joblog, using embedded SQL, that would be tied to an inquiry message that is sent to QSYSOPR. IBM has the ability to pushing multiple rows of output into an array, however, I'm not having too much success with it. https://www.ibm.com/docs/en/i/7.1?to...tructure-array

I've opened a ticket with IBM, and though the program compiles, it doesn't return anything as expected.

int main(){

    char subject[20] = "MESSAGE_WAITING";
    char data1[1024] = "";
    char data2[4096] = "";
    char data3[50] = "";
    char data4[1024] = "";
    char message[1024] = "";
    char cause[4096] = "";

    EXEC SQL BEGIN DECLARE SECTION;
        char job[27] = "476214/RSPEIGHT/T02C003001";
        typedef _Packed struct {
            short VARClen;
            char VARCdat[1024];
        } VARC;
        VARC msg_txt;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL DECLARE jobcr CURSOR FOR
    SELECT CAST(MESSAGE_TEXT AS VARCHAR(1024))
    FROM TABLE(QSYS2.JOBLOG_INFO(:job))
    ORDER BY MESSAGE_TIMESTAMP DESC;

    EXEC SQL OPEN jobcr;

    EXEC SQL WHENEVER NOT FOUND GOTO end_fetch;


    EXEC SQL FETCH jobcr INTO :msg_txt;
    end_fetch:

    EXEC SQL CLOSE jobcr;

    printf("\n\njoblog: %s\n\n", msg_txt);

};

I don't get an error, but the printf statement at the end comes up blank.

[JonBoy]

Are you sure that it is finding the job? The way you have it coded it looks to me as if you are not doing anything different on a not found condition. It will hit the same printf either way. Have you looked at the job log?

[Barbara Morris]

I don't think you can printf msg_txt as %s. As far as I know, %s only works with null-terminated strings. but msg_txt is a structure.

Try

printf("\n\njoblog: %*.*s\n\n",
      msg_txt.VARClen, msg_txt.VARClen, msg_txt.VARCdat);

[rynspate]

Barbara, this helped out quite a bit. Thank you for reminding me. I changed my printf statement to

 ("printf("\n\njoblog: %s\n\n", msg_txt.VARCdat);

and it's returning the very last line of the joblog.

[B.Hauser]

You need to include the SQL Communications Area (SQLCA) in your source code and check the SQLCODE or SQLSTATE after each SQL Statement, otherwise you will not get any information about an error.
SQLCODE < 0 is an Error or if you prefer the SQLSTATE if the first 2 positions of the SQLSTATE are neither 00, 01, 02 then you have an error.

https://www.ibm.com/docs/en/i/7.4?topic=cssicca-defining-sql-communication-area-in-c-c-applications-that-use-sql

BTW what you try here is not a multiple row fetch, but a single row fetch because you read only a single row and do not output a block of rows into an array data structure.

Also IMHO the WHENEVER Statement is not necessary, since you just open the cursor, fetch a single row/information and the close the cursor again.
There is no need if found or not. If no row is returned the host variable stays untouched.

[rynspate]

B.Hauser, thanks for the input. I agree that this isn't a multiple row fetch, though it should be. The original code example given by IBM is below:

// Declaring host variables 
EXEC SQL BEGIN DECLARE SECTION;
    char   empno[100][8];
    char   lastname[100][15];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE empcr CURSOR FOR
	SELECT empno, lastname FROM employee;

EXEC SQL OPEN empcr;

EXEC SQL WHENEVER NOT FOUND GOTO end_fetch;

while (1) {
	EXEC SQL FETCH empcr INTO :empno :lastname;  /* bulk fetch       */
	...                                          /* 100 or less rows */
	...
}
end_fetch:
EXEC SQL CLOSE empcr;

As you can see, the host variable they declared is a simple multidimensional array, which I've worked with and understand. But when I attempted to get something similar to work in our environment, it points to the multidimensional array as the problem. So, IBM sent a suggested code to help get around it.

With how my struct is right now, I can only put one line of returned text at a time. So, I'm still a bit in the dark about how to get this to work.

I attempted to work create a multidimensional struct array with the below:

typedef _Packed struct {
          short VARClen;
          char VARCdat[1024];
        } VARC;
        VARC msg_txt[5];

but the compiler specifically throws an error "Variable msg_txt not defined or not usable. Reason: The variable is a dimensioned array".

I attempted a while loop which works, in that it pushes one line of text into msg_txt.VARCdat, but only one at a time.

So, I'm still at a bit of a lose on how to implement the multi row fetch.