We have PHP server code, executing SQL statements against our iSeries midrange.
Here is a simplified version of the SQL query
SELECT 'Regular' "sales_type", sum(sales_type1) "sales" FROM salesTable
The query executes just fine, the problem is that when using a static field/value such as 'SomeText' "Title" and the results come back in PHP, they are not in the desired format
string(7) "م�����"
To connect to the system and retrieve the results
db2_connect ( '*LOCAL', 'user', 'pass' );
if (! $connection) {[error code]}
$stmt = db2_prepare ( $connection, $strSql );
if (! db2_execute ( $stmt ) ) { [error code ]
while ( $row = db2_fetch_array ( $stmt ) ) {
var_dump($row[1]);
}
We are on PHP version 5.2.17
Our iSeries is V7R1M0
A solution to convert server side with PHP or in the SQL query its self would be great.
Thanks!
Edit
From bucks suggestion we have changed the user profile CCSID to 37 instead of 65535
Now we get back (below) which is a bit closer...
string(7) "Ù…‡¤“™"
Is this possibly because we only changed the user? Does the system, job or table need to be changed too?
Edit 2
Here is the phpinfo output
_COOKIE["ZDEDebuggerPresent"] php,phtml,php3
_SERVER["ZendEnablerConfig"] /www/zendserver/conf/fastcgi.conf
_SERVER["PHPRC"] /usr/local/ZendSvr/etc/
_SERVER["PHP_FCGI_CHILDREN"] 40
_SERVER["PHP_FCGI_MAX_REQUESTS"] 0
_SERVER["CCSID"] 819
_SERVER["LANG"] C
_SERVER["INSTALLATION_UID"] 20101203131436121338
_SERVER["LDR_CNTRL"] MAXDATA=0x40000000
_SERVER["LIBPATH"] /usr/local/ZendSvr/lib
_SERVER["DB2NOEXITLIST"] TRUE
_SERVER["ORACLE_HOME"] .
_SERVER["ORA_NLS10"] no value
_SERVER["ORA_NLS_PROFILE33"] no value
_SERVER["FCGI_ROLE"] RESPONDER
_SERVER["REDIRECT_UNIQUE_ID"] UYKvWcCoAQIAAnZHWG8AABS@
_SERVER["REDIRECT_STATUS"] 200
_SERVER["UNIQUE_ID"] UYKvWcCoAQIAAnZHWG8AABS@
_SERVER["QIBM_USE_DESCRIPTOR_STDIO"] Y
_SERVER["HTTP_HOST"] vmas400.vm.com:10090
_SERVER["HTTP_CONNECTION"] keep-alive
_SERVER["HTTP_X_REQUESTED_WITH"] XMLHttpRequest
_SERVER["HTTP_USER_AGENT"] Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.31 (KHTML, like Gecko) Chrome/26.0.1410.64 Safari/537.31
_SERVER["CONTENT_TYPE"] application/x-www-form-urlencoded
_SERVER["HTTP_ACCEPT"] */*
_SERVER["REFERER"] http://vmas400.vm.com:10090ZendServer/Index/Index
_SERVER["HTTP_REFERER"] http://vmas400.vm.com:10090/ZendServer/Index/Index
_SERVER["REFERER_URL"] http://vmas400.vm.com:10090/ZendServer/Index/Index
_SERVER["HTTP_ACCEPT_ENCODING"] gzip,deflate,sdch
_SERVER["HTTP_ACCEPT_LANGUAGE"] en-US,en;q=0.8
_SERVER["HTTP_ACCEPT_CHARSET"] ISO-8859-1,utf-8;q=0.7,*;q=0.3
_SERVER["HTTP_COOKIE"] ZENDSERVERSESSID=7asfv608qffhv556msem6evi66; CosmeticContest=16062; CompanyWithStoreDetail=16061; TYLYClassAnalysis=16068; OrderDetail=17220; RmsOrders=17221; DailyReceipts=16063; DailySales=17562; OnOrder=16064; OpenPurchaseOrders=17566; RegularPriceRankings=17568; ReviewStatistics=17570; SalesAndStock=17573; StocksByPeriod=17575; Top10BestSellers=17577; ReplenishmentAssortment=17269; RABS=17616; FreeFormatSku=16473; TYLYSalesAndOH=21294; SalesRecapByDate=16312; VendorAgendaSummary=23219; BasicStock=23474; InStock=16067; RegSalesAvgStockSummary=21270; TYLYSalesMDGMByStore=23822; VendorAgenda=23826; Header=16066; usc=adam; hudi[u]=d106b7a04c0d94b8a0e7624a017ead98324b57e8; hudi[i]=fec51923e58c84db4647d2b3e11fe03ec3f0c202; FreeFormat=16506; __utma=118969486.352613215.1355776933.1365626094.1367348033.12; __utmz=118969486.1355776933.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none); ZDEDebuggerPresent=php,phtml,php3
_SERVER["PATH"] /bin:/usr/bin:/usr/ucb:/usr/bsd:/usr/local/bin
_SERVER["SERVER_SIGNATURE"] no value
_SERVER["SERVER_SOFTWARE"] Apache
_SERVER["SERVER_NAME"] vmas400.vm.com
_SERVER["SERVER_ADDR"] 192.168.1.2
_SERVER["SERVER_PORT"] 80
_SERVER["REMOTE_ADDR"] 172.16.129.193
_SERVER["DOCUMENT_ROOT"] /www/zendserver/htdocs/prod
_SERVER["SERVER_ADMIN"] [no address given]
_SERVER["SCRIPT_FILENAME"] /usr/local/zendsvr/gui/html/index.php
_SERVER["DOCUMENT_NAME"] /usr/local/zendsvr/gui/html/index.php
_SERVER["REMOTE_PORT"] 14259
_SERVER["REDIRECT_QUERY_STRING"] dojo.preventCache=1367519066445
_SERVER["REDIRECT_URL"] /ZendServer/Information/Phpinfo
_SERVER["GATEWAY_INTERFACE"] CGI/1.1
_SERVER["SERVER_PROTOCOL"] HTTP/1.1
_SERVER["REQUEST_METHOD"] GET
_SERVER["QUERY_STRING"] dojo.preventCache=1367519066445
_SERVER["REQUEST_URI"] /ZendServer/Information/Phpinfo?dojo.preventCache=1367519066445
_SERVER["SCRIPT_NAME"] /ZendServer/index.php
_SERVER["DOCUMENT_URI"] /ZendServer/index.php
_SERVER["RULE_FILE"] conf/httpd.conf
_SERVER["PHP_SELF"] /ZendServer/index.php
_SERVER["REQUEST_TIME"] 1367519066
EDIT
SOLUTION
The solution was to change the user profile to use CCSID 37, and the server jobs to use CCSID 37. We will make a manual change so that when the jobs are restarted, they do not revert.
The conversion can happen automatically if the IBM side is configured properly. Have the IBM admin check the system value QCCSID. If it's set to 65535, that's why no translation is taking place. 65535 says that all the data on the system is binary and should never be translated. There is a hierarchy of CCSIDs. It starts at the system level with QCCSID, moves down to the user profile and finally down to the individual table. This is to handle systems where multiple languages are in use.
The main reason systems are at 65535 is because when the distant ancestor of the current midrange machines was deployed, there was a single language; one EBCDIC, and when multiple languages were introduced, the default language was set to 'do not translate'.
EBCDIC is no longer a single character set. There is one encoding for each language. US English is CCSID(37). If it turns out that CCSID is the issue, have the admin create a test user profile with the proper CCSID and try that.
EDIT 1: I just did a test on my machine.
EDIT 2: Added a literal to the returned columns.
<?php
//Establish connection to database
$host = "midrange";
$conn = db2_connect ($host, user, pass);
?>
<table width="75%" border="1" cellspacing="1" cellpadding="1" bgcolor="#eeeeee">
<tr>
<td><b>Name</b></td>
<td><b>Email</b></td>
<td><b>3rd column</b></td>
</tr>
<?php
$query = 'Select name, email, \'Markdown\' "THIRD" from table';
//Execute query
$queryexe = db2_exec($conn, $query) ;
//Fetch results
while(db2_fetch_row($queryexe)) {
$name = db2_result($queryexe, 'NAME');
$email = db2_result($queryexe, 'EMAIL');
$const = db2_result($queryexe, 'THIRD');
//Put the results in an HTML table.
print("<tr bgcolor=#ffffff>\n");
print("<td>$name</td>\n");
print("<td>$email</td>\n");
print("<td>$const</td>\n");
print("</tr>\n");
}
?>
</table>
All my tables are CCSID(37) IBM i 7.1.
phpinfo() reports IBM_DB2 1.9.0, PHP 5.3.3 I note that I have iconv support enabled and my server CCSID is 819 - US ASCII.
Edit 3: very partial phpinfo()
ibm_db2
IBM DB2, Cloudscape and Apache Derby support enabled
Module release 1.9.0
Module revision $Revision: 297218 $
Binary data mode (ibm_db2.binmode) DB2_BINARY
DB2 instance name (ibm_db2.instance_name) no value
iconv
iconv support enabled
iconv implementation IBM iconv
iconv library version unknown
Directive Local Value Master Value
iconv.input_encoding ISO8859-1 ISO8859-1
iconv.internal_encoding ISO8859-1 ISO8859-1
iconv.output_encoding ISO8859-1 ISO8859-1
Environment
Variable Value
ZendEnablerConfig /www/zendsvr/conf/fastcgi.conf
PHPRC /usr/local/ZendSvr/etc/
PHP_FCGI_CHILDREN 5
PHP_FCGI_MAX_REQUESTS 0
CCSID 819
LANG en_US
INSTALLATION_UID 20101215125734236656
LIBPATH /usr/local/ZendSvr/lib
DB2NOEXITLIST TRUE
PHP Variables
Variable Value
_REQUEST["TJE"] no value
_REQUEST["TE3"] no value
_REQUEST["ZDEDebuggerPresent"] php,phtml,php3
_COOKIE["TJE"] no value
_COOKIE["TE3"] no value
_COOKIE["ZDEDebuggerPresent"] php,phtml,php3
_SERVER["ZendEnablerConfig"] /www/zendsvr/conf/fastcgi.conf
_SERVER["PHPRC"] /usr/local/ZendSvr/etc/
_SERVER["PHP_FCGI_CHILDREN"] 5
_SERVER["PHP_FCGI_MAX_REQUESTS"] 0
_SERVER["CCSID"] 819
_SERVER["LANG"] en_US
_SERVER["INSTALLATION_UID"] 20101215125734236656
_SERVER["LIBPATH"] /usr/local/ZendSvr/lib
_SERVER["DB2NOEXITLIST"] TRUE
_SERVER["FCGI_ROLE"] RESPONDER
_SERVER["SCRIPT_URL"] /hello.php
_SERVER["QIBM_USE_DESCRIPTOR_STDIO"] Y
_SERVER["HTTP_USER_AGENT"] Mozilla/5.0 (Windows NT 6.1; rv:20.0) Gecko/20100101 Firefox/20.0
_SERVER["HTTP_ACCEPT"] text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
_SERVER["HTTP_ACCEPT_LANGUAGE"] en-US,en;q=0.5
_SERVER["HTTP_ACCEPT_ENCODING"] gzip, deflate
_SERVER["HTTP_DNT"] 1
_SERVER["HTTP_CONNECTION"] keep-alive
_SERVER["HTTP_PRAGMA"] no-cache
_SERVER["HTTP_CACHE_CONTROL"] no-cache
_SERVER["PATH"] /bin:/usr/bin:/usr/ucb:/usr/bsd:/usr/local/bin
_SERVER["SERVER_SIGNATURE"] no value
_SERVER["SERVER_SOFTWARE"] Apache
_SERVER["DOCUMENT_ROOT"] /www/zendsvr/htdocs
_SERVER["SERVER_ADMIN"] [no address given]
_SERVER["SCRIPT_FILENAME"] /www/zendsvr/htdocs/hello.php
_SERVER["DOCUMENT_NAME"] /www/zendsvr/htdocs/hello.php
_SERVER["REMOTE_PORT"] 54747
_SERVER["GATEWAY_INTERFACE"] CGI/1.1
_SERVER["SERVER_PROTOCOL"] HTTP/1.1
_SERVER["REQUEST_METHOD"] GET
_SERVER["QUERY_STRING"] no value
_SERVER["REQUEST_URI"] /hello.php
_SERVER["SCRIPT_NAME"] /hello.php
_SERVER["DOCUMENT_URI"] /hello.php
_SERVER["RULE_FILE"] conf/httpd.conf
_SERVER["PHP_SELF"] /hello.php
_SERVER["REQUEST_TIME"] 1367514482
Edit 4: How to make server jobs CCSID(37)
There are several ways to get the server jobs to run US English. It's an admin decision as to which is lowest impact on the overall server operation. I set my US English-only system to go to QCCSID 37 over a IPL and have seen no issues.
CHGSYSVAL QCCSID 37 - This will set the entire server to US English. Restart the Apache server to take effect.CHGUSRPRF QTMHHTTP CCSID(37) - This will set all of the HTTP server jobs to US English. Restart the Apache server to take effect.CgiConvMode EBCDIC and DefaultNetCCSID 819 and CGIJobCCSID 37 Restart the Apache server to take effect. See CGI Data Conversions for the details. This will set one Apache server's jobs to US English.CHGPF ... CCSID(37) - This will set the file to US English. Need to alter them all.This isn't intended to be all inclusive. More like a rundown of how the hierarchy fits together.