Naive question about TABLE(s)

Databases
1

I was fooling around this night and fall into a naive question concerning TABLEs…

Beside the use of TableColumns in a complex code/UI, is there’s a solution to ask SQLite about the available TABLEs ?

I have somewhere a project who allows me to watch what TABLEs are defined in a SQLite file.
But where, that was the problem and I do not wanted to waste time to search it.

I added a break just after db.Connect() and checked in the debugger (no, there is nothing to read concerning the TABLEs definition there).

So, I changed the file extension to .txt, and read the TABLEs…

Then, I asked myself the question:

Can I poll SQLite about the availability of a TABLE… something like:
If db.Table("Characters") Then

2

@Emile_Schwarz, from memory it’s

SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name;

Kind regards, Andrew

3

Far easier than TableColumn (and UI), but needs to made a search in the RowSet.

Other advice ?

4 
myRecordSet = db_SQLite.TableSchema
5

Will give a list of column names in a table.

PRAGMA table_info(table_name);

So first answer will give a list of tables to compare to the previous list. Then if you wish to drill down to the columns names of the newly added table use the answer above.

Kind regards, Andrew

6

Thank you guys…

7

I have a method called tableExists:

method arguments:

dbh As SQLiteDatabase, tablename As String

Var  sql As String
sql = "select 1 from " + tablename
Try
  Call dbh.SelectSQL (sql)
  Return True
Catch e as DatabaseException
  Return False
end try
8

https://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-tables

someSQLiteDatabase.Tables As RowSet

Returns a RowSet with a list of all tables in the database.

A DatabaseException will be raised if an error occurs.

Tables returns a RowSet with one field: TableName As String.

The following code gets and displays the table names for the connected database:

// App.db is a connected database 
Var tables As RowSet 
tables = App.db.Tables 
Try 
  For Each row As DatabaseRow In tables 
    MessageBox(row.ColumnAt(0).StringValue) 
  Next tables.Close 
Catch error As NilObjectException 
    MessageBox("This database has no tables.") 
End Try
9

Thank you.

I was awaiting an answer à la Exist (think FolderItem isReadable, isWritable, Exist, etc. properties).

Life can be simple !

10

:point_up_2:t2:

11

“my” solution:


Public Function Check_Table(db As SQLiteDatabase, Search_Table As String) As Boolean
  // 
  // Function:  Check_Table()
  // Inputs:    db As SQLiteDataBase, Search_Table As String
  // Outputs:   Boolean (True: this TABLE exists)
  // Syntax:    Found = Check_Table(db,Search_Table)
  // 
    
  Var tables  As RowSet
  Var isFound As Boolean
  
  tables = db.Tables
  
  Try
    For Each row As DatabaseRow In tables
      If Search_Table = row.ColumnAt(0).StringValue Then
        // Found
        isFound = True
      End If
    Next
    tables.Close
    
    Return isFound
    
  Catch error As NilObjectException
    MessageBox("This database has no tables.")
  End Try
End Function

Called with an If … Then … Else … End If block.

The other solution displays a window with a graphic solution to choose a TABLE to display from a list of the existing TABLES (with the number of Records). But I have to locate it…

12

In a function designed to return true/false this should not be here

13

Thanks…

I asked Bard and here’s his answer:

If TableExists("MyFile.xojo", "MyTable") Then
  'Table exists
Else
  'Table does not exist
End If

a nice looking advice (still using bard):

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

Not tested, of course…

14

Bard / ChatGPT are still kind of dumb. Don’t ask them, test your things. :smiley:

I guess this one using your preferred way, is a bit more effective, faster and shorter:

Public Function TableExists(db As SQLiteDatabase, Search_Table As String) As Boolean
  
  // Pass a sqlite db and a table name, returns true if such table exists
  
  Var tables  As RowSet = db.Tables
  
  tables = db.Tables
  
  If tables = Nil Then Return False  // No tables, 
  
  For Each row As DatabaseRow In tables
    If Search_Table = row.ColumnAt(0).StringValue Then Return True  // Found
  Next
  
  Return False  // Not Found

End Function

I guess this is broken for SQLite, because name should be tbl_name for SQLite schemes.

15

Nice.

I set a variable (isFound) to use only one Return and attribute the variable a value only once (if True, else let it stays as False, the default value).

In Bard (SELECT COUNT(*)…) what if a Column with the TABLE passed name exists in sqlite_master ? Will I get a false positive ?

16

I don’t understand what you are trying to say, but…

I’m checking a SQLite DB here now, and SQLIte seems having a redundant info, maybe targeting compatibility. There’s also a name column with the same value of tbl_name.

This code:

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

Will return the number of ‘MyTable’ tables, which should return 0 (false) or n>0 (true)

17

After following this thread I do not know what is the problem that @Emile_Schwarz is trying to solve or what is wrong with my solution. :man_shrugging:

1 Like
18

@Rick_Araujo
That is what I suspected.

@Tim:
Question answered.

Thank you all.