opens.kr

리눅스 unixODBC FreeTDS 설치 (MSSQL)

리눅스에서 MSSQL 서버 접근할려고 MS에서 제공해주는 라이브러리 설치했지만 이래저래

문제가 많아서 FreeTDS를 사용하기로 결정했다.

리눅스 SQL Server(MSSQL) ODBC 설치 - http://www.opens.kr/34

Linux uninxODBC SQL Server Native Client 11.0 한글문제 - http://www.opens.kr/35

FreeTDS Yum 간단 설치 방법

1) 저장소 등록 ( 다른곳을 이용해도 무관함 )

$ rpm -Uvh http://mirror.webtatic.com/yum/el6/latest.rpm

2) Yum 설치

$ yum install freetds unixODBC

=================================================================================================================

Package Arch Version Repository Size

=================================================================================================================

Installing:

freetds x86_64 0.91-2.el6 webtatic 567 k

unixODBC x86_64 2.2.14-12.el6_3 base 378 k

Installing for dependencies:

libtool-ltdl x86_64 2.2.6-15.5.el6 base 44 k

Transaction Summary

=================================================================================================================

Install 3 Package(s)

3) tsql을 이용한 접속테스트

$ tsql -H 192.168.0.0 -p 1433 -U username -P userpassword

참조 가이드 : http://www.freetds.org/userguide/confirminstall.htm

4) ODBC에 FreeTDS등록

vi /etc/odbcinst.ini

----------------------------------

[FreeTDS]

Description = FreeTDS Driver

Driver = /usr/lib64/libtdsodbc.so.0

Setup = /usr/lib64/libtdsS.so.2

FileUsage = 1

CPTimeout = 5

CRReuse = 5

----------------------------------

5) isql을 이용한 접속 테스트

vi /etc/odbc.ini

----------------------------------

[MSSQLTEST]

driver=FreeTDS

server=192.168.0.0

port=1433

database=DBNAME

client_charset = UTF-8

tds_version = 8.0

----------------------------------

$ isql -v MSSQLTEST username userpassword

+---------------------------------------+

| Connected!

|

| sql-statement

| help [tablename]

| quit

|

+---------------------------------------+

※ ODBC 정보확인은 아래와 같이

$ odbcinst -j

unixODBC 2.2.14

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /root/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

※ PHP PDO나 타 개발언어에서 사용시

$ vi /etc/freetds.conf의 [global]에 기본 tds_version 버젼과 client_charset 지정해주는 게 좋을 듯하다.

-------------------------------

[global]

tds version = 8.0

client charset = UTF-8

text size = 64512

-------------------------------

※ 리눅스 PHP PDO dblib FreeTDS 사용 (MSSQL) - http://www.opens.kr/37

저작자표시 비영리 변경금지

Posted by 시니^^

Linux uninxODBC SQL Server Native Client 11.0 한글문제

MSSQL에서 UTF-8 텍스트를 데이터를 넣을때 NVARCHAR타입으로 해서

INSERT INTO table ( text ) value ( N'한글테스트' ) 이런식으러 넣는 다

그런데 리눅스에서 PDO에서 uninxODBC SQL Server Native Client 11.0 넣으면 제대로 들어가지 않고 깨진다

그래서 아래 처럼 UTF-16LE로 변환해서 hexcode로 넣으니까 잘된다..

$sName = iconv('utf-8','UTF-16LE',$sName);
$sName = bin2hex($sName);
$sQuery = "INSERT INTO table (name) VALUES (  0x{$sName} )";

그리고 값을 가져올때 가끔식 일부데이터는 문제가 또 발생한다 ㅠㅠ

그래서 CAST 로 한번 변환해주니까 잘된다..

참가로 NVARCHAR(MAX)로 하니까 글이 전혀 나오지 않아서 그냥 NTEXT로 꽁수를 부렸다..

$sQuery = "SELECT CAST(name AS NTEXT) AS name FROM table ";

그리고 참고로 PHP5.3.3 버전에서 PDO ODBC nvarchar(max)데이터 어느 정도 이상되면 깨지는 현상이 나오는듯하다.

그래서 PHP5.5.12 / PHP5.4.28 / PHP5.3.28에서는 CAST해서 하면 큰 문제가 없다.

아무튼 Linux uninxODBC SQL Server Native Client 11.0 이래저래 문제가 좀 있는듯하다.

그래서 개인적으로는 Linux에서 MSSQL 연결하는 경우 SQL Server Native Client 보다는 FreeTDS를 사용을 권하고 싶다.

저작자표시 비영리 변경금지

Posted by 시니^^

unixODBC SQL Server Native Client 11.0 설치 (MSSQL)

MS사이트 : http://www.microsoft.com/en-us/download/details.aspx?id=28160

1) 파일다운로드 ( centos6 64bit의 경우 Linux6을 선택 다운로드 안될때 MS사이트 URL확인 )

$ wget http://download.microsoft.com/download/6/A/B/6AB27E13-46AE-4CE9-AFFD-406367CADC1D/Linux6/sqlncli-11.0.1790.0.tar.gz

2) 압축풀고 build_dm실행

$ tar xvfz sqlncli-11.0.1790.0.tar.gz
$ cd sqlncli-11.0.1790.0

$ ./build_dm.sh //실행후 나오는 디렉토리 기준으로 make 실행
Enter 'YES' to have this script continue: YES
Verifying processor and operating system ................................... OK
Verifying wget is installed ................................................ OK
Verifying tar is installed ................................................. OK
Verifying make is installed ................................................ OK
Downloading unixODBC 2.3.0 DriverManager ................................... OK
Unpacking unixODBC 2.3.0 DriverManager ..................................... OK
Configuring unixODBC 2.3.0 DriverManager ................................... OK
Building unixODBC 2.3.0 DriverManager
...................................... OK
Build of the unixODBC 2.3.0 DriverManager complete.
Run the command 'cd /tmp/unixODBC.32631.28583.22057/unixODBC-2.3.0; make install' to install the driver manager.
PLEASE NOTE THAT THIS WILL POTENTIALLY INSTALL THE NEW DRIVER MANAGER OVER ANY
EXISTING UNIXODBC DRIVER MANAGER. IF YOU HAVE ANOTHER COPY OF UNIXODBC INSTALLED,
THIS MAY POTENTIALLY OVERWRITE THAT COPY.

3) 위에 tmp 경로에서 make install 실행

$ cd /tmp/unixODBC.32631.28583.22057/unixODBC-2.3.0
$ make install
touch /etc/odbcinst.ini
touch /etc/odbc.ini
mkdir -p /etc/ODBCDataSources
/usr/bin/odbc_config --header > /usr/include/unixodbc_conf.h
make[2]: Leaving directory `/tmp/unixODBC.32631.28583.22057/unixODBC-2.3.0'
make[1]: Leaving directory `/tmp/unixODBC.32631.28583.22057/unixODBC-2.3.0'

4) install 실행
$ cd /root/sqlncli-11.0.1790.0
$ ./install.sh verify
Microsoft SQL Server ODBC Driver V1.0 for Linux Installation Script
Copyright Microsoft Corp.
Starting install for Microsoft SQL Server ODBC Driver V1.0 for Linux
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .... NOT FOUND
Install log created at /tmp/sqlncli.18759.30552.14202/install.log.
One or more steps may have an *. See README for more information regarding
these steps.

$ ./install.sh install
Enter YES to accept the license or anything else to terminate the installation: YES
Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .... NOT FOUND
Microsoft SQL Server ODBC Driver V1.0 for Linux files copied ................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft SQL Server ODBC Driver V1.0 for Linux registered ........... INSTALLED
Install log created at /tmp/sqlncli.27629.13209.11572/install.log.
One or more steps may have an *. See README for more information regarding
these steps.

$ vi /etc/odbcinst.ini //odbc추가확인
[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
Threading=1
UsageCount=1

5) 최종 설치 결과확인

$ ./install.sh verify

Microsoft SQL Server ODBC Driver V1.0 for Linux Installation Script
Copyright Microsoft Corp.

Starting install for Microsoft SQL Server ODBC Driver V1.0 for Linux

Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft SQL Server ODBC Driver V1.0 for Linux already installed .... INSTALLED

6) isql 이용한 접속테스트

$ vi /etc/odbc.ini

-------------------------

[MSSQLTEST]
driver=SQL Server Native Client 11.0
server=192.168.0.0
port=1433
database=DBNAME

-------------------------

$ isql -v MSSQLTEST userid userpassword

※ PHP에는 PDO ODBC에는 odbcinst.ini에 등록된 이름으로 odbc.ini처럼 지정해주면됨
=> odbc:DRIVER=SQL Server Native Client 11.0;Server=192.168.0.0;port=1433;database=dbname;

※ C컴파일에러시 gcc설치할것!!
view /tmp/unixODBC.19411.11370.8462/build_dm.log
yum install gcc

※ ODBC 정보확인은 아래와 같이

$ odbcinst -j

unixODBC 2.2.14

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /root/.odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

저작자표시 비영리 변경금지

Posted by 시니^^

opens.kr

리눅스 unixODBC FreeTDS 설치 (MSSQL)

Linux uninxODBC SQL Server Native Client 11.0 한글문제

unixODBC SQL Server Native Client 11.0 설치 (MSSQL)

카테고리

공지사항

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바