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