4

I'm trying to perform a select operation on postgres DB table and I'm getting below error:

[etb@centos etbtest]$ ./a.out

Opened database successfully: ETBDB
ERROR:  relation "etb_reference.etb_member" does not exist
LINE 1: SELECT * FROM etb_reference.ETB_MEMBER

how do we refer the schema name in libpq++ exec function?

I tried escaping the schema name with other escape options like ",',\ etc but it didn't help.

my code :

   try {
      connection C("dbname = ETBDB user = etbadm password = etbtest \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

       work wrk(C);

   result res = wrk.exec("SELECT * FROM etb_reference.ETB_MEMBER");

   for (
      pqxx::result::const_iterator row = res.begin();
      row != res.end();
      ++row)
    {

     std::cout
        << row["MEMBER_ID"].as<int>() << "\t"
        << row["SYS_CRE_DATE"].as<std::string>() << "\t"
        << row["SYS_UPD_DATE"].as<std::string>() << "\t"
        << row["MEMBER_CS"].as<std::string>() << "\t"
        << row["MEMBER_TD"].as<std::string>() << "\t"
        << row["MEMBER_ASD"].as<std::string>() << "\t"
        << row["MEMBER_ITM"].as<std::string>() << "\t"
        << std::endl;

    }
C.disconnect ();
 return 0;

   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }
}

I can execute set search_path to 'schema' but I have divided by database into schemas like application tables, reference tables, configuration tables for easy management of porting and maintaining via automated shell and db scripts.

JGH
  • 15,928
  • 4
  • 31
  • 48
ramesh.metta
  • 139
  • 1
  • 1
  • 11
  • What is 'etb_reference.ETB_MEMBER'? It should be the name of a table in the ETBDB database. – myk Mar 29 '19 at 23:45
  • Hi Thank you for reply. etb_reference is the schema name and ETB_MEMBER is the table under the schema. Also, i have granted full permissions to the schema and table for the user i'm connected through c++ code. when i run this query in psql : SELECT * FROM etb_reference."ETB_MEMBER" , I'm getting the result for same user. – ramesh.metta Mar 29 '19 at 23:46
  • Grasping at straws so some silly suggestions: double check your spelling; add a semi-colon ; at end of the SQL statement thus: "SELECT * FROM etb_reference.ETB_MEMBER;"; try the SQL statement from pgAdmin or the command line; set a search path to the schema via PQexec() – myk Mar 29 '19 at 23:59

1 Answers1

3

Opened database successfully: ETBDB ERROR: relation "etb_reference.etb_member" does not exist LINE 1: SELECT * FROM etb_reference.ETB_MEMBER

The error message contains the table name in lower cases. It also prints the line where the error occurs, and this line contains the table name in upper case.

SELECT * FROM etb_reference."ETB_MEMBER"

When you query by hand, you use upper case and put the table name between double quotes.

PostgreSQL works with lower case by default, unless the name is between double quotes, so to fix the issue, simply put the table name between double quotes in the query (or, better, only use lower case names)

JGH
  • 15,928
  • 4
  • 31
  • 48