Re: simple problem with string handling

Hm, that is interesting. What you describe is in line with what the SQL Reference (7.1) states, but a little test with STRSQL (7.1) seems to contradict it:

Given that foo is a char(30) column in table bar, with actual values ranging in length from 8 to 30, the following:

select foo, length(trim(foo)), substr(trim(foo), 10, 30), length(substr(trim(foo), 10, 30)) from bar

returns 30 in the fourth column for all rows. That remains true when foo is varchar(30), char(1) or varchar(1).

Even though the reference explicitly says 'If length is explicitly specified, padding is not performed.', it appears that padding is performed.

Although I always accepted the behaviour of the RPG compiler as logical, I agree that the way SQL apparently handles it would have been preferable.


Joep Beckeringh



Op 25 feb. 2013, om 19:59 heeft CRPence <CRPbottle@xxxxxxxxx> het volgende geschreven:

Odd that the RPG does not simply return the empty string for the
result of the %subst opcode in that example, and therefore evaluate to
false for the comparison. Any substring of a varying variable that has
the value of the empty-string is always the empty-string, much like the
product of any numeric with the zero-value is always zero. FWiW the SQL
will return the empty-string in that case using its SUBSTR scalar
function [and LEFT, RIGHT, ¿and others?].

Doubtful, but maybe if it were reported as a defect [often enough]
instead of being accepted as the expected outcome, the RPG would
actually change to do [what is IMO, and probably also for many others,]
the correct thing, instead of giving an error.?

While the SQL also does not fail for substring beyond the declared
maximum length of the varying field, nor even for a zero or negative
start position or length [since some release change IIRC], I could see
the RNX0100 remaining as the effect for those situations in the RPG for
%subst. Similarly SQL will LOCATE an empty-string in an empty-string,
but if %scan could not, that is not a big issue like the %subst being
unable to deal /properly/ with the empty-string.

Regards, Chuck

On 25 Feb 2013 04:27, j.beckeringh wrote:

Although I'm strongly in favour of using varying length strings,
there is one thing that has bitten me a few times: substringing on
a position that is outside the size of the string;

For example:

if %subst(path :1 :1) = '/';
// absolute path
else;
// relative path
endif;

will bomb if path is an empty varying length field. <<SNIP>>

--
This is the RPG programming on the IBM i (AS/400 and 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.