SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Rolf Brandt on June 30, 2009, 12:15:45 pm

Title: Database Navigation with SQLitening
Post by: Rolf Brandt on June 30, 2009, 12:15:45 pm
Here is a little function to enable database navigation similar to Cheetah or DAO. Notice though that you are not really working with a recordset, this function just fakes it. In every day use you might be better of with the slSelAry function of SQLitening. But in cases there you could be running out of memory this might be a way out. 

The function is implemented as Dll and Include file.

It offers five ways to navigate:


There are two sample programs, one for using the include file and one for use with the dll. All files come as source code and in compiled form. Help file and Pdf are also included.

Rolf

See also this thread:
http://planetsquires.com/support/index.php?topic=2990.0 (http://planetsquires.com/support/index.php?topic=2990.0)

2010-08-16:
Upload NavDll.zip new, seemed to be corrupt.
Title: Re: Database Navigation with SQLitening
Post by: Marty Francom on July 24, 2009, 01:24:58 pm
This is wonderful!  I have a database I would like to move to SQLitening but absolutely need the Navagation functions you have provided.  Maybe now we can put SQLitening to work!
Thanks you, thank you, thank you!

How hows the search function work. That is, does it return the closest record matching the search string?   Say you have a database containing:
   apple orange pear strawberry
and you did a search for  peach  would it bring the closest record that is < or =  the reacord being search for?  In other words return   orange.    Or, does it not return any thing?

I would want at least to types of search:
    Search=    which would only return an exact match
    Search<=  which would return the record that is the
                    closest to the record being search if the
                     searched for record does not exist.   
Title: Re: Database Navigation with SQLitening
Post by: Marty Francom on July 24, 2009, 02:16:56 pm
Rolf,
  Tried to run your example program TestDLL.exe, the program loads but when I click on a navagate button I get the message:
  "Can not load "SQLitening Client.DLL""

Any suggestion?
Title: Re: Database Navigation with SQLitening
Post by: Rolf Brandt on July 24, 2009, 04:05:37 pm
Hello Marty,

do you have the SQLiteneing Dll's in the program folder? if you work in Server mode you would need:

SQLitening.Dll
SQLiteningClient.Dll
Zlib.Dll

If you are in local mode you also need to have:
SQLite3.Dll

Rolf


Title: Re: Database Navigation with SQLitening
Post by: Rolf Brandt on July 24, 2009, 04:40:38 pm
How does the Search Function work?

The Search Function is implemented with the LIKE %SearchText% operator in this case. It would be no problem to extend the function with an extra parameter for different operators like:

=  |  >   |   >=  |   <   |   <= 

Rolf
Title: Re: Database Navigation with SQLitening
Post by: Marty Francom on July 24, 2009, 09:06:49 pm
Still not running.  I made sure all the DLL's you listed are in the NavDLL directory.   I don't see a database file.  Did you include a database file in the NavDLL.zip?

It might be simpler if you just included all the files needed to run the TestDLL.exe  in the NavDLL.zip ???
Title: Re: Database Navigation with SQLitening
Post by: Paul Squires on July 24, 2009, 09:51:23 pm
The database file (sample.db3) comes in the SQLitening download package.

When you run the program, blank out the field called "connection". It currently has a hard coded IP address (192.168.178.33). If you are running client/server then you would need the ip address. Leave it blank in order to run in local mode.
Title: Re: Database Navigation with SQLitening
Post by: Paul Squires on July 24, 2009, 09:57:19 pm
Rolf's implementation is pretty good but my only concern is that a new SQL statement needs to be compiled and run every time you click on First/Next/Previous/Last. I would be more inclined to use Rolf's advice and save a larger recordset using slSelAry and iterate that array. I guess you could test it both ways.

I will be using SQLitening in my new accounting program.... but I can't get 100% into that program until FireFly 3 is finally released.

I think that SQLitening is an absolute fantastic program and I wish that we had it years ago. :)
Title: Re: Database Navigation with SQLitening
Post by: Marty Francom on July 25, 2009, 01:28:32 am
Paul,
   Thanks.  Found the database.  Now when I start the TestDLL program. I loads one record.  When I click on any of the navigation buttons.  I get a message "Program Not Responding".  Then after a bit I get a message:
    -16 = Can not connect
    State = Connect -- Server=192.168.178.33 Port=0

I admit I am near clueless when it comes to using SQLite.  If it would help and can list the files I have in the directory.
Title: Re: Database Navigation with SQLitening
Post by: Rolf Brandt on July 25, 2009, 02:11:02 am
Hello Marty,

Quote from: Marty Francom on July 25, 2009, 01:28:32 am
Paul,
  Then after a bit I get a message:
    -16 = Can not connect
    State = Connect -- Server=192.168.178.33 Port=0


Remove the the Server address "192.168.178.33" which I had hard coced in the TestDll.bas file. This is the line:
control add textbox, hDlg, %IDTXTCONN, "192.168.178.33", 300, 25, 100, 14

change it to:
control add textbox, hDlg, %IDTXTCONN, "", 300, 25, 100, 14

Or simply delete the server adress after program start before doing anything else. "192.168.178.33" is the adress of my Win7 server in my LAN, so it would not work in your environment. I just hard coded it because I was to lazy to type it in every time. If the server address is "" the the program will not try to connect and work local.


Quote from: Marty Francom on July 25, 2009, 01:28:32 amIf it would help and can list the files I have in the directory.


You would need to have the following files in the folder (without the code files):

TestDLL.exe
TestInc.exe
navdll.dll
SQLite3.Dll
SQLitening.Dll
SQLiteningClient.Dll
Zlib.Dll
sample.db3
sqltngnav.chm


For your convenience I have added a zip with all files. I also removed the Server address. Just copy the files into a folder and start the program. It should work.

Rolf
Title: Re: Database Navigation with SQLitening
Post by: Paul Squires on July 25, 2009, 04:06:35 am
Quote from: Marty Francom on July 25, 2009, 01:28:32 am
Paul,
   Thanks.  Found the database.  Now when I start the TestDLL program. I loads one record.  When I click on any of the navigation buttons.  I get a message "Program Not Responding".  Then after a bit I get a message:
    -16 = Can not connect
    State = Connect -- Server=192.168.178.33 Port=0

I admit I am near clueless when it comes to using SQLite.  If it would help and can list the files I have in the directory.


You're almost there Marty. You must have skipped over the second part of my post that read as follows:
Quote
When you run the program, blank out the field called "connection". It currently has a hard coded IP address (192.168.178.33). If you are running client/server then you would need the ip address. Leave it blank in order to run in local mode.

Title: Re: Database Navigation with SQLitening
Post by: Marty Francom on July 25, 2009, 01:58:20 pm
Rolf & Paul,
   Thanks for your patience and help.  Sorry I am such a bone head. It's working now.  It took me a while to figure out what was being indexed. The display is a bit crowded.  But now I think I see what's going on.

   What I would like to be able to do is mimmick what we do in Cheetah.  We have a listing that displays customers records.  The active record is displayed in the center of the listing and displayed above it are the previous 5 records (alphabetically indexed) and below it are the next 5 records.  This allow the user to see the records near the active record in alphbetical order. And then with the next/previous commands the user can browser through the records easily.  The search command allows the user to go to the active that is (or is near) the record for which he is searching.
   Rolf, I think your navigation tool for SQLitening may allow us to build a similar feature. Thank you.
Title: Re: Database Navigation with SQLitening
Post by: Marty Francom on July 25, 2009, 07:57:14 pm
Rolf,
   I have looked a bit at the code. And have a a few questions:

How does database size effect the operation / speed of this method? 
Would it hand a million record database as quickly as a 1000 record database?
What would be the limitations (if any) of this method?
Title: Re: Database Navigation with SQLitening
Post by: Rolf Brandt on July 26, 2009, 01:30:31 am
Hello Marty,

there should not be too much of a difference in speed. Also in Client/Server mode the size of the database would not matter since all operations are done on the server side, and the amount of data that is transported is very small.

I can't think of any limitations.

Rolf
Title: Re: Database Navigation with SQLitening
Post by: Paul Squires on July 26, 2009, 10:03:37 am
The performance would be very good especially if you create the correct indexes on the data so that the SQL engine uses it to optimize the SQL processing.
Title: Re: Database Navigation with SQLitening
Post by: Gafny Jacob on August 16, 2010, 06:49:46 am
The attached zip file seems to be corrupt, can someone please mail me a good one. Thx
Title: Re: Database Navigation with SQLitening
Post by: Rolf Brandt on August 16, 2010, 07:02:14 am
Hello Jacob,

I reuploaded the zip file. Should work now.

Rolf
Title: Re: Database Navigation with SQLitening
Post by: Gafny Jacob on August 16, 2010, 07:10:58 am
Thanks.
Title: Re: Database Navigation with SQLitening
Post by: cj on July 09, 2014, 08:27:44 am
If there are duplicates in a column wouldn't the previous and next always get the same record?

IF TRIM$(SearchTxt) = "" THEN
    SELECT CASE Direction
      CASE "<<"
        t1 = ""
        t2 = " LIMIT 1"
      CASE "<"
        T1 = " AND " & SrtFld & " " & direction & " '" & RecVal & "'"
        t2 = " DESC LIMIT 1"
      CASE ">"
        t1 = " AND " & SrtFld & " " & direction & " '" & RecVal & "'"
        t2 = " LIMIT 1"
      CASE ">>"
        t1 = ""
        t2 = " DESC LIMIT 1"
      CASE ELSE

    END SELECT
    t = "SELECT " & Flds & " FROM " & Tbl & " WHERE "   & WhereStr & _
      t1 & " ORDER BY " & SrtFld & t2

  ELSE
    t = "SELECT " & Flds & " FROM " & Tbl & " WHERE "
    IF TRIM$(WhereStr) = "" THEN
      t = t & SrtFld & " LIKE '%" & SearchTxt & "%' LIMIT 1"
    ELSE
      t = t & WhereStr &" AND " & SrtFld & " LIKE '%" & SearchTxt & "%' LIMIT 1"
    END IF
END IF                 

Title: Re: Database Navigation with SQLitening
Post by: Rolf Brandt on July 09, 2014, 09:38:06 am
CJ - you are perfectly correct! Have to make some changes to it.

rb
Title: Re: Database Navigation with SQLitening
Post by: cj on July 09, 2014, 11:09:25 am
Not sure if SQLite added cursors.  See some examples using them on the net.
This thread is about not using them and using slSelAry instead.
http://www.sqlitening.com/support/index.php?topic=2636.0 


--FIRST and LAST is easy, but previous and next?
--column + rowid search needs to be worked out for previous and next?

create table if not exists t1(f1 integer primary key autoincrement, f2);
create index if not exists index_name on t1 (f2);

-- Insert 'A' 3-times into column f2
insert into t1 (f1,f2) values (null,'A'),(null,'A'),(null,'A');

-- (get last) order by f2,f1 so descending if no index;
select 'LAST ' ,f2,rowid from t1 order by f2,f1 desc LIMIT 1;

-- (get first);
select 'FIRST' ,f2,rowid from t1 order by f2 ASC LIMIT 1;   

results:
LAST   A  3 
--------------------------------------------------
FIRST  A  1