SQLlite to DataGrid

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: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
lohill
Posts: 770
Joined: Tue Dec 08, 2009 6:37 pm

SQLlite to DataGrid

Post by lohill » Fri Apr 04, 2014 12:30 am

I haver a SQLLite database table with 9 columns. Every row is populated but not every field in the row contains data. To add to the confusion, some of the fields have multiple lines. I would like to get the entire table into a datagrid. A snipit of some of the test code is below:

Code: Select all

   put "SELECT sKey,id,ord,radiscript,conditions,steps,labels,refs,notes FROM steps" into tSQL
   put ExecuteSQL(tSQL) into tResult
   put the number of lines of tResult into tLines
   repeat with i=1 to tLines
      put line i of tResult into tLine
      put PrepForSteps(tLine) into tArray
      dispatch "AddData" to group "DataGrid Steps" with tArray
   end repeat
The not yet complete function PrepForSteps looks like this

Code: Select all

function PrepForSteps tLine
   set the itemdelimiter to tab
   put the number of items of tLine into tItems
   answer tItems & return & tLine
   
   return tArray
end PrepForSteps
My intention was to put the items of the line into the array but it turns our that the lines differ in the number items so I am not sure how to handle it. Any suggestion for how to make this work or any alternate methods that are better would be appreciated.

Thanks,
Larry

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: SQLlite to DataGrid

Post by bangkok » Fri Apr 04, 2014 7:25 am

Why do you want to use "AddData" to populate your datagrid with the result of your SQLIte query ?

Unless you want to apply some "post SQL query treatement" to the data, it's pointless.

You have 2 ways to populate a datagrid. Using dgtext (easiest) or dgdata (with array).

Code: Select all

put "SELECT sKey,id,ord,radiscript,conditions,steps,labels,refs,notes FROM steps" into tSQL
 put ExecuteSQL(tSQL) into tResult
set the DgText of group "DataGrid Steps" to tResult
For DgData you have to be sure that your array keys have the same name that the columns names in your datagrid.

Last but not least : what happens if your SQL columns contains "lines" (CR or LF character) or tabulation ? It will break the display of the data in the datagrid (because datagrids use tab as column delimiter and CR as row delimiter).

To manage this situation, just use a turn around with delimiters :

For instance :

Code: Select all

put "SELECT sKey,id,ord,radiscript,conditions,steps,labels,refs,notes FROM steps" into tSQL
put revDataFromQuery("$","|",tID,tSQL) into tResult
replace "|" with CR in tResult
replace "$" with tab in tResult
set the DgText of group "DataGrid Steps" to tResult

Post Reply