5.10. Example - Bytea Escaping - Picture Viewer, Part 2 - View Pictures

This example is a variation of the GIF picture viewer shown in Section 5.8, “Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures”. It also stores the images as bytea fields in the database, but rather than using prepared queries in binary mode, it uses normal text mode queries. In order to get the binary data through normal SQL queries intact, the commands pg_escape_bytea and pg_unescape_bytea are used. This version is generally less efficient that using binary prepared queries, and can be significantly slower.


pg_escape_bytea and pg_unescape_bytea are extensions added to pgtcl-ng version 1.5.2 and pgin.tcl version 2.2.0. This example will not work with other versions or implementations.

Given an identifier on the command line, it attempts to retrieve and display the stored picture with that identifier.

See Section 5.9, “Example - Bytea Escaping - Picture Viewer, Part 1 - Store Pictures” for the program used to insert the pictures in the database. (Since the same schema is used, the script in Section 5.7, “Example - Prepared Queries - Picture Viewer, Part 1 - Store Pictures” can also be used.)

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

Example 5.17. Bytea Escaping - View Pictures Stored in Database

# Example - picture storage as bytea, using escape/unescape:

package require Pgtcl

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

    # Note that for a text-mode query on a BYTEA column, PostgreSQL will escape
    # the data for us.
    set result [pg_exec $conn "SELECT picture FROM pics\
            WHERE pname='[pg_escape_string $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"
    # Unescape and return the data, which is the only column in the only row:
    set data [pg_unescape_bytea [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 ""]

# 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.