Discuss New Concept,New Technic,New Tools, Including EAI,BPM,SOA,Tibco,IBM MQ,Tuxedo, Cloud,Hadoop,NoSQL,J2EE,Ruby,Scala,Python, Performance,Scalability,Distributed,HA, Social Network,Machine Learning.

February 14, 2011

In this blog post I will introduce SQLShell and demonstrate, step-by-step, how to install it and start using it with MySQL. I will also reflect on the possibilites of using this with NoSQL technologies, such as HBase, MongoDB, Hive, CouchDB, Redis and Google BigQuery.

SQLShell is a cross-platform, cross-database command-line tool for SQL, much like psql for PostgreSQL or the mysql command-line tool for MySQL.

## Why Use It?

If you already use only one command-line tool, such as PostgreSQL’s psql or MySQL’s mysql tool and are happy then you may not need to. If you find yourself jumping between several of these tools and would like common functionality, or you are a fan of the many NoSQL technologies out there, then this is worth keeping an eye on.

### JDBC Driver Support

SQLShell is built in Scala. Scala is a scalable programming language that compiles to Java byte code, can run on the JVM and can be used alongside Java code. Therefore, SQLShell can interface to any database for which there is a JDBC (Java Database Connectivity) driver. Encase you do not know, there are many.

#### JDBC Drivers Available

• RDBMS Databases
• There are several JDBC drivers for many NoSQL technologies, but none of these implement JDBC fully enough to SQLShell, yet.

Something to note about Google’s recent BigQuery (part of Google Storage) is that, while they do not have a JDBC driver, yet, they do internally make use of sqlcmd with BigQuery. Sqlcmd is also created by Brian M. Clapper at Clapper.org, but he has discontinued development of it in favor of the new SQLShell. Therefore, I think there is a good chance that Google may soon develop a JDBC driver compatible with SQLShell. That said, Google does like Python and sqlcmd is written in Python.

Brian M. Clapper, author of SQLShell, writes about the move from Python to Scala in the SQLShell user-guide.

SQLShell is a SQL command line tool, similar in concept to tools like Oracle’s SQL Plus, the PostgreSQL psql command, and the MySQL mysql tool.

SQLShell is a Scala rewrite of my Python sqlcmd tool (rewritten because, as it turns out, I think JDBC is more consistent and portable than Python’s DB API).

– Brian M. Clapper, author of SQLShell

## Installing SQLShell

You can download a pre-compiled installation JAR file for SQLShell or compile the binary yourself. Downloading the JAR is recommended and I have download version 0.7.1

curl -O http://cloud.github.com/downloads/bmc/sqlshell/sqlshell-0.7.1-install.jar

Running this installer can be done with the java command, which will launch a graphical installer. The graphical installer uses IzPack, which is a cross-platform installation framework. Therefore, even though I am using Mac OS X, you should not have any problems installing on Windows or Linux.

java -jar sqlshell-0.7.1-install.jar

First thing you see if all goes well is the language prompt. I am speak the English.

Following this you will see a welcome screen, then a intro and info page, and then the license page. Be sure to read the license carefully – especially the part about “your first born child”.

Here is what you will see for the intro screen.

When you get to the end, you will see the following message…

You’ve successfully installed SQLShell.

For your convenience, a command-line wrapper script has been provided in
the “bin” directory under “/Applications/clapper.org/sqlshell”.

That tells you where sqlshell is installed. Under this directory is the bin directory, which contains sqlshell. It’s a command-line tool, and since we will be using it often, I’ll add the path of that bin directory to my PATH.

echo 'export PATH=\$PATH:/Applications/clapper.org/sqlshell/bin' >> ~/.profile
source ~/.profile
which sqlshell || echo "Not found in path" # This tests that it's found in your PATH

We can see what parameters it expects, by running sqlshell with the –help argument.

sqlshell --help

SQLShell, version 0.7.1 (2010/11/10 17:27:55)

Usage: sqlshell [OPTIONS] db [@file]

OPTIONS

-?
-h
--help                Show this usage message.

-V
--version             Show version and exit.

-c config_file
--config config_file  Specify configuration file. Defaults to:
/Users/phil/.sqlshell/config

-n
--no-ansi
--noansi              Disable the use of ANSI terminal sequences. This option
just sets the initial value for this setting. The value
can be changed later from within SQLShell itself.

-r lib_name
--readline lib_name   Specify readline libraries to use. Legal values:
editline, getline, gnu, jline, simple. (May be specified
multiple times.)

-s
--stack               Show all exception stack traces.

-v
--verbose             Enable various verbose messages. This option just sets
the initial verbosity value. The value can be changed
later from within SQLShell itself.

PARAMETERS

db     Name of database to which to connect, or an on-the-fly database
specification, of the form:

driver,url,[user[,password]]

If the name of a database is specified, SQLShellwill look in the
configuration file for the corresponding connection parameters. If a
database specification is used, the specification must one argument. The
driver can be a full driver class name, or a driver alias from the
configuration file. The user and password are optional, since some
databases (like SQLite) don't require them at all.

@file  Path of file of commands to run


## SQLShell With MySQL

### Install MySQL’s JDBC Driver

Download the driver from MySQL’s website.

curl -O http://mysql.mirror.iweb.ca/Downloads/Connector-J/mysql-connector-java-5.1.14.tar.gz
tar xvzf mysql-connector-java-5.1.14.tar.gz
cd mysql-connector-java-5.1.14
cp mysql-connector-java-5.1.14-bin.jar /Applications/clapper.org/sqlshell/lib/

Above, we copied the mysql-connector-java-5.1.14-bin.jar file to SQLShell’s lib. SQLShell will load all the JAR files found in that directory at start-up, and will then reference them by package and class name. We will use the class name of the MySQL driver to configure a “mysql” alias in the SQLShell configuration.

To configure the “mysql” alias we will edit the default configuration file ~/.sqlshell/config, which currently does not exist.

mkdir ~/.sqlshell
vim ~/.sqlshell/config # I use vim to edit files

Add the following configuration…

[drivers]
mysql = com.mysql.jdbc.Driver

### Connect To MySQL Using SQLShell

The format of your connection string for the MySQL JDBC driver should be

jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

I’m just going to connect to the local mysql database and look at the pre-installed MySQL database called “test”. You should have the same database, unless you deleted it.

sqlshell mysql,jdbc:mysql://localhost/test?user=root
SQLShell, version 0.7.1 (2010/11/10 17:27:55)
Copyright (c) 2009-2010 Brian M. Clapper
Using JLine
Type "help" for help. Type ".about" for more information.

sqlshell>

Ok, we are connected to MySQL and can start running some queries.

We can see the commands that we can run using the help command.

sqlshell> help
Help is available for the following commands:
-------------------------------------------------------------------------------
.about    .capture  .desc     .echo     .run      .set      .show
alter     begin     commit    create    delete    drop      exit
help      history   insert    r         rollback  select    update

Let’s see what databases we have…

sqlshell> show databases;
Execution time: 0.26 seconds
Retrieval time: 0.21 seconds
2 rows returned.

SCHEMA_NAME
------------------
information_schema
test

sqlshell>

We can create an new database.

sqlshell> create database sqlshell_test;
1 row affected.
Execution time: 0.5 seconds

SQLShell passes SQL statements in full to the database, so you can use any commands that your database understands.

## Conclusion

The initial purpose of this blog post was to demonstrate SQLShell with a NoSQL database, but unfortunately I failed to find a JDBC driver that implemented all the features that SQLShell requires. I’m hoping that development of such drivers will continue, as it will be good to have standard inferface to all these NoSQL technologies directly from the command-line. I look forward to seeing how SQLShell and these JDBC develop over time.

original:http://www.philwhln.com/sqlshell-a-cross-database-sql-tool-with-nosql-potential