Migrating from RB database to SQLite database

Greetings folks -

This is my first attempt to use the Xojo SQLite database. Using IDE 2016 1.1. I try the following code, mostly copied directly from the LR (new layout seems really confusing, by the way, and really laborious to use if you don’t know exactly where you are going.)

[code] Dim sql As Text
sql = “SELECT * FROM Note”

Dim data As SQLiteRecordSet

data = App.NoteDB.SQLSelect(sql)
Catch e As SQLiteException
MsgBox(“Error reading database”+ e.Reason)
End Try

If data <> Nil Then
MsgBox ("There are “+Str(data.FieldCount)+ " notes!”
MsgBox(“No notes yet!”)
End If[/code]

I am getting multiple errors that seem pretty basic.

  1. On the dim data line, error = can’t find a datatype with this name. This line was copied and pasted directly from the LR. Should it, perhaps, just be dim data as RecordSet?

  2. On the Catch line, error = can’t find a datatype with this name. Again, this was copied and pasted directly from the LR. Why the catch/try rather than just looking at App.NoteDB.Error and App.NoteDB.ErrorMessage?

Is there some switch I need to set or something to include? Pretty sure, the version I have includes database support and there is no problem with the SQLselect statement near the top.

I am also confused about the datatype text. First time I have seen it in Xojo. Why that instead of string?

Many thanks

Jim Wagner
Oregon Research Electronics

TEXT is a “new framework” replacement for STRING (yeah it makes no sense to me either, but for this STRING will work too)

There is no such thing as SQLiteRecordSet is just RecordSet (per the LR)

instead of TRY/CATCH… just query App.NoteDB.Error

and once again… The Language Reference (LR) is your friend

And I’m not going to comment on the validity of the source you got that from… just that it led you astray unfortunatly

 Dim sql As String
  sql = "SELECT * FROM Note"
  Dim data As RecordSet
  data = App.NoteDB.SQLSelect(sql)
 if app.notedb.error then 
    MsgBox("Error reading database"+ e.Reason)
  End if
  if data.recordcount > 0 then 
    MsgBox ("There are "+Str(data.RecordCount)+ " notes!"
    MsgBox("No notes yet!")
  End If


these mods are off the top of my head

FYI… RecordCount is how many ROWS were returned…
Field Count is how many fields in the record


This was from Class>SQLiteRecordSet :slight_smile: Seemed like a reasonable place to look since it is a SQLite database!

I want to get a count of the number of records and do some things differently if that is zero.

Appreciate your quick response.

another way to get just the count

Dim sql As String
  sql = "SELECT count(8) as cnt FROM Note" // yes that is an 8....why? fodder for a more advanced SQL lesson :)
  Dim data As RecordSet
  data = App.NoteDB.SQLSelect(sql)
 if app.notedb.error then 
    MsgBox("Error reading database"+ e.Reason)
  End if


less database overhead because all it does is return the count, NOT all the actual data… there will be ONLY one record, and it will have a value equal to the number of records

Well TEXT isn’t a direct replacement for string.

String has a nasty dual behavior which has been an endless source of confusion & bug reports of all kinds.
Sometimes it s bunch of bytes.
And sometimes its textual values.
And you can mix & match the methods you use to manipulate strings which can cause tons of problems - like suddenly you get diamonds in your UI because you appended two “strings” - yet one was really a bunch of bytes so the encoding is now NIL.
Just search the forums for “DefineEncoding” being part of the answer

A String behaves like TEXT and MEMORY block all in one

TEXT is - well … TEXT - not runs of bytes.
It is sequences of Unicode code points - which depending on what encoding you want the BYTES for may be very different
A byte by byte representation of the text “ABC” in UTF-8 is different than the one in UTF-16.
Add in any special characters like and things get really different depending on encoding.

TEXT’s make this dual behaviour go away.
When you get sent BYTES (like from a serial port or tcp socket or event fro a database blob field you get BYTES - not TEXT)
So you use a memoryblock which deal with bytes and convert TEXT to BYTEs then send that - hopefully in a way the other end knows what encoding it got.
And vice versa

We might have just changed Strings to be this way IF it wouldn’t have broken virtually every existing program
But it would have - hence TEXT

Norman… to use your style of response… I said is was a “new framework” replacement… NOT a direct replacement

My response was more focused on [quote=270169:@Dave S]yeah it makes no sense to me either[/quote]

The rest … whatever

Thanks on that alternate example, Dave!


FYI, SQLiteRecordSet and the other SQLite classes in the iOS Framework folder are only available for iOS.

Thanks, Paul, but that fact is not very obvious when searching through an insanely long class list!


That’s why you must read the entry :wink: