Thread: Counting updates with libpq

Counting updates with libpq

From

Bartek Teodorczyk

Date:

05 March 1999, 13:59:20

Interface: libpq

Is it possible to get number of updated records using the PQexec command
and processing the PQresult record?

BarTeo

Re: [INTERFACES] Counting updates with libpq

From

Tom Lane

Date:

05 March 1999, 20:38:25

Bartek Teodorczyk <barteo@it.pl> writes:
> Is it possible to get number of updated records using the PQexec command
> and processing the PQresult record?

See PQcmdTuples() ...

            regards, tom lane

web interface

From

"Max Calvo"

Date:

06 March 1999, 05:00:46

Hi all;

I am trying to put a web interface using Perl. Does anyone have some code to
share. I am trying to pass a variable name to the command line but I am
getting a compiling error.

any help will be greatly appreciated.

Thanx

Max

web interface

From

"Max Calvo"

Date:

06 March 1999, 05:13:22

Hi all;

I am trying to put a web interface using Perl. Does anyone have some code to
share. I am trying to pass a variable name to the command line but I am
getting a compiling error.

any help will be greatly appreciated.

Thanx

Max

Re: [INTERFACES] Counting updates with libpq

From

Michael Meskes

Date:

07 March 1999, 05:59:24

On Fri, Mar 05, 1999 at 05:36:46PM -0500, Tom Lane wrote:
> Bartek Teodorczyk <barteo@it.pl> writes:
> > Is it possible to get number of updated records using the PQexec command
> > and processing the PQresult record?
>
> See PQcmdTuples() ...

For ecpg I've beed using PQntuples(). Is it the same function?

Michael
--
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net          | Use PostgreSQL!

Re: [INTERFACES] Counting updates with libpq

From

Tom Lane

Date:

07 March 1999, 14:21:11

Michael Meskes <meskes@postgresql.org> writes:
>>>> Is it possible to get number of updated records using the PQexec command
>>>> and processing the PQresult record?
>>
>> See PQcmdTuples() ...

> For ecpg I've beed using PQntuples(). Is it the same function?

No.  PQntuples() is the number of rows in the returned result.  It will
always be zero if the SQL command was not SELECT (or FETCH).

PQcmdTuples() is just a dumb little function that looks at the command
status string (the same thing psql prints at the end of a query) and
pulls out the number that appears in "UPDATE n" or "DELETE n" or
"INSERT oid n".  Since the questioner asked about updated records,
I assumed that was the number he wanted.

            regards, tom lane

row count

From

Sergio

Date:

07 March 1999, 22:35:56

Byron,

How I can know the number of rows in the set after a _select_
(ie SQLRowCount isn't valid here) statement using ODBC ?

TIA,
Sergio

Re: [INTERFACES] Counting updates with libpq

From

Michael Meskes

Date:

08 March 1999, 05:45:51

On Sun, Mar 07, 1999 at 11:20:39AM -0500, Tom Lane wrote:
> > For ecpg I've beed using PQntuples(). Is it the same function?
>
> No.  PQntuples() is the number of rows in the returned result.  It will
> always be zero if the SQL command was not SELECT (or FETCH).

I'm afraid this is incorrect. I tried it on an insert (via exec sql execute
immediate) and it got the correct result.

> PQcmdTuples() is just a dumb little function that looks at the command
> status string (the same thing psql prints at the end of a query) and
> pulls out the number that appears in "UPDATE n" or "DELETE n" or
> "INSERT oid n".  Since the questioner asked about updated records,
> I assumed that was the number he wanted.

I need this number as well.

Michael
--
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net          | Use PostgreSQL!

Re: [INTERFACES] row count

From

Byron Nikolaidis

Date:

08 March 1999, 12:57:18

Sergio wrote:

> Byron,
>
> How I can know the number of rows in the set after a _select_
> (ie SQLRowCount isn't valid here) statement using ODBC ?
>
>

Actually, it is valid, and will return the number of rows in the result
set, but only if you are *NOT* using the "Declare/Fetch" option of the
driver.

Byron

Re: [INTERFACES] Counting updates with libpq

From

Tom Lane

Date:

08 March 1999, 13:48:14

Michael Meskes <meskes@postgreSQL.org> writes:
>> No.  PQntuples() is the number of rows in the returned result.  It will
>> always be zero if the SQL command was not SELECT (or FETCH).

> I'm afraid this is incorrect. I tried it on an insert (via exec sql execute
> immediate) and it got the correct result.

No, I'm afraid *you* are incorrect: a test program like

    res = PQexec(conn, "INSERT INTO test SELECT * FROM test");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "INSERT command failed\n");
        PQclear(res);
        exit_nicely(conn);
    }

    printf("INSERT result PQntuples = %d\n", PQntuples(res));
    printf("INSERT result PQcmdTuples = %s\n", PQcmdTuples(res));

produces the expected results (ntuples = 0, cmdTuples = number of
inserted rows) for either variant of INSERT.

It seems likely that ecpg is interposing some additional behavior
not seen in bare libpq, although I'm at a loss to guess what.
Surely ecpg wouldn't be trying to implicitly fetch the inserted rows?

            regards, tom lane

Re: [INTERFACES] row count

From

Sergio

Date:

08 March 1999, 19:42:10

Byron Nikolaidis <byronn@insightdist.com> el día Mon, 08 Mar 1999 09:27:34
-0500, escribió:

>
>
>Sergio wrote:
>
>> Byron,
>>
>> How I can know the number of rows in the set after a _select_
>> (ie SQLRowCount isn't valid here) statement using ODBC ?
>>
>>
>
>Actually, it is valid, and will return the number of rows in the result
>set, but only if you are *NOT* using the "Declare/Fetch" option of the
>driver.

Very good information, thanks.

Now suppose that I don't know if the user is using declare/fetch,
I have another option ?

Sergio

Re: [INTERFACES] row count

From

Byron Nikolaidis

Date:

08 March 1999, 20:33:15


Sergio wrote:

> Byron Nikolaidis <byronn@insightdist.com> el día Mon, 08 Mar 1999 09:27:34
> -0500, escribió:
>
> >
> >
> >Sergio wrote:
> >
> >> Byron,
> >>
> >> How I can know the number of rows in the set after a _select_
> >> (ie SQLRowCount isn't valid here) statement using ODBC ?
> >>
> >>
> >
> >Actually, it is valid, and will return the number of rows in the result
> >set, but only if you are *NOT* using the "Declare/Fetch" option of the
> >driver.
>
> Very good information, thanks.
>
> Now suppose that I don't know if the user is using declare/fetch,
> I have another option ?
>
>

SQLRowCount returns -1, which is what the odbc spec says to return if the
driver doesnt know the count.

Byron

Re: [INTERFACES] Counting updates with libpq

From

Michael Meskes

Date:

09 March 1999, 04:07:01

On Mon, Mar 08, 1999 at 10:47:38AM -0500, Tom Lane wrote:
> No, I'm afraid *you* are incorrect: a test program like
>
>     res = PQexec(conn, "INSERT INTO test SELECT * FROM test");
>     if (PQresultStatus(res) != PGRES_COMMAND_OK)
>     {
>         fprintf(stderr, "INSERT command failed\n");
>         PQclear(res);
>         exit_nicely(conn);
>     }
>
>     printf("INSERT result PQntuples = %d\n", PQntuples(res));
>     printf("INSERT result PQcmdTuples = %s\n", PQcmdTuples(res));
>
> produces the expected results (ntuples = 0, cmdTuples = number of
> inserted rows) for either variant of INSERT.

Strange. Have to check my code again. ... Argh! I'm stupid. I just checked
via pattern matching and found only the position where the return value was
PGRES_TUPLES_OK. In this case I use PQntuples(). If the result was
PGRES_COMMAND_OK I do use PQcmdTuples. So I guess that explains it. :-)

Michael
--
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net          | Use PostgreSQL!