SQL Azure – Using Azure Logic Apps to execute a Stored Procedure in SQL Azure

It is important in a lot of systems to be able to run queries on a regular schedule. Previously I have outlined that this can be done using an automation account. It is also possible to accomplish this using an Azure Logic App to call a Stored Procedure.

The procedure is
Create Logic App
Add items to it

Firstly using SSMS connect to the database and create an example stored procedure.

CREATE PROCEDURE [dbo].[TestInsert]

INSERT INTO dbo.T0003NLPRSitesTarget
SELECT Sitename from dbo.T0003NLPRSites

Next log into your Azure Portal and identify the following icon

Select it and then hit the Add button..

You will be presented with the following screen

Now you should be taken to an overview showing that the logic app has been created

Hit Go to resource

You should be presented with the following screen

Here we choose Recurrence

You will be presented with the following Logic Apps Designer

Here is an example
Interval is like how many times the logic app will run over what Frequency
So if we put 3 in Interval and Frequency Day it would run once every 3 days
At these hours – sets specific times when the logic app will run within the set Frequency
On these days – allows for you to set what days the logic app will run At these hours

To explain the logic app running work from the bottom up. So the below example

On Monday, Tuesday , Wednesday, Thursday and Friday at 9,10,11,12,13,14,15,16 and 17 hours

Every week this will run (If Interval had been set to 3 it would have been every three weeks on Monday,Tuesday,Wednesday, Thursday and Friday at the hours 9,10,11,12,13,14,15,16 and 17 hours it would run.

Now hit next step and then type in execute stored procedure

You will first need to set up the connection to the database and then enter the parameters of the server database name database username and password or your chosen security to connect to the database and then you will be taken to the appropriate item.

You will need to set up the firewall on the server to ensure otherwise the logic app is likely to stop working at somepoint..

You can get a list of the appropriate firewall rules to set up by going here..

Managed connectors outbound IP addresses

So after further testing with the following parameters I discovered that in practice SQL Azure is a bit loose about the hours recurrence option. For example the following

Has been runnnig at 43minutes past the hour. Which is indicative of when I hit a run trigger at..

I will be checking to see when it next runs but I suspect it is going to be 17:43

SQL Azure (Basic notes on backup operation)

If you are running things on the cloud you always want to be thinking about backups and ensure that you are really aware of what is happening with backups and how to restore from backups. You should regularly check what is happening with backups and regularly practice restoring databases.

Basic / Standard and Premium SQL Azure has two basic options for restore.

Point in Time Restore and
Long Term Retention

They perform the same task – they collect old databases that can be used to restore to a server if need be but what varies in the UI by which you restore them.

First point – Long Term Retention Policy is attached to the server and you go into it by first navigating to your server and then looking to the left hand section marked as Data management.

Select Backups and you will be presented with a list of all the long term backups of the databases on that server. The following are a couple of basic dtu databases and what can bee seen on the fictional server 01SeverName

In the above table you can see that Long Term Retention policy has NOT been set. How do you change this.

Firstly ensure that you are on the retention policies tab and then tick the particular database you would like to set the retention policy for in the lower section this will allow you to Configure a policy for that particular database.

Here are the options for a BASIC DTU database

Compare this with the options for a database in the Standard Tier

Set it up and then hit apply and you should be good to go.

MS SQL Azure – CREATE USER with read only access to 2 tables

In a lot of situations you may need a satellite website feeding off the main website with greatly reduced access.

It is a good idea to only give that user access to only what they need and limit it down to the bare minimum.
Here I have an azure database called LandRegister and I connect to it in the usual way through SSMS.

Use LandRegister

USE LandRegister

CREATE user TestUserRestricted with password = 'Password01';

Next you can go to the security section after a refresh to see if the user has been added.

And then grant access to the tables you wish them to see.

USE LandRegister

GRANT SELECT on dbo.T0003NLPRSites to TestUserRestricted
GRANT SELECT on dbo.T0024Authority to TestUserRestricted;

TestUserRestricted can now be used by your web application without any fear that
1) A USER cannot login to a server through SQL Server Management Studio
2) That they can see anything except the tables listed.
3) That in this case they can do anything except select statements on the stated tables. (So no write delete or updates on table)

Here I give full access to the table

USE LandRegister

dbo.T039Country to TestUserRestricted;

And it works with views as well

Use LandRegister

GRANT SELECT on dbo.v032 to TestUserRestricted;

MS Azure & Web Applications – TSQL Create a check constraint on nvarchar column specifying a minimum length in DB and through the front end ensure enforce OWASP security

Double security!
Enforce length in database and then through the front end by validating in this case password values (PHPRunner example).

First the backend
You have a nvarchar(128) column in MS Azure SQL Database (or any Microsoft SQL Server database) and you wish to add a check constraint that does not allow a value in the column to be less than lets say 15 characters long.

tablename = users
field = userpassword

ALTER TABLE dbo.users
ADD CONSTRAINT [MinLengthConstraint] 
CHECK (DATALENGTH([userpassword]))>14;

And then a good way to check the constraints on an individual table using TSQL through SSMS for MS AZURE.


Front End
Now for double security in your web application use a regular expression to prevent users entering a weak password.

^               # start of the string
(?=.*\d)        # a digit must occur at least once
(?=.*[a-z])     # a lower case letter must occur at least once
(?=.*[A-Z])     # an upper case letter must occur at least once
.{15,128}       # 15-128 character password must be a minimum of 15 characters and a maximum of 128
$               # end of the string

OWASP – Open Web Application Security Project

cPanel – take a manual backup of a MariaDB / MySQL database

There are no shortages of articles on how to take a backup of a MySQL or MariaDB database from cPanel however given how important it is I like to write these things down showing how I accomplished this for my own reference. Its extremely easy to do a download which means that you should not have any opportunity or reason not to do it regularly if you have an important database that for instance is part of a web application.

1. Enter your given cPanel management portal.

2.Find the database section and select the phpMyAdmin icon.

3.Select the database you are interested in from the lefthandside.

4.Click export in the menu section and then its just a case of clicking go. An SQL will be downloaded to the download directory (on a windows machine). And keep this and you can run this to create a new database.

And its very easy to go in and check if its ok you can use any good ide or you could change the suffix to TXT and then just look at it in word.
Happy backing up.!!!

MS Azure – Web App Service – Notes on creating a staging web front end on an existing Web App Service

At some point in developing sites for clients you will may want a satellite site feeding off the main database but with prototype features on it. Considering that certain levels of web app service come with free staging sites you may as well use these rather than either the free service which may be less well provisioned or a separate paid site that you will have to pay additional money for. The number of slots available will depend on the service you are on.

Beginners guide to setting up a staging slot

Once you have the site up and running you operate it in the same way as a normal service and so you can get the publishing details from the Get Publish Profile.

SQL Azure – Creating a numbers table using a Cartesian Product and then expanding dates between two field values for individual records

The following code uses a Common Table Expression to create a numbers table ,which is then used to expand all dates between two date values of an individual record. This structure could be used in a variety of circumstances where you are needing to charge costs to a product over a period of time or if you are needing to record consecutive dated values between two dates.

I am using Whisky Casks as an example here. Imagine a cask came in on a day and needs to be matured for a period and there will be a charge for every day that the cask is being stored to an account. Clearly going forward you might want to set up an overnight process to create the daily charges. But what happens where you wish to retrospectively create charges. Charge records will build up quickly and it will be difficult to manually keep on top of the required record correction.

Firstly I create a table that will hold a single record for each date a cask is in bond between the two tables. There are additional columns here as I need them for my use case (casks in bond).



CREATE TABLE [dbo].[t066tempexpand](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[chargedate] [datetime] NULL,
	[accountcode] [nvarchar](16) NULL,
	[pkidt002] [int] NULL,
	[pkidt053dailystoragecharge] [int] NULL,
	[locationwarehouse] [nvarchar](50) NULL,
	[pkid] ASC

Next we create a CTE numbers table and use this to explode a single record for each dated day between the two dates in this case bondindate and a disgorge date. I have an extra filter here that filters what casks are being put into the table (here pkid>2000). Other filters could be where disgorgedate is not null. The following line will be used to identify the charge date.
join Numbers v on v.number between 0 and datediff(day, bondindate, disgorgedate)
The first line shows the date that will be charged. The join is particularly elegant as it filters or restricts the records within the numbers table stored as a common table expression to multiple records of an individual cask based on all dates between two field values. The starting date identifies where to explode from and the second identifies the ending date (in this case the disgorge date) identifies where it must end. Another example if you were wanting to bring it up to todays date or the day before today , if you have an overnight process running at midnight getdate()-1.

The cross join in the common table expression definition is used to create a cartesian join on itself, from my investigations there is no particular significance to the sys.columns table used other than this is a system table that holds all columns in all tables in the database and therefore is likely to be somewhat large. It so happens in the database that I am using there are a lot of tables and a lot of columns so a single self cross join creates more than enough numbers than I will ever need. If you wish to create further records you can further cross join the sys.columns table on itself by repeating cross join line with subsequent aliases eg (cross join sys.columns c). After experimentation I discovered for instance if I left out the cross join there were only some 4,000 records in my numbers table. This is suspect reflects the fact that I have 4,000 columns in the tables of my database. In my case 4,000 X 4,000 or the cartesian product of the sys.columns table on itself results in 16 million records more than enough numbers for me. It should be noted that I am restricting the CTE numbers table to the top 20,000 anyway which in our case relates to over 54 years which is more back dating than I think I will ever need. It should be noted the original code I discovered had three cross joins presumably because the number of tables and so columns in the database was much smaller.

;WITH Numbers(number) AS (
  select top(20000) row_number() over (order by (select 0))-1
  from sys.columns a
  cross join sys.columns b

INSERT into t066tempexpand
  from t002caskfill d
  join Numbers v on v.number between 0 and datediff(day, bondindate, disgorgedate)
  where pkid > 2000

Here I am joining with an extra table to find out the daily storage charge and this is just a test.

from dbo.t0000temp a
join dbo.v128dailycaskstoragelk023 b on a.pkidt053dailystoragecharge=b.pkid 
order by pkidt002,chargedate;

And lastly I insert all this information into a Charge history table.

INSERT INTO dbo.t0032chargehistory
from dbo.t066tempexpand a
join dbo.v128dailycaskstoragelk023 b on a.pkidt053dailystoragecharge=b.pkid 
order by pkidt002,chargedate;

SQL Azure – Create a Function that uses multiple If Else statements

Example create function code / SQL Azure / that uses the IF ELSE statement to calculate dates between three parameters(referenced to fields) to determine an integer difference between dates – in this case whisky in a bond we are calculating number of days and the parameters are – date cask arrived in bond, date cask out of the bond and date cask is disgorged.
In English I am calculating number of days in a bond from the bond in date to todays date if there is no bond out date or disgorge date. If either of those dates exist the number of days in bond is the bond in date to the earlier of either of those dates.

I add in an extra day so that if a cask comes in on Tuesday and goes out the following days the number of days is counted as 2.

Clearly this is a nice structure that can be used in multiple contexts with different factors. I use the most common option in the first switch in the thought that this will speed things up on the server.

CREATE FUNCTION [dbo].[calculatedaysinbondcorrect]
@datebondin Date,
@datebondout Date,
@datedisgorge Date
RETURNS integer
DECLARE @date1 DATE, @date2 DATE, @date3 DATE;
DECLARE @daysinbond INT;

SET @date1 = DATEADD(DAY,-1,@datebondin)
SET @date2 = @datedisgorge
SET @date3 = @datebondout

IF ((@date2 is null) and (@date3 is null))
		SET @date2 = getdate()
ELSE If ((@date3 is null) and (@date2 is not null))
		Set @date2 = @datedisgorge
ELSE IF  ((@date3 is not null) and (@date2 is null))
		SET @date2 = @datebondout
ELSE IF (@date2 > @date3)
		SET @date2 = @datebondout
ELSE IF (@date2 < @date3)
		SET @date2 = @datedisgorge
ELSE IF (@date2 = @date3)
		SET @date2 = @datedisgorge

SELECT @daysinbond = DATEDIFF(dd, @date1, @date2)

RETURN @daysinbond;

Favicon Creation a suggested method

Icons are not just about branding.

They also assist users in distinguishing your application from other applications that may be open on the desktop. Now in the past the configuration of these icons seems to have been a bit flaky with different browsers requiring different formats. After quite a bit of research I have managed to settle on a procedure which will get you good icons for your php applications.

Firstly identify a picture that you wish to make into an icon.

Next get as good a quality 256 by 256 png of the icon you wish to display ensuring that if you require transparent background you include it into the png.

Next go into Inkscape and make 2 exports
1) as a 256 by 256 icon. This will be for desktop windows computers.
2) as a 57 by 57 icon titled apple-touch-icon.png

Then go to an icon converter site

The one I used to go to doesn’t seem to exist anymore but the following site seems to do a good job.

RedKetchup Icon Converter

There should be a step procedure here go through each step.

Step 1 : Choose the png file that you exported from Inkscape
Step 2 : Choose your preferred shape I normally just choose the square
Step 3 : It is important here to choose the multi size in one icon option as follows.

Note if you select multiple image shapes you will get multiple icon sizes. Download the generated ico file and then rename it to favicon.ico.

Place the favicon.ico file and the apple-touch-icon.png file in the root directory of your PHP application and upload to server everything should now work its way to desktop icons.

SQL Azure – Script to identify all Users in a SQL Azure DATABASE and how to remove Users

SELECT name AS username, 
     create_date, modify_date, 
     type_desc AS type, 
     authentication_type_DESC AS authentication_type 
FROM sys.database_principals 
WHERE type NOT IN ('A', 'G', 'R', 'X') 
          sid IS NOT null ORDER BY username;

And to remove a user one of the easiest ways both to see all the users and remove any of the individual users. After connecting to your Azure Database within the database in question identify the Security Branch and then the Users branch – right click on the user you wish to remove and select DELETE.

This follows on from the post whereby we were trying to creat a user see here
Creating Contained Users

SQL Azure – Azure Automation Hybrid Worker Setup and RunAs Account

The highs and lows.

You’re flying high – you’ve created a stored procedure that you’ve managed to reference in your automation account within a powershell runbook and you’ve got it nicely churning over on an extremely regular basis. You want to run it daily or weekly but as a test you run it hourly just to ensure that it works before releasing it to production. A day passes ok and you think wow this is really great. Then you forget about it and come back to it a week later. You find that somehow it stopped working at 1 am on Tuesday.

What happened????

Sorry you got hit by a block resulting from dynamic IP change on the runbook in the Automation Account – DAMN.
(there is probably a good reason for having a dynamic IP on automation accounts but damn it was really going too well)

So after quite a lot of investigation I found those in the know seemed to be saying the solution is.
Azure Automation linked to a Hybrid Worker

I kept investigating this and I kept getting directed to Microsoft documentation which although really extensive was just not helping me really get my head around things. It wasn’t until I found Travis Roberts that things started to click… I link to three of his videos below.

So the downsides of Hybrid Worker setup..

  • Despite being the recommended solution configuration is complicated.
  • You need to create a VM with a static IP just to run the hybrid worker (and then grant firewall access to the databaseserver for that VM)
  • That VM needs to have 2 cores and 4Gb of Ram (yes unfortunately not the smallest VM so you could be looking at $25 a month charge)
  • You need to set up log analytics
  • It is recommended that log analytics is in the SAME Azure location as the sample database BUT not all Azure locations have log analytics set up… Use UK South! Hands up who has already set up their server and just realised it is in a location that doesn’t allow log analytics.
  • So I was really struggling with this after my runbook unexpectedly started failing. After several days of reading and searching I found Travis Roberts. Hallelujah for MVPs.

    So I haven’t done it yet but I think the procedure can all be defined in here.

    1. Setting up an Azure VM with a fixed IP (It can be any VM but Azure is good for me)
    Setup VM with static IP
    The server you setup will need to have internet connectivity with SSL outbound on port 443

    2. Setting up Azure Log Analytics a step by step guide
    Setting up Azure Log Analytics a step by step guide

    3. Azure Automation Setup and Linking Log Analytics to your Automation account
    Set up Azure Automation and link it to Log Analytics

    4. Configuring an Azure Automation Hybrid Runbook Worker to run your runbook.
    Installation of Hybrid Runbook Worker
    Includes information about Runbook Worker Group and
    You add credentials to the Runbook Worker Group

    Firstly a big thank you to Travis Roberts for putting out those videos he has loads of Azure Centric content on his youtube channel which you can find here

    CAVEAT I haven’t implemented this myself properly and at the time of writing(December 2021) those videos are a couple of years old now. At the start of one of the Videos Travis indicates that things are changing quickly in the space and expect that there has been changes since these videos. Lets face it this should really be easier to do Azure is just frustrating their users with this complicated configuration.

    SQL Azure – Using Azure Automation and Runbooks to Schedule an Azure SQL Stored Procedure

    Its vital to be able to run stored procedures on a schedule it can be extremely important for doing daily housekeeping.

    This site was where I learned how to do this.
    How to Schedule an Azure SQL Database’s Stored Procedure

    Runbooks sit in Azure Automation and a Runbook consists of a script that performs a task.

    Runbooks can be written in Python or Powershell. See the last part of this post for an important additional issue.

    "Data Source=01server.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=01userid;Password=JK12jl423;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
    # Instantiate the connection to the SQL Database
    $sqlConnection = new-object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Data Source=servername.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=userid;Password=password;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
    Write-Output "Azure SQL database connection opened"
    # Define the SQL command to run
    $sqlCommand = new-object System.Data.SqlClient.SqlCommand
    $sqlCommand.CommandTimeout = 120
    $sqlCommand.Connection = $sqlConnection
    Write-Output "Issuing command to run stored procedure"
    # Execute the SQL command
    $sqlCommand.CommandText= 'exec [dbo].[storedProcedure]'
    $result = $sqlCommand.ExecuteNonQuery()
    Write-Output "Stored procedure execution completed"
    # Close the SQL connection
    Write-Output "Run completed"

    Honestly the hardest part is getting the code setting up the scheduling after that is relatively easy.

    WARNING : Runbooks have dynamic IPs so although you can set grant access to an IP your runbook will only run for a certain period before it stops working unless you either have a Hybrid Runbook Worker setup OR you allow Azure services and resources to access this server (which isn’t recommended) The next post I will do is my initial notes on how to configure a Hybrid Runbook Worker.

    SQL AZURE – Group records or widgets in Sets of a Specific Number

    Useful if you have factory products that are being packed into sets of a specfic number. In our case sixes.

    Swap out to a different amount if you need to. The last group in the record set will have a remainder level if the recordset is not perfectly divisible by the set integer.

    SELECT pkid,
    floor(((ROW_NUMBER() OVER(ORDER BY pkid))-1)/6)+1 as grpNum
    FROM t002caskfill

    Here is a screenshot of the above code being run on a table.

    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 ) {

    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.

    ALTER   PROCEDURE [dbo].[auditselection]
    (@firstname nvarchar(15),
    @Dcreate datetime)
    SELECT dbo.T0001Persons.PKID, 
    dbo.T0001Persons.Firstname=' + '''' + 
    @firstname + '''' + ' 
    AND dbo.T0001Persons.DateCreated <= ' + '''' 
    + @Dcreate + '''' +

    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 
    $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

    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


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

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

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

    SQL Azure – Convert UTC to your local time – and an interesting observation

    Not unsurprisingly SQL Azure GetDate() stores datetime as a UTC value.

    If your application is hosted in a specific country it is likely you will wish to return that value to the local user in the local time.
    Here I host in the UK and want to display in the UK so my choice is simple enough.

    The default CONVERT() statement in the SQL Azure library will do this for you.

    An example of the statement with parameters is here (slightly confusingly here I have the field name as a reserved word but just ensure that your field name is in the square brackets)

    [datetime] AT TIME ZONE 'GMT Standard Time'))) 
    as UKTime

    And here is that statement included in a full query.

    And here is an SQL statement to determine all time zones currently observing daylight saving on your server.
    This query can also be used to identify the pre-defined text string parameter you should have for your convert statement.

    FROM sys.time_zone_info
    WHERE is_currently_dst = 1;

    Running this I get the following..

    Now what is interesting about this is that the text string defined by Microsoft is GMT Standard Time and according to my investigations it definitely adjusts for British Summer Time (which most of the time is what I want!). OK but if you go to Wikipedia you get the following definition of what GMT is :-

    Greenwich Mean Time or GMT is the time displayed by the Shepherd Gate Clock at the Royal Observatory in Greenwich, London. When the sun is at its highest point exactly above the Prime Meridian, it is 12:00 noon at Greenwich.

    GMT is not affected by Daylight Saving Time (DST) clock changes.
    The Greenwich Meridian (Prime Meridian or Longitude Zero degrees) is the reference point for every Time Zone in the world.

    Every 15° longitude represents one hour’s difference in time: (24 x 15 = 360, the degrees of a circle). You can work out the time at every location on earth if you know how many degrees it is east or west of Greenwich.

    GMT is not affected by Daylight Saving Time (DST) clock change -> So obviously that contradicts the definition in SQL Server – fair play to the engineer who set up the sys.table he just wasn’t aware. Probably won’t be changed now but its indicative of the danger there is in dealing with time when you need to be ultra accurate. Users tend to think that dealing with time is straight forward but in reality its not!

    Multi-tenancy – Design Options

    Some personal notes on different options for managing multi-tenancy

    Silo – Different databases for each tenant. Again PHPRunner has an excellent solution for this.

    Siloed Database with one Front End PHPRunner

    Bridging – Unique schema for each tenant – for me this I can’t see any great advantages to this and quite a lot of extra required work.

    Row Level Security – Tenancy managed by the database – One database additional column in tables that record the tenant and prior to showing results of query the database will filter results by tenant. Policy set up with unique user for each tenant. Postgres applicable. Still requires an additional field in all the tables but it is nice to have and can be extremely quick to implement.

    Client Software – Client side implemented tenancy – PHP Runner has an option to implement Row Level Security in the design of the front end.

    Amazon Web Services article on Multi-tenancy

    Some points – for postgres implementing Row Level Security on tables does not carry through to the views which is a major issue for me. There are way rounds it though. It would be great to have the database managing the row level security and if you have all your tenants in one database possibly the best way to do it is using the below.

    How to Add Row Level Security to Views in PostgreSQL

    Identify whether you are using MariaDB or MySQL using phpMyAdmin (through cPanel here)

    When it comes to relational databases I used to think right there’s

      SQL Server

    I know there is MariaDB as well and I know that is a direct substitute for MySQL I think I might be using it but you know what I’m not really sure. Whenever I look things up I quite often look for MySQL and to date any information obtained using those parameters have worked when executed against my databases through phpMyAdmin.

    Should I be using MariaDB or hang on – am I???

    Well turns out they are so similar that you might be using and not even know it. Turns out my hosting company uses MariaDB instead of MySQL but you might be under the impression you are still using MySQL. Why? Because if you are like me and you are using cPanel all the branding is still MySQL. I suspect this is actually very usual for anyone using cPanel.

    Here’s my Database panel in cPanel

    MySQL written all over the place even with a registered trademark symbol reinforced with the Dolphin MySQL logo.

    Additionally phpMyAdmin never really mentions much about the database server you are using. And look it up and phpMyAdmin is often described as the MySQL client.

    Am I using MySQL —— NOPE

    Open up phpMyAdmin

    Open the SQL panel and execute the following query.

    SHOW VARIABLES LIKE "%Version%";

    So despite all the logos and branding to the contrary I’m using MariaDB for this blog! Useful to know. I don’t know if it was MariaDB when I signed up in 2014 or not but this blog has been running continuously for 7 years and 7 months (at date of publishing this article) with no issues.

    Kinsta on the differences between MariaDB and MySQL

    I’m happy with being on MariaDB just because I am not mad about Oracle. Turns out I’m not alone in that position. Google / AliBaba and the European Investment Bank are all heavily invested in MariaDB which is probably why so many hosting companies actually run MariaDB in the background. There are big companies on MySQL but MariaDB is not going anywhere and many like me might have been running everything on MariaDB without even knowing it for years!

    MariaDB on Wikipedia

    I note in the Wiki article they state as a difference between MySQL and MariaDB Geographical Function support – not sure that part is true – I have asked Wikipedia to investigate , according to my research there is support via extensions although I have no personal experience of their use.

    Setting up CRON job using cPanel

    Automation is so important these days and the following details how to create a simple cron job using CPANEL to send out a regular email. You can automate anything but I was wanting to use an example that would send me something to show that it was working. So I set up the following as an example to send out an email to myself every 5 minutes.

    See also
    How to Set Up and Run a Cron in CPanel

    I am going to focus on a single method. The above link details how to do it using the command line on a linux server which may be helpful. I am concentrating on using the cpanel interface here.

    Step 1 : Sign into your cpanel administrative panel.

    Step 2 : Look to the Advanced Tab and identify the Cron Jobs Icon

    Entering the Cron Jobs panel you will be presented with the following screen.

    Leave the Update email button…

    Step 3 : Identify the PHP script you wish to run and load it into a directory taking note of where you have noted.
    (You will need to point CRON to run your specific script)

    If you don’t have a script try this one. It uses the php mail function to send out a simple email to whatever target email you want which allows you to

    $to_email = 'targetemail@targetcompany.com';
    $subject = 'Mail sent using a Cron Job Script';
    $message = 'Hello and best wishes';
    $headers = 'From: noreply@yourcompany.com';

    Step 4 : Save the above PHP script somewhere and load it to your server so that taking a note of where you put it.

    Now I would start and set this to run up every 5 minutes just to test that it is working –

    In the common settings there is s drop down which has a range of settings everything from once a minute to once a year select once every 5 minutes.

    Step 6 : Set the command to be run.
    Now this is the only really tricky part to the whole thing. I haven’t found a great deal of documentation on the syntax of the command that you should enter. I found two different syntaxes that seemed to work.

    /usr/local/bin/php /home/youruserid/public_html/cronscripts/testrun.php


    php -q /home/youruserid/public_html/cronscripts/testrun.php

    Then simply hit the Add New Cron Job button and then wait for five minutes – if everything has completed successfully there will an additional line in the cron jobs listing and you will get an email every 5 minutes.

    Happy Cron Jobbing.

    One interesting point in my cpanel host was that you could limit the cron job to the domain which may be necessary as you become more proficient with the kind of jobs that you are wanting to run in your cron job.