sql : a mSQL command line tool

sql is a command line tool for a formated output of mSQL tables. A lot of options can specify the desired output format. It even has the capability to write mSQL tables in HTML format or to use it as a simple Perl interface.

Table of contents

• download sql.tar.gz
• sql manual
• some examples
• generating HTML tables
• sql as a Perl interface
• sql copying policy
• other intersting links
• about the author

NAME

sql - mSQL command line tool

SYNOPSIS

sql [-fiaktlnqub] [-b line_begin] [-s seperator] [-e line_end] [-h host] table

DESCRIPTION

sql reads the SQL command from standart input and prints the result to the standart output.

-s seperator

defines the seperator between the record fileds. The default seperator is ":". You can define a char or a string. Special characters, like \n \t \r \f \b \a \e and \\ are interpreted.

-b line_beginn

defines the character or string at the beginning of each line. The default is "".

-e line_end

defines the character or string at the end of each line. The default is "\n".

-f

the output is formatted, that means, every field is printed with the length specified in the mSQL database regardeless of the actual length.

-n

print the name of the fields on top of the output

-l

print the length of the fields on top of the output

-k

print the key info of the fields on top of the output. "P" for primary key and "N" for not null.

-t

prints the type of the fields on top of the output. INT for an integer (long), CHAR for a string or character and B>REAL for a floating point number.

-u

under line the header of the table (name, key info, type and length)

-i

prints the line number at the beginning of each line.

-q

prints the query too (see examples)

EXAMPLES

A little movie database I wrote ...

echo "select * from Filmlex" | sql Filmlexikon
a simple Query

12-monke::12 Monkeys:USA:1995:x:x
2-girls::Two Girls In Love:USA:1995:x:
2-leben:Die:zwei Leben der Veronika:P/F/D:1991:x:
drei-bla::Drei Farben Blau:P/F:1993:x:
4-rooms::Four Rooms:USA:1995:x:
7-footpr::Seven Footprints to Satan:USA:1929:x:

echo "select * from Filmlex" | sql -s "\t" Filmlexikon
use tabulators as seperator

12-monke                12 Monkeys      USA     1995    x       x
2-girls         Two Girls In Love       USA     1995    x
2-leben Die     zwei Leben der Veronika P/F/D   1991    x
drei-bla                Drei Farben Blau        P/F     1993    x
4-rooms         Four Rooms      USA     1995    x
7-footpr                Seven Footprints to Satan       USA     1929    x

echo "select * from Filmlex" | sql -b "<TR><TD>" -s "</TD><TD>" -e "</TD></TR>\n" Filmlexikon
a HTML table, I added the <TABLE BORDER> and the </TABLE> tag to the
beginning and the and of the HTML table

12-monke		12 Monkeys	USA	1995	x	x
2-girls		Two Girls In Love	USA	1995	x
2-leben	Die	zwei Leben der Veronika	P/F/D	1991	x
drei-bla		Drei Farben Blau	P/F	1993	x
4-rooms		Four Rooms	USA	1995	x
7-footpr		Seven Footprints to Satan	USA	1929	x

echo "select * from Filmlex" | sql -q Filmlexikon
the q-option

>query
select * from Filmlex
>result
12-monke::12 Monkeys:USA:1995:x:x
2-girls::Two Girls In Love:USA:1995:x:
2-leben:Die:zwei Leben der Veronika:P/F/D:1991:x:
drei-bla::Drei Farben Blau:P/F:1993:x:
4-rooms::Four Rooms:USA:1995:x:
7-footpr::Seven Footprints to Satan:USA:1929:x:

echo "select * from Filmlex" | sql -fiknult -b "|" -s "|" -e "|" Filmlexikon
turn everything on

-----------------------------------------------------------------------------------------
|-5  |Datei   |Artikel|Film                         |Land            |Jahr |Kurz |Kritik|
|-4  |8       |4      |30                           |16              |4    |1    |1     |
|-3  |CHAR    |CHAR   |CHAR                         |CHAR            |CHAR |CHAR |CHAR  |
|-2  |PN      |       | N                           |                |     |     |      |
-----------------------------------------------------------------------------------------
|0   |12-monke|       |12 Monkeys                   |USA             |1995 |x    |x     |
|1   |2-girls |       |Two Girls In Love            |USA             |1995 |x    |      |
|2   |2-leben |Die    |zwei Leben der Veronika      |P/F/D           |1991 |x    |      |
|3   |drei-bla|       |Drei Farben Blau             |P/F             |1993 |x    |      |
|4   |4-rooms |       |Four Rooms                   |USA             |1995 |x    |      |
|5   |7-footpr|       |Seven Footprints to Satan    |USA             |1929 |x    |      |
-----------------------------------------------------------------------------------------

Use sql with Perl
sql as a sipmle interface to Perl

open(SQL, "echo \"SELECT * FROM Filmlex\" | sql Filmlexikon |");
$i = 0;
while(<SQL>) {
    m/(.*):(.*):(.*):(.*):(.*):(.*):(.*)\n/;
    $film[$i] = $2 . " " . $3;
    $land[$i] = $4;
    $jahr[$i] = $5;
    $kritik[$i] = $7;
    $i++;
}
close(SQL);

COPYING POLICY

This software may be freely copied, modified, and redistributed,
provided that this copyright notice is preserved on all copies.

There is no warranty or other guarantee of fitness for this software,
it is provided solely "as is". Bug reports or fixes may be sent
to the author, who may or may not act on them as he desires.

You may not include this software in a program or other software product
without supplying the source, or without informing the end-user that the
source is available for no extra charge.

If you modify this software, you should include a notice giving the
name of the person performing the modification, the date of modification,
and the reason for such modification.

OTHER INTERESTING LINKS

Home of mSQL

Hughes Technologies Pty Ltd

mSQL-FAQ

als Text oder im HTML-Format

oder die allerneuste Version.

mSQL Mailing List Archive by thread

mSQL Mailing Lists Archives Search Engine

mSQL Discussion Forum

This Forum is rather new, maybe one more reason to use it.