PQexecParams in C++, query error

Question

I'm using pqlib with postgresql version 9.1.11

I have the following code

const char *spid = std::to_string(pid).c_str();
PGresult   *res;
const char *paramValues[2] = {u->getID().c_str(), spid};
std::string table;
table = table.append("public.\"").append(Constants::USER_PATTERNS_TABLE).append("\"");
std::string param_name_pid = Constants::RELATION_TABLE_PATTERN_ID;
std::string param_name_uid = Constants::RELATION_TABLE_USER_ID;
std::string command = Constants::INSERT_COMMAND + table + " (" + param_name_uid + ", " + param_name_pid + ")  VALUES ($1, $2::int)";
std::cout << "command: " << command << std::endl;
res = PQexecParams(conn, command.c_str(), 2, NULL, paramValues, NULL, NULL,0);

Where

• INSERT_COMMAND = "INSERT INTO " (string)

• USER_PATTERN_TABLE = "User_Patterns" (string)

• RELATION_TABLE_PATTERN_ID = "pattern_id" (string)

• RELATION_TABLE_USER_ID = "user_id" (string)

• pid = an int

• u->getID() = a string

• conn = the connection to the db

The table "User_Patterns" is defined as

CREATE TABLE "User_Patterns"(
    user_id    TEXT references public."User" (id) ON UPDATE CASCADE ON DELETE CASCADE
    ,pattern_id BIGSERIAL references public."Pattern" (id) ON UPDATE CASCADE
    ,CONSTRAINT user_patterns_pkey PRIMARY KEY (user_id,pattern_id)  -- explicit pk
)WITH (
    OIDS=FALSE
);

I already have a user and a pattern loaded into their respective tables.

The command generated is :

INSERT INTO public."User_Patterns" (user_id, pattern_id)  VALUES ($1, $2::int)

I also tried with $2, $2::bigint, $2::int4

The problem is:

I receive the error :

ERROR:  invalid input syntax for integer: "public.""

I already use PQexecParams to store users and patterns, the only difference is that they all have text/xml fields (the only int field on patterns is a serial one and I don't store that value myself) but because the user_patterns is a relation table I need to store and int for the pattern_id.

I already read the docs for pqlib and saw the examples, both are useless.

Answer 1

The problem is in the lines:

const char *spid = std::to_string(pid).c_str();
const char *paramValues[2] = {u->getID().c_str(), spid};

std::to_string(pid) creates temporary string and .c_str() returns a pointer to an internal representation of this string, which is destroyed at the end of the line, resulting in a dead pointer. You may also see answer to the question stringstream::str copy lifetime