Subject: Re: Select statement error !!!!

From: "Vutharkar Goutham" <[email-masked]>
Date: Wed, 24 Sep 2003 14:48:22 +0000

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.

---

From: Oliver Elphick <[email-masked]>
Date: Wed, 24 Sep 2003 18:13:16 +0100

On Wed, 2003-09-24 at 15:48, Vutharkar Goutham wrote:
...
> 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.

> if(!res || PQresultStatus(res)!=PGRES_TUPLES_OK)
                                  ^^^^^^^^^^^^^^^
                                  PGRES_COMMAND_OK
> if(!res || PQresultStatus(res)!=PGRES_COMMAND_OK)
                                  ^^^^^^^^^^^^^^^^
                                  PGRES_TUPLES_OK

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

> for(i = 0;i<PQntuples(res);i++)
     ^^^
      j

> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
-- 
Oliver Elphick                                [email-masked]
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