‘ SQLite Examples for new users

 

‘ This was written by a new user of SQLite in an effort to help others not familiar with SQLite

‘ become productive with minimum time spent on researching its use. There may be, and probably

‘ are, better ways to do this; but this works. The following code was lifted from a working program

‘ and modified for this example, but was not run in a test. Errors, if any, should be easy to correct

‘ when testing. It was written in NSBasic/CE and has examples on how to:

 

' create, read, write, rewrite, sort and delete specific records within a file, called a Table in SQLite

 

‘ Note that the terminology used in SQLite is different from other access methods:

TABLE is used instead of FILE

INSERT OR REPLACE is used instead of WRITE or REWRITE

SELECT is used instead of READ

‘ I use RECORD to describe one unique set of FIELDS or ELEMENTS within a TABLE [File]

 

' Green is used for comments

' Blue is used for Sub names and Sub exits

 

' The following are the Public variables used for this example:

 

Public SQLiteString

Public DatabaseObject

Public Current_Record_Set

Public Current_Record_Number

Public DoubleQuotes

Public SingleQuotes

Public NameToBeDeleted

Public HoldListBoxLine

 

Public ConstantNull

Public ConstantThreeZeros

Public Today_YYYYMMDD

Public Today_MM

Public Today_DD

 

' Table [File] structure where data from the Table is moved after it is accessed

‘ You must do the moving – see Read_ENTIRE_NAME_TABLE

 

Public Element_NameFirstLast

Public Element_YearMonthDayYYYYMMDD

Public Element_Territory

Public Element_Area

Public Element_CharacterField1

Public Element_CharacterField2

Public Element_CharacterField3

Public Element_NumericField1

Public Element_NumericField2

Public Element_NumericField3

Public Element_Available1

Public Element_Available2

Public Element_Available3

 

Sub Screen_Load

 

   ConstantNull = “”

   ConstantThreeZeros = “000”

DoubleQuotes = Chr(34)         ' equals "

   SingleQuotes = Chr(39)         ' equals '

 

‘ Get today’s date in YYYMMDD format

 

   Today_MM = Month(Now)

  

   If Len(Today_MM) < 2 Then

      Today_MM = "0" & Today_MM

   End If

  

   Today_DD = Day(Now)

  

   If Len(Today_DD) < 2 Then

      Today_DD = "0" & Today_DD

   End If

       

   Today_YYYYMMDD = Year (Now) & Today_MM & Today_DD

 

' Add database – The Table [or File] is named NAME, but can be called anything. There

‘ can be (and usually are) many different Tables within a database, each with a varying number

‘ of Fields or Elements.

‘ NOTE:

' On Error Goto 0 disables error handling routine

' On Error Resume Next executes next statement regardless

 

‘ In testing I found this necessary to use in case the database was not closed due to an abort:

  

On Error resume next                                

DatabaseObject.close

On Error Goto 0

 

‘ Note: It is not necessary to name this object “DatabaseObject”. It can be named anything, but must

‘ be declared Public as follows:

‘ Public SomethingILikeToCallIt

  AddObject "newObjects.sqlite3.dbutf8"," SomethingILikeToCallIt "

‘ which will also work

 

AddObject "newObjects.sqlite3.dbutf8","DatabaseObject"

     

If DatabaseObject.Open("DatabaseObject") Then 

        DatabaseObject.AutoType = True

        DatabaseObject.TypeInfoLevel = 4

Else

        MsgBox "Error on DatabaseObject.Open: " & DatabaseObject.lastError & "Contact Vendor"

        Call Stop_Program

End If

 

On Error resume next

Set Current_Record_Set=DatabaseObject.Execute("Select * from NAME")

On Error Goto 0 

 

   ' Create the database if it doesn't exist yet - should be executed only ONCE

  

If Left(DatabaseObject.lasterror, 13)="no such table" Then   

Call CreateDatabase     

End If

  

End Sub

 

'       C   R   E   A   T   E        D   A   T   A   B   A   S   E

'       C   R   E   A   T   E        D   A   T   A   B   A   S   E

 

Sub CreateDatabase

 

'       N   A   M   E       T   a   b   l   e  

 

‘ Note: On my computer I had trouble with the continuation bar: _ If that happens, do not use them

‘ and put everything on one line in the NS Basic editor

‘ Just start adding TABLES

‘ It is not necessary to name the Fields or Elements starting with “SQL_”. This was done for clarity in the

‘ program and to distinguish them from Public variables where they are moved after the SELECT [READ]

‘ Public variables start with “Element_” in this example

 

‘ This is how to delete a TABLE, useful to have a button to do this when testing:

 

On Error resume next

Set Current_Record_Set=DatabaseObject.Execute("DROP TABLE If EXISTS NAME") 

 

‘ This creates a TABLE named NAME

  

SQLiteString="CREATE TABLE NAME _

("" SQL_NameFirstLast"", _

"" SQL_YearMonthDayYYYYMMDD"", _

"" SQL_Territory "", _

"" SQL_Area"", _

"" SQL_CharacterField1"", _

"" SQL_CharacterField2"", _

"" SQL_CharacterField3"", _

      "" SQL_NumericField1"", _

"" SQL_NumericField2"", _

"" SQL_NumericField3"", _

      "" SQL_Available1"", _

"" SQL_Available2"", _

"" SQL_Available3"", _

   PRIMARY KEY(""SQL_YearMonthDayYYYYMMDD ""))"

  

On Error resume next

Set Current_Record_Set=DatabaseObject.Execute(SQLiteString)

On Error Goto 0

  

If DatabaseObject.lasterror<>"" Then

MsgBox "Create NAME Table error " & DatabaseObject.lasterror & " Contact Vendor”

Call Stop_Program

End If

  

‘ This INSERTS [or Writes a record] into the NAME TABLE [or File] just created with all values

‘ Note than INSERT OR REPLACE will do just that. This can be used as a WRITE or REWRITE

‘ in terminology used with other access methods

 

For illustrative purposes only, the Public variables which will be moved to the record are initialized,

‘ then the record is written to the TABLE, then we SELECT [or READ] what we just wrote:

 

   Call Initialize_Public_Elements  

   Call Write_NAME_TABLE

   SQLiteString="SELECT * From NAME"

Call Read_ENTIRE_NAME_TABLE

 

‘ This will display only one name, as we have only one record on file:

 

   If Current_Record_Set.Count > 0 Then

For NAME_TABLE_INDEX = 1 to Current_Record_Set.count

      Msgbox “Element_FirstLastName=” & Element_FirstLastName

Next

   End If

     

End Sub

 

 

‘ Main routine example <<<<<<<<<<<<<<<<<<<<<<<<<

‘ Main routine example <<<<<<<<<<<<<<<<<<<<<<<<<

 

Sub Main_Routine

 

‘ This code is for illustrative purposes only and does not follow a logical flow

 

‘ You can put command buttons to allow the user to sort as follows:

‘ To read TABLE sorted by third Field or Element [SQL_Territory]:

 

   SQLiteString="SELECT * From ""NAME"" ORDER BY 3"

 

   Call Read_ENTIRE_NAME_TABLE

  

   ListBox15_Screen15.Clear

   ListBox15_Screen15.Height = 100

  

   If Current_Record_Set.Count > 0 Then

      For Current_Record_Number = 1 To Current_Record_Set.Count

     

        Element_NameFirstLast = Current_Record_Set (Current_Record_Number)(1)

        Element_Territory = Current_Record_Set (Current_Record_Number)(3)

        Element_Area = Current_Record_Set (Current_Record_Number)(4)

             

        HoldListBoxLine = Element_Territory & " -- " & Element_Area & " -- " & Element_NameFirstLast

        ListBox15_Screen15.AddItem HoldListBoxLine

 

      Next

   End If

 

‘ To read TABLE sorted by Fourth Field or Element [SQL_Area]:

 

   SQLiteString="SELECT * From ""NAME"" ORDER BY 4"

 

‘ NOTE: Because this example uses a variable defined as Public SQLiteString to pass Information

‘ for an INSERT or SELECT it is necessary to not use the double quote [“] in order for it to be

‘ interpreted properly. One way to avoid a user from entering a double quote in a text field

‘ is to change it to a single quote before the data is send to SQLite as follows:

 

   If Len(Txt44_Name.Text) > 0 Then

      Txt44_Name.Text = Replace(Txt44_Name.Text, DoubleQuotes,SingleQuotes)

   End If

 

‘ To DELETE a specific record:

 

   NameToBeDeleted = “NoName”

 

   SQLiteString = "DELETE FROM ""NAME"" WHERE "" SQL_NameFirstLast "" =  """ & NameToBeDeleted & """"

  

   On Error resume next 

   Set Current_Record_Set=DatabaseObject.Execute(SQLiteString)

   On Error Goto 0 

 

‘ You may NOT want this check:

       

   If DatabaseObject.lasterror<>"" Then

      MsgBox "DELETE NAME Table error " & DatabaseObject.lasterror & " Contact Vendor - Program must quit"

      Call Stop_Program

   End If

  

   MsgBox NameToBeDeleted & vbCrLf & "permanently deleted",vbInformation,"Informative"

 

‘ To retrieve records with specific data in a FIELD or ELEMENT:

 

SQLiteString="SELECT * From ""NAME"" Where "" SQL_Area "" LIKE (""" & Element_Area & """)"

     

‘ To retrieve records, sorted, with specific data in a FIELD or ELEMENT:

 

SQLiteString = "SELECT * From ""NAME"" Where ""SQL_Area"" LIKE (""" & Element_Area & """) ORDER BY 1"

  

End Sub

 

' READ Entire NAME File

' READ Entire NAME File

' READ Entire NAME File

 

Sub Read_ENTIRE_NAME_TABLE

     

   On Error resume next

   Set Current_Record_Set=DatabaseObject.Execute(SQLiteString)

   On Error Goto 0 

  

   If Current_Record_Set.count = 0 Then

      Exit Sub

   End If

       

   If DatabaseObject.lasterror<>"" Then

      MsgBox "SELECT NAME Table error " & DatabaseObject.lasterror & " Contact Vendor - Program must quit"

      Call Stop_Program

   End If

  

‘ You do not want to use [Current_Record_Set (Current_Record_Number)(1), (2),… (7), etc.] in your program if

‘ possible. It is much more difficult to determine which element you are accessing and should you change the ‘

‘ record for any reason all references must be changed throughout the program. Better to set each to a variable

‘ as follows:

 

   Current_Record_Number = 1 ‘ Get first record – This is used for below moves

  

   Element_FirstLastName = Current_Record_Set (Current_Record_Number)(1)

   Element_StartDateYYYYMMDD = Current_Record_Set (Current_Record_Number)(2)

   Element_Territory = Current_Record_Set (Current_Record_Number)(3) 

   Element_Area = Current_Record_Set (Current_Record_Number)(4)

 

   Element_CharacterField1 = Current_Record_Set (Current_Record_Number)(5)

   Element_CharacterField2 = Current_Record_Set (Current_Record_Number)(6)

   Element_CharacterField3 = Current_Record_Set (Current_Record_Number)(7)

 

   Element_NumericField1 = Current_Record_Set (Current_Record_Number)(8)

   Element_NumericField2 = Current_Record_Set (Current_Record_Number)(9)

   Element_NumericField3 = Current_Record_Set (Current_Record_Number)(10)

  

   Element_Available1 = Current_Record_Set (Current_Record_Number)(11)

   Element_Available2 = Current_Record_Set (Current_Record_Number)(12)

   Element_Available3 = Current_Record_Set (Current_Record_Number)(13)

     

End Sub

 

' WRITE NAME File

' WRITE NAME File

' WRITE NAME File

 

‘ The “SQL_” names are those used when the TABLE was created and are necessary for this INSERT

‘ The “Element_” names are Public variables used to hold the values to be inserted into the record

 

Sub Write_NAME_TABLE

 

On Error resume next

SQLiteString="INSERT OR REPLACE INTO ""NAME""   _ 

(""SQL_FirstLastName"", _

""SQL_StartDateYYYYMMDD"", _

""SQL_Territory"", _

""SQL_Area"", _

""SQL_ CharacterField1"", _

""SQL_ CharacterField2"", _

""SQL_ CharacterField3"", _

""SQL_ NumericField1"", _

""SQL_ NumericField2"", _

""SQL_ NumericField3"", _

""SQL_ Available1"", _

""SQL_ Available2"", _     

""SQL_ Available3"") _

VALUES ("""& Element_NameFirstLast &""", _

"""& Element_YearMonthDayYYYYMMDD &""", _

"""& Element_Territory &""", _

"""& Element_Area &""", _

"""& Element_CharacterField1 &""", _

"""& Element_CharacterField2 &""", _

"""& Element_CharacterField3 &""", _

"""& Element_NumericField1 &""", _

"""& Element_NumericField2 &""", _

"""& Element_NumericField3 &""", _

"""& Element_ Available1 &""", _

"""& Element_ Available2 &""", _

“””& Element_Available3 &""")"

 

Set Current_Record_Set=DatabaseObject.Execute(SQLiteString)

On Error Goto 0

     

If DatabaseObject.lasterror<>"" Then

MsgBox "INSERT NAME Table error " & DatabaseObject.lasterror & " Contact Vendor”

Call Stop_Program

End If

  

End Sub

 

‘ Initialize Element Public variables

‘ Initialize Element Public variables

 

Sub Initialize_Public_Elements

 

Element_NameFirstLast = “NoName”

Element_YearMonthDayYYYYMMDD = Today_YYYYMMDD

Element_Territory = ConstantThreeZeros

Element_Area = ConstantThreeZeros

Element_CharacterField1 = ConstantNull

Element_CharacterField2 = ConstantNull

Element_CharacterField3 = ConstantNull

Element_NumericField1 = ConstantNull

Element_NumericField2 = ConstantNull

Element_NumericField3 = ConstantNull

Element_Available1 = ConstantNull

Element_Available2 = ConstantNull

Element_Available3 = ConstantNull

 

End Sub