PHP from CLI on IBM i

The place for general PHP questions and hints for PHP on IBM i
Post Reply
mark397
Posts: 87
Joined: Thu May 30, 2013 6:12 pm

PHP from CLI on IBM i

Post by mark397 » Wed Nov 19, 2014 7:14 pm

We have a setup where we're running Zendserver 6 on an IBM i and a vanilla LAMP server on Ubuntu. Ubuntu LAMP is basically there to serve the WWW site using the IBM i as the data store through unixODBC. In trying to make things uniform we're hoping to copy some of the logic from the WWW site to the IBM i and run the php from CL programs. We've found documentation on how to do this, but our problem has to do with encoding. In this specific instance we're generating form letters to email, which are stored in an HTML file, then run a call to SQL to find the recipients, then loop over the recipients and basically mail merge them (string replace {field} with 'value'). Most fields are coming out as garbled characters. I do know that the data is in ISO-8859 and likely the email client will be UTF-8 (which I could override to ISO-8859) but I can't even get past saving the output to a text file. I moved the same php-cli scripts from the IBM i to the Ubuntu, changed db2_*() calls to odbc_*() equivalents, and the output is fine. Does anyone know how to properly encode SQL returned data in php-cli on IBM i? There doesn't seem to be much info on Google and this hack-n-slash method isn't getting me very far.

scottgcampbell
Posts: 187
Joined: Wed Apr 22, 2009 2:29 pm
Location: Edmonton, AB, Canada

Re: PHP from CLI on IBM i

Post by scottgcampbell » Wed Nov 19, 2014 7:45 pm

Few questions:

What is your CCSID (DSPSYSVAL SYSVAL(QCCSID))

What do you mean "Most fields are coming out as garbled characters"? The values returned from SQL or the value being written to the file?

How are you calling the PHP? (Jobscheduler/sbmjob/qsh/qp2term/run from a CL)

Scott

mark397
Posts: 87
Joined: Thu May 30, 2013 6:12 pm

Re: PHP from CLI on IBM i

Post by mark397 » Wed Nov 19, 2014 9:07 pm

CCSID is 65535, I had expected 37, so I'll have to check into this first.

I hadn't checked this yet

I'm using ssh right now (Linux ssh client, from what I understand should be equivalent to qp2term) to run the php executable which is saving the output to a .html file, and in production the goal is to generate HTML and send as a formatted email. It will be called from a CL program which will be called from jobscheduler.

Here's a sample (fields that worked properly are redacted, but you get the point)
Greetings FirstName,

Now that �������@@@@@@@@@@@@@@@ has settled in with the RentalProduct, we’d like to tell you a little more about how we service ������`��������@��@��@@@@@@@@@.
I tried using mb_convert_encoding($field, "UTF-8", "ISO-8859-1") and I get:
Greetings FirstName,

Now that ÒÁÔÂÙÅÕ@@@@@@@@@@@@@@@ has settled in with the RentalProduct, we’d like to tell you a little more about how we service ÓÉÕãÖÕ`âãÖÃÒãÖÕ@âÙ@Èâ@@@@@@@@@.
Note that the LAMP server that can successfully use this data is using the same database, only odbc from a different server as opposed to db2 from the same server.

scottgcampbell
Posts: 187
Joined: Wed Apr 22, 2009 2:29 pm
Location: Edmonton, AB, Canada

Re: PHP from CLI on IBM i

Post by scottgcampbell » Wed Nov 19, 2014 9:15 pm

If you can change the CCSID that might fix it. Take a look at this post for some suggestions
http://forums.zend.com/viewtopic.php?f=68&t=122138

Scott

mark397
Posts: 87
Joined: Thu May 30, 2013 6:12 pm

Re: PHP from CLI on IBM i

Post by mark397 » Fri Nov 21, 2014 10:30 pm

Thanks Scott. I spoke with IBM Support and they helped me quite a bit with using DBMON to find the problems.
Message ID . . . . . . : SQL0332 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic

Message . . . . : Character conversion between CCSID 65535 and CCSID 1208
not valid.
Cause . . . . . : Character or graphic conversion has been attempted for
data that is not compatible. There is no conversion defined between CCSID
65535 and CCSID 1208.
If one CCSID is 65535, the other CCSID is a graphic CCSID. Conversion is
not defined between 65535 and a graphic CCSID.
Recovery . . . : Ensure that all character or graphic comparisons,
concatenation, or assignments are between columns or host variables with
compatible CCSID values.
The problem is string literals, whether it's in the COALESCE, a RIGHT or LEFT, or a concatenation. It's only an issue if the string literal is part of the SELECT, so WHERE or JOIN including literals is fine. As a workaround use the CAST with CCSID 37, or set the job, user, or systemwide CCSID to 37. For short-term we're going to look at using CAST(), then eventually migrating to UTF-8 data (CCSID 1208).

gul8438
Posts: 1
Joined: Thu Mar 26, 2015 12:38 pm

Re: PHP from CLI on IBM i

Post by gul8438 » Thu Mar 26, 2015 12:39 pm

I do know that the data is in ISO-8859 and likely the email client will be UTF-8 (which I could override to ISO-8859) but I can't even get past saving the output to a text file. I moved the same php-cli scripts from the IBM i to the Ubuntu, changed db2_*() calls to odbc_*() equivalents, and the output is fine. Does anyone know how to properly encode SQL returned data in php-cli on IBM i? There doesn't seem to be much info on Google and this hack-n-slash method isn't getting me very far.???

________________
GuL

mark397
Posts: 87
Joined: Thu May 30, 2013 6:12 pm

Re: PHP from CLI on IBM i

Post by mark397 » Thu Mar 26, 2015 5:34 pm

Here's an example of what had to be done - cast as CHAR and specify the CCSID:

Code: Select all

CAST(COALESCE(TRIM(CFNAME) || ' ' || TRIM(CLNAME))) AS CHAR(50) CCSID 37) AS NAME,
There's a proper fix now, perhaps I should start a new thread on it... AS/400 and DB2 support UTF-8, which is CCSID 1208. The correct setup is to store your data as such, and then there will be no need to convert on DB calls. The system-wide CCSID will need to be set to 65535 (no convert) and the default httpd.conf has these values as default:

Code: Select all

DefaultFsCCSID 37
CGIJobCCSID 37
Those will need to be 1208. The trouble is you'll need to roll your data over into the new encoding. The last obstacle is that the old iSeries Access doesn't support unicode, and any time you run into it your screen will error. The new IBM i Client Solutions does support unicode, so if you make the transition there you can overhaul the data and have an actual proper fix. We're a few days from starting to migrate our first, so I can't tell you much from experience, just what I've read.

gif5360
Posts: 1
Joined: Wed Jan 25, 2017 8:23 pm
Location: New York
Contact:

Re: PHP from CLI on IBM i

Post by gif5360 » Wed Jan 25, 2017 8:51 pm

Thanks-gifamerica com/ man!

Post Reply