5.7. Example - Prepared Queries - Picture Viewer, Part 1 - Store Pictures

This example is another version of the GIF picture viewer shown in Section 5.5, “Example - Large Objects - Picture Viewer, Part 1 - Store Pictures”, but this one stores the images as bytea fields in the database. (The bytea datatype is a PostgreSQL extension which stores an unlimited size byte stream with no interpretation by the database.) This example shows the use of prepared queries with pg_exec_prepared to insert binary data into the database, and to retrieve binary data from the database, without having to escape or quote the data.

Given pairs of identifiers (arbitrary names used as keys) and filenames on the command line, it inserts a record in a table with the name and the image data. The image data can be retrieved using the name as the key.

See Section 5.8, “Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures” for the program used to view the pictures. Also see Section 5.9, “Example - Bytea Escaping - Picture Viewer, Part 1 - Store Pictures” for an implementation without prepared queries.

The schema is created automatically if the table is not found. This method wouldn't normally be used in production, though. The example assumes database connection information is provided through the environment.

The prepared query, pics_insert, is used to insert the data. (So strictly speaking it isn't a query, but a command.) It takes two parameters: a text type for the picture name, and a bytea type for the image itself, and returns no result values. When pg_exec_prepared is used to execute the prepared query, one TEXT format parameter, the name, and one BINARY format parameter, the image data, are passed to the prepared query.

Example 5.14. Prepared Queries - Store Pictures in Database

# Example - picture storage as bytea - importer

package require Pgtcl

# Build the table schema:
proc build {conn} {
    pg_execute $conn "CREATE TABLE pics (pname TEXT PRIMARY KEY, picture BYTEA)"

# Create the prepared SQL statement:
#  pics_insert(name, data) - Insert the picture 'data' under name '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_insert (TEXT, BYTEA) AS\
       INSERT INTO pics (pname, picture) VALUES (\$1, \$2)"

# Insert file 'file' into the database with key 'name':
# If an error occurs, throws a Tcl error.
proc insert_file {conn name file } {
    # Read the whole image file into a variable:
    set f [open $file]
    fconfigure $f -translation binary
    set data [read $f]
    close $f

    # Insert the image into the database, using a prepared statement.
    set result [pg_exec_prepared $conn pics_insert {} {TEXT BINARY} $name $data]
    if {[pg_result $result -status] != "PGRES_COMMAND_OK"} {
        set message [pg_result $result -error]
        pg_result $result -clear
        error "Error inserting picture '$name': $message"
    pg_result $result -clear

if {$argc < 2 || $argc % 2 != 0} {
    puts stderr "Usage: insert_picture name filename \[name filename\]..."
    exit 1

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

# Check for table:
if {[catch {pg_execute $conn "SELECT COUNT(*) AS n FROM pics"} msg]} {
     puts "Note: unable to select from table. Let's try creating it."
     build $conn

# Prepare the SQL statement:
prepare_statement $conn

# Insert all the pictures named on the command line:
foreach {name filename} $argv {
    if {[catch {insert_file $conn $name $filename} message]} {
        puts "$filename NOT inserted: $message"
    } else {
        puts "$filename inserted OK as '$name'"

pg_disconnect $conn

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.