Inserting NULL/empty string using libpqxx library

Question

In the following code snippet, the std::string object with name mac is sometimes an empty string (i.e. "") and I want the prepared statement to treat this variable automatically as null. I wonder how this can be achieved in the below code. In my googling attempts, I happened to find that there is a way to set a flag indicating null value but I could not find a concrete example. Could you please provide an example to achieve this? Thnx.

try
{
  mConnection->prepare("insertBulkData", mSqlInsertStmt);
  pqxx::work xAction(*mConnection); 

  for(uint32_t i = 0; i < tList.size(); i++)
  {
    TCoreDTO* tCore = tList[i];              
    const std::string& mac   = tCore->getMac();
    const std::string& uuid  = tCore->getUUID();
    int coreNo = (int)tCore->getCoreNo();
    xAction.prepared("insertBulkData")(mac)(uuid)(coreNo).exec();
  }
  xAction.commit();
}
catch(std::exception& pqExp)
{
  //Error handling code
  .....
}

The Insert statement is as follows:

std::string mSqlInsertStmt 
= "INSERT INTO T_CORES (MAC, UUID, CORE_NO) VALUES ($1, $2, $3)";

Table structure is as follows:

CREATE TABLE IF NOT EXISTS T_CORES (
ID     SERIAL PRIMARY KEY,                            
MAC    TEXT, 
UUID   TEXT, 
CORE_NO INT DEFAULT 0);  

Fabulist

For those who are using the version that has no Invocation class: https://mcmap.net/q/2029823/-how-can-i-bind-null-value-in-libpq-with-c – Addie

Answer 1

With libpqxx you can send a null value by calling operator () on a prepared statement with no arguments, eg:

xAction.prepared("insertBulkData")()(uuid)(coreNo).exec();

would send NULL as the first parameter for the statement.

I don't think you can get it to automatically replace an empty string with NULL. One way to achieve this would be to modify the SQL you are using:

INSERT INTO T_CORES (MAC, UUID, CORE_NO) VALUES (CASE WHEN $1='' THEN NULL ELSE $1 END, $2, $3)

Evolution

Answer 2

Fast decision... add a parameter that will indicate when to insert into the NULL field

xAction.prepared("insertBulkData")(mac,!mac.empty())(uuid)(coreNo).exec();

link to doc

Woodward

Hmm...seems that this should be the accepted answer as of year 2020. – Runagate