Postgresql Return no value

John Hon

Hi all,

I have a problem to query data from my Postgresql database. My table, expenses_type, has following columns:

expense_type char varying
expense_id integer

CREATE OR REPLACE FUNCTION get_expense_type_id(expense_value varchar)
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
declare
	max_id integer;
BEGIN
   select expense_id into max_id from public.expenses_type where expense_type = expense_value;
   return max_id;
END
$func$;

When I ran the following statement on pgAdmin 4,


select get_expense_type_id('add')

it returns value, 19. This is my expected result.

On my Qt c++ source code, I have:

bool FINANCIAL_DATABASE::insert_Expense_Type(const QString& expense_type, int& expense_id)
{
 ....

    QSqlQuery query(m_db);
    QString expense_type1 = "add";
    query.prepare("select get_expense_type_id(:expense_type)");
    query.bindValue(":expense_type", expense_type1);
    query.exec();
    expense_id = query.value(0).toInt();
    return true;
}

query.value(0).toInt() return value, 0. The query.value(0).toInt() does not return 19 as my expected result. I cannot figure out what I did wrong here. Fyi, before I modified the above code, I implemented insert record in the same member function, the record was inserted successfully (I did not insert the code here) . Therefore, I believe the QSqlDatabase setting is correct.

John Hon

@Paul-Colby said in Postgresql Return no value:

Hi @John-Hon, not sure what the issue is, but all of those QSqlQuery functions return booleans, and (presumably) set relevant SQL errors on failure, so try something like:
if (!query.prepare("select get_expense_type_id(:expense_type)")) {
    qDebug() << "prepare failed:" << query.lastError().text();
    return false;
}
if (!query.bindValue(":expense_type", expense_type1)) {
    qDebug() << "bind failed:" << query.lastError().text();
    return false;
}
if (!query.exec()) {
    qDebug() << "exec failed:" << query.lastError().text();
    return false;
}
Cheers.

Fyi, the query.bindValue has no return value. I tried, I changed, query.prepare as :

if (!query.prepare("select get get_expense_type_id(:expense_type)")) {
qDebug() << "prepare failed:" << query.lastError().text();
    return false;
}

or

if (!query.exec("select expense_id  from public.expenses_type where expense_type = 'add'))
{
         qDebug() << "prepare failed:" << query.lastError().text();
        return false;
}

the above 2 codes, it does not report any errors. I ran the

select expense_id  from public.expenses_type where expense_type = 'add'

on pgadmin 4, it returns 19 but ran on the debugger mode, it returned 0. is there anything that I can miss out or should try?

Paul Colby

Hi @John-Hon, not sure what the issue is, but all of those QSqlQuery functions return booleans, and (presumably) set relevant SQL errors on failure, so try something like:

if (!query.prepare("select get_expense_type_id(:expense_type)")) {
    qDebug() << "prepare failed:" << query.lastError().text();
    return false;
}
if (!query.bindValue(":expense_type", expense_type1)) {
    qDebug() << "bind failed:" << query.lastError().text();
    return false;
}
if (!query.exec()) {
    qDebug() << "exec failed:" << query.lastError().text();
    return false;
}

Cheers.

John Hon

@Paul-Colby said in Postgresql Return no value:

Hi @John-Hon, not sure what the issue is, but all of those QSqlQuery functions return booleans, and (presumably) set relevant SQL errors on failure, so try something like:
if (!query.prepare("select get_expense_type_id(:expense_type)")) {
    qDebug() << "prepare failed:" << query.lastError().text();
    return false;
}
if (!query.bindValue(":expense_type", expense_type1)) {
    qDebug() << "bind failed:" << query.lastError().text();
    return false;
}
if (!query.exec()) {
    qDebug() << "exec failed:" << query.lastError().text();
    return false;
}
Cheers.

Fyi, the query.bindValue has no return value. I tried, I changed, query.prepare as :

if (!query.prepare("select get get_expense_type_id(:expense_type)")) {
qDebug() << "prepare failed:" << query.lastError().text();
    return false;
}

or

if (!query.exec("select expense_id  from public.expenses_type where expense_type = 'add'))
{
         qDebug() << "prepare failed:" << query.lastError().text();
        return false;
}

the above 2 codes, it does not report any errors. I ran the

select expense_id  from public.expenses_type where expense_type = 'add'

on pgadmin 4, it returns 19 but ran on the debugger mode, it returned 0. is there anything that I can miss out or should try?

Christian Ehrlicher

@John-Hon said in Postgresql Return no value:

that I can miss

You simply did not read the documentation on how it works. QSqlQuery::exec() executes the query, the data is fetched with QSqlQuery::next().