CODEWITHSUNDEEP
cpostgresqllibpq
marchefka
marchefka

Reputation:

GCC C and passing integer to PostgreSQL

I know that there probably was plenty on that but after several days of searching I am unable to find how to do one simple passing of integer and char in one go to PostgreSQL from C under Linux.

In PHP it is easy, like 123, and in C using libpq it seem to be like something out of ordinary.

I had a look at PQexecParams but is seem to be not helping. Examples on the net are not helping as well and it seems to be an impossible mission.

Would someone be kind enough to translate this simple PHP statement to C and show me how to pass multiple vars of different types in one INSERT query. 

col1 is INT
col2 is CHAR

$int1 = 1;
$char1 = 'text';
$query = "INSERT INTO table (col1, col2) values ('$int1',$char1)";
$result = ibase_query($query);

This would show what I am trying to do (please mind the code is very wrong):

void insert_CommsDb(PGconn *conn, PGresult *pgres, int csrv0) {                                                                                                                                                                                                             const char * params[1];
params[0] = csrv0;

pgres = PQexecParams(conn, "INSERT INTO comms_db (srv0::int) values ($1)",
1,
NULL,
params,
1,
NULL,
0);

if (PQresultStatus(pgres) != PGRES_COMMAND_OK)
{
    fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
    exit_nicely(conn,pgres);
}
    PQclear(pgres);                                                                                                                                                                                           
}

Upvotes: 0

Views: 746

Answers (3)

Rick
Rick

Reputation: 106

VS C++ not liking htonl(42):

arg0 = htonl(42); /* external binary format: network byte order */

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246818

wildplasser's answer shows the way in general.

Since you explicitly asked about several parameters, I'll add an example for that.

If you are not happy to convert integers to strings, the alternative would be to use the external binary format of the data type in question. That requires inside knowledge and probably reading the PostgreSQL source. For some data types, it can also depend on the hardware.

PGresult *res;
PGconn *conn;
Oid types[2];
char * values[2];
int lengths[2], formats[2];
int arg0;

/* connect to the database */

/*  
 * The first argument is in binary format.
 * Apart from having to use the "external binary
 * format" for the data, we have to specify
 * type and length.
 */
arg0 = htonl(42);  /* external binary format: network byte order */
types[0] = 23;     /* OID of "int4" */
values[0] = (char *) &arg0;
lengths[0] = sizeof(int);
formats[0] = 1;

/* second argument is in text format */
types[1] = 0;
values[1] = "something";
lengths[1] = 0;
formats[1] = 0;

res = PQexecParams(
        conn,
        "INSERT INTO mytab (col1, col2) values ($1, $2)",
        2,
        types,
        (const char * const *)values,
        lengths,
        formats,
        0  /* results in text format */
      );

I'd recommend that you use the text format for most data types.

The notable exception is bytea, where it usually is an advantage to use the binary format, as it saves space and CPU power. In this case, the external binary format is simply the bytes.

Upvotes: 0

wildplasser
wildplasser

Reputation: 44250

https://www.postgresql.org/docs/current/static/libpq-exec.html

As @joop commented above: If the paramTypes argument is NULL, all the params are assumed to be strings. So, you should transform your int argument to a string.

void insert_CommsDb(PGconn *conn, int csrv0) 
{
PGresult *pgres;
char * params[1];
char buff[12];

sprintf(buff, "%d", csrv0);

params[0] = buff;

pgres = PQexecParams(conn
        , "INSERT INTO comms_db (srv0::int) values ($1)"  // The query (we dont need the cast here)
        , 1     // number of params
        , NULL  // array with types, or NULL
        , params // array with parameter values
        , NULL  // ARRAY with parameter lenghts
        , NULL  // array with per-param flags indicating binary/non binary
        , 0     // set to 1 if we want BINARY results, 0 for txt
        );

if (PQrresultStatus(pgres) != PGRES_COMMAND_OK)
{
    fprintf(stderr, "INSERT failed: %s", PQerrorMessage(conn));
    exit_nicely(conn,pgres);
}

PQclear(pgres);                                                                                                                                                                                           
}

Upvotes: 1

Related Questions