PHP / SQL - Convert EBCDIC to ASCII

2019-07-31 01:41发布

站内问答 / PHP
983 2 5

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.

2条回答
唯我独甜
2楼-- · 2019-07-31 02:12

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.

  1. CHGSYSVAL QCCSID 37 - This will set the entire server to US English. Restart the Apache server to take effect.
  2. CHGUSRPRF QTMHHTTP CCSID(37) - This will set all of the HTTP server jobs to US English. Restart the Apache server to take effect.
  3. Reconfigure the Apache server. Set 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.
  4. 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.

查看更多
0人赞 添加讨论(0) 举报
何必那么认真
3楼-- · 2019-07-31 02:31

I have discovered a better way to do this... Using the TRANSLATE or ForceTranslation keyword in your DSN. For example:

odbc:DRIVER={iSeries Access ODBC Driver};SYSTEM=as400.myserver.local;TRANSLATE=1;

You don't have to change any files, system settings or use CAST in your queries.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)