UPDATE on SQLite Cause Crash please take a look.

Accepted Answer

You aren't properly quoting the text in the SQL statement. By way of example, if the text field had the value my sample text the SQL would end up:

UPDATE interests SET title= my sample text WHERE id=...

As you can see, that would be invalid SQL syntax. So just try:

textField.addEventListener('return',function(e)
{
dataBase.execute('UPDATE interests SET title= "'+textField.value+'" WHERE id='+id);    
});

This ignores the potential for the user to mess up the SQL by actually keying a double quote in the field (for example: He is 6' 5" tall). Because of that, and to avoid the possibility of a SQL injection attempt, you should really use substitution variables in update statements:

textField.addEventListener('return',function(e)
{
dataBase.execute('UPDATE interests SET title= ? WHERE id='+id, textField.value);    
});

— answered August 31st 2011 by Doug Handy
permalink

9 Comments

Since you can't mark your own answer as correct (last I knew), I am resuming comments here instead of under your own answer.

So the error you are getting is a parse error on line 40? I show that as being the top: 40, but then I show the SQL on line 56 not 55, so I am guessing you are getting the parse error on this line:
```
    value:myResultSet.fieldByName('title'),
```
Is that right? And the parse error occurs at runtime?

Are you sure you are getting a result set back at the top of the file? Try this at the top of the file where you define myResultSet:
```
var myResultSet = dataBase.execute('SELECT * FROM interests WHERE id = ' +win.id);
alert('Title is ' + myResultSet.fieldByName('title'));
```
Does it display the title you expect?

— commented August 31st 2011 by Doug Handy
Still struggling with this. I am now opening and closing the database and have fixed the issue with that. Now it seems that the database is confusing values with column.

Here is the error message:

, com.plausiblelabs.pldatabase.error.query.string=UPDATE person SET nickName = NickNam WHERE id=2, com.plausiblelabs.pldatabase.error.vendor.string=no such column: NickNam}

I am the proper call but then it seems that it's trying to use the value as the column name and says it can't find the column.

Could this be a bug?

— commented September 1st 2011 by Leonardo Amigoni
Please post the exact code creating the SQL statement. This is a different table name (person) then the previous code (interests) so this must be from another block of code.

The error is either with your quoting or with how you do the ? substitution.

— commented September 1st 2011 by Doug Handy
Also, if you have no longer have the table locked issue, it would be helpful for future Q&A searches if you marked the "best" answer. That way people searching for a similar problem later get directed to the right answer quicker.

— commented September 1st 2011 by Doug Handy
Already marked this as the best answer.

Here is the code I use to execute.
```
dataBase.execute("UPDATE person SET "+fieldname+" = "+textField.value+" WHERE id="+win.id)
```
— commented September 1st 2011 by Leonardo Amigoni
I meant marking the other thread with a correct answer – the one talking about table locks which you have evidently now cleared up.

Anyway, this error is almost exactly the same thing as your original problem. You don't have the quotes right so the SQL command ends up with invalid syntax.

The quick way to fix that is quoting the update value:
```
dataBase.execute("UPDATE person SET "+fieldname+" = '"+textField.value+"' WHERE id="+win.id);
```
But as I previously mentioned, that breaks when the user happens to actually key into the field whichever quote character you are using (you used double quotes here, and single quotes in the original question).

Consider again what happens when they type: He is 6' 5" tall.

The better way to fix this is to use substitution variables:
```
dataBase.execute("UPDATE person SET "+fieldname+" = ? WHERE id="+win.id, textField.value);
```
This is not only safer from a user keying a quote, but it helps protect against a SQL injection attack.

— commented September 1st 2011 by Doug Handy
Thanks Doug. The issue is that I am trying to add this key to an array that gets executed at a later time. I can't seem to store it in the array in the second form which is the preferred one. I opened another thread about it here.

— commented September 1st 2011 by Leonardo Amigoni
//When you use the substitution method, quotes are less confusing. When you use the syntax you are trying here, it is imperative to get the quotes right. Numeric fields (like id) do not need quoting, but text fields do. Hopefully you can see why when looking at this SQL:
```
UPDATE person SET nickName = my nick name WHERE id=2

// vs

UPDATE person SET nickName = 'my nick name' WHERE id=2
```
The problem with this simple method of quoting is it breaks when the user keys text with the quote character you used:
```
// works:
UPDATE person SET nickName = "o'henry" WHERE id=2

// breaks:
UPDATE person SET nickName = 'o'henry' WHERE id=2

// works when we repeat any embedded quote:
UPDATE person SET nickName = 'o''henry' WHERE id=2
```
If you KNOW the user won't use either quote character you are "safe". Or if you KNOW the user may use one style but not the other (single vs double quote), then use the opposite when declaring your SQL statement. But if they can use either, then neither method is safe.

In this case what you need to do is examine their text and "double up" any instance of the quote character you used in the SQL. Otherwise you are a SQL syntax error waiting to happen.

But you'd be in good company. You'd be surprised how many people try to get by with building SQL statements without taking that into account!

— commented September 1st 2011 by Doug Handy
Oops, was thinking of another language when I said to double up embedded quotes. In javascript you would escape an embedded quote:
```
UPDATE person SET nickName = 'o\'henry' WHERE id=2
```
It is easiest to do that with a function similar to PHP's addslashes() then do something like:
```
dataBase.execute('UPDATE interests SET title= "' + addslashes(textField.value) + '" WHERE id='+id);
```
— commented September 3rd 2011 by Doug Handy

Thank you very much Dough. Unfortunately it did not work. The app still crashes in the simulator even with the changes you suggested.

I am a newbie at appcelerator but I see a message in the console saying:

[ERROR] Syntax Error = Parse error at interests.js (line 40)

Terminating in response to SpringBoard's termination.
~

That might be the reason. Although I can't seem to find the issue at line 40 even if I comment the line out it still gives me the same error.

— answered August 31st 2011 by Leonardo Amigoni
permalink

3 Comments

First, please use the comment facility to retain continuity of the thread when trying to clarify an existing answer, as opposed to starting a new answer. It makes the Q&A flow easier to follow.

Second, what is line 40? The dataBase.execute? If you comment out a line and you still get a parse error, there is most likely something in the surrounding code. Can you post a block of code with some lines both before and after line 40?

— commented August 31st 2011 by Doug Handy

I attached the whole interests.js code. Please keep in mind that I am a newbee and I am still learning my way around. Feel free to give me any advice.

I inserted the code you advised me to use on line 55. I am not sure how the simulation works but it appears to me that since it's terminated, it can't update the database.

Thanks so much for your help.

Ti.include('data.js')

var win = Ti.UI.currentWindow;
win.backgroundColor = '#FFFFCC';
var myResultSet = dataBase.execute('SELECT * FROM interests WHERE id = ' +win.id);
win.id =  myResultSet.fieldByName('id');
win.title = myResultSet.fieldByName('title');


var done = Titanium.UI.createButton({
    title:'Done',
    style:Titanium.UI.iPhone.SystemButtonStyle.DONE
    });

    done.addEventListener('click', function()
    {
        //win.setRightNavButton(edit);
        //myTableView.editing = false;
        win.close()
    });

win.setRightNavButton(done);

var nameLabel = Titanium.UI.createLabel({
    text:'Interest Name',
    color:'#000',
    font:{fontSize:15},
    textAlign:'left',
    width:'auto',
    height:'auto',
    top:20,
    left:20
    });

win.add(nameLabel);    

var title= Titanium.UI.createTextField({
    hintText:'',
    value:myResultSet.fieldByName('title'),
    top: 40,
    left: 20,
    width: 280,
    height: 40,
    textAlign:'left',
    paddingLeft:10,
    backgroundColor:'white',
    borderRadius:10,
    borderColor:'#bbb'

    });


    title.addEventListener('return',function(e)
    {

    dataBase.execute('UPDATE interests SET title= ? WHERE id='+win.id, title.value );    

    });

    title.addEventListener('blur',function(e)
    {

    });


win.add(title)


var notesLabel = Titanium.UI.createLabel({
    text:'Notes',
    color:'#000',
    font:{fontSize:15},
    textAlign:'left',
    width:'auto',
    height:'auto',
    top:90,
    left:20
    });
win.add(notesLabel);

var notes= Titanium.UI.createTextArea({
    value:myResultSet.fieldByName('notes'),
    top: 110,
    left: 20,
    width: 280,
    height:200,
    textAlign:'left',
    backgroundColor:'white',
    borderRadius:10,
    borderColor:'#bbb'
});

win.add(notes);

— commented August 31st 2011 by Leonardo Amigoni

This makes no sense. I commented out the whole file and I still get the error. I event commented out any references to the file from other files. I still get the error. There has to be something in the build that is getting corrupted.

— commented August 31st 2011 by Leonardo Amigoni

If you think something in the build is corrupted, just do a menu Project > Clean from Ti Studio. Then build it again. If the problem persists at runtime, place breakpoints shortly before the last activity you can see happening then start stepping through the code.

— answered August 31st 2011 by Doug Handy
permalink

10 Comments

Cleaned the project. No luck. Commented out the problematic area after clean. No Luck.

I am fairly new to coding, so I am not very familiar with breakpoints. Either way I set a breakpoint at line 39 or 38 and it seems to be fine till then. Don't really know what to do after that. I don't have that type of knowledge.

— commented August 31st 2011 by Leonardo Amigoni
Solved! But not in a nice way. I had to copy all my resource files. Start a new project. Copy them back in. Now it works with no errors.

I wish I knew what happened and how to fix it. The clean didn't seem to do anything in this case.

The issue with update still stands though.
I even used this code. Still crashes but now the project doesn't get corrupted it was the VALUE that was making it corrupt.
```
dataBase.execute('UPDATE interests SET title= ? WHERE id=1', 'title' );
```
— commented August 31st 2011 by Leonardo Amigoni
I am glad you got that resolved, but it is a separate issue from the original question about the SQL error. The problem with that was the lack of quotes, as shown in my original answer.

Once you have determined the SQL works, if you mark an answer as correct it helps ranking of future Q&A searches by users looking for answers to their own problem.

— commented August 31st 2011 by Doug Handy
Here is the error:

* Terminating app due to uncaught exception 'PLSqliteException', reason: 'Error occurred calling next on a PLSqliteResultSet. SQLite error: 'database table is locked' for 'UPDATE interests SET title= ? WHERE id=0''

Why would the Database Table be locked? I left the database open?

— commented August 31st 2011 by Leonardo Amigoni
Is you app using a single context paradigm (i.e., no url property when opening a new window, like Tweetanium) or a multiple context paradigm (i.e. with url property like kitchen sink)?

SQLite is not good at concurrent "users" and multiple contexts can trip it up. Try adding this after each insert / update type operation:
```
dataBase.execute("END TRANSACTION");
```
If you are doing any type of loop, wait until the end of the loop before ending the transaction to maximize performance.

— commented August 31st 2011 by Doug Handy
I am using a multiple context paradigm like kitchen sink.

When i use the "END TRANSACTION" i get a new error and crash

SQLite error: 'SQL logic error or missing database' for 'END TRANSACTION'

Thanks for your patience.

— commented August 31st 2011 by Leonardo Amigoni
Isn't dataBase the name of the variable with your database object? Variable names are case sensitive in javascript so be sure that is right.

I didn't think SQLite required a begin transaction, but here is how I really do it:
```
dataBase.execute("BEGIN IMMEDIATE TRANSACTION");
dataBase.execute( ... sql statement(s) ... );
dataBase.execute("END TRANSACTION");
```
— commented August 31st 2011 by Doug Handy
It doesn't seem to be enough. Do I need to close each statement too? Still get the locked message.

— commented August 31st 2011 by Leonardo Amigoni
It is hard to say. However, this thread has strayed to a new problem now.

I would suggest starting a new question devoted to the table lock issue for at least two reasons:
- More people are likely to see a new question and offer insights
- Future Q&A searches on locked databases will be more meaningful
I'd also suggest doing a Q&A search on database locks to see what information is already out there.

— commented August 31st 2011 by Doug Handy
Good Suggestion. I will do so.

— commented August 31st 2011 by Leonardo Amigoni

Titanium Community Questions & Answer Archive

We felt that 6+ years of knowledge should not die so this is the Titanium Community Questions & Answer Archive

3 Answers

Accepted Answer