5.7.Example - Prepared Queries - Picture Viewer, Part 1 - Store Pictures
Prev | Chapter5.Example Programs | Next

## 5.7.Example - Prepared Queries - Picture Viewer, Part 1 - Store Pictures

この用例は､ Section5.5, “Example - Large Objects - Picture Viewer, Part 1 - Store Pictures” で示したものとは異なる版の､ GIF 絵画観察者である､但し､これは映像をデータベース内の､可変長のバイナリ列の領域として保存する｡(可変長のバイナリ列bytea データ型はPostgreSQL拡張で､データベースによる翻訳なしの､無制限寸法のバイト列として保存される｡) この用例は､ データを除外させたり引用させたりする事なく､ 二進データをデータベースに挿入し､且つ､データベースから二進デーを取出す為に､ pg_exec_prepared によって 準備された質問の使用を展示する｡ This example is another version of the GIF picture viewer shown in Section5.5, “Example - Large Objects - Picture Viewer, Part 1 - Store Pictures”, but this one stores the images as `bytea` fields in the database. (The `bytea` datatype is a PostgreSQL extension which stores an unlimited size byte stream with no interpretation by the database.) This example shows the use of prepared queries with pg_exec_prepared to insert binary data into the database, and to retrieve binary data from the database, without having to escape or quote the data.

識別子(鍵として使われる任意の名前)とファイル名の対が命令行上に与えられ､ 名前と映像データ伴って表内に記録を挿入する｡ 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.8, “Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures” を参照されたい｡又､用意された質問なしの実装は Section5.9, “Example - Bytea Escaping - Picture Viewer, Part 1 - Store Pictures” を参照されたい｡ See Section5.8, “Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures” for the program used to view the pictures. Also see Section5.9, “Example - Bytea Escaping - Picture Viewer, Part 1 - Store Pictures” for an implementation without prepared queries.

もし表が見付からないと､概要が自動的に生成される｡この方法は､製品には通常使われない｡ 用例は､データベース接続情報は環境を通じて提供されることを仮定している｡ 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.

準備された質問pics_insertはデータ挿入の為に使われる｡(率直に言うと､それは質問より命令だ) それは二つの假引数を取る: 絵画の名前の文書型､と､映像そのものの為のバイト型､そして結果の値を返さない｡ pg_exec_prepared が準備された質問を実行する為に使われると､ 一個のTEXT書式假引数､名前､及び､一個のBINARY書式假引数､映像データ､が準備された質問に 渡される｡ The prepared query, `pics_insert`, is used to insert the data. (So strictly speaking it isn't a query, but a command.) It takes two parameters: a text type for the picture name, and a bytea type for the image itself, and returns no result values. When `pg_exec_prepared` is used to execute the prepared query, one TEXT format parameter, the name, and one BINARY format parameter, the image data, are passed to the prepared query.

Example5.14.Prepared Queries - Store Pictures in Database

    #!/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)"
    }

    # 準備された SQL 記述:
    #  pics_insert(name, data) は絵画 'data' を名前 'name' に挿入
    # 註意 問合せ假引数は引用や除外の必要がない｡
    # 暗黙の不都合処理: 不都合ならtcl不都合発生｡.
    # 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

Prev | Up | Next
5.6.Example - Large Objects - Picture Viewer, Part 2 - View Pictures | Home | 5.8.Example - Prepared Queries - Picture Viewer, Part 2 - View Pictures