Datagrids, Tables, Columns, and "How do I do that?"

[Paul.RaulersonBUSIvAg]

Pretty basic question I am afraid, so apologies in advance if this is something simple and I just cannot find it in the documentation. Also, I'm a dB2 kind of guy, and I may be making assumptions that don't apply about SQLite and LiveCode.

I can create and manage a SQLite database now with no problems, queries, updates, deletes, drops, creates, etc. All works pretty smooth as long as you don't try to do anything at all fancy. Color me impressed.

But how does one do the simple things like - dynamically setup the columns in a datagrid to match the current query, including the names and number of columns?
In dB2, you can select the columnnames from a system table using a where clause to identify the table you want. The closest I found in SQLite is a "PRAGMA" command, pragma table_info(<tablename>), but I can not seem to find a way to limit the returned fields to just the name field.

Also, I found where you can use a tab delimited first row of data to setup the column names in a data grid, but I can't get that exact format back from SQLite, at least so far as I know.

Mostly this is for a screen that edits a bunch of domain tables, but also needed to edit a few tables that don't need a screen or card of their own, just the capability to easily edit them, with control over what columns the user sees.

Thanks
-Paul

Non working example code below:

Code: Select all

on mouseUp
   local iCount
   put 1 into iCount
   
   set the dgText of group DBTableList to empty
   wait 1 tick with messages
   -- get the column names
   put "PRAGMA table_info(DT00)" into tSQL
   put revDataFromQuery(tab,return,gConnectionID,tSQL) into tFields
   repeat for each line tline in tFields 
      set the dgColumnName[iCount] of group DBTableList to the second word of tline  
      set the dgColumnLabel[iCount] of group DBTableList to the second word of tline
      -- DEBUG CODE
      answer iCount && the dgColumnName[iCount] of group DBTableList && "Name=" & the second word of tline with "Ok"
      --- END DEBUG
      add 1 to iCount
    end repeat
   put "Select * from DT00 order by TYP ASC" into tSQL
   put revDataFromQuery(tab,return,gConnectionID,tSQL) into tRecords
   set the dgText[false] of group DBTableList to tRecords
end mouseUp

[Paul.RaulersonBUSIvAg]

Well, finally figured out how to set the column headings. Whew!

But it still seems like there is a bit of a dearth of meta information about the tables in SQLite. How are you guys handling that?
Keeping a master table of tables with column label names, maxsizes, etc? Or am I just over thinking this and missing the really simple solutions everyone else sees?

Yours,
-Paul

Code: Select all

on mouseUp
   local iCount
   put 1 into iCount
   
   set outerGlow[blendmode] of field DTList to normal
   set the dgText of group DBTableList to empty
   wait 1 tick with messages
   -- get the column names
   put "PRAGMA table_info(DT00)" into tSQL
   put revDataFromQuery(tab,return,gConnectionID,tSQL) into tFields
   put "" into tHeaders
   repeat for each line tline in tFields 
      put tHeaders & the second word of tline  & return into tHeaders
      add 1 to iCount
   end repeat

   put "Select * from DT00 " into tSQL
   put revDataFromQuery(tab,return,gConnectionID,tSQL) into tRecords
   set the dgText[false] of group DBTableList to tRecords
   set the outerGlow of field DTList to empty
end mouseUp

[Klaus]

Hi Paul,

can't you just use "revDatabaseColumnNames()"?
That will return a COMMA delimited list of all the columnnames for a give database table.

Check the dictionary for more info.


Best

Klaus

[Paul.RaulersonBUSIvAg]

Hi Paul,

can't you just use "revDatabaseColumnNames()"?
That will return a COMMA delimited list of all the columnnames for a give database table.

Check the dictionary for more info.


Best

Klaus

Told you I was a dB2 guy. Automatically sought a SQL based solution.

I missed that the ColumnNames call would work with the ConnectionID, and I wasn't using Recordsets for simplicity on these. That will work just nicely, and have the side benefit of not being specific to any particular database. I suppose it is not much trouble to move that code to using cursors instead.

Yours,
-Paul