PostgreSQL and Tcl combine quite pleasantly. It can be a delight using them for substantial development projects. Much the more demanding, at least in setup, is the former. Here's what it takes to start: ...
It may be worth having a look at TDBC[L1 ], which is a database neutral way of connecting a database with Tcl.
Either just install your OS specific package. For Linux it is available in most distributions. For almost all platforms, there are prepackaged versions. If all else fails start reading the fine PostgreSQL install instructions at:
https://www.postgresql.org/docs/current/static/installation.html
Make sure you have a running server, get its connection info like hostname, port, user and password for the user you want to use for the next steps.
Try:
package require Pgtcl
If it works, you have a probably working Pgtcl package installed, otherwise you need to install/compile one from either the PostgreSQL distribution you have installed (Pgtcl should be included there) or obtain and build Pgtcl.
Googie 09/09/2011 - There's also a pgintcl, a pure-Tcl package with very similar (yet slightly different) API.
Documentation
The basics:
# select and do something with the results...
# open connection
# password etc. must be provided according to the security settings on the postgres db
# you may be able to just do "pg_connect www" or even "pg_connect" if database is your username
package require Pgtcl
set db [pg_connect -conninfo [list host = localhost user = test dbname = testdb]]
pg_select $db "select * from testtable" user {
parray user
}
pg_disconnect $dbInserting into a database...
Note how pg_exec returns a result handle that has many things that can be done with it to access the results, which is done using pg_result. You can get a list interactively by doing a pg_result with no arguments. You gotta clean up the result handle this way. Note that pg_select, above, takes care of all that for you.
package require Pgtcl
proc doit {} {
set fp [open sampledata.txt]
set conn [pg_connect -conninfo ""]
while {[gets $fp line] >= 0} {
lassign $line name address city state zip
set statement "insert into peopletable values ( \
[pg_quote $name], [pg_quote $address], [pg_quote $city] \
[pg_quote $state], [pg_quote $zip]);"
set result [pg_exec $conn $statement]
if {[pg_result $result -status] != "PGRES_COMMAND_OK"} {
puts "[pg_result $result -error] executing '$statement'"
}
pg_result $result -clear
}
}
if !$tcl_interactive doitRecent version of Pgtcl running with fairly recent version of PostgreSQL can do variable substitutions, which are pretty cool, and require less quoting and stuff. Observe...
set statement {insert into peopletable values ($1, $2, $3, $4, $5);}
set result [pg_exec $conn $statement $name $address $city $state $zip]You can also prepare statements, which is a way to improve performance of a statement that is going to be frequently executed, by getting PostgreSQL to sort of preparse it and figure it out once rather than every time it is presented.
package require Pgtcl
proc doit {} {
set fp [open sampledata.txt]
set conn [pg_connect -conninfo ""]
set result [pg_exec $conn {prepare insert_peopletable \
(varchar, varchar, varchar, varchar, varchar, varchar) as \
insert into peopletable values ($1, $2, $3, $4, $5);}]
if {[pg_result $result -status] != "PGRES_COMMAND_OK"} {
puts "[pg_result $result -error] preparing statement"
exit 1
}
while {[gets $fp line] >= 0} {
lassign $line name address city state zip
set result [pg_exec_prepared $conn pgtest_insert_people \
$name $address $city $state $zip]
if {[pg_result $result -status] != "PGRES_COMMAND_OK"} {
puts "[pg_result $result -error] inserting '$line'"
}
pg_result $result -clear
}
}
if !$tcl_interactive doitpg_quote $string
This escapes a string by making it Postgres-safe. It quotes single quotes and backslashes. If you're doing something like
pg_exec "insert into foo(name='$name');"
...and name contains a single quote, it'll fail. Worse, it makes you vulnerable to SQL injection attacks.
Please run value strings through pg_quote to make sure they can be used as values and stuff in Postgres.
pg_exec "insert into foo(name=[pg_quote $name]);"
Any special characters that occur in name, such as single quote or backslash, will be properly quoted.
set conn [pg_connect -conninfo conninfoString]
The modern way to connect, all necessary information required for the connection (host, port, database, user, password, etc.) is specified in the connect info string. You usually don't have to pass everything, often only the database name.
pg_disconnect $conn
Close a backend connection.
set res [pg_exec $conn query var...]
Send a query string to the backend connection.
The return result is either an error or a handle for a query result. Handles start with the prefix "pgp". On recent versions of Pgtcl and PostgreSQL, vars can be specified and substituted for dollar-number variables in the query. See the example earlier in the page.
pg_select
Send a select query string to the backend connection.
pg_select connection query arrayName code
The query must be a select statement. The array named arrayName will be created as local variable, and be visible only inside the code. It will contain four "special" elements whose name starts with a ".", plus one element for each column. E.g.:
result(.command) = update result(.headers) = error_class comment result(.numcols) = 2 result(.tupno) = 58 result(comment) = result(error_class) = PLANETCHECK
The problem with this kind of statement is that if you have several columns with the same name, only one of them is correctly returned. This can happen easily when you join several tables sharing a column name which is not used as join criterion. All columns are retained, but the values of all but one of them are lost.
.numcols should be the list length of .headers, RS assumes.
The code is run once for each row found. See the example earlier in this page.
You can continue and return in the body and it'll do the expected thing. (What I think is expected, anyway.)
pg_result
Get information about the results of a query.
pg_result $res ?option?
The options are:
-status the status of the result
-error the error message, if the status indicates error;
otherwise an empty string
-conn the connection that produced the result
-oid if command was an INSERT, the OID of the inserted tuple
-numTuples the number of tuples in the query
-numAttrs returns the number of attributes returned by the query
-assign arrayName
assign the results to an array, using
subscripts of the form (tupno,attributeName)
-assignbyidx arrayName ?appendstr?
assign the results to an array using the first
field's value as a key.
All but the first field of each tuple are stored,
using subscripts of the form
(field0value,attributeNameappendstr)
-list
returns the results as a list
-llist
returns the results as a list of lists, where each list element is the row data
-getTuple tupleNumber
returns the values of the tuple in a list
-tupleArray tupleNumber arrayName
stores the values of the tuple in array arrayName,
indexed by the attributes returned
-attributes returns a list of the name/type pairs of the tuple
attributes
-lAttributes returns a list of the {name type len} entries of the
tuple attributes
-clear clear the result buffer. Do not reuse after thissee getting data from pgtcl for examples on the above options
pg_execute
Send a query string to the backend connection and process the result.
pg_execute ?-array name? ?-oid varname? connection query ?loop_body?
The return result is the number of tuples processed. If the query returns tuples (i.e. a SELECT statement), the result is placed into variables.
pg_conninfo
Apparently returns a list of all the database connections opened by pg_connect. BAS Yes, it does that as well as return the result handles per connection.
pg_conninfo connections - returns current opened connection handles
pg_conninfo results $con - returns the current opened result handles for $con connection