' The following code is an example of how SQLite can be used with NSBasic/CE

‘ 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

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

End If

End Sub

‘ 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

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

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

‘ 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

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