QSqlQuery::value: not positioned on a valid record

[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", "")

[jsulm]

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

FROM dbTable= :dbTable1

What is this?
It should be

FROM :dbTable1