Category: Zagon

PHPRunner – Pass Value to CSS Reference using Javascript in a pop up

Javascript is very powerful and will easily calculate all sorts of interesting things for you dynamically. In PHPRunner I use the popup windows for nearly every table or view form so I wanted it to work with these.

But with PHPRunner we want to store these in the database. I had a devilish time finding a way of referencing the field with which to copy any Javascript value into. After some lengthy discussion with ChatGPT 4 (via Bing) it suggested that I might try and use the CSS Selector.

I then discovered that I couldn’t seem to identify the name of the CSS Selector.

What I discovered is that I could not see a static CSS Selector reference for any of the fields except those that I had altered the formatting on for example changing the font to Roboto Mono.

So first step choose your target field and then alter it using the PHP page designer and then publish.

What I discovered was after that I could use the inspect item to identify the CSS Selector

On the published application navigate to the form and the field you wish to target for entry right click and select inspect.

Look to the DevTools window (in chrome and you should see in bold the css names of your field

You can then ask ChatGPT the following

Can you parse the input css selector I need from the following string that can be used by javascipt to be passed a value

[data-page="t0017_add"][data-itemid="integrated_edit_field4"][data-page="t0017_add"][data-itemid="integrated_edit_field4"][data-page="t0017_add"][data-itemid="integrated_edit_field4"][data-page="t0017_add"][data-itemid="integrated_edit_field4"] > * > * > input

An element is a part of a webpage. In XML and HTML, an element may contain a data item or a chunk of text or an image, or perhaps nothing. A typical element includes an opening tag with some attributes, enclosed text content, and a closing tag. Elements and tags are not the same things.

More on elements is available here

This can now be used behind a button and away we go… see below

document.querySelector('[data-page="t0017_add"][data-itemid="integrated_edit_field4"] > * > * > input').value = "Password123";

And here are some notes on finding CSS Selectors by ChatGPT4 I am still investigating CSS Selectors there seems to be a black art to understanding their structure and how they can be useful

I also asked chatGPT about child selectors in CSS. In my discussions with chatGPT I have discovered that spaces are important and symbols are important in naming and of course Javascript is case sensitive. The dynamic nature of CSS Selectors and their very specific naming conventions combined with spaces potentially being characters really means you need to be on your toes when you use them.

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.

MariaDB – Add calculated field that shows day name

ALTER TABLE tablename 
ADD COLUMN dayname VARCHAR(20) GENERATED ALWAYS AS (DAYNAME(startdate)) 
STORED;

This query will add a new column called dayname to the tablename table. The column will be of type VARCHAR with a maximum length of 20 characters. The GENERATED ALWAYS clause tells MariaDB to calculate the value of the column each time a row is inserted or updated. The AS keyword specifies the expression used to calculate the value, in this case the DAYNAME function applied to the startdate column. Finally, the STORED keyword tells MariaDB to store the calculated value in the table so that it can be retrieved more efficiently.

Note that the GENERATED ALWAYS and STORED clauses require MariaDB version 5.7.6 or later. If you are using an earlier version of MariaDB, you can still add a calculated field using a trigger or a view.

SQL Server Express – starting to work with Geometry Functions in SQL Server

Firstly I created a database.

This is essentially me trying to implement in SQL Server what I had written about in Postgres here – Using ST_Within in Postgres

Now I create the starting tables.

CREATE TABLE [dbo].[t00001fields](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[fieldname] [varchar](50) NULL,
	[geom] [geometry] NULL

Then

CREATE TABLE [dbo].[t00002plots](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[plotname] [varchar](50) NULL,
	[geom] [geometry] NULL

Now a magic junction table that will identify what plot is within which field when it is run

CREATE VIEW v001FieldPlotJunction AS
SELECT t00002plots.pkid as Plotspkid,t00001fields.pkid as Fieldspkid
FROM
t00002plots,
t00001fields
WHERE
t00002plots.GEOM.STWithin(t00001fields.GEOM) = 1;

and here’s an example of it running on local in SQL Express

And this is what I am seeing when viewed through QGIS = many of the fields are smaller than the plots and so although 1 2 look like they should be in the view because the plot is not WITHIN the field it does not show which is the correct response in this case.

Batch File to split CSV into smaller parts

So I am using CHATGPT regularly now and this is a good example of where it can prove very useful. The bottom bat iteration was obtained from StackOverflow and successfully split a large file into chunks of 2,500. I then realised that each sub file needed to have headers in it to assist in importing the information into a database. I asked CHATGPT to alter the starting file to ensure that headers were included subsequently.

It did it no problem..

@echo off

setlocal ENABLEDELAYEDEXPANSION
REM Edit this value to change the name of the file that needs splitting. Include the extension.

SET BFN=C:\csv\target.csv

REM Edit this value to change the number of lines per file.
SET LPF=2500

REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
SET SFN=SplitFile

REM Do not change beyond this line.

SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
set "header="
for /f "tokens=* usebackq" %%a in ("%BFN%") do (

if not defined header (
set "header=%%a"
echo !header! > %SFN%!FileNum!.%SFX%
) else (
SET /A LineNum+=1
echo %%a >> %SFN%!FileNum!.%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A FileNum+=1
echo !header! > %SFN%!FileNum!.%SFX%
)
)

)

endlocal

Pause

This is the original obtained from Stack Overflow..

@echo off

setlocal ENABLEDELAYEDEXPANSION

REM Edit this value to change the name of the file that needs splitting. Include the extension.
SET BFN=C:\csv\target.csv

REM Edit this value to change the number of lines per file.
SET LPF=2500

REM Edit this value to change the name of each short file. It will be followed by a number indicating where it is in the list.
SET SFN=SplitFile

REM Do not change beyond this line.

SET SFX=%BFN:~-3%
SET /A LineNum=0
SET /A FileNum=1
For /F "delims==" %%l in (%BFN%) Do (
SET /A LineNum+=1
echo %%l >> %SFN%!FileNum!.%SFX%
if !LineNum! EQU !LPF! (
SET /A LineNum=0
SET /A FileNum+=1

)

)

endlocal

Pause

MS Azure – TSQL a simple example using a Common Table Expression (CTE)

A common table expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and exists only for the duration of the query.

I’m finding them useful as you can use them within a view to contain all the references required for that particular view. This I feel makes things more maintainable.

An example of a simple common table expression (CTE) in T-SQL:

WITH CTE_EmployeeSalaries AS
(
    SELECT EmployeeID, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT *
FROM CTE_EmployeeSalaries;

This CTE defines a virtual table named CTE_EmployeeSalaries that contains all employees with a salary greater than 50,000. The CTE is then used to select all rows from this virtual table.

Common table expressions (CTEs) have several advantages:

1.Improved readability: can make complex queries easier to read and understand by breaking them up into smaller, more manageable pieces.
2.Improved maintainability: Because CTEs are self-contained, they can be easily modified or replaced without affecting the rest of the query.
3.Improved performance: In some cases can improve query performance by reducing the need for subqueries.
4.Recursive queries: CTEs can be used to perform recursive queries, which are useful for querying hierarchical data such as an organizational chart or a bill of materials.
5.Temporary results: can be used to store intermediate results that are used multiple times within a single query. This can be more efficient than repeatedly calculating the same intermediate results.
6.Better alternative to view: CTEs can be used as an alternative to views, which can be difficult to modify and are not always optimized for performance.
7.Reusable: CTEs can be defined once and used multiple times within a single query or in multiple queries within the same session.

MS Azure TSQL writing comments

In TSQL, comments can be added to code in two ways:

1.Single-line comments: These start with two hyphens (–) and continue until the end of the line. For example:

-- This is a single-line comment

2.Multi-line comments: These start with a forward slash and an asterisk (/) and continue until the closing asterisk and forward slash (/). For example:

/*
This is a multi-line comment. It can span
multiple lines and is often used to provide
detailed explanations or to comment out large
blocks of code.
*/

When writing comments, it’s important to keep them clear and concise, and to use them to explain why something is being done rather than how it is being done. This helps to make your code more readable and easier to understand for other developers who may be reading it.