Category: PHPRunner

This is useful where you have values in a table row that you wish to pass to a stored procedure.

As per the previous post I was wanting to use this in the context of needing to create a particular view from the data in a particular row.
Its probably possible to do this with some kind of DB::Query or DB::Lookup but in this instance I chose to use dynamic SQL. I will be working on other methods as well.

From my previous post I already had my SQL Azure Stored procedure defined as.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER   PROCEDURE [dbo].[auditselection]
(@firstname nvarchar(15),
@Dcreate datetime)
AS
BEGIN
SET NOCOUNT ON
 
EXEC ('CREATE or ALTER VIEW v01 AS 
SELECT dbo.T0001Persons.PKID, 
dbo.T0001Persons.Firstname, 
dbo.T0001Persons.DateCreated
FROM
dbo.T0001Persons 
WHERE 
dbo.T0001Persons.Firstname=' + '''' + 
@firstname + '''' + ' 
AND dbo.T0001Persons.DateCreated <= ' + '''' 
+ @Dcreate + '''' +
'')
END

Firstly I went to the list view in PHP Runner and within the designer I inserted a button into the row.
(I will be working on something a bit cleaner later but for now I’m just trying to get it to work)

In the CLIENT BEFORE part of the tri part event I put

params["Firstname"] = row.getFieldValue("Firstname");
params["Dcreate"] = row.getFieldValue("DateCreated");

Where Firstname and DateCreated are field values in my table T0001Persons

So in Javascript I am passing the field values to parameters called Firstname and Dcreate and then in PHP I pass those variables to the stored procedure and the database procedure does the rest.

Next I put the following code in the Server event

$sql = DB::PrepareSQL( "EXEC dbo.auditselection 
@firstname=':1',
@Dcreate=':2'", 
$params["Firstname"], $params["Dcreate"]);
DB::Exec( $sql );

Looking at the stored procedure syntax in SQL Azure.. you can see that the variables in the stored procedure that I happen to have been called @firstname and @Dcreate although they could have been named anything because PHP is passing the values and not the memory location(variable name) which makes sense because you are passing from a web server to a separate database server and the same memory spaces don’t exist in both.

Which lead me onto investigating how programs and operating systems manage memory space. link

And here are some screenshots of the code in PHPRunner

Javascript Client Before Part

PHP Server Part ( note this was my first effort which although worked did not qualify the database schema or name state the procedure variables being passed parameters)

and just for completeness the Javascript Client After Part

and how it looks in the list once deployed

and what happens once I have hit the button.

Of course this is all documented but when there is so much to learn it can take sometime to just look at the documentation and understand it. Most of what I have discovered here is written up
Link

Couple of points

Qualify the stored procedure with the database schema (although it does seem to work without this I am told this results in better performance)

You can qualify the variables that items are being passed to. Again will work without this.