SPL SQL statments with variable define table names
The poster asked whether an SPL stored procedure can prepare SQL using a table name held in a variable. Replies: IDS 11.50 supports true dynamic SQL (PREPARE/DECLARE/OPEN/FETCH) inside SPL; from about 9.30 onward the Exec DataBlade gives similar capability via UDR calls, albeit with awkward error handling. On older releases (7.x, SE/OnLine 5.x) it isn't possible — the alternatives are generating a new procedure from the table name, or using SPL's SYSTEM verb to run an external 4GL program (clumsy, needs a table to pass data back). Running IDS 7 and 10, the poster instead chose a nightly 4GL batch job that pre-summarises data for the reporting tool.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is there a way to prepare an sql for a stored procedure with a table name that is stored in a variable?
IDS 11.50 supports Dynamic SQL with in the Informix SPL where you can construct the SQL statement and prepare it. For more details please refer: http://www.ibm.com/developerworks/data/library/techarticle/dm-0806mottupalli/ind ex.html Thanks ! Regards, Srini "THEUNS VAN ONSELEN" <vanons_t@mtn.co. To za> ids@iiug.org Sent by: cc ids-bounces@iiug. org Subject SPL SQL statments with variable define table names [14445] 06/01/2009 14:41 Please respond to ids@iiug.org Is there a way to prepare an sql for a stored procedure with a table name that is stored in a variable? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Srinivasan, but are only now starting to upgrade from ver 7 to ver 10... So there is no other way except to have the store procedure call a 4gl program to do this? Anybody?
SPL can not call a 4GL program. With EXEC DATABLADE and UDRs, it should be possible to have some level of Dynamic SQL. Refer: http://www.ibm.com/developerworks/db2/zones/informix/library/demo/ids_exec.html? S_TACT=105AGX11&S_CMP=ART Srini "THEUNS VAN ONSELEN" <vanons_t@mtn.co. To za> ids@iiug.org Sent by: cc ids-bounces@iiug. org Subject Re: SPL SQL statments with variable define table n [14447] 06/01/2009 15:25 Please respond to ids@iiug.org Thanks Srinivasan, but are only now starting to upgrade from ver 7 to ver 10... So there is no other way except to have the store procedure call a 4gl program to do this? Anybody? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
We have use a lot of stored procedures that calls 4gl programs on our production machines, but it does have high processing costs... will have a look at the link, thanks S :)
If you have IDS v11.50 you CAN use dynamic SQL in SPL procedures including PREPARE, DECLARE ... CURSOR, OPEN, FETCH, CLOSE. If you are using IDS v9.30 and later you could install the Exec Datablade which will provide the same capabilities, though the error handling is a bit awkward, using function calls to the Datablade routines to prepare statements, execute the statement and retrieve the rows that cursor will produce. If you are using any earlier release (don't know if the Exec Datablade will work with earlier 9.xx releases actually) like SE 5.xx or 7.xx, OL 5.xx, or IDS 7.xx (or DS 6.01 for that matter) you cannot do this at all. Then the only solution is to create a procedure that takes the table name and writes a new procedure. Much of the verbiage above could have been saved if you had specified your version and platform information - always a good idea when posting. Art On Tue, Jan 6, 2009 at 4:11 AM, THEUNS VAN ONSELEN <vanons_t@mtn.co.za>wrote: > Is there a way to prepare an sql for a stored procedure with a table name > that > is stored in a variable? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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.
Of course SPL can call a 4GL program, if what the OP meant was to use the SPL SYSTEM verb to execute an external program written in 4GL to accomplish the operation. It's doable, just awkward at best, especially if you want data back in your current session. You'd have to use a premanent table for communication between the external app and the current session tagging the rows somehow to allow for multiple copies of the application. Art On Tue, Jan 6, 2009 at 5:53 AM, Srinivasan R Mottupalli < mrsrinivas@in.ibm.com> wrote: > SPL can not call a 4GL program. > > With EXEC DATABLADE and UDRs, it should be possible to have some level of > Dynamic SQL. > Refer: > > > http://www.ibm.com/developerworks/db2/zones/informix/library/demo/ids_exec.html? S_TACT=105AGX11&S_CMP=ART > > Srini > > "THEUNS VAN > > ONSELEN" > > <vanons_t@mtn.co. To > > za> ids@iiug.org > > Sent by: cc > > ids-bounces@iiug. > > org Subject > > Re: SPL SQL statments with variable > > define table n [14447] > > 06/01/2009 15:25 > > Please respond to > > ids@iiug.org > > Thanks Srinivasan, but are only now starting to upgrade from ver 7 to ver > 10... > > So there is no other way except to have the store procedure call a 4gl > program > to do this? > > Anybody? > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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.
I didn't think of SYSTEM statement that can directly execute an O/S executable file. It sounded more like calling 4GL functions from with in SPL which I meant wasn't possible. Thanks for that correction in my statement ! Regards, Srini "Art Kagel" <art.kagel@gmail. com> To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject Re: SPL SQL statments with variable 06/01/2009 22:21 define table n [14457] Please respond to ids@iiug.org Of course SPL can call a 4GL program, if what the OP meant was to use the SPL SYSTEM verb to execute an external program written in 4GL to accomplish the operation. It's doable, just awkward at best, especially if you want data back in your current session. You'd have to use a premanent table for communication between the external app and the current session tagging the rows somehow to allow for multiple copies of the application. Art On Tue, Jan 6, 2009 at 5:53 AM, Srinivasan R Mottupalli < mrsrinivas@in.ibm.com> wrote: > SPL can not call a 4GL program. > > With EXEC DATABLADE and UDRs, it should be possible to have some level of > Dynamic SQL. > Refer: > > > http://www.ibm.com/developerworks/db2/zones/informix/library/demo/ids_exec.html? S_TACT=105AGX11&S_CMP=ART > > Srini > > "THEUNS VAN > > ONSELEN" > > <vanons_t@mtn.co. To > > za> ids@iiug.org > > Sent by: cc > > ids-bounces@iiug. > > org Subject > > Re: SPL SQL statments with variable > > define table n [14447] > > 06/01/2009 15:25 > > Please respond to > > ids@iiug.org > > Thanks Srinivasan, but are only now starting to upgrade from ver 7 to ver > 10... > > So there is no other way except to have the store procedure call a 4gl > program > to do this? > > Anybody? > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
FWIW - I did look into the option of using a subset of 4GL to generate SPL via the Aubit4GL parser (I even went as far as discussing it with Jonathan Leffler at the UK IIUG meeting recently) That way you only need to know one programming language and can use a lot of the 4gl syntax like DEFINE ... RECORD LIKE etc. Anyone fancy sponsoring some POC development ? On Tuesday 06 January 2009 16:51:36 Art Kagel wrote: > Of course SPL can call a 4GL program, if what the OP meant was to use the > SPL SYSTEM verb to execute an external program written in 4GL to accomplish > the operation. It's doable, just awkward at best, especially if you want > data back in your current session. You'd have to use a premanent table for > communication between the external app and the current session tagging the > rows somehow to allow for multiple copies of the application. > > Art > > On Tue, Jan 6, 2009 at 5:53 AM, Srinivasan R Mottupalli < > > mrsrinivas@in.ibm.com> wrote: > > SPL can not call a 4GL program. > > > > With EXEC DATABLADE and UDRs, it should be possible to have some level of > > Dynamic SQL. > > Refer: > > http://www.ibm.com/developerworks/db2/zones/informix/library/demo/ids_exec. >html?S_TACT=105AGX11&S_CMP=ART > > > Srini > > > > "THEUNS VAN > > > > ONSELEN" > > > > <vanons_t@mtn.co. To > > > > za> ids@iiug.org > > > > Sent by: cc > > > > ids-bounces@iiug. > > > > org Subject > > > > Re: SPL SQL statments with variable > > > > define table n [14447] > > > > 06/01/2009 15:25 > > > > Please respond to > > > > ids@iiug.org > > > > Thanks Srinivasan, but are only now starting to upgrade from ver 7 to ver > > 10... > > > > So there is no other way except to have the store procedure call a 4gl > > program > > to do this? > > > > Anybody? > > *************************************************************************** >**** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > *************************************************************************** >**** > > > Forum Note: Use "Reply" to post a response in the discussion forum. -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ
Ahhh, I see what you mean Sri, I understand ;)
Sounds very interesting Mike, but I DEFINITELY won't have the time :( Haven't used Aubit before, easy to use?
Thanks Art, we use version 10 and 7, as I mentioned, so I'm looking at various options. I'll have a look at Exec Blade but given the time constraints for the current project, I won't have time to go into a lot of investigation to see if Exec can be used just yet, besides which, we are only switching to version 10 at the end of this month. I have found another more achievable solution for this and probably the best performance wise since SPL is not the greatest in that department... A pure 4gl program will extract all the data at night and generate summarized data for the report tools to do straight and simple queries from. Since the reporting tool uses remote ODBC connections to fetch data, this would have been a lot slower and resource intensive with the amount and number of queries for sales data that needed to be extracted and summarized... Thanks everybody for their input...