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, LOL, 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 easier to replicate the above code
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 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

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 a  
SELECT ASCII(' ') -- CHAR 32 ASCII SPACE

SELECT CHAR(160), CHAR(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















Wednesday, 18 March 2020

MS SQL Collation Issues

Fixing MS SQL Collation Differences

By Strictly-Software

With the quiet due to the COVID19 virus scare, and all major sporting events being cancelled for the foreseen future, I decided now would be a good time to get my old Automatic Betting BOT back up and working.

It used to run on a dedicated Windows 2003 server and MS SQL 2012 database before being outsourced to a French hosting company, OVH, which doesn't allow HTTP connections to online betting sites, basically making it useless. A decision that wasn't mine in anyway, but basically stopped my BOT from working.

It used to run as a Windows Service using Betfair's Exchange API to automatically create betting systems and then place bets based on systems with a rolling ROI > 5% over the last 30 and 100 days.

I am trying to get the BOT working on a local laptop and therefore it had already been backed up, the files FTP'd down to my laptop and restored in a local MS SQL Express database. 

However it seems that I had some issues when I previously attempted this some time ago as the database now had a different collation, a number of tables were now empty and had been scripted across without indexes and there was a collation difference between many columns and the new DB collation.

Just changing the Databases collation over isn't a simple act on it's own if numerous table column collations are different as well, especially those used in Indexes or Primary Keys as those references need to be removed before the collation can be changed.

A simple fix when it's only a small issue with one column might be to just edit the SQL where the issue arises and use a COLLATE statement so that any WHERE clause or JOIN uses the same collation e.g


SELECT  MemberID, MemberName
FROM MEMBERS 
WHERE MemberName COLLATE SQL_Latin1_General_CP1_CI_AI = @Name

However when you have a large database and the issue is that half your tables are one collation, the other another, and you need to decide which to move to, and then it becomes more difficult.

My new Database was using the newer SQL_Latin1_General_CP1_CI_AI collation whilst a subset of the tables were still using the older collation from the servers copy of the database Latin1_General_CI_AS.

Therefore finding out which columns and tables were using this collation was the first task and can easily be done with a system view with some SQL like so:

-- find out table name and columns using the collation Latin1_General_CI_AS
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE collation_name = 'Latin1_General_CI_AS'
ORDER BY table_name, column_name

From this I could see that the majority of my tables were using the older collation Latin1_General_CI_AS and that it would be easier to change the database collation to this, then the table collation.

However as there were very few indexes or keys I decided to do the reverse and use the newer collation SQL_Latin1_General_CP1_CI_AI, and change all columns using the LATIN to this new version.

However as I want to show you what to do if you need to change your MS SQL database and columns over to a new collation just imagine I am doing the opposite e.g I am changing my whole system from SQL_Latin1_General_CP1_CI_AI to Latin1_General_CI_AS.

If you did want to change the database collation after it had been created it is not as simple as just opening the databases property window and selecting a new collation OR just running the following query to ALTER the database. Just read the error message I was getting when attempting this.

USE master;
GO

ALTER DATABASE MyDatabase
COLLATE Latin1_General_CI_AS ;
GO

Msg 5030, Level 16, State 5, Line 18
The database could not be exclusively locked to perform the operation.

Msg 5072, Level 16, State 1, Line 18
ALTER DATABASE failed. The default collation of database 'MyDatabase' cannot be set to Latin1_General_CI_AS.

Apparently this locking issue is due to the fact that SSMS opens a second connection for Intellisense.

Therefore the correct way to do this, even if you are the only person using the database, as I was, is to put it into single user mode, carry out the ALTER statement, then put it back in multi user mode. #

This query does that.

ALTER DATABASE MYDATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE MYDATABASE COLLATE Latin1_General_CI_AS 

ALTER DATABASE MYDATABASE  SET MULTI_USER

Once the database has been changed to the right collation you will need to change all the tables with columns using the differing collation. However any columns being used in Indexes or Keys will need to have their references removed first. Luckily for me I didn't have many indexes at this point.

However I still needed to script out any Indexes and Key Constraints I had and save them into an SQL file so that they could easily be re-created afterwards.

I then dropped all the Indexes and Keys that needed to be removed and then ran the following piece of SQL which outputs a load of ALTER TABLE statements.

You may find like I did, that you actually need to add a COLLATE statement into the WHERE clause to get it to work without erroring as the system tables themselves may have a different collation than the one you are wanting to search for.

SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(o.name) + 
       ' ALTER COLUMN ' + quotename(c.name) + ' ' + type_name(c.system_type_id) 
    +CASE  
   WHEN c.max_length = -1 THEN '(max)'
   WHEN type_name(c.system_type_id)  = 'nvarchar' THEN '('+CONVERT(varchar(5),c.max_length/2)+')'
   ELSE '('+CONVERT(varchar(5),c.max_length)+')'   
  END + CASE WHEN c.[precision] = 0 THEN ' COLLATE '+c.collation_name   + ' ' ELSE ' ' END + LTRIM(IIF(c.is_nullable = 1, '', 'NOT ') + 'NULL ' )
FROM  sys.objects o
JOIN  sys.columns c ON o.object_id = c.object_id
JOIN  sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type = 'U' 
  AND c.collation_name <> 'Latin1_General_CP1_CI_AI' -- the collation you want to change from
ORDER BY o.[name]

You should get a load of SQL statements that can be run in a new query window to change all your columns.

It is best to run the previous SQL outputting to a textual window so that the output can easily be copied and pasted.

ALTER TABLE [dbo].[TRAINER_PERFORMANCE] ALTER COLUMN [RatingType] varchar(20) COLLATE Latin1_General_CI_AS
ALTER TABLE [dbo].[CANCEL_KILLED] ALTER COLUMN [RecordDetails] nvarchar(max) COL

Once you have run all these ALTER statements your whole database should be the collation that you require.

You can then take your copy of the Indexes and Keys that you created earlier, and run them to recreate any missing Indexes and Constraints. You should then be able to remove any quick fix WHERE clauses COLLATE statements that you may have used as a quick fix.

Collation differences are a right pain to resolve but if you do everything in order, and keep saved records of ALTER and CREATE statements that you need along the way it can be something fixed without too much work.

There are some large scripts to automate the process of dropping and re-creating objects if you want to use them however I cannot test to the reliability of these as I chose to do everything bit by bit, checking and researching along the way.

By Strictly-Software

Wednesday, 4 March 2020

Samsung Phones SmartView Application Bug

Fixing Problems With Samsung Phone SmartView Application

By Strictly-Software 

I have a Samsung Galaxy S8 mobile phone and recently I have had issues with the inbuilt SmartView application that allows you to mirror the screen of your phone to nearby devices such as Televisions, Rokus, Chromecasts and other Wifi enabled devices.

You access the app by using the swipe down settings panel at the top of the phone. It's on the second page accessed by swiping left and it's in the top right hand corner. See the below screenshot of my settings panel.



Whilst many applications that let you play videos have a "Play with external device" option that allows you to select the useful app AllCast before the device to then show the video on, some apps don't have a Chromecast beam square or option to play on an external device. 

This is where mirroring your screen is useful as you can just mirror yours phones screen to a TV and anything you do on the phone is then shown on the TV. The downside is that you can't use the phone or other apps whilst mirroring without your TV showing that app. However if you can use AllCast 
you can play a TV show, film or video from your phone on your TV but still use your phone to access other apps or make calls.

Up until last week everything worked fine, I could select SmartView and the apps little loading icon spins before local devices are shown to pick from. In my lounge I can pick from my Samsung 4k TV or the Chromecast device attached to it. This also allows me to beam videos from apps like YouTube that I don't want to mirror my phones screen to, or apps like sporting apps that don't have options to use AllCast to beam with.

In another room I have a Panasonic TV which also has a Roku attached and both could be used by AllCast or my phone to mirror to.The SmartView app used to be able to pick all 4 devices up and I could choose which TV or device to mirror my phone to.

However for some reason as I was watching a football game by mirroring my screen so the app could show the game on a big screen I left my pad. The connection was disconnected but when I came back I tried to use SmartView to re-connect it to my TV, but no devices were shown as available. In fact it didn't even look like it was scanning the room to find any devices to play to. Nothing was spinning in the corner and only the About Us and Contact us options were shown.

I filed numerous bug reports using Samsung's useful Members app and I had a reply that told me to go into System Settings, Applications, Select Show System Apps, choose SmartView then clear its cache. I did this but it didn't fix anything.

However tonight I came across a fix. I had to turn developer options on. You do this by going into Settings then About Phone and in there select Software Information before clicking on the Build Number about 7 times and it will give you the Developer Options.

In there you scroll down to Networking and turn on the option called "Wireless Display Certification"..



I then went into SmartView and it started spinning again, however a new panel appeared at the bottom with numerous WiFi connection options that I just ignored.



I then just scrolled to the bottom of these options and there appeared my selection of devices, in this case my TV and Chromecast.



And on choosing one, once again my phones screen was now mirrored to my TV.

I have no idea why this worked or what stopped SmartView from working in the first place but by doing my own debugging and playing around with the networking options I managed to solve what Samsung Tech Support couldn't. 

Hopefully this fix may help other Samsung phone users who have the same issue as me.

By Strictly-Software

© 2020 Strictly-Software

Friday, 24 January 2020

Making It Hard For Humans and BOTS to use SELECT Inputs

How To Ensure A Human Has To Select From A List Rather Than Just Hitting A Letter, Word or Number

By Strictly-Software

I noticed something the other day when logging into Halifax my bank, when you get to the memorable information form, you have to select 3 letters picked by random from your word

They will have 3 List boxes and they will pick 3 positions in your memorable phrase to select e.g letter 4, 7, 15.

I used to be able to just tab though the list boxes and hit the right letter or number and get it selected but no more.

At first I thought it might be something to prevent BOTS from using the form, and preventing bank fraud etc but when I inspected the SELECT inputs they used I noticed they were doing something else,

They have just put non breaking space entities in front of all the values and options e.g


<option value="&nbsp;a">&nbsp;a</option>

So they have just put a space character in front of the value and in the viewable list. However they haven't used a space as in a " " gap, they have used an encoded space as quick fingers would easily be able to select the box and hit the space bar and the letter to get to their value.

However it's not so easy to write out a whole &nbsp; character and the letter to select it without using your mouse.

I don't know how this would effect BOTS however as someone who examined their HTML code could easily write something that passed 3 letters to the server on submit with a non breaking space in front of them e.g &nbsp;a.

However it certainly makes sure that a human actually pays attention and USES the select box with a mouse rather than tabbing through.

If anyone can see how this increases security on Bank forms let me know as seems to not prevent BOTS - unless automated and those who don't know where they are from attacking a page. It might mean the rewriting of a generic intrusion tool to handle list boxes like this but that would be easy.

It just seems to make it harder for legitimate users from using their fingers and not the mouse from what I can see. However it does make sure you are more careful when entering the values. So maybe that is all they wanted to do.

If anyone can see any other reasons why putting a non breaking space character in the list box options is a good idea let me know please.

By Strictly-Software

© 2020 Strictly-Software


Sunday, 29 December 2019

Removing Chrome Has Sped Up My PC

How Removing Google Products Can Speed Up Your Laptop


By Strictly-Software

If you read my other recent pieces on speeding up my PC you might be interested in the fact that the other day I removed Google Chrome and now my PC is fast as road runner.

If you ever look at the task manager when you have 5+ tabs open in Chrome then you will see 5+ Google Chrome processes running. Their idea was that each tab of Google Chrome would run in it's own process so as not to interfere with the running of other tabs that maybe open.

Not only does that not seem to be the case in my situation, a slow tab will cause high memory, high CPU and DISK usage which then effects the other tabs, therefore I removed Google Chrome altogether and now just use Firefox, Opera and TOR Browser.

Just look at my task manager processes now that I have removed Chrome. Whilst I was suffering 100% DISK Usage, and high CPU and memory I am now showing only low numbers.

Currently as I write this article with 7 tabs open in Firefox including Facebook a heavy hitting site due to all the Ajax it uses and Betfair which updates constantly I am still only using 4% CPU, 54% Memory, 9% Disk Usage.


Not only has removing Google Chrome from my laptop has sped it up but if anyone has been paying attention, Google is an evil company (they had to remove that it didn't do evil from their motto), that is hooked into nearly every smart phone measuring when you sleep, walk, drive, the current temperature as well as listening to everything you say just in case it wants to be helpful.

I cannot count the number of times that I have been watching a football match streamed from my phone to my TV and then Google pops up to ask "What would you like to know about Liverpool" or some other nonsensical question I didn't want it to help me with in the first place.

This obviously means it is listening to you ALL THE TIME, otherwise it would not know when to offer help.

If you read the Terms and Conditions on your smart phone then you will see under privacy, a mile down the long winded notes, a section about how they are allowed to listen to you and send off to "unspecified 3rd parties" content that "may" help improve their service.

These unspecified parties maybe Google AdSense to show more relevant adverts by listening to the surrounding sound around the phone and then translating that to adverts you will see online. Or it maybe listening for more illicit speech that it can send local police enforcement or even the numerous 3 letter agencies in the UK and USA.

Will Binney from the NSA who was arrested for going public about the Prism program he had written and Edward Snowden had stolen to expose, told us about how he had designed it for spying on the Soviets in communist USSR. However once the cold war was over they flipped the script and used it to spy on US citizens illegally instead. 

If you read up on the web tech companies that were first involved in the unknowing spying on users, then they were Microsoft, Yahoo, AOL, Facebook, Google, Apple, PalTalk, YouTube, and Skype.

The UK was used as a test bed due to our lack of a real bill of rights or constitution and therefore we here in UK are rats roaming a maze designed by the NSA all so they can catch the right words and tell the correct authorities if need be.

The main problem is that you cannot remove Google products from Android phones and trying to do so will probably break your phone. Google is ingrained into most smart phones and almost impossible to remove totally. If you want an app you go to Google Play Store and trying to remove that from your phone would be a huge hassle.

Therefore I have done the logical thing due to the slowness of my PC and removed Google Chrome from it. Not only has it sped up my laptop but it means Google searches are no longer logged and kept for years as I now use DuckDuckGo or TOR Browser to surf the net. 

Even the browser Opera has a mini "VPN" built into it which goes through a proxy computer before the site you wanted to visit, hiding your real IP address, and making you look like you are in Germany or Bulgaria. 

Just try Opera and then go to this site https://manytools.org/http-html-text/http-request-headers/ and see what headers and IP address you are showing to websites and servers.



In another tab either type in the search box "Where am I now geo-IP" or just go to this link and you will see information like the results below when I just tested it with a new instance of Opera. My real IP is 86.164.1.XXX by the way.
However viewing my IP address on Opera with that search term or a header search reveals my location to be elsewhere from South East England. In fact they show me to be in the Scandinavian region of Europe with an IP address of 77.111.247.188.


Their Geo-IP location data is complied from 3 sources, IP2Location, ipinfo.io and www.db-ip.com they all use my Opera proxy IP of 77.111.247.188, and put my location in either Norway or Sweden, in either Oslo or Östergötland.

If you want more details on why Google is evil and Youtube is broken then read and watch the video on this blog. 

However if you don't care about Google working with the NSA and GCHQ and listening to you all the time through your phone and just want a faster PC, try removing Google Chrome from your machine and using another browser instead.

Remember to keep your task manager open to see if the DISK, CPU and Memory levels drop after a restart and a clearing of your registry and unused objects with CCleaner.


By Strictly-Software

© 2019 Strictly-Software