My favorites | Sign in
Project Logo
                
Search
for
Updated Aug 13, 2008 by maclema
Labels: Featured
Examples  

#Examples of using asSQL (Beta 2.5 and later)

On This Page

MySqlService Example

Token Responder Example 1

Token Responder Example 2

Inserting Binary Data Example

Selecting Binary Data Example

Stored Procedure Example

Streaming Results

MySqlService Example

This example is using MySqlService and DataGrid. The data grid's columns property and dataProvider property are bound to the MySqlService lastResult (ArrayCollection of Rows) and lastResultSet (The actual ResultSet).

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication 
        xmlns:mx="http://www.adobe.com/2006/mxml" 
        xmlns:assql="com.maclema.mysql.mxml.*" 
        layout="absolute">
        
        <mx:Script>
        <![CDATA[
			import mx.controls.Alert;
			import com.maclema.mysql.events.MySqlErrorEvent;
	        import com.maclema.util.ResultsUtil;
	
	        private function handleConnected(e:Event):void {
	        	service.send("SELECT * FROM employees2 LIMIT 10");
	        }
	        
	        private function handleError(e:MySqlErrorEvent):void {
	        	Alert.show(e.text);
	        }
        ]]>
        </mx:Script>
        
        <assql:MySqlService id="service"
                hostname="localhost" 
                username="root"
                password=""
                database="assql-test"
                autoConnect="true"
                connect="handleConnected(event)" 
                sqlError="handleError(event)" />
                
        <mx:DataGrid id="grid" left="10" right="10" top="10" bottom="10"
                dataProvider="{service.lastResult}"
                columns="{ResultsUtil.getDataGridColumns(service.lastResultSet)}" />
                
</mx:WindowedApplication>

Token Responder Example 1

This is an example of using an AsyncResponder to handle a query.

import com.maclema.mysql.Statement;
import com.maclema.mysql.Connection;
import com.maclema.mysql.ResultSet;
import mx.controls.Alert;
import mx.rpc.AsyncResponder;
import com.maclema.mysql.MySqlToken;
import com.maclema.util.ResultsUtil;

//The MySql Connection
private var con:Connection;

private function onCreationComplete():void {
	con = new Connection("localhost", 3306, "root", "", "assql-test");
	con.addEventListener(Event.CONNECT, handleConnected);
	con.connect();
}

private function handleConnected(e:Event):void {
	var st:Statement = con.createStatement();
	
	var token:MySqlToken = st.executeQuery("SELECT * FROM employees");
	
	token.addResponder(new AsyncResponder(
		function(data:Object, token:Object):void {
			var rs:ResultSet = ResultSet(data);
			Alert.show("Found " + rs.size() + " employees!");
		},
		
		function(info:Object, token:Object):void {
			Alert.show("Error: " + info);
		},
		
		token
	));
}

Token Responder Example 2

This is a more in depth example. With each statement, an info property is set on the MySqlToken. This way all queries and responses can be handled with the same result and fault handlers. This example also uses a statement that uses parameters.

import com.maclema.mysql.Statement;
import com.maclema.mysql.Connection;
import com.maclema.mysql.ResultSet;
import mx.controls.Alert;
import mx.rpc.AsyncResponder;
import com.maclema.mysql.MySqlToken;
import com.maclema.util.ResultsUtil;

//The MySql Connection
private var con:Connection;

private function onCreationComplete():void {
	con = new Connection("localhost", 3306, "root", "", "assql-test");
	con.addEventListener(Event.CONNECT, handleConnected);
	con.connect();
}

private function handleConnected(e:Event):void {
	getAllEmployees();
}

private function getAllEmployees():void {
	var st:Statement = con.createStatement();
	
	var token:MySqlToken = st.executeQuery("SELECT * FROM employees");
	token.info = "GetAllEmployees";
	token.addResponder(new AsyncResponder(result, fault, token));
}

private function getEmployee(employeeID:int):void {
	var st:Statement = con.createStatement();
	st.sql = "SELECT * FROM employees WHERE employeeID = ?";
	st.setNumber(1, employeeID);
	
	var token:MySqlToken = st.executeQuery();
	token.info = "GetEmployee";
	token.employeeID = employeeID;
	token.addResponder(new AsyncResponder(result, fault, token));
}

private function result(data:Object, token:Object):void {
	var rs:ResultSet;
	
	if ( token.info == "GetAllEmployees" ) {
		rs = ResultSet(data);
		Alert.show("Found " + rs.size() + " employees!");	
	}
	else if ( token.info == "GetEmployee" ) {
		rs = ResultSet(data);
		if ( rs.next() ) {
			Alert.show("Employee " + token.employeeID + " username is '" + rs.getString("username") + "'");
		}
		else {
			Alert.show("No such employee for id " + token.employeeID);
		}
	}
}

private function fault(info:Object, token:Object):void {
	Alert.show(token.info + " Error: " + info);
}

Inserting Binary Data Example

This is an example of inserting binary data.

import com.maclema.mysql.Statement;
import com.maclema.mysql.Connection;
import com.maclema.mysql.ResultSet;
import mx.controls.Alert;
import mx.rpc.AsyncResponder;
import com.maclema.mysql.MySqlToken;
import com.maclema.util.ResultsUtil;

//The MySql Connection
private var con:Connection;

private function onCreationComplete():void {
	con = new Connection("localhost", 3306, "root", "", "assql-test");
	con.addEventListener(Event.CONNECT, handleConnected);
	con.connect();
}

private function handleConnected(e:Event):void {
	//do something here
}

private function setEmployeePhoto(employeeID:int, photoFile:File):void {
	//the file bytes
	var filedata:ByteArray = new ByteArray();
	
	//read the file
	var fs:FileStream = new FileStream();
	fs.open(photoFile, FileMode.READ);
	fs.readBytes(filedata);
	fs.close();
	
	//execute the query
	var st:Statement = con.createStatement();
	st.sql = "UPDATE employees SET photo = ? WHERE employeeID = ?";
	st.setBinary(1, filedata);
	st.setNumber(2, employeeID);
	
	var token:MySqlToken = st.executeQuery();
	token.employeeID = employeeID;
	token.addResponder(new AsyncResponder(
		function (data:Object, token:Object):void {
			Alert.show("Employee " + token.employeeID + "'s photo updated! Affected Rows: " + data.affectedRows);
		},
		function (info:Object, token:Object):void {
			Alert.show("Error updating photo: " + info);
		},
		token
	));
}

Selecting Binary Data Example

This is an example of selecting binary data.

import com.maclema.mysql.Statement;
import com.maclema.mysql.Connection;
import com.maclema.mysql.ResultSet;
import mx.controls.Alert;
import mx.rpc.AsyncResponder;
import com.maclema.mysql.MySqlToken;
import com.maclema.util.ResultsUtil;

//The MySql Connection
private var con:Connection;

private function onCreationComplete():void {
	con = new Connection("localhost", 3306, "root", "", "assql-test");
	con.addEventListener(Event.CONNECT, handleConnected);
	con.connect();
}

private function handleConnected(e:Event):void {
	//do something here
}

private function getEmployeePhoto(employeeID:int, writeToFile:File):void {
	//execute the query
	var st:Statement = con.createStatement();
	st.sql = "SELECT photo FROM employees WHERE employeeID = ?";
	st.setNumber(1, employeeID);
	
	var token:MySqlToken = st.executeQuery();
	token.employeeID = employeeID;
	token.writeToFile = writeToFile;
	token.addResponder(new AsyncResponder(
		function (data:Object, token:Object):void {
			var rs:ResultSet = ResultSet(data);
			if ( rs.next() ) {
				//get the outFile from the token
				var outFile:File = token.writeToFile;
				
				//get the file data from the result set
				var filedata:ByteArray = rs.getBinary("photo");
				
				//write the file
				var fs:FileStream = new FileStream();
				fs.open(outFile, FileMode.WRITE);
				fs.writeBytes(filedata);
				fs.close();
				
				Alert.show("Photo written to: " + outFile.nativePath);
			}
			else {
				Alert.show("Employee " + token.employeeID + " not found!");
			}
		},
		function (info:Object, token:Object):void {
			Alert.show("Error getting photo: " + info);
		},
		token
	));
}

Stored Procedure Example

This is an example of calling a stored procedure that returns a ResultSet as well as output parameters.

import com.maclema.mysql.Statement;
import com.maclema.mysql.Connection;
import com.maclema.mysql.ResultSet;
import mx.controls.Alert;
import mx.rpc.AsyncResponder;
import com.maclema.mysql.MySqlToken;
import com.maclema.mysql.MySqlResponse;
import com.maclema.mysql.MySqlOutputParams;
import com.maclema.util.ResultsUtil;

//The MySql Connection
private var con:Connection;

private function onCreationComplete():void {
	con = new Connection("localhost", 3306, "root", "", "assql-test");
	con.addEventListener(Event.CONNECT, handleConnected);
	con.connect();
}

private function handleConnected(e:Event):void {
	var st:Statement = con.createStatement();
	st.sql = "CALL getEmployeeList(@LastUpdated)";
	st.registerOutputParameter("@LastUpdated");

	var token:MySqlToken = st.executeQuery();
	
	token.addResponder(new AsyncResponder(
		function(data:Object, token:Object):void {
			if ( data is ResultSet ) {
				//handle the results returned.
			}
			else if ( data is MySqlResponse ) {
				//check the affectedRows of the procedure
			}
			else if ( data is MySqlOutputParams ) {
				//get the output parameter.
				var lastUpdated:String = data.getParam("@LastUpdated");
			}
		},
		
		function(info:Object, token:Object):void {
			Alert.show("Error: " + info);
		},
		
		token
	));
}

Streaming Results

This is an example of streaming a very large ResultSet and updating a DataGrid every time we receive 500 new rows.

<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication 
        xmlns:mx="http://www.adobe.com/2006/mxml" 
        xmlns:assql="com.maclema.mysql.mxml.*" 
        layout="absolute" 
        creationComplete="onCreationComplete()">
        
        <mx:Script>
        <![CDATA[
        	import mx.controls.Alert;
        	import mx.rpc.AsyncResponder;
        	import mx.collections.ArrayCollection;
        	import com.maclema.mysql.ResultSet;
        	import com.maclema.util.ResultsUtil;
        	import com.maclema.mysql.events.MySqlEvent;
        	import com.maclema.mysql.MySqlToken;
        	import com.maclema.mysql.Statement;
        	import com.maclema.mysql.Connection;
        	
        	private var con:Connection;
        	
			private function onCreationComplete():void {
				con = new Connection("localhost", 3306, "root", "", "assql-test");
				con.addEventListener(Event.CONNECT, handleConnected);
				con.connect();	
			}
			
			private function handleConnected(e:Event):void {
				var st:Statement = con.createStatement();
				
				//turn on results streaming
				st.streamResults = true;
				
				//dispatch new rows event every 500 new rows
				st.streamingInterval = 500;
				
				//execute a query
				var token:MySqlToken = st.executeQuery("SELECT * FROM employees");
				
				//listen for our result set columns
				token.addEventListener(MySqlEvent.COLUMNDATA, function(e:MySqlEvent):void {
					grid.columns = ResultsUtil.getDataGridColumns( e.resultSet );
					grid.dataProvider = new ArrayCollection();
				});
				
				//listen for new rows
				token.addEventListener(MySqlEvent.ROWDATA, function(e:MySqlEvent):void {
					addNewRows(e.resultSet);
				});				
				
				//add a responder
				token.addResponder(new AsyncResponder(
					function(data:Object, token:Object):void {
						//call add new rows again to ensure we have all the rows
						addNewRows(ResultSet(data));
					},
					function(info:Object, token:Object):void {
						Alert.show("Error: " + info);
					},
					token
				));
			}
			
			private function addNewRows(rs:ResultSet):void {
				//get our data provider
				var dp:ArrayCollection = grid.dataProvider as ArrayCollection;
				
				//get the collection of new rows
				var newRows:ArrayCollection = rs.getRows(false, dp.length, (rs.size()-dp.length));
				
				//concat our current source, and our new rows source
				dp.source = dp.source.concat( newRows.source );
				
				//refresh our data provider
				dp.refresh();
			}
		]]>
        </mx:Script>
        
        <mx:DataGrid id="grid" left="10" right="10" top="10" bottom="10" />
                
</mx:WindowedApplication>

Comment by tunde.famakinwa, Mar 26, 2009

Pls why cant this be used with a Flex web Application ? Except AIR apps?

Comment by icodef...@yahoo.com, Mar 26, 2009

I created a Flex app using the first example and it works great. My flex app and mySQL db are on my local box, and I didnt have it set up with a webserver in my test. But everything works so far.

Cool code! I will be using to to create a Flex version of my air app, replacing the local sqlLite db with mySQL when the user is running in the Flex version.

cheers! Dustin

Comment by tho.benjamin, May 13, 2009

If somebody find how to use this code in flex web application...

Comment by rfkrocktk, May 15, 2009

Pls why cant this be used with a Flex web Application ? Except AIR apps?

It CAN be used with a Flex web application, but it's completely insecure. Storing your MySQL username and password anywhere that's web accessible is downright dangerous and wrong. Anyone can decrypt a SWF with enough determination, and then your database is owned by the world wide web. Even if you set your database account to be read-only, damage can still be done, and why would you want someone reading your database at their own whim?

Comment by guil.leclercq, Jun 11, 2009

I'm using this component in an air application and was working so far with a local MySql? database.

If someone could tell me how to set the "connection(...)" passing through a server now and the things to change in the flex project it would be very helpfull.

Comment by jaishah11, Jul 04, 2009

Is there anything like transaction management in asSQL. Since, browser will be on client side, he can anytime close it in the middle of a transaction resulting inconsistent data if not rolled back properly. Please advice.

Best Regards, Jai Kishan Shah

Comment by kwonob62, Jul 16, 2009

The stored procedure uses input parameters How to ask for

Comment by RaulBlanco122, Aug 05, 2009

I'm using assql with Flex 3 and sql. I'm having problems on insert some characters like ñ in 'españa' or characters like á é í ... I think this is because of the encoding. I have my sql database in UTF-8, but which encoding do assql use? thanks

Comment by vaclav.uher, Sep 18, 2009

When I try Example 1 or Example 2 in Flash CS4 with imported asSQL-Beta2.7.swc, I get this error: "1046: Type was not found or was not a compile-time constant: ArrayCollection?." What do I do wrong? Or is problem somewhere else? The file is AIR.

Comment by leef...@gmail.com, Sep 29, 2009

吓我了一跳,真的可以用,我现在在用纯AS3做项目,试了几次不行,按照例子在FLEX中可以运行。

Comment by xpecter, Oct 04, 2009

hi there.. theres some example to connect a webserver? localhost works fine... but on my lan ip dont.. btw very useful lib

Comment by wai...@gmail.com, Oct 13, 2009

Is this still on? Great project!


Sign in to add a comment
Hosted by Google Code