This error is caused by what? [ERROR] invalid SQL statement.

Question

This error is caused by what? [ERROR] invalid SQL statement.

You must Login before you can answer or comment on any questions.

My situation Application type :

mobile Titanium SDK : 2.1.1
Platform & version : iOS 5.1
Device : iOS simulator 
Host Operating System : mountain lion 
Titanium Studio : the last

file app.js

// ShoppingList
// main control window
 
Titanium.UI.setBackgroundColor('#000');
 
var itemData = [];
 
 
var tabGroup = Titanium.UI.createTabGroup({
    barColor: '#336699'
});
 
// create Items tab and root window
var itemWindowController = Ti.UI.createWindow({
    backgroundColor: '#fff',
    navBarHidden: true
});
 
var itemWindow = Titanium.UI.createWindow({  
    title: 'Lista',
    backgroundColor: '#fff'
});
 
var itemTab = Titanium.UI.createTab({  
    icon: 'KS_nav_views.png',
    title: 'Shopping List',
    window: itemWindowController
});
 
var addItemButton = Titanium.UI.createButton({
    systemButton:Titanium.UI.iPhone.SystemButton.ADD
});
 
addItemButton.addEventListener('click', function () {
    var createItemWindow = Titanium.UI.createWindow({
        url: 'createitem.js',
        title: 'Nuovo prodotto'
    });
 
    itemNavGroup.open(createItemWindow);
});
 
 
// Navigation Groups
var itemNavGroup = Ti.UI.iPhone.createNavigationGroup({
    window: itemWindow
});
itemWindowController.add(itemNavGroup);
 
 
//  add tabs
tabGroup.addTab(itemTab);  
 
 
tabGroup.open({
    transition: Titanium.UI.iPhone.AnimationStyle.FLIP_FROM_LEFT
});
 
// Load Table Views
var refreshItems = function(){
    var itemId, itemName, itemDesc;
    var db = Ti.Database.install( 'shoppingDB.sql', '' );
    var itemRS = db.execute('SELECT id, name FROM item ORDER BY UPPER(name)'); 
 
    while (itemRS.isValidRow()) { 
        itemId = itemRS.fieldByName('id'); 
        itemName = itemRS.fieldByName('name'); 
 
        itemData.push({
            title: itemName, 
            hasChild: true, 
            test: 'viewitem.js', 
            itemId: itemId
        }); 
        Ti.API.info(itemId + ' ' + itemName); 
        itemRS.next(); 
    } 
 
    itemRS.close();     
    db.close();
};
 
refreshItems();
 
 
// Table Views
var itemTableView = Ti.UI.createTableView({
    data: itemData,
    editable: true
});
 
itemTableView.addEventListener('delete', function (e) {
    var db = Ti.Database.install('shoppingDB.sql','');
    // check if row exists in table
    var itemRS = db.execute('SELECT id, name FROM item WHERE id=(?)', e.rowData.itemId);
    if (itemRS.rowCount != 0) {
        // delete row from item
        db.execute('DELETE FROM item WHERE id=(?)', e.rowData.itemId);
    } else {
        Ti.API.info('could not find id: ' + e.rowData.itemId + ' in item');
    }
 
    Ti.API.info('deleted: ' + e.rowData.title + 'with id: ' + e.rowData.itemId);
 
    itemRS.close();     
    db.close();
});
itemTableView.addEventListener('click', function (e) {
    if (e.rowData.test) {
        var itemWindow = Titanium.UI.createWindow({
            url:e.rowData.test,
            title:e.rowData.title
        });
        itemWindow.itemName = e.rowData.title;
        itemWindow.itemId = e.rowData.itemId;       
        itemWindow.navGroup = itemNavGroup;
 
        itemNavGroup.open(itemWindow);
    }
});
 
 
 
// inserts item into tableview alphabetically 
var insertItemIntoTableViewArray = function (newItem, tableview) {
    var tableData = tableview.data[0];
    var newData = [];
    if (!tableData){
        Ti.API.info('tabledata not found');
        newData.push(newItem);
    } else {
        Ti.API.info('tabledata FOUND!');
        var j = 0;
        var spotFound = false;
        var tableSize = tableData.rowCount;
        for (var i = 0; i<tableSize; i++) {
            if( tableData.rows[i].title.toUpperCase() > newItem.title.toUpperCase() && !spotFound){
                newData[j] = newItem;
                spotFound = true;
                j++;
            }
            newData[j] = {
                title: tableData.rows[i].title, 
                hasChild: tableData.rows[i].hasChild, 
                test: tableData.rows[i].test 
            };
            if (tableData.rows[i].locId) { 
                newData[j].locId = tableData.rows[i].locId; 
            } else {
                newData[j].itemId = tableData.rows[i].itemId;
            }
            j++;
        }
        if (!spotFound) {
            newData[j] = newItem;
        }
    }
 
    tableview.setData(newData);
};
 
var updateItemNameInTable = function (item, tableview){
    var tableData = tableview.data[0];
    var newData = [];
    var tableSize = tableData.rowCount;
    for (var i = 0; i<tableSize; i++) {
        newData[i] = {
            hasChild: tableData.rows[i].hasChild, 
            test: tableData.rows[i].test ,
            itemId: tableData.rows[i].itemId
        };
        if ( tableData.rows[i].itemId === item.itemId ) {
            newData[i].title = item.title;
            Ti.API.info(tableData.rows[i].itemId +'==='+ item.itemId);
        } else {
            newData[i].title = tableData.rows[i].title;
            Ti.API.info(tableData.rows[i].itemId + '!=' + item.itemId);
        }
    }
    tableview.setData(newData);
};
 
// custom event fires after successful addition of item
Ti.App.addEventListener('addItemToView', function (e) { 
    var newItem = {
        title: e.title, 
        hasChild: true, 
        test: 'viewitem.js', 
        itemId: e.itemId
    };
    insertItemIntoTableViewArray(newItem, itemTableView);
 
});
 
// custome even fires after successfull update to item
Ti.App.addEventListener('updateItemInView', function (e) {
    var updatedItem = {
        title: e.title, 
        itemId: e.itemId
    };
    updateItemNameInTable(updatedItem, itemTableView);
});
 
// Add UI elements to window
itemWindow.add(itemTableView);
itemWindow.leftNavButton = addItemButton;

file createitem.js

// View used to create and edit items
 
var win = Titanium.UI.currentWindow;
win.backgroundImage = 'sfondoDOPPIO.png';  // add + modifica dio cane la madonna
var navGroup = win.navGroup;
var itemId = win.itemId;
var itemName = win.itemName;
var itemDescription = win.itemDescription;
 
 
var newName = '';
var newDescription = '';
 
var nameLabel = Titanium.UI.createLabel({
    color: 'black',
    text: 'Nome Prodotto',
    top: 80,
    left: 40,
    width: 'auto',
    height: 'auto',
    font:{fontFamily:'Brush Script MT',fontSize:23}
});
 
var nameTextField = Titanium.UI.createTextField({
    color: '#336699',
    top: 105,
    left: 40,
    width: 250,
    height: 30,
    enabled: true,
    value: itemName,
    keyboardType: Titanium.UI.KEYBOARD_DEFAULT,
    returnKeyType: Titanium.UI.RETURNKEY_DEFAULT,
    borderStyle: Titanium.UI.INPUT_BORDERSTYLE_ROUNDED,
    clearButtonMode: Titanium.UI.INPUT_BUTTONMODE_ONFOCUS
});
nameTextField.addEventListener('return', function (e) {
    nameTextField.blur();
});
 
var descriptionLabel = Titanium.UI.createLabel({
    color: 'black',
    text: 'Descrizione prodotto',
    top: 150,
    left: 40,
    width: 'auto',
    height: 'auto',
    font:{fontFamily:'Brush Script MT',fontSize:23}
});
 
var descriptionTextField = Titanium.UI.createTextField({
    color: '#336699',
    top: 175,
    left: 40,
    width: 250,
    height: 30,
    enabled: true,
    value: itemDescription,
    keyboardType: Titanium.UI.KEYBOARD_DEFAULT,
    returnKeyType: Titanium.UI.RETURNKEY_DEFAULT,
    borderStyle: Titanium.UI.INPUT_BORDERSTYLE_ROUNDED,
    clearButtonMode: Titanium.UI.INPUT_BUTTONMODE_ONFOCUS
});
descriptionTextField.addEventListener('return', function (e) {
    descriptionTextField.blur();
});
 
 
win.addEventListener('click', function(e) {
    nameTextField.blur();
    descriptionTextField.blur();
});
 
var createButton = Titanium.UI.createButton({
    title: 'Add'
});
 
 
if (itemId) {
    createButton.title = 'Update';
}
 
 
createButton.addEventListener('click', function () {
    // save all form values
    newName = nameTextField.value;
    newDescription = descriptionTextField.value;
 
    // Add the new item to the database
    var db = Ti.Database.install('shoppingDB.sql', '');
    var nameRS;
    if (newName) {
        if (itemId) {
            nameRS = db.execute('SELECT name FROM item WHERE id!=(?) AND name=(?)', itemId, newName);
            if (nameRS.rowCount === 0) {
                Ti.API.info('About to update with name: ' + newName + 
                    ' description: ' + newDescription
                );
                db.execute('UPDATE item SET name=(?),description=(?) WHERE id=(?)', 
                    newName, newDescription, itemId
                );
                // alert confirmation
                alert(newName + ' updated');
                Ti.App.fireEvent('updateViewItemValues');
                Ti.App.fireEvent('updateItemInView',{
                    title: newName, 
                    itemId: itemId
                });
            } else {
                alert(newName +' already exists');
            }
            nameRS.close();
            db.close();
            // bug - If you close the window the rightNavButton will still be there
            // navGroup.close(win);
            Ti.App.fireEvent('closeCreateItem');
        } else {
            // check for item with that name in db
            var itemRS = db.execute('SELECT id FROM item WHERE name=(?)', newName);
            if (itemRS.rowCount === 0) {
                Ti.API.info('name: ' + newName + 
                    ' description: ' + newDescription
                );
                db.execute('INSERT into item (name,description) VALUES (?,?)',
                    newName, newDescription);
                // lookup item by name to get id
                var idRS = db.execute('SELECT id FROM item WHERE name=(?)', newName);
                var lastInsertId;
                if (idRS.isValidRow()) { 
                    lastInsertId = idRS.fieldByName('id');
                }
                // custom event to add item to table view
                Ti.App.fireEvent('addItemToView', { 
                    title: newName, 
                    itemId: lastInsertId 
                });
                // alert confirmation
                alert(newName + " added");
                nameTextField.value = '';
                descriptionTextField.value = '';
            } else {
                alert(newName +' already exists');
            }
            itemRS.close();
        }
    }else{
        alert('Name can\'t be blank');
    }
    db.close();
});
 
// Add UI elements to window
win.rightNavButton = createButton;
win.add(nameTextField);
win.add(descriptionTextField);
win.add(nameLabel);
win.add(descriptionLabel);

file viewitem.js

// View used to view items
 
var win = Titanium.UI.currentWindow;
win.backgroundImage= 'sfondoDOPPIO.png'; //----------------
 
 
var navGroup = win.navGroup;
 
var itemId = win.itemId;
var itemName = '';
var itemDescription = '';
var editItemWin;
 
// Get Item from db
var getItem = function() {
    var db = Ti.Database.install( 'shoppingDB.sql', '' );
    var itemRS = db.execute('SELECT id,name,description FROM item WHERE id=(?)', itemId);
    if (itemRS.rowCount === 0) {
        alert('could not find item');
        Ti.API.error('could not find item with id: ' + itemId + ' in database: item');
    }
    while (itemRS.isValidRow()) {  
        itemName = itemRS.fieldByName('name');
        itemDescription = itemRS.fieldByName('description');
        Ti.API.info(itemId + ' ' + itemName); 
 
        itemRS.next(); 
    } 
 
    itemRS.close();     
    db.close();
};
 
getItem();
 
var editButton = Ti.UI.createButton({
    title: 'Edit'
});
editButton.addEventListener('click', function () {
    // make new window for createitem.js to use for editing item
    //info2.visible=true;
    editItemWin = Titanium.UI.createWindow({
        url: 'createitem.js',
        title: itemName,
        backgroundImage:'sfondoDOPPIO.png'//----------------
    });
    editItemWin.itemId = itemId;
    editItemWin.itemName = itemName;
    editItemWin.itemDescription = itemDescription;
 
    editItemWin.navGroup = navGroup;
    navGroup.open(editItemWin);
});
 
var nameLabel = Titanium.UI.createLabel({
    color: 'black',
    text: 'Nome Prodotto\n\n\n\n\n    Premi "edit" per modificare\n         nome e/o descrizione\n       del prodotto selezionato!',
    top: 80,
    left: 40,
    width: 'auto',
    height: 'auto',
    font:{fontFamily:'Brush Script MT',fontSize:23}
});
 
var nameTextField = Titanium.UI.createTextField({
    color: '#336699',
    top: 105,
    left: 40,
    width: 250,
    height: 30,
    enabled: false,
    value: itemName,
    keyboardType: Titanium.UI.KEYBOARD_DEFAULT,
    returnKeyType: Titanium.UI.RETURNKEY_DEFAULT,
    borderStyle: Titanium.UI.INPUT_BORDERSTYLE_ROUNDED,
    clearButtonMode: Titanium.UI.INPUT_BUTTONMODE_ONFOCUS
});
 
var descriptionLabel = Titanium.UI.createLabel({
    color: 'black',
    text: 'Descrizione prodotto',
    top: 150,
    left: 40,
    width: 'auto',
    height: 'auto',
    font:{fontFamily:'Brush Script MT',fontSize:23}
});
 
var descriptionTextField = Titanium.UI.createTextField({
    color: '#336699',
    top: 175,
    left: 40,
    width: 250,
    height: 30,
    enabled: false,
    value: itemDescription,
    keyboardType: Titanium.UI.KEYBOARD_DEFAULT,
    returnKeyType: Titanium.UI.RETURNKEY_DEFAULT,
    borderStyle: Titanium.UI.INPUT_BORDERSTYLE_ROUNDED,
    clearButtonMode: Titanium.UI.INPUT_BUTTONMODE_ONFOCUS
});
 
 
 
var populateTableValues = function () {
    nameTextField.value = itemName;
    descriptionTextField.value = itemDescription;
};
 
Ti.App.addEventListener('updateViewItemValues', function () {
    getItem();
    populateTableValues();
});
 
Ti.App.addEventListener('closeCreateItem', function () {
    // API Bug - when closing window, rightNavButton still remains
    navGroup.close(editItemWin);
});
 
// Add UI elements to window
win.rightNavButton = editButton;
win.add(nameLabel);
win.add(nameTextField);
win.add(descriptionLabel);
win.add(descriptionTextField);

The error is this(line 63 page app.js)

[ERROR] invalid SQL statement. Error Domain=com.plausiblelabs.pldatabase Code=3 "An error occured parsing the provided SQL statement." UserInfo=0x9b841b0 {com.plausiblelabs.pldatabase.error.vendor.code=1, NSLocalizedDescription=An error occured parsing the provided SQL statement., com.plausiblelabs.pldatabase.error.query.string=SELECT id, name, description FROM item ORDER BY UPPER(name), com.plausiblelabs.pldatabase.error.vendor.string=no such table: item}  in -[TiDatabaseProxy execute:] (TiDatabaseProxy.m:186)
[ERROR] Script Error = invalid SQL statement. Error Domain=com.plausiblelabs.pldatabase Code=3 "An error occured parsing the provided SQL statement." UserInfo=0x9b841b0 {com.plausiblelabs.pldatabase.error.vendor.code=1, NSLocalizedDescription=An error occured parsing the provided SQL statement., com.plausiblelabs.pldatabase.error.query.string=SELECT id, name, description FROM item ORDER BY UPPER(name), com.plausiblelabs.pldatabase.error.vendor.string=no such table: item}  in -[TiDatabaseProxy execute:] (TiDatabaseProxy.m:186) at app.js (line 63).

1 Answer

Your Answer

Think you can help? Login to answer this question!

Accepted Answer

Accepted Answer

Shot in the dark, but the error is caused by a bad SQL query.

Specifically: no such table: item

The database that you have asked Titanium to open, apparently does not an actual table named item in it.