can not getting sql result.

[rahulvishwakarma]

hi to all, I've centos 7 and Qt 5.7 in VM. Here I want to access mysql database and loading data into form. Problem is that sql query returns 0 for both of columns ( Stock and Rate ). in mysql database

mine code is :-```
code_text

void Sales::on_lineEditQuantity_editingFinished()
{
    quantity = ui->lineEditQuantity->text().trimmed().toInt();
    int prdnumber = ui->lineEditProductNumber->text().trimmed().toInt();

    QString sql = "select Stock, Rate from tableProductRecords where ProductID = :id";

    query = new QSqlQuery();
    query->prepare(sql);
    query->bindValue(":id", prdnumber);
    qDebug() << "prdnumber = " << prdnumber;

    int *no = new int;
    Sales::maxno = setmaxno(no);
    if(query->exec())
    {
        if(query->next())
        {
            int st =  query->value(0).toString().trimmed().toInt();
            
	    qDebug() << "st = " << st << "  " << query->value(0).toString().trimmed().toInt();
            ui->lineEditRate->setText(query->value(1).toString().trimmed());
            qDebug() << "rate = "<< query->value(1).toString().trimmed().toInt();

            ui->lineEditBillNo->setText(QString::number(Sales::maxno));
            ui->lineEditTotal->setText(QString::number((query->value(1).toInt()) * quantity));

            int stk = st - quantity;//ui->lineEditQuantity->text().trimmed().toInt();
            if (stk < 0)
            {
                ui->lineEditRate->setText("");
                ui->lineEditBillNo->setText("");
                ui->lineEditTotal->setText("");
                QMessageBox::critical(this, "Sales", "Not enough stock\n available only " + QString::number(st));
                return;
            }
            else
            {
                ui->lineEditStock->setText(QString::number(stk));

                sql = "update tableProductRecords set stock = "+
		       QString::number(stk) + " "+ "where productID = "+ 
		       ui->lineEditProductNumber->text().trimmed()+";";
                query->prepare(sql);
                if(query->exec())
                {
                    QMessageBox::information(this, "Salas", "record updated");
                }
            }
        }
        else
        {
            QMessageBox::critical(this, "on_lineEditQuantity_editingFinished","Error : " +  query->lastError().text());
        }
    }
    else
    {
        QMessageBox::critical(this, "on_lineEditQuantity_editingFinished","Error : " +  query->lastError().text());
    }
}

Output is :-

prdnumber =  1
st =  0    0
QMYSQLResult::data: column 1 out of range
QMYSQLResult::data: column 1 out of range
rate =  0  
QMYSQLResult::data: column 1 out of range

MYSQLResult::data: column 1 out of range

problem is that via sql i am not getting stock and rate values

[Christian Ehrlicher]

Are you sure both columns exists (with the correct lower/uppercase spelling)
You can inspect the returned values by calling QSqlQuery::record() and print out what columns etc. were returned.

btw: your code leaks the query (no need to create it on the heap at all) and the string pointer to an integer ('no'). Also you're inconsistent in your usage of the returned values from the query ( query->value(1).toString().trimmed().toInt() <-> query->value(1).toInt()) which makes all this hard to read

[Pablo J. Rogina]

@rahulvishwakarma in addition to @Christian-Ehrlicher suggestions, are you able to run that same query from the same place where you run your Qt app and getting useful results?

[rahulvishwakarma]

@Pablo-J-Rogina yes i can get useful result when I fired query from terminal.

[hskoglund]

Hi. maybe also check the result of the prepare() function, say like this:

 qDebug() << query->prepare(sql);

does it return true or false?

[Christian Ehrlicher]

@rahulvishwakarma So did you actually tried out what I suggested?

[rahulvishwakarma]

i tried like this :-

void Sales::on_lineEditQuantity_editingFinished()
{
    int *no = new int;
    Sales::maxno = setmaxno(no);

    quantity = ui->lineEditQuantity->text().trimmed().toInt();
    int prdnumber = ui->lineEditProductNumber->text().trimmed().toInt();

    QString sql = "select Stock, Rate from tableProductRecords where ProductID = :id";

	qDebug() << query->prepare(sql); // prints true
    query->bindValue(":id", prdnumber);
    qDebug() << "prdnumber = " << prdnumber;

    if(query->exec())
    {
        if(query->next())
        {

            if(query->isActive())
            {
                QMessageBox::information(this, "on_lineEditQuantity_editingFinished", "query is active");
                QSqlRecord rd = query->record();
                int numcoll = rd.indexOf("Stock");

                QSqlQuery * sq = new QSqlQuery(*query);
                if(sq->isValid())
                {
                    qDebug() << "sq isvalid";

                    qDebug() << "rd.fieldName(1) " << rd.fieldName(numcoll);
                }
                else
                {
                    qDebug() << "sq is not valid";
                }

                while (sq->next())
                {
                    qDebug() << " sq " << sq->value(numcoll).toString().trimmed();
                }
            }


            int st =  query->value(0).toInt();
            qDebug() << "st = " << st << "  " << query->value(0).toInt();

            ui->lineEditRate->setText(query->value(1).toString().trimmed());
            qDebug() << "rate = "<< query->value(1).toInt();

            ui->lineEditBillNo->setText(QString::number(Sales::maxno));
            ui->lineEditTotal->setText(QString::number((query->value(1).toInt()) * quantity));// (ui->lineEditQuantity->text().trimmed().toInt())));

            QMessageBox::information(this, "on_lineEditQuantity_editingFinished", "st = " + QString::number(st));

            int stk = st - quantity;
            if (stk < 0)
            {
                ui->lineEditRate->setText("");
                ui->lineEditBillNo->setText("");
                ui->lineEditTotal->setText("");
                QMessageBox::critical(this, "Sales", "Not enough stock\n available only " + QString::number(st));
                return;
            }
            else
            {
                ui->lineEditStock->setText(QString::number(stk));

                sql = "update tableProductRecords set stock = "+ QString::number(stk) + " "+ "where productID = "+ ui->lineEditProductNumber->text().trimmed()+";";
                query->prepare(sql);
                if(query->exec())
                {
                    QMessageBox::information(this, "Sales", "record updated");
                }
            }
        }
        else
        {
            QMessageBox::critical(this, "on_lineEditQuantity_editingFinished","Error : " +  query->lastError().text());
        }
    }
    else
    {
        QMessageBox::critical(this, "on_lineEditQuantity_editingFinished","Error : " +  query->lastError().text());
    }
}

o/p :-
true
prdnumber =  1
sq isvalid
rd.fieldName(1)  "Stock"
QSqlQuery::value: not positioned on a valid record
QSqlQuery::value: not positioned on a valid record
st =  0    0
QSqlQuery::value: not positioned on a valid record
QSqlQuery::value: not positioned on a valid record
rate =  0
QSqlQuery::value: not positioned on a valid record

and also qDebug() << query->prepare(sql); is true

[Christian Ehrlicher]

Sorry but why are you now creating a new query inside the if statements??
You should simply inspect QSqlRecord what it contains and print it out to see if it's what you expect...

[rahulvishwakarma]

i got its solution : "query->seek(0)" added before int st = query->value(0).toInt();
and it happened