Re: PowerBuilder SQL statement problem

In article <3k6s4o$k5i_at_ionews.io.org>, Mario Andreoli <marioa_at_bonk.io.org> wrote:
>I'm having a problem in PowerBuilder 3.0a
>with an embedded SQL statement (running against an Oracle backend):
>
>SELECT first_name
>FROM my_table
>WHERE col_name IN ( :lsvNameList ) ;
>
>
>Hardcoded the lsvNamelist variable would look
>like 'John','Henry','Joe' and so on.
>When I try to build this string in a variable and
>pass that it always returns a
>sqlcode of 100 (no data found). Yet if I take the exact same SQL
>statement and hardcode the variable contents it works??!!
>
>Is there something I'm missing here ????
>
>Thanks
>
>
>Mario Andreoli
>marioa_at_io.org

Actually, I'm afraid you can't do what you're trying. Powerbuilder ( and really, most embedded SQL dialects ) expects exactly one value to be found in that variable.

For similar reasons, you could NOT have a statement such as:

SELECT :ls_column_name
INTO :ls_my_return
FROM :ls_some_table ;

SQL wants more. It wants to be able to parse the statement, and build an execution plan, even before it examines the value inside the variable. I believe that applies, at a low-level, on any DBMS. To accomplish what you want, you'll have to do something like:  

ls_sql = " SELECT first_name FROM my_table WHERE col_name IN ( " &

        + lsvNameList + " )"

Then, either use dwmodify (modify in pb4) to alter the SQL statement of your datawindow, or use Dynamic SQL to run your statement.

Sorry it's not as simple as you'd like.

Also, though, I think you can use a string array argument to a datawindow, and possibly you can get away with what you're trying. I haven't personally tried it, though.

Anyone else?

Kevin Fries
PB Developer

-- 
<Insert signature here>