Android Question Count of tables in SQL database.

Inuma · Nov 9, 2018

I have a sql database with an unknown count of tables. How to find the number of tables in the database?
For example I try:

B4X:

dim count_tables as Cursor
count_tables= Main.sql1.ExecQuery("SELECT COUNT() FROM sqlite_master WHERE TYPE='table'")
    count_tables.Position=0
    Msgbox("Tables",count_tables.GetInt(0))

but it returns error "column not exists"

Erel · Nov 9, 2018

Correct code:

B4X:

Dim count_tables As ResultSet = sql.ExecQuery("SELECT COUNT() FROM sqlite_master WHERE TYPE='table'")
count_tables.NextRow
Log("Number of tables: " & count_tables.GetInt2(0))
count_tables.Close

RB Smissaert · Nov 10, 2018

Erel said:

Correct code:

B4X:

Dim count_tables As ResultSet = sql.ExecQuery("SELECT COUNT() FROM sqlite_master WHERE TYPE='table'")
count_tables.NextRow
Log("Number of tables: " & count_tables.GetInt2(0))
count_tables.Close

This is a bit simpler:

B4X:

Sub CountTables
 Dim lCountTables As Long = SQL.ExecQuerySingleResult("select count(*) from sqlite_master where type = 'table'")
 Log("CountTables, lCountTables: " & lCountTables)
End Sub

RBS

Inuma · Nov 10, 2018

Thanks. It's working

eps · Nov 10, 2018

RB Smissaert said:

This is a bit simpler:

B4X:

Sub CountTables
 Dim lCountTables As Long = SQL.ExecQuerySingleResult("select count(*) from sqlite_master where type = 'table'")
 Log("CountTables, lCountTables: " & lCountTables)
End Sub

RBS

I would put the number in to an Int, but Long would more than suffice. There's no real need to put the asterisk in, I'd prefer a column_name in there, but it could throw an error if it no longer exists. It used to be accepted SQL practice, but then was frowned upon, but might work fine in this scenario but not in a 'normal' counting scenario, e.g. number of records in a specific table.

Android Question Count of tables in SQL database.

Inuma

Member

Erel

B4X founder

RB Smissaert

Well-Known Member

Inuma

Member

eps

Expert

Similar Threads

Android Question Count of tables in SQL database.

Inuma

Member

Erel

B4X founder

RB Smissaert

Well-Known Member

Inuma

Member

eps

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency