### Get Database & Table info

Various subs to obtain SQLite Database and Table information.
'Get database information from a SQLite database
'Example Output:
'Type = table
'name = CONTACTHISTORY
'tbl_name = CONTACTHISTORY
'rootpage = 3
'sql = CREATE TABLE [CONTACTHISTORY] ([ID] INTEGER PRIMARY KEY, [Name] INTEGER, [ContactDate] TEXT , [ContactNote] TEXT)
'etc.
`Sub SQLiteInfo(mSQL As SQL) As String
Dim result As String
Dim cursor As ResultSet
Dim cmd As String = "SELECT * FROM sqlite_master ORDER BY name;"
Dim sb As StringBuilder
sb.Initialize
Try
cursor = mSQL.ExecQuery (cmd)
Do While cursor.NextRow
For i = 0 To cursor.ColumnCount - 1
sb.Append(cursor.GetColumnName(i) & " = " & cursor.GetString2(i) & CRLF)
Next
sb.Append(CRLF)
Loop
Catch
Log("ERROR: " & LastException.Message)
End Try
result = sb.ToString
Return result
End Sub`
'Get the list of tables for a database
'Example Output:
'CONTACTHISTORY
'CONTACTS
`Sub SQLiteTableList(mSQL As SQL) As List
Dim Result As List
Result.Initialize
Dim cursor As ResultSet
Dim cmd As String = "SELECT name FROM sqlite_master WHERE TYPE=""table"" ORDER BY name;"
Try
cursor = mSQL.ExecQuery (cmd)
Do While cursor.NextRow
Result.Add(cursor.GetString("name"))
Loop
Catch
Log("ERROR: " & LastException.Message)
End Try
Return Result
End Sub`
'Checks if a table exists
`Sub SQLiteIsTable(mSQL As SQL, mTableName As String) As Boolean
Dim Result As Boolean = False
Dim TableMatch As String
Dim cmd As String = "SELECT name FROM sqlite_master WHERE type=""table"" AND tbl_name=?;"
Try
TableMatch = mSQL.ExecQuerySingleResult2(cmd, Array(mTableName))
Result = TableMatch.EqualsIgnoreCase(mTableName)
Catch
Log("ERROR: " & LastException.Message)
End Try
Return Result
End Sub`
'Get Tableinformation
'Example Output:
'ID, INTEGER
'Name, INTEGER
'ContactDate, TEXT
'ContactNote, TEXT
`Sub SQLiteTableInfo(mSQL As SQL, mTableName As String) As String
Dim Result As String
Dim sb As StringBuilder
Dim cursor As ResultSet
Dim cmd As String = "PRAGMA table_info(" & mTableName & ");"
Try
sb.Initialize
sb.Append("Table: " & mTableName & CRLF)
cursor = mSQL.ExecQuery(cmd)
Do While cursor.NextRow
sb.Append(cursor.GetString2(1) & ", " & cursor.GetString2(2) & CRLF)
Loop
Catch
Log("ERROR: " & LastException.Message)
End Try
Result = sb.ToString
Return Result
End Sub`
'Get Tablefields as list
`Sub SQLiteTableFields(mSQL As SQL, mTableName As String, WithType As Boolean) As List
Dim Result As List
Result.Initialize
Dim cursor As ResultSet
Dim cmd As String = "PRAGMA table_info(" & mTableName & ");"
Try
cursor = mSQL.ExecQuery(cmd)
Do While cursor.NextRow
If WithType Then
Result.add(cursor.GetString2(1) & ", " & cursor.GetString2(2))
Else
Result.add(cursor.GetString2(1))
End If
Loop
Catch
Log("ERROR: " & LastException.Message)
End Try
Return Result
End Sub`
'Check if a field exists
'SQLiteIsField(SQLObj, "CONTACTS", "Name")
`Sub SQLiteIsField(mSQL As SQL, mTableName As String, mTableField As String) As Boolean
Dim Result As Boolean = False
Dim l As List = SQLiteTableFields(mSQL, mTableName, False)
For i = 0 To l.Size - 1
Dim fld As String = l.Get(i)
If fld.EqualsIgnoreCase(mTableField) = True Then Result = True
Next
Return Result
End Sub`

Changed: , Category: SQLite, File: T6C254M1823.htm, ID: 1823