Hack 1. Run SQL from the Command Line

The command-line processor is the lowest common denominator when it comes to running SQL, but you'll find plenty of times when it comes in handy.

All of the popular SQL engines (except Access) have serviceable command prompt utilities that are installed by default, and all provide roughly the same benefits:

• You can type in SQL and see the results or error messages displayed immediately.
• You can start them up from an operating system prompt.
• You can specify the username and password that you want to use.
• You can pipe SQL statements in from another process.

This provides a flexible mechanism that is ideal for executing ad hoc SQL statements or developing queries that will eventually be used in applications.

The examples in this hack connect to a database on localhost called dbname with user scott and password tiger.

1.1.1. Pipe into SQL

One of the useful features of a command-line interface is the pipe, which chains a sequence of commands so that the output of one is the input for the next. You can use a pipe on Windows under the command prompt or on Linux/Unix using a shell. If you put your SQL command-line utility at the end of a pipe, the result is processed as SQL. For example, a common operation is to use a pipe to send a sequence of INSERT statements to your SQL command-line utility. Here's an example that runs on the Windows command prompt, but could also work on a Unix or Linux system if you used the appropriate SQL command-line utility (these are described later in this hack).

You must type this entire command on one line. On Unix or Linux, you could put a character (the line-continuation character) before the line break:

C:>perl -pe "s/DATE //g; " < cmnd.sql | sqlcmd -U 
scott -P tiger -d dbname -n
(1 row affected)
(1 row affected)

The preceding code takes a file of SQL commands, cmnd.sql, as input; it redirects it into a Perl script using the < operator, then pipes the output to sqlcmd.

The file cmnd.sql contains the following SQL statements:

INSERT INTO test(d,txt) VALUES (DATE '2007-01-01','row one');
INSERT INTO test(d,txt) VALUES (DATE '2007-01-02','row two');

The system will not accept input as it stands because date literals in SQL Server should be formatted as '2007-01-01' rather than DATE '2007-01-01'. The Perl used here performs a search and replace to remove the keyword DATE from the cmnd.sql input.

1.1.2. Switches

To use the command line you will need to know how to use the switches on the operating system command line. In the example shown in the preceding section, you have to specify the username and password using the U and P switches; without them the first two lines from the file cmnd.sql would be used as the username and password. You also need to specify the database to use with the d switch; without it you would have to have the lines use dbname and go as the first two lines of the input file. The -n switch is there to suppress the >1 prompt that you normally see when using sqlcmd interactively.

1.1.3. Microsoft SQL Server

The basic command prompt editor is sqlcmd (osql on older systems). You need to use either the U switch to specify the username or the E switch if you are using Windows authentication.

If you want to get your own copy of SQL Server, check out the SQL Server Express edition, which is available for free from Microsoft (http://msdn.microsoft.com/vstudio/express/sql).

A peculiar thing about sqlcmd is that it requires that you enter the word GO after every command (there is an implicit GO at the end of the file when you run in batch mode, as shown earlier). You can edit the current line and use the up and down arrow keys to access previous statements. Here are some common tasks you can perform using sqlcmd:

 

Get into SQL Server

A variation of this command should work under many SQL Server installations:

C:> sqlcmd U scott P tiger

 

If you are using Microsoft SQL Server Express edition or a version of SQL Server that was bundled with development tools, you may need to specify an instance name. For example, under the Express edition, the default instance is SQLEXPRESS (note also the use of -E for integrated authentication, which is the default configuration for SQL Server Express):

C:> sqlcmd E S (local)SQLEXPRESS

 

If your SQL Server came with another product, such as Visual Studio, you should check the documentation.

 

List your tables in SQL Server

If you want to see which tables are available in a given database, run these commands from within the sqlcmd utility:

1> use  dbname 
2> GO 
Changed database context to 'dbname '. 
1> sp_help 
2> GO 

 

The format of the output of sp_help is difficult to read on an 80x24 command window, so the following SELECT might be more useful:

1> SELECT name FROM sysobjects WHERE type='U'
2> GO

 

Import a file of SQL into SQL Server

You can do this from the Windows command prompt with the i switch:

C:> sqlcmd U scott d dbname i c:file.sql

 

1.1.4. Oracle

The Oracle command-line interface is called SQL*Plus. Use the program sqlplus on the operating system command line.

1.1.4.1. Getting into Oracle

To get into Oracle, use:

$ sqlplus scott/tiger

 

1.1.4.2. List your tables in Oracle

To list your tables, use:

SQL> SELECT * FROM cat;

sqlplus tends to display wide columns, which makes it difficult to see the output from even a two-column view such as cat. You can set the column widths to be used for a session if you know the name of the columns. The two columns of the cat view are TABLE_NAME and TABLE_TYPE:

SQL> COL table_name FORMAT a20;
SQL> COL table_type FORMAT a20;
SQL> SELECT * FROM cat;
TABLE_NAME TABLE_TYPE
-------------------- --------------------
AGENCY_TRADE TABLE
AGENCY_TRADE1 TABLE
INCORRECT TABLE
beatles TABLE
CORRECT TABLE
TMP TABLE
EMP_VIEW VIEW
EMPVIEW VIEW
SUITOR TABLE
HAS TABLE

 

1.1.4.3. Import a file of SQL into Oracle

Use the start command from the sqlplus prompt. If your file includes ampersand (&) characters, they may cause you problems, unless you issue SET DEF OFF first:

SQL> SET DEF OFF;
SQL> START file.sql

An alternative approach is to use the @ command. It automatically adds the extension .sql to the filename:

@file

 

1.1.5. MySQL

The MySQL command-line utility is a joy to use. You can use the up arrow key to get to previous commands and the system will display the results sensibly. There are masses of useful switches to change the default behavior of the client. Use mysql --help to see some of these options.

1.1.5.1. Getting into MySQL

Here's how to start up MySQL:

$ mysql u scott ptiger dbname

 

1.1.5.2. List your tables in MySQL

The show tables command does what you would expect:

$ mysql -uscott -ptiger dbname
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 39097 to server version: 5.0.18-standard

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> show tables;
+------------------+
| Tables_in_dbname |
+------------------+
| Perm |
| Table1 |
| aToA |
| access_log |
| actor |

 

1.1.5.3. Import a file of SQL into MySQL

The source command will read and execute a file of SQL:

mysql> source file.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

 

1.1.6. Access

Many Access users rely exclusively on the graphical interface for querying and database design tasks. You can also use more or less standard SQL for building queries, creating tables, and so on. If you can't figure out how to do something from the GUI, start a new query and do it in SQL.

1.1.6.1. Getting into SQL in Access

To get to the SQL interface you first need to create a new query from the database pane. If the Show Table dialog pops up while you are doing this, close it without selecting anything. Once you've created and opened a query, choose SQL View from the View menu, as shown in Figure 1-1. Access supports most standard SQL statements, including all the CREATE and DROP commands, as well as subqueries. Choose QueryRun to execute the query youve typed into the SQL view window. If you've issued a query that generates results (for example, a SELECT statement rather than an INSERT or UPDATE), you'll need to choose Viewimages/U2192.jpg border=0>SQL View to return to the SQL view window.

Figure 1-1. Getting to SQL View on a new query

 

1.1.6.2. Import a file of SQL commands

This vital tool is missing from Access, but a handful of lines of Visual Basic will do the job:

DoCmd.SetWarnings False
Open "c:ch01Access.sql" For Input As 1
Dim sql As String
Dim txt As String
While Not EOF(1)
 Line Input #1, txt
 sql = sql & txt & vbCrLf
 If Len(txt) > 0 And Right(txt, 1) = ";" Then
 DoCmd.RunSQL sql
 sql = ""
 End If
Wend
Close 1

To run this code, you could insert it into an event handler, such as the On Click event of a button.

Sometimes a .mdb file has been set up to hide the database pane and auto-start another form. Hold down the Shift key before double-clicking the .mdb file to get around this. To prevent the Shift key from working in this way create the database as a .mde file.

 

1.1.7. PostgreSQL

To work with PostgreSQL, you'll be using the psql utility.

1.1.7.1. Getting into SQL in PostgreSQL

The Postgres command-line utility is called psql. It uses up and down arrows to recover previous commands and will pause long lists in the more style:

$ psql -d dbname -U scott

 

1.1.7.2. List your tables in Postgres

The dt (directory of tables) command will list your tables. ? shows you all the other slash commands:

$ psql -d dbname -U scott
Password:
Welcome to psql 7.3.2, the PostgreSQL interactive terminal.

Type: copyright for distribution terms
 h for help with SQL commands
 ? for help on internal slash commands
 g or terminate with semicolon to execute query
 ' to quit

dbname=> dt
 List of relations
 Schema | Name | Type | Owner
--------+-------------------------------+-------+-------
 public | INT | table | scott
 public | TBL_CALLS | table | scott
 public | a | table | scott
 public | a1 | table | scott
 public | a401478 | table | scott
 public | a_test | table | scott
 public | aaa | table | scott
 public | aad_casos_especiales | table | scott

The commands include some useful options. Only the first few are given here:

dbname=> ?
 a toggle between unaligned and aligned output mode
 c[onnect] [DBNAME|- [USER]]
 connect to new database (currently "scott")
 C [STRING] set table title, or unset if none
 cd [DIR] change the current working directory
 copy ... perform SQL COPY with data stream to the client host
 copyright show PostgreSQL usage and distribution terms
 d [NAME] describe table, index, sequence, or view
 d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
 list tables/indexes/sequences/views/system tables
 da [PATTERN] list aggregate functions
 dd [PATTERN] show comment for object
 dD [PATTERN] list domains
 df [PATTERN] list functions (add "+" for more detail)

 

1.1.7.3. Import a file into PostgreSQL

The i command will import a file of SQL commands:

dbname=> i file.sql

 

1.1.8. DB2

DB2's command-line utility is a command-line processor (CLP) and you can start it with db2. You should not use semicolons to separate SQL statements when using DB2.

The db2 system has an astonishing parser. It has no need for statement separators and it seems to accept almost anything as a table name or column name.

With db2 you can create a table called from with columns called select and from. Incredibly, the parser deals with every one of the perfectly legal SQL statements; can you figure out what they do?

SELECT FROM FROM FROMSELECT FROM FROM FROM FROMSELECT 'FROM' FROM FROM, FROM FROMSELECT 'FROM' FROM FROM FROM, FROM FROM SELECT FROM MORF FROM FROM MORF

 

It is probably best not to use these names.

You can base authentication and authorization on your operating system account, so you may not need a username or password:

$ db2
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 SDK 8.1.2

You can issue database manager commands and SQL statements from the command
prompt. For example:
 db2 => connect to sample
 db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to scott

 Database Connection Information

 Database server = DB2/LINUX 8.1.2
 SQL authorization ID = ANDREW
 Local database alias = SCOTT

db2 => list tables

Table/View Schema Type Creation time
--------------------------- --------------- ----- --------------------------
TEST1 ANDREW T 2006-07-17-14.13.35.844330

 1 record(s) selected.

 

1.1.8.1. Import SQL into DB2

You can use the db2batch utility to import a file of SQL commands into DB2.