Month: November 2021

PHP Runner : Tips and Tricks – Reload a page from event that belongs to a button

For example on a page we have a button that parses text coming in.

1. Add the following code to Javascript OnLoad event of the table in question

window.tablePage = pageObj

2. Add the following code to any Javascript event where you want to reload the table. Can be ClientAfterevent of any button

if( window.tablePage ) {
window.tablePage.reload({a:'reload'});
}

As far as I can tell what you name the pageObj in this case tablePage is up to you – at the point of naming you are creating this variable.
My hunch is that you would want to name this something relating to the page and something not overly complicated but unique.

The ClientAfterevent references the same pageObj for page refresh.

My understanding of the way the code works is.
On loading the page create a pageObj variable named window.tablePage

After pressing the button if there is a pageObj variable named window.tablePage refresh it!

PHP-Runner linked to SQL Azure – Passing parameters from a row to a stored procedure which is then Executed

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.

    • SQL Azure – Dynamic SQL – Using a parameterized Stored Procedure to Create a View

    There are situations where you need to create SQL on the fly from a record that you are currently dealing with. This is sometimes referred to as Dynamic SQL. Here I start investigating creating a dynamically created SQL view using a stored procedure. In my case I was doing this so that I could create some particularly valuable values.

    Here we have a table called T0001Persons that has the following three fields

    PKID
    Firstname
    DateCreated

    How do we create a stored procedure that will Create a view

    USE yourdatabasename
GO

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

    and then an example execution is 

    EXEC auditselection 'Mark', '2021-10-03'

    Using the script as facility in SSMS we can identify the query that has been created by EXEC auditselection 'Mark', '2021-10-03'.

    SELECT dbo.T0001Persons.PKID, 
dbo.T0001Persons.Firstname, dbo.T0001Persons.DateCreated
FROM
dbo.T0001Persons WHERE dbo.T0001Persons.Firstname='Mark'AND dbo.T0001Persons.DateCreated<'Oct  3 2021 12:00AM'

    Several things should be noted here

    Within the EXEC statement we use a single quote to escape the string and then a plus sign followed by the parameter to insert the parameter into the SQL that will be executed. A single quote is then used again to escape the code to go back into the string.

    But what if the SQL we want to run needs to include a single quote (which is often the case). I tried escaping the the string with a single quote and then a plus sign and then a Unicode Character references (only tried 39) surrounding a single quote but that didn’t really work.

    What did work was to have four single quotes with two pluses on either side. 

    + '''' +

    Here a single quote escapes the code and starts the string again. Two single quotes together are interpreted as a single quote (clearly some kind of special interpretation is happening in the background) and then the last single quote escapes the string back into code.

    From a discussion on Stackoverflow I also found the option to SET QUOTED_IDENTIFIER switch.

    If escaping your single quote with another single quote isn’t working for you (like it didn’t for one of my recent REPLACE() queries), you can use SET QUOTED_IDENTIFIER OFF before your query, then SET QUOTED_IDENTIFIER ON after your query.

    SET QUOTED_IDENTIFIER OFF;

UPDATE TABLE SET NAME = REPLACE(NAME, "'S", "S");

SET QUOTED_IDENTIFIER ON;
-- set OFF then ON again

    Next up I will be passing parameters to parameterized stored procedure via PHP Runner where the parameters are taken from an onscreen record.

    PHPRunner create an event to pass parameters to a stored procedure

    This looks promising
    Passing one of the field values as a parameter:

    $sql = DB::PrepareSQL( "EXEC StoredProcNameHere ':1'", $values["FieldName"] );
DB::Exec( $sql );

    and I have discovered that the :1 is considered a placeholder for the following variable and so passing two variables would come in as the following assuming both first and second parameters are text values. If :1 was the target of an integer instead of ‘:1’ we would have :1.

    $sql = DB::PrepareSQL( "EXEC auditselection ':1',':2'", $values["FieldName1"], $values["FieldName2"]);
DB::Exec( $sql );

    This is php here the single quotes DO NOT escape the string. They will form part of the string that will be executed which is what I want.
    The string is not escaped until the double quotes. Confusingly two single quotes look EXACTLY the same as a double quote but they are completely different. If you use your cursor you can see that the double quote is a single character which is interpreted completely differently by the php engine.

    The following link may be important as well in determining what can be achieved
    Grid row Javascript API