samsam598 wrote:I want to define several functions that can navigate between rows in a sqlite3 table.For example
Code: Select all
if(!table->Bof())
table->Previous();//go back to previous record;
...
if(!table->Eof())
table->Next();//go to next record;
table->First();//go to the first record;
table->Last();//go to the last record;
...
Unfortunately a result set of a SQLite query can be processed only in forward direction, i.e. you can't step back to the previous record without rerunning the query and you can't step directly to the last record without reading all other records first.
Depending on your needs and any given constraints there exist work arounds to overcome this situation. If you have an appropriate database index available you can for example get the last n rows matching a given WHERE condition by specifying the "ORDER BY ... DESC" clause and adding a "LIMIT n" clause.
In a similar manner you can select the previous row if you know the key of the current row.
samsam598 wrote:Also I would like to check whether a certain value of a primary key has already in the table before new/insert/append take place.For example
Code: Select all
if(table->FindKey(keyvalue))
wxMessageBox("Key already exsits.Insert new record failed.");
How can I do this?
If you have specified a primary key in your table definition there is no need to query the key before hand. Just execute the SQL insert statement. If the key already exists, you'll get an exception. Catch that exception and emit an appropriate error message. In case of a primary key violation you get "SQLITE_CONSTRAINT" as the error string from the exception object.
In case you want to insert a new record if the key doesn't exist and to update the corresponding record if the key does exist, then you could use the "INSERT OR REPLACE" syntax of the SQLite insert statement.
samsam598 wrote:I noticed that wxSqlite3Database* db->GetLastRowId() is not usable.If I write code like this:
Code: Select all
...
wxSQLite3Transaction trans(db);
db->ExecuteUpdate("insert into car values( db->GetLastRowId()+2,'BMW');");
trans.Commit();
...
The program will cause a runtime exception. Is there anything I am doing wrong?
Sorry to say that, but the SQL statement is complete nonsense. How should SQLite know about the wxSQLite3 database object db and it's methods? You have to get the value of the last inserted row id using method GetLastRowId and could then bind that value to a parameter of a prepared statement:
Code: Select all
wxLongLong rowId = db->GetLastRowId();
wxSQLite3Statement stmt = db.PrepareStatement("insert into car values (?, 'BMW');");
stmt.Bind(1, rowId);
stmt.ExecuteUpdate();
Alternatively you could use the SQL function last_insert_rowid:
Code: Select all
db->ExecuteUpdate("insert into car values(last_insert_rowid()+2,'BMW');");
Before using one of these methods you should consult the SQLite documentation about the
last inserted row id. You only get a valid id
after successful execution of an SQL insert statement within the current database connection, otherwise you get zero. And that's probably not what you want.
Regards,
Ulrich