BUG #5268: PQgetvalue incorrectly returns 0

Started by Mike Landisover 16 years ago10 messagesbugs

mlandis@pnmx.com

over 16 years ago

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?

tomas@tuxteam.de

over 16 years ago

In reply to: Mike Landis (#1)

Re: BUG #5268: PQgetvalue incorrectly returns 0

-----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

over 16 years ago

In reply to: tomas@tuxteam.de (#2)

Re: BUG #5268: PQgetvalue incorrectly returns 0

<html>
<body>
Try the following, where
mystring is an extension of std::string... 
mystring sql( "SELECT COUNT(*) FROM information_schema.tables
WHERE table_name='proxies' "); 
int
<x-tab>
     </x-tab><x-tab>
        </x-tab>GetIntFromSQL(
mystring& sql ) 
{<x-tab>       </x-tab><x-tab>
        </x-tab>
// if there's more than one
record in the ResultSet, still only returns the first float 
<x-tab>
        </x-tab>
int
<x-tab>
     </x-tab><x-tab>
        </x-tab>retVal = -1; 
<x-tab>        </x-tab>
PGresult*<x-tab>       </x-tab>res =
GetQueryResult( sql ); 
<x-tab>        </x-tab>
if
 ( res ) 
<x-tab>        </x-tab>{<x-tab>
       </x-tab>
int
 nTuples = PQntuples(res); 
<x-tab>        </x-tab><x-tab>
        </x-tab>
int
 nFields = PQnfields(res); 
<x-tab>        </x-tab><x-tab>
        </x-tab>
if
 ( nTuples > 0  && 
nFields > 0 ) 
<x-tab>        </x-tab><x-tab>
        </x-tab>{<x-tab>
       </x-tab>
char
*<x-tab>   </x-tab>val =
PQgetvalue(res,0,0);<x-tab>      </x-tab>
<x-tab>        </x-tab>
// get first column, first
field 
<x-tab>
        </x-tab><x-tab>
        </x-tab><x-tab>
        </x-tab>retVal = atoi(
val ); 
<x-tab>        </x-tab><x-tab>
        </x-tab>} 
<x-tab>        </x-tab><x-tab>
        </x-tab>PQclear( res
); 
<x-tab>        </x-tab>} 
<x-tab>        </x-tab>
return
<x-tab>  </x-tab>retVal; 
} 
PGresult*<x-tab>
       </x-tab>GetQueryResult(
mystring& sql ) 
{<x-tab>       </x-tab><x-tab>
        </x-tab>
// run a query that may
return a result set 
<x-tab>
        </x-tab>PGresult*<x-tab>
       </x-tab>res = PQexec( conn,
sql.c_str() ); 
<x-tab>        </x-tab>
if
 ( res ) 
<x-tab>        </x-tab>{<x-tab>
       </x-tab>
int
<x-tab>
     </x-tab>status = PQresultStatus(res); 
<x-tab>        </x-tab><x-tab>
        </x-tab>
if
 ( status != PGRES_TUPLES_OK ) 
<x-tab>        </x-tab><x-tab>
        </x-tab>{<x-tab>
       </x-tab><x-tab>
        </x-tab>
// what happened? 
<x-tab>
        </x-tab><x-tab>
        </x-tab><x-tab>
        </x-tab>fprintf( stderr,

"GetQueryResult(%s) ->
%s\n", sql.c_str(),
PQerrorMessage(conn) ); 
<x-tab>        </x-tab><x-tab>
        </x-tab><x-tab>
        </x-tab>PQclear( res
);<x-tab> </x-tab><x-tab>
        </x-tab><x-tab>
        </x-tab><x-tab>
        </x-tab><x-tab>
        </x-tab>
// possibly moot 
<x-tab>
        </x-tab><x-tab>
        </x-tab><x-tab>
        </x-tab>res = NULL; 
<x-tab>        </x-tab><x-tab>
        </x-tab>} 
<x-tab>        </x-tab>} 
<x-tab>        </x-tab>
else 
<x-tab>
        </x-tab>{<x-tab>
       </x-tab>fprintf( stderr,

"GetQueryResult: insufficient memory to run:
%s\n", sql.c_str() ); 
<x-tab>        </x-tab><x-tab>
        </x-tab>
throw


"GetQueryResult: insufficient
memory"; 
<x-tab>        </x-tab>} 
<x-tab>        </x-tab>
return
<x-tab>  </x-tab>res; 
} 
At 02:14 AM 1/7/2010, you wrote: 
<blockquote type=cite class=cite cite="">-----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----- 
No virus found in this incoming message. 
Checked by AVG -
<a href="http://www.avg.com/" eudora="autourl">www.avg.com</a> 
Version: 9.0.725 / Virus Database: 270.14.127/2603 - Release Date:
01/06/10 02:35:00</blockquote></body>
</html>

Tom Lane

tgl@sss.pgh.pa.us

over 16 years ago

In reply to: Mike Landis (#1)

Re: BUG #5268: PQgetvalue incorrectly returns 0

"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

Robert Haas

robertmhaas@gmail.com

over 16 years ago

In reply to: Mike Landis (#3)

Re: BUG #5268: PQgetvalue incorrectly returns 0

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

Mike Landis

mlandis@pnmx.com

over 16 years ago

In reply to: Robert Haas (#5)

Re: BUG #5268: PQgetvalue incorrectly returns 0

#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

Import Notes

Resolved by subject fallback

Alvaro Herrera

alvherre@2ndquadrant.com

over 16 years ago

In reply to: Mike Landis (#6)

Re: BUG #5268: PQgetvalue incorrectly returns 0

Mike Landis wrote:

<html>
<body>
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...

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.

Mark Kirkwood

mark.kirkwood@catalyst.net.nz

over 16 years ago

In reply to: Mike Landis (#6)

Re: BUG #5268: PQgetvalue incorrectly returns 0

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

Robert Haas

robertmhaas@gmail.com

over 16 years ago

In reply to: Mark Kirkwood (#8)

Re: BUG #5268: PQgetvalue incorrectly returns 0

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? 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).

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

...Robert

#10

Mark Kirkwood

mark.kirkwood@catalyst.net.nz

over 16 years ago

In reply to: Robert Haas (#9)

Re: BUG #5268: PQgetvalue incorrectly returns 0

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

Import Notes

Reply to msg id not found: 20100108035148.92FC032D69@mail.catalyst.net.nzReference msg id not found: 20100108015745.84EEA32CEE@mail.catalyst.net.nzReference msg id not found: 4B4693F1.80600@catalyst.net.nzReference msg id not found: 20100108035148.92FC032D69@mail.catalyst.net.nz | Resolved by subject fallback

BUG #5268: PQgetvalue incorrectly returns 0

Attachments: