user5943954 user5943954 - 8 days ago 6
C++ Question

How do you insert variables into a database table using Postgre SQL via C++?

I have a C++ program that inserts values into a database table. I can't directly hardcode the values in because the data is constantly being updated, but I'm really confused about the syntax.
When I try to do this:

l.exec("INSERT INTO course VALUES(cid, term, 'subj',crse, sec, 'units', 'instructors');");
l.exec("INSERT INTO meeting VALUES(cid, term, 'type', 'days', 'time', 'build', room);");
l.exec("INSERT INTO enrolledin VALUES(cid, term, sid, 'major', 'classlevel', 'level', 'status', seat, numunits, 'grade');");
l.exec("INSERT INTO student VALUES(sid, 'surname', 'prefname', 'email');");

I get this error:
terminate called after throwing an instance of 'pqxx::undefined_column'
what(): ERROR: column "cid" does not exist
LINE 1: INSERT INTO course VALUES(cid, term, 'subj',crse, se...

^
HINT: There is a column named "cid" in table "course", but it cannot be referenced from this part of the query.


--
I was told that it's because I was inserting the literal string name instead of the values inside the string, and I'm confused as to how to insert the values inside the string via C++ while still using variable names.

Answer

Syntax of the used SQL INSERT query is incorrect. It should be:

INSERT INTO course (cid, subj) VALUES(1, 'subj');

You should specify table name together with columns to insert into and values after that. I reduced number of columns for simplicity. For a complete syntax of INSERT query check the PostgreSQL documentation.

To insert values from your variables you can do the following:

int cidValue = 1;
std::string subjValue = "subj";

l.exec("INSERT INTO course (cid, subj) VALUES(" + std::to_string(cidValue) + ", '" +  l.esc(subjValue)  + "')");

esc() function helps to prevent SQL injection attack.