Skip navigation
Currently Being Moderated

New to developing could anyone point me in the right direction for sqlite issue?

Nov 11, 2012 11:16 AM

Tags: #air #flex-mobile #database #sqllite #flex4.6

Hello I am a new developer in training trying to work on my first project I have been hit by a major roadblock that i have worked on correcting for 5 days before posting here.  I am currently using flash builder 4.6 coding in flex.  I am trying to make a database that is able to be accessed accross multiple views.   I am also trying to hook up the date spinners so it search's in the Archive view between the selected dates for entries. However for the life of me I cannot seem to get a database up and running.

 

I keep getting the following errors.

 

Database is now open

Error #2044: Unhandled SQLErrorEvent:. errorID=3115, operation=execute , message=Error #3115: SQL Error. , details=no such table: 'stb'   //this error is from the Journal view

Database is now open

Error #2044: Unhandled SQLErrorEvent:. errorID=3115, operation=execute , message=Error #3115: SQL Error. , details=no such table: 'stb' // this error is from the Archive View

 

 

 

 

 

 

// Journal view code

 

<s:View xmlns:fx="http://ns.adobe.com/mxml/2009"

                    xmlns:s="library://ns.adobe.com/flex/spark" title="Journal" creationComplete="createDatabase()">

 

          <fx:Script>

                    <![CDATA[

 

                              import flash.data.SQLConnection;

                              import flash.data.SQLStatement;

                              import flash.errors.SQLError;

                              import flash.events.Event;

                              import flash.events.SQLErrorEvent;

                              import flash.events.SQLEvent;

                              import flash.events.TimerEvent;

                              import flash.filesystem.File;

                              import flash.utils.Timer;

 

                              import mx.collections.ArrayCollection;

                              import mx.utils.ObjectUtil;

                              import org.osmf.events.TimeEvent;

 

 

 

 

 

                              public var sqlc:SQLConnection = new SQLConnection();

 

                              public var dbFile:File;

                              public var dbConnection:SQLConnection;

 

 

                              private function createDatabase():void

                              {

                                        dbFile = File.applicationStorageDirectory.resolvePath("assets/appsimplicity.sq lite"); //last modified

                                        dbConnection = new SQLConnection();

                                        dbConnection.addEventListener(SQLEvent.OPEN, onDatabaseOpen);

                                        dbConnection.addEventListener(SQLEvent.CLOSE, onDatabaseClose);

 

                                        dbConnection.openAsync(dbFile);

 

                              }

 

 

                              private function onDatabaseOpen(evt:SQLEvent):void

                              {

                                        trace("Database is now open");

                                        var statement:SQLStatement = new SQLStatement();

                                        statement.sqlConnection = dbConnection;

                                        statement.sqlConnection.open(appsimplicity.sqlite, SQLMode.READ);

                                        statement.text = "CREATE TABLE IF NOT EXISTS stb (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT , entry TEXT)";          ///working on issue here

 

                              }

 

                              private function onDatabaseClose(evt:SQLEvent):void

                              {

                                        trace("Database is now closed");

                              }

 

                              private function saveJournal():void

                              {

 

                                        var statement:SQLStatement = new SQLStatement();

                                        statement.sqlConnection = dbConnection;

                                        statement.text = "INSERT INTO stb_ (title , entry) VALUES ('"+title_txt.text+"','"+entry.text+"')"; //is this maybe wrong?

 

                                        statement.execute();

 

                                        title_txt.text = "";

                                        entry.text = "";

 

                              }

 

                              //debug log

                              //[SWF] Main.swf - 3,574,774 bytes after decompression

                              //Database is now open

                              //Error #2044: Unhandled SQLErrorEvent:. errorID=3115, operation=execute , message=Error #3115: SQL Error. , details=no such table: 'journallistb'

 

 

 

 

                    ]]>

          </fx:Script>

 

 

 

 

 

          <fx:Declarations>

                    <!-- Place non-visual elements (e.g., services, value objects) here -->

          </fx:Declarations>

 

 

 

          <s:Label id="title_lable_txt" x="10" y="48" width="124" height="21" color="#E2881E" text="Title:"/>

          <s:TextInput id="title_txt" x="47" y="36" width="146" enabled="true" prompt="Entry Title" needsSoftKeyboard="true" />

          <s:TextArea id="entry" x="9" y="120" height="147" enabled="true" prompt="Entry" needsSoftKeyboard="true" />

          <s:Button id="savejournal" x="11" y="326" width="296" label="Save" click="saveJournal()" />

          <s:Button id="addphotostojournal" x="11" y="275" width="296" label="Add Photos"/>

          <s:Label id="journal" x="10" y="97" color="#E2881E" text="Journal:"/>

          <s:Label id="Datefield" x="145" y="97" color="#E2881E" text="Date:"/>

          <s:TextInput id="date" x="187" y="78" width="120" height="34" enabled="true" prompt="enter date"/>

 

 

 

 

</s:View>

 

 

//Archive view code

 

<?xml version="1.0" encoding="utf-8"?>

<s:View xmlns:fx="http://ns.adobe.com/mxml/2009"

                    xmlns:s="library://ns.adobe.com/flex/spark"

                    title="Archive" creationComplete="createDatabase()">

 

 

 

 

          <fx:Script>

                    <![CDATA[

 

 

                              import mx.collections.ArrayCollection;

 

                              public var dbFile:File

                              public var dbConnection:SQLConnection;

                              public var selectQuery:SQLStatement;

 

                              private function createDatabase():void

                              {

                                        dbFile = File.applicationDirectory.resolvePath("appsimplicity.sqlite");

                                        dbConnection = new SQLConnection();

                                        dbConnection.addEventListener(SQLEvent.OPEN, onDatabaseOpen);

                                        dbConnection.addEventListener(SQLEvent.CLOSE, onDatabaseClose);

 

 

                                        dbConnection.openAsync(dbFile);

                              }

 

                              private function onDatabaseOpen(evt:SQLEvent):void

                              {

                                        trace("Database is now open");

                                        selectQuery = new SQLStatement();

                                        selectQuery.sqlConnection = dbConnection;

                                        selectQuery.text = "SELECT * FROM stb ORDER BY title ASC";

                                        selectQuery.addEventListener(SQLEvent.RESULT, onQueryResult);

 

                                        selectQuery.execute();

                              }

 

                              private function onQueryResult(evt:SQLEvent):void

                              {

                                        archivefiles.dataProvider = new ArrayCollection(selectQuery.getResult().data);

                              }

 

                              private function onDatabaseClose(evt:SQLEvent):void

                              {

                                        trace("Database is now Closed");

                              }

 

                    ]]>

          </fx:Script>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

          <fx:Declarations>

                    <!-- Place non-visual elements (e.g., services, value objects) here -->

          </fx:Declarations>

 

 

          <s:DateSpinner id="Startdate" x="66" y="54" width="40" height="60"/>

 

          <s:List id="archivefiles" x="69" y="119" width="185" height="138" enabled="true" labelField="title" ></s:List>

 

          <s:Button id="Delete" x="68" y="313" width="185" label="Delete"/>

 

          <s:VScrollBar x="248" y="120" height="138"/>

 

          <s:DateSpinner id="Enddate" x="68" y="361" width="40" height="50"/>

 

          <s:Label id="adspace" x="0" y="0" width="321" height="36" color="#751E1E" text="AdSpace"/>

 

 

 

 

</s:View>

 

 

I made the data base using sqlite manager "Firefox plugin" then copied it to a new package I called assets. inside the project SRC folder.  I am at a lose, and any help would be greatly appreciated! 

 

Message was edited by: JeanneM7289  updated with tags

 
Replies 1 2 Previous Next
  • Currently Being Moderated
    Nov 12, 2012 1:44 AM   in reply to JeanneM7289

    Whoah, hold your horses... You cannot expect volunteers to reply within one hour. Some patience is appreciated.

     

    Try replacing the following methods. I've added some logging, and added a statement.execute()-statement.

     

     

    private function createDatabase():void

                                  {

                                            dbFile = File.applicationStorageDirectory.resolvePath("assets/appsimplicity.sq lite"); //last modified

                                            dbConnection = new SQLConnection();

                                            dbConnection.addEventListener(SQLEvent.OPEN, onDatabaseOpen);

                                            dbConnection.addEventListener(SQLEvent.ERROR, onDatabaseError);        //***** added by bblommers

                                            dbConnection.addEventListener(SQLEvent.CLOSE, onDatabaseClose);

     

                                            dbConnection.openAsync(dbFile);

     

                                  }

     

                                 private function onDatabaseError(evt:SQLEvent):void     ////************ added by bblommers

    {

         trace('Error with the database: '+evt);

    }

     

     

                                  private function onDatabaseOpen(evt:SQLEvent):void

                                  {

                                            trace("Database is now open");

                                            var statement:SQLStatement = new SQLStatement();

                                            statement.sqlConnection = dbConnection;

                                            statement.sqlConnection.open(appsimplicity.sqlite, SQLMode.READ);

                                            statement.text = "CREATE TABLE IF NOT EXISTS stb (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT , entry TEXT)";          ///working on issue here

                                            statement.execute();                    //************* added by bblommers

                                             trace("Table is now created");     //************ added by bblommers

                                  }

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 12, 2012 8:56 PM   in reply to JeanneM7289

    Were I you, I'm not sure I'd take off on Peter and Joseph, both big contributors in the Flex community. If the tutorials you're looking at don't work for you, remember it's hard to predict exactly where everyone, as far as experience, is coming into an instructional piece. Re: your error, so you're using AIR with SQLite? The error message you're getting is pretty descriptive, it looks like you're already opened a connection and you're trying to open it again. As far as SQLite goes, I'd Google Paul Robertson. He's done a whole lot on SQLite and Flex and even has a couple of videos out there from old presentations that remain helpful. His things are where I learned most of my SQLite/AIR knowledge.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 12, 2012 11:43 PM   in reply to JeanneM7289

    It looks like you're opening the database twice:

     

    private function onDatabaseOpen(evt:SQLEvent):void

    {

         [..]

         statement.sqlConnection.open("appsimplicity.sqlit e", SQLMode.READ);

         [...]

    }

     

    I don't think this line is necessary, since this connection already is open.

     

    Edit: This would result in the Error: Error #3101: Database connection is already open.

    The second error, that the database table 'std' does not exist, is a result of this; the method fails before it can get to creating that table.

     

    Message was edited by: BBlommers, explanation why and how

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 13, 2012 9:29 AM   in reply to JeanneM7289

    And the stb table exists outside of being created in your program? i.e., the obvious, do you see the stb table if you look at it through a SQL admin tool of some sort?

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 13, 2012 10:11 AM   in reply to JeanneM7289

    I'm more suspicious of the createTable statement. Though I don't have time to diagnose the way you're creating it (which is fine) I use a queue and I certainly have to construct the whole string, not use the words as you would in a direct SQL statement. For instance, this works for me:

     

    q.addItem( "CREATE TABLE Addresses (" + "    GUID TEXT PRIMARY KEY , " + "    address1 TEXT, " + "    address2 TEXT, " + "    city TEXT, " + "    state TEXT, " + "    zip TEXT, " + "  addressType TEXT, " + "  country TEXT, " + "    latitude numeric, " + "    longitude numeric" + ")", [], true );

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 13, 2012 10:38 AM   in reply to JeanneM7289

    Should that be

     

    var createStatement:SQLStatement = new SQLStatement();

    createStatement.sqlConnection .....

     

    .....

    createStatement.execute()

     

    ?

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 13, 2012 11:33 AM   in reply to JeanneM7289

    Now it sounds like the connection isn't open again. I'm about to take off. If you haven't already run into this one, it seems like a good intro by Paul. Just double check spellings and syntax:

     

    http://www.adobe.com/devnet/air/flash/quickstart/articles/simple_sql_d atabase.html

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 14, 2012 9:59 AM   in reply to John Hall

    Did you ever get it working?

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 14, 2012 4:17 PM   in reply to JeanneM7289

    If you're willing to post a link to a bare-bones example of where you're at, I'm sure that more than one person would try to help.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 15, 2012 2:54 AM   in reply to JeanneM7289

    In post #16, I think you we're pretty close. However, there is no need to instantiate the createStatement-object twice.

    The moment you instantiate the object after the sqlConnection is set, this connection is disregarded.

     

    The creation complete statement only works with statements, not with function initializers.

    To execute a different AS-file, use something like this:

     

    creationComplete("new MyDbObject().sqlConnect('dbName','create table[…]','[…]')")

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 16, 2012 7:31 AM   in reply to JeanneM7289

    Well, can't say this is all best practice below (and I'm too lazy to protect my reputation by making it so ;-) but something along these lines should at least create the database:

     

    <?xml version = "1.0" encoding = "utf-8"?>

    <s:WindowedApplication xmlns:fx = "http://ns.adobe.com/mxml/2009"

                                                         xmlns:s = "library://ns.adobe.com/flex/spark"

                                                         xmlns:mx = "library://ns.adobe.com/flex/mx"

                                                         creationComplete="init()">

              <fx:Script>

                        <![CDATA[

                                  protected var dbFile : File

                                  protected var dbConnection : SQLConnection;

                                  protected var createStatement:SQLStatement;

     

                                  private function init():void{

                                            dbFile = File.documentsDirectory.resolvePath( "appsimplicity.db" );

                                            dbConnection = new SQLConnection();

                                            dbConnection.addEventListener( SQLEvent.OPEN, onDatabaseOpen );

                                            dbConnection.openAsync( dbFile );

                                  }

     

                                  private function onDatabaseOpen( evt : SQLEvent ) : void {

                                            createStatement = new SQLStatement();

                                            createStatement.sqlConnection = dbConnection;

                                            createStatement.text = "CREATE TABLE IF NOT EXISTS stb (      " +

                                                      "id          INTEGER PRIMARY KEY AUTOINCREMENT, " +

                                                      "title TEXT,                                    " +

                                                      "entry TEXT,                                    " +

                                                      "date INTEGER                                   " +

                                                      ")";                                            //working on issue here

                                            createStatement.execute();                                //************ added by bblommers

                                  }

                        ]]>

              </fx:Script>

     

              <s:List id = "archivefiles"

                                  x = "69"

                                  y = "119"

                                  width = "185"

                                  height = "138"

                                  enabled = "true"

                                  labelField = "title">

              </s:List>

    </s:WindowedApplication>

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 16, 2012 7:34 AM   in reply to John Hall

    Whoops, I guess out of habit I put it in the documents folder, but you get the idea.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 20, 2012 10:25 AM   in reply to JeanneM7289

    Yes, it should create a physical file that you can open with a SQLite editor. You don't see anything in your appData/Local/ folder that looks like it? Are you showing hidden files? I'm clueless on Mac location though, using Windows.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 31, 2012 1:40 AM   in reply to JeanneM7289

    Hi JeanneM7289 - I only just found this thread, apologies - are you still having problems with SQLite? Feel free to reach out to me directly, peter.elst at gmail and I'd be happy to help you along.

     

    I don't disagree that a few of the topics in the video training, including SQLite are a bit rushed - that said in our defense we wanted to cover a broad spectrum of topics for people starting with mobile development using Flex and AIR, something that turned out to be difficult within the time constraints given. Appreciate the feedback though.

     
    |
    Mark as:
  • Currently Being Moderated
    Dec 31, 2012 9:36 AM   in reply to JeanneM7289

    If you like feel free to email me your latest source code and I'll see if I can debug it for you. It sounds like a relatively easy problem to debug, shouldn't even have to contemplate a support contract.

     

    I've worked on a pretty cool wrapper class that makes it easy to set up SQL databases with an MXML tag, if you'd like to try that:

     

    http://code.google.com/p/sqlite-wrapper/

     

    I noticed earlier you're going for applicationStorageDirectory which doensn't actually exist until the application is installed so that doesn't work in the emulator. You can try just changing it to something like File.desktopDirectory and see if it shows up.

     

    Cheers,

    Peter

     
    |
    Mark as:
  • Currently Being Moderated
    Jan 1, 2013 8:24 AM   in reply to Peter Elst

    I certainly hope it helps Jeanne but I know I'll take advantage of it. Thanks for sharing.

     
    |
    Mark as:
1 2 Previous Next

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points