Re: SQL CLI problem -- RPG400-L


Hi Jeff,

When binding the first column, you use the following value for the field size:
%len(Row(1).Cust)*256 + %decpos(Row(1).Cust)
Why are you multiplying by 256?

Because that's what it tells me to do in the manual. Here's the relevantpage of the Info Center:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/cli/rzadpfnbindc.htm#rzadpfnbindc

Here's a quote from that page:

    If fcType is either SQL_DECIMAL or SQL_NUMERIC, cbValueMax must
    actually be a precision and scale. The method to specify both values
    is to use (precision * 256) + scale. This is also the value returned
    as the LENGTH of these data types when using SQLColAttributes().

The term "precision" means "the total number of digits in the field". Theterm "scale" means "the number of digits to the right of the decimalplace."

Why do you use %Len rather than %Size as in column 2?
%size(Row.Name)

Because %size() gives the number of bytes, whereas %len() gives the numberof digits. For example, if I have a field declared as 13P 4, %size()would return 7 because it occupies 7 bytes of memory. %len() would return13, because it's 13 digits long. Since SQL is looking for the number ofdigits, not the number of bytes, %len() is the appropriate tool.

On the other hand, if it's a fixed-length CHAR field and I'm using asingle-byte character set, then %len() and %char() return the same value.Since %size() runs faster, I might be inclined to use %size() in thatsituation.

Also, have you tried to use dynamic binding of a selection parameterwith a character field?
Ex. SQL_Stmt = Select Cust,Name,City from Custfl where Name = ?


No, I haven't tried that yet.  Instead I do something like:

      SQL_Stmt = 'Select Cust,Name,City from Custfl where Name = "'
               + %trimr(NameParam) + '"';

Granted, your way will run faster if the statement needs to be executedmultiple times with different values. It'll also be simpler because youdon't have to worry about quotes in the field data or something likethat... but I haven't yet gotten around to experimenting with parametermarkers in CLI. Maybe in a future project I'll need to...

.
Parm = '''JEFF'''
rc = SQLBindParm
When I tried it, the return code was 0 indicating no error, but thefetch did not return any values. When I embedded the value in the SQLstatement, I was able to retreive the data I wanted.

I suspect that it's because you added quotes to the outside of the string.Instead of searching the file for a field contianing JEFF, it's notsearching the file for a field contaiing 'JEFF' -- since there aren'tquotes in the field in the file (at least, I'm assuming not) it won't beable to find it.


Just a guess, though, I haven't tried it.