Stored Procedures

I am working on a Flex application that needs to call a Stored Procedure using PHP. This proved to be a real challenge, and since I could not find a lot of information on how it was done, I thought that I would post it here. It might not be the best way of doing it, but it works for me.
The code is at the bottom so you can scroll down and skip the next bit out, I would.

For those of you that are still reading… wow! The library application required you to login into it using your domain username. This was passed to the application in the URL (http:\\mylibrary\index.php#username=MyGroup\Sean). When the application loads, it takes the username, passes it to a PHP page on the server that calls the MSSQL Stored Procedure and returns the users details.

The first step for me was to install the correct DLL’s and switch on the MSSQL extension. This proved to be quiet easy on WAMP, but a real pain on XAMPP. For some reason I could not get the extensions to register. My testing server runs WAMP, and the production server runs XAMPP. It was really annoying when it worked on WAMP but not XAMPP. Anyway, the problem was the DLL’s, so if you get a white screen, look there first.

Once that was working, I tested the PHP code, making sure that it returned values correctly. The PHP code is for AMFPHP.  It took a fair bit of testing to get it to work. In hind-site it might have been easier to do it in plain old vanilla PHP, but since the rest of the site is AMFPHP, I stuck to that route.

Part of the problem was the slash (\) in the username, and knowing how to escape it properly.

Once I got the data back from PHP, I moved on to the Flex side. Here the hardest part for me was how to read the URL and how to get the username from it. The code comes straight out of the help file, so for once Copy/Paste was an option.

I know it is not much, but I hope it helps.

Sean

Solution:

Make a few changes to your php.ini file.
allow_call_time_pass_reference = on
extension=php_mssql.dll (enable this extension)
extension=ntwdblib.dll

Place the php_mssql.dll file in the php extensions folder. Mine I placed in the php\ext folder.
Place the ntwdblib.dll file in the Windows\System32 folder

PHP Code to call the Stored Procedure – using AMFPHP:
//login.php
methodTable = array(
“byName” => array(
“description” => “User details by NAME”,
“access” => “remote”,
)
);
}

function byName ($username) {

$myServer = “myserver”;
$myUser = “myUser”;
$myPass = “myPassword”;
$myDB = “myDatabase”;

$s = @mssql_connect($myServer, $myUser, $myPass)
or die(“Couldn’t connect to SQL Server on $myServer”);

$d = @mssql_select_db($myDB, $s)
or die(“Couldn’t open database $myDB”);

//$username = $_GET[‘username’];

$partA = substr($username, 0,6);
$partB = substr($username, 6);
$username = $partA.”\\”.$partB;

$query = mssql_init(“GetEmployeeDetailsFromUsername”, $s);

mssql_bind($query , “@Username”, $username, SQLVARCHAR, FALSE, FALSE, 57);
mssql_bind($query, “@EmployeeNo”, &$Userno, SQLINT4, TRUE, FALSE);
mssql_bind($query, “@FirstName”, &$First, SQLVARCHAR, TRUE, FALSE, 50);
mssql_bind($query, “@LastName”, &$Last, SQLVARCHAR, TRUE, FALSE, 50);
mssql_bind($query, “@Email”, &$Email, SQLVARCHAR, TRUE, FALSE, 100);

mssql_bind($query, “RETVAL”, &$SQL_Count, SQLINT2);
$result = mssql_execute($query, FALSE);

echo $return = “”.$Userno.””.$First.””.$Last.””.$Email.”\n\r”;

return ($return);

}
}
?>

The code for the Flex file: (I have trimmed the code to only show the bit for the Stored Procedure)

<![CDATA[

import com.LoginUsername;

import mx.managers.BrowserManager;
import mx.managers.IBrowserManager;
import mx.utils.URLUtil;

private var bm:IBrowserManager;

[Bindable]
public var username:String;

private function init(e:Event):void
{
//Get Users
bm = BrowserManager.getInstance();
bm.init(“”, “Library”);

var o:Object = URLUtil.stringToObject(bm.fragment, “#”);
username = o.username;

fullname.text = username;

dispatchEvent(new LoginUsername(username));

}

[Event(name=”LoginUsername”, name=”flash.events.Event”)]

//Login.mxml

[Event(name=”LoginDetails”, name=”flash.events.Event”)]

//LoginUsername.as
package com
{
import flash.events.Event;

public class LoginUsername extends Event
{
public var uname:String;

public static const LOGINUSERNAME:String = “LoginUsername”;

public function LoginUsername(username:String)
{
super(LOGINUSERNAME, true, true);
uname = username;
}

override public function clone():Event
{
return new LoginUsername(uname);
}

}
}

0 thoughts on “Stored Procedures

Leave a Reply

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