how to get integers from mysql

[eleanor]

Hi, I have this sql statement;

QSqlQuery query;
  query.exec(trUtf8("SELECT id FROM descriptor_table WHERE ime_tabele='%1'").arg("town"));

QSqlQuery query;
  query.exec(trUtf8("SELECT id FROM descriptor_table WHERE ime_tabele='%1'").arg("town"));

To copy to clipboard, switch view to plain text mode 

where descriptor_table and "town" are two tables in my database. The descriptor_table hold info about other tables.

Now I want to read the rows into the application at which the certain table appears. In this case lines are 1,2,3 (if I enter this command in mysql> command line). The problem now is that I want to get them into an application...

Any ideas are appreciated.

[jacek]

You need QSqlQuery::next() and QSqlQuery::value().

[eleanor]

Why isn't this working:

QSqlQuery query;
  QVariant integer[m_pTableRecord->count()];
  query.prepare(trUtf8("SELECT id FROM descriptor_table WHERE ime_tabele='%1'").arg(sTableName));
  for(qint32 i=0;i<m_pTableRecord->count();i++) {
	integer[i] = query.value(0);
	query.next();
	qDebug() << "Value " << i << "is: " << integer[i].toInt();
  }

QSqlQuery query;
  QVariant integer[m_pTableRecord->count()];
  query.prepare(trUtf8("SELECT id FROM descriptor_table WHERE ime_tabele='%1'").arg(sTableName));
  for(qint32 i=0;i<m_pTableRecord->count();i++) {
	integer[i] = query.value(0);
	query.next();
	qDebug() << "Value " << i << "is: " << integer[i].toInt();
  }

To copy to clipboard, switch view to plain text mode 

It always returns 0. It should return 0,1,2.

[jpn]

The query is prepared, but I don't see any bindValue() or exec()...

Edit: Take a look at http://doc.trolltech.com/4.3/qsqlque...binding-values and you'll see what I mean.

[eleanor]

And why doesn't this get the QString that's in table descriptor_table under field browse_caption at id==<some_number>

QVariant string[m_pTableRecord->count()];
    for (m_iIndex=1; m_iIndex<m_pTableRecord->count(); m_iIndex++) {
	  query.prepare(trUtf8("SELECT browse_caption FROM descriptor_table WHERE id='%1'").arg(integer[m_iIndex+1].toInt()));
	  query.exec();
	  string[m_iIndex] = query.value(0);
	  qDebug() << "String " << string[m_iIndex].toString();
      m_pColumnNames->append(string[m_iIndex].toString());
    }

QVariant string[m_pTableRecord->count()];
    for (m_iIndex=1; m_iIndex<m_pTableRecord->count(); m_iIndex++) {
	  query.prepare(trUtf8("SELECT browse_caption FROM descriptor_table WHERE id='%1'").arg(integer[m_iIndex+1].toInt()));
	  query.exec();
	  string[m_iIndex] = query.value(0);
	  qDebug() << "String " << string[m_iIndex].toString();
      m_pColumnNames->append(string[m_iIndex].toString());
    }

To copy to clipboard, switch view to plain text mode 

What it outputs is empty. Why

[jacek]

You have to prepare and execute the query only once. Next, to get the data, you have to first invoke next() and then value().

[eleanor]

query.prepare(trUtf8("SELECT browse_caption FROM descriptor_table WHERE id='%1'").arg(integer[m_iIndex+1].toInt()));

query.prepare(trUtf8("SELECT browse_caption FROM descriptor_table WHERE id='%1'").arg(integer[m_iIndex+1].toInt()));

To copy to clipboard, switch view to plain text mode 

Well I can't prepare the statement only once, because I have to change the argument to the prepend) function. The argument is an integer that should raise by one every time I go through for loop.

[jacek]

Well I can't prepare the statement only once, because I have to change the argument to the prepend) function.

I haven't noticed that you have changed the query. In such case you can prepare the statement only once and then use bindValue() to change the parameter value.

q.prepare( "SELECT ... FROM ... WHERE id = :id" );
for( ... ) {
   q.bindValue( ":id", nextId );
   if( q.exec() && q.next() ) {
      // ...
   }
   else {
      // exec failed or no data
   }
}

q.prepare( "SELECT ... FROM ... WHERE id = :id" );
for( ... ) {
   q.bindValue( ":id", nextId );
   if( q.exec() && q.next() ) {
      // ...
   }
   else {
      // exec failed or no data
   }
}

To copy to clipboard, switch view to plain text mode 

The argument is an integer that should raise by one every time I go through for loop.

Then maybe you don't need the WHERE clause at all?

[eleanor]

Hi.

query.prepare(trUtf8("SELECT browse_caption FROM descriptor_table WHERE id=':id'"));
 
    for (m_iIndex=0; m_iIndex<m_pTableRecord->count(); m_iIndex++) {
	  query.bindValue(":id",(integer[m_iIndex].toInt()));
                 query.exec();
	  string[m_iIndex] = query.value(0);
	  query.next();
	  qDebug() << "String " << string[m_iIndex].toString();
      m_pColumnNames->insert(m_iIndex, string[m_iIndex].toString());
    }

query.prepare(trUtf8("SELECT browse_caption FROM descriptor_table WHERE id=':id'"));
 
    for (m_iIndex=0; m_iIndex<m_pTableRecord->count(); m_iIndex++) {
	  query.bindValue(":id",(integer[m_iIndex].toInt()));
                 query.exec();
	  string[m_iIndex] = query.value(0);
	  query.next();
	  qDebug() << "String " << string[m_iIndex].toString();
      m_pColumnNames->insert(m_iIndex, string[m_iIndex].toString());
    }

To copy to clipboard, switch view to plain text mode 

This code does now work (it does not return a string...actually it always returns an empty string. --> what to do)?

[jacek]

"SELECT browse_caption FROM descriptor_table WHERE id=':id'"

It should be :id, not ':id'.

string[m_iIndex] = query.value(0);
query.next();

You have to call next() first, then value().