Android Code Snippet SQLite DB table exists

SubName: DBTableExists

Author: "LucaMs"

Description: checks if a table exists in a SQLite database


Code:

Public Sub DBTableExists(DB As SQL, TableName As String) As Boolean
    Dim Result As Boolean = False
    Dim Cur As Cursor

    Try
        Cur = DB.ExecQuery("SELECT name FROM sqlite_master WHERE type='table' AND name = '" & TableName & "'")
        Result = (Cur.IsInitialized AND Cur.RowCount = 1)
    Catch
        Log("DBTableExists failed." & CRLF & LastException.Message)
    End Try
 
    Return Result
End Sub

Dependencies: None


Tags: Table, Tables, Database, DB, SQLite


("on demand ")

 

[eurojam]

SQLite table names are case insensitive, but in a comparison it is case sensitive. To work properly in all cases you need to add COLLATE NOCASE.

Cur = DB.ExecQuery("SELECT name FROM sqlite_master WHERE type='table' AND name = '" & TableName & "' COLLATE NOCASE")

 

[Erel]

Cur = DB.ExecQuery2("SELECT name FROM sqlite_master WHERE type='table' AND name = ? COLLATE NOCASE", Array (TableName))

 

[Mahares]

Since @Erel is a big proponent of parameterized queries, he should have carried it out even further:

Cur = DB.ExecQuery2("SELECT name FROM sqlite_master WHERE type=? AND name = ?  COLLATE NOCASE", _
Array As String("table", TableName) )

Also, as a substitute for COLLATE NOCASE, you can do one of these:

Cur = DB.ExecQuery2("SELECT name FROM sqlite_master WHERE type=? AND upper(name) = ?", _
Array As String("table", TableName.ToUpperCase))

or:

Cur = DB.ExecQuery2("SELECT name FROM sqlite_master WHERE type=? AND lower(name) = ?", _
Array As String("table", TableName.ToLowerCase))

 

[Erel]

Your examples are good. However I wouldn't say that there is any problem with the "hard coded" values. They are also good.

Building the query with string concatenation is a bad practice.

 

[raphael75]

Building the query with string concatenation is a bad practice.

Is it bad practice because of safety or speed or something else?

 

[Erel]

Using parameterized queries solves three problems:

1. SQL injections. If the field value comes from a an EditText for example, then someone can modify the query with all kinds of commands (https://xkcd.com/327/)
2. No need to escape the string.
3. The syntax is more clear.

 

[LucaMs]

Using parameterized queries solves three problems:

1. SQL injections. If the field value comes from a an EditText for example, then someone can modify the query with all kinds of commands (https://xkcd.com/327/)
2. No need to escape the string.
3. The syntax is more clear.

Double like!