Client 4 - An Interactive Query Processor

Client 3 ”An Interactive Query Processor

In this section, we'll build an interactive command processor in Tcl/Tk. Fortunately, we can reuse most of the code that we developed in client2.tcl . I'll explain the differences and point out where we can share code with the previous client.

Figure 16.5 presents what we are trying to build.

Figure 16.5. The client3.tcl ”results.

You can see that this application is similar to the previous application. I've added a few widgets: a label at the top of the window that tells the user what to do, a text entry widget where you enter commands, and a status bar that gives feedback.

Now, let's look at the code. We have to change three procedures to transform client2.tcl into client3.tcl . Listing 16.19 shows the main procedure for the third client.

Listing 16.19 client3.tcl ” main

 1 #!/usr/local/bin/wish  2 #  3 # Filename: client3.tcl  4  5 proc main { } {  6  7   wm withdraw .  8  9   package require Tktable 10 11   set conn [connect] 12 13   if { $conn != {} } { 14 15     build_dialog $conn 16 17     tkwait window .top 18 19     pg_disconnect $conn 20   } 21 } 

If you compare this to the main procedure from client2.tcl (refer to Listing 16.9), you'll see that the only difference is that I have removed the call to process_command . In the new application, the query is not hard-coded into the application ”you prompt the user for a command string instead. So, after connecting to the server, you call build_dialog to construct the user interface and then wait for the dialog window to close.

Listing 16.20 shows the build_dialog procedure.

Listing 16.20 client3.tcl ” build_dialog

 23 proc build_dialog { conn } { 24 25   toplevel .top 26 27   wm title .top "client3" 28 29   set table [make_table .top] 30 31   button .top.close -text "Close Window" -command {exit} 32 33   label .top.label   -text "Enter an SQL Command and Press Return" 34   text  .top.command -height 3 35   label .top.status 36 37   focus -force .top.command 38 39   bind .top.command <Return> \ 40       "process_command $conn $table \[.top.command get 1.0 end\]" 41 42   scrollbar .top.sy -command [list $table yview] 43   scrollbar .top.sx -command [list $table xview] -orient horizontal 44 45   grid     .top.label 46   grid    .top.command      -sticky news 47   grid     .top.status 48   grid   $table .top.sy     -sticky news 49   grid      .top.sx         -sticky ew 50   grid     .top.close 51 52   grid columnconfig .top 0 -weight 1 53 54   grid rowconfig    .top 1 -weight 0 55   grid rowconfig    .top 3 -weight 1 56   grid rowconfig    .top 5 -weight 0 57 } 

The build_dialog procedure is a little longer than it used to be, but not any more complex. I've added a label (line 33) that displays a prompt to the user. I've also added a text widget. The text widget (named .top.command ) is where you'll type in your PostgreSQL commands. A text widget is a like a multiline entry widget ”you configure it to be three lines tall. We also add a second label widget ( .top.status ), in which, we will display the status of each command. Refer to Figure 16.5; the .top.status widget is positioned between the text entry widget and the table widget.

Line 37 forces the keyboard focus to .top.command (the text entry widget).

Next, you bind a piece of Tcl code to the Return key. This piece of code executes whenever the user presses the Return key while the .top.command widget has the focus.

Line 40 might look a bit cryptic. It might be easier to understand if you walk through the evaluation process that Tcl will use when it executes our code snippet.

When you call the bind procedure, Tcl will evaluate the code segment, performing variable substitution wherever it sees an unquoted dollar sign. So, if $conn contains pg224 and $table contains .top.table , the first iteration translates from

 process_command $conn $table \[.top.command get 1.0 end\] 

to

 process_command pg224 $table \[.top.command get 1.0 end\] 

Next, Tcl translates the second variable substitution to

 process_command pg224 .top.table \[.top.command get 1.0 end\] 

Finally, Tcl removes the escape characters from the string, resulting in

 process_command pg224 .top.table [.top.command get 1.0 end] 

At this point, Tcl stops evaluating the code snippet. It binds this final string to the Return key. When the Return key is pressed, Tk will execute this string. The last part of the string ( [.top.command get 1.0 end] ) extracts the contents of the text entry widget.

The net effect is that the process_command procedure is called whenever the user presses the Return key, and the text of the command is passed as the final parameter.

The rest of the code in build_dialog should be pretty familiar. We create a vertical and horizontal scrollbar and then arrange everything using the grid layout manager.

The final three lines in this procedure ensure that the text entry widget and the Close Window button remain visible if you resize the application window.

Now, let's look at the process_command procedure (see Listing 16.21).

Listing 16.21 client3.tcl ” process_command

 75 proc process_command { conn table command } {  76  77   set result_set [pg_exec $conn $command]  78  79   switch [pg_result $result_set -status] {  80  81     PGRES_EMPTY_QUERY {  82       .top.status configure -text ""  83     }  84  85     PGRES_TUPLES_OK {  86       .top.status configure -text "Ok"  87       load_table $table $result_set  88     }  89  90     PGRES_COMMAND_OK {  91       .top.status configure -text "Ok"  92     }  93  94     default  95     {  96       .top.status configure -text ""  97  98       tk_messageBox -title [pg_result $result_set -status] \  99                     -message [pg_result $result_set -error] \ 100                     -type ok 101     } 102   } 103 } 

The process_command procedure has changed considerably. In the previous version (refer to Listing 16.12), a couple of assumptions were made that need to be corrected here if you want to process arbitrary commands. First, it was assumed that the command executed successfully. If you are executing something other than a hard-wired command, you must expect errors to occur (of course, you really should expect errors, even when you know which commands are going to execute). The second assumption was that you were executing only SELECT commands. Again, you have to handle any type of command if you let the user enter arbitrary text.

Like before, call the pg_exec procedure to execute the command provided by the caller.

Next, examine the value returned by pg_result -status to determine what kind of result set you have. As I mentioned earlier, pg_result -status returns values such as PGRES_TUPLES_OK , PGRES_COMMAND_OK , PGRES_FATAL_ERROR , and so on. You will handle three of these values explicitly and assume that anything else is a message that you should display to the user.

The simplest case occurs when the user presses the Return key without entering a command. When that happens, pg_result -status will return PGGRES_EMPTY_QUERY . In this case, clear the status line ( .top.status ) and return.

Next, handle PGRES_TUPLES_OK . pg_result -status returns PGRES_TUPLES_OK when you (successfully) execute a SELECT command. Handling the result set from a SELECT command is something you already know how to do; you set the status line to Ok and call the load_table procedure to copy the result set into the table widget. The load_table procedure is unchanged from client2.tcl .

pg_result -status returns PGRES_COMMAND_OK when you successfully execute a command other than SELECT . This one is easy ”you just set the status line to read Ok . If you were really energetic , you might also display the OID from the previous command ( pg_result -oid ).

Finally, assume that any other return code is a message that you should simply display to the user. After clearing the status line, use the tk_messageBox to display the status ( pg_result -status ) and error message ( pg_result -error ).

That's it. All the other procedures in client3.tcl are identical to those in client2.tcl .

Run this application a few times to see how it behaves. Be sure to feed it a few errors so you can see the error handling in action (how exciting).

I'll wrap up this chapter by describing how to access large-objects from a Tcl application.