Tuesday, May 17, 2011

How to check if a table exists in an SQLite database

In this tutorial we will learn how to see if a table exists in an SQLLite database.

Sometimes, it is needed to find out whether a table already exists in the database or not.

If you are creating a database, you can simply have "CREATE IF NOT EXISTS" in your query, but sometimes you'll need to see if a table exists not to create it, but to, for example, insert rows into it.

For example, I have a table which has columns Month, 1, 2, 3... and all the way to 31. I want to insert 12 rows containing a month's name and data for that month. This can be used for storing holidays data or something.

Now, I only want to add these 12 rows once. And that must happen only when I create the table. If the table didn't exist, it is created, and the 12 rows are added. If the table exists, it is not created again, but the 12 rows are added again... I don't want that. I need some kind of a check to be there to see if the table already exists, and if it does - don't add anything.

This can be done by taking advantage of the Responder class.

We can perform an action to the table, for example, send a SELECT command and specify the table's name. Then, we set an executer and add a Responder object in the parameter. The responder has 2 parameters, one for the function that is called when the action succeeds, and one for the function if an error occurs.

var st:SQLStatement = new SQLStatement();
st.sqlConnection = connection;
st.text = "SELECT * FROM tablename";
st.execute(-1, new Responder(sqlYes, sqlNo));

So, if the command succeeds, we call the sqlYes function. If it throws an error, we call the sqlNo function.

Now, when could an error happen in a situation like this? It could happen, if the table with the name we've specified couldn't be found. And that is exactly what we're looking for! If the table does not exist - the sqlNo function is called.

var st:SQLStatement = new SQLStatement();
st.sqlConnection = connection;
st.text = "SELECT * FROM tablename";
st.execute(-1, new Responder(sqlYes, sqlNo));

function sqlYes(SQLResult)
{
trace("SQL: Table found");
}

function sqlNo(SQLResult)
{
trace("SQL: Table not found");
}

This question was asked around the web some times, and all the solutions I could find in 15 minutes involved something way more complicated than the method I've shown today.

Hope this snippet will be helpful.

Thanks for reading!

No comments:

Post a Comment