Re: Viewing the resultant SQL in an RPG program - RESOLVED

OK. Thanks for all the help and information. Although none of it directly solved the issue, each little piece added another clue.

The actual problem was not the *LOVAL/*HIVAL stuff, it was that I was concatenating single quotes to each of the character fields out of habit. Apparently the compiler is smart enough to recognize a character field as such and a numeric field as such and properly format the statement when referencing the host variables. When I code in Java I need to make sure that character fields have single quotes so that the statement is formatted correctly.

Dropping the appending of the single quote constants did the trick. All of which brings me back around to my original "wish": If I could have seen the resultant SQL (warts and all) and could do a cut and paste in to an interactive session I would have immediately seen the issue and fixed it. A debug view that shows the SQL statement fully rendered and as it would be executed would be a sweet addition. I would have seen this:

select * from myfile where
myssn between -999999 and 999999 and
myuser between ''PETE' ' and ''PETE' '

Which immediately I would have recognized as wrong.

Thanks to all,

Pete Helgren


Pete Helgren wrote:

It seems to be working as Joe indicated. I still don't see a recordset when I run the SQL through RPG vs the interactive SQL session using the hex representations of *LOVAL and *HIVAL so I don't know what is going on, yet. I don't get records running the RPG version but if I run what I assume the RPG version is running using interactive SQL, I do get records. A few more hours of debugging and trial and error and I'll figure it out.

Pete


vhamberg@xxxxxxxxxxx wrote:

I thought *LOVAL for character fields was x'40404040...' for the length of the field - it ought to be in the RPG Reference. It might be x'00000000...' for hex fields.

But then I'm not fully awake, so don't know for sure.

-------------- Original message -------------- From: "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>

From: Pete Helgren
I'll do some trial and error testing of the statement using different variables and commenting portions of the statement and see if I can figure out why the SQL is not returning records like I expect. The *LOVAL/*HIVAL technique is a good one, I just need to know what to submit in the SQL for *LOVAL and *HIVAL when the field is a 10 character numeric.

Did you try x'00000000000000000000' and x' FFFFFFFFFFFFFFFFFFFF'?
Joe

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.