5.8.Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures
Prev | Chapter5.Example Programs | Next

## 5.8.Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures

この用例は､GIF絵画鑑賞の第二部であり､ 映像を､データベース領域内に可変長のバイナリ列として保存し､ 二進データからasciiへの､又は逆向きのascii符号化をさせることなく､ 映像を取出す為の準備された質問を使う｡ 命令行上に識別子が与えられ､その識別子を使って､ 保存されたデータを取出し表示する事を試みる｡ This example is the second part of a GIF picture viewer which stores the images as `bytea` fields in the database, and uses prepared queries to retrieve the images without having to convert from or to an ASCII encoding of binary data. Given an identifier on the command line, it attempts to retrieve and display the stored picture with that identifier.

映像をデータベースに挿入する為に使われた算譜は Section5.7, “Example - Prepared Queries - Picture Viewer, Part 1 - Store Pictures” を参照されたい｡ See Section5.7, “Example - Prepared Queries - Picture Viewer, Part 1 - Store Pictures” for the program used to insert the pictures in the database.

主鍵として絵画の名前を使い､表からデータを選択する為に､ 準備された質問､pics_select,が使われる｡ それは一個の假引数をとる: 文書型の絵画の名前､そして､映像そのものの一個の､ 可変長のバイナリ列､を返す｡ 準備された質問を実行する為に､pg_exec_prepared が使われる時､ 一個のTEXT 書式假引数の､名前､が､準備された質問に渡され､結果は､ 一個のBINARY書式の値､映像データ､である｡ The prepared query, `pics_select`, is used to select the data from the table, using the picture name as the primary key. It takes one parameter: a text type for the picture name, and returns one bytea type which is the image itself. When `pg_exec_prepared` is used to execute the prepared query, one TEXT format parameter, the name, is passed to the prepared query, and the result is one BINARY format value, the image data.

この用例はwishの下で動き､tclshでは動かない｡ データベース接続の情報は環境を通して適用されることを仮定している｡ This example runs under wish, not tclsh. It also assumes database connection information is provided through the environment.

Example5.15.Prepared Queries - View Pictures Stored in Database

    #!/usr/bin/wish
    # Example - picture storage as bytea - viewer

    package require Pgtcl

    # 準備された SQL 記述｡:
    #  pics_select(name) - name についての画像データを返す｡
    # 註意 質問の假引数は引用化や除外の必要はない｡.
    # 暗黙の不都合操作: 不都合時はtcl不都合を発す｡
    # Create the prepared SQL statement:
    #  pics_select(name) - Return the picture data for the 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_select (TEXT) AS\
            SELECT picture FROM pics WHERE pname=\$1"
    }

    # 'name' で識別される絵画データを返す｡
    # 読めない時は不都合を発す｡
    # Return the picture data identified by 'name'.
    # Throw an error if it can't be read.
    proc get_picture {conn name} {

        set result [pg_exec_prepared $conn pics_select BINARY TEXT $name]
        if {[pg_result $result -status] != "PGRES_TUPLES_OK"} {
            set message [pg_result $result -error]
            pg_result $result -clear
            error "Error: Query failed: $message"
        }

        if {[set n [pg_result $result -numTuples]] != 1} {
            pg_result $result -clear
            error "Error: Query returned $n results"
        }

        # 行内の列のみのデータを返す｡
        # Return the data, which is the only column in the only row:
        set data [lindex [pg_result $result -getTuple 0] 0]
        pg_result $result -clear
        return $data
    }

    if {$argc != 1} {
        puts stderr "Usage: view_picture name"
        exit 1
    }
    set name [lindex $argv 0]

    # データベースへの接続.
    # Connect to the database.
    set conn [pg_connect -conninfo ""]

    # SQL 記述の準備:
    # Prepare the SQL statement:
    prepare_statement $conn

    # 絵画データ取得:
    # Get the picture data:
    set failed [catch {get_picture $conn $name} data]

    # データベース接続完了:
    # Done with database connection:
    pg_disconnect $conn

    # データ取出せない場合は出る:
    # Exit if unable to retrieve the data:
    if {$failed} {
        puts "Failed to view picture '$name': $data"
        exit
    }

    # 観察を行い､絵画を表示:
    # Make the viewer and show the picture:
    wm title . "Picture: $name"
    image create photo p -data $data -format GIF
    label .top -image p
    button .quit -text Close -command exit -default active
    bind . <Return> exit
    pack .top -side top
    pack .quit

Prev | Up | Next
5.7.Example - Prepared Queries - Picture Viewer, Part 1 - Store Pictures | Home | 5.9.Example - Bytea Escaping - Picture Viewer, Part 1 - Store Pictures