Re: pg_select in a tcl script

Hi

The documentation for pg_select says that "the return result is either
an error message or a handle for a query result".

How can I catch this output? and How do I determine if it is an error or
a handle?

I have tried

set res [pg_select ...]
pg_result res -status
complains that res is not a result handle, it seems to be a null value.

Thanks in advance for and help or ideas

Steve

Steve Burger <steve(at)customware(dot)aust(dot)com> writes:
> I have tried
> set res [pg_select ...]
> pg_result res -status
> complains that res is not a result handle, it seems to be a null value.

Shouldn't that be
set res [pg_select ...]
pg_result $res -status

regards, tom lane

steve(at)customware(dot)aust(dot)com wrote:
> Hi
>
> The documentation for pg_select says that "the return result is either
> an error message or a handle for a query result".
>
> How can I catch this output? and How do I determine if it is an error or
> a handle?
>
> I have tried
>
> set res [pg_select ...]
> pg_result res -status
> complains that res is not a result handle, it seems to be a null value.

No result handle is returned. pg_select creates, uses, and then destroys
the result structure before it returns. Where in the documentation did
you see the above quote?

ljb wrote:

> steve(at)customware(dot)aust(dot)com wrote:
> > Hi
> >
> > The documentation for pg_select says that "the return result is either
> > an error message or a handle for a query result".
> >
> > How can I catch this output? and How do I determine if it is an error or
> > a handle?
> >
> > I have tried
> >
> > set res [pg_select ...]
> > pg_result res -status
> > complains that res is not a result handle, it seems to be a null value.
>
> No result handle is returned. pg_select creates, uses, and then destroys
> the result structure before it returns. Where in the documentation did
> you see the above quote?

The documentation of the Tcl binding is not very complete ... :-(
Perhaps I should (some day) write some stuff for it ... (I'm doing
a lot of stuff with it -- so maybe it's a good idea to write all that
from a pratical point of view).

To your question:

pg_select loops over a set of records. If something is wrong, the loop
is just not executed. So the typical use of pg_select could look like
this:

set query "SELECT * FROM something WHERE anyitem = $searchval;"
pg_select $conn $query tmp {
puts "$tmp(anyitem) $tmp(anotheritem) $tmp(whatsoever)"
}

The fields in the result are placed in the array 'tmp' with the column
names used as array index. Pay attention to columns with duplicate names
(if you are selecting from more than one set). Use 'AS some_other_name'
to overcome ambiguity of the items.

There seems to be no way to find out if there are just no matching rows
or if something is wrong with the connection or your code ...

As far as the SELECT statement is static you can ensure correctness by
intensive tests during development (just make sure that there are rows
matching your request). Any dynamically setup query is hard to check.

Hope this helps

Andreas

andi(at)kretzer-berlin(dot)de wrote:
>...
> There seems to be no way to find out if there are just no matching rows
> ...

It's easier with pg_execute, since it returns the number of tuples. This
new command showed up around PostgreSQL 7.1, and it's still there at 7.2,
but still undocumented. It looks rather useful. Another thing it can do,
that you can't otherwise do with libpgtcl, is find out how many rows were
affected by insert, update, or delete. I can see pg_execute replacing
pg_select because it is more flexible, too.

Since it isn't listed in HISTORY or the documentation, I wonder if it is
considered "experimental", or might it be staying around, just waiting for
somebody to document it? I would like to know, because there is a bug in
the "-oid" code; I have a tiny patch for it if it is going to be supported.

ljb <lbayuk(at)mindspring(dot)com> writes:
> It's easier with pg_execute, since it returns the number of tuples. This
> new command showed up around PostgreSQL 7.1, and it's still there at 7.2,
> but still undocumented. It looks rather useful. ...

> Since it isn't listed in HISTORY or the documentation, I wonder if it is
> considered "experimental", or might it be staying around, just waiting for
> somebody to document it? I would like to know, because there is a bug in
> the "-oid" code; I have a tiny patch for it if it is going to be supported.

Looking at the CVS logs, I see that Jan added this command; he should be
blamed for not having added any documentation. Feel free to contribute
a documentation patch ...

What's wrong with the -oid code?

regards, tom lane

tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:

> Looking at the CVS logs, I see that Jan added this command; he should be
> blamed for not having added any documentation. Feel free to contribute
> a documentation patch ...

I have documentation, but just as plain text. Unfortunately I don't have
the tools or know-how to get it (correctly) into SGML at this time.
What should I do?

> What's wrong with the -oid code?

pg_execute throws an error when you use -oid (with no error message), because
the return value check on Tcl_SetVar is wrong. Here's the fix:

*** src/interfaces/libpgtcl/pgtclCmds.c.orig Mon Dec 3 09:49:46 2001
--- src/interfaces/libpgtcl/pgtclCmds.c Mon Feb 18 17:25:27 2002
***************
*** 909,915 ****

sprintf(oid_buf, "%u", PQoidValue(result));
if (Tcl_SetVar(interp, oid_varname, oid_buf,
! TCL_LEAVE_ERR_MSG) != TCL_OK)
{
PQclear(result);
return TCL_ERROR;
--- 909,915 ----

sprintf(oid_buf, "%u", PQoidValue(result));
if (Tcl_SetVar(interp, oid_varname, oid_buf,
! TCL_LEAVE_ERR_MSG) == NULL)
{
PQclear(result);
return TCL_ERROR;

ljb <lbayuk(at)mindspring(dot)com> writes:
> tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
>> Looking at the CVS logs, I see that Jan added this command; he should be
>> blamed for not having added any documentation. Feel free to contribute
>> a documentation patch ...

> I have documentation, but just as plain text. Unfortunately I don't have
> the tools or know-how to get it (correctly) into SGML at this time.
> What should I do?

Send the plain text to pgsql-docs, someone will do something with it.

>> What's wrong with the -oid code?

> pg_execute throws an error when you use -oid (with no error message), because
> the return value check on Tcl_SetVar is wrong.

Good catch. Patch applied for 7.3.

regards, tom lane

This was applied March 4. I was not sure you had been informed so I am
sending this email.

---------------------------------------------------------------------------

ljb wrote:
> tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:
>
> > Looking at the CVS logs, I see that Jan added this command; he should be
> > blamed for not having added any documentation. Feel free to contribute
> > a documentation patch ...
>
> I have documentation, but just as plain text. Unfortunately I don't have
> the tools or know-how to get it (correctly) into SGML at this time.
> What should I do?
>
> > What's wrong with the -oid code?
>
> pg_execute throws an error when you use -oid (with no error message), because
> the return value check on Tcl_SetVar is wrong. Here's the fix:
>
> *** src/interfaces/libpgtcl/pgtclCmds.c.orig Mon Dec 3 09:49:46 2001
> --- src/interfaces/libpgtcl/pgtclCmds.c Mon Feb 18 17:25:27 2002
> ***************
> *** 909,915 ****
>
> sprintf(oid_buf, "%u", PQoidValue(result));
> if (Tcl_SetVar(interp, oid_varname, oid_buf,
> ! TCL_LEAVE_ERR_MSG) != TCL_OK)
> {
> PQclear(result);
> return TCL_ERROR;
> --- 909,915 ----
>
> sprintf(oid_buf, "%u", PQoidValue(result));
> if (Tcl_SetVar(interp, oid_varname, oid_buf,
> ! TCL_LEAVE_ERR_MSG) == NULL)
> {
> PQclear(result);
> return TCL_ERROR;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Doc addition for this applied.

---------------------------------------------------------------------------

Tom Lane wrote:
> ljb <lbayuk(at)mindspring(dot)com> writes:
> > It's easier with pg_execute, since it returns the number of tuples. This
> > new command showed up around PostgreSQL 7.1, and it's still there at 7.2,
> > but still undocumented. It looks rather useful. ...
>
> > Since it isn't listed in HISTORY or the documentation, I wonder if it is
> > considered "experimental", or might it be staying around, just waiting for
> > somebody to document it? I would like to know, because there is a bug in
> > the "-oid" code; I have a tiny patch for it if it is going to be supported.
>
> Looking at the CVS logs, I see that Jan added this command; he should be
> blamed for not having added any documentation. Feel free to contribute
> a documentation patch ...
>
> What's wrong with the -oid code?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026