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 Data Grid Table

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

Add Columns

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

Create a Button for Testing

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

Edit Button Script

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

The Result

Now when you click on the Display Data button the records from the database table will be displayed in the data grid.

21 Comments

Daniel Shapiro

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?

Trevor DeVore

The example code wasn't reporting connection errors and it seems the example database isn't working at the moment. I'll update the code to display the error and try and figure out why we can't connect.

Trevor DeVore

The example db is up and running again and I have updated the example code with error reporting and the new example database name. Everything should function now.

markus

Hi Trevor
I cut and pasted the code, and all I got was the last name, not the first name. I also could not find the j which was refrenced in the local. Is that loop missing from the array?

Trevor DeVore

@markus: I just tested the code in a fresh stack and it worked as expected. Check the column name in the Data Grid and make sure it is "firstname".

"j" is not actually used. I have removed it from the sample script.

ken

This sort of works but the text in the cursor is truncated to the first character only. I can't look into the cursor through the variable value viewer - it just shows an integer. Should be two text columns with multiple rows.

MaxV

Hi,
my query with SQLite is:
"SELECT rowid,* FROM mytable"

but using your code the rowid column sometime appears empty, why?

Trevor DeVore

@MaxV - I'm not sure why that would be. Some suggestions off the top of my head - is rowid present for some rows in the same cursor but not others? Or is it empty for all rows? If it is empty for all rows then in the ConvertSQLCursorToArray you should check that rowid is being returned in the list of fields returned by revDatabaseColumnNames. If not then that would be the problem.

Trevor DeVore

@ken - If the text is being truncated then that would be an issue with revDB and not the code in this article. You should probably put together a very basic test. If you run into problems then I would suggest asking around on the mailing list or forums.

learningLC

So how would update a row in the datagrid using this same way that you illustrated? Spend the last 3 hours trying to update a row to my own mysql database with no luck. How?

Trevor DeVore

@learningLC - Updating a row in the data grid with new information is explained in this article:

http://lessons.runrev.com/s/lessons/m/datagrid/l/7315-how-do-i-update-data-in-a-row

You would query your database to get the new data and then update the data grid row using the method described in the article.

Paul Foraker

In order to reduce the number of database queries (and lines of code), I would have used

revDataFromQuery([columnDelim],[rowDelim],databaseID,SQLQuery[,varsList])

and then converted the data to an array using split.

Is there an advantage to using a cursor instead?

Trevor DeVore

@Paul - Using revDataFromQuery and storing the result as an array can take a really long time for large data sets. The cursor allows you to display the results from the query much faster. Plus, the large data set is being managed by the database.

Paul Foraker

Clear. Thanks.

Robert

Hi.How can I integrate an encoding for UTF-8 ( used by SQLite ) to Unicode ( used in DataGrid )?
Thanks Robert

Elanor Buchanan

Hi Robert

You can use the textDecode and textEndcode functions to convert between binary data and text. So, for example, when displaying UTF8 data from an SQLite database in LiveCode you would use something like

put textDecode(tDBData,"UTF8") into field "name"

I hope that helps.

Elanor

Robert

Thanks. Works now.
Robert

NURUL AMIRA

hye. may i know how to display data in next stack after fill the textfield. only display that data once we key in to the database. i hope you can replay my msg as soon as possible.

Elanor Buchanan

Hi Nurul Amira, I'm sorry but I'm not quite clear on what you want to achieve. Can you clarify?

Thank you,

Elanor

Mark

Just a note that it seems the connection to the db in the example stack is no longer working.

Elanor Buchanan

Hi Mark

I just tested it at it worked here. Maybe a temporary issue with the server? Could you test it now and let me know if it is still not working for you?

Kind regards

Elanor

Add your comment

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.