How to insert array of bytes in PostgreSQL table via libpq C++ API

Question

I am trying to update table

CREATE TABLE some_table
(
    id integer NOT NULL,
    client_fid bigint NOT NULL,
    index bytea[],
    update_time timestamp without time zone
)
WITH (
    OIDS = FALSE

using modified code snipped from here How to insert text array in PostgreSQL table in binary format using libpq?

#define BYTEAARRAYOID 1001
#define BYTEAOID 17

Here is a pgvals_t structure definition

struct pgvals_t
{
    /* number of array dimensions */
    int32_t ndims;
    /* flag describing if array has NULL values */
    int32_t hasNull;
    /* Oid of data stored in array. In our case is 25 for TEXT */
    Oid oidType;
    /* Number of elements in array */
    int32_t totalLen;
    /* Not sure for this one. 
     I think it describes dimensions of elements in case of arrays storing arrays */
    int32_t subDims;

    /* Here our data begins */

} __attribute__ ((__packed__));

I've removed dataBegins pointer from struct as it affects data layout in memo

std::size_t nElems = _data.size();
        uint32_t valsDataSize = sizeof(prx::pgvals_t) + sizeof(int32_t) * nElems +
                            sizeof(uint8_t)*nElems;

        void *pData = malloc(valsDataSize);
        prx::pgvals_t* pvals = (prx::pgvals_t*)pData;
        /* our array has one dimension */
        pvals->ndims = ntohl(1);
        /* our array has no NULL elements */
        pvals->hasNull = ntohl(0);
        /* type of our elements is bytea */
        pvals->oidType = ntohl(BYTEAOID);
        /* our array has nElems elements */
        pvals->totalLen = ntohl(nElems);
        pvals->subDims = ntohl(1);

        int32_t elemLen = ntohl(sizeof(uint8_t));
        std::size_t offset = sizeof(elemLen) + sizeof(_data[0]);
        char * ptr = (char*)(pvals + sizeof(prx::pgvals_t));
        for(auto byte : _data){

            memcpy(ptr, &elemLen, sizeof(elemLen));
            memcpy(ptr + sizeof(elemLen), &byte, sizeof(byte));
            ptr += offset;
        }

        Oid paramTypes[] = { BYTEAARRAYOID };

        char * paramValues[] = {(char* )pData};
        int paramLengths[] =  { (int)valsDataSize };
        int paramFormats[] = {1};
        PGresult *res = PQexecParams(m_conn, _statement.c_str(),
            1,             
            paramTypes,
            paramValues,
            paramLengths,
            paramFormats,
            1
        );
        if (PQresultStatus(res) != PGRES_COMMAND_OK) {
            std::string errMsg = PQresultErrorMessage(res);
            PQclear(res);
        throw std::runtime_error(errMsg);
    }
    free(pData);

The binary data is contained in std::vector variable and am using the following query in a _statement variable of type std::string

INSERT INTO some_table \
(id, client_id, \"index\", update_time) \
VALUES \
 (1, 2, $1, NOW())

Now after call to PQExecParams I am get an exception with message "incorrect binary data format in bind parameter 1"

What can be the problem here?

"using modified code snipped from here" - cobbling code together from various sources without really knowing what the code does/what one is doing, rarely ends well. Just saying. — Jesper Juhl
– Jesper Juhl, Commented Feb 27, 2019 at 19:36

Laurenz Albe · Accepted Answer · 2019-02-27 19:46:40Z

0

If you want to pass a bytea[] in binary format, you have to use the binary array format as read by array_recv and written by array_send.

You cannot just pass a C array.

answered Feb 27, 2019 at 19:46

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eugene Kotyashov Over a year ago

Yes, you're right, I will probably go for text format transfer

Collectives™ on Stack Overflow

How to insert array of bytes in PostgreSQL table via libpq C++ API

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related