AppStudio SQLite and 'COMMIT'


#1

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

#2

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.

Eric


#3

Hi Eric.

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.


#4

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.

Eric


#5

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. :confused:


#6

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


#7

There should be no need to do a COMMIT.

Are you running under PhoneGap when you see this?


#8

Yes, using ‘Make Native App with Phonegap’. Then install on the iOS and Android devices.


#9

Hi Neil.
This code was taken from one of the Sample apps. All that was changed is the table data.


#10

That’s really important to note! You should use the PhoneGap Plugin for SQLite by adding this to your configxml:

<plugin name="cordova-sqlite-evcore-extbuild-free" source="npm" />

#11

Pretty sure your code of assigning sqlList = XXXX to 2 rows one after the other won’t work…
the second row will overwrite the first…


#12

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?


#13

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…


#14

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.