SQLite DB Connection Errors

  1. 7 months ago

    Philip W

    16 Nov 2018 New Mexico, USA

    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?

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

    mDB.DatabaseFile = dbFile.Child("tttacg.sqlite")
  2. Dave S

    16 Nov 2018 San Diego, California USA
    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

  3. Philip W

    16 Nov 2018 New Mexico, USA

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

  4. Dave S

    17 Nov 2018 San Diego, California USA

    @Philip W 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

  5. Tim S

    17 Nov 2018 Canterbury, UK

    It should work by doing:

    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
  6. Massimiliano C

    is not verified 17 Nov 2018 Europe (Italy, Bergamo)

    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.

  7. Dave S

    17 Nov 2018 San Diego, California USA

    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..

  8. Philip W

    20 Nov 2018 New Mexico, USA

    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?

  9. Dave S

    20 Nov 2018 San Diego, California USA

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

  10. Karen A

    20 Nov 2018 Pre-Release Testers

    @Philip W I must physically call an open file dialogue then select the database I want to use.)

    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

  11. Beatrix W

    20 Nov 2018 Pre-Release Testers Europe (Germany)
    Edited 7 months ago

    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.

  12. Javier M

    20 Nov 2018 Pre-Release Testers, Xojo Pro AprendeXojo - Europe, Spain
    Edited 7 months ago

    Hi @Philip W

    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

  13. Dave S

    21 Nov 2018 San Diego, California USA

    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
  14. Tim S

    21 Nov 2018 Canterbury, UK

    @Philip W 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 ...

    What do you mean by "load"? Do you mean "open"?

    (that is, on starting the app, I must physically call an open file dialogue then select the database I want to use.)

    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.

    The SpecialFolder doesn't seem to like me.

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

  15. Philip W

    21 Nov 2018 New Mexico, USA

    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
  16. Paul L

    21 Nov 2018 Xojo Inc Answer http://docs.xojo.com

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

    mDB.DatabaseFile = dbFile.Child("tttacg.sqlite")
  17. Karen A

    21 Nov 2018 Pre-Release Testers

    Try:

    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
  18. Dave S

    21 Nov 2018 San Diego, California USA

    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

  19. Tim S

    21 Nov 2018 Canterbury, UK

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

    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.

  20. Philip W

    21 Nov 2018 New Mexico, USA

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

or Sign Up to reply!