Web and WebWorks Development

[cassidyd]

Hi

I've started to play around with Gears and SQlLite today and I'm getting an error when I try to insert values into a table with more than one column.

I have:

var db = google.gears.factory.create('beta.database');
    db.open('developerSet');
    db.execute('create table if not exists Developers (DeveloperName text, DeveloperAge int)');

    var devName = "Davy"
    var devAge = 32;

    try {
        db.execute('insert into Developers values (?, ?)', [devName, devAge]);
        alert('success');       
    }
    catch (e) {
        alert(e);
    }

I get the error:

 net.rim.device.api.database.DatabaseException; insert into Developers values (?,?): SQL logic error or missing database.

I'm using this reference:  http://code.google.com/apis/gears/api_database.html

Everything works if I just have one field like:

var db = google.gears.factory.create('beta.database');
    db.open('developerSet');
    db.execute('create table if not exists Developers (DeveloperName text)');

    var devName = "Davy"
    var devAge = 32;

    try {
        db.execute('insert into Developers values (?)', [devName]);
        alert('success');       
    }
    catch (e) {
        alert(e);
    }

 I'm using the Visual Studio 2.0 plug in for 2008 running Windows XP SP and simulator 2.13.0.56

Thanks

Davy

[astanley]

Hi Davy, thanks for your post.

Are you running this Gears DB code from within the BlackBerry browser , or from within your own BlackBerry Widget application?

The first time you ran the CREATE TABLE statement, did you create a database with 1 or 2 columns?  If it was 1 column, then the next time you ran your INSERT statement with values for 2 columns, I'd expect you would see that error (since the existing table only has 1 column in it). 

Your CREATE TABLE statement will not create a new table nor will it overwrite the existing table if one already exists (since you included the "if not exists" statement in the query).

Do you also have functionality in your application to DROP the table?   If not I'd encourage you to delete the database between tests.

Sincerely,

Adam

[cassidyd]

Hi Adam

Thanks - it's all working after I dropped the table and recreated.

I thought it would overwrite the old one. 

So, will that  DB persist between battery pulls etc?

On a device - is it better to drop tables when empty and recreate them when needed or leve them on the device empty?

Thanks again

Davy 

[astanley · Accepted solution]

Yes a SQLite DB will persist between battery pulls.  The database is saved either to internal eMMC or removable media (not device memory), depending on which is available on your device.

In general, its not considered a best practice to drop your table when it becomes empty, and re-create it again when you wish to add data.  Doing this adds extra overhead to the final delete and first insert for a given table.  Instead, define and finalize your schema before creating your table.  Once created, consider this schema static.

That being said, for the purposes of development, it may be easier to provide an easy way to drop your tables while you are developing your schema.

Cheers,

Adam