Thread: BUG #5268: PQgetvalue incorrectly returns 0
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?
-----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-----
"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
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
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
#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
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.
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
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
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