How can I bind NULL value in libpq with C++?

Question

I can't bind NULL value for a numeric type in C++ with libpq library. I use this description How to prepare statements and bind parameters in Postgresql for C++ and it worked fine.

But I couldn't find out how to bind a NULL value for a numeric type.

 pqxx::prepare::invocation& prep_dynamic(std::vector<std::string>& data, pqxx::prepare::invocation& inv) 
{ 
    std::vector<std::string>::const_iterator it; 
    for(it=data.begin();it!=data.end();it++) 
    { 
        inv((*it)); 
    } 
    return inv; 
}





try 
     {
        pqxx::connection C("dbname = postgres user = postgres password=postgres hostaddr = 127.0.0.1 port = 5432");
        if (C.is_open()) 
        {
            std::string dbname = C.dbname();
            DIAG(DETAILED_INFO, "Opened database successfully:[%s]", dbname.c_str());           
        } 
        else 
        {
           DIAG(DETAILED_INFO, "Can't open database!");       
        }

        work W(C);

        C.prepare("example", "insert into ipls_troubleticketerrorreport (CUSTOMER, NETWORK,SELECTABLEMONTH, ERRORCODE,ERRORMESSAGE, TTID ) " \
                             "values ($1, $2, to_date($3,'YYYY-MM-DD'), $4, $5, $6);"); 


        pqxx::prepare::invocation w_invocation = W.prepared("example");


        std::vector< std::pair<std::string,std::string> > sqlParameters2;
        sqlParameters2.push_back(std::pair<std::string, std::string>("CUSTOMER", ""));
        sqlParameters2.push_back(std::pair<std::string, std::string>("NETWORK", ""));
        sqlParameters2.push_back(std::pair<std::string, std::string>("SELECTABLEMONTH", ""));
        sqlParameters2.push_back(std::pair<std::string, std::string>("ERRORCODE",""));
        sqlParameters2.push_back(std::pair<std::string, std::string>("ERRORMESSAGE", ""));
        sqlParameters2.push_back(std::pair<std::string, std::string>("TTID",""));


       for (int i = 0; i < sqlParameters2.size(); i++)
       {
            std::string value = "";                   
            value = i_get(sqlParameters2[i].first.c_str());            
            sqlParameters2[i].second = value;                         
       }

       std::vector<std::string > vect;

       for(int i=0; i<sqlParameters2.size();i++)
       {
           vect.push_back(sqlParameters2[i].second);

       }           

       prep_dynamic(vect, w_invocation);
       pqxx::result r = w_invocation.exec();
       W.commit();
      C.disconnect ();
     }
    catch(const std::exception &e)
    {
       DIAG(DETAILED_INFO, "exception occured.[%s]",e.what());   
       throw e;


    }

Ordonnance

What's your code if you tried to do so? Don't need NULL values need to be checked with IS NULL and not the = operator? – Indoor

Some helpful link: #24933753 – Indoor

This answer should help you: https://mcmap.net/q/1935140/-inserting-null-empty-string-using-libpqxx-library – Idiomorphic

Thank you for your answer but how could I solve it when I use prep_dyamic pqxx::prepare::invocation& prep_dynamic(std::vector<std::string>& data, pqxx::prepare::invocation& inv) { std::vector<std::string>::const_iterator it; for(it=data.begin();it!=data.end();it++) { inv((*it)); } return inv; } – Ordonnance

Answer 1

Change the type of vect as std::vector<std::optional<std::string>> vect; And set std::nullopt to elements that you want to assign null. That's it.

Touchy