Android Code Snippet SQLite DB table exists

SubName: DBTableExists

Author: "LucaMs"

Description: checks if a table exists in a SQLite database


Code:
B4X:
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

Well-Known Member
Licensed User
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.

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

Mahares

Expert
Licensed User
Since @Erel is a big proponent of parameterized queries, he should have carried it out even further:
B4X:
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:
B4X:
Cur = DB.ExecQuery2("SELECT name FROM sqlite_master WHERE type=? AND upper(name) = ?", _
Array As String("table", TableName.ToUpperCase))
or:
B4X:
Cur = DB.ExecQuery2("SELECT name FROM sqlite_master WHERE type=? AND lower(name) = ?", _
Array As String("table", TableName.ToLowerCase))
 
Top