Re: Using host variables in SQLRPGLE doesn't seem to be working.... IN clause

Thanks for the explanation Scott.

I guess the real point here is the the commas are part of the sql
statement that must be precompiled. I actualy have 5 different columns I
need to do this with so I was trying to be a bit more succinct and use
just 1 host variable 5 times instead of 5 host variables 5 times. Plus, I
only want to include the host variable if it isn't equal to *blanks, so I
guess i'm just going to have to build and prepare the statement.

Thanks for the clear explanation. Good way to start a Monday, learning
learning learning.


Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777



Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/07/2011 05:33 PM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Re: Using host variables in SQLRPGLE doesn't seem to be working.... IN
clause






Hi Bryce,

On 1/7/2011 4:08 PM, Bryce Martin wrote:
...snip...

Where rnword1 in(:NameString)

...snip...

I build NameString from the array values. Its a character string. Its
value is like... 'word1','word2','word3'

Nope, that won't work.

To understand this, make a distinction between the "source code" for
your SQL statement, and the actual code that gets executed.

We all know that you want the finished SQL statement to look like this:

Where rnword1 = ('word1', 'word2', 'word3')

We know that's what you *want*... And if you physically insert the
contents of your string into the SQL statement, it would indeed look
like that. but that's source code! the SQL precompiler is converting
that statement into something else... it doesn't remain in your program
in that form after it's been compiled!

So if the precompiler sees this:

Where rnword1 in(:Word1, :Word2, :Word3)

It generates code that does something like (pseudo-code):

1) Move contents of Word1 into first choice
2) Move contents of Word2 into second choice
3) Move contents of Word3 into third choice
4) Call database API

And that'll work just fine. but, following the same logic, the code
you've written looks like this:

Where rnword1 in(:NameString)

1) Move contents of NameString into first choice
2) Call database API

It doesn't know that NameString contains more SQL statement syntax that
it should interpret it as part of the "source code!" It doesn't try to
"compile" the data in your NameString at run-time! It, therefore, can't
contain anything that's part of the SQL statement syntax!

However, you certainly can do this:

Where rnword1 in(:Word1, :Word2, :Word3)

And you can set Word1, Word2 and Word3 to the same value if you only
want it to find one value in the file, instead of 3 different ones...