Some problem with sql query.
-
Good day.
Again i must asking for your help and attention. I'am wondering a strange behavior of simple sql query. Nothing supernatural, but it's not valid:
@
QString name;
QString nameOfCreator;
QStringList list;
QStringList exhibitors;
QStringList ListOfTheGroups;
QStringList ListOfPasswords;
in>>name>>list;
QSqlQuery query;
//name VARCHAR(30) PRIMARY KEY, creator VARCHAR(30),exhibitors TEXT, files TEXT, password VARCHAR(20)"
for(int i=0;i<list.count();i++)
{//Here is a location of problem with joining into group. //query do not get a value from table Groups query.exec("SELECT * FROM Groups WHERE name='"+list.at(i)+"'"); if(query.isNull(0)) qDebug()<<query.lastError(); else { qDebug()<<"In table Groups was selected:"<<query.value(0)<<query.value(1)<<query.value(2)<<query.value(3)<<query.value(4); } if(query.isValid()) { exhibitors.append(query.value(2).toStringList()); nameOfCreator=query.value(1).toString(); } if((name!=nameOfCreator)&&(!exhibitors.contains(name))) { ListOfTheGroups.append(query.value(1).toString()); ListOfPasswords.append(query.value(3).toString()); }
}
@Consoles output on qDebug()<<query.lastError(): "QSqlQuery(-1,"","");"
I'm confused by such strange behavior. Many sql queries was used in same file without any problem. Database - SQLite. Connection and creation of table was successful.
Question in pursuit, can it be causes by changing of Qt enviroment? I wrire most part of code on different machines, but stucked with such problem in first time.
Many thanks in advance.
-
Could you share the structure of table Groups?
-
Take another look at the "QSqlQuery":http://qt-project.org/doc/qt-4.8/qsqlquery.html#details documentation.
[quote]
Successfully executed SQL statements set the query's state to active so that isActive() returns true. Otherwise the query's state is set to inactive. In either case, when executing a new SQL statement, the query is positioned on an invalid record. An active query must be navigated to a valid record (so that isValid() returns true) before values can be retrieved. Navigating records is performed with the following functions: next(), previous(), first(), last(), seek().
[/quote]
[quote]
"QSqlQuery::isNull()":http://qt-project.org/doc/qt-4.8/qsqlquery.html#isNull Returns true if the query is active and positioned on a valid record and the field is NULL; otherwise returns false. Note that for some drivers, isNull() will not return accurate information until after an attempt is made to retrieve data.[/quote] -
Structure a table Groups:
@TABLE Groups (name VARCHAR(30) PRIMARY KEY, creator VARCHAR(30),exhibitors TEXT, files TEXT, password VARCHAR(20))@New situation with sql query has arrived.
Here is a code:
@
QString MyServer::getListOfContact(QString name)
{
QSqlQuery query;
query.exec("SELECT listOfUsers FROM users WHERE login='"+name+"'");
// if(query.isActive()&&query.isValid())
// {
QString listOfContact;
while(query.next())
listOfContact=query.value(0).toString();QStringList l=listOfContact.split(","); listOfContact.clear(); for(int i=0;i<l.count();i++) if(l.at(i)=="NULL") l.removeAt(i); for(int i=0;i<l.count();i++) listOfContact+=l.at(i)+","; listOfContact.remove(listOfContact.size()-1,1); query.clear(); return listOfContact;
// }
// else
// {
// qDebug()<<query.isValid();
// qDebug()<<query.lastError();
// }}
@I belive issue is a similar to the previous. When program using this method in a first time, it's fine, exept check the validity. My consideration about follow below.
After what, when program using this procedure again, AND VARIABLE name HAS A SAME VALUE, query is empty. There no calls to database, between first and last query. What it can be?Now about check for validity. Here is one of procedure of adding new element to table:
@
QString nameOfCreator;
QString nameOfTheGroup;
QString listOfTheGroupExhibitors;
QString passwordOfTheNewGroup;
QStringList listOfAlreadyExistingGroups;///////////////////////////// if(!listOfAlreadyExistingGroups.contains(nameOfTheGroup)) { query.exec("INSERT INTO Groups(name, creator, exhibitors, files, password)" "VALUES(:name,:creator,:exhibitors,:files,:password)"); query.bindValue(":name",nameOfTheGroup); query.bindValue(":creator",nameOfCreator); query.bindValue(":exhibitors",listOfTheGroupExhibitors); query.bindValue(":files",NULL); query.bindValue(":password",NULL);
@
Not a single query does not pass for validity, but i don't understand why. I already try create table with TEXT fileds - result is a same: query is not valid and lastError return @QSqlError(-1, "", "")@. It's more than just a strange, because database open correctly and first query was execute file. I'm stuck.
Many thanks in advance.