SQLite – Column names and number of columns

[csd214]

I want to create a function to find the column names in a table. That isn’t difficult if there are at least one row in the table. But what to do when the table is empty? I could retrieve the sql query that created the table (from sqlite_master), but it seems to be a hard job to analyse a string like this:

Create table Custommers(Id integer primary key, Account integer, Company text, Region text, LastPurchase text, Amount numeric(10,2))

The number of columns can be found using the code below, but to find the names (and preferably the type) is a more complex task.

Is there an easier method to determine the table structure?

function SQLdb.StructFieldCount(sDB, sTable)
--[[
Function:	SQLdb.StructFieldCount
Purpose:	Determine number of columns in the table by reading sqlite_master
		(can be used even when there are no records in the table)
Arguments:	(database) The handle to the database that you want to operate on.
		(string) Name of table
Returns:	(number) or nil (if unsuccessful)
]]

nFieldCounter = nil;
SQLite.Compile(sDB, "SELECT sql FROM sqlite_master WHERE type='table'");
repeat
    SQLite.Step(sDB);
    err = Application.GetLastError();
    if (err == SQLite.ROW) then
		tbRow = SQLite.GetRowDataAssoc(db);
		for strColumn, strData in tbRow do
			if String.Find(strData, sTable, 1, false) > -1 then
				lenData = String.Length(strData);
				nPos = 1; nFieldCounter = 0;
				while nPos < lenData do
					nPosDel = String.Find(strData, ", ", nPos, false);
					if nPosDel > -1 then
						nFieldCounter = nFieldCounter + 1;
						nPos = nPosDel + 2;
					else
						break;
					end
				end
				nFieldCounter = nFieldCounter + 1;
			end
		end
    end
until (err ~= SQLite.ROW)
SQLite.Finalize(db)
return nFieldCounter;
end

[csd214]

..my thought was: “Why do it difficult if it can be done easy?”

i) If the table is empty, add a dummy row
ii) Use actions GetRowColumnNamesArray() and GetRowColumnTypesArray()
iii) Delete the dummy row

BUT:
To add the dummy row I have to know at least ONE column name to perform a query like
“insert into Customers (ID) values (NULL)"
(SQLite is not case sensitive; (id) works fine (if ‘id’ exists!)

Another solution is to insert the proper number of NULL values. If the number of columns is 4, the SQL command has to be:
"insert into Customers values (NULL, NULL, NULL, NULL)"

I already have the function to find the number of columns, and it should not be too difficult to find one single column name (the first?) by reading sqlite_master.

Thanks to this “Self Help Forum”; it’s a good idea to think loudly... But if anybody has a better solution, pleas give me a hint.

[Worm]

Maybe this will help you out.

tblTables = SQLite.QueryToTable(db, "Select tbl_name from sqlite_master")
nRows = tblTables.Rows
for n=1, nRows do
	tblColumns=SQLite.QueryToTable(db, "Select * from ".. tblTables.Data[n]["tbl_name"])
	sMsg = "Table:  " .. tblTables.Data[n]["tbl_name"] .. "\r\nFields:  "
	for m=1, tblColumns.Columns do
		sMsg = sMsg .. tblColumns.ColumnNames[m] .. "\t"
	end
	Dialog.Message("SQLite Info", sMsg)	
end

[Worm]

This should return the SQL statement that created the table too.

tblTables = SQLite.QueryToTable(db, "Select tbl_name from sqlite_master")
tblTest = SQLite.QueryToTable(db, "SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE tbl_name LIKE '" .. tblTables.Data[1]["tbl_name"] .. "' AND type!='meta' ORDER BY type DESC, name")
Dialog.Message("", tblTest.Data[1]["sql"])

[csd214]

Worm, I had just finished my module – working! – when I read your first post. Really interesting to see a quite different – but working – approach. (Nested for loops need some brain work.) Common to both solutions is that the table has to have at least one record (the dummy solution is needed).

Your second post (with return of the SQL Create) is brilliant, but I have to study this code in deep (the UNION command is something I want to fully understand).

In my solution I gather the information in a resulting associative table to be able to create a report like this:

Structure of table Customers
====================
SQL = Create table Customers(Id INTEGER_PRIMARY_KEY, Account INTEGER, Company TEXT, Region TEXT__with_leading_zeroes, LastPurchase DATE__yyyymmdd)

Col Name - Type
-------------------
Col 1: Id - INTEGER_PRIMARY_KEY
Col 2: Account - INTEGER
Col 3: Company - TEXT
Col 4: Region - TEXT__with_leading_zeroes
Col 5: LastPurchase - DATE__yyyymmdd

The main point is to catch up with the desired information. Thank you for your time, Worm. I’m going to play with your suggestions.

[Intrigued]

This should return the SQL statement that created the table too.

tblTables = SQLite.QueryToTable(db, "Select tbl_name from sqlite_master")
tblTest = SQLite.QueryToTable(db, "SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE tbl_name LIKE '" .. tblTables.Data[1]["tbl_name"] .. "' AND type!='meta' ORDER BY type DESC, name")
Dialog.Message("", tblTest.Data[1]["sql"])

Worm, when dealing with SQLite queries, etc. etc.... what documentation do you use, if I may ask? Or, do you just code such off the top of your head?

I really need some guidance with concerns to SQLite and how to reap the full benefits of using such.

Thank you in advance.

Sincerely,

[Worm]

I've been dealing with MS SQL Server for around 4 years now, so SQL is something I'm fairly familiar with. There are slight differences, but SQLite is quite similar. Other than that, I'm just like everyone else and hit Google

Worm, when dealing with SQLite queries, etc. etc.... what documentation do you use, if I may ask? Or, do you just code such off the top of your head?

I really need some guidance with concerns to SQLite and how to reap the full benefits of using such.

Thank you in advance.

Sincerely,

[Intrigued]

I've been dealing with MS SQL Server for around 4 years now, so SQL is something I'm fairly familiar with. There are slight differences, but SQLite is quite similar. Other than that, I'm just like everyone else and hit Google

Well, we use MySQL in the 'shop' here. I hope that is closely related to SQLite as well, as far as the language syntax?

Thanks for your feedback Worm!

Sincerely,