Month: November 2023

PHPRunner and MS SQL Azure – Running a Stored Procedure behind a button – With Sweet Alert User Feedback

I have written previously about triggering stored procedures in MS SQL Azure through a PHP Runner generated web front end. This is a vanilla explanation which includes sweet alerts to indicate users updating them on the progress of longer running stored procedures. It also locks the front end preventing users from rushing procedures until the queries have had time to complete.

Firstly navigate to the table you wish to run the stored procedure from in this example it is table T032Email List Screen

Insert a Custom Button here I call it Transform

Next select the button and look to the right for the Edit Button Code button.

You will be presented with the Buttons properties window dialog with three tabs
1=Client Before
2=Server
3=Client After

1) In the Client Before place the following code

Swal.fire({
  icon:"info",
  title: 'Please wait... transforming selected records',
  allowOutsideClick: false,
  allowEscapeKey: false,
  showConfirmButton: false
});

2) On the Server trigger the stored procedure – swap out your stored procedure name.

DB::Exec("EXEC dbo.transformemail");

and on the 3) Client After notify the user once everything has been completed

  Swal.fire({
    icon: "success",
    allowOutsideClick: false,
    allowEscapeKey: false,
    title: "Records Transformed",
    showConfirmButton: false,
    timer: 2000
  });
setTimeout("window.t032emailPage.reload({a:'reload'})", 2000);

PHPRunner – Using Sweet Alerts to give Users confirmation of Save

In PHPRunner as of version 10.51 the sweet alert javascript library is included in PHPRunner generated web applications.

How can we add code to a project to make bespoke adjustments?

Firstly navigate to the form you wish to add a special sweet alert to and insert a Custom Button.

Next navigate to the Events tab in PHPRunner and expand the table or view to which you added the additional button.

Behind the Javascipt OnLoad event add the following code

$('a[id^="saveButton"]').hide(); // Hide button "Save"

And on the Client Before Event of the new button add the following code

Swal.fire({
icon: "success",
title: "Saved",
showConfirmButton: false,
timer: 1000
});
$('a[id^="saveButton"]').click();
return false;

So what does the code do
Javascript OnLoad Event – Hides the real save button
On Click – Triggers the sweet alert success routine and once that is complete triggers the hidden savebutton code.

And more examples and inspiration

Sweet alert 2

And also if you find the Sweet Alert modal size too small add this to your page css:

.swal2-popup { font-size: 1.6rem !important; }

Most of this was from the following Xlinesoft User forum Thread
Thread

MS SQL Azure – Computed Column – Create UTC Datetime value from Datetime & Timezone fields – Daylight Saving

This is not tested at time of writing obtained from ChatGPT 4

Answer
Sure, here’s an example of how you might implement the ConvertToUTC function to handle time zones like ‘London/Europe’. This example uses the AT TIME ZONE clause, which is available in SQL Server 2016 and later:

CREATE FUNCTION dbo.ConvertToUTC (@localDateTime DATETIME, @timeZone NVARCHAR(50))
RETURNS DATETIME
AS
BEGIN
    -- Convert the local datetime to a datetimeoffset value using the specified time zone
    DECLARE @datetimeoffset DATETIMEOFFSET = @localDateTime AT TIME ZONE @timeZone
    -- Convert the datetimeoffset value to UTC
    DECLARE @utcDateTime DATETIME = CONVERT(DATETIME, SWITCHOFFSET(@datetimeoffset, 0))
    RETURN @utcDateTime
END

You can then use this function in a computed column definition:

ALTER TABLE YourTable
ADD UtcDateTime AS dbo.ConvertToUTC(field01, field02)

In this example, field02 should contain time zone names that are valid for the AT TIME ZONE clause. For example, ‘UTC’, ‘Pacific Standard Time’, ‘Central European Standard Time’, etc. For London, you would use ‘GMT Standard Time’.

Please note that this is just a basic example. The actual implementation would depend on how you’re storing time zone information and the specific requirements of your application. It’s also worth noting that handling date and time conversions can be complex due to factors like daylight saving time, so it’s important to thoroughly test your solution to ensure it handles all edge cases correctly.

If you’re frequently performing these types of conversions, you might want to consider storing datetime values in UTC directly in your database. This can simplify your data model and avoid potential issues with time zone conversions.

Question
Does AT TIME ZONE have a default value that will alter depending on whether the UK is under British Summer time?

Answer
Yes, the AT TIME ZONE clause in SQL Server does take daylight saving time into account. When you use a time zone name with this clause, it will automatically adjust the time based on the current daylight saving time rules for that time zone.

For example, if you use ‘GMT Standard Time’ (which is the time zone name for UK time in SQL Server), it will automatically show the correct offset for daylight saving when it is in effect.

Here’s an example:

SELECT GETDATE() AT TIME ZONE 'GMT Standard Time'

This will return the current date and time on the database BUT in my testing does NOT adjust the time to the timezone but will show the correct time zone OFFSET for the date for corrected for daylight saving i.e Greenwich Mean Time (GMT) or British Summer Time (BST), depending on the date.

Please note that the actual time zone names and their daylight saving time rules are based on the time zone data on the server where SQL Server is running. This data is updated through the Windows operating system, so it’s important to keep your servers up-to-date to ensure that the time zone rules are accurate.

And here are what I think are the supported time zones
MS Azure supported time zone values

Post-script
A lot of online editors won’t let you run the At Time Zone function but this one does..

dofactory

Comparing the time to my location and the time where I am I can tell that this is Pacific Standard Time – i.e Western America – California perhaps.

We also note that the time isn’t adjusted to the stated timezone but we do see the offset (see +01:00 in record return) Again this is because per se it doesn’t tell you what the server is set to but by using the sysdatetimeoffset we can correct the server time back to UTC and the adjust for timezone see second code example. The Timezone although included in the time is again be stated.

SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'W. Europe Standard Time' as WEuropeTime, 
SYSDATETIMEOFFSET() AT TIME ZONE 'GMT Standard Time' as GMTStandard

And so there is a direction to timezone switch – In the above we have mainly been switching from UTC to a timezone but the below switches from a timezone to UTC which is what we will need if we are storing the input as a datetime and a separate timezone for each record.

SELECT SWITCHOFFSET(GetDate() AT TIME ZONE 'Pacific Standard Time', '+00:00')

Remember though timezones are held outside SQL Server databases on the server and as such are non deterministic. This is a good demonstration of determinism in practice

Deterministic algorithms are entirely predictable and always produce the same output for the same input.

Non-deterministic algorithms may produce different outputs for the same input due to random events or other factors.

Databases and Time – Time Zones are not Datatypes

When working with time in databases and web applications we should recognise that time zones are NOT a datatype. They represent an important part of a datetime value that links to a datetime value and may represent the time on the server or the time in the browser or some time in a particular country for an attribute of a record. Simply viewing a datetime value individually is incomplete if users are entering data from all over the world. If this is not designed into the schema from the beginning problems may be presented when filtering and ordering records based on datetime.

Does your application have users within the same or different time zones? Is your database hosted in the same or a different time zone?

SQL Azure database servers for instance only allow for a default time zone set to UTC. This means wherever you are in the world if you set a default value on an datetime field new records will record time at UTC. This can present issues where users submit values based on a different localised time.

Default values that are set to GetDate() can be out by a given time offset. Additionally web interfaces which set the datetime value at input may be set by the browser and if the browser is not on UTC that time will use a clock that may be forward or behind UTC. What’s the solution? My suggestion is to record the time zone in a separate field per record to the datetime value and then use a view to convert all datetimes to a common time zone field (probably best UTC). At least that should reduce complexity for users. Developer and users should be aware as this could be confusing when a user inputs a time into a database and then is presented with the UTC value when viewing the value.

It should be noted that Azure Logic Apps are on a server that is not set to UTC but local time (in my case London / Europe) I suspect that whatever local you set will be affect the local time. This led to a situation where in British Summer Time a logic app procedure triggered for 10pm was showing 9pm in the database – the BST offset.

Repeat after me – Time Zones are not Datatypes

And here is a nice site that identifies differences from UTC of timezones based on URL parameters

Time around the world
British Summer Time – 10am UTC

Eastern Seaboard Time – 10am UTC

Sydney Australia – Aussie Eastern Daylight Time – 10am UTC