QSqlQuery::value: not positioned on a valid record

[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

[JonB]

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

but really is only trascription error not real error

Given what you are showing and asking us how can people answer if you allow "only transcription errors" to appear? Please think about what you are asking others and the time they are putting in.

You cannot use a construct like SELECT ... FROM :dTable1. Variable binding can only be placed where the driver allows it, such as for values in a WHERE clause. The table name for a statement cannot be specified by a bound variable.

Your QSqlQuery::prepare() call returns a boolean value precisely so you can check whether what you are passing is legal/can be bound. This is not the first time you do not write code to check return values for your benefit. Please do so, you should be getting false back from your call.

[gfxx]

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

Your QSqlQuery::prepare() call returns a boolean value precisely so you can check whether what you are passing is legal/can be bound. This is not the first time you do not write code to check return values for your benefit. Please do so, you should be getting false back from your call.

for first thanks for your reminder.... this is also a help

for second .... last debug result become from bolean value ....

if(!qryUser1.exec()){
                        qDebug() << "try to not work...." << qryUser1.lastQuery() << qryUser1.lastError();
                        qryUser1.finish();
                        qryUser1.clear();
                    }

after these ..... someone in thread suggest me to make the query on QT way .... so I try .... but first to try, I express my doubt about .... you pherhaps comfirm my doubt ....

So the solution could be to assemble a const string.... the printout could become the query.... the problem is that using arg(xxx) I can't eliminate the unwanted " ' "... and they are not failed not even assembling QString....is there a way do it or should I abandon?

[JonB]

@gfxx
I'm sorry but I'm afraid I don't understand what you are saying here.

You cannot write your SQL query just the way you did, e.g. with FROM :dTable1 and possibly inside your date() expression, as bindable variables, or if you have some mix up with the quote marks. (You should be able to get QString to do whatever you actually want.)

However, you do not have to use bindable variables, where they are not suitable. You can always construct the required query as a literal string with any "substitutions" already inserted (e.g. using QString::arg()) instead of trying to bind variables. So if you know you want to execute the query you showed you can certainly do that this way if required.

[gfxx]

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

@gfxx
I'm sorry but I'm afraid I don't understand what you are saying here.
You cannot write your SQL query just the way you did, e.g. with FROM :dTable1 and possibly inside your date() expression, as bindable variables, or if you have some mix up with the quote marks. (You should be able to get QString to do whatever you actually want.)
However, you do not have to use bindable variables, where they are not suitable. You can always construct the required query as a literal string with any "substitutions" already inserted (e.g. using QString::arg()) instead of trying to bind variables. So if you know you want to execute the query you showed you can certainly do that this way if required.

try to use QString::arg() .... but obtain everytime that " ' " undesired .... but no worry I try again ... thanks

[Christian Ehrlicher]

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

try to use QString::arg() .... but obtain everytime that " ' " undesired .... but no worry I try again ... thanks

Do you really understand what you are writing? At least I don't...

[JonB]

@gfxx
Let's take your WHERE timeDayData BETWEEN date('now', 'data2= :data21 day') and date('now', 'data1= :data11 day') segment. We do not know what you are trying to produce.

At a guess:

WHERE timeDayData BETWEEN date('now', '10 day') and date('now', '99 day')

Is that what you are intending to send to your dialect of SQL? Have you verified this works before you spend time trying to produce it?

[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 ...

[Christian Ehrlicher]

@gfxx lol

not that you add those 'strange' characters by yourself...

BETWEEN date('now', ' '"+data2+"' day') 

[piervalli]

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

timeDayData

A little Exercse with Chat Gpt

CREATE TABLE YourTableName (
    id INT PRIMARY KEY,
    timeDayData DATETIME
);

-- Inserting a row with a specific date and time
INSERT INTO YourTableName (id, timeDayData) VALUES (3, '2023-09-14 12:30:00');

-- Inserting another row with a different date and time
INSERT INTO YourTableName (id, timeDayData) VALUES (4, '2023-11-15 09:45:00');
select * from YourTableName WHERE timeDayData  >= '2023-09-06T23:15:24' AND timeDayData  <= '2023-12-04T22:15:24'

Now in Qt

    // Get the current date and time
    QDateTime currentDateTime = QDateTime::currentDateTime();

    // Calculate 10 days ago
    const auto tenDaysAgo = currentDateTime.addDays(-10).toString(Qt::ISODate);
    const auto ninetynineAgo = currentDateTime.addDays(-99).toString(Qt::ISODate);
    qDebug() << "Current Date and Time:" << currentDateTime.toString(Qt::ISODate);
    qDebug() << "10 Days Ago:" << tenDaysAgo;
    qDebug() << "99 Days Ago:" << ninetynineAgo;
    qDebug() << QString("Select * from YourTableName WHERE timeDayData  >= '%1' AND timeDayData  <= '%2'").arg(ninetynineAgo,tenDaysAgo);

I prefer write sql in Qt, so we can log the query on file, if you have a problem the debug it easy.