Converting a Database Cursor to a Data Grid Array
This lesson will show you how to query a database server and convert the resulting database cursor to an array that can be displayed in a data grid table. You can find the sample stack used for this lesson here: https://tinyurl.com/y9uf827g
Create a Data Grid Table

Create a new stack and drag a data grid from the Tools Palette onto the card.
Add Columns

The database table that we will query has two fields that we are going to display. One is called firstname and the other lastname. We will create two columns with the same name in the data grid.
1) With the data grid selected open the Property Inspector and switch to the Columns pane.
2) Click the + button to add a new column.

Name the column firstname. Click the + button again and name the second column lastname.
Create a Button for Testing

Now drag a button onto the card and name it Display Data using the Properties Palette.
Edit Button Script

Right-click on the button and select Edit Script from the contextual menu.
Connecting to the Database
Now we are ready to connect to and query the LiveCode example database and display some data. To open a connection to a database you call revOpenDatabase. Let's begin with the following script in the button.
on mouseUp
## Connect to the database
put "mysql" into theDBType
put "www.livecode.com" into theDBHost
put "runrev_test" into theDBName
put "runrev_example" into theDBUser
put "example" into theDBPassword
put revOpenDatabase( theDBType, theDBHost, theDBName, theDBUser, theDBPassword ) into theConnectionID
if theConnectionID is an integer then
## Query the database for data
put revQueryDatabase( theConnectionID, "SELECT * FROM Table1") into theCursor
else
answer "Error connecting to the database:" && theConnectionID & "."
end if
end mouseUp
At this point the script has connected to the database and asked for all of the data in table Table1. But how do we display the data the database returned in the data grid? revQueryDatabase returns an integer that represents a database cursor and you can only assign arrays and text to a data grid.
Converting a Database Cursor to an Array
Converting a database cursor to an array is pretty straightforward. Here is a handler you can use for that very purpose.
command ConvertSQLCursorToArray pCursor, @pOutArrayA
local i
local theFields
local theError
## Get the names of all the columns in the database cursor
put revDatabaseColumnNames(pCursor) into theFields
if theFields begins with "revdberr," then
put item 2 to -1 of theFields into theError
end if
if theError is empty then
put 0 into i
## Loop through all rows in cursor
repeat until revQueryIsAtEnd(pCursor)
add 1 to i
## Move all fields in row into next dimension of the array
repeat for each item theField in theFields
put revDatabaseColumnNamed(pCursor, theField) into pOutArrayA[i][ theField ]
end repeat
revMoveToNextRecord pCursor
end repeat
end if
return theError
end ConvertSQLCursorToArray
Bringing It All Together
Now that we have the conversion handler we can update the button script accordingly. Copy, paste and compile the following script into the button.
==========
Copy & Paste
==========
on mouseUp
## Connect to the database
put "mysql" into theDBType
put "www.livecode.com" into theDBHost
put "runrev_test" into theDBName
put "runrev_example" into theDBUser
put "example" into theDBPassword
put revOpenDatabase( theDBType, theDBHost, theDBName, theDBUser, theDBPassword ) into theConnectionID
if theConnectionID is an integer then
## Query the database for data
put revQueryDatabase( theConnectionID, "SELECT * FROM Table1") into theCursor
if theCursor is an integer then
ConvertSQLCursorToArray theCursor, theDataGridArray
put the result into theError
if theError is empty then
## The cursor was successfully converted to an array.
## Assign it to the data grid. The 'firstname' and 'lastname' columns
## from the database cursor will appear in the matching columns
## in the data grid.
set the dgData of group "DataGrid 1" to theDataGridArray
end if
## Close the database cursor
revCloseCursor theCursor
end if
## Close the database connection
revCloseDatabase theConnectionID
else
answer "Error connecting to the database:" && theConnectionID & "."
end if
end mouseUp
command ConvertSQLCursorToArray pCursor, @pOutArrayA
local i
local theFields
local theError
## Get the names of all the columns in the database cursor
put revDatabaseColumnNames(pCursor) into theFields
if theFields begins with "revdberr," then
put item 2 to -1 of theFields into theError
end if
if theError is empty then
put 0 into i
## Loop through all rows in cursor
repeat until revQueryIsAtEnd(pCursor)
add 1 to i
## Move all fields in row into next dimension of the array
repeat for each item theField in theFields
put revDatabaseColumnNamed(pCursor, theField) into pOutArrayA[i][ theField ]
end repeat
revMoveToNextRecord pCursor
end repeat
end if
return theError
end ConvertSQLCursorToArray
The Result

Now when you click on the Display Data button the records from the database table will be displayed in the data grid.
I have just tried this and nothing happens - nothing is displayed in the datagrid.
I have downloaded the stack just incase I did something wrong and this does not work.
Have I done something wrong here?