How to delete the first row of sqlite table at every call?

gfxx

My intention is grab the max number of row in a table, check if these number is major of 50 (for exe.) than delete permanetely the first row of a table ..... these every time that the system call these void ...

void MainWindow::CancelRowNumberSystem()
{
    int rows211 = 0;
    QSqlQuery q211(db);
    q211.clear();
    q211.prepare("SELECT COUNT(*) FROM errori");
    if (q211.next()) {
        rows211 = q211.value(0).toInt();
    }

    qDebug() << "il totale righe è:   " << rows211;

    if(rows211 >= 10)
    {
        QSqlQuery q212(db);
        q212.clear();
        q212.prepare("DELETE FROM errori WHERE id_errore = :id_errore");
        q212.addBindValue(1);
        q212.exec();
        q212.next();
    }
}

no error checked but not work and rows211 return everytime "0" value instead the real one.

not see my error ....

regards
giorgio

mrjj

Hi
you do not execute the Query

int numberOfPages=0;
query.prepare("SELECT COUNT( * )  FROM errori");  
if ( query.exec()  )  {
     numberOfPages = query.value(0).toInt();
  } else { error }

Not sure if (query.next()) should be used instead since it should
always return a result.

gfxx

@mrjj i try all these solutions (in these way DELETE statement is better) ...

void MainWindow::CancelRowNumberSystem()
{
    int rows211 = 0;
    QSqlQuery q211(db);
    q211.clear();
    q211.prepare("SELECT * FROM errori");
    q211.exec();
    q211.next();
    /*q211.prepare("SELECT COUNT (1) FROM errori");
        if (q211.exec()) {
            rows211 = q211.value(0).toInt();
        }*/

    rows211 = q211.size();


    qDebug() << "what error in row search?:   " << q211.lastError();

    qDebug() << "il totale righe è:   " << rows211;

    if(rows211 >= 10)
    {
        QSqlQuery q212(db);
        q212.clear();
        q212.prepare("DELETE FROM errori WHERE id_errore = (SELECT MIN(id_errore) FROM errori)");
        //q212.addBindValue(":id_errore", 1);
        q212.exec();
        q212.next();

        qDebug() << "what error in delete stmt?:   " << q212.lastError();

    }
}

but the total number of row not appear ... with COUNT or with size .... and not error.

P.S.: ok ... seems query.size is not supporteg by sqlite .... only last and previous ,,,,
read here ...

regards
Giorgio

mrjj

Hi
So the delete dont work ?
Does you database support that type of inline
use of select ?

Please check that prepare works

int ok = query.prepare("DELETE FROM xxx")
if (!ok) qDebug() << "prepare failed";

gfxx

@mrjj I'm explain me better .... DELETE stmt work perfect, expecially using my last posted code ..... my problem is about COUNT(*) .... that not show me the total row number in mytable .... if using these solution:

int rows211 = 0;
    QSqlQuery q211(db);
    q211.clear();
    q211.prepare("SELECT * FROM errori");
    q211.exec();
    
    rows211 = q211.size();

    qDebug() << "the select error:    " << q211.LastError();
    qDebug() << "the number of row:    " << q211.size();

I get:

the select error:    qSqlquery error ("""" , """" , """");
the number of row:    -1;

if I use these:

int rows211 = 0;
    QSqlQuery q211(db);
    q211.clear();
    q211.prepare("SELECT COUNT (1) FROM errori");
        if (q211.exec()) {
            rows211 = q211.value(0).toInt();
        }

    qDebug() << "the select error:    " << q211.LastError();
    qDebug() << "the number of row:    " << rows211;

I get these other messages:

the select error:    qSqlquery error ("""" , """" , """");
the number of row:    -1;

int rows211 = 0;
    QSqlQuery q211(db);
    q211.clear();
    q211.prepare("SELECT COUNT (*) FROM errori");
        if (q211.exec()) {
            rows211 = q211.value(0).toInt();
        }

    qDebug() << "the select error:    " << q211.LastError();
    qDebug() << "the number of row:    " << rows211;

I get these other messages:

the select error:    qSqlquery error ("""" , """" , """");
the number of row:    0;

So I'm not able to get mytable total row number .....

And I read about Sqlite unsupport .size() request. So .size() is not a solutions .... but COUNT must be one ... but not work.

So I think about my error or COUNT is unsupported and I must use .last, .previous and .next instead of COUNT.

I'm in error?

regards
giorgio

mrjj

Hi
You seem again to forget to call exec()
If you dont call exec() its not run :)
and it wont report count correctly.
prepare DO NOT run it. ( so there be no errors either)

int numberOfPages=0;
query.prepare("SELECT COUNT(*)  FROM errori");  
if ( query.exec()  )  {
     numberOfPages = query.value(0).toInt();
  } else { error }

this works for me. reports the row count for db.
SQLite

you can even do

int numRows=0;
if ( query.exec(SELECT COUNT(*)  FROM errori")  )       
numRows = query.value(0).toInt();

note its
SELECT COUNT (*) and not SELECT COUNT (1)

* and not 1

gfxx

@mrjj no .... the green color over code is only some type of error in the html code .... my code and yous is the same .... any how in these moment run my code on QT5.6 instead QT5.8 ..... My versions of code returns "0" value .... so I try to call the void from different point of code .... the same ... so I try these versions:

int numRows=0;
QSqlQuery query(db);
query.clear();
if ( query.exec(SELECT COUNT(*)  FROM errori")  )       
numRows = query.value(0).toInt();

and I get these messages on application output:

QSqlQuery::value: not positioned on valid record

so yes previosly the esecution not stat because these error ..... but db is open without error ..... errori table exist ... it have a primarykey ... i belive these is not a problem .... so not understand these messsages .... because if try other type of select it works .... and DELETE works too ....

Giorgio

gfxx

@gfxx without query.clear no error again but result is "0" .....

mmmm ... my table not start from my_id = 1 .... and all example that I see report example_id start from 1 ..... not see example with example_id start from 9 ...... maybe the problem?

I reply by my own : I use sqliteman to make a test. I make a view with these code: SELECT (*) FROM errori . The result is 62 ... my table id start from 5 and the end is 66 .... so it works in that case.

So there is an erro in my c++ code that not see ....

Actually I create a view on sqlite db with the query SELECT COUNT(*) FROM errori .... than in my c++ code I make a SELECT * FROM mucountview and obtayn the right row number .....

works ok ..... using COUNT directly in my c++ code not works at all .....

regards
giorgio

mrjj

Ok
so either what ever db engine you have do not support count
or you need to use next() as
"QSqlQuery::value: not positioned on valid record" sounds like that.

qDebug() << "exec result:" << query.exec("SELECT COUNT( * ) FROM errori");
if (query.next())
rows = query.value(0).toInt();
else
qDebug() << "empty";

gfxx

@mrjj I think you are in right .... but I can make these last test only tomorrow night. After these I post the result.

For Now Thanks.
Giorgio

gfxx

@mrjj So the test code used:

void MainWindow::sss16()
{
    int numRows=0;
    QSqlQuery query(db);
    query.clear();
    if ( query.exec("SELECT COUNT(*)  FROM errori")  )
    numRows = query.value(0).toInt();

    qDebug() << "last positioning error:   " << query.lastError();
    query.clear();      /* to these row my normal code with undesired result ... no error but no result over Sqlite .... */


    qDebug() << "exec result:" << query.exec("SELECT COUNT( * ) FROM errori");
    if (query.next())
    numRows = query.value(0).toInt();
    else
    qDebug() << "empty";   /* .... your suggested code .... run because .next() ....*/
    qDebug() << "the real number of table row: .... " << numRows;

}

the output messsages when run the void:


QSqlQuery::value: not positioned on a valid record
last positioning error:    QSqlError("", "", "") /*..... these two row is the output messages of my code .... so no SELECT query result because not positioning on valid record .......*/
exec result: true  /*............. the result of your code .... obviously the .next() command solve the situation ..........*/
the real number of table row: ....  47

real thanks
Have a real nice week end.
Giorgio

mrjj

Hi
Super.
So its best to use always if (query.next())
to be compatible with all databases.

Thank you and good weekend to you too.