[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: ... '''1. Setup PostgreSQL''' Either just install your OS specific package if it is available for example the postgreSQL rpm, or start reading the fine postgreSQL install instructions at: http://www.postgresql.org/docs/7.4/interactive/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. '''2. Make sure you have a working Pgtcl package''' 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 [libpgtcl]. '''3. Start using Pgtcl''' Take a look at: http://www.postgresql.org/docs/7.4/interactive/pgtcl.html 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 $db Inserting 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 doit Recent 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 doit '''4. Pgtcl Commands''' '''pg_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 ''.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 this see [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 ---- [Category Database] | [Category Tutorial]