EXEC SQL Statement - How to use?

[Tobias Hammerschmidt]

Hello,

i want to figure out if the use of ECEX SQL is an alternative to our actual environment (standard use of blockfetch with restict and row selection).

is exec sql a real alternaitve to a blockfetch and how would it work?

we have to use the exec sql in a server function and in my understanding the exec sql returns all rows corresponding to the sql statement. these rows we have to fetch (fetchnext) and set into output variable. how is the handling if the number of rows exceed the number of occurrence in the variable? in my understanding we can not call the server function again because there is no "position" (=no ; 2nd Call to BlickFetch) and the sql statement returns at 2nd call the same rows...

or am i totally wrong?

best regards,
tobias

[HIFUMI ONISHI]

Hello Tobias,

If you want to use the Exec SQL statement in a BlockFetch function that inherits from Storage/RelationalTable.Fetch.BlockFetch, you should add some logic as shown in the attached image.
You can get the result you want by setting "No" to Control <Position> of the Input parameter, when calling the created BlockFetch function from the second time or later.

Regards,
Hifumi

[Tobias Hammerschmidt]

Hello Hifumi,

thank you for this code snippet.

This works.

i will test performance and functionality.

best regards, tobias

[Jorge Ubeda]

Cursor will maintain the full select output, and fetch next will process a block since last read row. Each loop fetchnext will get a new row, until the last one of the selection.

[Jorge Ubeda]

Hi Tobias,
it was a topic yesterday at the conference. Maybe you have seen it?.
By the way, which platform are you using? Discussed solution is a generic for Windows/other than rpg (anyway used there by means of odbc/jdbc). And there are another one for use with rpgi. Indeed these are more flexible and efficient.

[Jorge Ubeda]

use of cursor. You will get next block with fetchnext.

[Jorge Ubeda]

Curious...these three messages were posted a week ago, but never shown. Finally, chatted by email

------------------------------
Developer
Nahitek Digital/Amatech
------------------------------