Thread: BUG #5268: PQgetvalue incorrectly returns 0

BUG #5268: PQgetvalue incorrectly returns 0

From
"Mike Landis"
Date:
The following bug has been logged online:

Bug reference:      5268
Logged by:          Mike Landis
Email address:      mlandis@pnmx.com
PostgreSQL version: 8.4.1
Operating system:   Vista
Description:        PQgetvalue incorrectly returns 0
Details:

When I execute the following SQL:

"SELECT COUNT(*) FROM information_schema.tables WHERE table_name='proxies'"

in the PGAdmmin 1.10.0, rev 7945-7946 query tool, I get "1" (the correct
answer).  When I run the exact same SQL in a C program, I get a result set
with one tuple and one field (so far so good), but when I run
PQgetvalue(resultSet,0,0) I get "0" (wrong answer).

Am I missing something?  You can't do squat in a client program without
PQgetvalue().  Is any sort of regression test being run against libpq
functions?

What can I do to get this resolved?

Re: BUG #5268: PQgetvalue incorrectly returns 0

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, Jan 07, 2010 at 04:11:03AM +0000, Mike Landis wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5268
> Logged by:          Mike Landis
> Email address:      mlandis@pnmx.com
> PostgreSQL version: 8.4.1
> Operating system:   Vista
> Description:        PQgetvalue incorrectly returns 0
> Details:
>
> When I execute the following SQL:
>
> "SELECT COUNT(*) FROM information_schema.tables WHERE table_name='proxies'"
>
> in the PGAdmmin 1.10.0, rev 7945-7946 query tool, I get "1" (the correct
> answer).  When I run the exact same SQL in a C program, I get a result set
> with one tuple and one field (so far so good), but when I run
> PQgetvalue(resultSet,0,0) I get "0" (wrong answer).
>
> Am I missing something?  You can't do squat in a client program without
> PQgetvalue().  Is any sort of regression test being run against libpq
> functions?

Hm. I don't know for sure, but I'd assume that PGAdmin relies on
libpq...

> What can I do to get this resolved?

Could you show us a more complete test case? That might help in
pin-pointing the problem.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFLRYnoBcgs9XrR2kYRAjV/AJ9+HAZZu5sFKuHw1vp7aZNLKM7ykwCfQ+FX
q+NXaFojGP7uS4O/4Km/stM=
=zblK
-----END PGP SIGNATURE-----

Re: BUG #5268: PQgetvalue incorrectly returns 0

From
Tom Lane
Date:
"Mike Landis" <mlandis@pnmx.com> writes:
> When I execute the following SQL:

> "SELECT COUNT(*) FROM information_schema.tables WHERE table_name='proxies'"

> in the PGAdmmin 1.10.0, rev 7945-7946 query tool, I get "1" (the correct
> answer).  When I run the exact same SQL in a C program, I get a result set
> with one tuple and one field (so far so good), but when I run
> PQgetvalue(resultSet,0,0) I get "0" (wrong answer).

The most likely bet I can think of offhand is that your C program
is connecting to the wrong database.

> Am I missing something?  You can't do squat in a client program without
> PQgetvalue().  Is any sort of regression test being run against libpq
> functions?

libpq is exercised probably hundreds of millions of times a day,
every day.

            regards, tom lane

Re: BUG #5268: PQgetvalue incorrectly returns 0

From
Mike Landis
Date:
Try the following, where
mystring is an extension of std::string...
mystring sql( "SELECT COUNT(*) FROM information_schema.tables
WHERE table_name=3D'proxies' ");
int

     
        GetIntFromSQL(
mystring& sql )
{       
        
// if there's more than o=
ne
record in the ResultSet, still only returns the first float

        
int

     
        retVal =3D -1;
        
PGresult*       res =3D
GetQueryResult( sql );
        
if
 ( res )
        {
       
int
 nTuples =3D PQntuples(res);
        
        
int
 nFields =3D PQnfields(res);
        
        
if
 ( nTuples > 0  && 
nFields > 0 )
        
        {
       
char
*   val =3D
PQgetvalue(res,0,0);      
        
// get first column, first
field

        
        
        retVal =3D atoi(
val );
        
        }
        
        PQclear( res
);
        }
        
return
  retVal;
}
PGresult*
       GetQueryResult(
mystring& sql )
{       
        
// run a query that may
return a result set

        PGresult*
       res =3D PQexec( conn,
sql.c_str() );
        
if
 ( res )
        {
       
int

     status =3D PQresultStatus(res);
        
        
if
 ( status !=3D PGRES_TUPLES_OK )
        
        {
       
        
// what happened?

        
        
        fprintf( stderr,

"GetQueryResult(%s) ->
%s\n", sql.c_str(),
PQerrorMessage(conn) );
        
        
        PQclear( res
); 
        
        
        
        
// possibly moot

        
        
        res =3D NULL;
        
        }
        }
        
else

        {
       fprintf( stderr,

"GetQueryResult: insufficient memory to run:
%s\n", sql.c_str() );
        
        
throw


"GetQueryResult: insufficient
memory";
        }
        
return
  res;
}
At 02:14 AM 1/7/2010, you wrote:
-----BEGIN PGP SIGNED
MESSAGE-----
Hash: SHA1
On Thu, Jan 07, 2010 at 04:11:03AM +0000, Mike Landis wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5268
> Logged by:         
Mike Landis
> Email address:      mlandis@pnmx.com
> PostgreSQL version: 8.4.1
> Operating system:   Vista
> Description:        PQgetvalue
incorrectly returns 0
> Details:
>
> When I execute the following SQL:
>
> "SELECT COUNT(*) FROM information_schema.tables WHERE
table_name=3D'proxies'"
>
> in the PGAdmmin 1.10.0, rev 7945-7946 query tool, I get
"1" (the correct
> answer).  When I run the exact same SQL in a C program, I get a
result set
> with one tuple and one field (so far so good), but when I run
> PQgetvalue(resultSet,0,0) I get "0" (wrong answer).
>
> Am I missing something?  You can't do squat in a client program
without
> PQgetvalue().  Is any sort of regression test being run against
libpq
> functions?
Hm. I don't know for sure, but I'd assume that PGAdmin relies on
libpq...
> What can I do to get this resolved?
Could you show us a more complete test case? That might help in
pin-pointing the problem.
Regards
- -- tom=C3=A1s
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFLRYnoBcgs9XrR2kYRAjV/AJ9+HAZZu5sFKuHw1vp7aZNLKM7ykwCfQ+FX
q+NXaFojGP7uS4O/4Km/stM=3D
=3DzblK
-----END PGP SIGNATURE-----
No virus found in this incoming message.
Checked by AVG -
www.avg.com
Version: 9.0.725 / Virus Database: 270.14.127/2603 - Release Date:
01/06/10 02:35:00

Re: BUG #5268: PQgetvalue incorrectly returns 0

From
Robert Haas
Date:
On Thu, Jan 7, 2010 at 7:51 AM, Mike Landis <mlandis@pnmx.com> wrote:
> Try the following, where mystring is an extension of std::string...

It seems to me this would be a lot easier if you could attach a
complete program that someone could just compile, instead of code
fragments that are missing unspecified includes and necessary class
definitions.  It would take someone half an hour to fix this up and
they still won't be doing exactly what you're doing.

...Robert

Re: BUG #5268: PQgetvalue incorrectly returns 0

From
Mike Landis
Date:
#include <stdio.h>
#include <tchar.h>      // on Vista
#include <libpq-fe.h>   // from: the postgres 8.4 install include directory, for me...
D:\Programs\PostgreSQL\8.4\include

// configure these constants and get a '0' even though the same query produces a '1' in pgAdmin
const char*     pgUser = "us";          // PG user
const char*     pgDbms = "db";          // database
const char*     pgPass = "xyz";     // password
const char*     pgHost = "localhost";   // host domain or IP
const char*     pgTable = "tableName";  // a table that exists in the pgDbms

// on UNIX you can obviously revert the main() declaration to main( int argc, char** argv )
int _tmain( int argc, _TCHAR* argv[] )
{       char    connInfo[128];
        sprintf( connInfo, "host=%s dbname=%s user=%s password=%s",     pgHost, pgDbms, pgUser, pgPass );

        PGconn* conn = PQconnectdb( connInfo );
        if ( PQstatus(conn) == CONNECTION_OK )
        {               // in my case...  SELECT COUNT(*) FROM information_schema.tables WHERE table_name='proxies'
                sprintf( connInfo, "SELECT COUNT(*) FROM information_schema.tables WHERE table_name='%s'",
                        pgTable );
                PGresult*       res = PQexec( conn, connInfo );
                if ( res )
                {       if ( PQresultStatus(res) == PGRES_TUPLES_OK )
                        {       int nTuples = PQntuples(res);
                                int nFields = PQnfields(res);
                                if ( nTuples > 0  &&  nFields > 0 )
                                {       char*   val = PQgetvalue(res,0,0);      // get first column, first field
                                        fprintf( stderr, "val=%s\n", val );
                                }
                        }
                        PQclear( res );                                         // possibly moot
                        res = NULL;
                }
                PQfinish( conn );
        }
        return  0;
}
//--------------------------------------------------------------------
// you'll need to link with.libpq, in my case, that's:   D:\Programs\PostgreSQL\8.4\lib\libpq.lib

Re: BUG #5268: PQgetvalue incorrectly returns 0

From
Alvaro Herrera
Date:
Mike Landis wrote:
> <html>
> <body>
> <font color="#0000FF">Pick a database and table that exists, configure
> the string cconstants, compile and run the attached cpp, get 0 instead of
> 1 (that you get in pgAdmin...<br><br>

You realize that information_schema only shows you tables that have
permissions on, right?  Make sure you're using the same user in pgAdmin
than in your program.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: BUG #5268: PQgetvalue incorrectly returns 0

From
Mark Kirkwood
Date:
Mike Landis wrote:
> Pick a database and table that exists, configure the string
> cconstants, compile and run the attached cpp, get 0 instead of 1 (that
> you get in pgAdmin...
>
> Where's can I download the libpq source?  Maybe I can find and/or fix
> the problem myself.

Your program works fine for me (apart from minor datatype changes to
port to Linux). I used user=postgres and database=regression and
pgtable='tenk1' (schema from the regression test suite). As Tom
mentioned, your user might not have access to the table you are using -
try using the superuser account - typically 'postgres' to eliminate this
possibility.

With respect to the libpq source, it is in the source tarball from the
Postgresql website (directory src/interfaces/libpq ).

regards

Mark

Re: BUG #5268: PQgetvalue incorrectly returns 0

From
Robert Haas
Date:
On Thu, Jan 7, 2010 at 7:31 PM, Mark Kirkwood
<mark.kirkwood@catalyst.net.nz> wrote:
> Mike Landis wrote:
>>
>> Pick a database and table that exists, configure the string cconstants,
>> compile and run the attached cpp, get 0 instead of 1 (that you get in
>> pgAdmin...
>>
>> Where's can I download the libpq source? =A0Maybe I can find and/or fix =
the
>> problem myself.
>
> Your program works fine for me (apart from minor datatype changes to port=
 to
> Linux).

It works fine for me, too.  At first I was getting val=3D0, but then
after I modified it to actually be running against the same database
where I created the test table, I got val=3D1 as expected.

...Robert

Re: BUG #5268: PQgetvalue incorrectly returns 0

From
Mark Kirkwood
Date:
Mike Landis wrote:
> At 09:09 PM 1/7/2010, you wrote:
>
>>
>> I suspect they do not. Its all in the permissions.
>
> There's no user account control enabled on this Vista machine,
> therefore effectively wide open, hence different platform behavior or
> at least a difference between the behavior in pgAdmin and client program.
>

The lack of os level permissions is not relevant to this issue - I was
referring to database level users and their permissions on tables. It
does look like you are somehow running your c program as a different
(db) user from who you are using in Pgadmin. As Robert suggested, try
doing 'SELECT user' in both.

Also note that Pgadmin user PQexec and PQgetValue...

Cheers

Mark