Insert Binary Float using PQexecParams

Question

May be I done a mistake, how can i please direct insert binary float in my table. I don't know if i done the things right. The code works but the values are not correct Thank you for reply

static void to_nbo(double in, double *out) {
    uint64_t *i = (uint64_t *)&in;
    uint32_t *r = (uint32_t *)out;

    /* convert input to network byte order */
    r[0] = htonl((uint32_t)((*i) >> 32));
    r[1] = htonl((uint32_t)*i);
}
 const char command[] = "INSERT INTO car1 (id, price, sale)"
                         "VALUES($1::integer, $2::real, $3::real)";

  int nParams = 3;
  //const char *const paramValues[] = {"2.545","44.655"};

  const Oid paramTypes[] = {23, 700, 700};
  const int paramLengths[] = {sizeof(int), sizeof(int), sizeof(int)};
  const int paramFormats[] = {1, 1, 1};
  int resultFormat = 0;

  int test = htonl(45);
  double test2;
  double test3;
  to_nbo(75.585, &test2);
  to_nbo(8.4785, &test3);
  //char paramFloat[] ={0x41,0x40,0x00,0x00}; //12.0
  //char paramFloat1[] ={0x42,0x32,(char) 0x9e, (char) 0xb8}; //44.655
  //char paramFloat[] ={0x00,0x00, 0x40,0x41}; //12.0
  //char paramFloat1[] ={(char) 0xb8,(char) 0x9e,0x32,0x42}; //44.655
  //const char *const paramValues2[] = {paramFloat, paramFloat1};

 const char *const paramValues2[] = {(const char*)&test, (const char*)&test2, (const char*)&test3};

  /* PQexecParams INSERT */
  res = PQexecParams(conn, command, nParams, paramTypes, paramValues2, paramLengths, paramFormats, resultFormat);
  if (PQresultStatus(res) != PGRES_COMMAND_OK) {
      std::cout << "PQexecParams failed: " << PQresultErrorMessage(res)
                << std::endl;
  }

Note: it is good etiquette to link to your previous question, even if it has no accepted answer, or edit it. — Botje, Jul 15 '20 at 07:33

Botje · Answer 1 · 2020-07-15T08:02:27.813

0

You are not giving the PQExecParams function any information about the type of your binary data. It is trying to parse that binary junk as a string containing a floating point number.

Edit your SQL query like so:

const char command[] = "INSERT INTO car1 (id, price, sale)"
                         "VALUES($1::integer, $2::real, $3::real)";

this gives Postgres a hint what types you will be inserting.

Alternatively, fill in the paramTypes[] array; it has the following documentation:

Specifies, by OID, the data types to be assigned to the parameter symbols. If paramTypes is NULL, or any particular element in the array is zero, the server infers a data type for the parameter symbol in the same way it would do for an untyped literal string.

Even then, you may need to do byteswapping as per this answer

EDIT: looking through the public postgres.h header, I think you need to pass the output of Float4GetDatum to PQExecParams.

edited Jul 15 '20 at 08:02

answered Jul 15 '20 at 07:36

Botje

26,269
3
31
41

In my case which kind of values can I pass to ParamTypes[ ] please? – Derrick Jul 15 '20 at 08:54
OIDs. See [this answer](https://stackoverflow.com/a/12531823/1548468) – Botje Jul 15 '20 at 09:06
I have modified the code, but the values in the table are still not correct. I don't know what is the mistake. Can you help please? – Derrick Jul 15 '20 at 10:58
I cannot help unless you provide more information. How did you modify the code? What is "not correct"? – Botje Jul 15 '20 at 11:00
I edited the SQL query and then i filled the paramType[ ] array. – Derrick Jul 15 '20 at 11:07
I made a bytesswapping too, but the values in the Table are not the same with those in the query – Derrick Jul 15 '20 at 11:09
Were you always passing `doubles` in? Because those are [`double`](https://www.postgresql.org/docs/9.5/datatype-numeric.html#DATATYPE-FLOAT) in PostgreSQL, while `real` is for `float`s. If that still fails, edit your question with inputs *AND* outputs. Also note that `sizeof(int) != sizeof(double)` – Botje Jul 15 '20 at 11:09
So change the types in the query _and_ in `paramLengths`. – Botje Jul 15 '20 at 11:14
I have done exactly what you're described... It works now! Thanks – Derrick Jul 15 '20 at 11:36

Insert Binary Float using PQexecParams

1 Answers1