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);
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With