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.

<?php
 
/**
 * 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
		if(!$rsResult)
		{
			$res->type = $this->ERROR;
			$res->error = $mysqli->error;
 
		   	return $res;
		}
 
		// Create proper result
		switch($type)
		{
			case $this->SELECT:
			{
				while($row = $rsResult->fetch_object())
					array_push($res->records, $row);
			}
			break;
 
			case $this->INSERT:
			{
				$res->lastInsertId = $mysqli->insert_id;
			}
			break;
 
			default:
		}
 
		$mysqli->close();
		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` (
  `id` INT(5) NOT NULL AUTO_INCREMENT,
  `fname` VARCHAR(255) NOT NULL,
  `lname` VARCHAR(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
 
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.

CREATE PROCEDURE getUsers()
  BEGIN
    SELECT * FROM users WHERE 1;
  END

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="http://www.adobe.com/2006/mxml" layout="vertical" creationComplete="init()">
	<mx:DataGrid id="dg">
		<mx:columns>
			<mx:DataGridColumn dataField="fname" />
			<mx:DataGridColumn dataField="lname" />
		</mx:columns>
	</mx:DataGrid>
	<mx:Button label="Get data" click="clickHandler(event)"/>
	<mx:Script>
 
		<![CDATA[
			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
			{   
				trace("Error"+query.getError());
			}
 
		]]>
	</mx:Script>   
</mx:Application>

Ronald says:

November 22, 2010 at 8:33 pm

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 ConnectionData.as, 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:

http://fcolaco.com/blog/2009/07/02/amfphp-and-mssql/

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

Regards,
Bogdan Manate says:

November 22, 2010 at 8:48 pm

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,
Bogdan

You may also like...

Blogging from my BlackBerry 8520 Curve

Your Flash Builder is running slow ? Allocate some memory to JVM

Adobe developer day 10-14 May

2 Responses