PQexecparams - Not supporting third parameters in binding array

Question

I am newbie in postgresql and trying to insert the values in db with PQexecparams. When i bind two parameters in values array it works perfectly but when i move to three it shows an error "INSERT failed: cannot allocate memory for output buffer" Here is my code:

     void InsertBinaryRec(PGconn *conn, double *valueX, char *nameString, double *valueY)
{

        int paramLengths[10];
        int paramFormats[3] = { 1, 0, 1 }; 

        const char* values[3] = {(const char *) valueX, nameString, (const char *) valueY };
        cout << "ya phr gya????" << endl;
        paramLengths[3] = 10 ;
        PGresult *res = PQexecParams(conn,
                           "INSERT INTO testData (X, NAME, Y) VALUES ($1::bytea, $2::TEXT, $3::bytea)",
                           3,           
                           NULL,         
                           values,
                           paramLengths,
                           paramFormats,
                           3);           

        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "INSERT failed: %s\n", PQerrorMessage(conn));
                PQclear(res);
                CloseConn(conn);
        }
        cout << "Insert testData record - OK\n";

  PQclear(res);
}

Are the database fields really bytea ?

Craig Ringer
– Craig Ringer

2017-04-06 11:51:24 +00:00
Commented Apr 6, 2017 at 11:51 — Craig Ringer
– Craig Ringer, Commented Apr 6, 2017 at 11:51
Yes, i have set it bytea

Muhammad Mubeen
– Muhammad Mubeen

2017-04-06 12:17:33 +00:00
Commented Apr 6, 2017 at 12:17 — Muhammad Mubeen
– Muhammad Mubeen, Commented Apr 6, 2017 at 12:17

Laurenz Albe · Accepted Answer · 2017-04-07 03:59:09Z

1

You should initialize paramLengths like this:

int paramLengths[3] = { (int)sizeof(double), 0, (int)sizeof(double) };

It is rather weird to store the binary representation of a double precision value as a bytea field, but if you don't need to process it in the database, why not. That way you won't lose precision.

edited Apr 7, 2017 at 3:59

answered Apr 6, 2017 at 10:47

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

5 Comments

Muhammad Mubeen Over a year ago

I tried this and this was the error which came in front: error: cannot convert ‘<brace-enclosed initializer list>’ to ‘int’ in assignment Second thing, i have to store double as bytea. it is necessary for the system

Muhammad Mubeen Over a year ago

is it possible or is there any way to cast double to char?

Laurenz Albe Over a year ago

You can cast double to char in C, but the result might not make you happy (remove decimal fraction, calculate the remainer for division by 256).

Muhammad Mubeen Over a year ago

Is there any method to store the double* into database with bytea directly without casting it to char*? PQexecparams required it to cast it to the char*. but i don't want to do that. I want to store it directly.

Laurenz Albe Over a year ago

That is direct. All it does is to treat the value as a sequence of bytes rather than a double, which is exactly what you want.

Craig Ringer · Accepted Answer · 2017-04-06 08:06:39Z

0

    paramLengths[3] = 10;

You've assigned an unused entry in paramLengths, and left the two entries for your two binary mode params uninitialized.

You're also assuming that your host's native format for double is the same as PostgreSQL's wire format for binary double. You need to make very sure that's the case, or convert to text-format. If you are going to pass double* values directly in the values array, you should at least set the corresponding lengths fields to sizeof(double).

answered Apr 6, 2017 at 8:06

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

9 Comments

Muhammad Mubeen Over a year ago

I have converted the double * into char* before inserting it. check this const char* values[3] = {(const char *) valueX, nameString, (const char *) valueY }; Then i insert it. In db, the type of these double arrays is bytea

Muhammad Mubeen Over a year ago

Here is my create table code: void CreateTableForBinary(PGconn *conn) { // sql statement execute PGresult *res = PQexec(conn, "CREATE TABLE testData (ID SERIAL PRIMARY KEY, NAME TEXT NOT NULL, X bytea, Y bytea)"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { cout << "Create testData table failed"; PQclear(res); CloseConn(conn); } cout << "Create testData table - OK\n"; // Clear result PQclear(res); }

Craig Ringer Over a year ago

@MuhammadMubeen Please don't post code in comments please, it's generally illegible. And no, your code doesn't convert them to text-representations of floats in char*, you cast them to char*, so it's a mistyped pointer. You're still passing binary double, not character data. This isn't a postgres or libpq issue, it's a C/C++ issue.

Muhammad Mubeen Over a year ago

sorry for posting the code i will not do that again. Second thing, it is mandatory to cast double* to char* before insertion because values[] only accept the char*. Is there any other method to cast them?

Craig Ringer Over a year ago

If you actually intend to pass the byte value of the double, it's fine to cast them. It's only if you thought that would convert them to a text representation that it would be a problem.

|

Collectives™ on Stack Overflow

PQexecparams - Not supporting third parameters in binding array

2 Answers 2

5 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related