SQL

Getting Started
22

= was good Dave! what didn’t work for me was:
sql = “SELECT buzzword FROM micadtable WHERE buzzword = ““PAUL KLEE”””
just needed “SELECT * FROM…”

23

Just a note on the syntax: SQLite accepts the mix of quotes:

sql = "SELECT buzzword FROM micadtable WHERE buzzword = 'PAUL KLEE';"

it’s just a little more readable
jjc_Mtl

24

Actually it is good practice to always use SINGLE QUOTES for literal string values
as SOME database engines (ie. Oracle) give a special meaning to DOUBLE QUOTES, and they cannot be interchanged.

25

Hi. Please help…

I’ve opened this code again 1 year later and I’m getting now getting an error
“An exception of class NilObjectException was not handled. Shut down.”

on this line:
rs = micadtextfile.SQLSelect(sql)

Any ideas folks?
Thanks…

26

what is micadtextfile ? it’s certainly NIL, so the exception.

28

sqllite database

29

You should Run your app in the debugger from the IDE and investigate why the database instance is Nil.

30

Thanks. Will research that.

31

[quote=394509:@Gary Dalal]on this line:
rs = micadtextfile.SQLSelect(sql)[/quote]
add a debug point (a click in the left of the line until you get a red dot)
then run (as Greg advice)
and read the state of micadtextfile (in the lines before that one).

BTW: I added some needed dim lines, changed the TABLE name and put your code into a project of mine: same Nil, same location.
Oh, I forget to add code to not have micadtextfile Nil.

I will explorate a little bit more.

32

Did you have New as in the next line:

Dim micadtextfile As New SQLiteDatabase

The code below works fine:

[code] Dim sql As String
Dim micadtextfile As New SQLiteDatabase
Dim RS As RecordSet
Dim DIM_record_number As String
Dim DIM_buzzword As String

Dim dbFile As FolderItem
dbFile = GetOpenFolderItem(FT_IO.All)
micadtextfile.DatabaseFile = dbFile
If micadtextfile.Connect Then
//proceed with database operations here…
Else
MsgBox("The database couldn’t be opened. Error: " + micadtextfile.ErrorMessage)
End If

sql = “SELECT * FROM List_Data”
rs = micadtextfile.SQLSelect(sql)
If rs <> Nil and not micadtextfile.error Then
While Not rs.EOF
DIM_record_number = rs.IdxField(1).StringValue
DIM_buzzword = rs.IdxField(2).StringValue
'MESSAGE BOX
// MsgBox( DIM_record_number + DIM_buzzword )
rs.MoveNext
wend
rs.Close
else
MsgBox("Error: " + micadtextfile.ErrorMessage)
End If[/code]

Nota: do not use a MsgBox inside a loop; and the used loop above does not have an exit line, so you have to break the program unless you want to press hundred of times the OK button (if your db is hundred of Records…).

Edit:
FT_IO.All is a File Type file that holds a .sqlite entry.

33

Thanks so much - will try all this. Bit frustrating because it worked 1 year ago!
I really should practice every week or so.

34

Use System.DebugLog to return the information (instead of MsgBox).

To read them (due to my bad english, I will explain like that):

cmd-f (or CTRL+F), then click in the icon on the right of the Find - Warning - “WiFi” icons (the one that looks like a wifi logo) after the run and enjoy to look at the returned data.

This works fine (even if you have > 200 Records in the DB as the example I used):

System.DebugLog(DIM_record_number + DIM_buzzword)