Linux configure unixodbc access ms SQL Server description

 

1. Install unixodbc

Use yum for installation:

Linux uses a CD to build a local Yum Server

Http://blog.csdn.net/tianlesoftware/article/details/7302358

 

[Root @ Rac1 MNT] # Yum install unixodbc

[Root @ Rac1 MNT] # Yum install unixodbc-devel

 

Run the iSQL command to install unixodbc. You can use this command to verify the configuration of unixodbc.

 

[Root @ Rac1/] # iSQL -- version

Unixodbc 2.2.14

[Root @ Rac1/] # Which iSQL

/Usr/bin/iSQL

[Root @ Rac1/] # iSQL

 

**************************************** ******

* Unixodbc-iSQL *

**************************************** ******

* Syntax *

**

* ISQL DSN [uid [PWD] [Options] *

**

* Options *

**

*-B batch. (no prompting etc )*

*-DX delimit columns with x *

*-X0xxx delimit columns with XX, where *

* X is in Hex, ie 0x09 is tab *

*-W wrap results in an HTML table *

*-C column names on first row .*

* (Only used when-d )*

*-Mn limit column display width to N *

*-V verbose .*

*-Lx set locale to x *

*-Q wrap char fields in dquotes *

*-3 Use ODBC 3 CILS *

*-N use New Line Processing *

* -- Version *

**

* Commands *

**

* Help-list tables *

* Help table-list columns in table *

* Help-list all help options *

**

* Examples *

**

* ISQL webdb myid mypwd-W <my. SQL *

**

* Each line in my. SQL must contain *

* Exactly 1 SQL command failed t for *

* Last line which must be blank (unless *

*-N option specified ).*

**

* Please visit ;*

**

* Http://www.unixodbc.org *

* Pharvey@codebydesign.com *

* Nick@easysoft.com *

**************************************** ******

 

[Root @ Rac1/] #

 

2. Install freetds

Freetds official website address: http://www.freetds.org/

 

Freetds is a setof libraries for UNIX and Linux that allows your programs to natively talk tomicrosoft SQL Server and Sybase databases.

-- Freetds is a collection of UNIX and Linux libraries. freetds can be used to connect to Microsoft SQL Server and Sybase dB.

 

2.1 Download freetds:

[Root @ Rac1 ~] # Wget http://ibiblio.org/pub/Linux/ALPHA/freetds/current/freetds-current.tgz

-- 10:14:43 -- http://ibiblio.org/pub/Linux/ALPHA/freetds/current/freetds-current.tgz

Resolving ibiblio.org... 152.19.134.40

Connecting toiblio.org | 152.19.134.40 |: 80... connected.

HTTP request sent, awaiting response... 200ok

Length: 2192516 (2.1 m) [application/X-gzip]

Saving to: â € your freetds-current. tgz â €

 

100% [============================================== ==============================>] 2,192,516 43.7 K/s in 37 S

 

10:15:21 (58.5 kb/s)-â € freetds-current. tgz â € saved [2192516/2192516]

 

 

2.2 install freetds:

For freetds installation help, refer:

Unixodbc-ms SQL Server/PHP

Http://www.unixodbc.org/doc/FreeTDS.html

 

[Root @ Rac1 ~] # Tar xzf freetds-current.tgz

 

-- View the help documentation of configure:

[Root @ Rac1 freetds-0.92.dev.20120305] #./configure -- Help

 

-- Methods provided on the official website:
#./Configure -- With-tdsver = 8.0 -- With-unixodbc =/usr/local
# Make
# Make install

 

Note:

If unixodbc is installed in source code,-with-unixodbc =/usr/local
This option is not required if unixodbc is installed at rpm.

 

Our unixodbc is installed through the RPM package, so here we omit this option and directly configure.

 

#./Configure -- With-tdsver = 8.0 -- prefix =/usr/local/freetds -- enable-msdblib -- With-GNU-LD -- enable-shared -- enable-static

-- Configure is successful this time. Install freetds in the/usr/local directory.

 

Then make:

# Make
# Make install

 

2.3 configure Environment Variables

Configure the path of the freetds library file to the LD_LIBRARY_PATH parameter:
$ Export LD_LIBRARY_PATH = $ LD_LIBRARY_PATH:/usr/local/freetds/lib /:

Set the bin path
$ VI/root/. bashrc
Add the following content:
Export freetds =/usr/local/freetds
Export $ Path = "$ path: $ freetds/bin"

Export LD_LIBRARY_PATH = $ LD_LIBRARY_PATH:/usr/local/freetds/lib

 

3. Configure unixodbc and freetds

 

3.1 register freetds driver with unixodbc

Modify the/etc/odbcinst. ini (or/usr/local/etc/odbcinst. INI) file and add the following content:

 

# Driver from freetds package
# Setup from freetds package
[Freetds]
Description = ODBC of freetds for ms SQL 2008
Driver =/usr/local/lib/libtdsodbc. So
Fileusage = 1

 

3.2 add access information pointing to a specific database in the freetds configuration file

 

Modify the/usr/local/etc/freetds. conf file and add the following content:
[Mysqlserver]

Host = 192.168.3.211

Port = 1433

TDS version = 8.0

Client charset = UTF-8

 

Note:

Client charset, a character set that can be recognized by the client software, must be greater than or equal to the character set used by the server database.

Since the UTF-8 is the superset of the character set, it is always feasible to set it to the UTF-8. In addition, the client charset of freetds cannot be set to utf16.

 

3.3 Modify/etc/ODBC. ini (if ODBC is installed from the source code,/usr/local/etc/ODBC. INI)

 

Add the following content to/etc/ODBC. ini:

[ODBC data sources]
Test1dsn = my first test DSN
Test2dsn = my second test DSN

 

[Test1dsn]; DSN name
Driver =/usr/local/lib/libtdsodbc. So
Description = my first test DSN
Trace = No
Servername = mysqlserver; defined in freetds. conf
Database = mytestdb; Database Name

 

[Test2dsn]
Driver =/usr/local/lib/libtdsodbc. So
Description = my second test DSN
Trace = No
Server = 192.168.3.211; you can directly write the access information of the database server.
Database = Dave
Port = 1433
Tds_version = 8.0

 

[Default]
Driver =/usr/local/freetds/lib/libtdsodbc. So

 

Note:

There are two configuration methods to access ms SQL Server through freetds with unixodbc.

(1) write the server information in the configuration file $ prefix/etc/freetds of freetds. conf, while/etc/ODBC. use servername in ini to point to freetds. the DSN.
In the above example, [test1dsn]

(2) write the server information in/etc/ODBC. ini. [test2dsn] in the above example.

Note that the keywords are different. For example, the TDS version in freetds. conf is tds_version in/etc/ODBC. ini.

 

Method (2) is relatively simple, but there are only a few keywords to control freetds. For other features of freetds, use the default configuration of freetds.

Method (1) Although complicated, freetds can be controlled in more detail. For example, you can specify the character set of the client. recommended method (1) for configuration.

 

Iv. Test

Whether using client software or programming to access the database, there are three parameters: DSN, username, and password.

For example,
DSN = test1dsn or test2dsn
Username = somename,
Password = somepasswd,

 

That means accessing the ms SQL Server library Dave at 192.168.3.211. the character set of the query result is UTF-8.

A universal GUI Database Connection Client provided by unixodbc is datamanager.

 

-- Test dsn2

[Root @ Rac1 freetds-0.91RC2] # iSQL-V test2dsndave Dave

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

| Connected! |

|

| SQL-statement |

| Help [tablename] |

| Quit |

|

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

SQL> select * from Dave;

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

| ID | Name |

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

| 1 | 1 |

| 1 | 1 |

| 1 | 1 |

| 1 | 1 |

| 1 | 1 |

| 1 | 1 |

| 1 | 1 |

| 1 | 1 |

| 1 | 1 |

| 1 | 1 |

| 1 | 1 |

| 1 | 1 |

| 2 | 2 |

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

Sqlrowcount returns 13

13 rows fetched

SQL>

 

-- Test dsn1

[Root @ Rac1 freetds-0.91RC2] # iSQL-vtest1dsn Dave

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

| Connected! |

|

| SQL-statement |

| Help [tablename] |

| Quit |

|

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

 

 

V. Character Set:

Freetds can automatically identify the charset on the server. Therefore, freetds requires you to set the charset on the client. This is the charset that the client application expects to use for the data obtained from freetds.

Once the client charset is set, freetds converts data from the server charset <--> client charset.

If both client applications have access to the same MS sqlserver, but unfortunately the character sets accepted by both client applications are UTF-8 and gb2312. then the solution is in freetds. set two sets of datasource in Conf. Their server settings are the same, but clientcharset is set to UTF-8 and gb2312. in ODBC. in INI, you also set two different DSN groups to point to the datasource groups. in the end, the two client programs use their corresponding DSN.

 

 

Sorted from:

Http://www.humin.com.cn/linux-unixodbc-freetds-ms-sql-server.html

 

PS:

This method can solve the Chinese garbled characters of rstudio. If rstudio is used directly in windows, the Chinese characters are garbled, And the rstudio SERVER + unixodbc in Linux is used, you can solve Chinese garbled characters.

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940