libpq (1/1)

Introduction

libpq is the C client library for PostgreSQL. In this tutorial we will show how to write programs that make use of it to interfact with PostgreSQL databases.

Prerequisites

Familiarity with basic PostgreSQL installation and operations is assumed in this tutorial. We do have a PostgreSQL tutorial that covers this.

The examples use Bakefile to generate the Visual Studio project files and makefiles. Knowdledge of Bakefile is not required to use and understand these makefiles but it may help as the bakefile is usually a lot more terse.

Setup

Windows

On Windows libpq is installed when you install the database itself using the installer as described in our PostgreSQL basic tutorial.

libpq is available in a 32-bit and a 64-bit version. The 32-bit version is installed when you install a 32-bit version of PostgreSQL and the 64-bit version is installed when you install a 64-bit version of PostgreSQL. This is not necessarily what you want though as the fact that you have installed a 64-bit version of PostgreSQL doesn't necessarily mean that the client application will be a 64-bit application. Fortunately nothing prevents you from installing both a 32-bit and 64-bit version on the same machine.

The header files can be found in the include directory at the root of the PostgreSQL installation e.g. C:\Program Files\PostgreSQL\9.4\include for a 64-bit installation of version 9.4. The header file that you need to include in your code is libpq-fe.h.

libpq is in fact a DLL. The import library, libpq.lib, can be found in the lib directory at the root of the PostgreSQL installation e.g. C:\Program Files\PostgreSQL\9.4\lib for a 64-bit installation of version 9.4.

The DLL itself, libpq.dll, can be found in the bin directory at the root of the PostgreSQL installation e.g. C:\Program Files\PostgreSQL\9.4\bin for a 64-bit installation of version 9.4. In that directory you will also find intl.dll in the 32-bit version or libintl-8.dll in the 64-bit version. libpq.dll has a dependency on these DLLs.

Both libpq.dll and its dependency, intl.dll (32-bit case) or libintl-8.dll (64-bit case), must be on the DLL search path when launching the application. The easiest way to achieve this is to copy them in the same directory as the executable.

Maintaining 32-bit and 64-bit configurations (Windows)

All the examples in this page can be built on Windows as 32-bit or 64-bit applications. You can simply select the build configuration you want to build in Visual Studio. Depending on your choice you need to have the appropriate header files, libraries and DLLs in place (32-bit, 64-bit or both) as explained in the Setup section.

We also want to avoid hardcoding paths in our makefiles so we use 2 environment variables, POSTGRESQL_32 and POSTGRESQL_64, to point to the root directory of the corresponding PostgreSQL installation.

As for the DLLs we will simply copy them in the same directory as the executable. It is not recommended TODO

Connecting to a database

The full example is available from our Codeplex repository at PostgreSQL/C/DbConnect1.

File: PostgreSQL/C/DbConnect1/Source/main.c

#include <libpq-fe.h>

int main(int argc, char* argv[])
{
    // The variables used to build the connection
    // string. Modify them to match your setup.
    const char* hostaddr = "127.0.0.1";
    const int port = 5432;
    const char* dbname = "postgres";
    const char* user = "postgres";
    const char* password = "password";

    char connectionString[1024];
    _snprintf(connectionString, sizeof(connectionString),
        "hostaddr=%s port=%d dbname=%s user=%s password=%s",
        hostaddr, port, dbname, user, password);

    PGconn* dbConnection = PQconnectdb(connectionString);
    if (PQstatus(dbConnection) != CONNECTION_OK)
    {
        printf("Database connection error: %s", PQerrorMessage(dbConnection));
    }

    PQfinish(dbConnection);

    return 0;
}

Creating a database

The full example is available from our Codeplex repository at PostgreSQL/C/DbCreation1.

File: PostgreSQL/C/DbCreation1/Source/main.c

#include <libpq-fe.h>

int main(int argc, char* argv[])
{
    // The variables used to build the connection
    // string. Modify them to match your setup.
    const char* hostaddr = "127.0.0.1";
    const int port = 5432;
    const char* dbname = "postgres";
    const char* user = "postgres";
    const char* password = "password";

    char connectionString[1024];
    _snprintf(connectionString, sizeof(connectionString),
        "hostaddr=%s port=%d dbname=%s user=%s password=%s",
        hostaddr, port, dbname, user, password);

    PGconn* dbConnection = dbConnection = PQconnectdb(connectionString);
    if (PQstatus(dbConnection) != CONNECTION_OK)
    {
        printf("Database connection error: %s", PQerrorMessage(dbConnection));
    }
    else
    {
        PGresult* result = PQexec(dbConnection, "CREATE DATABASE mydb");
        if (PQresultStatus(result) != PGRES_COMMAND_OK)
        {
            printf("Database creation error: %s", PQresultErrorMessage(result));
        }
    }

    PQfinish(dbConnection);

    return 0;
}

Creating a table

#include <libpq-fe.h>

int main(int argc, char* argv[])
{
    // The variables used to build the connection
    // string. Modify them to match your setup.
    const char* hostaddr = "127.0.0.1";
    const int port = 5432;
    const char* dbname = "mydb";
    const char* user = "postgres";
    const char* password = "password";

    char connectionString[1024];
    _snprintf(connectionString, sizeof(connectionString),
        "hostaddr=%s port=%d dbname=%s user=%s password=%s",
        hostaddr, port, dbname, user, password);

    PGconn* dbConnection = dbConnection = PQconnectdb(connectionString);
    if (PQstatus(dbConnection) != CONNECTION_OK)
    {
        printf("Database connection error: %s", PQerrorMessage(dbConnection));
    }
    else
    {
        PGresult* result = PQexec(dbConnection, "CREATE TABLE mytable(column1 TEXT)");
        if (PQresultStatus(result) != PGRES_COMMAND_OK)
        {
            printf("Table creation error: %s", PQresultErrorMessage(result));
        }
    }

    PQfinish(dbConnection);

    return 0;
}
#include <libpq-fe.h>

int main(int argc, char* argv[])
{
    // The variables used to build the connection
    // string. Modify them to match your setup.
    const char* hostaddr = "127.0.0.1";
    const int port = 5432;
    const char* dbname = "mydb";
    const char* user = "postgres";
    const char* password = "password";

    char connectionString[1024];
    _snprintf(connectionString, sizeof(connectionString),
        "hostaddr=%s port=%d dbname=%s user=%s password=%s",
        hostaddr, port, dbname, user, password);

    PGconn* dbConnection = dbConnection = PQconnectdb(connectionString);
    if (PQstatus(dbConnection) != CONNECTION_OK)
    {
        printf("Database connection error: %s", PQerrorMessage(dbConnection));
    }
    else
    {
        PGresult* result = PQexec(dbConnection, "ALTER TABLE mytable ADD COLUMN column2 TEXT");
        if (PQresultStatus(result) != PGRES_COMMAND_OK)
        {
            printf("Table creation error: %s", PQresultErrorMessage(result));
        }
    }

    PQfinish(dbConnection);

    return 0;
}

References


blog comments powered by Disqus

Copyright(c) 2006-2017 Xavier Leclercq | Privacy policy

Home
Contact Us
Search