Re: MCH3601 on Fetch -- RPG400-L

Hey Pete,

From the looks of your SQL you are working with BPCS. In this case you

don't have to worry about an SQL injection. Using a parameterized dynamic
SQL statement is inefficient in this case. It doesn't matter if the value
of your host variables change all the time or not. BPCS has enough
inefficient code, you should use static sql in this case.

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

Pete Helgren <pete@xxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
02/09/2011 10:28 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: MCH3601 on Fetch

That didn't seem to be the issue although once I defined variables in
the D specs, SQL seemed happy to fetch into them. It could be that the
parameters, even initialized ones, passed in on the PLIST just aren't
the correct type. Since the parameters don't carry a packed or zoned
designation, maybe SQL couldn't transform them? I don't really know.
So I defined each of the host variables in the D specs and then set the
parameter values from the host variables before returning to the caller.

As for using dynamic vs static SQL, I have just gotten so used to using
parameterized SQL statements in Java web apps to minimize the risk of
SQL injection attacks that I just naturally used it here. The parameter
values change with each invocation so it seemed the right way to handle
it. I don't know if just appending the host variable values to the SQL
directly is better (is it?)

I am not using pointers. And nothing was BASED. Pretty sure it was the
direct fetch into the parameters as host variables that somehow caused
the problem.

Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com

On 2/9/2011 5:22 PM, Scott Klement wrote:

Hi Pete,

Is one of your host variables (ORDER#,ITEM,QTY,VIN#) declared as a
parameter that you forgot to pass, by chance?

If that's not the case, then... are you using pointers? Any of these a
BASED field?

This probably isn't related to the MCH3601, but... is there some reason
you're using dynamic SQL instead of static SQL? I can't see a reason
(in the part you posted) why you'd want to do extra work, and have your
program run slower? Seems like static SQL would be easier to code, and
run faster...

On 2/9/2011 4:42 PM, Pete Helgren wrote:

This one has me stumped. I have a prepared statement that selects a

row

based on parameter values. The sql looks like this:

gSQLStmnt2 = 'select distinct extord, lprod, lqord, ' +
'substr(extvin,1,10) from ecl ' +
'inner join eclx01 on lord=extord and

lline=exline ' +

'where lprod = ? and extvin = ? ';

I then do the following in another sub procedure:

c/free
EXEC SQL
PREPARE S2 FROM :gSQLStmnt2;
EXEC SQL
DECLARE C2 CURSOR FOR S2;
EXEC SQL
OPEN C2 USING :ItemNum, :Vin;
/end-free

When I run the program the joblog shows this:
.......
Blocking used for query.
Cursor C2 opened.
1 rows fetched from cursor C2.
Pointer not set for location referenced.

That MCH3601 occurs on this fetch:

FETCH FROM C2 INTO :order#,:Item,:QTY,:vin#;

This is the first time I have used a parameterized SQL statement in an
RPG program so I have probably done something wrong. The compiler
listing shows this at the statement the error points to:

451 C SQL_00032 IFEQ '1'
452 C EVAL ORDER# = SQL_00034
453 C EVAL ITEM = SQL_00035
454 C EVAL QTY = SQL_00036
455 C EVAL VIN# = SQL_00037
456 C END

It is almost as though it doesn't like the host variables. I double
checked the sizes and types of the variables and they *seem* OK.

Anybody seen something like this before and can point me to my error?