SQLite - Testing for existence of a table

Question

Created Jul ’23

Replies 1

Boosts 0

Views 1.4k

Participants 2

I am learning how to access SQLite without the use of Swift Data. I have a function that tests to see if a table exists and it works. The problem is that I do not understand why it works. It is my understanding that the SELECT statement returns a 0 if the table does not exist and 1 if it does. But the API step statement returns 101 (SQLITE_Done) if the table does not exist. But if the table does exist it does not return 101. Per the SQLite documentation it would appear that 101 means the operation has completed. What am I missing here and is there a way to capture the underlying SQLite 0 or 1 which would allow me to test for that? Below is my function.

func doesTableExist(db: OpaquePointer?) -> Bool {
    
    var tableExists: Bool = true
    
    let testForTable = """
    SELECT name FROM sqlite_master
    WHERE type='table'
    AND name='Contact';
    """
    
    var testForTablePtr: OpaquePointer?
    
    if sqlite3_prepare_v2(db, testForTable, -1, &testForTablePtr, nil) == SQLITE_OK {
        if sqlite3_step(testForTablePtr) == SQLITE_DONE {
            tableExists = false
        }
    } else {
        print("unable to compile sql statement testing to see if table exists")
    }
    return tableExists
}

Boost

Answer 1

Scott OP

Jul ’23

Accepted Answer

It is my understanding that the SELECT statement returns a 0 if the table does not exist and 1 if it does.

Actually that statement will return a result row if the table exists, and no result rows if it doesn’t exist. If you really want a result row containing a number every time, then you could use:

SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = 'Contact'

But this would require you to fetch the column value to check the number. Your original query actually makes this task easier by letting you simply check if a row was returned or not.

the API step statement returns 101 (SQLITE_Done) if the table does not exist.

That means sqlite3_step has finished stepping through all the result rows, of which there are none.

But if the table does exist it does not return 101.

That would be SQLITE_ROW which means it got a result row. If you were to call sqlite3_step once again (or in a loop) you would get SQLITE_DONE the next time.

1