GCC C and passing integer to PostgreSQL

Question

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);                                                                                                                                                                                           
}

If you don't supply an array with datatypes, all the parameters should be strings. -->> `char buff[12]; sprintf(buff,"%d", csrv0); params[0] = buff;` — joop, Sep 21 '18 at 15:54

wildplasser · Answer 1 · 2018-09-22T13:11:25.293

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);                                                                                                                                                                                           
}

score 0 · Answer 2 · answered Sep 24 '18 at 11:12

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.

Oops, I forgot about the endianness! – wildplasser Sep 27 '18 at 07:26 — wildplasser, Sep 27 '18 at 07:26
No, you didn't, because you used text mode. – Laurenz Albe Sep 27 '18 at 10:56 — Laurenz Albe, Sep 27 '18 at 10:56

score 0 · Answer 3 · answered Aug 29 '19 at 20:12

0

VS C++ not liking htonl(42):

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

answered Aug 29 '19 at 20:12

Rick

106
9

GCC C and passing integer to PostgreSQL

3 Answers3