5.8. Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures

This example is the second part of a GIF picture viewer which stores the images as bytea fields in the database, and uses prepared queries to retrieve the images without having to convert from or to an ASCII encoding of binary data. Given an identifier on the command line, it attempts to retrieve and display the stored picture with that identifier.

See Section 5.7, “Example - Prepared Queries - Picture Viewer, Part 1 - Store Pictures” for the program used to insert the pictures in the database.

The prepared query, pics_select, is used to select the data from the table, using the picture name as the primary key. It takes one parameter: a text type for the picture name, and returns one bytea type which is the image itself. When pg_exec_prepared is used to execute the prepared query, one TEXT format parameter, the name, is passed to the prepared query, and the result is one BINARY format value, the image data.

This example runs under wish, not tclsh. It also assumes database connection information is provided through the environment.

Example 5.15. Prepared Queries - View Pictures Stored in Database

# Example - picture storage as bytea - viewer

package require Pgtcl

# Create the prepared SQL statement:
#  pics_select(name) - Return the picture data for the name.
# Note that the query parameters do not need to be quoted or escaped.
# Implicit error handling: will throw a Tcl error if it fails.
proc prepare_statement {conn} {
    pg_execute $conn "PREPARE pics_select (TEXT) AS\
        SELECT picture FROM pics WHERE pname=\$1"

# Return the picture data identified by 'name'.
# Throw an error if it can't be read.
proc get_picture {conn name} {

    set result [pg_exec_prepared $conn pics_select BINARY TEXT $name]
    if {[pg_result $result -status] != "PGRES_TUPLES_OK"} {
        set message [pg_result $result -error]
        pg_result $result -clear
        error "Error: Query failed: $message"

    if {[set n [pg_result $result -numTuples]] != 1} {
        pg_result $result -clear
        error "Error: Query returned $n results"

    # Return the data, which is the only column in the only row:
    set data [lindex [pg_result $result -getTuple 0] 0]
    pg_result $result -clear
    return $data

if {$argc != 1} {
    puts stderr "Usage: view_picture name"
    exit 1
set name [lindex $argv 0]

# Connect to the database.
set conn [pg_connect -conninfo ""]

# Prepare the SQL statement:
prepare_statement $conn

# Get the picture data:
set failed [catch {get_picture $conn $name} data]

# Done with database connection:
pg_disconnect $conn

# Exit if unable to retrieve the data:
if {$failed} {
    puts "Failed to view picture '$name': $data"

# Make the viewer and show the picture:
wm title . "Picture: $name"
image create photo p -data $data -format GIF
label .top -image p
button .quit -text Close -command exit -default active
bind . <Return> exit
pack .top -side top
pack .quit

SourceForge.net Logo

This version of the manual was produced for the Pgtcl-ng Sourceforge project web service site, which requires the logo on each page.

To download a logo-free copy of the manual, see the Pgtcl-ng project downloads area.