Populating Datagrid From SQL Cursor

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller

Post Reply
gagsoft
Posts: 161
Joined: Sat Jun 29, 2013 7:56 pm

Populating Datagrid From SQL Cursor

Post by gagsoft » Sun Sep 08, 2019 10:02 am

I Want to add column headers dynamically into the datagrid and then populate it with an SQL Cursor.
The code connects to the database and creates the headers but does not populate the grid or generate any errors.
I must be missing something here.
Here is my code
global conID -- connection ID
global realPath -- where executable was started
global supress
constant sq="'"
on mouseUp
-- use a global variable to hold the connection ID so other scripts can use it
global ConID
-- set up the connection parameters - edit these to suit your database
put "sql9.jnb2.host-h.net" into tDatabaseAddress
put "gigscwsneg_db1" into tDatabaseName
put "gigscwsneg_1" into tDatabaseUser
put "Password" into tDatabasePassword
-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
-- check if it worked and display an error message if it didn't
-- & set the connection ID global
if tResult is a number then
put tResult into ConID
answer info "Connected to the database." & cr & "Connection ID = " & ConID
else
put empty into ConID
answer error "Unable to connect to the database:" & cr & tResult
end if
end mouseUp
populating the grid.
global conID -- connection ID
on mouseUp
local theCursor, temp, theFields, tSQL, pText, dList, ii
if not Connected() then exit to top
put revQueryDatabase( conID, "SELECT * FROM testtable") into theCursor
put the result into temp --for debugging
if "ERROR" is in temp then
answer "ERROR=" & the result
exit to top
end if
put theCursor into fld "rCounter" -- displays the number of lines
put revDatabaseColumnNames(theCursor) into theFields -- get field names from table
if the " revdberr" is in the result then
return "ERROR=" & the result
end if
replace "," with CR in theFields
set the dgProp["columns"] of group "myGrid" to theFields
put "SELECT * FROM testtable" into tSQL
put revdb_querylist(,,conID,tSQL) into ptext
put the result into dlist
if "revdberr" is in the result then
return "ERROR=" & the result
end if
put the number of lines in pText into fld "rCounter" -- displays the # of lines
set the dgColumnWidth["Handle"] of group "myGrid" to 100
set the dgColumnWidth["Entry"] of group "myGrid" to 160
repeat with ii = 1 to fld "rCounter"
put line ii of dlist into temp
dispatch "AddLine" to grp "myGrid" with temp
end repeat
end mouseUp
Any help / pointers would be appreciated.
Thanks
Peter G

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

Re: Populating Datagrid From SQL Cursor

Post by bangkok » Sun Sep 08, 2019 11:10 am

Hi,
2 mistakes :

- put the result into dlist (actually the data from the query are stored into ptext !)

- wrong method to populate the DG (addline. Use instead dgText)

So here is something that should work, more simple :

Code: Select all

     put "SELECT * FROM testtable" into tSQL
     put revDataFromQuery(,,conID,tSQL) into ptext

     put the result into tResult
     if tResult contains "revdberr"  then
          answer "ERROR=" & tResult 
     end if

     put the number of lines in pText into fld "rCounter" -- displays the # of lines

     set the dgText of group "myGrid" to pText --- that's all you need.

You could also use dgData to populate the datagrid, with an array that you would build (with a loop) from the raw data returned by the SQL query.

It could be usefull for instance to change the order of columns displayed versus the columns order in your SQL table.

If your DG has 3 columns : column3, column2 ,column1
And your table : column1, column2, column3

Code: Select all

put "SELECT * FROM testtable" into tSQL
put revDataFromQuery(,,conID,tSQL) into ptext

put empty into pArray
set itemdelimiter to tab

repeat with i = 1 to the number of lines of pText

put  item 3 of line i of pText into pArray[i]["column3"]
put  item 2 of line i of pText into pArray[i]["column2"]
put  item 1 of line i of pText into pArray[i]["column1"]

end repeat

set the dgData of group "myGrid" to pArray
This is of course just an example. It would be much easier to do directly :

Code: Select all

put "SELECT column3,column2,column1 FROM testtable" into tSQL
But it's just to show you the process with an array and dgData.

gagsoft
Posts: 161
Joined: Sat Jun 29, 2013 7:56 pm

Re: Populating Datagrid From SQL Cursor

Post by gagsoft » Sun Sep 08, 2019 1:35 pm

Thanks you Bankok
Got it to populate the datagrid now.
But it does only the first 7 Columns Although it managed to create all the column headers from the selected table.
This this seems to be a bit weird because both are based on the same query result. :)

gagsoft
Posts: 161
Joined: Sat Jun 29, 2013 7:56 pm

Re: Populating Datagrid From SQL Cursor

Post by gagsoft » Sun Sep 08, 2019 1:42 pm

Hi Bankok
My Apologies.....Had a typo and fixed it.
Works perfectly.

MAny Thanks

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”