Incorrect comparison of the column name in the table

[Rofl113]

Good afternoon, everyone!
I'm trying to understand why there is a problem when extracting a parameter by column name.
The code works if the client is SQLite, but does not work if PostgreSQL.
Here is a minimal example (it feels like it's not the whole string being compared, but a pointer to a string).

// drogon::orm::DbClientPtr m_dbBe;
const std::string cmdGetIdUser = "SELECT \"idUser\" FROM \"tokens\" WHERE \"token\" = '" + token + "';";
const auto resultGetIdUser = m_dbBe->execSqlSync(cmdGetIdUser);
// Found
if (resultGetIdUser.size() == 1)
{
    const auto& resultParams = resultGetIdUser.front();
    for (const auto& item : resultParams)
    {
	printf(">>> [%s] = [%s] = %s\n", item.name(), item.c_str(), (std::string(item.name()) == "idUser") ? "YES" : "NO");
    }
    const int idUser = resultParams.at("idUser").as<int>();
    printf("idUser : %d\n", idUser);
}

It turns out this is the conclusion:

>>> [idUser] = [1] = YES
[EE] type: drogon::orm::DrogonDbException; message: "there is no column named idUser"

Maybe I missed something when using the database client?

[Rofl113]

I checked the output for SQLite - I was very surprised. (Although the column in the database is case-sensitive.)

>>> [iduser] = [1] = NO
 idUser : 1

[Rofl113]

Here are the requests via third-party tools:

[Rofl113]

apparently a quick solution is not to use uppercase? =))

[Rofl113]

Yes, I'm sorry, I should have described the problem more precisely. There is a table in PostgreSQL. In the photo, its structure and the command call via the console. Please note - there is a column with the name in uppercase. Below is the test code using the client and the output after launch. I looked at the implementation for PostgreSQL - there is a call to the shared library, most likely the problem is somewhere there.

Code:

// drogon::orm::DbClientPtr m_dbBe;
const std::string cmdGetIdUser = "SELECT \"idUser\" FROM \"tokens\" WHERE \"token\" = '" + token + "';";
const auto resultGetIdUser = m_dbBe->execSqlSync(cmdGetIdUser);
// Found
if (resultGetIdUser.size() == 1)
{
    const auto& resultParams = resultGetIdUser.front();
    for (const auto& item : resultParams)
    {
	printf(">>> [%s] = [%s] = %s\n", item.name(), item.c_str(), (std::string(item.name()) == "idUser") ? "YES" : "NO");
    }
    const int idUser = resultParams.at("idUser").as<int>();
    printf("idUser : %d\n", idUser);
}

Output:

>>> [idUser] = [1] = YES
[EE] type: drogon::orm::DrogonDbException; message: "there is no column named idUser"

[rbugajewski]

I guess the problem is that PostgreSQL columns are only supposed to be case-sensitive when double-quoted.

[Rofl113]

I specified double quotes in the request. Or is it necessary to add quotation marks in the key request too?

[rbugajewski]

For the sake of sanity I suggest to use lowercase & snake case for table & column names with PostgreSQL.

[Rofl113]

Understood, thank you very much for your help!