C++ postgis prepared statement failing, invalid geometry

Question

I'm using libpqxx and a prepared statements, when I try to insert programmatically it fails with an error "Invalid geometry", but I can do the same insert via sql prompt.. Am I missing something? I tried to escape the ' in the prepare statement call but same error

con.prepare("chat_insert", "INSERT INTO chat values (nextval('chat_seq'), $1, ST_GeomFromText('POINT($2 $3)', 4326), $4)");
worker.prepared("chat_insert")(chatid)(lon)(lat)(msg).exec();
worker.commit();

I also tried

con.prepare("chat_insert", "INSERT INTO chat values (nextval('chat_seq'), $1, ST_GeomFromText(\'POINT($2 $3)\', 4326), $4)");

Output:

Chat id: chat:user:128946234
Lat: 14.6049
Lon: 121.033
ERROR:  parse error - invalid geometry
HINT:  "POINT(" <-- parse error at position 6 within geometry

If I go into the sql prompt I can run this and it'll insert

insert into chat values (nextval('chat_seq'), 'chat:user:128946234', ST_GeomFromText('POINT(121.033 14.6049)', 4326), 'This is a test msg....');

Answer 1

You are attempting to parse a WKT string of POINT($2 $3) which has dollar signs in it. These are not parameters in this context, since WKT is a string.

Use a function that accepts numeric parameters, such as ST_MakePoint(x, y):

con.prepare("chat_insert", "INSERT INTO chat (chatid, geom, msg) "
            "VALUES ($1, ST_SetSRID(ST_MakePoint($2, $3), 4326), $4)");

Note that I've listed the columns to insert after chat, which is considered a best practice.