Re: Dynamic SQL

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.