Postgres
in [TCL]
|
From: jr4412 on 13 Jan 2010 20:49 On Jan 13, 3:45 am, ljb <ljb1...(a)pobox.com.delete.this> wrote: > "pgtcl", by which I mean:http://pgfoundry.org/projects/pgtcl/ > went from version 1.5 to 1.7. If you have something that calls itself > 1.6.2, you probably have pgtcl-ng fromhttp://pgfoundry.org/projects/pgtclng/ > > So assuming you aren't asking me to compare pgtcl and pgtcl-ng, a topic > best avoided, I can summarize the changes in pgtcl-ng for you (from the > release NEWS file): > > The next release after 1.6.2 was 1.7.0 (2009-09-11). I added these commands: > + pg_encrypt_password to encrypt a password for certain SQL commands > + pg_lo_truncate to truncate a large object > + pg_describe_cursor to return information about a cursor (portal) > + pg_describe_prepared to return information about a prepared statement. > I added 2 options to pg_result, for use with pg_describe_prepared to return > information about a prepared statement: > + pg_result -numParams returns the number of parameters > + pg_result -paramTypes returns the parameter type OIDs. > In this release, pg_escape_string, pg_quote, and pg_escape_bytea > accept an optional connection parameter. This allows use of > connection-specific information to properly handle string escaping. > > The next release was 1.7.1 (2009-11-25). This has a Tcl-stubs-enabled > release for Windows. (Linux builds were always stubs-enabled.) So it can be > loaded into different Tcl versions and also has something to do with > "wrapping" an application. thanks, appreciated.
From: Avivi on 28 Jan 2010 16:01
On Jan 11, 11:46 am, jr4412 <jr4...(a)googlemail.com> wrote: > On Jan 11, 4:38 pm, jr4412 <jr4...(a)googlemail.com> wrote: > > > > > On Jan 11, 4:04 pm, Avivi <aviv...(a)gmail.com> wrote: > > > > On Jan 11, 10:22 am, jr4412 <jr4...(a)googlemail.com> wrote: > > > > > On Jan 11, 3:05 pm, Avivi <aviv...(a)gmail.com> wrote: > > > > > > On Jan 9, 9:54 pm, ljb <ljb1...(a)pobox.com> wrote: > > > > > > > peta...(a)iit.demokritos.gr wrote: > > > > > > > ???????? 8/1/2010 23:27, O/H Avivi ????????????: > > > > > > >> Hi > > > > > > > >> Does Pgtcl support COPY FROM STDIN ? > > > > > > > >> Thanks > > > > > > >> Avi > > > > > > > > Perhaps unrelated, but there is a tcl package called > > > > > > > "pgintcl". This implements everything in Tcl, using a socket. > > > > > > > Perhaps if you fail to find a solution, this extension may come handy, > > > > > > > at least for some transactions... > > > > > > > With pgintcl (mine!) you can do the copy, but not with puts. It has a > > > > > > special set of calls you use. > > > > > > > Both libpq-based implementations of pgtcl ("pgtcl", and "pgtcl-ng" (also > > > > > > mine!)) support copy as follows. To copy in to a table, you execute a "COPY > > > > > > mytable FROM STDIN", check the result handle status is PGRES_COPY_IN, then > > > > > > you "puts" tab-separated fields using the connection handle as a Tcl stream. > > > > > > At the end, you put the end marker "\.", and the result status should be > > > > > > PGRES_COMMAND_OK. > > > > > > > For copy out, you send COPY mytable TO STDOUT, check status, then read > > > > > > from the connection handle until it gets EOF, and check status again. > > > > > > George, Thanks for the information, I will definitely keep that in > > > > > mind. > > > > > > JR, LJB: Thank you too. Here are my "issues": > > > > > > This is a snippet from the program (please disregard typos): > > > > > > set dbHandle [ pg_connect -conninfo [ dbname = xyz ] > > > > > set copyString "copy xyz ( date, value ) from stdin" > > > > > set resHandle [ pg_exec $dbHandle $copyString ] > > > > > set result [ pg_result $resHandle -status ] > > > > > puts "RESULT: <$resHandle> $result" > > > > > > # pg_result $resHandle -clear ;# clear #1 > > > > > > set date "2010-01-11" > > > > > for { set i 0 } { $i <= 5 } { incr i } { { > > > > > puts $dbHandle "$date $i" > > > > > } > > > > > > puts $dbHandle {.} > > > > > set result [ pg_result $resHandle -status ] > > > > > Print 9 "RESULT: <$resHandle> $result" > > > > > > The problems: > > > > > > 1. If I un-comment-out "clear #1", I am getting: > > > > > > RESULT: <pgsql5.0> PGRES_COPY_IN > > > > > error writing "pgsql5": file busy > > > > > while executing > > > > > "puts $dbHandle "$date $i"" > > > > > > 2. If I comment-out "clear #1", I am getting: > > > > > RESULT: <pgsql5.0> PGRES_COPY_IN > > > > > RESULT: <pgsql5.0> PGRES_COPY_IN > > > > > and nothing was actually written to the DB. > > > > > > #1 - the [ pg_result -clear ] interferes with [ puts ], why? > > > > > > #2 baffles me since I exapcted to see PGRES_COMMAND_OK, as you > > > > > suggested. > > > > > However, [ pg_result -status ] returns a new status *only* after a new > > > > > [ pg_exec ] which was not issued before/after "puts $dbHandle {.}" > > > > > Aren't we missing something here? > > > > > > Again, many thanks to all of you > > > > > Avi > > > > > hi Avi, > > > > > will look at code more closely if needed but at first glance, "puts > > > > $dbHandle {.}" is a problem, the syntax is "puts $dbHandle {\.}", the > > > > backslash is required. > > > > JR > > > > I tried that and got: > > > ERROR: COPY: Input record 1 has been rejected (Invalid integer format > > > '\N' for column 2) > > > error writing "pgsql5": I/O error > > > while executing > > > "puts $dbHandle {\.}" > > > > Before you spend (waste?) more time on this, let me add one more piece > > > of information. > > > The DB is actually Vertica, which is built "on top" of Postgres. For > > > example, I can access it using psql. Also, Pgtcl is capable of > > > writing to and querying it. > > > > Many Thanks > > > Avi > > > hi Avi, > > > given that you get "ERROR: COPY: Input record 1 has been rejected > > (Invalid integer format '\N' for column 2) " > > > I suspect that the TAB char doesn't survive, try to do the write a > > little more carefully, ie. instead of > > puts $dbHandle "$date $i", try > > puts $dbHandle [format "%s\n%d" $date $i] > > > if you have no problems o/wise, Vertica is unlikely to be the cause. > > crikey, sorry, should read [format "%s\t%d" $date $i] of course. JR I apologize for the delayed response. Nothing worked. It is very likely to be Vertica. THANKS, again, for your time and wise words. Avi |