SQL coalesce retrieving garbage

[jstagliano]

I am writing a script accessing records using SQL. I am joining a file with left outer join and selecting the field from that file with coalesce(field). My sql statement works great on the ibm i. However, when write it in my php script, the characters returned are unreadable.

Also, when I do a "SELECT SUBSTR(DIGITS(FIELD),1,2) ..." the field retrieved is unreadable.

It appears that the values are not getting converted from ebcdic to ascii. All values returned without the additional functions are readable. It is only those values returned with substr or coalesce.

Does anyone have any suggestions?

[erich_hieden]

Check your QCCSID in IBM i and DefaultFsCCSID and CGIJobCCSID in /www/zendsvr/htdocs/httpd.conf

[jstagliano]

QCCSID system value is 65535 - is this correct?

DefaultFsCCSID and CGIJobCCSID are not in /www/zendsvr/conf/httpd.conf - Should they be? and what should the lines look like?

I am probably going to bread my sql statement into multiple pieces so that I do not need coalesce and I'll substring after returning my values.

This is the stuff that frustrates me with php. It looks like it should be a great solution for making browser based applications on the i and then I hit wall after wall with very little help. I end up spending way too much time researching why things don't work and creating work arounds rather than developing new applications. I have multiple problems that I have created work arounds rather than solving the original issue.

[erich_hieden]

QCCSID 65535 means that characters are used by their hex value as is and won't get translated. IMHO you should always set the QCCSID so it represents the codepage you're actually using. Why IBM changed this value's default is out of my knowlegde.

Regarding httpd.conf, here's mine (German codepage with Euro sign)

Code: Select all

DefaultFsCCSID 1141
CGIJobCCSID 1141

You should also make sure, that you got the CCSID in fastcgi.conf right (most propably and by default 819).

I know that there are some pitfalls in using PHP on IBM i. As they are in using on Linux, Unix, Windows ... As they are in using C#, Java, <insert any language here> ...
But I can assure you, once you get past this problems, this concept offers great opportunities. (No, I'm not a Zend employee)

And don't hesitate to ask here.

Regards

[aseiden]

Martin/Erich is right about CCSID. That's how EBCDIC and ASCII convert back and forth.

Resources:
http://www.youngiprofessionals.com/wiki/FastCGI
http://www.slideshare.net/aseiden/db2-a ... s-on-ibm-i

As those resources say, you really do have to add those two CCSID lines in Apache, then restart Apache.

If you are in the USA then use CCSID 37, like so:
DefaultFsCCSID 37
CGIJobCCSID 37

Alan

[aseiden]

P.S. In the SlideShare link I provided, look at slides 65-68 for answers to your issue of "getting EBCDIC gibberish when using functions in SQL." The CCSID solution should do the trick.