5.9. Example - Bytea Escaping - Picture Viewer, Part 1 - Store Pictures

This example is a variation of the GIF picture viewer shown in Section 5.7, “Example - Prepared Queries - Picture Viewer, Part 1 - Store 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 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.10, “Example - Bytea Escaping - Picture Viewer, Part 2 - View Pictures” for the program used to view the pictures. (Since the same schema is used, the script in Section 5.8, “Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures” can also be used.)

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.

Example 5.16. Bytea Escaping - Store Pictures in Database

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

package require Pgtcl

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

# 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:
    set result [pg_exec $conn "INSERT INTO pics (pname, picture) VALUES \
        ('[pg_escape_string $name]',\
        '[pg_escape_bytea $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

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