I have a mobile application that uses an sqlite database. I cannot seem to figure out how to access the next record when the user presses the 'next' button. My database is using 'id' as primary key with autoincrement as integer.
private function nextMoveRPart(event:EffectEvent):void
{
var cardNumber:int = parseInt(cardNumberLabel.text);
stmt.sqlConnection = conn;
conn.open(File.applicationStorageDirectory.resolvePath("FlashCards.db"));
stmt.text = "SELECT * FROM cardItems WHERE id = ?" + "AND id <= MAX(id)";
stmt.parameters[0] = cardNumber+1;
stmt.addEventListener(SQLEvent.RESULT, resultHandlerPrev);
stmt.execute();
conn.close();
moveEffectRPart2.play();
}
function resultHandlerNext(event:SQLEvent):void // result handler next
{
var result:SQLResult = stmt.getResult();
var numResults:int = result.data.length;
for(var i:int = 0; i < numResults; i++)
{
var row:Object = result.data[i];
stext1.text = row.cSide1;
stext2.text = row.cSide2;
cardNumberLabel.text = row.id;
}
}
You can always store current id in some variable in ActionScript. Then You will be able to rewrite the whole query string. I think You can write something like this:
stmt.text = "SELECT * FROM cardItems WHERE id > " + current_id + " ORDER BY id ASC LIMIT 1;";
The current_id is an ActionScript variable. This should be good enough for sqlite. You may set current_id to 0 for the first record. If a user click a next button, You should store there current id from the already received record.
The second option is to use OFFSET. It exclude some records from the beginning of returned set of rows.
stmt.text = "SELECT * FROM cardItems ORDER BY id ASC LIMIT 1 OFFSET " + your_offset + ";";
The your_offset is an ActionScript variable. It is 0 if You want get the first record, 1 for second, 2 for third and so on.
===
Try this code.
protected function button6_clickHandler(event:MouseEvent):void // pushed --> button
{
var cardNumber:int = parseInt(cardNumberLabel.text);
/* Deleted */
var sqlresult:SQLResult = stmt.getResult();
stmt.sqlConnection = conn;
conn.open(File.applicationStorageDirectory.resolvePath("FlashCards.db"));
/*Edited two lines*/
stmt.text = "SELECT * FROM cardItems WHERE id > ? ORDER BY id ASC LIMIT 1";
stmt.parameters[0] = cardNumber;
stmt.execute();
trace(sqlresult.data); // value = [object Object]
stext1.text = sqlresult.data.cSide1;
trace(sqlresult.data.cSide1); // value = 'undefined'
cardNumberLabel.text = sqlresult.data.id;
trace(sqlresult.data.id); // value = 'undefined'
conn.close();
moveEffectRPart1.play();
moveEffectRPart1.addEventListener(EffectEvent.EFFECT_END, nextMoveRPart);
}
