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.

Advertisements

2013-03-02

MongoDB Nutshell

Filed under: web application development — Tags: , , , , , , — Zhixian @ 11:12:03 am

I decided that my previous notes on MongoDB quickstart is too wordy. Here’s a more succinct version.

Up-&-Running

  1. Get and unzip MongoDB files from http://www.mongodb.org/downloads into MongoDB directory (eg. C:\MongoDB)
  2. Create a Data directory in MongoDB directory (eg. C:\MongoDB\Data\Test)
  3. Start MongoDB server using C:\MongoDB\mongod.exe –-dbpath C:\MongoDB\Data\Test
  4. Start MongoDB client using C:\MongoDB\mongo.exe

Mongo Client Command Basics

help – display a summary of commands available
db – indicates the database that you are using
show dbs – list databases
use demo – switch to use database call demo
book1 = { author:”Lewis Caroll”, title:”Alice in Wonderland” }defines a document call book1 with attributes author and title.
db.books.insert(book1) – insert “book1” into a collection call “books”
db.books.find() – list documents (as cursor) in “books” collection
db.books.find({title:”Alice in Wonderland”}) – list documents (as cursor) with title of “Alice in Wonderland” in “books” collection

db.books.find().limit(3) – limits the numbers results in list results
db.books.findOne() – return a document in “books” collection

Other tricks – variables & loops

for (var i = 1; i <= 20; i++) db.things.insert( { x : 4 , j : i } )
var c = db.things.find()
while ( c.hasNext() ) printjson( c.next() )
printjson( c [ 4 ] )

Side Notes

A MongoDB is datastore with many databases. Each database may contain multiple collections of free-form documents.
MongoDB will create collections and databases implicitly upon their first use (insertion of data), hence no creation statements are needed.
All records (documents) in MongoDB are dynamic, hence no schema needs to be defined.

MongoDB Up and Running

Filed under: web application development — Tags: , , , , — Zhixian @ 10:25:03 am

This is my notes to quickly get started with MongoDB under Windows environment.
Most of the material here can be found from the MongoDB Manual.

This post covers the following:

  1. Materials required
  2. Installation
  3. Running MongoDB  Server
  4. Running MongoDB Client

Materials required – 1 required

  1. MongoDB (required)
    Download from http://www.mongodb.org/downloads
    As of date (2013-03-02), the production version is 2.2.3.
    I’m using the Windows 64-bit (2008+) build since this as said to enhance performance when running MongoDB with 64-bit Windows.

Installation

Installation of MongoDB is straight-forward as it is simply a zip file.
Simply extract the contents of the zip file to a convenient location like C:\MongoDB.
The contents of the zip file should look like the below:

image

All the executables related to MongoDB are found in the bin folder.
image

Before proceeding further, you should create folder(s) to hold your data as well.

I try to keep to this convention on naming directories to store MongoDB data:<MongoDB directory>\Data\<application>

Where:

  • <MongoDB directory> – refers to the directory where I extracted the MongoDB files.
  • <application> – refers to the name of the application that MongoDB is used to store data for.

For this post, I will store my data in C:\MongoDB\Data\Quickstart

Running MongoDB Server

MongoDB server is run by executing mongod.exe executable file. Basic execution of this file takes the following syntax:

mongod.exe –-dbpath <data directory path>

<data directory path> refers to the directory that you will store MongoDB data.
As per installation step, we will be using C:\mongodb\data\quickstart for example.

To run MongoDB server from the command-line, go to MongoDB’s bin directory and execute the following command:

mongod.exe –dbpath C:\mongodb\data\quickstart

image

If the directory that you specify does not exists, you may get the following screen:

image

To resolve the error, simply create the directory before running the command again.

The first time, you run mongod.exe you may get a firewall warning like the below:

image

For security, ensure that only the Private networks, such as my home or work network option is checked before clicking on the Allow access button. This should be fine for general local development and testing purposes.

If MongoDB has started, you should see the following screen:

image

The important thing to note, is the last line.
It tells you that MongoDB has started and is waiting for requests on port 28017.

Running MongoDB Client

MongoDB client is run by executing mongo.exe executable file, in the MongoDB directory.

The first time you run this executable, you will get a introductory text as shown in the the following screen.

image

Subsequent executions, will not display the introductory text.

image

2013-02-20

contextify – Specified platform toolset (v110) is not installed or invalid

Filed under: web application development — Tags: , , — Zhixian @ 23:42:02 pm

I ran into this issue recently.
I was trying to install jquery Node package module into my node.js instance using the following command:

npm install jquery –g

Ultimately, the installation failed, and I received a list of errors among which contains:

C:\Program Files (x86)\MSBuild\Microsoft.Cpp\v4.0\Platforms\x64\Microsoft.Cpp.x64.Targets(514,5):
error MSB8008: Specified platform toolset (v110) is not installed or invalid.
Please make sure that a supported PlatformToolset value is selected.

In the case of the jquery node package module, it seems this may display for the contextify module.

This error may occurred if you have multiple versions of C++ SDKs installed.
If you do have multiple versions of C++ installed, this error simply means that the command prompt that you are running npm under is referencing the wrong version of C++.

In my case, I had installed VS2010 and VS2012 EXPRESS for Desktop in my PC.
The command prompt that I’m using to run npm has references only to the C++ installed with VS2010 – platform toolset (v100).
But the msbuild command that ran when building contextify is referencing the C++ installed with VS2012 – platform toolset (v110).

To resolve this issue, simply run installation command using one of the other command prompts that comes along with VS2012.

image

Note: By default, using the Developer Command Prompt for VS2012 to install contextify may also result in the same error.
This is because this particular command prompt is missing a VisualStudioVersion environment variable.
If you must use it, execute the following SET statement, before running npm:

SET VisualStudioVersion=11.0

2012-12-19

ASP.NET application restarts on every request (Part 2)

Filed under: web application development — Tags: , , , — Zhixian @ 11:43:12 am

This blog post is an continuation to my previous blog post on the same topic.

In my previous post, I mentioned that I did not reach a definitive conclusion as to what could have cause the application restarts on every request.
As it turns out while working on a module in my project, I chance across another scenario which may cause the same effect.

Just to provide some background, my project was running on ASP.NET framework 4.0  (running on IIS 7.5 in integrated managed pipeline mode).
Development machine was a Windows 7 (x64).

The scenario that I’m talking about is that I had mixed a x86 DLL into the bin folder of the project.

And wham! The issue of application restarts came back again.

I did not find out whether its that particular x86 DLL in question or will the same thing occur for all x86 DLLs (I’m leaning towards all).
My hypothesis that is that I’m running the application off .NET framework 4 on a x64 machine and mixing x86 DLLs with such a configuration will trigger the application restarts on every request.

Solutions:

  1. Replace the x86 DLL with a x64 version of the DLL
    This is the best solution.
  2. In the Advanced Settings of the AppPool use by the project in IIS, set the Enable 32-bit Applications flag to True.
    image
    This works but will cause your application to have some performance issues (ie. slow down the application; running in 32bit vs 64bit).

2012-12-07

ASP.NET application restarts on every request

Filed under: web application development — Tags: , , , — Zhixian @ 09:00:12 am

Lately I have been struggling with a very strange issue in one of my ASP.NET projects.

For some strange reason, the application would perform a recompilation on every request.
Having the recompilation means that the application needs to be restarted.
What this effectively means, is that the application will not be snappy as it should be as it will be going through the application life-cycle.

Interestingly, the shutdown reason provided by System.Web.Hosting.HostingEnvironment.ShutdownReason is BuildManagerChange.
Unfortunately, what MSDN has to say about this is:

The compilation system shut the application domain.
The BuildManagerChange member is introduced in the .NET Framework version 3.5.

What’s worse for me is that this topic of BuildManagerChange appears to be relatively uncharted territory.
Either no one has come across this issue or I’m one of the first developers to come across this issue or I’m using the wrong keywords to find resources regarding this topic.

To cut the long story short, I finally come to the conclusion that this is an infrastructure issue, specifically with IISEXPRESS 8.0 which is a bit of a disappointment because I really like developing using IISEXPRESS.
This conclusion was reached by virtue that the problem disappears when I:

  1. Use IIS (7.5, the default for my WIndows 7 machine)
  2. Use the web development server instead of IISEXPRESS
  3. Use IISEXPRESS 7.5 (inconclusive!)

I could not reach to a happy conclusion with point 3. The reason being that when you install IISEXPRESS 8.0, it will effective replace IISEXPRESS 7.5
So when I say “use IISEXPRESS 7.5”, it implies that I’m using a different machine. Since it’s a different machine, there would be tons of probable variables that may result the application in working. Hence, inconclusive.

But point 1 and 2 are good enough reasons for me to lay the blame at IISEXPRESS 8.0
So if you are encountering application restarts for no apparent reason on IISEXPRESS, try it with IIS 7.5

2012-06-23

Development applications for Precise Pangolin

Just a short note on the other software that I installed to do development work.

Version control related applications:

  1. git
    Distributed version control system (DVCS) brought to fame by Linus Torvalds.
  2. gitg
    A graphical interface to interacting with git
  3. Mercurial
    A alternate DVCS written in Python.
  4. TortoiseHg
    A graphical interface for interacting with Mercurial
  5. Meld
    A directory and file comparing tool

Browser:

  1. Chrome browser
    Popular browser from Google

Firefox plug-ins:

  1. Firebug for FireFox
    Web development tool for use with Firefox
  2. IcedTea Java Web Start
    Java plug-in to run Java applications on Firefox

Other helpful utilities:

  1. OpenJDK Java 7 Runtime
    A Java runtime to run Java applications on Ubuntu.
  2. Tomboy Notes
    A Mono application use for taking quick notes.

IDEs:

  1. MonoDevelop (with mono-xsp4 add-on)
    IDE for developing applications running on the Mono platform.

Web server:

  1. Apache2
    Web server to host web applications.

With the exception of the FireBug plug-in, all of these applications can be installed via Ubuntu Software Center.

2011-12-03

Running Umbraco source code

Filed under: web application development — Tags: , , — Zhixian @ 08:59:12 am

If you are trying out Umbraco content management system (CMS) using the source code (as oppose to using the Web Platform method recommended for end-users), you may encounter the following problem.

Upon running the solution (or more specifically the umbraco.presentation project) for the first time, you may get a login screen instead of the setup screen.

imageLogin screen

imageInstallation screen

Ok.
After getting the login screen, you maybe thinking “Ok. Since I reached the login screen, there must be some administrator credentials that I can use to access the administer the site.

Wrong.

There is no default administrator credentials.
The administrator’s password is set as part of the installation setup.
But wait, we get a login screen. 
We didn’t get any installation screen.

At this stage, you are probably wondering what’s going on.
The cause of this is because the application setting umbracoConfigurationStatus in the web.config file is set to some value.

image

To get the installation screen, we should be setting the value for that setting to be an empty string.

image

After you saved the web.config, you should see the installation screen.

2011-07-09

Installing MinGW

Filed under: web application development — Tags: , , — Zhixian @ 21:50:07 pm

This is a blog post on installing MinGW.

  • Short introduction to MinGW
  • Getting MinGW
  • Installing MinGW using the automated installer

Short introduction to MinGW

MinGW is a contraction for "Minimalist GNU for Windows.
The goal of MinGW is to provide a minimal development environment on Windows based on GNU utilities that are commonly found in Unix/Linux platforms.
Its important to note the MinGW is an environment provided on top of Windows. It is not a replacement for a Unix systems nor is it POSIX (Portable Operating System Interface for Unix) compliant. For a POSIX compliant development environment on Windows, you would have to look at cygwin (http://www.cygwin.com/).

Getting MinGW

To install MinGW, I recommend you use the automated installer that can be found in Sourceforge.
This is the fastest way to get a working MinGW environment.

Note: This installer is a downloader front-end.
That is to say, based on the options that you selected in during the installation process, the installer will download only those selected components over the Internet. This process assumes you are connected to the Internet while doing the installation.

The other (more tedious) way is to download each of the required runtime and install it from them.
MinGW’s website has something on this process here. So I will not repeat that information here.

Installing MinGW using the automated installer

After you download the automated installer, you have have an executable like the following:

Icon

Double-click on this executable to start the installation process.

 

After the installer startup, the first 2 steps are typical of every Windows installation.
Just click on the Next button to proceed.

mingw-installation-step1 mingw-installation-step2

The 3rd dialog may be a little confusing.
Its asking if you want to get the latest version of MinGW files or if you would rather get the version of MinGW files associated with the installer.
This is a matter of necessity/preference really. I typically select using the pre-package repository catalogues (ie. the version of MinGW files associated with the installer). After clicking on the  desired radio button option, click on the Next button to proceed with the next step.

mingw-installation-step3

This step is the EULA (End User License Agreement), click on the Next button to proceed after you read through it.

mingw-installation-step4 

The next step is to decide the location to install MinGW in. The default is MinGW directory.
You can pick another directory but preferably one without spaces in it (eg. C:\GNU\MinGW)
After you decide, click on the Next button to proceed with the next installation step.

 

 

mingw-installation-step5 

The next dialog ask which folder do you want to put the shortcuts for MinGW in your start menu.
Click on the Next button to proceed with the next installation step.

mingw-installation-step6

You select the components that you want to install for MinGW in this step.
The default installation only install the C Compiler.
In addition, I would also recommend getting the C++ Compiler and MSYS Basic System.
Update (2011-07-09): The MSYS Basic System is really a basic system. It does not even come with a text editior.
As such, I am correcting my recommendation to include the MinGW Developer Toolkit option.

If you are need a Fortran compiler or Obj-C compiler, feel free to add those options.
Click on the Next button to proceed with the next installation step.

image

After you selected your desired installation options, the next dialog screen will provide a checklist of items for user to check through the items to install.
After you click on the Install button, the install will proceed to download the packages from the Internet.

mingw-installation-step8 mingw-installation-step10

After all the necessary packages are downloaded, you will get the below dialog.
Click on the Finish button to complete the installation process.

mingw-installation-step11

2010-09-02

Eclipse Plug-ins (PDE)

Filed under: computing, web application development — Tags: , , , — Zhixian @ 01:33:09 am

This post is about something silly that I did.

This may or may not sound counter-intuitive. I was trying to use a plug-in in Eclipse and I couldn’t get it to install. When I tried, I would get the following helpful message:

An error occurred while installing the items session context was:(profile=PlatformProfile, phase=org.eclipse.equinox.internal.provisional.p2.engine.phases.Install, operand=null –> [R]org.eclipse.cvs 1.0.400.v201002111343, action=org.eclipse.equinox.internal.p2.touchpoint.eclipse.actions.InstallBundleAction). Cannot connect to keystore. This trust engine is read only. The artifact file for osgi.bundle,org.eclipse.cvs,1.0.400.v201002111343 was not found.

Nice. I have totally no idea what is this message talking about. A few googles later, I realise I encounter this because my copy of Eclipse does not have the plug-in development(eclipse-PDE) package installed. I think I may have install Eclipse using the very helpful Ubuntu Software Center. If I have paid more attention, I might have read the following description in the installation package for Eclipse:

This package provides only the Eclipse Platform. It does no include any development plug-ins. These are available in different packages:

  • eclipse-jdt Java Development Tools
  • eclipse-pde Plug-in Development Tools
  • eclipse Complete development environment

This package is the base for all eclipse plug-ins. Please note that many plugins will fail to install if you don’t have the eclipse-pde package installed.

Ahem…
Ah. A beginner’s mistake to be sure.

Here’s the good news. To quickly resolve this issue, just go Ubuntu Software Center and search for eclipse-pde and install it. After you install it, you should be able to install the plug-in that did not get installed previously.

« Newer PostsOlder Posts »

Blog at WordPress.com.