Building SQL from parameters in SPL

This may be an easy one but I can't quite get my head around it - having not used SPL before. Within a stored procedure I want to generate SQL routines based on paramaeters passed to the procedure. So far so good. But I want the database name and onconfig to be passed to the procedure to make it as flexible as possible - so I can generate select account_number fromdatabase_variable@onconfig_variable:customer but I don't know how to set it up so database_variable and on-config_variable get translated to whatever is passed to the procedure. The statement also needs to go into a FOREACH loop. I'm farily sure I need to prepare the statement and build it up into a string but I'm nt sure of the syntax within SPL. Any help greatly appreciated. Ian

On Mon, 04 Aug 2003 11:01:50 -0400, Ian Briscoe wrote: > This may be an easy one but I can't quite get my head around it - having > not used SPL before. Not easy actually. SPL does not permit dynamic queries so the database and table names must be static in the code. Only replaceable parameters in VALUES, SET and WHERE clauses are permitted. Having said that there IS a DATABLADE available for download from the IIUG Software Repository that will permit you do create, prepare, and run dynamic queries. > Within a stored procedure I want to generate SQL routines based on > paramaeters passed to the procedure. So far so good. But I want the > database name and onconfig to be passed to the procedure to make it as > flexible as possible - so I can generate > select account_number from> database_variable@onconfig_variable:customer Now, what do you mean by an 'onconfig_variable'? <SNIP> Art S. Kagel

"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2003.08.04.12.21.29.970076.15473@bloomberg.net>... > On Mon, 04 Aug 2003 11:01:50 -0400, Ian Briscoe wrote: > > > This may be an easy one but I can't quite get my head around it - having > > not used SPL before. > > Not easy actually. SPL does not permit dynamic queries so the database > and table names must be static in the code. Only replaceable parameters > in VALUES, SET and WHERE clauses are permitted. > > Having said that there IS a DATABLADE available for download from the > IIUG Software Repository that will permit you do create, prepare, and run > dynamic queries. > > > Within a stored procedure I want to generate SQL routines based on > > paramaeters passed to the procedure. So far so good. But I want the > > database name and onconfig to be passed to the procedure to make it as > > flexible as possible - so I can generate > > select account_number from> > database_variable@onconfig_variable:customer > > Now, what do you mean by an 'onconfig_variable'? > <SNIP> > > Art S. Kagel Art, Thanx for that - the onconfig variable is wrong - I guess I technically mean the server name - which must be passed dynamically to build up the query. It is to allow a user to do cross references to other companies within our group - they would identify an account number at a remote site - the name of the remote site is matched to another field which is an entry in sqlhosts. Really anoying that theres no straightforward way of doing this!!! Ian

On Mon, 04 Aug 2003 17:09:48 -0400, Ian Briscoe wrote: > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:<pan.2003.08.04.12.21.29.970076.15473@bloomberg.net>... >> On Mon, 04 Aug 2003 11:01:50 -0400, Ian Briscoe wrote: >> >> > This may be an easy one but I can't quite get my head around it - >> > having not used SPL before. >> >> Not easy actually. SPL does not permit dynamic queries so the database >> and table names must be static in the code. Only replaceable >> parameters in VALUES, SET and WHERE clauses are permitted. >> >> Having said that there IS a DATABLADE available for download from the >> IIUG Software Repository that will permit you do create, prepare, and >> run dynamic queries. >> >> > Within a stored procedure I want to generate SQL routines based on >> > paramaeters passed to the procedure. So far so good. But I want the >> > database name and onconfig to be passed to the procedure to make it >> > as flexible as possible - so I can generate >> > select account_number from>> > database_variable@onconfig_variable:customer >> >> Now, what do you mean by an 'onconfig_variable'? <SNIP> >> >> Art S. Kagel > > Art, > > Thanx for that - the onconfig variable is wrong - I guess I technically > mean the server name - which must be passed dynamically to build up the > query. It is to allow a user to do cross references to other companies > within our group - they would identify an account number at a remote > site - the name of the remote site is matched to another field which is > an entry in sqlhosts. > > Really anoying that theres no straightforward way of doing this!!! There is and easy way, just not in SPL. It's easy in 4GL, ODBC, ESQL/C, JDBC, Perl DBD/DBI, etc. Here's a suggestion anyway. You can write multiple SPLs, one for each company, and a driver SPL that executes the correct SPL based on the input parameters in a FOREACH loop returning that function's output with RETURN ... WITH RESUME;. The user's or apps call the driver SPL only. Art S. Kagel > Ian

Ian Briscoe wrote: > Really anoying that theres no straightforward way of doing this!!! I understand your frustration, but SPL are pre-compiled and optimized. You can't pre-optimize (establish an execution plan) if you don't know what it will do, and which databases/tables etc. it will use. Life can be sad... Does anybody know any kind of code in any DBMS which is pre-compiled and pre-optimized and that allows dynamic SQL? Regards.