Example SQLite/SQlitening program for FF3

[Martin Francom]

This is a program that Rolf Brandt and I collaborated on.  Rolf did all the SQLite database integration.  I just provided the GUI and requested Rolf to come up with a way to browse the database in a monor similar to Cheetah.  I think he did a great job with it.  This is what I asked Rolf to do:

That's great. Ok, attached is the FireFly-2 project.
The database needs the following fields in each record:

Company    (String - 30 characters)
NCPDP       (Numeric - 7 characters)
UserCount   (Numeric - 3 characters)
Name          (String - 30 characters)
Add-1         (String - 30 characters)
Add-2         (String - 30 characters)
City            (String - 30 characters)
State           (String - 30 characters)
Zip              (String - 10 characters)
Email           (String - 60 characters)
Phone          (Numeric -15 characters)
Fax              (Numeric -15 characters)
Cell             (Numeric -15 characters)
NPI             (String - 30 characters)
Optional      (String - 30 characters)
A-Exp         (Date - 10 characters)
L-Exp          (Date - 10 characters)
O-Exp         (Date - 10 characters)
A-Lic           (String - 1 character)
L-Lic           (String - 1 character)
O-Lic          (String - 1 character)
Note           (String - 1000 characters)

There needs to be six indexes:
   NCPDP
   Company
   A-Exp
   L-Exp
   O-Exp
   State

The program will allow selecting index to be used by putting a check on
the desired index in the "Sort Option CheckBoxes"

The program will allow browsing through the database using a "Get Previous" or "Get Next" routine using the selected index.

The program will allow to search for a desired record. If found will display
that record as the current active record. If not found will display the record
that is the closest match to the search request as the current record.

The program will display the Current active record in the fields at the top
of the main window and at the bottom of the main window the program will a show a composite line of 7 records. The Current record, 3 records previous and 3 records next from the current record. The Up/Down Control will allow the user to browse up or down through the database based on the index currently selected.

The program will allow the user to create New records, Edit records and
delete records.

    I have attached two zip files one contains the project files and the other contains a working copy of the compiled project and all files necessary for it to run in local mode.  You can make changes to it's INI file if you want to test it in Client/Server mode.  See Rolf's instructions in the PDF file.
    Note: when you run the program do a search for  "F" to get the data base to load.  That will put you in the middle of the database.  And, you will be able to Browse up and down through the database using the up down buttons.  You can switch the index being used by the bullet control.

   If you compile the project you will need to use version 1.20 of SQLitening.  We are having a problem getting it to compile with version 1.30 of SQLitening.

   Feel free to use this example any way you like.  We hope that this example program will be helpful as others try to learn SQLite.  If any one has any suggestions for improvement please post.

Paul,  If you can suggest what we need to do to get this project to compile using SQLitening 1.30 that would be greatly appreciated.

[Martin Francom]

Rolf,  here's a couple questions:

When running the AdBook program I noticed:

1)  When changing the index to a non-unique  index like A-Exp the browse routine does not stop at each record.  It will stop at one date and then skip any additional records that have that data and go to the next date.   This behavior is because the index item is not unique.  What is the best way to fix that?
   I noticed that you have a record number, can the index for A-Exp  (etc.) be changed to be a combination of  A-Exp + Rec#   How would I go about to make that change?

   I suppose it would be best to make all indexes unique as to prevent this problem.  Would combining the "key field + Rec#" be the best way to create a unique index?

2) When browsing the records, when you reach the end and try to go 1 record beyond the list gets cleared.  Is there a way to stop at the end (or beginning) and stay at that point, possibly playing beep sound to indicate end of index ? 

[Rolf Brandt]

Hi Marty,

When changing the index to a non-unique  index like A-Exp the browse routine does not stop at each record.  It will stop at one date and then skip any additional records that have that data and go to the next date.   This behavior is because the index item is not unique.  What is the best way to fix that?

I'll have a look at that.

I noticed that you have a record number, can the index for A-Exp  (etc.) be changed to be a combination of  A-Exp + Rec#   How would I go about to make that change? I suppose it would be best to make all indexes unique as to prevent this problem.  Would combining the "key field + Rec#" be the best way to create a unique index?

The record number is SQLite's intrinsic rowid. An index can of course be a combination of more than one field, and rowid is just an automatic generated field.

I suppose it would be best to make all indexes unique as to prevent this problem.  Would combining the "key field + Rec#" be the best way to create a unique index?

When browsing the records, when you reach the end and try to go 1 record beyond the list gets cleared.  Is there a way to stop at the end (or beginning) and stay at that point, possibly playing beep sound to indicate end of index ? 

Yes - I just kept the code as simple and basic as possible. All we need to do is check if there is a row, and if there is not than simply do not clear the text boxes.

Rolf

[Martin Francom]

Rolf
  Is this how I show code the Index to ensure the index items are uniqe?

Code Select Expand


   t1 = "CREATE TABLE Customers ("   & Flds & ");"
   slExe "Begin"  'This starts the transaction
      slExe t1, "E0"
      slExe "CREATE INDEX NCPDP ON Customers (NCPDP & rowid);", "E0"
      slExe "CREATE INDEX Company ON Customers (Company & rowid);", "E0"
      slExe "CREATE INDEX [A-Exp] ON Customers ([A-Exp] & rowid);", "E0"
      slExe "CREATE INDEX [L-Exp] ON Customers ([L-Exp] & rowid);", "E0"
      slExe "CREATE INDEX [O-Exp] ON Customers ([O-Exp] & rowid);", "E0"
      slExe "CREATE INDEX State ON Customers (State & rowid);", "E0"
   slExe "End"
   


This compiles and runs but it browsing the database still has the problem of skipping records.  Do I need to do to correct the browse feature?

Also, Is it posible to set the index to UPPERCASE  so the browsing will be correct reguardless of when the field was entered in upper or lower case.   

Would this be the way to write the command?

Code Select Expand


slExe "CREATE INDEX Company ON Customers UPPERCASE(Company & rowid);", "E0"


This does seem to work.  What am I doing wrong?

[Paul Squires]

Would this be the way to write the command?
Code: [Select]slExe "CREATE INDEX Company ON Customers UPPERCASE(Company & rowid);", "E0"

You would use the SQLite builtin function Upper rather than UpperCase.

Here is a list of SQLite functions (from the SQlitening.chm Help file):

abs(X) Return the absolute value of argument X.


coalesce(X,Y,...) Return a copy of the first non-NULL argument. If all arguments are NULL then NULL is returned. There must be at least 2 arguments.


glob(X,Y) This function is used to implement the "X GLOB Y" syntax of SQLite. The sqlite3_create_function() interface can be used to override this function and thereby change the operation of the GLOB operator.


ifnull(X,Y) Return a copy of the first non-NULL argument. If both arguments are NULL then NULL is returned. This behaves the same as coalesce() above.


last_insert_rowid() Return the ROWID of the last row insert from this connection to the database. This is the same value that would be returned from the sqlite_last_insert_rowid() API function.


length(X) Return the string length of X in characters. If SQLite is configured to support UTF-8, then the number of UTF-8 characters is returned, not the number of bytes.


like(X,Y [,Z]) This function is used to implement the "X LIKE Y [ESCAPE Z]" syntax of SQL. If the optional ESCAPE clause is present, then the user-function is invoked with three arguments. Otherwise, it is invoked with two arguments only. The sqlite_create_function() interface can be used to override this function and thereby change the operation of the LIKE operator. When doing this, it may be important to override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement the LIKE operator depending on whether or not an ESCAPE clause was specified.


lower(X) Return a copy of string X will all characters converted to lower case. The C library tolower() routine is used for the conversion, which means that this function might not work correctly on UTF-8 characters.


max(X,Y,...) Return the argument with the maximum value. Arguments may be strings in addition to numbers. The maximum value is determined by the usual sort order. Note that max() is a simple function when it has 2 or more arguments but converts to an aggregate function if given only a single argument.


min(X,Y,...) Return the argument with the minimum value. Arguments may be strings in addition to numbers. The minimum value is determined by the usual sort order. Note that min() is a simple function when it has 2 or more arguments but converts to an aggregate function if given only a single argument.


nullif(X,Y) Return the first argument if the arguments are different, otherwise return NULL.


quote(X) This routine returns a string which is the value of its argument suitable for inclusion into another SQL statement. Strings are surrounded by single-quotes with escapes on interior quotes as needed. BLOBs are encoded as hexadecimal literals. The current implementation of VACUUM uses this function. The function is also useful when writing triggers to implement undo/redo functionality.


random(*) Return a random integer between -2147483648 and +2147483647.


round(X)


round(X,Y) Round off the number X to Y digits to the right of the decimal point. If the Y argument is omitted, 0 is assumed.


soundex(X) Compute the soundex encoding of the string X. The string "?000" is returned if the argument is NULL. This function is omitted from SQLite by default. It is only available the -DSQLITE_SOUNDEX=1 compiler option is used when SQLite is built.


sqlite_version(*) Return the version string for the SQLite library that is running. Example: "2.8.0"


substr(X,Y,Z) Return a substring of input string X that begins with the Y-th character and which is Z characters long. The left-most character of X is number 1. If Y is negative the the first character of the substring is found by counting from the right rather than the left. If SQLite is configured to support UTF-8, then characters indices refer to actual UTF-8 characters, not bytes.


typeof(X) Return the type of the expression X. The only return values are "null", "integer", "real", "text", and "blob". SQLite's type handling is explained in Datatypes in SQLite Version 3.


upper(X) Return a copy of input string X converted to all upper-case letters. The implementation of this function uses the C library routine toupper() which means it may not work correctly on UTF-8 strings.


In any aggregate function that takes a single argument, that argument can be preceeded by the keyword DISTINCT. In such cases, duplicate elements are filtered before being passed into the aggregate function. For example, the function "count(distinct X)" will return the number of distinct values of column X instead of the total number of non-null values in column X.


avg(X) Return the average value of all non-NULL X within a group. Non-numeric values are interpreted as 0.


count(X)


count(*) The first form return a count of the number of times that X is not NULL in a group. The second form (with no argument) returns the total number of rows in the group.


max(X) Return the maximum value of all values in the group. The usual sort order is used to determine the maximum.


min(X) Return the minimum non-NULL value of all values in the group. The usual sort order is used to determine the minimum. NULL is only returned if all values in the group are NULL.


sum(X) Return the numeric sum of all numeric values in the group. If there are no input rows or all values are NULL, then NULL is returned. NULL is not a helpful result in that case (the correct answer should be zero) but it is what the SQL standard requires and how most other SQL database engines operate so SQLite does it that way in order to be compatible. You will probably want to use "coalesce(sum(X),0)" instead of just "sum(X)" to work around this design problem in the SQL language.

[Martin Francom]

I put the UPPER clause in this statement and it caused a run time SQL error.

Code Select Expand


Sql1 = "SELECT rowid, * FROM Customers WHERE [" & sFld & "] " & direction & " '" & sTxt & "' ORDER BY UPPER[" & sFld & "]"


error message:
   1 = near "[Company]" ; syntax error

   Statement = Sel SELECT rowid, FROM Customers WHERE [Company] >= 'A' ORDER BY UPPER[Company] LIMIT 1

[Rolf Brandt]

Marty,

you need to put normal (round) brackets around the field name if you use UPPER. The sqare brackets are just used to show what is the field name. You need square brackets for field names if the field name contains dashes or spaces, like [A-Exp].

The UPPER function looks like this: UPPER(fieldname) or UPPER([fieldname]).

The query should look like this:
Sql1 = "SELECT rowid, * FROM Customers WHERE [" & sFld & "] " & direction & " '" & sTxt & "' ORDER BY UPPER([" & sFld & "])"

Rolf

[Martin Francom]

Marty,

you need to put normal (round) brackets around the field name if you use UPPER. The sqare brackets are just used to show what is the field name. You need square brackets for field names if the field name contains dashes or spaces, like [A-Exp].

The UPPER function looks like this: UPPER(fieldname) or UPPER([fieldname]).

The query should look like this:
Sql1 = "SELECT rowid, * FROM Customers WHERE [" & sFld & "] " & direction & " '" & sTxt & "' ORDER BY UPPER([" & sFld & "])"

Rolf

Rolf,
   That is exactly as I have it.  But it generates this error message:

error message:
   1 = near "[Company]" ; syntax error

   Statement = Sel SELECT rowid, FROM Customers WHERE [Company] >= 'A' ORDER BY UPPER[Company] LIMIT 1

[Rolf Brandt]

No Marty, take a close look at the UPPER function. You have only the sqare brackets, the outer round bracket are missing.

Rolf

[Paul Squires]

Personally, I would drop the use of the square brackets altogether.

[Rolf Brandt]

Then Marty would need to change some of his field names. For example "A-Exp" works only with square brackets, "A_Exp" would work without.

The field name "Company" works of course without the square brackets.

[Paul Squires]

A yes, right you are. I rarely use weird characters in my column names.

[Martin Francom]

No Marty, take a close look at the UPPER function. You have only the sqare brackets, the outer round bracket are missing.

Rolf

Ok. the fixed that problem.  Thanks.

But the browse  UP/Down through the index still is not functioning properly. I skips records that have the same value.  I tried making the index items unique  by creating the Index key  to be the  Key-Field plus ROWID.    As follows:

      slExe "CREATE INDEX Company ON Customers UPPER ((Company & rowid));", "E0"
      slExe "CREATE INDEX [A-Exp] ON Customers ([A-Exp] & rowid);", "E0"
      slExe "CREATE INDEX [L-Exp] ON Customers ([L-Exp] & rowid);", "E0"
      slExe "CREATE INDEX [O-Exp] ON Customers ([O-Exp] & rowid);", "E0"
      slExe "CREATE INDEX State ON Customers UPPER ((State & rowid));", "E0"

Should I be doing that differently?   Or, is there some other change I need to make in the program at some different location in the program?

[Martin Francom]

Then Marty would need to change some of his field names. For example "A-Exp" works only with square brackets, "A_Exp" would work without.

The field name "Company" works of course without the square brackets.

I will change the column names to get rid of the - in the name.  And also get rid of the brackets [ ]  to conform .    But I am not sure how to get the browsing to work correctly.

[Martin Francom]

Ok, since I can't figure out how to make the index unique by using the rowid as part of the index key.

Maybe I can make the key field unque at time of record creation.  How would you suggest I do that?    I was thinking of adding a record number to the end of the key field.  But the the problem then becomes how do I know what the last record number in the file is?   (so i can increment it)

If I have a field in the record called  "uniquenumber"   Is there an SQL statemnt I could use to get the last record added?    then I could read the uniquenumber field for that last record added and increment the number by one and use that number as the next uniquenumber.

Or am I just going about this all  bass-ackwards ?
Any suggestions appreciated.