How to use MySQL stored procedures with AS3FlexDB

I wrote this post because a lot of people are asking if is possible to use MySQL stored procedures with AS3FlexDB and how. You can use AS3FlexDB, but first you must make  some changes, because the mysql extension can’t handle the data sets returned by stored procedures.

1. Enable mysqli extension from php.ini.

2.  Copy/paste the new version of as3flexdb.php. It should be in your www/amfphp/services/mysql folder.

 * Main class.
class AS3FlexDB
	var $NAME 		= "as3flexdb";
	var $VERSION	= "3.0.0";
	var $USERNAME	= "root";
	var $PASSWORD	= "";
	var $SELECT		= "select";
	var $INSERT		= "insert";
	var $UPDATE		= "update";
	var $MULTIPLE	= "multiple";
	var $DELETE		= "delete";
	var $ERROR		= "error";
	 * Constructor.
	function AS3FlexDB() {}
     * Execute a SQL statement to databse.
     * @returns the result object.
	function query($query, $host, $database, $type)
		$res = new AS3FlexDBResult();
		$res->type = $type;
		$res->records = array();
		$mysqli = new mysqli($host,$this->USERNAME,$this->PASSWORD,$database);
		$rsResult = $mysqli->query($query);
		// Return the error
			$res->type = $this->ERROR;
			$res->error = $mysqli->error;
		   	return $res;
		// Create proper result
			case $this->SELECT:
				while($row = $rsResult->fetch_object())
					array_push($res->records, $row);
			case $this->INSERT:
				$res->lastInsertId = $mysqli->insert_id;
		return $res;
	 * Execute multiple SQLs to database.
	 * @returns an array of arrays
	function queryAll($querys, $host, $database)
		$res = new AS3FlexDBResult();
		$res->type = $this->MULTIPLE;
		$res->records = array();
		foreach($querys as $q)
			$tmp = $this->query($q['q'], $host, $database, $q['option']);
			array_push($res->records, $tmp);
		return $res;
     * Class name.
     * @returns a string that reprezent class name.
	function getName()
		return $this->NAME;
     * Class version.
     * @returns a string that reprezent class version.
	function getVersion()
		return $this->VERSION;
 * Returned object.
class AS3FlexDBResult
	var $type = 'select';
	var $records = array();
	var $error = '';
	var $lastInsertId = 0;

3.  Create a table for test in database named test.

CREATE TABLE `users` (
  `fname` VARCHAR(255) NOT NULL,
  `lname` VARCHAR(255) NOT NULL,
  PRIMARY KEY  (`id`)
INSERT INTO `users` (`id`, `fname`, `lname`) VALUES
(2, 'bogdan3', 'manate'),
(1, 'bogdan', 'manate'),
(3, 'bogdan32', 'manate');

4. Create a stored procedure. You may not be able to create it from phpadmin, so I suggest using another MySQL client.

    SELECT * FROM users WHERE 1;

5. Create a Flex example ( If you are using services-config.xml don’t forget to add it to the compiler arguments ).

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="" layout="vertical" creationComplete="init()">
	<mx:DataGrid id="dg">
			<mx:DataGridColumn dataField="fname" />
			<mx:DataGridColumn dataField="lname" />
	<mx:Button label="Get data" click="clickHandler(event)"/>
			import flash.external.*;
			import mx.collections.*;
			import mx.rpc.AsyncResponder;
			import mx.rpc.IResponder;
			import phi.db.Database;
			import phi.db.Query;
			import phi.interfaces.IDatabase;
			import phi.interfaces.IQuery;
			public var db :IDatabase;
			public var query :IQuery = new Query();
			private function init():void
				Database.getInstance().connect( "db", "localhost", "test", true);    
				db = Database.getInstance();   
				query.connect( db.getDefaultConnectionName(), db);
			protected function clickHandler(event:MouseEvent):void
				// TODO Auto-generated method stub
				var rs :IResponder = new AsyncResponder( querySelectHandler, queryErrorHandler );
				var sql :String = "CALL getUsers()";
				// you can set the query type to Query.SELECT because the stored procedure return
				// the same data set like an SELECT statement
				query.execute( sql, Query.SELECT, rs);
			protected function querySelectHandler( result:Object, token:Object = null ):void
				dg.dataProvider = result as ArrayCollection;
			protected function queryErrorHandler( result:Object, token:Object = null ):void

2 Replies to “How to use MySQL stored procedures with AS3FlexDB”

  1. Thank you, Bogdan. This helps me a lot.

    I wonder if we could use AS3FlexDB (1.4) with other types of DB such as MS SQL or Oracle rather than MySQL only? I try to change the SOURE constant in, and on the server side, I change something in the service to connect to MS SQL, but out of success although I have fixed the MS SQL adapter following the guide here:

    It will be wonderful if we can change the DB one by one at runtime.


  2. Hi Ronald,

    You can use the AS3FlexDB v1.4 with other types of DB, but you should change the PHP service. For now you can’t change the DB types at runtime, but I will talk with Alex and maybe we will include that feature in the next release of AS3FlexDB. If you want to use other types of DB please send me an email using the contact form or post an comment to this article.

    Have a nice day,

Leave a Reply

Your email address will not be published. Required fields are marked *