Inserting NULL/empty string using libpqxx library

c++, libpqxx, postgresql

Solution

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)

Problem

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

Original source