pgintcl and PREPARE

pgintcl 是純粹使用 Tcl 所撰寫的 PostgreSQL client。

不過沒有實作 PQprepare 相關的部份，那麼如果要使用 prepared statement，要怎麼使用？答案是使用 PostgreSQL 的 PREPARE statement。

下面是一段簡單的 test code 的內容：

package require pgintcl

# connect to database
set db [pg_connect -conninfo "dbname=danilo user=danilo password=danilo"]

pg_execute $db "CREATE TABLE IF NOT EXISTS contact (id int, name varchar(200), primary key(id));"
pg_execute $db "PREPARE test1 AS INSERT INTO contact (id, name) VALUES (\$1, \$2);"
set res [pg_describe_prepared $db test1]
# Although Get the param type, it is the Object IDs for the Postgres data types
pg_result $res -paramTypes
pg_result $res -clear

# Need setup text or binary format, here is setup to all TEXT format
set res [pg_exec_prepared $db test1 TEXT  TEXT 1 "Raynor"]
pg_result $res -status
pg_result $res -clear

# Need setup text or binary format, here is setup to all TEXT format
set res [pg_exec_prepared $db test1 TEXT  TEXT 2 "Tassadra"]
pg_result $res -status
pg_result $res -clear

# Need setup text or binary format, here is setup to all TEXT format
set res [pg_exec_prepared $db test1 TEXT  TEXT 3 "Kavin"]
pg_result $res -status
pg_result $res -clear

pg_execute $db "PREPARE test2 AS select * from contact;"
set res [pg_describe_prepared $db test2]
# Get column number and attributes
pg_result $res -numAttrs 
pg_result $res -lAttributes
# Get param types
pg_result $res -paramTypes
pg_result $res -clear

set res [pg_exec_prepared $db test2 TEXT  TEXT]
# Get the result numbers
pg_result $res -numTuples
# Returns the query results as a Tcl dictionary
pg_result $res -dict

pg_execute $db "DROP TABLE IF EXISTS contact;"

pg_disconnect $db

這樣就可以在 pgintcl 中使用 prepared statement 了。另外 PostgreSQL 不是使用 ?，而是使用 $1, $2 來代表。