Database Navigation with SQLitening

[Rolf Brandt]

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:

• FIRST
• PREVIOUS
• NEXT
• LAST
• SEARCH

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

2010-08-16:
Upload NavDll.zip new, seemed to be corrupt.

[Marty Francom]

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.   

[Marty Francom]

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?

[Rolf Brandt]

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

[Rolf Brandt]

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

[Marty Francom]

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

[Paul Squires]

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.

[Paul Squires]

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.

[Marty Francom]

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.

[Rolf Brandt]

Hello Marty,

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.

If 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

[Paul Squires]

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:

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.

[Marty Francom]

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.

[Marty Francom]

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?

[Rolf Brandt]

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

[Paul Squires]

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.