How do I get Column Name from SQLite3 database

[Redlion]

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.

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)
	

It works but it is not very reliable.

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()

The code for column names should be something like this but that does not work.

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()

Can anyone show me the correct code to get the column names.

Many Thanks
Leo

[plouf]

i will do it that way

Code: Select all

	db = sqlite3.open(DB$)

	For row In db:nrows("PRAGMA table_info(TABLE_NAME_HERE);")
	 	DebugPrint("Column position = "..row.cid.." - Column name ="..row.name)
	Next
db:close()

[Redlion]

@plouf

Thanks, that works great.

Cheers
Leo