How do I get Column Name from SQLite3 database
Posted: Wed Mar 08, 2023 12:04 pm
Hello all,
I am trying to update some code because it fails on some databases, I have narrowed it down to extra spaces in the column name, here is how I was doing it.
It works but it is not very reliable.
I have found a way to get the Table name but not the column names.
The code for column names should be something like this but that does not work.
Can anyone show me the correct code to get the column names.
Many Thanks
Leo
I am trying to update some code because it fails on some databases, I have narrowed it down to extra spaces in the column name, here is how I was doing it.
Code: Select all
OpenFile(1, DB$)
info$ = ReadString(1, 4000, #ENCODING_ISO8859_1)
CloseFile(1)
info$ = ReplaceStr(info$, ", ", ",", True, 1, #ENCODING_ISO8859_1)
Var1 = FindStr(info$, "CREATE TABLE", True, 1, #ENCODING_ISO8859_1)
Var2 = FindStr(info$, " (", True, Var1+13, #ENCODING_ISO8859_1)
Var3 = FindStr(info$, ")", True, Var2, #ENCODING_ISO8859_1)
tablename$ = MidStr(info$, Var1 + 13, var2 - (Var1 + 13), #ENCODING_ISO8859_1)
tablename$ = TrimStr(tablename$, " ", True, #ENCODING_ISO8859_1)
tablename$ = TrimStr(tablename$, " ", False, #ENCODING_ISO8859_1)
Fieldnames$ = TrimStr(MidStr(info$, Var2 + 2, (Var3 - (Var2 + 2)), #ENCODING_ISO8859_1), " ", False)
Fieldnames$ = ReplaceStr(Fieldnames$, ", ", " ,", True, 1)
I have found a way to get the Table name but not the column names.
Code: Select all
db = sqlite3.open(DB$)
For row In db:rows("SELECT name FROM sqlite_master WHERE type='table';")
tablename$ = row[0]
Next
db:close()
Code: Select all
RD = {}
db = sqlite3.open(DB$)
For row In db:nrows("SELECT * FROM pragma_table_info('" .. tablename$ .. ");")
For Info = 1 To count
RD[Info] = row[""..field[Info - 1 ].. ""]
Next
Next
db:close()
Many Thanks
Leo