Inserting integer array with postgresql in C (libpq) -

i'm trying post integer array postgresql database. i'm aware format string , send string 1 sql command. however, believe pqexecparams function should bring help. however, i'm kind of lost how use it.

    //we need convert number network byte order     int val1 = 131;     int val2 = 2342;     int val3[5] = { 0, 7, 15, 31, 63 };     //set values use     const char *values[3] = { (char *) &val1, (char *) &val2, (char *) val3 };     //calculate lengths of each of values     int lengths[3] = { sizeof(val1), sizeof(val2), sizeof(val3) * 5 };     //state parameters binary     int binary[3] = { 1, 1, 1 };      pgresult *res = pqexecparams(conn, "insert family values($1::int4, $2::int4, $3::integer[])", 3, //number of parameters             null, //ignore oid field             values, //values substitute $1 , $2             lengths, //the lengths, in bytes, of each of parameter values             binary, //whether values binary or not             0); //we want result in text format 

yes copied tutorial. returns :

 error:  invalid array flags 

using conventional method work:

pqexec(conn, "insert family values (2432, 31, '{0,1,2,3,4,5}')"); 

inserts data fine, , can read out fine well.

any appreciated! :)

libpq's pqexecparams can accept values in text or binary form.

for text values, must sprintf integer buffer put in char** values array. how it's done. can use text format query parameters, there no particular reason fall interpolating parameters sql string yourself.

if want use binary mode transfers, must instead ensure integer correct size target field, in network byte order, , have specified type oid. use htonl (for uint32_t) or htons (for uint16_t) that. it's fine cast away signedness since you're re-ordering bytes.

so:

• you cannot ignore oid field if you're planning use binary transfer
• use htonl, don't brew own byte-order conversion
• your values array construction wrong. you're putting char**s array of char* , casting away wrong type. want &val1[0] or (equivalent in most/all real-world c implementations, not technically same per spec) val1, instead of (char*)&val1
• you cannot assume on-wire format of integer[] same c's int32_t[]. must pass type oid int4arrayoid (see include/catalog/pg_type.h or select oid pg_type typname = '_int4' - internal type name of array _ in front of base type) , must construct postgresql array value compatible typreceive function in pg_type type (which array_recv) if intend send in binary mode. in particular, binary-format arrays have header. cannot leave out header.

in other words, code broken in multiple exciting ways , cannot possibly work written.

really, there rarely benefit in sending integers in binary mode. sending in text-mode faster because it's more compact on wire (small values). if you're going use binary mode, need understand how c represents integers, how network vs host byte order works, etc.

especially when working arrays, text format easier.

libpq make lot easier presently offering array construct / deconstruct functions both text , binary arrays. patches are, always, welcome. right now, 3rd party libraries libpqtypes largely fill role.