学习笔记之postgresql

佚名 6年前 (2019-05-05) 随笔 756人围观抢沙发百度已收录

void pg_test::pg_init()
{
    char *pghost = "127.0.0.1",
        *pgport = {0},
        *pgoptions = { 0 },
        *pgtty = { 0 };

    char  *dbName = "db_cctv";
    int  nFields = 0;
    int  i = 0;
    int j =0;

#ifdef DEBUG
    FILE    *debug;
#endif  /* DEBUG */

    PGconn    *conn = {0};
    PGresult   *res = { 0 };

    /*
    * Begin by setting the parameters for a backend connection.
    * If the parameters are NULL, the system tries to use
    * reasonable defaults by looking up environment variables
    * or, failing that, using hardwired constants.
    */

    const char *conninfo = "postgresql://dxh:123456@localhost:5432/db_cctv";

    /* make a connection to the database */
    //conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
    conn = PQconnectdb(conninfo);

    /* check to see that the backend connection was successfully made */
    if (PQstatus(conn) == CONNECTION_BAD)
    {
        fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
        fprintf(stderr, "%s", PQerrorMessage(conn));
        exit_nicely(conn);
    }

#ifdef DEBUG
    debug = fopen("/tmp/trace.out", "w");
    PQtrace(conn, debug);
#endif  /* DEBUG */

    /* start a transaction block */
    res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "BEGIN command failed\n");
        PQclear(res);
        exit_nicely(conn);
    }

    /*
    * should PQclear PGresult whenever it is no longer needed
    * so as to avoid memory leaks
    */
    PQclear(res);

    /*
    * fetch instances from the pg_database, the system catalog of
    * databases
    */
    res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "DECLARE CURSOR command failed\n");
        PQclear(res);
        exit_nicely(conn);
    }
    PQclear(res);

    res = PQexec(conn, "FETCH ALL in myportal");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH ALL command didn't return tuples properly\n");
        PQclear(res);
        exit_nicely(conn);
    }

    /* first, print out the attribute names */
    nFields = PQnfields(res);
    for (i = 0; i < nFields; i++)
        printf("%-15s", PQfname(res, i));

    printf("\n\n");

    /* next, print out the instances */
    for (i = 0; i < PQntuples(res); i++)
    {
        for (j = 0; j < nFields; j++)
            printf("%-15s", PQgetvalue(res, i, j));
        printf("\n");
    }

    PQclear(res);

    /* close the portal */
    res = PQexec(conn, "CLOSE myportal");
    PQclear(res);

    /* end the transaction */
    res = PQexec(conn, "END");
    PQclear(res);

    /* close the connection to the database and cleanup */
    PQfinish(conn);

#ifdef DEBUG
    fclose(debug);
#endif  /* DEBUG */

    return ;
}