[Libpqxx-general] insert using tablewriter

[Jeroen T. Vermeulen]

On Thu, October 1, 2009 01:11, tans at email.arizona.edu wrote:
> I am new to libpqxx and PostgreSQL.  I have the following pseudo code
> function:
>
> bool insertEmployee(Connection &con, Employee &obj)
> {
>   const char* stmt = "INSERT INTO employee (id, name, salary) VALUES (%d,
> '%s',
> %.10f)";
>
>   if(!con.execute(stmt, obj.getId(), obj.getName(), obj.getSalary()))
>      return false;
>   return true;
> }
>
> What is a good way to implement the above code using libpqxx?

This looks like a use-case for a prepared statement:

  con.prepare("make_employee", "INSERT INTO [etc.]")
    ("integer")
    ("string", pqxx::prepare::treat_string)
    ("money");

...

  txn.prepared("make_employee")
    (obj.getId())
    (obj.getName())
    (obj.getSalary()).exec();


If that's too complicated, you can also just compose your string:

  txn.exec("INSERT INTO [etc.] VALUES (" + to_string(obj.getId()) + ", "
      txn.quote(obj.getName()) + ", " + to_string(obj.getSalary()) + ")");


> Looking through the libpqxx api, I think the following would work:
>
> bool insertEmployee(pqxx::connection &con, Employee &obj)
> {
>   const char *const CData[][2] =
>   {
>      {"id", obj.getId()},  //integer
>      {"name", obj.getName()},  //string
>      {"salary", obj.getSalary()},  //float
>      {0,0}
>   };

Don't use tablewriter unless you have a real performance problem to solve.
 For "a few" employees (and that could easily be thousands), regular
inserts are likely to be fast enough.


>   catch (const exception &e) {
>     std::cerr << "Exception: " << e.what() << std::endl;
>     return false;
>   }
>
>   return true;
> }

Returning booleans for this is basically C practice.  You generally don't
want to do it this way; let the exceptions propagate all the way up to
where you can really handle them.


Jeroen