使用libpqxx库插入NULL/空字符串 [英] Inserting NULL/empty string using libpqxx library

问题描述

在以下代码段中，名称为mac的std :: string对象有时为空字符串(即)，并且我希望Prepared语句自动将此变量视为null.我想知道如何在下面的代码中实现这一点.在我的谷歌搜索尝试中，我偶然发现有一种方法可以设置一个指示空值的标志，但是我找不到具体的示例.您能否提供一个示例来实现这一目标?谢谢.

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
  .....
}

Insert语句如下:

The Insert statement is as follows:

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

表结构如下:

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

推荐答案

使用libpqxx，您可以通过在不带参数的已准备好的语句上调用operator()来发送空值，例如:

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

将发送NULL作为该语句的第一个参数.

would send NULL as the first parameter for the statement.

我认为您无法自动将空字符串替换为NULL.实现此目的的一种方法是修改您正在使用的SQL:

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)

这篇关于使用libpqxx库插入NULL/空字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！