Perl DBI Overview

An Interactive Statement-Execution Program

We are now in a position to put together much of what we've developed so far and use it to write a simple interactive statement-execution client, stmt_exec. This program lets you enter statements, executes them using our general purpose statement handler process_statement(), and displays the results using the process_result_set() display formatter developed in the preceding section.

stmt_exec is similar in some ways to mysql, although of course with not as many features. There are several restrictions on what stmt_exec will allow as input:

• Each input line must contain a single complete statement.

• Statements should not be terminated by a semicolon or by \g.

• The only non-SQL commands that are recognized are quit and \q, which terminate the program. You can also use Ctrl-D to quit.

It turns out that stmt_exec is almost completely trivial to write (about a dozen lines of new code). Almost everything we need is provided by our client program skeleton (connect2.c) and by other functions that we have written already. The only thing we need to add is a loop that collects input lines and executes them.

To construct stmt_exec, begin by copying the client skeleton connect2.c to stmt_exec.c. Then add to that the code for the process_statement(), process_result_set(), and print_dashes() functions. Finally, in stmt_exec.c, look for the line in main() that says this:

 /* ... issue statements and process results here ... */ 

Replace that line with this while loop:

 while (1) {     char    buf[10000];     fprintf (stderr, "query> ");                    /* print prompt */     if (fgets (buf, sizeof (buf), stdin) == NULL)   /* read statement */         break;     if (strcmp (buf, "quit\n") == 0 || strcmp (buf, "\\q\n") == 0)         break;     process_statement (conn, buf);                  /* execute it */ } 

Compile stmt_exec.c to produce stmt_exec.o, link stmt_exec.o with the client library to produce stmt_exec, and you're done. You have an interactive MySQL client program that can execute any statement and display the results. The following example shows how the program works, both for SELECT and non-SELECT statements, as well as for statements that are erroneous:

 % ./stmt_exec query> USE sampdb 0 rows affected query> SELECT DATABASE(), USER() +------------+-------------------+ | DATABASE() | USER()            | +------------+-------------------+ | sampdb     | sampadm@localhost | +------------+-------------------+ 1 rows returned query> SELECT COUNT(*) FROM president +----------+ | COUNT(*) | +----------+ |       42 | +----------+ 1 rows returned query> SELECT last_name, first_name FROM president ORDER BY last_name LIMIT 3 +-----------+-------------+ | last_name | first_name  | +-----------+-------------+ | Adams     | John        | | Adams     | John Quincy | | Arthur    | Chester A.  | +-----------+-------------+ 3 rows returned query> CREATE TABLE t (i INT) 0 rows affected query> SELECT j FROM t Could not execute statement Error 1054 (42S22): Unknown column 'j' in 'field list' query> USE mysql Could not execute statement Error 1044 (42000): Access denied for user 'sampadm'@'localhost' to  database 'mysql'