How do I read table field names?

LiveCode is the premier environment for creating multi-platform solutions for all major operating systems - Windows, Mac OS X, Linux, the Web, Server environments and Mobile platforms. Brand new to LiveCode? Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
jpottsx1
Posts: 46
Joined: Thu Jun 04, 2009 12:46 am
Contact:

How do I read table field names?

Post by jpottsx1 » Mon Oct 03, 2011 12:41 am

Hi, I'm fooling with SQLite and want to generate an INSERT statement by reading in the field names from an existing table.

I want to read in the total number of fields ie:NumbFields and the field name itself ie:FieldName

Code: Select all

put "INSERT INTO Mood ( " into sqlQuery
repeat with i = 1 to NumbFields
put "FieldName,&" after sqlQuery
end repeat
put "INSERT INTO Mood ) " after sqlQuery 
How is the best way to get this data into the script? Should I do it with SQL only using some sort of a select statement to return the field names or should I execute a SELECT * FROM query and then parse the data from the cursor? How would I extract the field names? Is there a DESCRIBE TABLE command? I can't find anything like it in SQLite.
I'm not able to get any further than this as I'm not a very experienced programmer.

Your help is appreciated.
Jeff G potts

dglass
Posts: 519
Joined: Thu Sep 24, 2009 9:10 pm
Location: California
Contact:

Re: How do I read table field names?

Post by dglass » Mon Oct 03, 2011 1:24 am

revDatabaseColumnNames will give you the names of the columns in a recordset.

jpottsx1
Posts: 46
Joined: Thu Jun 04, 2009 12:46 am
Contact:

Re: How do I read table field names?

Post by jpottsx1 » Mon Oct 03, 2011 2:22 am

I've been able to return the column names using

Code: Select all

put revDatabaseColumnNames(conID, "control") into pptext
   answer "ColumnNames"& pptext
What direction do I take to read through the field names in "pptext" and read the field names and count into my loop?

I assume that using revDatabaseColumnCount will provide me with record number, and then how do I cycle through the field names?
Jeff G potts

dglass
Posts: 519
Joined: Thu Sep 24, 2009 9:10 pm
Location: California
Contact:

Re: How do I read table field names?

Post by dglass » Mon Oct 03, 2011 3:25 am

Off the top of my head....

Code: Select all

put "INSERT INTO Mood (" into sqlQuery
put empty into tFieldNames
put revDatabaseColumnNames(conID, "control") into pptext

repeat for each line linColumn in pptext
put tFieldNames & linColumn & "," into tFieldNames
end repeat

repeat while the last character of tFieldNames is ","
delete last character of tFieldNames
end repeat

put sqlQuery & tFieldNames & ") VALUES (" into sqlQuery

jpottsx1
Posts: 46
Joined: Thu Jun 04, 2009 12:46 am
Contact:

Re: How do I read table field names?

Post by jpottsx1 » Thu Oct 06, 2011 1:11 am

I am getting close to having a correctly word INSERT INTO sql query but I cannot seem to get the values portion of the statement within quotation marks. The code I have puts quotation marks around the entire contents of the VALUES portion of the statement and not around the individual value terms.

Code: Select all

put "INSERT INTO Mood (" into sqlQuery
put empty into tFieldNames
put revDatabaseColumnNames(conID, "control") into pptext

repeat for each line linColumn in pptext
   put tFieldNames & linColumn & "," into tFieldNames
   put "'"& tFieldNames &"'" & "," into tFieldValues
end repeat

repeat while the last character of tFieldNames is ","
   delete last character of tFieldNames
   delete last character of tFieldValues
end repeat

put sqlQuery & tFieldNames & ") VALUES ("into sqlQuery
put  tFieldValues & ")" after sqlQuery
answer sqlQuery
The output currently is "INSERT INTO Mood (Handle,Entry,Field01,Field02) VALUES ('Handle,Entry,Field01,Field02,')" but it needs to be "INSERT INTO Mood (Handle,Entry,Field01,Field02) VALUES ('Handle','Entry','Field01','Field02')"

Your help with this is greatly appreciated
Jeff G potts

dglass
Posts: 519
Joined: Thu Sep 24, 2009 9:10 pm
Location: California
Contact:

Re: How do I read table field names?

Post by dglass » Thu Oct 06, 2011 1:58 am

Code: Select all

put "INSERT INTO Mood (" into sqlQuery
put empty into tFieldNames
--add this bit
put empty into tFieldValues
put revDatabaseColumnNames(conID, "control") into pptext

repeat for each line linColumn in pptext
   put tFieldNames & linColumn & "," into tFieldNames
   --put "'"& tFieldNames &"'" & "," into tFieldValues
  put tFieldValues & "'" & linColumn & "'," into tFieldValues
end repeat

repeat while the last character of tFieldNames is ","
   delete last character of tFieldNames
end repeat
--do these separately just in case
repeat while the last character of tFieldValues is ","
   delete last character of tFieldValues
end repeat

put sqlQuery & tFieldNames & ") VALUES (" into sqlQuery
put  tFieldValues & ")" after sqlQuery
answer sqlQuery

jpottsx1
Posts: 46
Joined: Thu Jun 04, 2009 12:46 am
Contact:

Re: How do I read table field names?

Post by jpottsx1 » Fri Oct 07, 2011 1:23 am

Thanks for the clarification on the FieldValues portion of the code.

However I'm still not able to get the VALUES properly quoted. THe code puts quotation marks around the combined VALUES list instead of around each value as required for the SQL to interpret the VALUES properly.

I'm unsure how to 'chunk' the linColumn values into individual field values, each one surrounded by quotations marks.

Your help is always appreciated.
Jeff G potts

dglass
Posts: 519
Joined: Thu Sep 24, 2009 9:10 pm
Location: California
Contact:

Re: How do I read table field names?

Post by dglass » Fri Oct 07, 2011 1:38 am

My mistake. revDatabaseColumnNames returns a comma-delimited string not a return-delimited one so put the following after your revDatabaseColumnNames call...

Code: Select all

replace comma with return in pptext
That will make it work with the repeat loop you've already constructed.

jpottsx1
Posts: 46
Joined: Thu Jun 04, 2009 12:46 am
Contact:

Re: How do I read table field names?

Post by jpottsx1 » Fri Oct 07, 2011 1:47 am

THat worked great! I was just trying the use of splitting the pptext using "split linColumn by comma and return" but I had it too far down in the code.
Jeff G potts

Post Reply

Return to “Getting Started with LiveCode - Experienced Developers”