SQlite error:undefined (Code 5)

1

I have a number of native Android Apps developed using AppStudio 8.5.7.5. I am building via VoltBuilder using engine name=“android” spec=“9.1.0”. The Apps run just fine after they are first installed on an Android Device (Samsung Galaxy A50, S20, S22). If I leave the app, to run something else on the phone, and then come back at a later time, it continues to work fine.

If the App is dumped from memoory by the Adroid System or if I close the App (remove from active memory by swiping up from the ||| OS screen) and then start it again, I get an SQlite error:undefined (Code 5) error popup. It doesn’t matter how may times I now close the app and try to rerun it, I still get the same error popping up. I know that for some reason, the SQL database is not responding or connecting correctly. (This error has only started up since about 4 months ago where new Android patches have been updating the cell phones).

If I go to the phones Setting > Apps > {My Apps name} > Storage and “Clear” the data; and then run the App, I get no error. I know that a new Datasebase is being created and I can see the original data that is stored in it.

I have tried all sort of things to resolve this issue but to no avail. It is always the first SQL Select statement that fails. (See code below).

The following code snippet is what I have in the Project Properties and Global Code section:

document.addEventListener("deviceready", onDeviceReady, False)

gDataBase=SqlOpenDatabase("PEATS.db")
If gDataBase=0 Then
  NSB.MsgBox("Database Not found", 0, "Please restart Application")
End If

Function onDeviceReady() 
  Dim T
  
  document.addEventListener("backbutton", onBackKeyDown, False)
  addReceiveListeners()
  createUDPSocket()
  
  NSB.ShowProgress("Loading Support Data...")
  T=SetTimeout(LoadSupportData,3000)   
End Function

Sub LoadSupportData()
  Dim sqlList
  sqlList    = []
  sqlList[0] = ["Select * from HandheldSettings;", HandheldSettingHandler]
  sqlList[1] = ["Select * from ProvStates ORDER BY Sequence;", ProvinceHandler]
  sqlList[2] = ["Select * from PrivateComments ORDER BY Sequence, Description;", PrivateCommentsHandler]
  sqlList[3] = ["Select * from PublicComments ORDER BY Sequence, Description;", PublicCommentsHandler]
  sqlList[4] = ["Select * from PINBooks;", PINBookHandler]
  
  sqlList[5] = ["Select * from TerminalFiles WHERE Updated_On IS NULL;", TerminalFilesHandler]
  sqlList[6] = ["Select * from SavedTxn;", CountSavedTxnHandler]
  sqlList[7] = ["Select * from SavedPhotos;", CountSavedPhotoHandler]
  sqlList[8] = ["Delete from PlateList;", ClearedPlateListHandler]

  Sql(gDataBase,sqlList)  
  
End Sub

Any help would be greatly Appreciated as it is causing my customers a lot of pain and worry.
Many Thanks,
Robert

2

Are you using a plugin for SQLite? Which one?

PS. You probably should upgrade your engine to the current one - Google is getting serious about dropping support for 9.1.0.

3

Yes, I am using the following plugin: plugin name=“cordova-sqlite-evcore-extbuild-free” source=“npm”
According to the VoltBuilder log it is using version “cordova-sqlite-evcore-extbuild-free: 0.18.0”.

4

I think this might be something to ask about on the plugin’s support page. I don’t think this is an AppStudio/VoltBuilder issue.

5

Hi George,

Sorry for taking a while to get back to you on this issue. I have been running a variety of tests using a Debug Build. I am now also building for engine name=“android” spec=“11.0.0”. I am also running it on Android 11 devices (Samsung A50, S20, S22+). I have further trapped the SQLite error and it tells me that the Tables in my database don’t exist.

I have traced the app step by step and is appears that when the app is run the First Time it is not deploying the associated Database (as per your Using SQLite Persisting Updates to the Database section of your Wiki documentation). I have deleted and re-installed the App and also Cleared the Data from the App numerous times but nothing seems to resolve the issue.

I have the following in the extraFiles parameter:
PEATS.db,gDataBase,initDB,NSB.overwriteIfVersionDifferent
I have also tried:
PEATS.db,gDataBase,initDB,NSB.overwriteAlways

When the App executes the SqlOpenDatabase(“PEATS.db”) command, it opens/create a SQLite database called PEATS.db but there are no tables present. I can programmatically add a table which seems to persist when I close and re-open the app. But it does not import the database automatically when the application starts for the first time (as per your documentation).

Note: previous versions of my application are running fine until the user either Clears the Data or re-installs the app on their cellphone. Once they do, the error start to popup and nothing that I do will make the app run again.

I have also Built the Northwind Sample Project and it exhibits the same behaviour. It doesn’t seem to create the Northwind SQLite database when run the first time on a device.

Could you please assist as I am getting a little desperate. I have private customers running my application (it is not in the Play Store) and their devices are all starting to fail.

Is there a way to manually load my SQL Database when the App is run the first time? If so, can you explain how I would do this.

Any and all help will be greatly appreciated.
Many Thanks,
Robert

6

Are you using an SQLite plugin? (Such as cordova-sqlite-evcore-extbuild-free)

7

From Previous Message (Feb 2, 2023)…
Yes, I am using the following plugin: plugin name=“cordova-sqlite-evcore-extbuild-free ” source=“npm”
According to the VoltBuilder log it is using version “cordova-sqlite-evcore-extbuild-free: 0.18.0”.

8

How are you opening your database? The call when using the plugin is a bit different than the built in function:

    openDatabase(name) {
      if (window.sqlitePlugin) {
        return window.sqlitePlugin.openDatabase({ name, location: 'default' });
      }

      // no plugin? use the default method
      return window.openDatabase(name, '', name, 0);
    },
9

I have been calling the SqlOpenDatabase function. (See message on Jan 1, 2023 ). Here is the code for that AppStudio Function:

// SQL Functions

function SqlOpenDatabase(name) {
	if (typeof(window.sqlitePlugin) === "object") {
		return window.sqlitePlugin.openDatabase({ name:name, location: 'default' });
	}

	// no plugin? use the default method
	return window.openDatabase(name, '', name, 0);
}

I can verify that my Debug version of the App is using the window.sqlitePlugin.openDatabase Function call.

10

Looking at the code in AppStudio, it looks like the problem could be there. Let me work on this some more…

11

I think I’ve found the problem - it’s a timing issue on startup of the app.

To test, try executing this command once your app is fully loaded:

NSB.preMainFunctions[0]()

You can do this from the console or from a button you add to the app.

If this fixes your issue, we will look into a more elegant way to do this.

12

Hi George,

Yes, adding a button to call the NSB.preMainFunctions0 seems to work in creating the SQLite Database. The only issue is that I then need to either leave the app and come in a second time so that my App retrieves the data from the SQLite tables or I need to add a second button to call my LoadSupportData function.

As you suggested, a more elegant way is needed to handle the initial database creation and the subsequent call to reteive data (all when starting the app).

Regards,

13

I’ve been struggling with the exact same problem in Javascript. What is the Javascript equivalent of: NSB.preMainFunctions0?

Thank you

14

@Warren_Sundet - NSB.preMainFunctions is an array of functions which get executed at startup. It’s probably not directly useful to you: by the time your app has started so you can run any code, the functions in NSB.preMainFunctions will have been executed.

15

@Robert_Suranyi - I’ve been thinking about what you might be doing here. If I understand correctly, you will reloading the database every time you start your app. You probably only need to do so the first time.

In that case, you might want to put some code in your app which calls NSB.preMainFunctions()[0] after checking to see if the database needs to be loaded. Sub Main will probably be too soon.

16

Hi George,

Yes, every time the app is started it needs to call SqlOpenDatase(). It is my understanding that the database must be opened before any SQL queries can be run. If it is the First Time that the app has been run on the android device (or the database has been cleared from the device), it should automatically Create the Database (which is not happening).

To check to see if the Database has been created, I still need to call the SqlOpenDatase() function before running a query to test to see is any tables exist. Note that the SqlOpenDatase() function is called after the onDeviceReady fires. I could try a call to NSB.preMainFunctions() after running a test to see if any tables exist in the database but will that also be too soon (as compared to Sub Main)?

BTW - Everything was working fine previously for years, but lately I encouter this issue. Did Android or Appstudio change the way apps get loaded? Why did it work before but not now (same old app running on newer Android Devies or new Android builds)?

Is there any way to restore the original functionality by having appstudio calling the NSB.preMainFunctions() later in the app’s startup process?

Kindest Regards,
Robert

17

Yes, there have been changes in the browser which make the order of execution different.

I’ll try a few more things here.

18

I’ve got a beta for you to try out. (Windows).

https://www.dropbox.com/s/uy72cxpmfmzqgce/AppStudioBeta.exe?dl=1

Can you give it a try? (Let me know if you need MacOS)

19

HI George,
The Beta version that you let me download did NOT fix the issue. Meaning that, when I run the App for the first time, I still get the SQL Error stating that the Handheld table was not found. After I clear the error, I check and find that the database has been create and the Data has been loaded. When I run the app a second time, everything seems works fine. So it is still a timing issue. (See code below).

Function onDeviceReady() 
  initDB()
End Function

Function initDB()
  Dim sqlList
  
  gDataBase=SqlOpenDatabase("PEATS.db")
  If gDataBase=0 Then
    NSB.MsgBox("Database Not found", 0, "Please restart Application")
  Else
'    nTables = -1
'    sqlList=[]
'    sqlList[0]=["SELECT COUNT(*) AS tabCount FROM sqlite_master;",tableExistCheck,masterErr]
'    Sql(gDataBase,sqlList)  
    LoadSupportData()  
  End If  
End Function

Sub LoadSupportData()
  Dim sqlList

  sqlList    = []
  sqlList.push(["Select * from HandheldSettings;", HandheldSettingHandler, masterErr])
  sqlList.push(["Select * from ProvStates ORDER BY Sequence;", ProvinceHandler, masterErr])
  sqlList.push(["Select * from PrivateComments ORDER BY Sequence, Description;", PrivateCommentsHandler, masterErr])
  sqlList.push(["Select * from PublicComments ORDER BY Sequence, Description;", PublicCommentsHandler, masterErr])
  sqlList.push(["Select * from PINBooks;", PINBookHandler, masterErr])
  
  sqlList.push(["Select * from TerminalFiles WHERE Updated_On IS NULL;", TerminalFilesHandler, masterErr])
  sqlList.push(["Select * from SavedTxn;", CountSavedTxnHandler, masterErr])
  sqlList.push(["Select * from SavedPhotos;", CountSavedPhotoHandler, masterErr])
  sqlList.push(["Delete from PlateList;"])

  Sql(gDataBase,sqlList)  
  
End Sub
20

If I add a 3 second timer to the initDB() function to call the Load Function, it works fine the first time the app is run. I don’t think that the adding a timer is the full elegant solution :thinking:.

Function initDB()
  Dim sqlList
  Dim T
  
  gDataBase=SqlOpenDatabase("PEATS.db")
  If gDataBase=0 Then
    NSB.MsgBox("Database Not found", 0, "Please restart Application")
  Else
'    nTables = -1
'    sqlList=[]
'    sqlList[0]=["SELECT COUNT(*) AS tabCount FROM sqlite_master;",tableExistCheck,masterErr]
'    Sql(gDataBase,sqlList)  

 '   LoadSupportData()  
 
  T=SetTimeout(LoadSupportData,3000)   
  End If  
End Function

Here is the Console log of the runing code (if that helps).
image