SQL coalesce retrieving garbage

The place for general PHP questions and hints for PHP on IBM i

SQL coalesce retrieving garbage

Postby jstagliano on Fri Jan 20, 2012 11:41 pm

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?
jstagliano
 
Posts: 2
Joined: Fri Jan 20, 2012 11:25 pm

Re: SQL coalesce retrieving garbage

Postby erich_hieden on Mon Jan 23, 2012 7:59 am

Check your QCCSID in IBM i and DefaultFsCCSID and CGIJobCCSID in /www/zendsvr/htdocs/httpd.conf
erich_hieden
 
Posts: 371
Joined: Tue Jul 07, 2009 9:01 am

Re: SQL coalesce retrieving garbage

Postby jstagliano on Mon Jan 23, 2012 9:40 pm

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.
jstagliano
 
Posts: 2
Joined: Fri Jan 20, 2012 11:25 pm

Re: SQL coalesce retrieving garbage

Postby erich_hieden on Tue Jan 24, 2012 10:08 am

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
erich_hieden
 
Posts: 371
Joined: Tue Jul 07, 2009 9:01 am

Re: SQL coalesce retrieving garbage

Postby aseiden on Tue Jan 24, 2012 10:28 pm

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
 
Posts: 793
Joined: Thu Apr 09, 2009 5:45 pm

Re: SQL coalesce retrieving garbage

Postby aseiden on Tue Jan 24, 2012 10:36 pm

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.
aseiden
 
Posts: 793
Joined: Thu Apr 09, 2009 5:45 pm


Return to PHP Questions / Hints

Who is online

Users browsing this forum: No registered users and 1 guest