SQLite database timing out?

1

My webapp has a problem with its database. If I update the database fairly frequently there are no problems, but if I wait for approximately 10-15 minutes or more between updates they no longer work. They don’t give any sql errors, but the updates never take place. My app is a golf scorekeeping app and in test mode when I enter the data one hole after another there is no problem, however, when playing golf and only doing updates after every hole the updates don’t work (no errors are reported). The program appears to work properly, but the updates I’ve done never get put into the database. It’s like after a certain time period I lose my sqlite database connection if I don’t do more frequent updates. (This is in iOS).

2

Is this running as a native app or a web app?

Can you reproduce this?

(I have not heard of this happening before myself, but you might want to scour the web)

3

On the last reply under message ‘The future of SQLite’ another user reported the same thing over a month ago. He said he was going to try to do the connection every time he access the database. I also have the same issue. Question, if you start a new connection every time that you write to the database, do you have to kill the connection when you are done? not sure how to code something like that as I have always set the connection when my page first loads. Maybe I am overthinking it, as we had to do those thing on ASP/VB.

4

Thanks for finding that.

You’ll have to experiment to see if that works.

5

Pre SQLITE being dropped (when I last used it), I created the connection on app init and then tested the connection before each transaction. If you’re using the plugin, I’m not sure how much it’s changed that part but I’d bet it closes on commit or there’s little to no overhead on reconnecting before each block of transactions.

6

How do I test the database connection? I’ve tried the following in BASIC:

If DB = undefined then DB = SqlOpenDatabase(“TestData”,“1.0”,“Test Database”)

It seems to work for the first connection, but not on subsequent ones if the connection is lost.

7

What error are you getting?

8

I don’t get an error, but after the first time it doesn’t seem to take the branch to “If DB = undefined” and try to re-open the database, and any updates after that point don’t get applied (as if the database isn’t open).

9

Post a larger block of your code. It sounds more like a logic error.

10

UpdateRestart(“UPDATE restarttable SET currenthole = '” & CurrentHole & “’;”)

Sub UpdateRestart(UpdateString)
  DBOpen()
  Dim sqlList()
  sqlList[0] = UpdateString
  Sql(DB,sqlList)
End Sub

Sub DBOpen()
  If DB = undefined Then
    DB = SqlOpenDatabase("BettorGolfPlus","1.0","BettorGolfPlus Database")
  End If
End Sub

This is a sample - there are many other calls to UpdateRestart in the code. They all seem to work if the database is accessed frequently, but if there is a delay of more than approximately 10-15 minutes there are no errors, but the updates never take place. I can just remove the “If DB = undefined” and re-open the database automatically every time and see if that works, but I was trying to prevent that by checking if it was already open.

11

Sorry I missed this. I’ve been horribly busy!

By and large, you should always check to make sure your DB is open before accessing it. Take for example, you’re on a screen and the user switches your app to the background and does stuff and comes back. While the app make go back to the same page, odds are some of your resources are gone. I use the pause/resume event handlers just to make sure I handle those things.

12

Hey guys,
Wondering what was the final solution. Did you find a good way of checking if the DB is not open and reconnecting?

13

I now re-open the database every time I access it

14

Thanks for your answer.
So you open and close on every call? On my app sometimes the data comes in too quick to do that, so I ran into the database being on the wrong state. But if I don’t do that the connection times out. I was wondering of you found a good way to tell if the DB was already open. My attempts to that have not worked.

15

Maybe this will help you.
This is what I use on my Android app, which I use on a Kindle Fire 7.
In extra files this is all I have:

    myDB.db,"",NSB.overwriteNever
-------------------------------------------------------------------------------------
    'Open the myDB.db database
     DB = SqlOpenDatabase("myDBt.db")
      'debugger   'for checking this
      If DB.version <> "" Then
        glDBOpen = True
        txtMessage.value = "Database opened." ' Version " & DB.version
      
    and then I start loading tables.

      Else
        MsgBox "Could Not Open database."
      End If

I was noticing that if I opened the database too soon, like right at
the beginning of the Sub Main(), in my code it wouldn’t load the
first table all the time, so I just added a few lines of code to slow
the process down:

    Dim gcDate
    Dim Today = Date
    gcDate = FormatDateTime (Today, "mm/dd/yy")

    Sub Main()
      'Set to current date on start
      DateDE.value = gcDate
      DateVDL.value = gcDate
      'Save date as string
      lcDEDate = gcDate

     'Open the myDB.db database

I’ve run this code for years without closing the database at all, except when
closing the app, and had very few problems.
CU

16

Checking for deviceready in main is safer than the timer delay. Search this board for lots of examples.