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);

For those who are using the version that has no Invocation class: stackoverflow.com/a/66851576/2395663 — Oğuzhan Türk
– Oğuzhan Türk, Commented Mar 29, 2021 at 9:22

harmic · Accepted Answer · 2014-02-05 10:54:36Z

9

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)

answered Feb 5, 2014 at 10:54

harmic

31k6 gold badges75 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alexey Rozum · Accepted Answer · 2018-03-22 09:23:56Z

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

edited Mar 22, 2018 at 9:23

answered Mar 22, 2018 at 8:11

Alexey Rozum

213 bronze badges

1 Comment

juzzlin Over a year ago

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

Collectives™ on Stack Overflow

Inserting NULL/empty string using libpqxx library

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related