Ô 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