AIR and sqLite : if table exists conditional

Question

How do I get a Boolean value in AS3 whether a table or an entry exists in the database?

Wisla

Answer 1

As opposed to finding it manually with SQL you should use the built in Schema information classes/functions. Here is an example of how it would work.

public function doesTableExist(connection:SQLConnection, tableName:String):Boolean
{
    connection.loadSchema();
    var schema:SQLSchemaResult = connection.getSchemaResult();

    for each (var table:SQLTableSchema in schema.tables)
    {
        if (table.name.toLowerCase() == tableName.toLowerCase())
        {
            return true;
        }
    }
    return false;
}

Bonn

This is the best way. AS3 SQLite does not support PRAGMA. Official Documentation – Darrendarrey

This does indeed work best. A few heeds of warning: 1.) I would suggest wrapping this whole function in a try-catch statement, because if you have NO tables at all in your database, an error will get thrown stating there is no schema to load, and of course cause issues during runtime. 2.) If you are using an async database connection you MUST make sure it has fully loaded before you start checking the schema, or else you will get an error complaining about the connection being bad. – Leatherette

Answer 2

There is no simple statement to achieve boolean value, but you can:

1. use PRAGMA table_info(tbl_status) and analize list.

2. try to execute SELECT col FROM table_name in try...catch block, in case of error simply set variable to bool.

BTW, maybe you need to use IF NOT EXISTS in create statement for table, index creation...

Doublecross

Thanks, I'll give that a try. I do already have CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS, but was wondering how to get this in as3 – Wisla

didn't get around to test it yet as I switched to a DataObject, but will give it a try when I get a chance, thanks. – Wisla

Answer 3

can be useful for someone - for async connection:

connection.loadSchema();
connection.addEventListener(SQLEvent.SCHEMA, check_result);

private function check_result(event:SQLEvent):void{
var schema:SQLSchemaResult = connection.getSchemaResult();
//as above
}

Congratulatory