Host Variables and Table names = Dynamic SQL ?

[Tim.D]

People,

I've ventured into the wonderful world of Stored Procedures. My first
experience has been relatively successful however I am stuck on using
host variables to specifiy actualy table or column names in a FROM
clause. After many hours or reading all manner of manuals I've
discovered it appears this is not possible and that in order to so I
need to further venture into dynamic SQL.

My present procedure is based on all static SQL and specifies a numer
of host variables used in IF and WHILE clauses and cursor WHERE
statements. If I have to now use dynamic SQL to get around this nasty
TABLE restriction, is there a way to include such in the same
procedure under different declare statements or am I going to have to
start a completely new learning curve here ?

I guess what I am asking is for a very basic example of how I might go
about using a variable to specify a table name under whatever
circumstances you wish to provide (if at all .. heh).

The variables I use presently are usually set by SELECT INTO
statements, or as the result of a calculation and the reason I need a
vaiable for the table name is it depends on the result of a query in
the proc, therefore not being known at compile time. I take it this
rule also applies to UDFs as well ?

Any rules and limitations providing a brief example would be very much
appreciated. Id very much like to NOT have to completly re-write the
whole thing as it is getting somewhat lengthy.

Procedure is written in a simple SQL CLP script and debugged using
Development Center. System is Windows 2000 server, DB2 v8.1.4 ESE

Many thanks in anticipation,

Tim

[Serge Rielau]

Re: Host Variables and Table names = Dynamic SQL ?

Tim,

There is not much magic to dynamic SQL in SQl Procedures.

Simply look up the EXECUTE IMMEDIATE as well as PREPARE and EXECUTE
statements.
Essentially you glue together any string you please, associate alues to
the parameter markers (in place of SQL variables and host variables) and
of you go.

BTW, this "nasty restriction" fundametal to the working of SQL.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Eric Herber]

Re: Host Variables and Table names = Dynamic SQL ?

Tim.D wrote:
[color=blue]
> People,
>
> I've ventured into the wonderful world of Stored Procedures. My first
> experience has been relatively successful however I am stuck on using
> host variables to specifiy actualy table or column names in a FROM
> clause. After many hours or reading all manner of manuals I've
> discovered it appears this is not possible and that in order to so I
> need to further venture into dynamic SQL.
>
> My present procedure is based on all static SQL and specifies a numer
> of host variables used in IF and WHILE clauses and cursor WHERE
> statements. If I have to now use dynamic SQL to get around this nasty
> TABLE restriction, is there a way to include such in the same
> procedure under different declare statements or am I going to have to
> start a completely new learning curve here ?
>
> I guess what I am asking is for a very basic example of how I might go
> about using a variable to specify a table name under whatever
> circumstances you wish to provide (if at all .. heh).
>
> The variables I use presently are usually set by SELECT INTO
> statements, or as the result of a calculation and the reason I need a
> vaiable for the table name is it depends on the result of a query in
> the proc, therefore not being known at compile time. I take it this
> rule also applies to UDFs as well ?
>
> Any rules and limitations providing a brief example would be very much
> appreciated. Id very much like to NOT have to completly re-write the
> whole thing as it is getting somewhat lengthy.
>
> Procedure is written in a simple SQL CLP script and debugged using
> Development Center. System is Windows 2000 server, DB2 v8.1.4 ESE
>
> Many thanks in anticipation,
>
> Tim[/color]

Here is an example of using dynamic SQL inside
a SQL procedure:

CREATE PROCEDURE myprepopen
(
IN p_name VARCHAR(32)
)

SPECIFIC myprepopen
DYNAMIC RESULT SETS 1
LANGUAGE SQL

BEGIN
DECLARE v_sql VARCHAR(256);
DECLARE v_stmt STATEMENT;
DECLARE c_staff CURSOR WITH RETURN TO CLIENT FOR v_stmt;

/*
SQL select vorbereiten
*/
SET v_sql = 'SELECT id, name, salary FROM staff WHERE name LIKE ? FOR
READ ONLY';
PREPARE v_stmt FROM v_sql;

/*
Cursor oeffnen, platzhalter durch hostvariable ersetzen
*/
OPEN c_staff USING p_name;

END@


HTH.

Best regards

Eric
--
IT-Consulting Herber
*************** *************** *************** **
Download the IFMX Database-Monitor for free at:

http://www.herber-consulting.de/BusyBee

*************** *************** *************** **

[Tim.D]

Re: Host Variables and Table names = Dynamic SQL ?

Hi serge, long time no see.

I've managed to figure out most of it through more reading,
experimenting and trial and error, and it seems to work very nicely
for the most part. What I am having trouble with is piecing some
elements together into a variable for the PREPARE statement. I have
everything added using the concat || to contruct the statement but
cannot get a last piece to add properly.

It is the result of UDF that returns a varchar(12) character string. I
want to use this piece in the WHERE COLUMN = string statement then
order by the column. Unfortunately the UDF returns fine but without
apostrophie. I need to encase this result in 'string' ie: apostrophies
inorder to be syntactically correct.

For the life of me I have tried all I know but come up short on this
last bit.

ie:

set stmt = 'select ID from ' || tb_name || ' where ' || col_name || '
= ' || getudf(value) || ' order by ' || col_name ;

where tb_name and col_name are variables set in previous statement.
The result of getudf(value) must be something like 'ABC' rather than
ABC as col_name is a varchar column. btw the stmt is used in a cursor.
Ive even tried setting the result to another variable then using that
inlieu of the UDF but it still will not populate the value with the
required apostrophies. The PREPARE actually works of course, but when
the cursor opens it fails as the value requires the damned 's. Ive
tried multiple ''s but that will not build then. [mutter]

Arrrghhh .. heh

Any ideas on how this can be done and a quick example would save my
entire weekend ! .. many thanks again sir.

[Serge Rielau]

Re: Host Variables and Table names = Dynamic SQL ?

Tim,

In principle I'd suggest to use parameter markres (as described by
Eric). Thsi way you have a chance that DB2 recognizes multiple prepares
as being teh same and hence will exploit the dynamic statement cache.
To answer you question about quotes, you need to escape the quote with
another quote:
e.g values '''Hello'''
would return: 'Hello'
Ths can be done inside your UDF or outside.

'...WHERE ... = ''' || UDF() || '''';

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab