Quick question regarding AppStudio and SQLite COMMIT…
I can’t seem to get new entries to save (and stay) into a database. When I reload the app, the new entries are gone. From what I’ve read on the forums, I have to "COMMIT’ the changes or they are rolled back and not saved to the database. This is after installing and running the app on Android and iOS devices (via Phonegap Build).
I’ve read through the SQLite Wiki docs but cannot find the proper syntax to commit the changes after an insert.
Can someone offer a solution?
Here is the code…
function Main() {
DB = SqlOpenDatabase("miles.db");
sqlList = "CREATE TABLE IF NOT EXISTS trip ('date', 'destin', 'purp', 'startm', 'endm', 'triptot')";
sqlList = "SELECT * FROM trip";
Sql(DB, [sqlList]);
}
//Insert a new entry
btnSave.onclick = function() {
sqlList = "INSERT INTO trip (date,destin,purp,startm,endm,triptot) VALUES (?,?,?,?,?,?)";
args = [fdate.value];
args[1] = fdestin.value;
args[2] = fpurp.value;
args[3] = fstart.value;
args[4] = fend.value;
args[5] = ftriptot.value;
Sql(DB, [
[sqlList, args, sqlSuccess]
]);
};
function sqlSuccess() {
**//Not sure of the proper "COMMIT" syntax to place here..**
NSB.MsgBox("Entry Saved");
}
Are the entries there while you’re running the program? In other words, if you do an insert and then try and reload the data in the same instance of the program does it work? If so then it’s not a commit issue. It’s been a while since I’ve used SQLite, but you don’t need a commit unless you’ve got a transaction going, so if you didn’t do a “START TRANSACTION” or “BEGIN TRANSACTION” (whatever the SQLite syntax is) then you shouldn’t need a “COMMIT”. Assuming the answer to my first two questions is “yes”, it sounds more like your database is getting recreated from scratch every time you restart your program.
Yes the data is there until I close the app and open it again. Then any newly added entries are gone.
I have already tried commenting out the recreation of the database if not exists and it doesn’t matter. It loads the pre-existing data just fine. Just the new inserts don’t stick after closing the app. Our previous mobile dev environment, Appcelerator Titanium (before we ported apps over to AppStudio) didn’t have this issue. Happens both on Android and iOS devices.
It still doesn’t sound like a commit issue to me, but short of having any better advice to offer, doing a commit is as simple as issuing an SQL command that is simply the word COMMIT. I’m not sure when to tell you to do this, since I’m not real familiar with the event structure when it comes to mobile apps, but I suppose for the purpose of testing you could just put it right after the INSERT statement to see if it makes a difference.
We have already tried adding COMMIT in the INSERT as well. Again, we didn’t need COMMIT in Appcelerator so I’m not sure how AppStudio is handling the commit. The sample SQL apps don’t work either when installed via Phonegap. Not sure what else to try at this point.
Hi,
Looks to me like a syntax issue…
You have (e.g.)
DB = SqlOpenDatabase("miles.db");
sqlList = "CREATE TABLE IF NOT EXISTS trip ('date', 'destin', 'purp', 'startm', 'endm', 'triptot')";
sqlList = "SELECT * FROM trip";
Sql(DB, [sqlList]);
I believe it SHOULD be…
sqlList=[]
sqlList[0] = "CREATE TABLE IF NOT EXISTS trip ('date', 'destin', 'purp', 'startm', 'endm', 'triptot')";
sqlList[1] = "SELECT * FROM trip";
Sql(DB, sqlList);
I think the issue as you can see above is that you’ve not initiailised an array and are not calling the SQL to run through the whole array and execute…
Best Regards,
Neil
Same results after adding the PhoneGap plugin. Just cannot get the inserts to stay after the app is closed and opened again. However, now getting an error that says “no such table”.
I’m sure it’s something I’ve missed. The original code was taken from one of the samples and all that was changed is the database file and table name.
Does anyone have a simple SQLite read/write block of code I can try? A simple db file with a single table?
Did you try my version of the code?
No offence but a quite a few samples don’t work and your code won’t CREATE a table because you overwrite the variable…
I don’t need any plugins for my SQLite…
Neil. Yes, I tried your code and get an error “Uncaught ReferenceError: sqlList is not defined.”. The previous code does not give this error. I don’t think the problem is with the array. I can list out an existing db fine with the same code. Just can’t add anything to the db.
No, I never could get SQLite to actually store data at all. Did tons of research as well. Even the SQLite sample apps were not able to store (AND KEEP) new data that was inserted. It all looked like it was working until you reload the app, the old data was there, none of the new data. I’ve been all over the forums on this but to no avail. This was on iOS and Android devices as well. Did not matter if it was Phonegap build or web app, neither worked.
Spent days and days on it but nobody had an answer that worked. Sent a demo app to George for support but never heard back. Just gave up on it in AppStudio. Wound up storing the data using other methods instead.
Function btReadExternalDB_onclick()
window.resolveLocalFileSystemURL(cordova.file.externalRootDirectory, gotExtDB, failExtDB)
End Sub
Function failExtDB(error)
Call AddLog("can not open db")
End Function
Function gotExtDB(externalDataDirectoryEntry)
Dim DB = window.sqlitePlugin.openDatabase({name: "extern.db", androidDatabaseLocation: externalDataDirectoryEntry.toURL()})
s=Array(["Select * from user WHERE user_level='administrator';", LoadDB_dataHandler])
Sql(DB,s)
End Function
Sub LoadDB_dataHandler(transaction, result)
Dim DBrecords
DBrecords=result
For i=0 To DBrecords.rows.length-1
record=DBrecords.rows.item(i)
Call AddLog(record["username"])
Next
End Function
But it stopped working when I had to use:
<preference name = 'phonegap-version' value = "cli-8.0.0" />
In some older versions of android if it works and reads the database, but in more recent versions it no longer works, it does not read the database.
What could be happening?
I had tried the Chrome Remote debugger back when trying to solve the issues, no errors. Changes to content security didn’t make any difference. SQLite would not hold new entries no matter what. It certainly LOOKED like it was storing the data as long as the app was running. But when closing and reloading the app, no new data. We opened the db in an SQLite database manager and it also showed no new data in the table. We must have tried like 6 different database files as well and no go.
