Web app with SQLite

Getting Started
1

Hello
when the web app in session this code works but when i use same query in a web page look like SQLite Db is close or lost referents.
If any idea way

thanks

Var dbFile As FolderItem = New FolderItem(“C:\InventoryCNT\InventoryDB.db”)

If dbFile <> Nil and dbFile.Exists Then

Var ConnDB As New SQLiteDatabase
//ConnDB.MultiUser=true
ConnDB.DatabaseFile = dbFile

Try
ConnDB.Connect

Var Rs As RowSet
Var SqlStr As String
If ConnDB <> Nil Then 
  
  SqlStr="Select * From UserInfoTb Where UserName ='Alex' and PasswordCnt ='123' "
  Rs=ConnDB.SelectSQL(SqlStr)
  
  If (Rs<>Nil) Then
    
    Session.UserNameCNT=Rs.Column("Name").StringValue
    
  End If
  
End If

Catch error As DatabaseException
MessageBox("Error: " + error.Message)
exit
End Try

End If

2

This creates a local variable which will not be available anywhere else.

Assuming you have a session property named ConnDB, use

ConnDB = New SQLiteDatabase

1 Like
3

Why turn off MultiUser?

4

Rightly or wrongly, I always feel nervous about maintaining a constant open connection to a database file.

I personally create a function in Session that returns an SQLiteDatabase object when requested, and I then always close that database object after use. There is no noticeable lag in doing this…

But if I wanted to maintain a persistent connection to the database I’d use a Session variable to hold the SQLiteDatabase object (in the code above ConnDB), so it’s unique to each user and maintained for the session.

5

Hi Stam
thanks for the advise i am going to change all.
thanks to all help

6

The property type, in case someone needs it stated on here, needs to be set to SQLiteDatabase.

7

ok
thanks

8

@Alexis_Colon_Lugo

Multiuser MUST be set to True if you’ll be connecting from within sessions. You’ll have never ending issues if you do not.

Here’s what I do…

I have a module in my apps called DBStuff which contains

  1. a private property mDBCache as Dictionary.
  2. A method for getting database connections
  3. A method for closing database connections

The connection method looks like this:

Protected Function Connect() as SQLiteDatabase(cached as Boolean = True)
    If mDBCache = nil then
        mDBCache = new dictionary
    End if

    Dim key as string = "app"
    If session <> nil then
        Key = session.identifier
    End if

    If cached and mDBCache.haskey(key) then
        Return mDBCache.value(key)
    End if

    Dim db as new sqlitedatabase
    // declare your db config here

    If cached then 
        mDBCache.value(key) = db
    End if

    Return db
End Function

Protected Sub Close()
    If mDBCache = nil then
        Return
    End if

    If session = nil then return
    
    Dim key as string = session.identifier
    If mDBCache.haskey(key) then
        Dim db as sqlitedatabase = mDBCache.value(key)
        fb.close
        mDBCache.remove(key)
    End if
End Sub

Call DBStuff.connect whenever you need a database connection. Call DBStuff.Close in Session.Close.

9

look good

thanks