check the Table if exist

Sinan Tuzcu · Feb 1, 2013

Helo,

can i checked the table, if the exist with follow code

B4X:

SQL1.ExecNonQuery("DROP TABLE IF EXISTS" & Name & ")

gruß
sinan

Mahares · Feb 1, 2013

Do not do that. You will delete the table. Do this:

B4X:

If Sql1.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name ='yourTableName'") = 0 Then
 msgbox("Does not exist","")
Else
 msgbox("Table exists","")
End If

Sinan Tuzcu · Feb 1, 2013

helo,

I would only check if the table exists, if not, then I want to create it.

B4X:

SQL1.ExecNonQuery("DROP TABLE IF EXISTS" & ayir2(0) & ")
SQL1.ExecNonQuery("CREATE TABLE"  & ayir2(0) & "(Nummer TEXT , Name TEXT, Mesaj TEXT , LinkAdres TEXT, Datum TEXT)")
SQL1.ExecNonQuery2("INSERT INTO"  & ayir2(0) & "VALUES(?,?,?,?,?)", Array As Object(ayir2(0), ayir2(1), ayir2(2), ayir2(3),ayir2(4)))

but doesn't work

Mahares · Feb 1, 2013

Replace this line:

B4X:

SQL1.ExecNonQuery("DROP TABLE IF EXISTS" & ayir2(0) & ")

with this line:

B4X:

SQL1.ExecNonQuery("DROP TABLE IF EXISTS " & ayir2(0))

Notice the space after EXISTS

EDIT: I forgot to mention also that you need a space here after TABLE like this: ("CREATE TABLE "
Also, you need a space after INTO like this: "INSERT INTO "

mc73 · Feb 1, 2013

I'm trying to understand... You want to delete a table and recreate it, or you want to create it in case it doesn't exist? If it's the second, you should use

B4X:

create table if not exists yourTable (and so on...)

SQLite Query Language: CREATE TABLE

Sinan Tuzcu · Feb 1, 2013

Mahares said:
Replace this line:

B4X:

SQL1.ExecNonQuery("DROP TABLE IF EXISTS" & ayir2(0) & ")

with this line:

B4X:

SQL1.ExecNonQuery("DROP TABLE IF EXISTS " & ayir2(0))

Notice the space after EXISTS

EDIT: I forgot to mention also that you need a space here after TABLE like this: ("CREATE TABLE "
Also, you need a space after INTO like this: "INSERT INTO "

only after TABLE ?
and here & ayir2(0) & " VALUES
between & and values?

Mahares · Feb 2, 2013

@Sinan: Here is the code with all the necessary spaces and syntax:

B4X:

SQL1.ExecNonQuery("DROP TABLE IF EXISTS " & ayir2(0))
SQL1.ExecNonQuery("CREATE TABLE "  & ayir2(0) & " (Nummer TEXT , Name TEXT, Mesaj TEXT , LinkAdres TEXT, Datum TEXT)")
SQL1.ExecNonQuery2("INSERT INTO "  & ayir2(0) & " VALUES(?,?,?,?,?)", Array As Object(ayir2(0), ayir2(1), ayir2(2), ayir2(3),ayir2(4)))

madSac · Feb 2, 2013

you can use

B4X:

if(table_name.compareto(SQ.ExecQuerySingleResult("Select name From sqlite_master WHERE Type='table' AND name='"& table_name & "'"))==0)then
log("Table exist")
else
log("Table don't exist")
end if

Sinan Tuzcu · Feb 4, 2013

madSac said:

you can use

B4X:

if(table_name.compareto(SQ.ExecQuerySingleResult("Select name From sqlite_master WHERE Type='table' AND name='"& table_name & "'"))==0)then
log("Table exist")
else
log("Table don't exist")
end if

Helo,

this code doesn't working

madSac · Feb 4, 2013

table_name is name of table .....

B4X:

SQ.ExecQuerySingleResult("Select name From sqlite_master WHERE Type='table' AND name='"& table_name & "'")

this code returns table name if table exist otherwise it returns null.

check the Table if exist

Sinan Tuzcu

Well-Known Member

Mahares

Expert

Sinan Tuzcu

Well-Known Member

Mahares

Expert

mc73

Well-Known Member

Sinan Tuzcu

Well-Known Member

Mahares

Expert

madSac

Active Member

Sinan Tuzcu

Well-Known Member

madSac

Active Member

Similar Threads

check the Table if exist

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Expert

Active Member

Well-Known Member

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency