acloudtree
RSS

Category Programming

How To: Install FreeTDS and UnixODBC On OSX Using Homebrew For Use With Ruby, Php, And Perl

14 comments By jared.folkins Filed in Macintosh, Programming, Rails, Ruby January 10th, 2012 @

This little project started out as a basic script to connect to a Microsoft SqlServer and get data. It was a nightmare as I probably spent 15 hours learning about and troubleshooting both FreeTDS and UnixODBC. My pain is now your gain.

NOTICE: I have homebrew configured to install all packages into my local directory /Users/jared/.homebrew/

1) Install UnixODBC

[jared@localhost]$ brew install unixodbc
==> Downloading http://www.unixodbc.org/unixODBC-2.3.0.tar.gz
File already downloaded in /Users/jared/Library/Caches/Homebrew
==> ./configure --disable-debug --prefix=/Users/jared/.homebrew/Cellar/unixodbc/2.3.0 --enable-gui=no
==> make install
/Users/jared/.homebrew/Cellar/unixodbc/2.3.0: 24 files, 932K, built in 22 seconds
[jared@localhost]$

2) Edit the FreeTDS formula And install

What we are doing is changing the default tds version, enabling the msdblib, and pointing out where unixodbc installed.

require 'formula'
 
class Freetds > Formula
url 'http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-0.91.tar.gz'
homepage 'http://www.freetds.org/'
md5 'b14db5823980a32f0643d1a84d3ec3ad'
 
def install
system "./configure",·
"--prefix=#{prefix}",·
"--with-tdsver=7.0",·
"--enable-msdblib",
"--with-unixodbc=/Users/USERNAME/.homebrew/Cellar/unixodbc/2.3.0",
"--mandir=#{man}"
system 'make'
ENV.j1 # Or fails to install on multi-core machines
system 'make install'
end
end
[jared@localhost]$ brew install freetds

3) Start a new terminal session to make sure all your paths update

4) Confirm that you can connect to the server

We need to make sure that you can connect to the sqlserver and that the port is open and available to you.

To do this we use telnet. If you see the following, success! The port is open on the server.

[jared@localhost]$ telnet server.example.com 1433
Trying 192.168.1.101...
Connected to server.example.com.
Escape character is '^]'.

If you see the following. You failed. Check the Sqlserver configuration, firewalls, or network configuration.

[jared@localhost]$ telnet server.example.com 1433
Trying 192.168.1.101...
telnet: connect to address 192.168.1.101: Connection refused
telnet: Unable to connect to remote host

Note: Press the ctrl + ] keys to break to a prompt and then type exit.

5) Tsql

FreeTDS comes with a couple cli applications. One of them is tsql. It isn’t great, but I use it test and see if at least FreeTDS is working correctly. After you install FreeTDS using homebrew try and connect to the host using the following command.

[jared@localhost]$ tsql -H server.example.com -U USERNAME -P PASSWORD -v
 
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>exit

If you see a prompt, you haz awesome!

6) Sym link the FreeTDS and UnixODBC conf files

I create 3 sym links to the following files just for simplicity.

ln -s /Users/jared/.homebrew/Cellar/freetds/0.91/etc/freetds.conf ~/.freetds.conf
ln -s /Users/jared/.homebrew/Cellar/unixodbc/2.3.0/etc/odbc.ini ~/.odbc.ini
ln -s /Users/jared/.homebrew/Cellar/unixodbc/2.3.0/etc/odbcinst.ini ~/.odbcinst.ini

7) edit the .freetds.conf and add the following

[example]
host = server.example.com
port = 1433
tds version = 7.0

8 ) edit the odbcinst.ini and add the following

You are telling unixodbc where your FreeTDS drivers are located using this configuration file.

[FreeTDS]
Description = FreeTDS
Driver = /Users/jared/.homebrew/lib/libtdsodbc.so
Setup = /Users/jared/.homebrew/lib/libtdsodbc.so
UsageCount = 1

9) edit the .odbc.ini and add the following

[myexample]
Driver = FreeTDS // we just set this up a second ago
Description = MyExample
ServerName = example // this is the name of the configuration we used in the .freetds.conf file
UID = USERNAME
PWD = PASSWORD

10) isql should work

[jared@localhost]$ isql sqlinternal USERNAME PASSWORD
+---------------------------------------+
| Connected!
| sql-statement
| help [tablename]
| quit
+---------------------------------------+
SQL>quit

11) Osql Error

If you try osql, it throws an error.

[jared@localhost]$ osql -S myexample -U USERNAME -P PASSWORD
checking shared odbc libraries linked to isql for default directories...
/Users/jared/.homebrew/bin/osql: line 53: ldd: command not found
strings: can't open file: (No such file or directory)
osql: problem: no potential directory strings in "/Users/jared/.homebrew/bin/isql"
osql: advice: use "osql -I DIR" where DIR unixODBC\'s install prefix e.g. /usr/local
isql strings are:
checking odbc.ini files
reading /Users/jared/.odbc.ini
[myexample] found in /Users/jared/.odbc.ini
found this section:
[myexample]
Driver = FreeTDS
Description = MyExample
Servername = example
UID = USERNAME
PWD = PASSWORD
 
looking for driver for DSN [myexample] in /Users/jared/.odbc.ini
found driver line: " Driver = FreeTDS"
driver "FreeTDS" found for [myexample] in .odbc.ini
found driver named "FreeTDS"
"FreeTDS" is not an executable file
looking for entry named [FreeTDS] in /odbcinst.ini
found driver line: " Driver = /Users/jared/.homebrew/lib/libtdsodbc.so"
found driver /Users/jared/.homebrew/lib/libtdsodbc.so for [FreeTDS] in odbcinst.ini
/Users/jared/.homebrew/lib/libtdsodbc.so is not an executable file
osql: error: no driver found for sqlinternal
[jared@localhost]$

If you go through the error you will find that a certain driver is not executable. You just need to chmod the file.

[jared@localhost]$ chmod 554 /Users/jared/.homebrew/Cellar/freetds/0.91/lib/libtdsodbc.0.so

Now run it again.

[jared@localhost]$ osql -S myexample -U USERNAME -P PASSWORD
checking shared odbc libraries linked to isql for default directories...
/Users/jared/.homebrew/bin/osql: line 53: ldd: command not found
strings: can't open file: (No such file or directory)
osql: problem: no potential directory strings in "/Users/jared/.homebrew/bin/isql"
osql: advice: use "osql -I DIR" where DIR unixODBC\'s install prefix e.g. /usr/local
isql strings are:
checking odbc.ini files
reading /Users/jared/.odbc.ini
[myexample] found in /Users/jared/.odbc.ini
found this section:
[myexample]
Driver = FreeTDS
Description = myexamples
Servername = myexample
UID = USERNAME
PWD = PASSWORD
 
looking for driver for DSN [myexample] in /Users/jared/.odbc.ini
found driver line: " Driver = FreeTDS"
driver "FreeTDS" found for [myexample] in .odbc.ini
found driver named "FreeTDS"
"FreeTDS" is not an executable file
looking for entry named [FreeTDS] in /odbcinst.ini
found driver line: " Driver = /Users/jared/.homebrew/lib/libtdsodbc.so"
found driver /Users/jared/.homebrew/lib/libtdsodbc.so for [FreeTDS] in odbcinst.ini
/Users/jared/.homebrew/lib/libtdsodbc.so is an executable file
Using ODBC-Combined strategy
DSN [myexample] has servername "myexample" (from /Users/jared/.odbc.ini)
/Users/jared/.freetds.conf is a readable file
looking for [myexample] in /Users/jared/.freetds.conf
found this section:
[myexample]
host = myexample.bendcable.net
port = 1433
tds version = 7.0
 
Configuration looks OK. Connection details:
 
DSN: myexample
odbc.ini: /Users/jared/.odbc.ini
Driver: /Users/jared/.homebrew/lib/libtdsodbc.so
Server hostname: myexample.bendcable.net
Address: 192.168.12.103
 
Attempting connection as username ...
+ isql myexample USERNAME PASSWORD -v
+---------------------------------------+
| Connected!
| sql-statement
| help [tablename]
| quit
+---------------------------------------+
SQL> quit

SUCCESS!!!

Some other useful commands.

Useful commands

odbcinst -j
 
odbcinst -q -d
 
odbcinst -q -s

Newbs, Ruby, Profiling Memory Leaks Using Memprof, And Web Scraping with Mechanize

3 comments By jared.folkins Filed in Programming, REGEX, Scraping Tagged with , , , , , , , , August 8th, 2011 @

So for the past four weeks in my spare time, I’ve been writing a sweet web scraping application for a personal research project. After overcoming the majority of regex and pattern matching challenges, I was finally able to get my spider a crawlin’ and then go to bed.

When I woke, I was excited to see what little bits of interesting information my application had found. But instead, I saw that it had dumped stack. And as a Ruby newb, here starts the long and arduous journey to understanding how to profile a ruby application. Yikes!

Definition of “Memory Leak” in Ruby

From what I have researched and my own personal experience. You (the developer) can’t really create a traditional memory leak.

Traditional Leak

  1. Allocate Memory (malloc, alloc)
  2. Assign Value (Object, string, int, etc…) To Memory
  3. No longer needing the value, you forget to release (dealloc) it
  4. The data stays in memory during the life of your application
  5. Particularly problematic when iterating or looping and assigning new values to memory

Ruby Memory Leak

  1. You assign a value to a variable
  2. You expect that the value has fallen out of scope
  3. Instead, it persists in memory as some Object you are unaware of is retaining it
  4. Not really noticeable in small applications or page requests where memory is freed at the end of the run cycle

Thats the basic difference (from my understanding). Please correct me if I am off on something.

Tips To Identify That You Have A Memory Leak

So a couple quick ways that you can tell you could have a memory leak.

Total Object Count

Try printing the total Object count to screen while your application is running. Should the count increase over time, this would indicate that you are retaining objects. Please note that you need to force Garbage Collection regularly to make sure that the total Object count is not just the logical growth cycle of your application.

Monitor Memory Usage

Monitor the Process I.D. (PID) of your application and look to see if memory usage increases over a given period of time. Should it continue to go up, this would strengthen the argument that you have a leak.

Again, these are two quick methods I used to come to the following point of action.

“Crap, it looks like something is going on with memory usage, I better take a closer look.”

Try forcing your Objects to release

I learned rather quickly, that by design, Ruby does not allow you to release or unset your Objects manually. So there ya go.

Set your Objects to nil to identify they are ready to be Garbage Collected

I tried this while grasping at straws, it was a dumb idea. I was taking shots in the dark and I wouldn’t advise going this route. What I needed was a really deep look at what Objects I was storing in memory so I could develop a story on why my application was leaking.

Memprof & its visualization partner Memprof.com

This is when I happened across the git repo Memprof by Joe Damato (@joedamato http://timetobleed.com). It was exactly what I needed.

Using Ruby 1.8.7, Install Memprof

$ gem install memprof

Signup for memprof.com

memprof.com (be aware, that when using this tool, it publishes your code to the web)

Include Memprof (at the VERY beginning of your application, just in case you want to profile your included gems too)

require `gem which memprof/signal`.chomp

Wrap Run Loop in Memprof.trace

def run_loop
  while next_link?(@browser.page) do
    Memprof.trace{
      iterate_search_page(@browser.page)
      click_next_link(@browser.page)
    }
  end
end

Run Your Application

$ ruby web_crawler.rb

Find Your Process ID of application

$ ps -ef | grep web_crawler.rb

After 5 minutes, Dump Trace To Memprof.com

$ memprof --pid 27890 --name elvis_dump2 --key YOUR_API_KEY

Analyze the heap visually with Memprof.com

Click On (objects with most outbound references)

On the far right, you will see an Object with the “Mechanize” class, and its length is 1081. This piece of information was critical in identifying that a Mechanize object I had instantiated was retaining a crap pile of objects.

What was the issue?

So it turns out that by default, Mechanize does NOT set a max_history=() value. What does this mean? Basically, my web crawler was storing every visited page, in memory, in entirety. BEWARE!

Hat tip ( @bradhe, @tim_linquist, @joedamato, @elazar)

Note: My “Ruby Memory Leaks” definition is not entirely correct, but for the newbs out there reading this, I think trying to break it down further could potentially confuse you. If you want to understand Ruby memory allocation at some point, go watch this video by Joe Damato. (link)

Macifier.com – FREE Mac App That Locks Your Keyboard And Mouse

1 comment By jared.folkins Filed in Life, Macintosh, Objective-C, Programming Tagged with , , , , , , , February 5th, 2011 @

Macifier - Pacifying Your Keyboard And Mouse

My daughter Gracie was routinely disabling our family laptop while she skyped with her Grandparents. So I wrote a small Macintosh application that locks the keyboard and mouse. After showing the initial app to my buddy Mike Brisk, he created an awesome logo.

Head over to http://macifier.com and download it for free.

(Nerd) Git Diff & Syntax Highlighting Using VIM

4 comments By jared.folkins Filed in Git, Programming Tagged with , , , , December 6th, 2010 @

I wanted syntax highlight from the command line using VIM and Matthew Turland gave me the following tip.

jared@example.com$ git config color.diff auto
Read previous entries

Copyright © Jared Folkins
Programming, Computers, Writing, Economics, and Life

Powered by WordPress