QSqlQuery::value: not positioned on a valid record

[gfxx]

on my Sqlite db can write:

SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data)
FROM getdata
WHERE timeDayData BETWEEN date('now','-605 day') and date('now','-598 day')

with valid result ... so in my mainwindovs void I try to call:

QString dbTable = "getdata";
QString data2 = "-605";
QString data1 = "-598 ";

QSqlQuery qryUser(QSqlDatabase::database("db"));
                        qryUser.exec("SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM '"+dbTable+"' WHERE timeDayData BETWEEN date('now', ' '"+data2+"' day') and date('now', ' '"+data1+"' day')");
while (qryUser.next()){
                           /* grab data code */
                        }
                        qryUser.finish();
                        qryUser.clear();
                        qDebug() << qryUser.lastError() ;

but obtain only these error and no data:

QSqlError("", "", "")    /* for => qDebug() << qryUser.lastError() ;*/
QSqlQuery::value: not positioned on a valid record /* because some error */

i write the query in wrong way? someone can suggest the right one?

regards

[gfxx]

@JonB my interger is negative only ..... and produce from one day to other sum of all values of 1data, 2data etc etc .... if 1data equal selling hamburger, from now-605 days and now-590day I can see sum of hamburger selling .... but can be every data from other every data ..... first of these I use the sqlite view, now a parametric view for infinite data table ... with same table prototype obviusly ..... sorry for my bad english ... but think you can well understand.

Any how I solve using std::string and std::ostringstream in these way:

std::string dbTab = "getdata";
                    qDebug() <<  data2 << " ... number 2.." << dtNow1;
                    qDebug() << data1 << " ... number 1.." << dy1;
                    oss << "SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM " << dbTab << " WHERE timeDayData BETWEEN date('now', '" << dtNow1 << " day') AND date('now', '" << dy1 << " day')";
                    std::string varQ = oss.str();
                    QString strQ = QString::fromUtf8(varQ.c_str());
                    QSqlQuery qryUser(QSqlDatabase::database("db"));
                    qryUser.exec(strQ);
                    qDebug() << "..query executed ....  " << qryUser.executedQuery();
                                            while (qryUser.next()){ / ***** rest of code ****/

ostringstream not add strange character as QString during assembling .... and converted QString can read from query.exec() whitout issue ....

thanks to all ....

merry Christmas to all ...

[JonB]

@gfxx
Is your database open? QSqlQuery::exec() returns a value, test it. Or maybe there is no error, I cannot tell from what you have shown.

[gfxx]

@JonB for sure is open because i try other query reply is perfect ... problem was related to variable .... if write inside query a direct value in these way:

qryUser.exec("SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM  getdata WHERE timeDayData BETWEEN date('now', ' -620 day') and date('now', ' -590 day')");
while (qryUser.next()){

query work ...

[gfxx]

@JonB said in QSqlQuery::value: not positioned on a valid record:

@gfxx
Is your database open? QSqlQuery::exec() returns a value, test it. Or maybe there is no error, I cannot tell from what you have shown.

update ... previous code not work ..... these one yes .... note no spaces before - (minus) ...

qryUser.exec("SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM  getdata WHERE timeDayData BETWEEN date('now', '-620 day') and date('now', '-590 day')");
while (qryUser.next()){

[Christian Ehrlicher]

@gfxx As @JonB already told you - QSqlQuery::exec() returns a bool which you should check...

[gfxx]

@JonB any original code without spaces give me the same result ....no error but

QSqlQuery::value: not positioned on a valid record 

not undestand ...

[jsulm]

@gfxx How about following what others suggested?
For the third time: check what QSqlQuery::exec() returns and if it fails check what https://doc.qt.io/qt-6/qsqlquery.html#lastError and https://doc.qt.io/qt-6/qsqlquery.html#executedQuery return...

[Bonnie]

@gfxx
date('now', ' '"+data2+"' day') -> date('now', ' '-605' day')
date('now', ' '"+data1+"' day') -> date('now', ' '-598' day')
I don't think the above is right because you have extra single quotes in the string.
As others already have told you, your exec() probably fails, so there's no "valid record".

[piervalli]

@gfxx
I usually for best practice print in console every error with query, so you can check quickly.

try this :
if(!qryUser.exec("SELECT SUM(1Data) ...") qDebug() << qryUser.lastQuery() << qryUser.lastError()

[gfxx]

@piervalli said in QSqlQuery::value: not positioned on a valid record:

qDebug() << qryUser.lastQuery() << qryUser.lastError()

I try but I have no debug messages .. think because query is working .... think these messages ...

QSqlError("", "", "")    /* for => qDebug() << qryUser.lastError() ;*/
QSqlQuery::value: not positioned on a valid record /* because some error */

related to debug of my query show a right query but some "invalid record" .... so think sqlite driver not working in right manner the query ... most probabily because variable is not write in right manner ....

[gfxx]

@piervalli finally with your suggest I see what happens .... but not try solution ... for now try to compose a string for a statement like:

data2 = "date('now', '" + QString::number(dtNow1) + " day')";
data1 = "date('now', '" + QString::number(dy1) + " day')";

so final part of query become: ...WHERE timeDayData BETWEEN '"+data2+"' and '"+data1+"'");

At these point I obtain the resulted request .. that is not right because extra " ' " characters .....

/* lastQuery result*/  "SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM 'getdata' WHERE timeDayData BETWEEN 'date('now', '-11 day')' and 'date('now', '-12 day')'" QSqlError("1", "Unable to execute statement", "near \"now\": syntax error")

how can leave extra " ' " out? because if write '"+date2+"' obtain a reply ... if write "+date2" obtain nothing nor desired result not lastquery macro result ...

[piervalli]

@gfxx said in QSqlQuery::value: not positioned on a valid record:

SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM 'getdata' WHERE timeDayData BETWEEN 'date('now', '-11 day')' and 'date('now', '-12 day')

const auto q = QStringLiteral("SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM getdata WHERE timeDayData BETWEEN date('now','-%1 day') and date('now','-%2 day')").arg(dtNow1).arg(dy1);

So, You can use placeholder, you have extra ' i the query

[gfxx]

@piervalli said in QSqlQuery::value: not positioned on a valid record:

const auto q = QStringLiteral("SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM getdata WHERE timeDayData BETWEEN date('now','-%1 day') and date('now','-%2 day')").arg(dtNow1).arg(dy1);

const auto q1 = QStringLiteral("SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM %1 WHERE timeDayData BETWEEN date('now','-%2 day') and date('now','-%3 day')").arg(myQStringTableName).arg(dtNow1).arg(dy1);

is right in your opinion?

[gfxx]

@piervalli said in QSqlQuery::value: not positioned on a valid record:

const auto q = QStringLiteral("SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM getdata WHERE timeDayData BETWEEN date('now','-%1 day') and date('now','-%2 day')").arg(dtNow1).arg(dy1);

unfortunately these not show result ....

[jsulm]

@gfxx Unfortunatelly you do not show the output qryUser.lastQuery() and qryUser.lastError() after executing the query.
And you should use https://doc.qt.io/qt-6/qsqlquery.html#prepare instead of doing string substitution.

[gfxx]

@jsulm these is not right ....

SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM 'getdata' WHERE timeDayData BETWEEN 'date('now', '-11 day')' and 'date('now', '-12 day')

these is one of lastquery .... as I write problem is extra " ' " in every manner I write the query exec .... so think these is completely qt side problem .... and for these query using query prepare ... not try .... but have some doubt about ....

[Christian Ehrlicher]

@gfxx said in QSqlQuery::value: not positioned on a valid record:

so think these is completely qt side problem ..

Since the query is passed to sqlite the way you write it - no.

Please provide a minimal, compilable example of your problem, including the table creation etc if you want to prove me wrong.

[gfxx]

@gfxx so try in qt way .... but not sure these query can write in that manner ....

QSqlQuery qryUser(QSqlDatabase::database("db"));
                        qryUser.prepare("SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM :dbTable WHERE timeDayData BETWEEN date('now', ':date2 day') and date('now', ':date1 day')");
                        qryUser.bindValue(":dbTable", dbTable);
                        qryUser.bindValue(":data2", data2);
                        qryUser.bindValue(":data1", data1);
                        qryUser.exec();

last query return these ....

try to not work.... "SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM :dbTable WHERE timeDayData BETWEEN date('now', ':date2 day') and date('now', ':date1 day')" QSqlError("", "Parameter count mismatch", "")

is simple if possible write query in these manner ...

qryPausa.prepare("INSERT INTO getdata (timeDayData, 1Data, 2Data, pausaData,3Data, 4Data, 5data) VALUES (:timeDayData, :aData, :bData, :pausaData, :cData, :dData, :eData)");
        qryPausa.bindValue(":timeDayData", timestamp);

in these way work .... but not sure is possible insert VALUES request in my SELECT query ...... because need " ' " ..... if somone can suggest a way to write that query using queryprepare ..... is perfect fo me .....

[Christian Ehrlicher]

@gfxx said in QSqlQuery::value: not positioned on a valid record:

qryUser.prepare("SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM :dbTable WHERE timeDayData BETWEEN date('now', ':date2 day') and date('now', ':date1 day')");
qryUser.bindValue(":dbTable", dbTable);
qryUser.bindValue(":data2", data2);
qryUser.bindValue(":data1", data1);

You should at least try to post correct snippets when you're not able to create a minimal, compilable example... date != data, no checking of QSqlQuery::prepare() return value, ...

No more comments from me in this post here.

[gfxx]

@Christian-Ehrlicher i understand your point of view ... but really is only trascription error not real error .... now try in other manner .... wit same result .. about simple example ..... need to create a simple table with 50 item with these sqlstatement ....

CREATE TABLE "getdata" (
	"Field1"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"1Data"	TEXT,
	"2Data"	TEXT,
        "date"            TEXT
);

than tray select on sqlite3

SELECT SUM(1Data), SUM(2Data),
FROM getdata
WHERE timeDayData BETWEEN date('now','-50 day') and date('now','-60 day')

than try the same on qt mainwindows .... result is mine .... data2 and data1 need to be integer value ..... request is sort "date" from NOW to xxxx value AND from NOW to xxxxMoreLittle ..... xxx and xxxMoreLittle is the var with problem because inside 'xxx day' and 'xxxMoreLittle day' macro .....

Any how write again in other way .....

QSqlQuery qryUser1(QSqlDatabase::database("db"));
                    qryUser1.prepare("SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM dbTable= :dbTable1 WHERE timeDayData BETWEEN date('now', 'data2= :data21 day') and date('now', 'data1= :data11 day')");
                    qryUser1.bindValue(":dbTable1", dbTable);
                    qryUser1.bindValue(":data21", data2);
                    qryUser1.bindValue(":data11", data1);
                    if(!qryUser1.exec()){
                        qDebug() << "try to not work...." << qryUser1.lastQuery() << qryUser1.lastError();
                        qryUser1.finish();
                        qryUser1.clear();
                    }

... see lastquery result ...

try to not work.... "SELECT SUM(1Data), SUM(2Data), SUM(3Data), SUM(4Data), SUM(5Data) FROM dbTable= :dbTable1 WHERE timeDayData BETWEEN date('now', 'data2= :data21 day') and date('now', 'data1= :data11 day')" QSqlError("", "Parameter count mismatch", "")