QSqlQuery::value: not positioned on a valid record

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

[gfxx]

@Christian-Ehrlicher in that row for sure ... but in 4 days trying think sometime one I write without strange characters add by myself .... and for sure queryexecuted (for debug) produce for 3 days only non usable query on sqlite3 ..... for sure only 4hour days of test ... no more

[gfxx]

@piervalli thanks never use chatgtp ... and thank for your interesting on these.