PHP / SQL - Convert EBCDIC to ASCII -

we have php server code, executing sql statements against our iseries midrange.

here simplified version of sql query

select 'regular' "sales_type", sum(sales_type1) "sales" salestable 

the query executes fine, problem when using static field/value such 'sometext' "title" , results come in php, not in desired format

string(7) "م�����"  

to connect system , retrieve 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 on php version 5.2.17

our iseries v7r1m0

a solution convert server side php or in sql query self great.

thanks!

---

edit

from bucks suggestion have changed user profile ccsid 37 instead of 65535

now (below) bit closer...

string(7) "Ù…‡¤“™" 

is possibly because changed user? system, job or table need changed too?

---

edit 2

here 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, 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"]   _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 change user profile use ccsid 37, , server jobs use ccsid 37. make manual change when jobs restarted, not revert.

the conversion can happen automatically if ibm side configured properly. have ibm admin check system value qccsid. if it's set 65535, that's why no translation taking place. 65535 says data on system binary , should never translated. there hierarchy of ccsids. starts @ system level qccsid, moves down user profile , down individual table. handle systems multiple languages in use.

the main reason systems @ 65535 because when distant ancestor of current midrange machines deployed, there single language; 1 ebcdic, , when multiple languages introduced, default language set 'do not translate'.

ebcdic no longer single character set. there 1 encoding each language. english ccsid(37). if turns out ccsid issue, have admin create test user profile proper ccsid , try that.

edit 1: did test on machine.

edit 2: added literal returned columns.

<?php   //establish connection 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" 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 results in 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 tables ccsid(37) ibm 7.1.
phpinfo() reports ibm_db2 1.9.0, php 5.3.3 note have iconv support enabled , server ccsid 819 - ascii.

edit 3: partial phpinfo()

ibm_db2 ibm db2, cloudscape , 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"]   _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 make server jobs ccsid(37)

there several ways server jobs run english. it's admin decision lowest impact on overall server operation. set english-only system go qccsid 37 on ipl , have seen no issues.

1. chgsysval qccsid 37 - set entire server english. restart apache server take effect.
2. chgusrprf qtmhhttp ccsid(37) - set of http server jobs english. restart apache server take effect.
3. reconfigure apache server. set cgiconvmode ebcdic , defaultnetccsid 819 , cgijobccsid 37 restart apache server take effect. see cgi data conversions details. set 1 apache server's jobs english.
4. chgpf ... ccsid(37) - set file english. need alter them all.

this isn't intended inclusive. more rundown of how hierarchy fits together.