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

Browser Slow Down May Have a Windows Cause

By Strictly-Software

Yesterday if you read my blog article about Firefox and Chrome slowing me down as if I was being dragged through mud by a 3 legged donkey, then you will know that I was blaming the browsers and their settings for the slow down.

However when I could take no more, of browser windows hanging and then seemingly Windows applications not opening when I clicked the desktop icon which I have never had before I thought it must be a virus or some sort of app slowing me down however virus scans with Windows Defender and MalwareBytes AntiMalware showed nothing at all.

I was trying to open applications like Open Office only to see the process in the task manager running as a background process. It was not up the top under Apps, but just sat there forever under background processes.

I also had 100% Disk usage with no singular app or process looking like it was causing it. I also had high memory and CPU usage. So I thought what I had done lately. I went to Programs and Features to see which applications had been updates or loaded recently. Skype was there, like it is today, and I removed it as I can no longer use it with Windows 8.1r.

There was also Spotify which I hardly ever use so I uninstalled that as well. However it was after this I started getting the problems.

So I went to System Restore Points and noticed a Critical Windows Update had been carried out on the 11th of this month. I ticked the box to show all restore points and chose the Automatic Restore Point before the update.

I did a system restore to that date which was 2 days before the Windows Update and after an hour I had my PC back and working. It was much faster and the browsers were fast as well, both Chrome and Firefox.

However I will not take away the points I made yesterday about over complicating the options and issues for privacy and security which seem far too over the top for the average user to understand.

So when I was finished with what I was doing I went to shut the laptop off and did the Windows Update again. This time it seems to not have slowed everything down. The Critical Update has been done and I have a much faster laptop. For example just look at the Task Manager, which for the same apps, with the same tabs open was showing 100% DISK usage and high Memory and CPU usage, with programs running in the background not as open Apps.

However I am not holding my breath as it seems that all these browsers and other apps like Rapport (banking crud software), seems to be slowing the laptop down.

I think it is time I do a proper search of the computer, remove apps I don't use, run Chkdsk -f (from the command prompt), and see if a particular browser is causing me issues.

At the moment I have 5% CPU, 40% Memory 3% Disk usage (Not the 100% I was seeing all the time).

I have no ideas what the Windows Update did to screw this up and when I look at programs recently installed I only see Google Chrome as having updated itself today. Firefox is set to auto update so I will wait to see what happens when the latest version comes in as I am currently on version 70, and yesterday I was on version 71. Since Chrome updated however the CPU and Disk Usage also started to spike again.


I have decided to uninstall Chrome and just use Firefox and since doing so my disk usage is running at 1%. I have no idea what Chrome could be doing to cause these memory leaks and disk usage. I did notice in their settings under advanced the other day an option to look for "malware" that could be slowing your computer down. I reckon it is more to do with all the holes that let PUPs and other files that keep popping up after a scan in the Chrome Roaming folder that they are looking for.

However it in not uncommon for people to have issues after Windows Updates. User Profiles going missing causing you to think you have lost all your apps and other weird things. Having to do restore points seems drastic but at the moment I am happy as my apps are all running and the task manager is showing good stats. However I still think Firefox's security and privacy options are too much for the average user and some of them even confused me as their was no explanation. So read that article if you can.
I think these auto updates behind the scenes can cause major slowdowns and they should all be optional. If I have a version of Chrome that is running fast, not causing major CPU, Memory or Disk usage then I should be able to keep it. I don't want some new buggy install being rolled out and then a fix for that buggy version rolled out later.

As I said earlier IE only brings a new version of IE out once every couple of years. I cannot really see the need for 70+ versions of Chrome or Firefox unless they contain bugs and future version are to fix them.

By Strictly-Software

© 2019 Strictly-Software

Browsers New Automatic Settings Slowing Site Loads Down

Blocking All Social Media Cookies and Trackers Seems To Be Slowing Down Chrome and FireFox

By Strictly-Software

I have had recent automatic updates for Firefox and Chrome to versions

Mozilla/5.0 (Windows NT 6.3; Win64; x64; rv:71.0) Gecko/20100101 Firefox/71.0

and 

Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36

How these two have got to versions nearing 80 so quickly is funny, when IE just rolls out a new version of it's browser every year or so not every time I try and open Firefox from my taskbar.

However these updates seem to contain some important settings, some may have been around for a while which I just haven't noticed. However it is the slowness of these browsers compared to Opera, that uses a proxy server in it's pretend VPN, so you are actually going through 2 servers to your site compared to the other browsers, that is doing my head in.

I liked Firefox, and Chrome when it first came out for their speed, and add-ons. However either my laptop is either deciding to slow down these 2 browsers for some reason and let Opera hop before loading a faster page or something else is going on.

My version of Opera with this "VPN" is:

Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36 OPR/65.0.3467.62

When I open it, it is faster than both Chrome and FireFox despite their VPN which when I check with a geo location website shows I have these details instead of my real ones:

• Your Public IPv6 is: 2001:67c:2660:425:7::dfa
• Your IPv4 is: 77.111.247.105
• Location: Amsterdam, NH NL 
• ISP: Hern Labs AB

I notice they have removed the word Opera and replaced it with OPR now, also nearing it's 70th edition. So not only does it protect my privacy a bit it is faster than Chrome and FireFox at the moment for me.

So today after upgrades to both Chrome (manual due to it's insane slowness) and an automatic update for FireFox that took almost 20 minutes, I noticed major slowdowns.

Chrome seems to always be showing a "resolving host" message in the status bar and loading in remote scripts from the big 3 spyware social networks, Twitter, Facebook and Google.

Of course site builders have put these outbound scripts into their code as they want people to Like, Follow and Tweet whatever crap they are selling and you may like seeing a Twitter scroller on the blog you are following and the ability to share the page to Facebook.

However I watched a video on www.darkpolitricks.com the other night about how many #alttnews sites are moving their videos from YouTube to BitShute. YouTube is broken and Google is an evil company. And it seems they are indeed.

It is all about how these niche news outlets had created YouTube making it the biggest video sharing site online, and although the company claims they are only 1% of all videos watched they still feel the need to de-rank these alternative views and put "authoritative sources" above your searches in their algorithm tweaks. These tweaks were all admitted by a Google employee who described getting those Up Ticks and Likes as a "drug" that ensures people continue with their outpourings of every thing they do in life on Social Media.

Yes we do want to see where Jane is having lunch, who with, where the cafe is located and then everything else she does that day as we follow her goings about on Facebook and Instagram.Well you may want to but I don't. However to do so you need to load in Facebook scripts from their servers.

However it seems if you delve into the privacy and security settings for Firefox you get to see that their default setting is to stop tracking cookies from cross site and social media trackers which obviously means if you are loading a 3rd party script from another location you could see as I did today on one site the "trying to connect to t.co" message appear dozens of times as the page tried to load. All the while the page was hung and unusable.

You can go into the FireFox settings and change your settings under Privacy and Security. The heading is...

Browser Privacy

Enhanced Tracking Protection

Trackers follow you around online to collect information about your browsing habits and interests. Firefox blocks many of these trackers and other malicious scripts.

The default setting will block Social media trackers, Cross-site tracking cookies, Tracking content in Private Windows and Cryptominers.

Obviously the latter few are definitely required but let me know if you have noticed a slow down with the standard setting that supposedly is "Balanced for protection and performance. Pages will load normally.", as they may load normally but they seem very slow to load, and off server scripts like Twitter and Facebook are attempted multiple times before a page is usable.

What happened to just loading the core code first to let the page be usable and load any off server scripts by Ajax in the background. It seems too many sites now use pure JavaScript and Ajax to load the content, probably to prevent content scraper BOTS however it does mean a lot of code has to run and be loaded before the page is usable. Have you had a look at the source HTML of www.google.com lately?

Apart from some META tags after the HTML tag the whole source is JavaScript and probably Ajax to load in the content for what is really nothing more than a white page with a different image every now and then above a text input box for searching.

The links to your Google account and Gmail in the top right corner are just that links. We could shorten the load time and the code to a few lines of HTML in reality. I really think Google have gone overboard with their API Jizz all across their systems as their need to stop scrapers has just caused slow loading pages it seems.

Would you like all 3rd party scripts and cookies blocked, or would you like the site to work and load quickly? It seems a dilemma these browsers are making over complicated especially for non techies who wouldn't know half the words in Firefox's Privacy and Security settings.

The difference between Standard which says "Balanced for protection and security. Pages will load normally" and Strict which says "Stronger protection, but may cause some sites or content to break" seems to only be the addition of:

• Tracking content in all windows - rather than standard mode which only blocks "Tracking content in Private Windows" and
• Fingerprinters (blocking Browser finger printing, logging your add-ons, window size and other ways to identify you from just your browser)

They don't actually explain what a fingerprinter is, and to the average user they would be scratching their head thinking about their latest Samsung phone and the ability to login using your fingerprint. However these two extra blocks seem to be deadly for a working website as they state underneath :

Heads up! Blocking trackers could impact the functionality of some sites. Reload a page with trackers to load all content.

So god knows how someone is supposed to manage the 3rd option which is a custom way of blocking things you don't understand or know why they would break a site.

Of course they have a number of complicated Knowledge Base articles  for you to read and get your head round to try and understand whether they need to use Private Windows for browsing all the time, and why the prevention of loading certain features is going to stop your site loading.

Of course Firefox has a "simple way" to help you understand what is going on by just clicking on the shield in the address bar you can change the mode of protection on or off. 

You can view this site with "Enhanced Tracking Protection is OFF for this site" or ON and if you don't know what the difference is they have helpful little graphs that tell you about their Enhanced Tracking Protection, how many trackers they have blocked over the week and ways to look for data breaches. All very interesting but not very helpful information.

They helpfully clarify the situation by saying "Social networks place trackers on other websites to follow what you do, see, and watch online. This allows social media companies to learn more about you beyond what you share on your social media profiles."

Of course you could just disable 3rd party cookies and JavaScript by default with a web developer toolbar and see if the page loads or not. If it doesn't work turn on JavaScript and try again before white listing the site so it can use JavaScript again.

It seems that as Windows in numptifying the front end of their latest operating systems and making it harder for developers to dig in and get into the back end like Windows 8.1r which I still have - now without Skype support - the browsers are offering their users far too many options they probably don't understand or need to know about.

What I want from a browser is for websites to load quickly, any 3rd party hosted widgets like Facebook or Twitter widgets to load in asynchronously and not prevent the working of the site. I want the browser to do the dirty stuff behind the scenes and I don't want 100's of options to play about with. They should block dangerous content, warn users about dangerous sites and stop anything that may have a dangerous effect on my browsing or privacy.

Yes - Ask me if I want to load this soon to be outdated flash movie or allow notifications but don't give me too much to tweak about with.

The speed of loading a site is the most important factor for most users and also affects the sites SEO. If they want to give us an option for being as private as possible or allowing tracking cookies then just have a single option "Privacy HIGH or OFF" option, and then use their own browsing logic to work out if a page won't load instead of offering the user a whole list of options to try out if a page doesn't load.

What is wrong with just keeping incognito windows that are private as possible, don't allow trackers or fingerprinting and the logging of pages visited with a clear out of cookies automatically when I leave?

It just seems that as Chrome enters the laptop world with it's Chromebooks, that as Operating Systems continually ask for your admin password in Windows 10+ when opening an application. Hiding all the nitty gritty that really slows your PC down behind automated "maintenance jobs". That browsers are trying to become their own little PC within a PC.

Just give me fast loading pages and if I want to hide what I am doing from sites and other users of my laptop then make the incognito windows as private as possible. Stop trackers, fingerprinting, 3rd party cookies, and anything else you are now making a "choice" for the user under the settings.

It is bad enough that as everyone moves to HTTPS we see the TLS handshake message in the taskbar constantly which is obviously slowing down the loading of pages and their content, especially if it's mixed.

Just give me a fast browser. I thought using FireFox today would speed things up as Chrome is just getting unusable and as everyone realises they are actually evil, I don't want to help Google pass on my data from their browser or search engines and analytics trackers to advertisers and god knows who else.

From now on Opera with its extra server hop is going to be my standard browser. The "VPN" offers enough privacy and whilst some pages won't remember certain settings due to my location changing the browser is fast.

Anyone find their settings too complicated nowadays and the speed an issue?

By Strictly-Software

© 2019 Strictly-Software

 

Getting SQL Server Information

Using TSQ to obtain MS SQL Server Information

By Strictly-Software

In the years gone by when we had relational database systems that worked across MS SQL Versions such as our DEV, DEMO and LIVE databases we sometimes needed to do a check to see what the Version of the server was before running the appropriate code.

Our DEV server might be pretty old compared to our LIVE server and code that would run on SQL 2005 wouldn't on 2000.

An example would be if the Server needed to know whether it could use a CTE and temporary view to return hierarchical information or whether it had to use a more basic stack style temporary table version.

For example the stored proc we would call on any server would be:

EXEC dbo.usp_get_workers_heriarchy

-- inside the stored proc we would have a function wrapped around the @@version code that would just return 2000,2005,2008,2012 etc
DECLARE @SQLVERSION INT
SELECT @SQLVERSION = dbo.udf_GET_SQL_VERSION(@@VERSION) -- returns 2000 or 2012

IF @SQLVERSION > 2005
  BEGIN
        -- use a more modern CTE and temporary view
        EXEC dbo.usp_SQL_GET_WORKERS_CTE_VERSION
  END
ELSE -- handle everything under 2005 when CTE's and temporary views came out
  BEGIN
        -- use old stack method for adjency 
        EXEC dbo.usp_SQL_GET_WORKERS_STACK_VERSION
  END

So if the dbo.udf_GET_SQL_VERSION(@@VERSION) returned 2005, 2008, 2012, 2015 then it would use the most modern type of TSQL to handle returning a hierarchy whilst anything below that would use a stack version.

We would have to build this code up on an SQL system that handled CTES otherwise trying to save the stored procedure and the CTE would just cause invalid SQL errors as it wouldn't recognise the code.

So we built it on an SQL 2005+ machine and then had no problems as all calls were made from the front end by stored procedures and any machine over 2005 would handle a CTE or Stack response.

In the old days when we only had @@VERSION with very little text to parse it was pretty simple to just extract the SQL version from the return string, e.g remove 'Microsoft SQL Server ' or look for the first number after the word Server and LTRIM(RTRIM(@Version)) it to get 2005 such as this example piece of code which would have been used in the UDF dbo.udf_GET_SQL_VERSION in the example above.

DECLARE @Version varchar(30)
SELECT @Version = @@VERSION -- Microsoft SQL Server 2012 (RTM) - 14.0.1000.169 (X64)
SELECT @Version = REPLACE(@VERSION,'Microsoft SQL Server ','')
SELECT @Version = LEFT(@Version,4) -- to get just 2012 not the (RTM) - 14.0.1000.169 (X64)
SELECT @Version = CAST(@Version as INT) -- return as an integer
RETURN @Version -- e.g 2012

Now we have even more information in the @@VERSION system variable. For example now if you call @@VERSION on a later edition you will get.

SELECT  @@version
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
	Feb 10 2012 19:39:15 
	Copyright (c) Microsoft Corporation
	Web Edition (64-bit) on Windows NT 6.2  (Build 9200: ) <x64>

Which as you can see contains much more information than the old @@VERSION which just returned the bare basics of the server. Now we get the build version, the Edition type and Build details.

To access this information you can now use the SERVERPROPERTY function calls to get detailed information from the @@VERSION data but on it's own e.g

SELECT  SERVERPROPERTY ('productversion')
SELECT  SERVERPROPERTY ('productlevel')
SELECT	SERVERPROPERTY ('edition')

Which returns....

11.0.2100.60
RTM
Web Edition (64-bit)

Two other interesting functions are:

SELECT	SERVERPROPERTY ('InstanceDefaultDataPath')
SELECT	SERVERPROPERTY ('InstanceDefaultLogPath')

Which return the paths of your default data and log files locations for the current SQL Server instance e.g

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\

So as we can see using a branch off @@VERSION on any old code you may have lying about may need editing and if you are automating things such as data beign moved and paths being created you can see hopw the DefaultDataPath and DefaultLogPath variables maybe of use. We all live and learn and the world of SQL keeps turning ensuring jobs for people to fix old code!

By Strictly-Software

Do You Want Multiple Wickr Accounts?

How to get another Wickr Account To Work

By Strictly-Software

If you are like me you might have an Android phone with Wickr the encrypted chat app installed on it.

However if you have been blocked by someone, locked out, forgotten your password or just want another Wickr account it's not easy as Wickr is tied to your Google account and device and if you try and install it from Google Play Store on a different device it will just say "Already Installed". 

You have to either remove the app from your phone and start again or use a different phone with a different Google Account linked to it as Google Play always knows what devices you have installed and which apps are on it.

I tried the obvious route first, use an Android Emulator such as NOX, and then created a new Google Account and Email address and then went to the PlayStore to download Wickr. The only thing was the link was broken and it kept sending me to an error page.

I tried downloading the APK file from a site I searched for Wickr Download APK, but even after installing it, it got to the 99% of "encrypting your device" e.g my NOX Emulator on Windows 8.1, and just hung forever.

Therefore I couldn't get the emulator to work. I could have installed another one but whats the point of slowing down a PC with multiple Android emulators just to watch free TV from Navisport etc?

Therefore I actually did a search for "Download Wickr For Windows 8.1" and all I got were help articles telling me to download further emulators and then install from Google Play. This was obviously a no go.

Therefore I actually tried the following which did work on Windows 8.1 from this link.

The Windows 10 link is the first one on the left. The rest are APK, Mac and IOS files.

To install on a Windows 8.1 machine do the following, without the need for an Android Emulator.

1. Create a new Google Account, username, password, add it to the 12 you already have.
2. Log out of any Google account you might be in e.g especially if you are using Chrome.
3. Go to this link https://pro-download.wickr.com/#/version/pro
4. Select "Windows - For use with Windows 10"  and download the application.
5. Install the MSI file called WickrMe-5.4.3.msi.
6. It may take a while but it should start to install, even on Windows 8.1.
7. Once installed, create a new Wickr account and username.
8. Find your old contacts and start chatting from your laptop or PC.

The direct download link can be found here if you click here.

It works for me, so it should work for you, don't spend hours trying to getting your emulator to install an APK file that just hangs. Try the Windows 10 Desktop MSI file first.

By Strictly-Software

SQL Regular Expressions - Extracting Income From PayPal IPN Data

Extracting Payment Amounts From PayPal IPN Data Using SQL 

By Strictly-Software

Recently I had to come up with a way on one of my sites, www.fromthestables.com, to find the total amount of income received by certain members from their PayPal subscription information. 

This is all stored in an MS SQL Payments table along with the Member ID, the PayPal IPN data and Payment type e.g P for Payment, C for Cancelled, W for Waiting, E for EOT etc.

I also have a table of Member Subscriptions which stores details of every subscription they have ever had including their Member ID, a custom GUID to identify the subscription as they could have had multiple subscriptions. Also I store their PayPal Subscription ID and details of their Sign Up Date, Cancellation Date and Next Payment Date alongside other relevant information.

If you don't know about PayPal and how to make a payment system using their shopping cart or a custom payment system then you can read up about it on their site here.

However the main thing to know is that when payments are made, recurring subscriptions started or cancelled then PayPal will send your site an Instant Payment Notification (IPN) to a callback page for you to handle.

This IPN data is a long string of text including the payers name, email, address, recurring payment information such as the number of days payments are made, plus any custom values you may need for your site such as a unique identifier (GUID).

This is useful in website systems so that you can save the new member in a database table with an ID before they go off to PayPal. When they return to your site this information can be sent back to your IPN callback page so that you can analyse the IPN data and ensure it's a valid callback attempt by making a handshake with PayPals system. Once you know it's valid information you can link the callback and IPN data string to a member by matching their Member ID with the one you stored earlier in the database.

An example of an IPN string looks like the text below and contains all the information about an IPN. There are many types of IPN strings such as those that handle cancellations, waiting, EOT (End Of Term) etc but the one I am interested in that contains two bits of information I require is the Payment Notification. 

You can see the two pieces of information in the IPN text below as they are highlighted in blue and red. The start of the string contains one part and the other is halfway through the data.

mc_gross=30.00&protection_eligibility=Eligible&address_status=confirmed&payer_id=NW2XBXPZ6C78W&address_street=Some+Street&payment_date=13%3A27%3A02+Jan+17%2C+2019+PST&payment_status=Completed&charset=windows-1252&address_zip=D02TX83&first_name=Paul&mc_fee=1.37&address_country_code=IE&address_name=Paul+Hickey¬ify_version=3.9&subscr_id=I-PLD46W039GB&custom=%7B7CD66296-0664-4A51-9ACD-05FE46821D44%7D&payer_status=verified&business=rob%40somesite.com&address_country=Ireland&address_city=Dublin&verify_sign=AczUU94BLMilZ9uHs3gDJVFDFmnrAhoedso-UI.71KEbRJ9deMwoa8KS&payer_email=paulhickey1234%40hotmail.com&txn_id=91X75169EH810362V&payment_type=instant&last_name=Hickey&address_state=DUBLIN&receiver_email=rob%40somesite.com&payment_fee=&receiver_id=5Z7P6UFG56B3P&txn_type=subscr_payment&item_name=Strictly+Software&mc_currency=GBP&item_number=%7B24C686D0-BBF5-54B6-A1D6-215AP099CD43%7D&residence_country=IE&transaction_subject=Membership&payment_gross=&ipn_track_id=98d91af71234

As you can see the amount I want is in the first part is at the beginning of the string, just after mc_gross e.g mc_gross=30.00.

However due to legacy issues of the system and I also need to connect the users PayPal subscription ID to the one I store in the members subscription table.

This is highlighted further along in the string e.g subscr_id=I-PLD46W039GB.

An example of the CLR User Defined Function I use to allow for Regular Expression Replacements can be seen below. It is connected to a DLL that contains the C# code that runs the regular expression replacing, however that is another topic altogether. You just need to know the format of the parameters for the SQL I show you later on.

Personally I store this function in the MSDB system database so that all my databases on the server can utilise it for regular expression replacements.

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[udf_SQLRegExReplace](@Pattern [nvarchar](500), @MatchString [nvarchar](max), @ReplaceString [nvarchar](2000))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [asbl_SQLRegExpr].[UserDefinedFunctions].[RegExReplace]

The SQL code I use for totaling up all amounts for a certain member is below.

I use two embedded SQL UDF Replacement Functions to extract just the monetary amount and remove everything after it. I also use the same function to join the Subcriber ID in my table to the subscr_id value in the string. 

This involves using a sub-query that does the cleaning and replacing and returns the monetary value. Then an outer query uses that value and a SUM function to total all the values up.

SET ANSI_NULLS OFF
SELECT SUM(VAL) as Amount
FROM (
  SELECT CAST(msdb.dbo.udf_SQLRegExReplace('&.+?$',msdb.dbo.udf_SQLRegExReplace('^.*mc_gross=',PayPalDetails,''),'') as money) as val 
  --*
  FROM MEMBERS_PAYMENTS as p with (nolock) 
  JOIN MEMBER_SUBSCRIPTIONS as ms with (nolock) 
   ON ms.MemberFK = p.MemberFK
    AND ms.PaypalSubscriptionID = dbo.udf_SQLRegExReplace('&.+?$',dbo.udf_SQLRegExReplace('^.*subscr_id=',PayPalDetails,''),'')
    AND ms.MemberFK = 10342
  WHERE PaymentStatus = 'P'
 ) as t 
WHERE ISNUMERIC(t.VAL)=1

As you can see the SQL function calls are wrapped in an inner SQL statement so that it just returns payment values and then the outer SQL statement returns the SUM amount for a particular member ID value.

This is a good example of having to use SQL CLR Regular Expressions as well as the use of embedded functions to extract hard to get values from a long complicated string of data.

You could attempt to write the regular expression calls so that only one function call is made for the monetary amount and another one for the subscriber ID. However the IPN strings are not always similar in format and sometimes it is better for performance to break your regular expressions down into multiple replacement calls instead of writing a complicated expression to cover all possible formats.

Try it and see for yourself. See if you can re-write the query with one regular expression per extraction, and then compare the performance of the two queries with SQL Performance Monitor.

By Strictly-Software

© 2019 Stictly-Software