Tips to define db navigate functions Topic is solved

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
samsam598
Super wx Problem Solver
Super wx Problem Solver
Posts: 340
Joined: Mon Oct 06, 2008 12:55 pm

Tips to define db navigate functions

Post by samsam598 »

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;

...
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?Would you please kindly give me some tips?


EDIT:

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?


Thanks a lot.
Regards,
Sam
-------------------------------------------------------------------
Windows 10 64bit
VS Community 2019
msys2-mingw13.2.0 C::B character set: UTF-8/GBK(Chinese)
wxWidgets 3.3/3.2.4 Unicode Mono Static gcc static build
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Tips to define db navigate functions

Post by utelle »

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
samsam598
Super wx Problem Solver
Super wx Problem Solver
Posts: 340
Joined: Mon Oct 06, 2008 12:55 pm

Re: Tips to define db navigate functions

Post by samsam598 »

utelle wrote: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
Thanks.Now I understood.The wxSQLite3Statement is very simple to use but very powerful!It works now.Thanks!

But I noticed that db->GetLastRowId() always returns zero evern if new record is inserted successfully into the database(I am not using db->GetLastRowId()+2 here as the value of the primary key field for the new record) .Why?
Regards,
Sam
-------------------------------------------------------------------
Windows 10 64bit
VS Community 2019
msys2-mingw13.2.0 C::B character set: UTF-8/GBK(Chinese)
wxWidgets 3.3/3.2.4 Unicode Mono Static gcc static build
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Tips to define db navigate functions

Post by utelle »

samsam598 wrote:But I noticed that db->GetLastRowId() always returns zero evern if new record is inserted successfully into the database(I am not using db->GetLastRowId()+2 here as the value of the primary key field for the new record). Why?
The returned value is zero if and only if there was no succesful insert prior to calling the method GetLastRowId on the same database connection. So this means either the insert failed or the method GetLastRowId was called from a different database connection. A bug in method GetLastRowId is very unlikely since it only calls the SQLite function sqlite3_last_insert_rowid for the associated SQLite database.

Regards,

Ulrich
samsam598
Super wx Problem Solver
Super wx Problem Solver
Posts: 340
Joined: Mon Oct 06, 2008 12:55 pm

Re: Tips to define db navigate functions

Post by samsam598 »

utelle wrote:
samsam598 wrote:But I noticed that db->GetLastRowId() always returns zero evern if new record is inserted successfully into the database(I am not using db->GetLastRowId()+2 here as the value of the primary key field for the new record). Why?
The returned value is zero if and only if there was no succesful insert prior to calling the method GetLastRowId on the same database connection. So this means either the insert failed or the method GetLastRowId was called from a different database connection. A bug in method GetLastRowId is very unlikely since it only calls the SQLite function sqlite3_last_insert_rowid for the associated SQLite database.

Regards,

Ulrich
Thanks.I solved the problem at the first stage.If I want to hold the return value of db->GetLastRowId() for future use,how can I determine if it is a useful value after a successful insertion or a zero?

Code: Select all


try
{
    stmt=PerpareStatement(...);
    stmt.ExecuteUpdate();
    wxLongLong totalRows=db->GetLastRowId();
}
catch(wxSQLite3Exception exp)
{
    wxMessageBox(exp.GetMessage();
   //cleanups
}
I don't think totalRos will hold the total rows before the insertion if the insertion statement failed and exception gets caught.
I want something like this:

Code: Select all

if(successInsert)
  totalRows=db->GetLastRowId9);
else
  totalRows=totalRowsCurrent;
Is this the recommaned way?

Code: Select all

if(db->GetLastRowId()!=0)
totalRows=db->GetLastRowId();
else
  totalRows=totalRowsCurrent;


Thank you so much in advance.
Regards,
Sam
-------------------------------------------------------------------
Windows 10 64bit
VS Community 2019
msys2-mingw13.2.0 C::B character set: UTF-8/GBK(Chinese)
wxWidgets 3.3/3.2.4 Unicode Mono Static gcc static build
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Tips to define db navigate functions

Post by utelle »

samsam598 wrote:I solved the problem at the first stage. If I want to hold the return value of db->GetLastRowId() for future use, how can I determine if it is a useful value after a successful insertion or a zero?
Well, if the last insert was successful GetLastRowId returns it's key, if it fails GetLastRowId returns the key of the previous successful insert or zero if there was no previous insert. Furthermore there exist circumstances where the value returned by GetLastRowId is not predictable. For example if the same database connection is updated from more than one thread or if there exist triggers in the database.
samsam598 wrote:

Code: Select all

try
{
    stmt=PerpareStatement(...);
    stmt.ExecuteUpdate();
    wxLongLong totalRows=db->GetLastRowId();
}
catch(wxSQLite3Exception exp)
{
    wxMessageBox(exp.GetMessage();
   //cleanups
}
I don't think totalRos will hold the total rows before the insertion if the insertion statement failed and exception gets caught.
In your code the variable totalRows wouldn't eveb be accessible since it is local to the try block. And keep in mind that it is not guaranteed at all that GetLastRowId returns the total number of rows of the table. That would be true if and only if you never delete rows from the table.
samsam598 wrote:I want something like this:

Code: Select all

if(successInsert)
  totalRows=db->GetLastRowId9);
else
  totalRows=totalRowsCurrent;
Is this the recommaned way?

Code: Select all

if(db->GetLastRowId()!=0)
totalRows=db->GetLastRowId();
else
  totalRows=totalRowsCurrent;
If you need to keep track of the number of rows in your table and you know you never delete rows then you might use

Code: Select all

select max(rowid) from yourtable;
to get the highest rowid present in the table. But this is only true if you don't interfer with the primary key yourself.

Certainly you could always use

Code: Select all

select count(rowid) from yourtable;
to determine the number of rows in a table but that is usually an expensive operation.

Another way to keep track of the number of rows of a table would be to introduce triggers, one fro inserts and one for deletes, and a table holding the row count of one or more other tables. The triggers would then increment resp. decrement the associated row count, and you could get the total number of rows of a table by querying the table rowcounts. Example:

Code: Select all

CREATE TABLE rowcounts (
  tablename char primary key,
  rowcount integer
);
INSERT INTO rowcounts VALUES ('my_table', 0);
CREATE TRIGGER insert_my_table AFTER INSERT ON my_table 
BEGIN
  UPDATE rowcounts SET rowcount = rowcount+1 WHERE tablename = 'my_table';
END;
CREATE TRIGGER insert_my_table AFTER DELETE ON my_table 
BEGIN
  UPDATE rowcounts SET rowcount = rowcount-1 WHERE tablename = 'my_table';
END;
...
-- Get total number of rows of table my_table:
SELECT rowcount FROM rowcounts WHERE tablename = 'my_table';
Regards,

Ulrich
samsam598
Super wx Problem Solver
Super wx Problem Solver
Posts: 340
Joined: Mon Oct 06, 2008 12:55 pm

Re: Tips to define db navigate functions

Post by samsam598 »

utelle wrote:If you need to keep track of the number of rows in your table and you know you never delete rows then you might use

Code: Select all

select max(rowid) from yourtable;
to get the highest rowid present in the table. But this is only true if you don't interfer with the primary key yourself.

Certainly you could always use

Code: Select all

select count(rowid) from yourtable;
to determine the number of rows in a table but that is usually an expensive operation.


Regards,

Ulrich
Thanks.Really appreciated.

Two more questions:P

1.How can I get the return value of rowid based on the above select statement?

Code: Select all

wxLongLong rowid;
db->ExecuteUpdate("select max(rowid) from car;");
Suppose now rowid has the desired value ,is this true?

2.How about wxSQLite3Table.GetRowCount()?Does this function returns proper value no matter deletion/failed insertion/no action occurs prior to calling this function?

Code: Select all

wxSQLite3Table table=db->GetTable("select * from car;");
wxLongLong recs=table.GetRowCount();
Regards,
Sam
-------------------------------------------------------------------
Windows 10 64bit
VS Community 2019
msys2-mingw13.2.0 C::B character set: UTF-8/GBK(Chinese)
wxWidgets 3.3/3.2.4 Unicode Mono Static gcc static build
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Tips to define db navigate functions

Post by utelle »

samsam598 wrote:1.How can I get the return value of rowid based on the above select statement?

Code: Select all

wxLongLong rowid;
db->ExecuteUpdate("select max(rowid) from car;");
Suppose now rowid has the desired value ,is this true?
For SQL selects you have to use method ExecuteQuery to get access to the results. In your case you have a select which returns a single scalar integer value. For this purpose wxSQLite3 provides the method ExecuteScalar

Code: Select all

int maxrow = db->ExecuteScalar("select max(rowid) from car;");
samsam598 wrote:2.How about wxSQLite3Table.GetRowCount()? Does this function returns proper value no matter deletion/failed insertion/no action occurs prior to calling this function?

Code: Select all

wxSQLite3Table table=db->GetTable("select * from car;");
wxLongLong recs=table.GetRowCount();
Yes, since the whole table is read into memory and kept there. But keep in mind that this method is really expensive in terms of time and memory. wxSQLite3Table exists for completeness of the wrapper to provide access to almost all SQLite API functions, but it's use is only advisable for small tables for which it is a desired behaviour to keep it in memory. One shortcoming of wxSQLite3Table is that all values are returned by SQLite in form of strings, i.e. numbers are always converted. These conversions might not always work as expected. In short the use of wxSQLite3Table is generally not recommended.

Regards,

Ulrich
samsam598
Super wx Problem Solver
Super wx Problem Solver
Posts: 340
Joined: Mon Oct 06, 2008 12:55 pm

Re: Tips to define db navigate functions

Post by samsam598 »

utelle wrote:
samsam598 wrote:1.How can I get the return value of rowid based on the above select statement?

Code: Select all

wxLongLong rowid;
db->ExecuteUpdate("select max(rowid) from car;");
Suppose now rowid has the desired value ,is this true?
For SQL selects you have to use method ExecuteQuery to get access to the results. In your case you have a select which returns a single scalar integer value. For this purpose wxSQLite3 provides the method ExecuteScalar

Code: Select all

int maxrow = db->ExecuteScalar("select max(rowid) from car;");
samsam598 wrote:2.How about wxSQLite3Table.GetRowCount()? Does this function returns proper value no matter deletion/failed insertion/no action occurs prior to calling this function?

Code: Select all

wxSQLite3Table table=db->GetTable("select * from car;");
wxLongLong recs=table.GetRowCount();
Yes, since the whole table is read into memory and kept there. But keep in mind that this method is really expensive in terms of time and memory. wxSQLite3Table exists for completeness of the wrapper to provide access to almost all SQLite API functions, but it's use is only advisable for small tables for which it is a desired behaviour to keep it in memory. One shortcoming of wxSQLite3Table is that all values are returned by SQLite in form of strings, i.e. numbers are always converted. These conversions might not always work as expected. In short the use of wxSQLite3Table is generally not recommended.

Regards,

Ulrich
Got it.Appreciated!!
Regards,
Sam
-------------------------------------------------------------------
Windows 10 64bit
VS Community 2019
msys2-mingw13.2.0 C::B character set: UTF-8/GBK(Chinese)
wxWidgets 3.3/3.2.4 Unicode Mono Static gcc static build
Post Reply