Zhixian's Tech Blog

2013-06-23

How to connect to Microsoft Sql Server from Ubuntu using pyODBC

This is continuation of my previous 3-part blog post that list out the steps carried out to establish a connection to Sql Server via ODBC on Ubuntu.

Assumptions

  1. The version of Ubuntu used in this guide is Ubuntu 12.04.2 LTS (Precise Pangoline) 32-bit.
  2. unixodbc is installed on the Ubuntu system.
  3. freetds is installed on the Ubuntu system.
  4. You are trying to connect to Microsoft Sql Server 2008 or later.
  5. You know how to use a terminal session in Ubuntu.

Required materiels

  1. pyODBC (latest version is 3.0.6 as of writing)
    (downloadable from http://code.google.com/p/pyodbc/downloads/list)

Download source vs apt-get

The apt-get utility in Ubuntu does have a version of pyODBC. (version 2.1.7)
However, it is badly out-of-date (2.1.7 vs 3.0.6) and may not work well with the newer versions of unixODBC and freetds.
This is especially important if you are trying to connect to later versions of Microsoft Sql Server (2008 onwards).
It is recommended that you use the latest versions of unixODBC, freetds and pyODBC when working with the latest Microsoft Sql Server instead of relying on packages in apt-get.

PyODBC Installation Overview

The steps for installing pyODBC are as follows:

  1. Download the source zip file. (I’m assuming you know how to do this)
  2. Extract the contents of the package.
  3. Install the contents of the package.
  4. Configure your system environment variables.

Extracting contents from pyODBC zip file

1) Assuming you downloaded the pyODBC zip into the Downloads folder of your Home directory, you should see a screen like the below on your file browser.

image_thumb[1]

2) To extract the contents of the package, right-click on it. A popup menu should appear.
Select the menu item “Extract Here”.

image_thumb[3]

3) After you extracted the contents, your file browser should look like the below:

image_thumb[5]

 

Installing pyODBC

1) Start a new terminal session.
Navigate to the location where you extracted the pyODBC source files by typing cd ~/Downloads/pyODBC-3.0.6/ at the command line.

image

2a) Proceed to install pyODBC by typing python ./setup.py install on the command line.

image

If you received a screen like the below, it means that your user account do not have the necessary permissions to install pyODBC onto the system.
Type sudo python ./setup.py install on the command line to use a superuser account to perform the installation.

image

3) If the installation succeed, you should see a screen like the below:

image

 

Configure system environment variables

pyODBC has a dependency on the file libodbc.so.
This is the library of functions that pyODBC use to perform ODBC operations.
If pyODBC cannot find this file, you may see the following message:

ImportError: <libodbc.so name> cannot open shared object file: No such file or directory

The actual name <libodbc.so name> may varied from system to system. A sample screen is as follows:

image

To help pyODBC find the library, we need to declare an environment variable LD_LIBRARY_PATH and specify the location of the libodbc.so in this variable.

Assuming my libodbc.so is located at  the directory /usr/local/lib

1) Type the following at the command line, to set the variable:
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib

image

2) Type the following to set the variable as a global variable so that the executing environment can access this variable.
export LD_LIBRARY_PATH

image

 

[Optional]

The changes made in the previous step are not persistent.
This means you need to carry out the same steps every time you start a new terminal session.

To make this change persistent, I recommend you put these 2 commands in your shell’s startup file.
If you are using the default bash shell, this would be your ~/.bashrc file.
Put the following 2 lines at the bottom of the file and save the file.

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH

 

Testing pyODBC

For testing pyODBC, I have the following python script:

import pyodbc

database = pyodbc.connect('DRIVER=FreeTDS;SERVER=SARA\\SQLEXPRESS;UID=dbadmin;PWD=password;DATABASE=Arbalest;TDS_Version=8.0;',unicode_results=True)
cursor = database.cursor() encoding = 'utf-8' sql = "SELECT * FROM Arbalest.dbo.test;" cursor.execute(sql) for row in cursor:     print row

image

This python script assumes:

  1. I am trying to connect to the “SQLEXPRESS” Sql Server instance on a machine with the hostname of SARA
  2. The user account that I am using to log into the database is “dbadmin”
  3. The password to my user account is “password”
  4. The database that I am connecting to is “Arbalest”

For Sql Server later than 2005, having the parameter TDS Version=8.0 is recommended.

If this parameter is not defined in the connection string, the pyODBC may work correctly.

Assuming I save this script to a file call “test.py”, I would be able to run the script by typing the following on the command line:

python test.py

image

Note that the connection string that I used above in the pyodbc.connect statement is what is referred to as DSN-less connection string.

With this you should be able to connect to Sql Server from python using pyODBC.

2013-06-22

How to connect to Microsoft Sql Server from Ubuntu via ODBC using FreeTDS

Filed under: computing — Tags: , , , , , , , — Zhixian @ 22:08:06 pm

This post describes the steps carried out to test my connection to Microsoft Sql Server from Ubuntu using ODBC.
This is part of my “How to connect to Sql Server from Ubuntu” series of blog post.

Assumptions

  1. The version of Ubuntu used in this guide is Ubuntu 12.04.2 LTS (Precise Pangoline) 32-bit.
  2. You are trying to connect to Microsoft Sql Server 2008 or later.
  3. You know how to use a terminal session in Ubuntu.
  4. You have installed unixodbc.
  5. You have installed freetds.

Note: Some of configuration files may be sitting in folders where your default user account may not have permission to edit them.
It is assume that that you will use “sudo” to gain the necessary elevated permission to edit the file if this happens.

Information required

  1. Location of odbcinst.ini file.
    This file is where you will be placing configuration details of any ODBC drivers that you will be using.
    If you have followed the steps in the previous blog post, this file should be at /usr/local/etc/odbcinst.ini
  2. Location of the freetds drivers.
    If you have followed the steps in the previous blog post, this file should be at /usr/local/lib/libtdsodbc.ini

Configuration overview

  1. Add driver configuration information for freetds
  2. Test freetds
  3. Configure freetds.conf for Sql Server connectivity testing
  4. Test connection
  5. Troubleshooting notes

Add driver configuration for freetds

1) Edit your /usr/local/etc/odbcinst.ini.
It should be blank. Add the following lines to the file and save the file.

[FreeTDS]
Description    = FreeTDS unixODBC Driver
Driver        = /usr/local/lib/libtdsodbc.so
Setup         = /usr/local/lib/libtdsodbc.so

Test freetds

After you have configure the driver, you can test the connection to your Sql Server.
We will use the tsql command to test the connection.

1)  Start a terminal session. Type tsql –C on the command line.

This command displays some of freetds configuration.
You should see results like the below.

image

Of note is the freetds.conf directory. This file is used to define Sql Servers aliases as well as freetds driver default settings.

2) Assuming you manage to display the configuration information, this means that your freetds setup correctly.
The next thing to try to do is probe for your Sql Server.
You do thing by typing the command tql –LH <machine_hostname>

<machine_hostname> can be hostname or an IP address.
In the screen below, the machine that host my Sql Server is SARA, so I typed tsql –LH SARA

image

From the above results, I can see that I have 2 instances of Sql Server installed on the host SARA.
It is important to note the TCP port number used by the server instance.
For the remainder of this post, we will try to connect to the SQLEXPRESS instance.
But prior to that, we will need to add a configuration setting in order to use the tsql command to connect to Sql Server.

 

Configuring freetds.conf

1) Remember the freetds.conf  file that you noted in step 1 of under test freetds? The one located at /usr/local/etc/freetds.conf ?
There are content in this file. But you don’t really need them at this stage. What you really need are just the following lines:

[global]
tds version = 8.0

Save the file.
The reason this step is required is because the tsql command utility do not have any parameters that allow you to specify the TDS version that it should use to connect to Sql Server. This is a problem when trying to connect to Sql Servers 2005 or later.
Hence, we set it as a global setting so that the default TDS version that driver will revert to is version 8.0

Test connection

1) Now you can use the tsql command to connect to your Sql Server.

Assuming my database user account is dbadmin, I would connect to the server by typing the following at the command line:
tsql –S SARA\\SQLEXPRESS –U dbadmin

If you get a screen like the below, it means you have successfully connected to your Sql Server.

image

You can run sql statements in this shell.
Type quit in the shell to exit.

image

 

Troubleshooting notes

Problem 1)

If you get a message like “There was a problem connecting to the server” as per the screen below, check your freetds.conf file and make sure tds version is set to 8.0 under global settings section.

image

 

Problem 2)

Another problem you might encounter when test is the following.
You appear to have logged in successfully, but you get a  running numeric prompt that starts from 1, 2, 3, …until such a time it timed out.

image

Essentially, it means tsql was not able to locate the server.

Checkpoints for this problem are:

  1. Is this correct server?
    If you are trying to connect to a database instance, you should specify tsql –S SARA\\SQLEXPRESS –U dbadmin
  2. Is the server running on default port?
    Note the TCP used by Sql Server by using the tsql –LH <machine_hostname> command. Then try the tsql command again by specify the port number with –p parameter. That is to say:
    tsql –S SARA\\SQLEXPRESS –U dbadmin –p 1433

 

Hope this helps anyone who had trouble setting up connection to Sql Server from their Ubuntu boxes.

How to install FreeTDS on Ubuntu

Filed under: computing — Tags: , , , , , , , — Zhixian @ 17:47:06 pm

This post describes the steps carried out to build freetds from source files.
It then go on to describe the steps carried out to install freetds.

This is part of my “How to connect to Sql Server from Ubuntu” series of blog post.

Assumptions

  1. The version of Ubuntu used in this guide is Ubuntu 12.04.2 LTS (Precise Pangoline) 32-bit.
  2. You are trying to connect to Microsoft Sql Server 2008 or later.
  3. You know how to use a terminal session in Ubuntu.
  4. You have installed unixodbc.

Required materials

  1. freetds (as of this writing, latest version is 0.91.89)
    Database driver that would allow us to connect to Sybase/Sql Server database servers.
    (download from
    ftp://ftp.freetds.org/pub/freetds/stable/)

freetds installation steps overview

  1. Download source code for freetds. I assume you know how to do this.
  2. Extract source code out of freetds gzipped package
  3. Compile and install freetds . This involves the following steps:
    1. ./configure
    2. make
    3. make install

Extract source code out of freetds gzipped package

1) Assuming you downloaded the freetds gzip file to a directory call Downloads in your home directory, you should see something like the below in your file manager:

image

2)  Right-click on the package.
A popup menu should appear.
Select the menu item “Extract Here” to extract the contents of the package.

image

3) After the contents are extracted, you should now see a screen like the below in the file manager:

image

 

Compile freetds

1) Start a terminal session.
Navigate to the directory where you extract the freetds source files.

image

2) Configure the compilation process by typing ./configure on the command line.

image

This step checks to make sure that the system has all the required components needed to build freetds.

3) If there are no error messages in the configuration, proceed to build freetds by typing make on the command line.

image

4) Assuming the compilation succeed, you can proceed to install freetds onto your system.
You do this by typing make install on the command line.

image

If for some reason your user account do not have sufficient persmissions to install freetds, you will probably see error messages in the terminal .
This means you need to use a superuser account to install freetds.
In which case you would type sudo make install on the command line instead.

image

5. After installation, you should get a screen like the below:

image

With this, you have installed freetds into your Ubuntu system.

How to install unixODBC on Ubuntu

Filed under: computing — Tags: , , , , , , — Zhixian @ 14:04:06 pm

This post describes the steps carried out to build unixODBC from source files.
It then go on to describe the steps carried out to install unixODBC.

This is part of my “How to connect to Sql Server from Ubuntu” series of blog post.

Assumptions

  1. The version of Ubuntu used in this guide is Ubuntu 12.04.2 LTS (Precise Pangoline) 32-bit.
  2. You are trying to connect to Microsoft Sql Server 2008 or later.
  3. You know how to use a terminal session in Ubuntu.

Required materials

  1. unixodbc (as of this writing, latest version is 2.3.1)
    Allows unix/linux platforms to use ODBC to connect to databases
    (downloadable from http://www.unixodbc.org/download.html)

Download source vs apt-get

The apt-get utility in Ubuntu do have a version of unixODBC.
However, it is out-of-date and may not work well with later versions of Microsoft Sql Server and their corresponding database drivers.
This is especially important if you are trying to connect to later versions of Microsoft Sql Server (2005 onwards).
It is recommended that you get the the latest versions of unixODBC when working with the latest Microsoft Sql Server instead of relying on packages in apt-get.

unixodbc installation steps overview

  1. Download source code for unixodbc. (I assume you know how to do this.)
  2. Extract source code out of unixodbc gzipped package.
  3. Compile unixodbc. This involves the following steps:
    1. ./configure
    2. make
    3. make install

Extracting sourcecode out of unixodbc package

1) Assuming you download the unixodbc gzip file to a directory call Downloads in your home directory, you should see something like the below in your file manager:

image

2)  Right-click on the package.
A popup menu should appear.
Select the menu item “Extract Here” to extract the contents of the package.

image

3) After the contents of the file were extracted, you should now see something like the below in your file manager:

image

 

Compile unixodbc

1) Start a terminal session.
Navigate to the directory where you extract the unixodbc source files.

image

2) In the folder. configure the compilation process by typing ./configure on the command line.

image

The system will then check through if the system has all the required components build unixodbc.

3) If you do no see any error messages in the configuration step, you can proceed to build the unixodbc by typing make on the command line.

image

This is the step that takes the source code and compile them to make unixodbc.

4)  Assuming the compilation succeed, you can now install unixodbc into your system.
This is done by typing make install on the command line.

image

If you do not have sufficient permissions to install unixodbc into your system, you may see permission denied and error messages.
If that is the case, type sudo make install on the command line instead.

image

5)  Your final result screen should look like the below.
It would also be useful to note down the directories highlighted below.
These are folders where you will be placing your ODBC configuration.

image

 

With this, you have installed unixodbc into your Ubuntu system.

2013-06-16

Removing a group in Skydrive

Filed under: Uncategorized — Tags: , , , — Zhixian @ 20:08:06 pm

One of the things that bugs me when using my Skydrive account is the inability to remove invalid groups. For example, see the below sample screen. Under the groups header there are 2 entries for the group “NCS”. The second entry is a valid entry. The first entry leads to a “This item might not exist or is no longer available” page.

Sample-screen

I suspect the reason the invalid group cannot be removed is because of I have another entry with identical name. To remove such invalid groups, we need to navigate to the group options page of the invalid group.

  1. Click on the invalid entry to navigate to the “This item might not exist or is no longer available” page.
  2. On the url navigation bar, note the value for the “cid” query parameter. I will refer to this value xxxxxxxxxxxxxxxx henceforth.

Navigation-bar

  1. Navigate to the delete group options page with following url, replacing the xxxxxxxxxxxxxxxx with the cid noted in step 2.

    https://skydrive.live.com/p.mvc#!/cid-xxxxxxxxxxxxxxxx/GroupOptions/DeleteGroup

  2. You should see a screen like the below. Click on the “Delete group” button to delete the invalid group entry.

image

  1. After you clicked on the “Delete group” button, you will get a confirmation prompt. Click on the “Yes” button to confirm deletion of the invalid group entry.

image

After the group is deleted, you should only see one entry under the groups header.

image

Blog at WordPress.com.