5.9.Example - Bytea Escaping - Picture Viewer, Part 1 - Store Pictures
Prev | Chapter5.Example Programs | Next

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

この用例は､Section5.7, “Example - Prepared Queries - Picture Viewer, Part 1 - Store Pictures” で示したGIF絵画鑑賞の変種である｡ これも映像を可変長のバイナリ列として保存するが､ 準備された質問を二進様式で使うより､通常文書様式を使う｡ 通常のSQL質問そのものを通して二進データを得る為に､ pg_escape_bytea と pg_unescape_bytea が､使われる｡ この版は､二進の準備された質問の使用より､効果が劣り､本質的に遅いかもしれない｡ This example is a variation of the GIF picture viewer shown in Section5.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.

### 註意 Note

pg_escape_bytea と pg_unescape_bytea は､pgtcl-ng 第1.5.2版と､pgin.tcl 第 2.2.0 版に追加された 拡張である｡この用例は､他の版や実装では動かない｡ `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.

絵画を見るために使われる算譜は､Section5.10, “Example - Bytea Escaping - Picture Viewer, Part 2 - View Pictures” を参照されたい｡ (同じ同じ形式が使われているので､記述譜のSection5.8, “Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures” も使用できる｡) See Section5.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 Section5.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.

Example5.16.Bytea Escaping - Store Pictures in Database

    #!/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

Prev | Up | Next
5.8.Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures | Home | 5.10.Example - Bytea Escaping - Picture Viewer, Part 2 - View Pictures