In Memory SQLite From Module or Class How To?

  1. 10 months ago

    Benjamin B

    25 Jan 2019 DFW Metroplex (Flower Mound) T...
    // -------------------------------------------------------------
    // -- I can create a local instance of SQLite and it works
    // -- however it's only accessible from that one method
    // -------------------------------------------------------------
    Dim varInMemorySQLiteDB as New SQLitedatabase
    If not varInMemorySQLiteDB.connect Then
       MsgBox("Error: cannot create in memory db")
       Return
     Else
       varInMemorySQLiteDB.commit
    EndIf
    
    // ----------------------------------------------------------------------
    // -- I have read various blogs and posts that in order 
    // -- to have this accessable globally, create this in a Module or Class
    // -- I've tried various combinations and dot notations but to no avail
    // -- This is my latest attempt
    // -- can someone please guide me? Thanks, Ben
    // ----------------------------------------------------------------------
    // -- In the IDE:
    // ---------------------------------
    globalModuleInMemorySQLiteDB
      Properties
         InMemorySQLiteDB               [Type: SQLiteDatabase]
         						       [Scope: Global]
    
    // --------------------------
    // -- In the App.open event
    // --------------------------
    	Dim InMemorySQLiteDB As New SQLiteDatabase
    
    	If not InMemorySQLiteDB.Connect Then
    		MsgBox("Error: cannot create in memory db")
    		Return
    	Else
    		InMemorySQLiteDB.Commit
    	End If
    
    
    
    // -----------------------------------------
    // -- In a method on a different window
    // -----------------------------------------
    MethodInADifferentWindow_UseInMemoryGlobalModule
    
    	Dim sql as String
    	sql = "Select column from Table;"
    
    	If not InMemorySQLiteDB.Connect Then                       <-- nil object exception
    		MsgBox("Could not connect to inMemorySQLite db")
    		Return
    	Else
    		InMemorySQLiteDB.SQLSelect(sql)
    	End If
    

    When you dim a variable it's local to the method it's in. So when you dim it in App.Open, you're only able to use it in App.Open. You have to store it as a property on the module, and the connect to that module property rather than the local variable every time.

    modDB as Module
        DB as SQLiteDatabase
    App.Open
        modDB.DB = new SQLiteDatabase
        dim bConected as Boolean = modDB.Connect
        // Handle errors if needed
    MethodInADifferentWindow
        dim rs as RecordSet = modDB.DB.SQLExecute("SELECT * FROM Users")
  2. Javier M

    25 Jan 2019 Pre-Release Testers, Xojo Pro, Third Party Store AprendeXojo - Europe, Spain

    Hi Benjamin,

    Apart from the Module, Class or a simple property hanging from the App object… where is the SQLite instance pointing to the SQLite database file on disk? Probably that is what is failing to you? ( I mean, assigning the FolderItem before Connect)

    Javier

  3. Tim P

    25 Jan 2019 Pre-Release Testers Answer Rochester, NY

    When you dim a variable it's local to the method it's in. So when you dim it in App.Open, you're only able to use it in App.Open. You have to store it as a property on the module, and the connect to that module property rather than the local variable every time.

    modDB as Module
        DB as SQLiteDatabase
    App.Open
        modDB.DB = new SQLiteDatabase
        dim bConected as Boolean = modDB.Connect
        // Handle errors if needed
    MethodInADifferentWindow
        dim rs as RecordSet = modDB.DB.SQLExecute("SELECT * FROM Users")
  4. Benjamin B

    25 Jan 2019 DFW Metroplex (Flower Mound) T...
    Edited 10 months ago

    Thanks Tim!
    I was missing this line:

    App.Open
    modDB.DB = new SQLiteDatabase

  5. Tim P

    25 Jan 2019 Pre-Release Testers Rochester, NY

    Just for clarity, what you had done was redefined a local to the method variable named InMemorySQLiteDB because you used dim. In the App.Open event you had access to two variables named InMemorySQLiteDB that were SQLite Databases.

    The local one was instantiated, the global module one was not. This is why you were able to connect in App.Open, but the other method was giving you a nil object exception. For even more confusing fun, you'd still be able to access the global module one as well if you used the full namespace (globalModuleInMemorySQLiteDB.InMemorySQLiteDB).

  6. 5 months ago

    Aurelian N

    Jul 5 Pre-Release Testers, Xojo Pro

    @Javier Meacute;ndez Hi Benjamin,

    Apart from the Module, Class or a simple property hanging from the App object… where is the SQLite instance pointing to the SQLite database file on disk? Probably that is what is failing to you? ( I mean, assigning the FolderItem before Connect)

    Javier

    That is not actually needed as the title mention, it is an InMemory Database and not a Disk file database .

or Sign Up to reply!