sqlite seek() bug?

[ibergmark]

I'm having a wierd problem with seek() on a sqlite database (Windows XP).
I have prepare'd a SQL statement, and then use seek() to get a random record in the result set.
The seek() function always returns true, but sometimes the record is not initialized. I see no logic behind this behaviour, since isValid(), isActive() and isSelect() all return true after a seek() (see attached output).

Anybody have any clue to why this is happening?

Regards,
Ingemar

bool VAce::randomSynonym(QSqlQuery *currentSql, int synCount)
{    
	int engIndex = currentSql->record().indexOf("wrdeng");
	int typeIndex = currentSql->record().indexOf("typid");
 
    QSqlQuery randomSql(QSqlDatabase::database("vaceDB"));
 
    //get number of records
    randomSql.prepare("select count(*) from word where wrdeng != :engWord and typid = :typeID");
    randomSql.bindValue(":engWord", currentSql->record().value(engIndex).toString());
    randomSql.bindValue(":typeID", currentSql->record().value(typeIndex).toString());
    randomSql.exec();
    randomSql.next();
    int numRecs = randomSql.record().value(0).toInt();
 
 
    randomSql.prepare("select * from word where wrdeng != :engWord and typid = :typeID");
    randomSql.bindValue(":engWord", currentSql->record().value(engIndex).toString());
    randomSql.bindValue(":typeID", currentSql->record().value(typeIndex).toString());
    randomSql.exec();
 
    int idIndex = randomSql.record().indexOf("wrdid");
 
    for (int i=0; i<synCount; i++)
    {
    	int newRec = rand() % numRecs;
    	bool b1 = randomSql.seek(newRec);
 
    	qDebug() << newRec;
    	qDebug() << "seek OK " << b1;
    	qDebug() << "select " << randomSql.isSelect();
    	qDebug() << "active " << randomSql.isActive();
    	qDebug() << "valid " << randomSql.isValid();
    	qDebug() << "fields in record" << randomSql.record().count();
    	qDebug() << "field 0 =" << randomSql.record().field(0).name();
    	qDebug() << "field 0 value =" << randomSql.record().field(0).value();
 
    	//remove after debug
    	/*if (randomSql.record().value(idIndex).toInt() == 0)
    	{
    		QMessageBox::information(this, "Error", "randomSql record not initialized.\nPrinting will abort.");
    		return false;
    	}*/
    	//----------------
 
	    bool continueSearch = true;
		int tmpSynonym = 0;
		QString tmpStr = "";
 
	    while ((tmpStr.length() == 0) || continueSearch)
	    {
	    	tmpSynonym = (rand() % 6 + 1) + 3;
 
	    	if (synonymList.isEmpty())
	    		tmpStr = currentSql->record().value(tmpSynonym).toString().trimmed();
	    	else
	    		tmpStr = randomSql.record().value(tmpSynonym).toString().trimmed();
 
	        // make sure we don't select a synonym twice
	        if (synonymList.contains(tmpStr))
	        	continueSearch = true;
	        else
	        	continueSearch = false;
	    }
 
	    synonymList.append(tmpStr);
 
	    if (synonymList.size() == 1)
	    	pickedSynonym.append(tmpSynonym);
    }
 
    return true;
}

bool VAce::randomSynonym(QSqlQuery *currentSql, int synCount)
{    
	int engIndex = currentSql->record().indexOf("wrdeng");
	int typeIndex = currentSql->record().indexOf("typid");

    QSqlQuery randomSql(QSqlDatabase::database("vaceDB"));
    
    //get number of records
    randomSql.prepare("select count(*) from word where wrdeng != :engWord and typid = :typeID");
    randomSql.bindValue(":engWord", currentSql->record().value(engIndex).toString());
    randomSql.bindValue(":typeID", currentSql->record().value(typeIndex).toString());
    randomSql.exec();
    randomSql.next();
    int numRecs = randomSql.record().value(0).toInt();
    
    
    randomSql.prepare("select * from word where wrdeng != :engWord and typid = :typeID");
    randomSql.bindValue(":engWord", currentSql->record().value(engIndex).toString());
    randomSql.bindValue(":typeID", currentSql->record().value(typeIndex).toString());
    randomSql.exec();

    int idIndex = randomSql.record().indexOf("wrdid");
    
    for (int i=0; i<synCount; i++)
    {
    	int newRec = rand() % numRecs;
    	bool b1 = randomSql.seek(newRec);
    	
    	qDebug() << newRec;
    	qDebug() << "seek OK " << b1;
    	qDebug() << "select " << randomSql.isSelect();
    	qDebug() << "active " << randomSql.isActive();
    	qDebug() << "valid " << randomSql.isValid();
    	qDebug() << "fields in record" << randomSql.record().count();
    	qDebug() << "field 0 =" << randomSql.record().field(0).name();
    	qDebug() << "field 0 value =" << randomSql.record().field(0).value();
    	
    	//remove after debug
    	/*if (randomSql.record().value(idIndex).toInt() == 0)
    	{
    		QMessageBox::information(this, "Error", "randomSql record not initialized.\nPrinting will abort.");
    		return false;
    	}*/
    	//----------------
    	
	    bool continueSearch = true;
		int tmpSynonym = 0;
		QString tmpStr = "";
	
	    while ((tmpStr.length() == 0) || continueSearch)
	    {
	    	tmpSynonym = (rand() % 6 + 1) + 3;
	    	
	    	if (synonymList.isEmpty())
	    		tmpStr = currentSql->record().value(tmpSynonym).toString().trimmed();
	    	else
	    		tmpStr = randomSql.record().value(tmpSynonym).toString().trimmed();
	
	        // make sure we don't select a synonym twice
	        if (synonymList.contains(tmpStr))
	        	continueSearch = true;
	        else
	        	continueSearch = false;
	    }
	    
	    synonymList.append(tmpStr);
	    
	    if (synonymList.size() == 1)
	    	pickedSynonym.append(tmpSynonym);
    }
    
    return true;
}

To copy to clipboard, switch view to plain text mode 

Output from the qDebug statements. As you can see in the last entry the field is not initialized even though the record seems fine..

warning: 661

warning: seek OK true

warning: select true

warning: active true

warning: valid true

warning: fields in record 12

warning: field 0 = "wrdid"

warning: field 0 value = QVariant(qlonglong, 4240)

warning: 336

warning: seek OK true

warning: select true

warning: active true

warning: valid true

warning: fields in record 12

warning: field 0 = "wrdid"

warning: field 0 value = QVariant(qlonglong, 3387)

warning: 404

warning: seek OK true

warning: select true

warning: active true

warning: valid true

warning: fields in record 12

warning: field 0 = "wrdid"

warning: field 0 value = QVariant(qlonglong, 3561)

warning: 146

warning: seek OK true

warning: select true

warning: active true

warning: valid true

warning: fields in record 12

warning: field 0 = "wrdid"

warning: field 0 value = QVariant(qlonglong, 2901)

warning: 693

warning: seek OK true

warning: select true

warning: active true

warning: valid true

warning: fields in record 12

warning: field 0 = "wrdid"

warning: field 0 value = QVariant(qlonglong, 4312)

warning: 28

warning: seek OK true

warning: select true

warning: active true

warning: valid true

warning: fields in record 12

warning: field 0 = "wrdid"

warning: field 0 value = QVariant(qlonglong, 2605)

warning: 130

warning: seek OK true

warning: select true

warning: active true

warning: valid true

warning: fields in record 12

warning: field 0 = "wrdid"

warning: field 0 value = QVariant(qlonglong, 2878)

warning: 98

warning: seek OK true

warning: select true

warning: active true

warning: valid true

warning: fields in record 12

warning: field 0 = "wrdid"

warning: field 0 value = QVariant(qlonglong, 2804)

warning: 44

warning: seek OK true

warning: select true

warning: active true

warning: valid true

warning: fields in record 12

warning: field 0 = "wrdid"

warning: field 0 value = QVariant(qlonglong, 2714)

warning: 461

warning: seek OK true

warning: select true

warning: active true

warning: valid true

warning: fields in record 12

warning: field 0 = "wrdid"

warning: field 0 value = QVariant(, )

[ibergmark]

I found a solution to my problem for getting random records.

Instead of using seek(), SQLite supports the random() function, so I just include this in the SQL statement.
For example, to get 4 random rows from a table, do this;

select col1,col2,...,random() r from table order by r limit 4

Works pefectly

/ Ingemar