SQLite DB Connection Errors

I have an SQLite read-only database that I would like to (1) be embedded within my app, (2) open when my app opens without the user opening a folder then selecting the database, and (3) fill records into text fields rather than a listbox. I have Googled, and I have tried some examples from various forum responses, and I have followed a few YouTube videos, but I just cannot get it to “click.” I was able to load it by selecting a directory or folder, now I’m convinced that was by accident. My latest effort was from watching a video on database connections by Paul Lefebrvre. I thought surely I had found my solution by adding his instructions but I still get errors on running.

My app is an algebra game I created using MicroSoft’s Visual Studio, but since I’ve discovered Xojo (I purchased a Mac edition license), I thought it would be great to port it over to the Mac desktop platform as well. I simply don’t want my SQLite database edited, or corrupted by a user, as I painstaking created over 580 algebra questions each with multiple choice responses. I’m frustrated because this problem is preventing my success. Is their help for me?

  1. it can be embedded just like any other resource… .just drag it into the left side pane, and it becomes a resource
  2. simply open the databases with the proper resource path name. no dialog box required (all that does is give you the path anyways)
  3. will depend on how you name/layout your textfields

not a definative answer, but the question wasn’t specific either…

So,… can it be done? certainly… the exact means and methods are dependant on other factors

FYI… you can also encrypt the SQLite database, and nobody but your app will be able to read it

Thank you Dave. I hope you are safe from the fire.

As of now the closest of those fires is about 150 miles away… but this area of California had a MAJJOR incident a few years ago, and unfortunatly the conditions indicate it could happen again

It should work by doing:

[code]Dim myXFile As Xojo.IO.Folderitem, dbFile As FolderItem, dbh as new sqlitedatabase, result as boolean

myXFile = Xojo.IO.SpecialFolder.GetResource (“mydatabase”) // mydatabase is your filename
dbFile = GetFolderItem (myXfile.Path, FolderItem.PathTypeNative)
dbh.DatabaseFile = dbFile
result = dbh.connect
[/code]

Put data directly in apps in a costant array or a class
database are open in read-only mode but are you sure for security ?
3 year ago i worked for Minnesota university, for make a simple MMPI2 / MMPI-A ( and the reduced form in Italian Language ) psicometric test somministration software for Mac and Linux platform. Data was store in app costant ( 567 item for MMPI2, 370 item for MMPI2 reduced form, 478 for MMPI-A and 370 for MMPI-A reduced form ), and all 1000 result code interpretation.

Doubtful that an Algebra test requires a high level of security, and I’d guess using a database (which could be encrypted) offers alot more flexiblity than an array of constants…

Thank you all for your feedback. Since my last post I have done quite a bit of reading and researching. In fact, I just finished “Program SQLite with Xojo Desktop” by Eugene Dakin. It was a good read. However, I have yet to find a way for my database to be selected from a directory, then load into my app without my having to load it manually (that is, on starting the app, I must physically call an open file dialogue then select the database I want to use.) The SpecialFolder doesn’t seem to like me. Is there a way to have the database load without a user selection first?

The open dialog returns a folderitem… just create the folderitem yourself.

[quote=415161:@Philip Whittle]I must physically call an open file dialogue then select the database I want to use.)
[/quote]

I early don’t need to do that on a Mac or Windows… BUT that almost sounds a like a sandbox security thing… but that should not just happen either…

Try what Dave said… and if that does not work then post the code you used to create the folderitem and assign it to the database object… maybe we can see what the issue is.

  • karen

As always: show us your code. We all have been beginners at some time. When you learn something the first steps are the hardest. We aren’t snotty Stackoverflowers.

Hi @Philip Whittle

I don’t know if you has read this info already. Maybe you can find it useful: http://developer.xojo.com/sqlitedatabase-and-iossqlitedatabase-for-beginners-tutorial-quizzes

There is also a “SQLite Foundations” Tutorial and Quizzes at: http://developer.xojo.com/tutorials/sqlite-basics

Javier

and of course a quick trip to the lang ref is always a good thing

Dim db As New SQLiteDatabase
db.DatabaseFile = GetFolderItem("db.sqlite") // just replace with whatever is appropriate for you app
If Not db.Connect Then
  //handle error here
End If

What do you mean by “load”? Do you mean “open”?

As we are talking about one specific database, you add it to your project as a resource - just drag it to the left hand column of the IDE. Then when you run your app, you can open it using the sequence I posted upthread. Using its data to populate textfields is then more code you have to supply.

What does this mean? Can you post your code, error messages, etc?

Thanks everyone. I’ve attached my code. As you can see it is relatively routine. One thing though, when I use the GetFolderItem(“databaseName”), I get both Connection and openDatabase error messages; but, I do not get those errors if I use the GetOpenFolderItem(“databaseName”). Weird? My database is located both within my app’s left-side panel and the “Documents:HomeWorks:dataFiles” folder (I’m using MacBook Air running Sierra 10.13.6). So from my “openDB” method …

Dim dbFile As FolderItem = SpecialFolder.Documents.Child("HomeWorks").Child("dataFiles")

If dbFile <> nil Then
  mDB = New SQLiteDatabase 
  mDB.DatabaseFile = GetOpenFolderItem("tttacg.sqlite") //GetFolderItem("tttacg.sqlite") //
  
  If mDB.Connect Then
    loadDB
  ElseIf Not mDB.Connect Then
    MsgBox("Connection error: " + mDB.ErrorMessage)
  End If
  
  If mDB.Error Then
    MsgBox("DB Error: cannot openDatabase " + mDB.ErrorMessage)
    Return
  End If
End If

If your DB file is actually in “Documents/HomeWorks/dataFiles”, then you’d access it like this:

mDB.DatabaseFile = dbFile.Child("tttacg.sqlite")

Try:

[code]Dim dbFile As FolderItem = SpecialFolder.Documents.Child(“HomeWorks”).Child(“dataFiles”).Child(“tttacg.sqlite”)

If dbFile <> nil Then
mDB = New SQLiteDatabase
mDB.DatabaseFile = dbFile

If mDB.Connect Then
loadDB
ElseIf Not mDB.Connect Then
MsgBox("Connection error: " + mDB.ErrorMessage)
End If

If mDB.Error Then
MsgBox("DB Error: cannot openDatabase " + mDB.ErrorMessage)
Return
End If
End If
[/code]

if dbFile.EXISTS

the file may not be NIL but that doesn’t mean you chose the correct path syntax to point to the actual database file

[quote=415252:@Philip Whittle]

Dim dbFile As FolderItem = SpecialFolder.Documents.Child("HomeWorks").Child("dataFiles") [/quote]

Why is it in the Documents folder? You said you wanted it embedded within your app. Doing what I said earlier today will cause it to be embedded (at least under macOS), and the code I gave you on 17th Nov will open it.

Thank you all for your responses. Paul, that worked perfectly. Thank you.