Re: A problem in embedded SQL

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Show the actual RPG code...

Assuming it looks like
myiccode = 'EAPP%';
myccomm = '%81925B%';

exec sql;
SELECT ipol#
FROM commcmtp
join commitmp on ccomp=icomp
and ipol# =  cpol#
and cseq = iseq
and iccode like :myiccode
and crecid = 1
and ccomm like :myccomm;

Then the problems is likely that the variables are fixed length...resulting
in an actual search value of  'EAPP%    '

Easiest fix, change the variables to varchar.
Alternatively, load '%' all the way to the end of the variables,
'EAPP%%%%%'

Charles

On Tue, Dec 10, 2019 at 4:47 PM Marvin Radding <
marvin.radding@xxxxxxxxxxxxxxxxxxxxx> wrote:

> I am working on a program that is supposed to return a data element when
> the user requests it is using two parameters.
>
> The problem is in the RPGLE (running under 7.1) the sql statement returns
> EndOfData when I attempt to use the program with parameter that I know is
> there. But if I take the sql statement and use interactive sql, it works
> and selects the correct record.
>
> I don't understand what the difference is between the two methods.
>
> The sql statement is:
>
> SELECT ipol#
> FROM commcmtp
> join commitmp on ccomp=icomp
> and ipol# =  cpol#
> and cseq = iseq
> and iccode like 'EAPP%'
> and crecid = 1
> and ccomm like '%81925B%'
>
> In the RPGLE, it is a prepared statement that is okayed by the prepare
> statement. It is okayed by the open statement. But the fetch statement
> returns EndOfData.
>
> Taking the same sql statement and executing it in interactive sql, it
> works properly and returns the expected data element.
>
> Can anyone give me a clue why it doesn't work in RPGLE?
>
> Marvin
>
> Notice: This e-mail transmission may contain information that is
> proprietary, privileged and/or confidential and is intended exclusively for
> the person(s) to whom it is addressed. This message may also contain
> Protected Health Information (PHI) and must be treated confidentially and
> handled in accordance with HIPAA and other federal and state privacy laws.
> Any use, copying, retention or disclosure by any person other than the
> intended recipient or the intended recipient's designees is strictly
> prohibited. If you are not the intended recipient or their designee, please
> notify the sender immediately and delete this e-mail (and any accompanying
> attachments).
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com