Re: Dynamic SQL

[nickmart@xxxxxxxxxxxxxxx]

Greg,

I ran into the same situation about a year ago. I would actually place the
value of the host variable into the string. You will want to make sure to
punctuate like this.

#select1 = 'select System_Table_Name, '  +
           'System_Table_Schema, '       +
           'Table_Text '                 +
           'from SYSTABLES, SomeFile '   +
           'where system_table_schema '  +
           '= Somelib and table_type = ' +
           '''P'''                       +
           ' and file_type = '           +
           '''D'''                       +
           ' and duser = '             +
           ''''                          +
           sdsusr                        +
           ''''                          +
           ' order by '                  +
           'system_table_name, dseq';

You can also use editing on numeric fields within the statement.


Nick W. Mart



> I'm trying to prepare a dynamic SQL statement, and the "prepare"
> statement is giving me an error "host variable not defined or not
> usable".
>
> Here's the statement:
>
> <snip>
>
> w_SQLString = 'Select Count(*) into :w_Count From MKCUSW';
>
> Exec SQL Prepare w_SQLStmnt from :w_SQLString;
>
> Exec SQL Execute w_SQLStmnt;
>
> </snip>
>
> Ultimately, I'm planning to substitute a variable for the file name in
> when I populate my string,  but since it wasn't working I simplified it
> to this version, and it still doesn't work.
>
> I know my :w_count variable is defined OK, because if I make it static
> SQL, this statement works:
>
> <snip>
>
> Exec SQL Select Count(*) into :w_Count from MKCUSW;
>
> </snip>
>
> Can I not use host variables in dynamic SQL ?
>
>
>
> Thanks
>
>
>
> Greg Fleming
>
> Senior Programmer/Analyst
>
> Everglades Direct, Inc.
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.