In Memory SQLite From Module or Class How To?

[code]// -------------------------------------------------------------
// – 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

[/code]

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

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")

Thanks Tim!
I was missing this line:

App.Open
modDB.DB = new SQLiteDatabase

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

[quote=422546:@Javier Mené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[/quote]
That is not actually needed as the title mention, it is an InMemory Database and not a Disk file database .