SQL table exists

[chuckyegg]

[SOLVED] Test if SQL table exists

Hi All,

I'm still very new to B4A, but I'm trying to find a way to detect whether an SQL table already exists.

If the table does exist I want to test for a record (if sql.recordcount>0 then OK)

Can anyone help me with how to construct an IF around table and record existence?

Ta
Chris

 

[chuckyegg]

Doh!

Am I being thick, can I just put a SELECT in a Try/EndTry and only create the table in the Catch section?

 

[parijs]

SQL.Initialize(File.DirRootExternal, "YourDir/your.db", False)
intCount = SQL.ExecQuerySingleResult("SELECT count(*) FROM Yourtable")
If intCount > "0" Then

And Look Here

 

[chuckyegg]

Thanks for that, and the FAQ link

In case it's useful to any other SQL newbies this code:
1. Looks for a table (creates table and inserts default values if table not found)
2. Looks counts records in that table (inserts default values if no records found)

   Dim strDefaultSettings As String
   strDefaultSettings  = "INSERT into Settings VALUES ('value1', 'value2', 'value3')"      
   sqlDB.Initialize(File.DirDefaultExternal, "sqlTest.db", True)
   
   If sqlDB.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name ='Settings'") = 0 Then
      'don't exist
      strSQL = "CREATE TABLE Settings (Field1 TEXT, Field2 TEXT, Field3 TEXT)"
      sqlDB.ExecNonQuery(strSQL)
      sqlDB.ExecNonQuery(strDefaultSettings)
   Else
      'exists
      If sqlDB.ExecQuerySingleResult("SELECT count(Field1) FROM Settings") = 0 Then
         'No records
         sqlDB.ExecNonQuery(strDefaultSettings)
      Else
         'records found
      End If
   End If