3

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;


    }
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Tomtom
  • 45
  • 7
  • 3
    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? – πάντα ῥεῖ Apr 30 '19 at 09:19
  • Some helpful link: https://stackoverflow.com/questions/24933253/preparedstatement-is-null-in-where-clause-without-if-conditional-dynamic-quer – πάντα ῥεῖ Apr 30 '19 at 09:36
  • This answer should help you: https://stackoverflow.com/a/21575201/1734130 – mvp Apr 30 '19 at 09:53
  • Thank you for your answer but how could I solve it when I use prep_dyamic pqxx::prepare::invocation& prep_dynamic(std::vector& data, pqxx::prepare::invocation& inv) { std::vector::const_iterator it; for(it=data.begin();it!=data.end();it++) { inv((*it)); } return inv; } – Tomtom Apr 30 '19 at 10:04

1 Answers1

0

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.

Oğuzhan Türk
  • 360
  • 2
  • 9
  • 21