How do I read table field names?

[jpottsx1]

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.

[dglass]

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

[jpottsx1]

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?

[dglass]

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]

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

[dglass]

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]

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.

[dglass]

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]

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.