R (vi): RODBC access to SQL Server

In my actual work, the data source on the one hand is the relational database MS SQL Server, on the one hand is hbase. This section focuses on accessing the MS SQL Server installation configuration via RODBC, see Data (https://msdn.microsoft.com/en-us/library/hh568454 (v=sql.110). aspx ), Note: There is a mandatory correspondence between the version of the downloaded MSODBC and the UNIXODBC version, see: (https://msdn.microsoft.com/en-us/library/hh568449 (v= sql.110). aspx), this article chooses to install the msodbcsql-11.0.2270.0, according to the official introduction, the corresponding UnixODBC version is: unixODBC-2.3.0

Directory:

• UNIXODBC Installation
• Msodbcsql Installation
• ODBC configuration
• RODBC Installation
• RODBC Invocation Example

Description

• UNIXODBC provides Linux support for ODBC, but it is only an ODBC manager, and to connect to the actual database you also need to provide the ODBC driver for this database.
• Msodbcsql is the free ODBC driver used to connect to SQL Server under Linux ( Note: You must install UNIXODBC before installing SQLSERVERODBC)

UNIXODBC Installation :

• Software installation package Download (unixodbc-2.3.0.tar.gz),:https://sourceforge.net/projects/unixodbc/files/unixODBC/2.3.0/
• Command:yum remove UnixODBC (if other versions of UnixODBC are already installed on the machine)
• Command:tar xvzf unixodbc-2.3.0.tar.gz (upload the download to a local package to a Linux host and unzip it)
• Command:CD unixODBC-2.3.0 (switch to the extracted directory)
• Command:cppflags= "-dsizeof_long_int=8"
• Command:export cppflags
• Command:./configure--prefix=/usr--libdir=/usr/lib64--sysconfdir=/etc--enable-gui=no--enable-drivers=no-- Enable-iconv--with-iconv-char-enc=utf8--with-iconv-ucode-enc=utf16le
• Command: make (compile and install)
• Command: Makeinstall

Msodbcsql Installation :

• Software installation package Download (msodbcsql-11.0.2270.0.tar.gz),:https://www.microsoft.com/en-us/download/details.aspx?id=36437
• Command:tar xvzf msodbcsql-11.0.2270.0.tar.gz (unzip after the installation package is uploaded to the Linux host)
• Command:CD msodbcsql-11.0.2270.0 (switch to extract the home directory)
• Command:./install.sh Verify (Verify that the current environment meets the software installation requirements)
• Command:./install.sh Install (on the premise that the previous step is OK, perform the installation)
• Command:odbcinst-q-d-n "ODBC Driver one for SQL Server" (test installation succeeded, OK below)

  Description=microsoft ODBC Driver one for SQL serverdriver=/opt/microsoft/msodbcsql/lib64/ libmsodbcsql-11.0.so.2270.0Threading=1usagecount=1

ODBC configuration :

• After successful installation, you can find the Odbcinst.ini and Odbc.ini files in the/etc directory.
• Odbcinst.ini indicates which types of drivers have been installed on the machine, and the following are correctly installed

  [ODBC DriverOne-to-one for SQL Server] Description=microsoft ODBC Driver one for SQL serverdriver=/opt/microsoft/msodbcsql/lib64/ libmsodbcsql-11.0.so.2270.0Threading=1usagecount=1

• Command:vim/etc/odbc.ini (Configure MS SQL Server connection information), enter the following in the open file

  [testsql] Driver=odbc Driver one for SQL serverserver= database server name or ipdatabase= database name

• Note: The Driver assignment must be consistent with the security in the Odbcinst file

• Command:isql testsql sa XXXX (testsql: Set ODBC connection name, followed by a space to enter the user name and password, successful connection OK,)
• Command: quit (quit)

RODBC Installation:

• Command:R (enter the R command line)
• Execution:install.packages ("RODBC") (Installation RODBC)
• After the installation is complete, execute the following script under the R command line, as shown below OK

  <-odbcconnect ("testsql""sa""YourPassword "  <-sqlquery (pile,"select Top 5 code,name from Codeitems") Close ( Pile) Data

• The result is as follows, OK

R (vi): RODBC access to SQL Server