PQprepare and PQexecPrepared Usage

Question

Hoping someone can help me with the usage of PQprepare and PQexecPrepared. I'm sure I must have something wrong but nothing I try seems to work.

I'm trying to insert into a table using a prepared query but I keep getting this error

ERROR: invalid input syntax for integer: "50.2000008"

This is the value for latitude which I've set the Oid to be 701 (float8) but it says its an integer. Am I totally missing something or have something the wrong way round?

bool database::AddDataRow(int datasetid, string readingdatetime, float depth, float value, float latitude, float longitude) {
    //data_plus

    const char* stmtName = "PREPARE_DATA_PLUS_INSERT";
    Oid oidTypes[6] = {23, 1114, 701, 701, 701, 701};
    int paramFormats[6] = {0, 0, 0, 0, 0, 0};
    PGresult* stmt = PQprepare(
            conn,
            stmtName,
            "INSERT INTO data_plus(datasetid, readingdatetime, depth, value, uploaddatetime, longitude, latitude)"
            "VALUES ($1, $2, $3, $4, NOW(), $5, $6);",
            6,
            (const Oid *) oidTypes
            );

    cout << PQresultErrorMessage(stmt) << " Test";

    const char* paramValues[6];
    int paramLengths[6];

    paramValues[0] = lexical_cast<string>(datasetid).c_str();
    paramValues[1] = readingdatetime.c_str();
    paramValues[2] = lexical_cast<string>(depth).c_str();
    paramValues[3] = lexical_cast<string>(value).c_str();
    paramValues[4] = lexical_cast<string>(longitude).c_str();
    paramValues[5] = lexical_cast<string>(latitude).c_str();

    paramLengths[0] = strlen (paramValues[0]);
    paramLengths[1] = strlen (paramValues[1]);
    paramLengths[2] = strlen (paramValues[2]);
    paramLengths[3] = strlen (paramValues[3]);
    paramLengths[4] = strlen (paramValues[4]);
    paramLengths[5] = strlen (paramValues[5]);

    PGresult* test = PQexecPrepared(conn,
            stmtName,
            6,
            paramValues,
            paramLengths, 
            paramFormats,
            0);

    cout << PQresultErrorMessage(test);

    PQclear(test);
    PQclear(stmt);
}

\d data_plus

                  View "public.data_plus"
     Column      |            Type             | Modifiers 
-----------------+-----------------------------+-----------
 id              | bigint                      | 
 datasetid       | integer                     | 
 readingdatetime | timestamp without time zone | 
 depth           | double precision            | 
 value           | double precision            | 
 uploaddatetime  | timestamp without time zone | 
 longitude       | double precision            | 
 latitude        | double precision            |

Thanks,

Mark

Using `libpqtypes` will save you a fair bit of hassle. Are you *sure* that 701 is the correct oid for `float8` on your DB (it should be, looks like it has been 701 for a long time, but check anyway)? You shouldn't hardcode oids; the team makes an effort to keep them stable, but you really should look them up from `pg_type` during program startup. — Craig Ringer, Jun 04 '13 at 11:46
Might be handy if you make this a self-contained compileable example, too. — Craig Ringer, Jun 04 '13 at 11:55
Have added the output of `\d data_plus`. I did check my db its definitely 701 for float8 will check out `libpqtypes` thanks for the advise. — Mark Davidson, Jun 04 '13 at 12:14
How you solved the problem ? I am also facing the same problem. — Amit, Nov 07 '13 at 11:48

score 2 · Answer 1 · answered Jun 04 '13 at 11:58

2

Try passing a null value for oidTypes and let the server infer the data types.

The manual says:

If paramTypes is NULL, or any particular element in the array is zero, the server assigns a data type to the parameter symbol in the same way it would do for an untyped literal string.

... and there shouldn't be any issues inferring any of these types so long as the table is defined suitably.

You can also pass NULL for paramFormats since the default is to assume that all params are text not binary.

paramLengths is not useful or required when you're using text format parameters. Leave it as null. This could actually be the cause of the problem.

answered Jun 04 '13 at 11:58

Craig Ringer

307,061
76
688
778

Thanks for the suggestion have tried setting them all to `NULL` still results in the same error occurring. – Mark Davidson Jun 04 '13 at 12:16
@MarkDavidson Odd. Self-contained compileable example + sql to define the tables? Hard to proceed further without given that the result doesn't make much sense to me. Unless I'm just missing something obvious. – Craig Ringer Jun 04 '13 at 12:17

score 1 · Answer 2 · answered Feb 03 '17 at 09:44

This is quite an old thread, but I will still answer, as others might be looking at it. Everything here seems to be fine it terms of libpq calls, but the problem is with the following code:

const char* paramValues[6];
int paramLengths[6];

paramValues[0] = lexical_cast<string>(datasetid).c_str();

lexical_cast returns a temporary std::string and you are saving a pointer to string which will be/is destructed. Need to save those strings somewhere or memcpy data to paramValues (and later delete it). Something like this:

std::array<std::string, 6> temp_params;
temp_params[0] = lexical_cast<string>(datasetid);
paramValues[0] = temp_params[0].c_str();

score 0 · Answer 3 · answered Jun 04 '13 at 15:18

It is likely that the reason of the error is not at all in the code shown, which by itself looks OK.

data_plus is not a table, it's a view according to the first line output by \d data_plus. So there's probably a RULE or an INSTEAD OF trigger which does the actual insertion. Plus there is an id column that is not filled in by your code, so it's done somewhere else too.

You may want to focus on that code and check for any confusion between columns and values transfered into them.

PQprepare and PQexecPrepared Usage

3 Answers3