usage of PREPARE in SPL
According to the manuals, IDS 11.5 allows you to use the PREPARE statement in SPL (as well as ESQL/C). However, the same manual states that the EXECUTE statement is only available in ESQL/C. What is the point of PREPAREing a statement if you're not going to then EXECUTE it? What value is there to this? On the chance that the manuals just had not been updated for the EXECUTE statement, I did try to PREPARE and EXECUTE within SPL. The PREPARE works fine, but I get a syntax error on the EXECUTE. I tried several variants of this. Yes, I know there is the EXECUTE IMMEDIATE statement. The problem with that is that it does not allow SELECT (unless it is a SELECT ... INTO TEMP). So, if I want to PREPARE a SELECT that will be EXECUTEd repeatedly with different values in the WHERE clause, EXECUTE IMMEDIATE is not an option. Also, these are singleton SELECTs, so it's a lot of extra work to try declare/open/fetch/close a cursor.
No choice. You'll have to prepare/declare/open/fetch/close. If you want to make complex routines look neater, wrap the select in a function and execute the function in the parent. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jul 15, 2009 at 5:13 PM, MARK COLLINS <markc@myfastmail.com> wrote: > According to the manuals, IDS 11.5 allows you to use the PREPARE statement > in > SPL (as well as ESQL/C). However, the same manual states that the EXECUTE > statement is only available in ESQL/C. > > What is the point of PREPAREing a statement if you're not going to then > EXECUTE it? What value is there to this? > > On the chance that the manuals just had not been updated for the EXECUTE > statement, I did try to PREPARE and EXECUTE within SPL. The PREPARE works > fine, but I get a syntax error on the EXECUTE. I tried several variants of > this. > > Yes, I know there is the EXECUTE IMMEDIATE statement. The problem with that > is > that it does not allow SELECT (unless it is a SELECT ... INTO TEMP). So, if > I > want to PREPARE a SELECT that will be EXECUTEd repeatedly with different > values in the WHERE clause, EXECUTE IMMEDIATE is not an option. Also, these > are singleton SELECTs, so it's a lot of extra work to try > declare/open/fetch/close a cursor. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016368e2bc9b0ea4b046ed39e24
Art, Thanks. I worked it out by a different approach. But I'm still trying to figure out what is the value of having a PREPARE statement available in SPL?
So that you can construct an SQL statement on the fly in an SPL routine, PREPARE it, DECLARE a cursor against it, OPEN the cursor, and FETCH the returned result set either with a singleton FETCH or within a loop. I built an entire dynamic WEB application (OK I didn't do any of the WEB coding) around that dynamic capability in 11.50. It created complex SQL on the fly from user input into a WEB form within a stored procedure, stored the SQL in a database table, and other functions could retrieve the SQL and use it to retrieve data and present it to the users. All connection-less from the users' point of view. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jul 21, 2009 at 3:54 PM, MARK COLLINS <markc@myfastmail.com> wrote: > Art, > > Thanks. > > I worked it out by a different approach. But I'm still trying to figure out > what is the value of having a PREPARE statement available in SPL? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5a9ff3d0991046f3dccdf
Ah, now I've got it. Thanks. I've just been using FOREACH, but now I see that it doesn't allow for dynamic SQL statements.