flex creating local sqlite

Question

am trying to convert xml to sqlite in my mobile flex application

which should populate a list in the application

but am getting these error :

SQLError: 'Error #3115: SQL Error.', details:'no such table: 'words'', operation:'execute', detailID:'2013'
    at flash.data::SQLStatement/internalExecute()
    at flash.data::SQLStatement/execute()
    at dao::WordsDAO/findByTerm()[C:\Users\sayydo1\Adobe Flash Builder 4.6\sayydo1\src\dao\WordsDAO.as:32]

and this is the code for my WordsDAO.as :

package dao
{
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.filesystem.File;
import flash.filesystem.FileMode;
import flash.filesystem.FileStream;

import dao.Words;

import mx.collections.ArrayCollection;

public class WordsDAO
{
    private var _sqlConnection:SQLConnection;

    public function get sqlConnection():SQLConnection
    {
        if (_sqlConnection)
            return _sqlConnection;
        openDatabase(File.documentsDirectory.resolvePath("EZDemo.db"));
        return _sqlConnection;
    }

    public function findByTerm(searchKey:String):ArrayCollection
    {
        var sql:String = "SELECT * FROM words WHERE term LIKE '%"+searchKey+"%'";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.parameters[1] = searchKey;
        stmt.execute();
        var result:Array = stmt.getResult().data;
        if (result)
        {
            var list:ArrayCollection = new ArrayCollection();
            for (var i:int=0; i<result.length; i++)
            {
                list.addItem(processRow(result[i]));   
            }
            return list;
        }
        else
        {
            return null;
        }
    }

    public function create(words:Words):void
    {
        trace(words.term);
        var sql:String = 
            "INSERT INTO words (id, term, defin, term1, defin1) " +
            "VALUES (?,?,?,?,?)";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.parameters[0] = words.id;
        stmt.parameters[1] = words.term;
        stmt.parameters[2] = words.defin;
        stmt.parameters[3] = words.term1;
        stmt.parameters[4] = words.defin1;
        stmt.execute();
        words.loaded = true;
    }

    protected function processRow(o:Object):Words
    {
        var words:Words = new Words();
        words.id = o.id;
        words.term = o.term == null ? "" : o.term;
        words.defin = o.defin == null ? "" : o.defin;
        words.term1 = o.term1 == null ? "" : o.term1;
        words.defin1 = o.defin1 == null ? "" : o.defin1;


        words.loaded = true;
        return words;
    }

    public function openDatabase(file:File):void
    {
        var newDB:Boolean = true;
        if (file.exists)
            newDB = false;
        _sqlConnection = new SQLConnection();
        _sqlConnection.open(file);
        if (newDB)
        {
            createDatabase();
            populateDatabase();
        }
    }

    protected function createDatabase():void
    {
        var sql:String = 
            "CREATE TABLE IF NOT EXISTS words ( "+
            "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "term VARCHAR(50), " +
            "defin VARCHAR(250), " +
            "term1 VARCHAR(50), " +
            "term1 VARCHAR(50), " + 
            "defin1 VARCHAR(250))";
        var stmt:SQLStatement = new SQLStatement();
        stmt.sqlConnection = sqlConnection;
        stmt.text = sql;
        stmt.execute();         
    }

    protected function populateDatabase():void
    {
        var file:File = File.applicationDirectory.resolvePath("assets/words.xml");
        var stream:FileStream = new FileStream();
        stream.open(file, FileMode.READ);
        var xml:XML = XML(stream.readUTFBytes(stream.bytesAvailable));
        stream.close();
        for each (var emp:XML in xml.words)
        {
            var words:Words = new Words();
            words.id = emp.id;
            words.term = emp.term;
            words.defin = emp.defin;
            words.term1 = emp.term1;
            words.defin1 = emp.defin1;


            create(words);
        }
    }

}
    }

Words.as :

package dao
{
import mx.collections.ArrayCollection;
import flash.utils.ByteArray;

[Bindable]
public class Words
{
    public var loaded:Boolean = true;

    public var id:int;
    public var term:String;
    public var defin:String;
    public var term1:String;
    public var defin1:String;



    }

}

i hope thats enough to clarify my problem.

edited further info...

Answer 1

I'm just guessing here, but as your code only checks for the existence of the DB file, could it be that it was created incorrectly previously (maybe because of the duplicate column problem mentioned below), ie without the table being present? That would result in the error you are experiencing.

There are also some other problems with your code, once fixed your snippet works fine for me (using dummy data).

Duplicate columns

Your code tries to create the term1 column twice.

"defin VARCHAR(250), " +
"term1 VARCHAR(50), " +
"term1 VARCHAR(50), " + // remove this row
"defin1 VARCHAR(250))";

This should normally throw an error and result in the table not being created.

Invalid statement parameters

You are defining an unnamed parameter on index 1.

stmt.parameters[1] = searchKey;

This alone should throw an error as numeric indices start at 0, but it would also throw an error when using proper indices as there are no parameters in your query.

Your code should be more like this:

var sql:String = "SELECT * FROM words WHERE term LIKE ?";
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = sqlConnection;
stmt.text = sql;
stmt.parameters[0] = '%' + searchKey + '%';
// ...

See http://help.adobe.com/.../flash/data/SQLStatement.html#parameters for more information on prepared statements.