해결과제
PHP로 개발할 홈페이지에서 원격지 mssql(SQL2000) 서버에 접속해 DATA 를 얻어와야 함.
중요이슈
- 웹 호스팅 서버에서 가능한지 여부
- 접속 된 후 가져온 데이터가 한글이 깨지는 문제 해결
진행순서
- PHP, MYSQL 환경의 웹호스팅 신청
- mssql 사용할 수 있는 환경 추가 신청
- adodb 모듈 다운받아 업로드
- 접속 (접속 테스트 중 아래와 같은 오류로 인해 혼란을 겪음)
- WARNING! Some character(s) could not be converted into client's character set. Unconverted bytes were changed to question marks ('?'). (severity 16) ==> 접속은 정상!! 하지만, 클라이언트 캐릭터 셋이 맞지 않아 생긴 문제임을 몰라 접속 정보가지고 몇 시간을 씨름 하였음
- 특히, 테스트 삼아 작성한 쿼리의 데이터가 많아 기대한 내용이 출력되지 않아 위와 같은 혼란을 겪었으니 보다 명확한 쿼리로 테스트 해볼 것을 권함
- 데이터 가져옴
- 가져온 데이터중 한글이 깨지는 문제 발생
- 원인은 위 4-1 에서 발생한 warning 때문인데, 웹 문서들을 오랜시간 찾아 본 결과 FreeDTS 라는 모듈을 설치해서 해결할 수 있다는 내용이 많음
- 웹 호스팅이라는 종속적인 상황상 모듈설치를 하지 않고 해결할수 있는 방법 모색하던중
- 각 필드의 데이터를 binary 로 변환해서 받고 iconv 를 이용해 인코딩 방식을 변경하는 방법으로 해결함
"SELECT convert(varbinary(20), ENGINEER), convert(varbinary(200), CUSTNM), PHONENO, MOBILENO, SEQ FROM VIEW_GMR_RECOVERTY_STATUS_RETRIEVE where SEQ like '%1121%'";
mb_convert_encoding($field_value,"UTF-8", "CP949"); - 다양한 캐릭터 셋을 테스트 해야 할 경우가 많으므로 루프를 돌려 테스트해 볼 것을 권함 ==> 한 개씩 테스트 할 때 해결하지 못하다가 여러 캐릭터 셋을 배열에 넣고 mb_convert_encoding 호출되는 for 루프를 돌려 바로 해결 하였음
<?php
echo("
<html>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
<title>:: 원격지 MS-SQL DATA 얻기</title>
</head>
<body>
");
<html>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
<title>:: 원격지 MS-SQL DATA 얻기</title>
</head>
<body>
");
$db_host = "원격지호스트IP또는URL:포트번호"; // 포트번호는 기본이면 : 과 함께 생략
$db_name = "DB이름";
$db_user = "사용자ID";
$db_passwd = "패스워드";
$db_name = "DB이름";
$db_user = "사용자ID";
$db_passwd = "패스워드";
include "./adodb/adodb.inc.php";
$db = @ADONewConnection("mssql"); # 예 'mysql' 또는 'postgres'
$db->debug = true; // 디버깅용
@$db->Connect($db_host, $db_user, $db_passwd, $db_name); // 접속 ( @ 를 제거하면 캐릭터 셋이 맞지 않는다는 오류가 나올 수 있음, 접속은 정상 )
@$rs = $db->Execute("SELECT convert(varbinary(20), 한글값필드명1), convert(varbinary(200), 한글값필드명2), 기타필드명들... FROM 테이블이름 where SEQ like '%1121%'");
$TTT = $rs->GetRows();
$db = @ADONewConnection("mssql"); # 예 'mysql' 또는 'postgres'
$db->debug = true; // 디버깅용
@$db->Connect($db_host, $db_user, $db_passwd, $db_name); // 접속 ( @ 를 제거하면 캐릭터 셋이 맞지 않는다는 오류가 나올 수 있음, 접속은 정상 )
@$rs = $db->Execute("SELECT convert(varbinary(20), 한글값필드명1), convert(varbinary(200), 한글값필드명2), 기타필드명들... FROM 테이블이름 where SEQ like '%1121%'");
$TTT = $rs->GetRows();
// 변경해 볼 캐릭터 셋 배열로 추가
$chars_array = array("UTF-16BE", "UTF-16LE", "ISO-10646-UCS-2", "CP949", "EUC-KR", "ASCII", "iso-8859-1", "UCS-2", "UCS-2LE", "UCS-4LE", "UCS-2BE");
$chars_array = array("UTF-16BE", "UTF-16LE", "ISO-10646-UCS-2", "CP949", "EUC-KR", "ASCII", "iso-8859-1", "UCS-2", "UCS-2LE", "UCS-4LE", "UCS-2BE");
for ($i=0,$cnt=sizeof($chars_array); $i<$cnt; $i++) {
// 정상적으로 변환되는 캐릭터 셋이 무엇인지 출력 해 봄
echo("{$chars_array[$i]} : ") . mb_convert_encoding($TTT[0][0],"UTF-8", $chars_array[$i]) . " / ";
echo("{$chars_array[$i]} : ") . mb_convert_encoding($TTT[0][1],"UTF-8", $chars_array[$i]) . " / {$TTT[0][2]} / {$TTT[0][3]} / {$TTT[0][4]}<br>";
}
echo("{$chars_array[$i]} : ") . mb_convert_encoding($TTT[0][0],"UTF-8", $chars_array[$i]) . " / ";
echo("{$chars_array[$i]} : ") . mb_convert_encoding($TTT[0][1],"UTF-8", $chars_array[$i]) . " / {$TTT[0][2]} / {$TTT[0][3]} / {$TTT[0][4]}<br>";
}
echo("
</body>
</html>
");
?>
</body>
</html>
");
?>
Character Sets
charset=
Some Basic Ones
- charset=big5
- Chinese Traditional (Big5)
- charset=euc-kr
- Korean (EUC)
- charset=iso-8859-1
- Western Alphabet
- charset=iso-8859-2
- Central European Alphabet (ISO)
- charset=iso-8859-3
- Latin 3 Alphabet (ISO)
- charset=iso-8859-4
- Baltic Alphabet (ISO)
- charset=iso-8859-5
- Cyrillic Alphabet (ISO)
- charset=iso-8859-6
- Arabic Alphabet (ISO)
- charset=iso-8859-7
- Greek Alphabet (ISO)
- charset=iso-8859-8
- Hebrew Alphabet (ISO)
- charset=koi8-r
- Cyrillic Alphabet (KOI8-R)
- charset=shift-jis
- Japanese (Shift-JIS)
- charset=x-euc
- Japanese (EUC)
- charset=utf-8
- Universal Alphabet (UTF-8)
- charset=windows-1250
- Central European Alphabet (Windows)
- charset=windows-1251
- Cyrillic Alphabet (Windows)
- charset=windows-1252
- Western Alphabet (Windows)
- charset=windows-1253
- Greek Alphabet (Windows)
- charset=windows-1254
- Turkish Alphabet
- charset=windows-1255
- Hebrew Alphabet (Windows)
- charset=windows-1256
- Arabic Alphabet (Windows)
- charset=windows-1257
- Baltic Alphabet (Windows)
- charset=windows-1258
- Vietnamese Alphabet (Windows)
- charset=windows-874
- Thai (Windows)
A Longer List
- Arabic (ASMO 708)
- ##charset=ASMO-708
- Arabic (DOS)
- ##charset=DOS-720
- Arabic (ISO)
- ##charset=iso-8859-6
- Arabic (Mac)
- ##charset=x-mac-arabic
- Arabic (Windows)
- ##charset=windows-1256
- Baltic (DOS)
- ##charset=ibm775
- Baltic (ISO)
- ##charset=iso-8859-4
- Baltic (Windows)
- ##charset=windows-1257
- Central European (DOS)
- ##charset=ibm852
- Central European (ISO)
- ##charset=iso-8859-2
- Central European (Mac)
- ##charset=x-mac-ce
- Central European (Windows)
- ##charset=windows-1250
- Chinese Simplified (EUC)
- ##charset=EUC-CN
- Chinese Simplified (GB2312)
- ##charset=gb2312
- Chinese Simplified (HZ)
- ##charset=hz-gb-2312
- Chinese Simplified (Mac)
- ##charset=x-mac-chinesesimp
- Chinese Traditional (Big5)
- ##charset=big5
- Chinese Traditional (CNS)
- ##charset=x-Chinese-CNS
- Chinese Traditional (Eten)
- ##charset=x-Chinese-Eten
- Chinese Traditional (Mac)
- ##charset=x-mac-chinesetrad
- ##charset=950
- Cyrillic (DOS)
- ##charset=cp866
- Cyrillic (ISO)
- ##charset=iso-8859-5
- Cyrillic (KOI8-R)
- ##charset=koi8-r
- Cyrillic (KOI8-U)
- ##charset=koi8-u
- Cyrillic (Mac)
- ##charset=x-mac-cyrillic
- Cyrillic (Windows)
- ##charset=windows-1251
- Europa
- ##charset=x-Europa
- German (IA5)
- ##charset=x-IA5-German
- Greek (DOS)
- ##charset=ibm737
- Greek (ISO)
- ##charset=iso-8859-7
- Greek (Mac)
- ##charset=x-mac-greek
- Greek (Windows)
- ##charset=windows-1253
- ##charset=
- Greek, Modern (DOS)
- ##charset=ibm869
- Hebrew (DOS)
- ##charset=DOS-862
- Hebrew (ISO-Logical)
- ##charset=iso-8859-8-i
- Hebrew (ISO-Visual)
- ##charset=iso-8859-8
- Hebrew (Mac)
- ##charset=x-mac-hebrew
- Hebrew (Windows)
- ##charset=windows-1255
- IBM EBCDIC (Arabic)
- ##charset=x-EBCDIC-Arabic
- IBM EBCDIC (Cyrillic Russian)
- ##charset=x-EBCDIC-CyrillicRussian
- IBM EBCDIC (Cyrillic Serbian-Bulgarian)
- ##charset=x-EBCDIC-CyrillicSerbianBulgarian
- IBM EBCDIC (Denmark-Norway)
- ##charset=x-EBCDIC-DenmarkNorway
- IBM EBCDIC (Denmark-Norway-Euro)
- ##charset=x-ebcdic-denmarknorway-euro
- IBM EBCDIC (Finland-Sweden)
- ##charset=x-EBCDIC-FinlandSweden
- IBM EBCDIC (Finland-Sweden-Euro)
- ##charset=x-ebcdic-finlandsweden-euro
- IBM EBCDIC (Finland-Sweden-Euro)
- ##charset=x-ebcdic-finlandsweden-euro
- IBM EBCDIC (France-Euro)
- ##charset=x-ebcdic-france-euro
- IBM EBCDIC (Germany)
- ##charset=x-EBCDIC-Germany
- IBM EBCDIC (Germany-Euro)
- ##charset=x-ebcdic-germany-euro
- IBM EBCDIC (Greek Modern)
- ##charset=x-EBCDIC-GreekModern
- IBM EBCDIC (Greek)
- ##charset=x-EBCDIC-Greek
- IBM EBCDIC (Hebrew)
- ##charset=x-EBCDIC-Hebrew
- IBM EBCDIC (Icelandic)
- ##charset=x-EBCDIC-Icelandic
- IBM EBCDIC (Icelandic-Euro)
- ##charset=x-ebcdic-icelandic-euro
- IBM EBCDIC (International-Euro)
- ##charset=x-ebcdic-international-euro
- IBM EBCDIC (Italy)
- ##charset=x-EBCDIC-Italy
- IBM EBCDIC (Italy-Euro)
- ##charset=x-ebcdic-italy-euro
- IBM EBCDIC (Japanese and Japanese Katakana)
- ##charset=x-EBCDIC-JapaneseAndKana
- IBM EBCDIC (Japanese and Japanese-Latin)
- ##charset=x-EBCDIC-JapaneseAndJapaneseLatin
- IBM EBCDIC (Japanese and US-Canada)
- ##charset=x-EBCDIC-JapaneseAndUSCanada
- IBM EBCDIC (Japanese katakana)
- ##charset=x-EBCDIC-JapaneseKatakana
- IBM EBCDIC (Korean and Korean Extended)
- ##charset=x-EBCDIC-KoreanAndKoreanExtended
- IBM EBCDIC (Korean Extended)
- ##charset=x-EBCDIC-KoreanExtended
- IBM EBCDIC (Multilingual Latin-2)
- ##charset=CP870
- IBM EBCDIC (Simplified Chinese)
- ##charset=x-EBCDIC-SimplifiedChinese
- IBM EBCDIC (Spain)
- ##charset=X-EBCDIC-Spain
- IBM EBCDIC (Spain-Euro)
- ##charset=x-ebcdic-spain-euro
- IBM EBCDIC (Thai)
- ##charset=x-EBCDIC-Thai
- IBM EBCDIC (Traditional Chinese)
- ##charset=x-EBCDIC-TraditionalChinese
- IBM EBCDIC (Turkish Latin-5)
- ##charset=CP1026
- IBM EBCDIC (Turkish)
- ##charset=x-EBCDIC-Turkish
- IBM EBCDIC (UK)
- ##charset=x-EBCDIC-UK
- IBM EBCDIC (UK-Euro)
- ##charset=x-ebcdic-uk-euro
- IBM EBCDIC (US-Canada)
- ##charset=ebcdic-cp-us
- IBM EBCDIC (US-Canada-Euro)
- ##charset=x-ebcdic-cp-us-euro
- Icelandic (DOS)
- ##charset=ibm861
- Icelandic (Mac)
- ##charset=x-mac-icelandic
- ISCII Assamese
- ##charset=x-iscii-as
- ISCII Bengali
- ##charset=x-iscii-be
- ISCII Devanagari
- ##charset=x-iscii-de
- ISCII Gujarathi
- ##charset=x-iscii-gu
- ISCII Kannada
- ##charset=x-iscii-ka
- ISCII Malayalam
- ##charset=x-iscii-ma
- ISCII Oriya
- ##charset=x-iscii-or
- ISCII Panjabi
- ##charset=x-iscii-pa
- ISCII Tamil
- ##charset=x-iscii-ta
- ISCII Telugu
- ##charset=x-iscii-te
- Japanese (EUC)
- ##charset=euc-jp
- ##charset=x-euc-jp
- Japanese (JIS)
- ##charset=iso-2022-jp
- Japanese (JIS-Allow 1 byte Kana - SO/SI)
- ##charset=iso-2022-jp
- Japanese (JIS-Allow 1 byte Kana)
- ##charset=csISO2022JP
- Japanese (Mac)
- ##charset=x-mac-japanese
- Japanese (Shift-JIS)
- ##charset=shift_jis
- Korean
- ##charset=ks_c_5601-1987
- Korean (EUC)
- ##charset=euc-kr
- Korean (ISO)
- ##charset=iso-2022-kr
- Korean (Johab)
- ##charset=Johab
- Korean (Mac)
- ##charset=x-mac-korean
- Latin 3 (ISO)
- ##charset=iso-8859-3
- Latin 9 (ISO)
- ##charset=iso-8859-15
- Norwegian (IA5)
- ##charset=x-IA5-Norwegian
- OEM United States
- ##charset=IBM437
- Swedish (IA5)
- ##charset=x-IA5-Swedish
- Thai (Windows)
- ##charset=windows-874
- Turkish (DOS)
- ##charset=ibm857
- Turkish (ISO)
- ##charset=iso-8859-9
- Turkish (Mac)
- ##charset=x-mac-turkish
- Turkish (Windows)
- ##charset=windows-1254
- Unicode
- ##charset=unicode
- Unicode (Big-Endian)
- ##charset=unicodeFFFE
- Unicode (UTF-7)
- ##charset=utf-7
- Unicode (UTF-8)
- ##charset=utf-8
- US-ASCII
- ##charset=us-ascii
- Vietnamese (Windows)
- ##charset=windows-1258
- Western European (DOS)
- ##charset=ibm850
- Western European (IA5)
- ##charset=x-IA5
- Western European (ISO)
- ##charset=iso-8859-1
- Western European (Mac)
- ##charset=macintosh
- Western European (Windows)
- ##charset=Windows-1252
