MySQL results from the command line with isql and shell scripts

It seems a simple enough use case:

I want to run a query against the database from the command line.

It should accept a query via STDIN (or as an argument) and return the result to STDOUT (or to a file).
It should be able to specify which database you want to connect to (or optionally configure a default.)

I seem to remember a tool called “isql” for informix that worked like this. It was great for ad hoc queries, shell scripts, batch processes, and cron jobs. There may have been something like this for Oracle as well, but I doubt it.

Sadly, neither MySQL or PostgreSQL seem to have this feature. They both have “command line clients” but they’re not really command line tools. They’re text-based shells that establish a persistent connection to the database and have their own commands. Most importantly, they don’t work with standard input and standard output. You could use your favorite scripting language to build queries, or even a generic query tool, but that’s not really portable.

I did a search on “isql” and it turns out that unixodbc has a tool with just that name. This stems from Microsoft SQL Server’s heritage from Sybase which included a port of isql. ODBC is, of course, Microsoft’s database connection standardization, and probably one of the main reasons they won the enterprise application development wars (the other reason obviously being that users were familiar with Windows UI.)

It’s not identical to the old command line tool, which my faulty memory tells me could spit out raw results (while walking uphill in the snow), but it’s fairly customizable, and with a little bit of help from standard tools like grep and sed, can give me what I want.

First, install unixodbc and the appropriate driver (e.g. MySQL or PostgreSQL.)

On Debian-based systems (including Ubuntu)
apt-get install unixodbc

On Redhat-based systems (including Fedora and Centos)
yum install unixodbc

To install the mysql odbc driver

(on Debian)
apt-get install libmyodbc

(on Redhat)
yum install mysql-connector-odbc

or the postgresql odbc driver

(on Debian)
apt-get install odbc-postgresql

(on Redhat)
yum install postgresql-odbc

Now run the command ‘odbcinst -j‘ to see where it looks for configuration files. You should see something like this:

unixODBC 2.2.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/aaron/.odbc.ini


Drivers need to be installed by root (or someone with permissions to edit /etc/odbcinst.ini.) An example driver (on my Ubuntu system is available at /usr/share/libmyodbc/odbcinst.ini) and looks like this:

[MySQL]
Description = MySQL driver
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so
CPTimeout =
CPReuse =


The trick is to find where your driver and setup files are (libmyodbc.so and libodbcmyS.so) and point to them in /etc/odbcinst.ini

Now you can create a DSN that references your driver. (Ubuntu provides another example at /usr/share/doc/libmyodbc/examples/odbc.ini) Copy this to either your global /etc/odbc.ini or create one specific to your user at ~/.odbc.ini

[myodbc]
Driver = MySQL
Description = MySQL ODBC Driver DSN
Server = localhost
Port =
User = root
Password = secret
Database = test
Option = 3
Socket =


Now you should be good to go, provided you have the right server/user/password/database/port. Port defaults to 3306 (for MySQL) and I don’t Know what Option 3 does (MySQL 3.x?) The driver should be the same as the heading name specified in /etc/odbcinst.ini. Go ahead and try executing isql from the command line now

The syntax is
echo $query | isql $dsn

$query of course being replace by an sql statement such as “select * from user” and $dsn is the name of the heading in your odbc.ini file.

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> +---------------------------------------+
... your results in table format here ...
SQL>

If you run which gives you output — but by default, it’s in a format similar to what you’d see from the mysql or psql tool. Let’s add a few options to tell it to give us a comma separated output and include the column names as the first record (standard CSV output.)

query="select host,user,password from mysql.user where host != 'localhost'"
dsn="myodbc"
echo $query | isql $dsn -d, -c


That’s better, but we’ve still got all that junk. I originally piped it through a couple of shell scripts that looked like this

echo $query | isql $dsn -c -d, | grep -v ^"[\+\|]" | sed s/^SQL\>//g


which gets the job done (sortof) but a little bit of experimentation showed me that ‘-b’ (for “batch”) is what I want. So now my final command looks like

echo $query | isql $dsn -b -c -d, > file.csv


One problem that comes up is when you do “select * from tablename” in a shell variable, it gets expanded into a file glob, but that issue only comes up when you assign a variable named $query from the command line. It won’t come up in scripts or if you include the content of your query like

echo "select * from tablename" | isql -b -c -d,


or place your query in a file (recommended if you reuse it)

cat my_query.sql | isql $dsn -b -c -d, > file.csv


Advertisements

Redmine Test Case Management redux

A lot of people seem really interested in the Redmine TCM plugin.  Which would be great if I had something to share.  But I don’t even have something to use.  I’ve been swamped fighting fires doing manual regression testing at work, and haven’t even got a decent automation test suite going.  I’ve been managing complexity with sticky notes, and it’s not pretty (well, my desk does look kind of colorful with all those bule and orange and green sticky notes.)

Someone needs to kick my butt and get me to work on it.

Apart from finding time at work to build it (beyond it’s current prototype state that’s not integrated into redmine), here are the roadblocks I need to overcome:

1. Learn more about Rails.
   I’m still a novice, and I need to take a step back and do some more serious studying
2. Learn how to integrate it as a Redmine plugin.
   It shows up but doesn’t use redmne auth, isn’t in the menus, and doesn’t have any relation to “issues”. I need a good example for this, and the Timetracker plugin is probably the best bet.
3. Settle on a relatioship model.
   Right now it’s very basic.  A table of tests, and a table of test results.  I want to have something like:
   testCase =>testConditions
   testCase <-> testExecution
   testExecution -> testResult
   testRun<=testExecutions
   testRun => testEnvironments
   testSuite => testCases
   testRun => testCases
   Comments on testCase
   Notes testExecution
   Notes on testRun
4. Getting the UI to look the same

I also recently switched from Windows to Linux (hooray) so I don’t even have a redmine development environment setup.  It might take me a half day to get to the point where I can start coding and see where I left off.

I really want this to be a priority, but I’ve been spending my spare time on Resumelink for the past month, and will probably spend much of my spare time on it this month as well. Look for something in June.

My first 10 steps for creating a startup

Here’s my response (slightly edited) to a question posted on the Seattle Tech Startups mailing list.  The question was: “If you were to create a startup today, what would your first 10 steps be?”

It’s not just advice, it’s what I’ve done with Resumelink in the past month.

1. Pick a working name

Don’t dwell on it, you can always change it, but you’ve got to find
something that describes what your idea is in 1 or 2 words, and can be
used as a shorthand for “my idea for a product to do X”

2. Get a domain

Claim your territory online.  This should at least be close to your
name, but don’t necessarily let domain availability determine your name,
although it can be fun to try.  You don’t need a .com — but if you
find a good one, it can be worth building a business around.  Plan to
scoop up your .com with your series A round, if your name hasn’t changed
by then.

3. Start a blog

Talk about the idea (don’t worry, no one will read your blog yet.)
This is partially about branding, you want to start building a history
of content for search engines, but mostly it forces you to think about
what you are doing.  It’s a good way to talk to yourself, and who knows,
you might get feedback.  Maybe even a partner, investor, or customer.  At
least one competitor reads my blog, and we’re working on a partnership.

4. Create a mockup & spec

Something that captures your vision (not just version 1 features).
After you’ve talked to yourself enough that you have a pretty clear
vision, try and write up something more formal.  I don’t mean formal,
just *more* formal than blog posts that occur to you as you think of
them.  This is what you’re going to show your wife, friends, and
potential partners.  You’ll probably have to rewrite it a dozen times
anyway.  Keep the spec conversational, don’t use words like “shall”.
Draw your mockups on printer paper with a sharpie, or a notepad with a
pencil.  If you want, something like Balsamiq Mockups can help, but
don’t think you need something you can pass off to a designer or
developer to implement.

5. Pick a first feature, and build a prototype/demo.

Don’t care about efficiency, beauty, scalability, security, or anything.
The idea is just to give people something to show.  Instead of filling
out a complex form, doing validation & fancy stuff on the server
side, just have a button that always shows “this is what it looks like
after you did X with a given input.”  It shouldn’t be complex.  It
should be one scenario, and shouldn’t take the user more than a minute
to complete.  Think more complex than a “contact me” form, but less
complex than Excel.  If you’re building a spreadsheet, a 10×10
table that you can edit and add formulas would be a great prototype
(just make the only formula that works “cell1+cell2+…cell9=cell10”).

6. Mention your idea to 10 people and get their feedback

Of course, you’ve probably already done this to friends and family, and
if you’re like me your wife is tolerant, but probably sick of hearing
about it in the middle of the night when she just got back from feeding
the baby.  If you’re shy like me, go ahead and count friends and family,
but pick 10 people from diverse backgrounds, not all techies, or not
only people in your model rocket club (especially if websites for model
rocket clubs is your idea.)

7. Identify 5 potential customers and 5 potential competitors

Google search keywords you would use if you were advertising.  You can
probably already identify some.  Not only will researching competitors
let you know what you are up against, but it’s something you’ll be
expected to know.  And being able to say “We’re like X because … but
we’re different from X because …” helps you clarify your market
position and is a quick way to explain it to others.  I actually
described some of my competitors on my blog and got feedback from
them.  If you can’t identify actual potential customers, by name, with
telephone numbers and email address, how are you going to sell to them?

8. Write a “pitch deck”

This is a good exercise even if you’re not looking for investors. A good
description of what should be in a pitch deck comes from the #1 Google
search result for “pitch deck”
http://startups.nuvvo.com/lesson/348-how-to-make-a-pitch-deck
Powerpoint is the original twitter, and a harder disciplinarian.  Try to
get 140 letters on a slide with 30 point font.  I dare you.

9. Get a logo and graphic design for your site

Now it’s time to start getting real.  You need to look professional.  If
you’ve got the chops, do it yourself.  But pay a few bucks and get a
website design (or at least a logo) you can be proud of.  This can be a
frustrating process, but once you are done it gives you a new level of
confidence telling people about your project — because it’s not just an
idea anymore.  There is actually something there that people can look
at, and you’ve invested real time and money.

10. Start showing what you’ve got to people and iterate.

This is where I’m at.  I’m still working on the prototype and haven’t
gotten a good web design yet, but people can register, and get a general
idea for the product.  And with a lot of manual work on the back end, I
can actually deliver the service.

Check it out at http://resumelink.org