Perl connects to SQLServer through unixODBC

This article describes three things: 1: 2: Environment configuration 3: Operation script content 1: MS has never provided SQLServerforLinux, so do not try to install SQLServer on Linux, however, you can use ODBC to connect to the SQL Server database in Windows. For WindowsServer2003, note:

This article describes three things: 1: 2: Environment configuration 3: Operation script content 1: MS has never provided SQLServer for Linux, therefore, do not try to install SQLServer on Linux, but you can connect to the Windows database in Windows through ODBC. For Windows Server 2003, note: In Windows Se

This article describes three things:

I. Description

II. Environment Configuration

Iii. Operation script content

I. Description

MS has never provided SQLServer for Linux, so do not try to install SQLServer on Linux, but you canPassODBCConnectionSQL Server database in Windows;
Note:
The SQL Server installed on Windows Server 2003 does not open port 1433. Therefore, after installing SQL Server, Windows users must install SQL Server SP3 pudding.

Ii. Install related driver and environment Configuration

If DBI is not installed, it is best to install DBI first or an error will be reported later.

DBI installation: DBI detailed information reference: http://dbi.perl.org/
1. Download the DBI package:
: Http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.620.tar.gz

Tar xzf DBI-1.620.tar.gz enters the extract package perl Makefile. PL make testmakemake install (if not the root user, sudo it)

1. Download related software unixODBC, freetds and DBD-ODBC

First, you need to download three files:
ODBC in Linux
UnixODBC-2.2.8.tar.gz (http://www.unixodbc.org)

ConnectionSQLServer or Sybase driver
Freetds-0.62.4.tar.gz (http://www.freetds.org)

ODBC module of perl
DBD-ODBC-1.12.tar.gz (http://cpan.perl.org)
Put the three downloaded files in the same directory

2. Install unixODBC

# tar vxzf unixODBC-2.2.8.tar.gz# cd unixODBC-2.2.8# ./configure --prefix=/usr/local/unixODBC --enable-gui=no# make# make install

3. Install freetds

# tar vxzf freetds-0.62.4.tar.gz# cd freetds-0.62.4# ./configure --prefix=/usr/local/freetds --with-unixodbc=/usr/local/unixODBC --with-tdsver=8.0# make# make install

4. Install DBD-ODBC
Before installation, you must set the environment variables.

# export ODBCHOME=/usr/local/unixODBC

Then start compilation and Installation

# tar vxzf DBD-ODBC-1.12.tar.gz# cd DBD-ODBC-1.12# perl Makefile.PL# make# make install

All the software has been installed now, and the next step is to configure


5. Configure freetds

# cd /usr/local/freetds# vi etc/freetds.conf

Modify the following section and remove;
Modified content:
[MyServer2k] # different drivers have different names here. Note the corresponding names during the following tests.
Host = 192.168.0.32
Port = 1433
Tds version = 8.0

[MyServer2k] indicates the name of the server used on the client, host indicates the IP address of the SQLServer server, port indicates the port, and testConnection;

The configuration items are described as follows:
Tds version: the version of the tds protocol,ConnectionUsed in the database, if not set in the Environment Variable
Therefore, the Protocol version may be 4.2, 5.0, 7.0, or 8.0.

Host: the host name or IP address of the database server.

Port: the listening port of the database server, which can take any valid port value. Generally, Sybase SQL10 used 5000 for 1433,10 and above, and Sybase SQLAnywhere 7 is 2638, for Microsoft SQL server, 1433 is used. This configuration can be rewritten by TDSPORT in the environment variable.

Initial block size: This value can only be a multiple of 512. The default value is 512. the maximum value of the Protocol block is specified,
Do not change the default configuration.

Dump file: Any valid file name that specifies the path of the dump file and opens the log.

Dump file append: yes or no to determine whether to append the file to the dump file.

Timeout: sets the maximum waiting time for processing.

Connect timeout: SetConnection.

Emulate little endian: yes or no, whether to force a large computer to communicate with the MS Server in a small way.

Client charset: Any valid iconv character set. The default value is a ISO-8859-1 that enables FreeTDS to use iconv to convert between the database server and the user program.

Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note that the client charset must be greater than or equal to the character set used by the server database.

For example, the Server database is ms SQL Server 2000 and the character set is GB2312.
So client charset can be set to GB2312 (equal to), GB18030 (greater than), UTF-8 (greater than) but cannot be set to ISO-8859-1 (less than), Shift_JIS (not equal ).
Since the UTF-8 is the superset of the character set, it is always feasible to set it to the UTF-8.
In addition, FreeTDS's client charset cannot be set to UTF16. currently, the software design is limited. its official website said that support for client charset UTF16 may be increased in future versions.

# bin/tsql -S MyServer2k -H 192.168.0.32 -p 1433 -U sa -P password1> use gameDB2> go1> select count(*) from t_ip2> go41> quit

Everything went well
The following parameters are used to describe the functions of-S MyServer2k in freetds. -H 192.168.0.32 Server IP address, and freetds. if the-S parameter is used, this parameter is optional.-p 1433 is used to specify port 1433, and-U sa is used to specify ConnectionThe username of the database.-P password is used to specify ConnectionThe password of the database.


6. Configure unixODBC

# cd /usr/local/unixODBC

Add an SQLServer driver to ODBC

# vi etc/odbcinst.ini

Write the following content:
[TDS]; driver name
Description = MS-SQLServer; Description
Driver =/usr/local/freetds/lib/libtdsodbc. so; Driver
Setup =/usr/local/freetds/lib/libtds. so; don't ask me, I don't know what this is
FileUsage = 1

Or

[FreeTDS]
Driver =/usr/local/freetds/lib/libtdsodbc. so
; Threading = 1

Save and exit

Add DSN

# Cd/usr/local/unixODBC

# vi etc/odbc.ini

Write the following content
[2, 123]; DSN name
Driver = TDS; ODBC Driver corresponds to the Driver name in odbcinst. ini.
Server = 192.168.0.32; sqlserver Server IP address or Servername = MyServer2k; defined in freetds. conf
Database = gameDB; Database to be used
Port = 1433; Port
Socket =
Option =

Stmt =

Or

[Zzq_cq]
Driver = FreeTDS
Server = 192.168.0.89
Port = 1433
TDS_Version = 7.2
Database = db
User = sa
Password = 123
Client charset = UTF-8

Save and exit to test the ODBC Connection

# bin/isql -v 123 sa password

SQL> select count (*) from t_ip
+ ---------- +
|
+ ---------- +
| 4 |
+ ---------- +
SQL> quit
OK, test PassTo test whether perl can correctly Use ODBC.
When using perl ConnectionPrepare for ODBC
Copy all ODBC shared libraries to the/usr/lib directory.

# cp /usr/local/unixODBC/lib/libodbc.* /usr/lib

If an error is reported during script operation:

Install_driver (ODBC) failed: Can't load '/usr/local/lib64/perl5/auto/DBD/ODBC. so 'for module DBD: ODBC: libodbc. so.2: cannot open shared object file: No such file or directory at/usr/lib64/perl5/DynaLoader. pm line 200.
At (eval 5) line 3
Compilation failed in require at (eval 5) line 3.
Perhaps a required shared library or dll isn' t installed where expected
At createalltable. pl line 27

Run the following command:

# Find/-name "libodbc. so"
/Usr/local/unixODBC/lib/libodbc. so

# Echo "/usr/local/unixODBC/lib/">/etc/ld. so. conf

# Ldconfig

Iii. Operation script content

# vi test.pl

The content is as follows:
#! /Usr/bin/perl
Use DBI;
$ Dbh = DBI-> connect ('dbi: ODBC: 100', 'sa ', 'Password'); // here, 123 is used for odbc. corresponding to the DSN name defined in ini
My $ something = $ dbh-> prepare ("select * from t_ip"); // The SQL statement to be executed
$ Something-> execute ();
While (@ data = $ something-> fetchrow_array () {// put the obtained content in the data list
Print "$ data [0] $ data [1] $ data [2] \ n ";

}

Resource release statement and close statement (a little different from mysql, mysql can directly disconnect ):

$ Something-> finish;

$ Dbh-> disconnect;

Only disconnect alone will report an error:

DBI: db = HASH (0x812938)-> disconnect invalidates 1 active statement handle
(Either destroy statement handles or call finish on them before disconnecting)

Note that the variable "finish" at the corresponding position of $ sh is not the variable "$ dbh ".

Otherwise, an error is reported:

Can't locate object method "finish" via package "DBI: db"

Save and exit

# perl test.pl 

PS: If you get a host and do not know its driver, you can go to/usr/local/unixODBC/etc to view the configuration files odbcinst. ini and odbc. ini.

References:

Http://space.itpub.net/82392/viewspace-238018

Http://opsyun.blog.51cto.com/1037036/713910

PS :!!!!! Note that in linux, when using perl to extract data from sqlserver, the encoding of the linux system must be the same as that of the sqlserver database. Otherwise, the extracted data may be garbled. For example, if the data in sqlserver is gbk, the linux system running perl must also be set to gbk. For details, see: mysql garbled problem solution (I use other perl languages to solve it)