#!/usr/bin/tclsh
# 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