Postgres
in [TCL]
|
From: jr4412 on 11 Jan 2010 10:22 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.
From: Avivi on 11 Jan 2010 11:04 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
From: jr4412 on 11 Jan 2010 11:38 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.
From: jr4412 on 11 Jan 2010 11:46 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.
From: jr4412 on 11 Jan 2010 23:35 hi ljb, kind words, thank you very much. since you're the author of 'pgtcl-ng' I take the opportunity to ask a couple of questions; I've installed pgtcl 1.6.2 from source (for use with Postgresql 8.3.0), what are the differences between pgtcl and pgtcl-ng? and which additional features would I gain if I installed pgtcl-ng 1.7.1?
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: TclOO: "static" method? Next: Looking for a textmode ui ... |