CODEWITHSUNDEEP
c++postgresqllibpq
Mark Davidson
Mark Davidson

Reputation: 5513

PQprepare and PQexecPrepared Usage

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

Upvotes: 3

Views: 8909

Answers (3)

Tadzys
Tadzys

Reputation: 1092

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();

Upvotes: 1

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61656

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.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324841

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.

Upvotes: 2

Related Questions