Thread: Select statement error !!!!
Hello,
I am doing a simple program with PostgreSql wherein i am giving a select
statement as an argument to PQexec like this
char query[1024];
strcpy(query,"Select * from Udp_Table");
res = PQexec(conn,query);
if(!res || PQresultStatus(res)! = PGRES_COMMAND_OK)
{
fprintf(stderr,"Select Failed.\n");
PQclear(res);
exit(1);
}
now if i try to execute the above statement it is giving me always teh
select failed message. I dont know where the problem is? But when i try to
store some other query in array like update statement or insert statement or
any other statement it is working and giving the results i want to see. But
i dont know what is wrong with the select statement i think it should work
isn't it? Can anybody out there please help me to solve this problem.
Thank You,
Goutham.V
MSIT.
_________________________________________________________________
Get personal loans. It's hassle-free.
http://server1.msn.co.in/msnleads/citibankpersonalloan/citibankploanjuly03.asp?type=txt
It's approved instantly.
On Wed, 2003-09-24 at 11:27, Vutharkar Goutham wrote: > Hello, > > I am doing a simple program with PostgreSql wherein i am giving a select > statement as an argument to PQexec like this > > > char query[1024]; > strcpy(query,"Select * from Udp_Table"); > now if i try to execute the above statement it is giving me always teh > select failed message. In that statement you have a mixed case name for the table. If it has a mixed case name in the database too, the name needs to be enclosed in double quotes. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "And we know that all things work together for good to them that love God, to them who are the called according to his purpose." Romans 8:28
"Vutharkar Goutham" <goutham4u@hotmail.com> writes:
> strcpy(query,"Select * from Udp_Table");
> res = PQexec(conn,query);
> if(!res || PQresultStatus(res)! = PGRES_COMMAND_OK)
> {
> fprintf(stderr,"Select Failed.\n");
The result status from a successful SELECT will be PGRES_TUPLES_OK;
PGRES_COMMAND_OK is only used for commands that do not return a tuple
set.
As commented nearby, it's not real clear that this query is succeeding,
anyway. It would behoove you to print something more useful than
"Select Failed" in your failure path ... see PQresStatus() and
PQresultErrorMessage() for starters.
regards, tom lane
PS: this does not belong on pgsql-docs. pgsql-novice would be an
appropriate forum for first-time questions.
Hello,
I am sorry that this isn't the right group to putup the question but i am
desperate to get it right and moreover thats what the major hindrance to our
project. We are doing Intrusion Detection System as project to fulfill the
requirement of completion of Post Graduation in MSIT.
So we are using PostgreSQL as our database to store the captured data from a
LAN or from outside network. So in the mean process we successfully captured
the data packets and sent them into the database tables but the problem is
when we are are trying to retrieve data then we are failing and we dont know
how to proceed further. We searched all the forums for right answer but
didn't get to it so i was forced to post here. Now let me paste the code
here ....
#include<stdio.h>
#include<libpq-fe.h>
void exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
int main()
{
char *pghost,*pgport,*pgoptions,*pgtty;
char *dbname;
char query[1024];
struct PQprintOpt *po;
int nFields;
int i,j;
FILE *fp;
fp = fopen("data.txt","w+");
strcpy(query,"select * from UDP_Table");
printf("\nInitial Query is : %s\n",query);
PGconn *conn;
PGresult *res;
pghost = NULL;
pgport = NULL;
pgoptions = NULL;
pgtty = NULL;
dbname = "Project";
conn = PQsetdb(pghost,pgport,pgoptions,pgtty,dbname);
if(PQstatus(conn) == CONNECTION_BAD)
{
fprintf(stderr,"Failed to make a connection.\n",dbname);
fprintf(stderr,"%s",PQerrorMessage(conn));
exit_nicely(conn);
}
else
printf("\nConnection established with backend.\n");
res = PQexec(conn,"BEGIN");
if(!res || PQresultStatus(res)!=PGRES_TUPLES_OK)
{
fprintf(stderr,"Begin Failed.\n");
PQclear(res);
exit_nicely(conn);
}
else
printf("\nBegin Transaction Completed Successfully.\n");
printf("\nQuery B4 execution is : %s\n",query);
res = PQexec(conn,query);
if(!res || PQresultStatus(res)!=PGRES_COMMAND_OK)
{
fprintf(stderr,"\nSelect Statement Failed.\n");
PQclear(res);
exit_nicely(conn);
}
else
{
printf("\nSelect statement executed successfully.\n");
PQprint(fp,res,(const struct PQprintOpt *)po);
}
// Print Attribute Names
printf("\nThe Attribute Names are \n\n");
nFields = PQnfields(res);
for(i = 0;i<nFields;i++)
printf("%-15s",PQfname(res,i));
printf("\n\n");
//Print out rows
printf("The Rows in the table are \n\n");
for(i = 0;i<PQntuples(res);i++)
{
for(i = 0;j<nFields;j++)
printf("%-15s",PQgetvalue(res,i,j));
printf("\n");
}
res = PQexec(conn,"COMMIT");
PQclear(res);
PQfinish(conn);
return 0;
}
I tried as told by you but still i failed. So please dont mind solving the
problem for me if i am not troubling you. Your help will be appreciated a
lot by our project team.
Thank You,
Goutham.V
MSIT.
_________________________________________________________________
Talk to Karthikeyan. Watch his stunning feats.
http://server1.msn.co.in/sp03/tataracing/index.asp Download images.
On Wed, 2003-09-24 at 15:48, Vutharkar Goutham wrote:
...
> So we are using PostgreSQL as our database to store the captured data from a
> LAN or from outside network. So in the mean process we successfully captured
> the data packets and sent them into the database tables but the problem is
> when we are are trying to retrieve data then we are failing and we dont know
> how to proceed further. We searched all the forums for right answer but
> didn't get to it so i was forced to post here. Now let me paste the code
> here ....
>
>
>
>
> #include<stdio.h>
> #include<libpq-fe.h>
>
> void exit_nicely(PGconn *conn)
> {
> PQfinish(conn);
> exit(1);
> }
>
> int main()
> {
> char *pghost,*pgport,*pgoptions,*pgtty;
> char *dbname;
> char query[1024];
> struct PQprintOpt *po;
> int nFields;
> int i,j;
> FILE *fp;
>
> fp = fopen("data.txt","w+");
>
> strcpy(query,"select * from UDP_Table");
^^^^^^^^^
If this table's name is truly mixed case, it must be enclosed in double
quotes; otherwise it will be folded to lowercase automatically.
> printf("\nInitial Query is : %s\n",query);
> PGconn *conn;
> PGresult *res;
>
> pghost = NULL;
> pgport = NULL;
> pgoptions = NULL;
> pgtty = NULL;
> dbname = "Project";
>
> conn = PQsetdb(pghost,pgport,pgoptions,pgtty,dbname);
>
> if(PQstatus(conn) == CONNECTION_BAD)
> {
> fprintf(stderr,"Failed to make a connection.\n",dbname);
> fprintf(stderr,"%s",PQerrorMessage(conn));
> exit_nicely(conn);
> }
> else
> printf("\nConnection established with backend.\n");
>
> res = PQexec(conn,"BEGIN");
>
> if(!res || PQresultStatus(res)!=PGRES_TUPLES_OK)
^^^^^^^^^^^^^^^
PGRES_COMMAND_OK
> {
> fprintf(stderr,"Begin Failed.\n");
> PQclear(res);
> exit_nicely(conn);
> }
> else
> printf("\nBegin Transaction Completed Successfully.\n");
>
> printf("\nQuery B4 execution is : %s\n",query);
>
> res = PQexec(conn,query);
>
> if(!res || PQresultStatus(res)!=PGRES_COMMAND_OK)
^^^^^^^^^^^^^^^^
PGRES_TUPLES_OK
> {
> fprintf(stderr,"\nSelect Statement Failed.\n");
> PQclear(res);
> exit_nicely(conn);
> }
> else
> {
> printf("\nSelect statement executed successfully.\n");
> PQprint(fp,res,(const struct PQprintOpt *)po);
segfaults here - the third parameter should be const PQprintopt * which
should point to a PQprintOpt struct with values assigned (see the libpq
manual).
> }
>
> // Print Attribute Names
>
> printf("\nThe Attribute Names are \n\n");
>
> nFields = PQnfields(res);
>
> for(i = 0;i<nFields;i++)
> printf("%-15s",PQfname(res,i));
> printf("\n\n");
>
> //Print out rows
>
> printf("The Rows in the table are \n\n");
>
> for(i = 0;i<PQntuples(res);i++)
> {
> for(i = 0;j<nFields;j++)
^^^
j
> printf("%-15s",PQgetvalue(res,i,j));
> printf("\n");
> }
>
> res = PQexec(conn,"COMMIT");
>
> PQclear(res);
>
> PQfinish(conn);
>
> return 0;
> }
>
>
> I tried as told by you but still i failed. So please dont mind solving the
> problem for me if i am not troubling you. Your help will be appreciated a
> lot by our project team.
>
> Thank You,
>
> Goutham.V
> MSIT.
>
> _________________________________________________________________
> Talk to Karthikeyan. Watch his stunning feats.
> http://server1.msn.co.in/sp03/tataracing/index.asp Download images.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And we know that all things work together for good to
them that love God, to them who are the called
according to his purpose."
Romans 8:28