Android Question How to know if a SQL table already exists

[Serge Bertet]

Hello all .... this is my first B4A question,

The question is actually in the title.

I found a lot of answer about this ... but nothing working.

Some use sql_lite, I am using SQL, I don't know if there is a difference (I use to us mySql when coding PHP).

My syntax is:

db.Initialize(File.DirInternal, dbFileName, True)
if db.ExecQuerySingleResult("SELECT count(*) FROM mytable") = Null <== ERROR HERE

I get:
android.database.sqlite.SQLiteException: no such table: mytable (code 1): , while compiling: SELECT count(*) FROM mytable

Even with Try Catch I'm unable to test.

Hope someone is able to help me. Thw.

 

[BillMeyer]

The code you posted is Sqlite - There is a difference between that and MySQL (pertaining to command syntax etc)

All you need to do is check if the table exists first and if not then create it.

Maybe this will point you in the right direction:

 'Create the Medication Reminder Table Here
    txt="CREATE TABLE IF NOT EXISTS  MyMeds (Serial INTEGER PRIMARY KEY AUTOINCREMENT, Medication TEXT, Acute TEXT, Frequency INTEGER, Tablets INTEGER, TabsRemain INTEGER, StartOn TEXT, StartAt TEXT, RemindOn TEXT, Status TEXT)"
    SQL1.ExecNonQuery(txt)

    Dim ret as Int
    ret = SQL1.ExecQuerySingleResult("SELECT count(*) FROM MyMeds")

    If ret <= 0 then
       msgbox("Table Exists BUT is Empty","Rec Count")
    ELSE
       msgbox("Table has "&ret" records in it","Rec Count")
    End If

PS. I've used SQL1. instead of db.

Enjoy !!

 

[Serge Bertet]

Many thanks for your help,
I did not remind the "CREATE TABLE IF NOT EXISTS" command, I'll try that, it is a good idea.
I just start usin B4A so i'm still in debug mode.
Do you know what append if the "ExecNonQuery" fail in release mode? Error, hang or shutdown of the app?

Serge