How do I get Column Name from SQLite3 database

Discuss about plugins that don't have a dedicated forum
Post Reply
User avatar
Redlion
Posts: 93
Joined: Sun Jul 10, 2011 5:05 am
Location: Perth, Western Australia

How do I get Column Name from SQLite3 database

Post by 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
----------------------------------------------------------------------------------------
Redlion
Sam460 Lite
A4000 A3000 A2000 A1200 A1000 A600 A500 CD32
plouf
Posts: 462
Joined: Sun Feb 04, 2018 11:51 pm
Location: Athens,Greece

Re: How do I get Column Name from SQLite3 database

Post by 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()
Christos
User avatar
Redlion
Posts: 93
Joined: Sun Jul 10, 2011 5:05 am
Location: Perth, Western Australia

Re: How do I get Column Name from SQLite3 database

Post by Redlion »

@plouf

Thanks, that works great.

Cheers
Leo
----------------------------------------------------------------------------------------
Redlion
Sam460 Lite
A4000 A3000 A2000 A1200 A1000 A600 A500 CD32
Post Reply