Monday 27 December 2021

SQL - Using CASE Statements IN SQL Statements

How To Use IF Statements In Your SET BASED SQL

By Strictly-Software

Now if you are new to SQL you may not know the benefits of the CASE statement. It is basically the way you can implement an IF statement within your SET Based SQL.

I do a lot of work with Horse Racing and I like to rank my horses on a number of variables adding up a "Rating" score by appending values to a default value so that runners with the best score appear at the top.

An example of this would be the simplified cut down version below where I am adding points to a Ratings column that will be displayed in the final results. I used multiple CASE WHEN THEN ELSE END, statements to check various stats a horse may have and if they meet the criteria I append a score to a rolling count e.g

This example uses a couple of CASE statements to show me the horses Last Races Official Rating, but also creates a SCORE using a number of factors which I can then order by easily at the end.

The SQL

SELECT RacePK,RunnerPK,Racedatetime,IsHandicap,CourseName + N' - ' + RaceTime + ' - ' + HorseName + ' @ ' + CurrentOdds as Info,OfficialRating,
		CASE WHEN LastRaceFK>0 THEN (SELECT OfficialRating FROM RACE_RUNNERS WITH (NOLOCK)  WHERE RaceFK=run.LastRaceFK AND HorseNameCountry=run.HorseNameCountry) ELSE 0 END as LastRaceOfficialRating,Class		
		CASE ClassChange WHEN 1 THEN 'UP IN CLASS' WHEN 2 THEN 'DOWN IN CLASS' WHEN 0 THEN 'SAME CLASS' ELSE 'NA' END as ClassChange,
		CASE	WHEN LastRaceFK = 0 THEN 'NA'
			WHEN LastRaceFK > 0 AND (SELECT Class FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) = '' THEN 'NO LAST CLASS INFO' 
			WHEN LastRaceFK > 0 THEN 'LAST RACE CLASS: ' + (SELECT MyClass + ' - ' + RaceName FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) ELSE 'NA' END as LastRaceClass, Going, GoingCode,
		CASE	WHEN LastRaceFK = 0 THEN 'NA'
			WHEN LastRaceFK > 0 THEN 'LAST RACE GOING: ' + (SELECT UPPER(GOINGCODE) FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK)	
			WHEN LastRaceFK > 0 AND (SELECT GOINGCODE FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) = '' THEN 'NO LAST RACE GOING INFO' END as LastRaceGoing,
		LastFinishPos, FORM,  
		Score = CASE WHEN Favourite = 1 THEN 50 
			     WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,2) THEN 35
			     WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,3) THEN 20 
			     WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,4) THEN 10 
			     ELSE 0 END +
			-- 0 = no change, 1 = up in grade, 2 down in grade, 3 = unknown, -1 default value				
			CASE WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' AND (HasMaxOfficialRating = 1 AND Favourite = 1) AND LastFinishPos = 1 THEN 50
			     WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' AND (HasMaxOfficialRating = 1 AND Favourite = 1) THEN 45
		             WHEN ClassChange = 1 AND LastFinishPos = 1 AND (HasMaxOfficialRating = 1 OR Favourite = 1) THEN 40
		             WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' THEN 35
			     WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' THEN 30			     
		             WHEN ClassChange = 1 AND KeyForm LIKE '%L%' AND LastFinishPos BETWEEN 2 AND 4 THEN 27						 		
			     WHEN ClassChange = 1 AND KeyForm LIKE '%L%' THEN 25
			-- down in grade could have won at this grade before
			     WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' AND LastFinishPos = 1 THEN 35 
			     WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' AND LastFinishPos BETWEEN 2 AND 4 THEN 30
			     WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' THEN 27
			     WHEN ClassChange IN(0,1) THEN 5
			     WHEN ClassChange = 2 THEN 3
			     WHEN ClassChange IN(3,-1) THEN - 5
			     ELSE 0
			     END + 
			-- new IF for no of runs the more win percentage the better 
			CASE WHEN Runs > 5 AND WinPerc = 0 THEN -20   
			     WHEN Runs > 5 AND WinPerc = 100 THEN 100
			     WHEN Runs > 2 AND WinPerc = 100 THEN 50
			     WHEN Runs > 5 AND PlacePerc = 100 THEN 60
			     WHEN Runs > 2 AND PlacePerc = 100 THEN 30
			     WHEN Runs > 5 AND PlacePerc > 70 THEN 50
			     WHEN Runs > 2 AND PlacePerc > 70 THEN 30
			     WHEN Runs > 2 AND LosePerc = 100 THEN -50
			     WHEN Runs > 5 AND LosePerc > 60 THEN -60
			     WHEN Runs = 0 OR LOSEPERC > 70 THEN -50 ELSE -10 END +
			 -- Official Rating higher the better
			CASE WHEN OfficialRating > 170 THEN 300
			     WHEN OfficialRating > 160 THEN 275
			     WHEN OfficialRating > 150 THEN 250
			     WHEN OfficialRating > 125 THEN 200
			     WHEN OfficialRating > 110 THEN 175
			     WHEN OfficialRating > 105 THEN 150
			     WHEN OfficialRating > 105 AND IsHandicap = 1 THEN 100
			     WHEN HasMaxOfficialRating = 1 THEN 50
			     ELSE 10 END
FROM	RACES as ra  with (nolock)
JOIN	RACE_RUNNERS as run with (nolock)  
	ON	ra.RacePK = run.RaceFK 
JOIN	COURSES as c  with (nolock) 
	ON	c.CoursePK = r.CourseFK
WHERE	RacePK = 269330 -- current race I am looking at


The Results

Below are the results from that query outputted in MS SQL Management Studio.




Another Example


This is where I am using a CASE WHEN THEN ELSE END, statement in the ORDER By clause to show the output of a race card. 

The race could be finished and in that case I would want the ORDER of the results to be from no 1 (winner) to the horse that finished last. I would also want any runners not finishing who would get a  finish position of 0 appearing last. 

I would also want any non-runners, horses who were pulled out of the event, before the race to appear at the bottom of the results.

Also with racing, in the flat, horses start the race in stalls, so if it is a flat race I want to show the stall they are in, if the race is a jump or NHF (Bumper or National Hunt Flat Race), then they use no stalls so I would not order by stall no.

The SQL

SELECT	RunnerPK,Racename,raceType,HorseName,Jockey,NonRunner,FinalOdds,CurrentOdds,Draw,Finishposition,favourite,officialrating
FROM	RACE_RUNNERS as run  WITH (nolock)
JOIN	RACES as ra  WITH (nolock)
	ON ra.RacePK=run.RaceFK
WHERE	RacePK=270423
ORDER BY CASE WHEN nonrunner=1 THEN 150
	      WHEN EXISTS(select top(1) runnerPK from race_runners as r1 WITH (nolock) where r1.Racefk=ra.RacePK and finishposition>0) THEN
		   CASE WHEN finishposition=0 then 100 ELSE finishposition END 
	      ELSE CASE when ra.racetype IN('NH','B') THEN horsename else draw END
	 END 

The Results

Below are the results from that query outputted in MS SQL Management Studio.




As I hope you can see the use of a CASE statement either in a SELECT clause or an ORDER BY is very useful for adding an IF statement into a SET based query. 

You can also use them in your JOINS to help decide which table you actually JOIN onto, for example if I had two tables, one for RACES that had not been run yet, and one for RACES that had finished I could add a CASE statement into the JOIN so that if there existed a runner with a finish position or a RACE with a finishing time I would join to the RACE_RESULTS table and for RACES not run yet I would join to the RACE_CARD table (if they were named like that for races before running and after a result was in).

There are actually lots of ways you can use CASE statements in your SQL and it may help you to be able to CONVERT looping code or procedural code with actual IF statements in a ROW by ROW CURSOR or LOOP into a SET BASED SQL statement that will perform a lot quicker.

Please post your favourite CASE statement usages in the comments section.


By Strictly-Software

Tuesday 7 December 2021

Test Your Anti Virus Software

A Quick Way To Test If Your Computers Anti-Virus Software Is Working

By Strictly-Software

This is a quick and quite a simple way of testing if your PC is protected properly from files that may contain viruses or malware. It has apparently been around for a long time but I only stumbled across it tonight and when I tested it against my AV tools I was surprised at the results.

The test is called a Eicar test file is a file, developed by the EICAR organization, that is used in testing anti-virus scanners for their integrity in detecting viruses. The actual file is simply a text file of either 68 or 70 bytes that can be created using any text editing program like Notepad. It is not actually a virus but the characters within the file should flag a hit in most Anti Virus tools making them think it is one.

With so many free anti-malware / virus software out there to use it can be a case that you have multiple apps installed to protect your computer as I have found that some AV products will detect some malware, and others malware that the previous products didn't find.

Therefore I have found that having more than one AV / Anti Malware product on my computer is the best policy just in case something is found by old school virus definitions or by heuristic behaviour detection which other AV products use.

I read something the other day about Bitcoin miners hacking into a quantum computer at some US university to leverage it's super power to mine coins. Obviously their standard AV software didn't detect whatever program was running so they actually came up with a new solution which I really don't understand why home PC users couldn't use as well.

It's a pretty simple concept in that the Quantum Computer just like any computer should only be running certain "allowed" programs.  System programs that operate the machine and then programs that have been installed on the machine by admin. All the new anti-virus tool did was hold a list of every program allowed to run on the machine, and then it would constantly scan the Task Manager or equivalent tool and if it found any program executing that wasn't on the "allowed" list it would terminate the process and quarantine or remove the executable.

To me this sounds like quite a simple solution to malware detection and a lot better than constantly updating AV software with definition lists. How hard would it be to create a program that logged all tasks and services that were OS-based, and then add to this whitelist any programs that were properly installed by an administrator. You could run the setup of this program in safe mode, or on a clean install so that it stored only safe OS programs in it's a whitelist and then when a new program was installed it would be added to the same list.

Then the AV tool would just run alongside your Task Manager looking for any process not in it's white list and flag it when found. The user could then inspect the properties and location of the file and decide whether it was legitimate or illegal and either add it to the white list or have it removed. I might even have a crack at making something like this.

Anyway this is about testing your current system to see if your AV software will detect the following file as malware which it should. I don't know what this does if anything, or whether it just matches a virus definition from some time back but I ran the test earlier and you can see my results below.

Run Your Own Anti Virus Test

1. Open Notepad -> Run -> Notepad

2. Type the following text into the file: 
X5O!P%@AP[4\PZX54(P^)7CC)7}$EICAR-STANDARD-ANTIVIRUS-TEST-FILE!$H+H*

3. Save the file somewhere on your PC as an executable, e.g. I saved my file in Documents as AVTest.exe

4. If you have multiple AV tools in your context menu. You can right click above the file and test each one to see if they detect the file as malware e.g


5. However if you are on Windows and haven't replaced Windows Defender with a premium AV tool it should always be running and detect threats as they come. It does on my PC despite me having other AV tools. As soon as I save the AVtest.exe file on my machine Windows Defender pops up a message saying it has detected malware on my machine in the top right corner and in the bottom right corner another message tells me that I don't need to do anything as "Detected threats are being cleaned" and within a minute the file disappears from my Documents folder. Therefore if you want to test other AV software you need to have them up and running and ready so you can do a quick single file virus test from your context menu before Windows Defender removes it.



Notice the two Windows Defender messages in the right hand corners of the screen when I save the file.

So I have a variety of AV tools on my system but keep Windows Defender running at all times. These were the results I got from my Context Menu test. Please let me know of any other free AV tools that either detect or DON'T detect this as a virus.

-Windows Defender: It detected the file as a virus as soon as I saved it. Showed me warning messages and said it would be removing it. Within a minute or so the file had gone.
-Malwarebytes Anti-Malware: Running a scan on this file from the context menu it detected it and quarantined it for me but a full scan did not find it.
-Norton PRO: used instead of Windows Defender on my other laptop, BT has changed from McAfee to Norton, and you get a pro version of the AV software for free from BT, I didn't even realise until they told me to update from McAfee to Norton, anyway - DID DETECT AND REMOVE IT a few seconds after I saved the file on my laptop.
-McAfee PRO: used  instead of Windows Defender as my main virus checker - DID NOT DETECT IT AS A VIRUS
-Emsisoft Security Center: - DID NOT DETECT IT AS A VIRUS.
-SuperAntiSpyware (Free Edition): - DID NOT DETECT IT AS A VIRUS

Now I cannot be a 100% sure that the reason that the last two apps did NOT detect it as a virus was that Windows Defender had somehow put a lock on the file so that they could not detect it or something however the free version of Malware Anti-Malware did detect it when running a single file scan from the right-click context menu.

Anyway, this is a good test just to make sure your computer is detecting virus files and it only takes a few minutes to copy that string of letters and place them in a text file, save it, and see which AV tool shoots up a message that a virus has been detected.

It's an old trick so I don't claim to be the first to know about it but I was just surprised at the results, it is always good to know your machine is protected and this is a very simple way without installing an actual virus.

Let me know of any other FREE AV / Anti Malware tools that don't detect it as malware in the comments.

By Strictly-Software

Thursday 28 October 2021

Need Help Editing Your Blogger Blog?

Do You Need Help Editing Your Blogger Site

By Strictly-Software

After my last post about editing blogger blogs using JavaScript on the client side due to the lack of functionality in the back end of the admin side of blogger I was contacted by a couple of people who needed help on their blogs.

I have implemented features such as the following using the JavaScript/Text widgets that you can add to your layout for adding CSS Styles and JavaScript code to do the following so far:

1. Added the code I use to turn my SEO optimisation on or off. In my sidebar there is a section where you can turn bolded sentences or words off and on again. Some people like having the bold on as it holds the key information in a blog piece and helps with skim reading. However as it is loaded server side to aid SEO, I can turn it off with client side JavaScript either on each page or by using cookies to remember your choice when doing so that when you come back the next time it will have remembered your choice.

2. Used JavaScript to totally change a blogs colour scheme from a default light grey to a dark blue and white scheme. Similar to using the JavaScript widgets but with a bit more code and use of the querySelector and querySelectorAll methods and then looping through matching elements to change their colours using .style.backgroundColor & .style.color methods.

3. Again changed the layout of the blog by expanding buttons that were too small on search bars, adding in custom search engines that only search certain sites, usually related blogs or sites about the same content that the blog in question was about.

4. Ensured Google Adsense was enabled and turned on in the best way to increase revenue streams from visitors to the site.

5. For a blog that was hotlinking to a site that had no SSL anymore to display images. I obtained the original image and uploaded into Blogger so that it was stored IN the blog and referenced from there. People are often put off sites with no HTTPS URL such as my own, purely due to Browser warning pages that believe every site nowadays needs to have an SSL.

Well if you are not actually buying anything or logging into the site or passing personal information to it e.g it's just a blog or site with text and pictures you are reading then there is really no danger at all from going to the uncertified site. 

Just click Advanced and then "Go To Site Anyway" or the comparable message. You can check this out by going to my own site www.strictly-software.com, however I have found the if you use BRAVE as your browser it or either one of the following extensions remembers that you have gone there and prevents the same MalwareBytes Browser Guardwarning page from showing: or DuckDuckGo Privacy Essentials.

You can check 2 of these sites out here:



And check out the changes and features for yourself.

If you are interested in getting some widgets added to your own Blogger site then contact me by email and we can work out a price for the work. There is a lot we can do client side with JavaScript to replace the lack of functionality the old Blogger system had for changing layout and colours etc on the page loading.


By Strictly-Software

Thursday 21 October 2021

Changing Blogger With JavaScript

Using JavaScript To Edit Blogger Layout

By Strictly-Software

If you are using Googles blogger to output content then there will often be times where you want to edit the layout depending on the page you are on, or the content that is shown.

I recently had a site where I needed to edit the content in multiple places depending on whether a GoFundMe post was being shown on the homepage, or it's own page. As I also had a mini GoFundMe logo in the sidebar I didn't want both images to be shown at the same time.

I also had a Custom Google Search Bar which only searched a certain number of websites related to the content on the blog.

Therefore the logic I needed was to.

1. Once the page had loaded I needed to ensure if a post of GoFundMe was shown on the page then I needed to remove the sidebar image in ID #Image4.
2. Just doing a test for #Image4 doesn't work as the widgets change names server side before output therefore I need to list out the pages / URLS that the post appears on where I know that the image in the side bar and the main post image both exist.
3. I need to add text to a special Google Search Bar when the site has loaded, sometimes this can be slow depending on Google's Servers, so therefore I decided to put it in a window.onload event to ensure all other images and external items had loaded before I edited the input bar.
4. On focus of the search bar I need to clear it to allow people to put their own search terms.
5. On leaving the search bar if it's empty then I need to put the default text back in, a simple onblur, onfocus toggle.

As the widget I added (an HTML/JavaScript widget), was at the bottom of the page the likelihood of the code running after these items had loaded was high so the chances of the code running before the search bar being loaded was low. 

However on the chance it did run, the window.onload event unlike DOMLoad events ensure that all external items have loaded. If I tried a DOMLoad event there was a small chance that the code would run before the items it was affecting had loaded.

Therefore to test that the page was showing a post with the GoFundMe on it and therefore removing a server side loaded item from the page I used Regular Expressions

I looked at all the places the pages loaded this post and they were the homepage, the homepage for mobile or non mobile sites, with or without the Google # link for the search bar tab being in the URL, plus certain labels that only this post appeared on.

The Regular Expression I came up with that worked using my test page I made in HTML & JS that worked was this.
^https:\/\/sitename.blogspot.com\/?(\??(m=\d)?(#gsc.tab=\d+)?|\S+?GoFundMe\S+?|\S+?\/(?:Donate|Donations|GoFundMe)\S+?)?$

This expression uses the document.location.href value to run on and is anchored from the start to end of input with ^ and $.

It always contains the main domain in the URL and I have made the ending / optional with a ? e.g escaped with a forward slash first and putting a question mark after it e.g \/?.

The next part is whether or not a querystring ? exists which is why there is an escaped question mark first to denote a real question mark, followed by another one to say it "May or may not be there". 

The next part is a check to see if there has been a toggle used on the site to change from a mobile view to a website view. They use a simple querystring value for this m=0 (website), m=1 (mobile), so I have (m=\d) which denotes m=(any digit), just incase they change or append digits.

This is the 1st part of an OR section wrapped in a group. I could have put ?: at the start of the group to say "do not store", but as I am not back referencing or using the group in replacements then I didn't bother.

The whole OR is broken down into this logic.

The logic contained within the OR (expression|expression|expression) is the equivalent to this long explanation:

(\??[optional back slash](m=\d)?[optional mobile/website URL toggle to any digit](#gsc.tab=\d+)[optional anchor that denotes Google has loaded up its search bar code as if you are quick you can catch a document.location.href value without this on it]. Then a wrap around the main post called GoFundMe with \S+? which makes it look for non space characters 1 or more times before or after the words GoFundMe which appear on the posts page in the middle of the URL e.g https://sitename.blogspot.com/2021/10/httpssitename.blogspot.comGoFundMe.html.html?m=0#gsc.tab=0 and then finally a look for the 3 labels that are used to find the page and appear in the URL like so https://sitename.blogspot.com/search/label/Donations?m=0 (as you can see when I copied the URL the page hadn't loaded in the Google Search Bar which is why there is no #gsc.tab=0 in the URL) so this is why I have made that part optional as well.)

So that regular expression handles the following URL's whether or not the Google code has loaded or there is code to force it show a mobile site or website version of the blog.

https://sitename.blogspot.com
https://sitename.blogspot.com/
https://sitename.blogspot.com/?
https://sitename.blogspot.com/?m=0 or https://sitename.blogspot.com/?m=1 
https://sitename.blogspot.com/#gsc.tab=0
https://sitename.blogspot.com/?m=1#gsc.tab=0
https://sitename.blogspot.com/2021/10/httpssitename.blogspot.comGoFundMe.html.html#gsc.tab=0

I made a quick function call to querySelector so that it is short, I always used G when it was document.getElementById(val), so I used it again as to me it means GET G(x) = GetMe(x) a reference to the small framework I made which is on the blog somewhere e.g

G = function(v){return document.querySelector(v)};

Then I can reference objects by class, selector or ID easily with a one letter function e.g

var el=G(".gsc-search-button > input");
G("#Image4").remove();

The final code I inserted into the JavaScript/HTML widget was this.

<script type="text/javascript">
G = function(v){return document.querySelector(v)};
window.addEventListener("load", function() 
{
	if(G("#gsc-i-id1")){
		G("#gsc-i-id1").value = "Search for similar articles here";
	}
	G("#gsc-i-id1").addEventListener("focus",function(){
		G("#gsc-i-id1").value="";
	});
	G("#gsc-i-id1").addEventListener("blur",function(){
		if(G("#gsc-i-id1").value==""){
			G("#gsc-i-id1").value = "Search for similar articles here";
		}
	});	
});
// As these elements are loaded server side they will exist when any JavaScript gets to run so no need to put inside the Window.Onload event
var exp = new RegExp(/^https:\/\/sitename.blogspot.com\/?(\??(m=\d+)?(#gsc.tab=\d+)?|\S+?GoFundMe\S+?|\S+?\/(?:Donate|Donations|GoFundMe)\S+?)?$/,"gi");
if(exp.test(document.location.href)){
	G("#Image4").remove();
}
// search button never appears wide enough to say "Search" so expand
var el=G(".gsc-search-button > input"); // Use selector to get child input of class
gsc-search-button which is used numerous times
if(el){el.style.width="60px";} // just a safety test to ensure it's there - which it should be
</script>

It works and I am happy to see no double GoFundMe images on the page and the focus/blur code working fine.

It is just an example of using JavaScript to modify the DOM when you cannot use Server Side code to initially output it.

© 2021 By Strictly-Software

Friday 6 August 2021

Fix for Wifi Adapter not working - no WiFi

A WiFi Fix Without Rebooting PC

By Strictly Software

I just woke up again on my laptop at 5am, and the keyboard had all changed, I noticed as I went onto YouTube to post a video onto Twitter about the US Government wanting to spy on everyone's text messages and stop any that contain mis-information. 

Now if the US does it then you can be sure the UK GOV will follow as we are already halfway down that slippery wedge towards dystopia. The UK is already becoming a police state using COVID as the cover to implement draconian laws as everyone is worrying about this or that variant and whether or not to get a vaccine just to get a passport that will control your social life, and allow total monitoring of your activities by the Government. 

You can watch the video below about the invasion of US citizens privacy here. What happens in the US usually comes to the UK soon after but when it comes to mass surveillance the NSA like to use GCHQ to test their Prism algorithms and mass home population surveillance techniques out on 1st before using it back home. 

Why? Well because we don't have a real written down constitution or Bill of Rights that protect citizen freedoms is the main reason. The 2nd is our constant desire to be bed buddies with the US, in our "Special Relationship", and keep them sweet by being useful to them, hoping for a share in their data gathering techniques.


I noticed the keyboard change when I went to put a hash tag in and got a | pipe instead and the " quotes were an @ sign. So I did the quick fix I outlined from the other night > "Quick Fix To Keyboard Layout Change"

However I was trying to tweet this out from YouTube to Twitter. Twitters site was up, and I kept pressing "Tweet" but it kept responding with a "There is a problem, try again" message after 20 seconds of non activity.

It was obviously using AJAX as the page didn't refresh, but a quick ping to Twitter.com came back with no server response. It was then I noticed that somehow my WiFi had gone, saying my connection to my router was weak.

Sites that use AJAX for everything, especially posting, should really be able to tell if you are online or not as the reason for a post not appearing

Rather than freaking out someone as the site is still on the screen, and most of the API is in JavaScript so you can still hit buttons and get the impression you are online. There must be a load of ways that after 3 failed post attempts or even 1, that an AJAX API could tell you that you were no longer online in a message.

A method that when you press the button sees if you can remotely access a site that is always up, for instance, would be easy. Then the error message could notify you that you are offline. If they just did a FORM submission when posting Tweets instead of using AJAX you would instantly know anyway from the "No Internet Access" page your web browser would bring up.

So once I had found out that I could not PING Twitter, I checked my Wi-Fi status and it said I had a weak connection to my router. 

Now before I did the keyboard fix, I was online and everything was okay, so I don't know if somehow that quick hot key fix could somehow cause your Wi-Fi adapter to fail, but the two actions seem causally linked. 

So I went to the built in Windows "Troubleshoot Problems" wizard you get when you click on your WiFi symbol in your Desk Tray to run a diagnostic test that clears the DNS, resets your router and adapter and then tries to ping an outside site to see if you have Internet connectivity restored again or a problem accessing the Internet.



I was getting back 3 error messages saying I was offline, my RealTek Wi-Fi adapter was not working, and a default Wi-Fi adapter message, The wizards cure for the problem was to use Ethernet cables but I am not linking loads of cables together just to get round this simple problem.

Usually at first I just try to turn Flight Mode on/off and see if that changes the number of Wi-Fi routers that appear in my list

If it doesn't and I turn Wi-Fi on/off and that still doesn't show my router then one way to fix it is to just log out and log back in again - or reboot if you want to.

However a quicker fix I have found to this commonly occurring RealTek (HP) laptop Wi-Fi adapter problem is to first go to the Device Manager app, under the Windows key, bottom left corner.



I then just go to the Network Adapters section and a load will pop up, if the RealTek Wireless LAN is out of action, usually a red cross will appear next to it. 

In this screenshot I had already fixed it so I wasn't going to try and break it again on purpose just to get a real screenshot. However a red cross next to the adapter having problems is usually the sign you need to double click that adapter and open the menu for it.


I usually try both the "Update Driver Software" and "Scan for hardware changes" options to see which one fixes it but usually one does. It is a lot easier than having to save all your work, remember which files were open, and reboot your laptop.

After fixing the adapter in Device Manager I always run another diagnostic test to ensure there is no other problems but usually my Wi-Fi is instantly restored as the router comes back online and the adapter connects to it.

I always finish off with a quick ping to the site I was on to ensure even though I am online they are too but you can easily skip this step.


And hey ho, I am back online, not banned from Twitter which was on of my 1st thoughts when they couldn't post a Tweet and give me an "offline" status error message. 

However that is the problem with sites that are full of API Jizz, they use it so much they cannot even show you the simplest message at times when all you need to know is that you are offline.

I hope the fix for the keyboard change is not related to the Wi-Fi adapter change but it could be somehow, it's just odd how I was online with a wrong keyboard layout, then I did the quick fix to reset it and then became offline. 

If anyone knows any reason the two might be connected please let me know.

Thanks.....

By Strictly Software

© 2021 Strictly Software

Saturday 24 July 2021

Do Not Be Scared!!!

Main Site And Tools Still Running For Now


By Strictly-Software


Just to let you know that the main site with all the tools such as the:

  • Best JavaScript HTML Encoder & Decoder available, handles double encoding, numeric or entity.
  • Depacking packed code, even if it's been packed multiple times and reformatting it to a readable state.
  • My top WordPress Strictly-AutoTags plugin (Free/Professional), which gives you automated #SEO, deep-linking, and automatic tagging of important words with no 3rd party lookups. No need to check a list of names, if John Smith carries out a wicked crime tonight that is in articles you post automatically then it will find the word and bold it to aid SEO.
  • My top WordPress Strictly-TweetBot plugin, that allows you to tweet to multiple accounts, only if certain words are found in an article OR NOT, using different hash tags made up from either #posttags #categories or #default tags. The professional version has a way of caching the page first before Tweeting so that you are not overwhelmed by any Twitter Rush when you Tweet. Timers can be placed between each Tweet out as well.
  • Lots of free scripts and code that fix holes in frameworks like jQuery.
  • SQL Performance scripts and tuning procedures.
  • Plus lots lots more....
Is still working and you CAN STILL ACCESS IT......

All that has happened is that I can no longer can access the server, due to new security on the site that hosts it. They have added an extra layer of security in which sends an email out to an address I have no access to requesting the code they sent out to be entered on their site to get in. Therefore I cannot get into the servers site, to then use their Java applet to get into the server to add my ever changing IPv4 address to the firewall.

Therefore when you try and go to www.strictly-software.com you will probably see some sort of warning message from your browser like the one shown in the picture below. It will say the site is not secure and hackers maybe trying to steal your data - that is rubbish. 

All that has happened is that due to not being able to renew my SSL certificate, the site is no longer https, no-one is trying to steal your data. I just have not been able to put an SSL on my webserver.

So ignore the message, hit "Advanced", then you should see a "Proceed To Site" link underneath, click that and you can still access the site for now.

Here are two examples, one from Brave/Chrome, the other Opera.

Chrome trying to block access to the site




Opera trying to block access, click the bottom link to get to the site



So no-one is trying to hack you or steal your information, just go to "Advanced" and then click the "Proceed to www.strictly-software.com" link.

All I would suggest is if you are going to buy one of my top WordPress plugins that you do NOT use a PUBLIC WIFI SPOT. 

Do it at home, use a VPN if you can, but do NOT do it at as you are walking down the street being connected and re-connected to various BT public WIFI hotspots

I am sure 99% of them are safe as the users have no technical knowledge to set up their own DNS server and take you somewhere else other than my site, but just to be safe do it at HOME or on a PRIVATE network.

Hope this helps anyone trying to get to the main site.



By Strictly-Software


© 2021 Strictly-Software

Wednesday 21 July 2021

Making A Super Trim Function

Using Regular Expressions To Make a SuperTrim() Function


By Strictly-Software

How many times has there been when you have two bits of text that you have extracted from various websites, or feeds or even databases and tried to compare them but they would not match?

I know I wrote a little example of when two different ASCII space characters are used within SQL the other day and how to check and remove them to make a match but what about all the various ways you can HTML Encode spaces like &nbsp; &#32; and &#x20; plus others that make up a CrLF or just a Cr or Lf, a bit like the VbCrLf constant for a carriage return and line feed, either using Environment.NewLine or constants that hold values for \r \n and also maybe a tab \t.

All these are spaces that need removing and with a special function that uses regular expressions they can all easily be removed .

I use this function in MS SQL with a CLR C# UDF as well as Extending C# projects with a new SuperTrim() method like so:

public static string SuperTrim(this string value)
{
    string newval = "";

    // match each type of space from start of input up to a word character that may or may not have spaces in between
    // e.g a sentence like Hello There John and then removes the same space characters to the right to the end of sentence.
    string re = @"(^(?:&nbsp;|&#32;|&#x20;|\s|\t|\r|\n)+?)(\w+[\s\S]+?\w+)((?:&nbsp;|&#32;|&#x20;|\s|\t|\r|\n)+?$)";
    Regex regex = new Regex(re, RegexOptions.Compiled | RegexOptions.IgnoreCase);

    newval = regex.Replace(value, ""); // replace each space HTML char with nothing

    return newval;
}


It is pretty easy enough to create yourself a test page in HTML using JavaScript with a couple of textarea input boxes for the test value containing encoded spaces a button to run a JS function that runs the regex as seen in the C# example and then outputs the result in another box. 

The regular expression is interchangeable between languages, that's what I love about Regular Expressions, they can be tested and played about with on a simple HTML page with JavaScript and then one the expression works you can easily move it into whatever language you are working in e.g C# or PHP.

For example this encoded text:

&nbsp;  &#x20;&#x20; Rob Reid &#x20;&#x20;&#x20;   

Then after running the regular expression or string newValue = EncodedValue.SuperTrim() method in C# or JavaScript you should get this value with no encoded characters left.
Rob Reid

I find extending whatever language I am writing in to include a SuperTrim() function very handy. If you were handling URL's you might want to remove %20 and the + sign, you can always add more or less into the expression depending on your needs of course like values for nulls or \v for vertical tabs depending on the content you are handling.


By Strictly-Software

© 2021 Strictly-Software

Thursday 10 June 2021

Fallen asleep at your laptop and now the keyboard has changed letters?

Fixing Keyboard Letter Changes

By Strictly-Software

If you anything like me then you may often move from room to room just to check on a program's running status or to run a query and then the next thing you know it's 3 hours later and you find that you have been asleep at your desk.

You go to start writing something, maybe a Facebook post with a hash tag but when you hit the hash # key you find that a back slash comes up \ or maybe you go to write an email and when you hit the at sign @ you find that double " quotes come up instead.

The first thing that usually crosses your mind is that the geographic location has changed from UK to US and you delve into Control Panel only to find that everything is still UK based, keyboard layout, special characters, time, the lot.

Well something you should try, and I never knew this until it happened to me tonight was that a common cause of this problem is to have accidentally hit both the SHIFT KEY and the ALT key at the same time.

Try it, go into Notepad or Editplus or any editor, and hit both those keys at the same time then hit @ or " or # etc and see what letter actually appears.

It's an easy fix, but if you don't know the solution and think that it's buried away somewhere in the Control Panel Location Settings somewhere then you could waste a lot of time scratching your head and wondering what went wrong.

So if you ever fall asleep at your desk / laptop like I often do and wake to find the keyboard is all messed up then try the simple fix first, it might save you a lot of time!

By Strictly-Software

© 2021 Strictly-Software

Monday 7 June 2021

A little piece of SQL and a Regular Expression that saves me a hell of time

Betting, SQL and Regular Expressions

By Strictly-Software

Following on from the previous post, now that I am a professional trader on Betfair, or rather a bettor with some good luck, I managed to pick the 33/1 winner of the Derby out on Saturday somehow, I am always working on my code and my Automatic betting BOT which can TRADE for me on the Betfair Exchange.

However as I also run a Facebook page where I post up free tips from myself, and international tipster friend and other "pro" tipsters > https://www.facebook.com/ukhorseracingtipster/ I have to work on my social media quite a lot.

One of the things I had to do every night was get up at 12am to set my BOT running to import the days race cards, runners, prices and related statistics that help my systems pick out horses to bet on, and therefore to post up to tip.

However one of the most annoying jobs I found myself repeating was every night having to go to SportingLife.com and the daily race card page, copying out UK and Irish races, reformatting it and then adding it in to one of the 1st posts of the day.

Now I have tweaked the import process so I can import races from tomorrow, or 2 days away whenever I like rather than waiting until midnight just to get the current days races. I can now insert race cards for days ahead whenever I like and just concentrate on the betting and tipping. Running the BOT just to get up to date prices etc.

The post would always start like this....

Monday's racing comes from Gowran Park, Leicester, Lingfield Park, Listowel, Pontefract and Windsor.

Which means re-ordering the copied text into alphabetically listed race courses, adding in the day of the week and of course changing the last course name so it doesn't precede a comma but the word "and x" and have a full stop after it.

Not much work you might think to yourself. Well no not really, fun with refreshing pages that don't have data and are stuck in some cyclical mode, but it can take up to 20 minutes.

Therefore today I wrote a small piece of SQL that can work on any days data already imported to format this sentence correctly. I could have done it without a regular expression but then where would the fun be in that?

So here is the code:
DECLARE @Courses varchar(255),@Message varchar(255), @day varchar(15), @racedate date, @Days int
SELECT	@Days = 0 -- how many days into the future do you want data for - put 0 for today
SELECT  @Racedate = CAST(DATEADD(DAY,@Days,GETDATE()) as DATE) -- convert GETDATE() (todays datetime) into a DATE format
SELECT  @Day = DATENAME(weekday, @Racedate) -- get the current weekday for the date above
SELECT	@Courses = COALESCE(@Courses + ', ',' ') +  CourseName -- build up a CSV of courses
FROM	RACES as ra with (nolock)
JOIN	COURSES as c with (nolock)
	ON	ra.CourseFK=c.CoursePK
WHERE	Abandoned=0
	AND Racedate = @Racedate
GROUP BY CourseName
ORDER BY CourseName 
-- get the last course in the list, I could have used a UDF with comma counting but that would mean replicating the above code, easier in a regex
SELECT @Courses = [dbo].[udf_SQLRegExReplace]('(^[\s\S]+?)(?:, ([^,$]+?$))',@Courses,'$1 and $2')
SELECT @Message = @Day + '''s racing comes from ' + LTRIM(@Courses) + '.'
SELECT @Message


If you want to know what the regular expression below does:
SELECT @Courses = [dbo].[udf_SQLRegExReplace]('(^[\s\S]+?)(?:, ([^,$]+?$))',@Courses,'$1 and $2')


Then watch out for hopefully a soon coming Amazon book I am writing on getting started with regular expressions I know there are loads out there but for me personally I learn by doing things not reading things and this is what customer type the book is aimed at

It gets you writing regular expressions straight away and then working out what they do rather than just listing all the possible flags, identifiers and other boring stuff that a lot of reference books contain.

If you have a quicker RegEx for doing the changing of the last word etc then please post it in the comment section. I am always willing to look at other ways of doing things.

If you are into betting then always check out my facebook page for UK, Irish, French and International tipshttps://www.facebook.com/ukhorseracingtipster/



By Strictly-Software

© 2021 Strictly-Software

Sunday 25 April 2021

Solving an issue where you cannot tell the difference between the two conflicting words

Weird Issue - Two Names That Look The Same But Aren't

By Strictly-software 

I have a table of Jockeys that returns me all jockeys from a RACE_RUNNERS table but I had a problem which was I kept getting one jockey returned twice in the output. 

I couldn't work out why this would be, and only for one jockey, and jumped straight to collations. The column collation is set to DB default and when I do a compare using the same collation on each value they still don't match. e.g

-- Column in dbo.RACE_RUNNERS table is 

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation'; 

DB Collation = SQL_Latin1_General_CP1_CI_AS

SELECT	Jockey COLLATE SQL_Latin1_General_CP1_CI_AS,'JOCKEY',COUNT(RaceFK)
FROM	dbo.RACE_RUNNERS as run with (nolock)
JOIN	dbo.RACES as r with (nolock)
    ON	r.RacePK = run.RaceFK
WHERE	Jockey IN('Mr P W Mullins','Mr P W Mullins')
	AND Racedatetime > '2021-JAN-01'		
GROUP BY Jockey 
ORDER BY Jockey COLLATE SQL_Latin1_General_CP1_CI_AS

/*
Returns two records for the jockey that look alike
Mr W P Mullins
Mr W P Mullins
*/

-- do some basic comparison tests with each value

IF LTRIM(RTRIM('Mr P W Mullins')) = LTRIM(RTRIM('Mr P W Mullins'))
  PRINT 'MATCH'
ELSE
  PRINT 'NO MATCH'

--NO MATCH

IF LTRIM(RTRIM('Mr P W Mullins')) COLLATE SQL_Latin1_General_CP1_CI_AS = LTRIM(RTRIM('Mr P W Mullins')) COLLATE SQL_Latin1_General_CP1_CI_AS
  PRINT 'MATCH'
ELSE
  PRINT 'NO MATCH' 

--NO MATCH


So I was slightly confused and thinking about deleting the 2nd record for MR P W MULLINS from the table or changing his name first to the MR P W NULLINS with most results. 

Then it dawned on me. Importing data often leads to characters that you cannot see the difference in but in SQL will appear different. So I did a simple test by removing all spaces in the IF statement comparison e.g
IF 'MrPWMullins' = 'MrPWMullins'
  PRINT 'MATCH'
ELSE
  PRINT 'NO MATCH'

-- And the result was
MATCH
So I just checked each space character in the two words with ASCII() function and found that the last space between W and Mullins was different from each other.
-- match the space between W and Mullins as it seems to be different
SELECT ASCII(' ') -- CHAR 160 another type of SPACE
SELECT ASCII(' ') -- CHAR 32 ASCII SPACE

160
32

Outputting both character numbers with CHAR in a select statement just gave me two columns of spaces. No way to tell the difference by sight, and it only occurred on one jockey for some reason. 

Therefore I did an update on the main RACE_RUNNERS table to replace any CHAR(160) with CHAR(32) for this jockey and might have to put it in the main stored proc that either saves jockeys into the RACE_RUNNER table or just the stat proc.

UPDATE	RACE_RUNNERS 
SET	JOCKEY = REPLACE(Jockey,CHAR(160),CHAR(32))
WHERE	JOCKEY = 'Mr P W Mullins'
Hey presto one instance of the name in the table. A tricky issue that I thought was down to collation but it wasn't. 

The collations of table columns and the DB were the same and doing matches with a specified collation didn't solve the issue. 

Therefore you have to think out of the box and go back to basics and just think what could be different. In this case it was the spaces. Removing them from both words created a MATCH result therefore I knew one space was a different character to the other. 

Here is a link to an ASCII chart if you should ever want to see what character CHAR(N) returns > https://ascii.cl/.

Problem solved. 

By Strictly-Software

© 2021 By Strictly-Software