Thread: Regarding PL/SQL with C

Dear All,

          Iam a newbiw to PL/SQL. So iam having a query regarding
accessing PL/SQL through C program.

I have written code for connecting to a database and select one row and
just print it out.. Iam able to make a connection with the databse. But
after connection it is showing that select query failed.... but if iam
directly selecting through PL/SQL prompt iam able to do the same.......

I have attached the source file also.........

thanks & regards
krishnaprasd

-

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

        int
        main()
        {
            char        state_code[3];
            char        query_string[256];
            PGconn     *conn;
            PGresult   *res;
            int         i;

        conn = PQconnectStart("user=postgres,dbname=krishna");

        switch(PQstatus(conn))
        {
            case CONNECTION_MADE:
                printf("Connection established\n");
                break;
            case CONNECTION_BAD:
                printf("Connection error\n");
                break;
            default:
                printf("No choice\n");
        }

            sprintf(query_string,"SELECT * from prasad");

           res = PQexec (conn,query_string);


            if (PQresultStatus(res) != PGRES_TUPLES_OK)
            {
                fprintf(stderr, "SELECT query failed.\n");
                PQclear(res);
                PQfinish(conn);
                exit(1);
            }

            for (i = 0; i < PQntuples(res); i++)
                printf("%s\n", PQgetvalue(res, i, 0));

            PQclear(res);

            PQfinish(conn);

            return 0;
        }

On Sat, Feb 25, 2006 at 02:03:51PM +0530, Krishnaprasad wrote:
> I have written code for connecting to a database and select one row and
> just print it out.. Iam able to make a connection with the databse. But
> after connection it is showing that select query failed.... but if iam
> directly selecting through PL/SQL prompt iam able to do the same.......

You're connecting to the database thusly:

>   conn = PQconnectStart("user=postgres,dbname=krishna");

Parameters in the connect string should be space-separated, not
comma-separated.  The select is probably failing because the connect
failed; you're not seeing the connection failure because you're
calling PQconnectStart to make a nonblocking connection and you're
not waiting for the connection to complete before making the query.
Unless you need a nonblocking connection use PQconnectdb instead:

    conn = PQconnectdb("user=postgres dbname=krishna");

Then check for CONNECTION_OK, not CONNECTION_MADE.

When an operation has failed, you can use PQerrorMessage or
PQresultErrorMessage to display the reason.

--
Michael Fuhr

hi michael

          thanks for ur input... I tried with *PQconnectdb* also. But
still it is showing connection error
when I tried with PQErrorMessage it is coming as below.

*FATAL:  IDENT authentication failed for user "postgres" ...

pls help..

thanks
krishna
*
Michael Fuhr wrote:

>On Sat, Feb 25, 2006 at 02:03:51PM +0530, Krishnaprasad wrote:
>
>
>>I have written code for connecting to a database and select one row and
>>just print it out.. Iam able to make a connection with the databse. But
>>after connection it is showing that select query failed.... but if iam
>>directly selecting through PL/SQL prompt iam able to do the same.......
>>
>>
>
>You're connecting to the database thusly:
>
>
>
>>  conn = PQconnectStart("user=postgres,dbname=krishna");
>>
>>
>
>Parameters in the connect string should be space-separated, not
>comma-separated.  The select is probably failing because the connect
>failed; you're not seeing the connection failure because you're
>calling PQconnectStart to make a nonblocking connection and you're
>not waiting for the connection to complete before making the query.
>Unless you need a nonblocking connection use PQconnectdb instead:
>
>    conn = PQconnectdb("user=postgres dbname=krishna");
>
>Then check for CONNECTION_OK, not CONNECTION_MADE.
>
>When an operation has failed, you can use PQerrorMessage or
>PQresultErrorMessage to display the reason.
>
>
>


--
Rgds

KRISHNAPRASAD.K
R&D Engineer
HCL INFOSYSTEMS LTD
PONDICHERRY
Mob:09894530948

On Mon, Feb 27, 2006 at 09:24:33AM +0530, Krishnaprasad wrote:
>          thanks for ur input... I tried with *PQconnectdb* also. But
> still it is showing connection error
> when I tried with PQErrorMessage it is coming as below.
>
> *FATAL:  IDENT authentication failed for user "postgres" ...

What operating system user is the program running as?  What connect
string are you using now (the previous one was erroneous)?  Does
pg_hba.conf have an ident map that would allow the operating system
user to connect to the specified database as the specified database
user?

If you're not sure how to check pg_hba.conf then see the "Client
Authentication" chapter in the documentation, in particular the
"Ident-based authentication" section:

http://www.postgresql.org/docs/8.1/interactive/client-authentication.html
http://www.postgresql.org/docs/8.1/interactive/auth-methods.html#AUTH-IDENT

--
Michael Fuhr