Marten’s blog

Recently I was asked to implement extension which will track external links into Google Analytics. For me I was facing to problems. One I that if you have running 3rd party system it’s hard to implement anything, that will change all external links and the second one is that Javascript is not my cup of tea.

I found and implemented one solution which looks simple and works. Main idea is to listen to click on page and if the clicked element is anchor, call Googles page tracking before browser goes to that page. Another solution is to use jQuery, I’ll show this solution later.

First step is to create a listener, what will listen events on page and call another function to handle the action.

function addListener(element, type, functionName)
{
  if(window.addEventListener) {
    element.addEventListener(type, functionName, false);
  }
  else if(window.attachEvent) {
    element.attachEvent('on' + type, functionName);
  }
  else return false;
  return true;
}

addListener(document, 'click', trackingExternalClick);

Second step will be to create function, which will contain the logic for onclick action. Lets say that we want to track anchors which point to external page (starting with http:// or https://) and wants to know whats the clicked domain, we don’t need to know whole url. Name of the function will be trackingExternalClick (third parameter in addListener).

function trackingExternalClick(event) {
  var cElem = (window.event) ? event.srcElement : event.target; //Get the clicked element
  //If it is an Anchor
  if(cElem.nodeName == 'A'){
    // Check if link is external, not contain local domain
    if(cElem.href.indexOf(location.host) == -1) {
    //Replace characters not supported by Google Analytics
      var url = cElem.href.match(/^([http|https]*):\/\/?([^\/]+)/g, "").toString().replace(new RegExp(/^([http|https]*)?:\/\//i),"");
      var str = '/outgoinglink/' + url;
      try{
        pageTracker._trackPageview(str);
      }
      catch(err){ }
    }
  }
}

Same practice can be used to track download links (.zip, .pdf, etc.). We will only change lines 6 to 9.

if(cElem.href.indexOf(".pdf") == -1) {
  //Replace characters not supported by Google Analytics
  var url = cElem.href;
  var str = '/download/' + url;

When you will have problems that these clicks are not tracked, try to move Google Analytics code to the head section of the page and these codes after GA. If you need anything more or wants to help with something, leave a message here.

google google analytics, javascript, tracking

This is really common task in any database project. You always need to get initial data into database. Maybe most common file format is CSV.

We have CSV file in drive C: with name import.csv with following content. The location of the file is C:\import.csv.

id,login,name,surname
1,user1,James,White
2,user2,Johny,Walker
3,user3,Jim,Beam

We will create table with these four columns.

CREATE TABLE CSVImport (
  ID INT,
  Login VARCHAR(20),
  FirstName VARCHAR(40),
  LastName VARCHAR(40),
  BirthDate SMALLDATETIME
);

Now we have everything we need to import the file into database. We will use BULK INSERT to put the data into table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK INSERT CSVImport
FROM 'c:\import.csv'
WITH
(
  FIRSTROW = 2,
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n'
)
GO

Parameter FIRSTROW will tell BULK LOAD that we don’t want to import first line (= 1) because it contains headers.

If you will have table what have less columns than the csv file these columns will be added after text in the last column. If this column will reach maximum length, this line will not be inserted. With MAXERRORS you can specify the maximum number of error lines what are allowed before whole INSERT will be considered as failed.

Possible problems

Permissions

• User which runs bulk insert must have BULKINSERT permissions.
• SQL Sever must have access rights to the file.

Database MSSQL, MSSQL 2005, MSSQL 2008

I want to share two documents about Commerce Server and how to get data from it via API. This is only few information taken from MSDN and another internet sources. Some parts are own thinking and code examples. I hope that this will help someone. If you want you can change these documents in any way an if you will share with the others, it can become more useful.

Download:

• Developing with the Catalog System
• Developing Modules Connecting to Commerce Server

Others books, Commerce Server

Some time ago I was developing project what reports all action of user on web site. I hit big number of problems, mainly performance problem. Biggest site had around 40,000 unique users each day. And one requirement is that it must recognize country by IP of the user. I get list of countries from MaxMind what I used for previous projects. I was able to insert the CSV file by BULK LOAD.

CREATE TABLE [dbo].[GeoIPCountry_Import] (
  [startIp] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [endIp] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [startIpNum] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [endIpNum] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [countryCode] [char](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [country] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
);

CREATE PROCEDURE [dbo].[UpdateGeoCountryList]
  @file nvarchar(400)
AS
BEGIN
  SET NOCOUNT ON;

  TRUNCATE TABLE [dbo].[GeoIPCountry_Import]
  EXEC('BULK INSERT [dbo].[GeoIPCountry_Import]
  FROM N''' + @file + ''' WITH
  (
    FIELDTERMINATOR ='','',
    ROWTERMINATOR =''\n'',
    CODEPAGE = ''OEM'',
    KEEPNULLS
  )')
  DECLARE @count int
  SELECT @count = COUNT(*) FROM [dbo].[GeoIPCountry_Import]
  IF(@count < 90000)
  BEGIN
    RAISERROR('File is too small or in wrong format. It must contain at least 90k records, fieldterminator = '','', rowterminator = ''\\n''.',15,1)
  END

  UPDATE [dbo].[GeoIPCountry_Import]
  SET
    startIp = [staging].[Trim](REPLACE(startIp, '"', '')),
    endIp = [staging].[Trim](REPLACE(endIp, '"', '')),
    startIpNum = [staging].[Trim](REPLACE(startIpNum, '"', '')),
    endIpNum = [staging].[Trim](REPLACE(endIpNum, '"', '')),
    countryCode = [staging].[Trim](REPLACE(countryCode, '"', '')),
    country = [staging].[Trim](REPLACE(country, '"', ''))

  SELECT @count = COUNT(*) FROM [dbo].[GeoIPCountry_Import]
  WHERE startIpNum IS NULL OR startIpNum = '' OR endIpNum IS NULL OR endIpNum = ''
  IF(@count > 0)
  BEGIN
    RAISERROR('Some IP''s cannot be converted into ip number.',15,1)
  END
END

This is the first part and it’s possible to use this format to getting country representation for ip. What is realy faster than to have ip range like ‘from 45.10.8.50 to 45.10.8.138′ is to have them like integer. We will create scalar function which will convert ip into integer.

CREATE FUNCTION [staging].[IPtoInt]  (@strIP varchar(15))
RETURNS bigint
AS
BEGIN
DECLARE @intIPNum bigint
IF (LEN(@strIP)-LEN(REPLACE(@strIP,'.','')))/LEN('.') = 3 -- if there 3 periods
SET @intIPNum = (16777216 * CAST(PARSENAME(@strIP,4) as bigint)  + 65536 * PARSENAME(@strIP,3) + 256 * PARSENAME(@strIP,2) + PARSENAME(@strIP,1))
RETURN @intIPNum
END

When we have this function we can create better performing table for storing data about countries and IPs. It’s possible to use city list from MaxMind. We will prepare the import procedure to be able import new database at anytime, when newer version is released.

CREATE TABLE [dbo].[GeoIPCountry](
  [startIp] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [endIp] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [startIpNum] [bigint] NOT NULL,
  [endIpNum] [bigint] NOT NULL,
  [countryCode] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [country] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  CONSTRAINT [PK_GeoIPCountry] PRIMARY KEY CLUSTERED
  (
    [startIpNum] ASC,
    [endIpNum] ASC
  )
)

And add few lines at the end of [GeoIPCountry_Import] procedure.

TRUNCATE TABLE dbo.GeoIPCountry
INSERT INTO dbo.GeoIPCountry (startIp, endIp, startIpNum, endIpNum, countryCode, country)
SELECT
  startIp, endIp,
  staging.IPtoInt(startIp), staging.IPtoInt(endIp),
  countryCode, country
FROM [dbo].[GeoIPCountry_Import]

We prepared everything what is needed for this. I even created table with most frequent IPs which help a lot, because the site had 90% of visitors from one country. This table contains IPs for this country an make selecting much more faster with less records in table. This solution was more than 10 times faster than table with IPs. And if you need to parse 100,000 records each day, it will help a lot.

SELECT
  IP, CASE WHEN GeoIPCountry.Country IS NOT NULL THEN GeoIPCountry.Country ELSE 'NA' END AS Country
FROM
  WebLog WITH (NOLOCK)
LEFT JOIN staging.GeoIPCountry WITH (NOLOCK) ON
  GeoIPCountry.startIPNum <= staging.IPtoInt(WebLog .IPNum)
  AND GeoIPCountry.endIPNum >= staging.IPtoInt(WebLog .IPNum)

I’m not really sure why, but there is faster to use GeoIPCountry.startIPNum <= IP AND GeoIPCountry.endIPNum >= IP then to use BETWEEN. If anyone will be interested in the cached solution or wants codes for all part I can provide them.

Database Database, MSSQL 2005, MSSQL 2008, SQL, Stored procedures

I hit this need when I was developing own custom module into Commerce Server. Commerce Server have all tables separated into catalogs. Catalog is group of tables which contains records for particular client (site), but have same structure like all other catalogs. Lets say that we have two clients ‘Client1′, ‘Client2′. Each client has tables for products ‘Client1_Products’, ‘Client2_Products’. Now we can create two procedures for retrieving data from both tables or better we can create one procedure, which will have one input parameter - Catalog name.

We can create the procedure:

CREATE PROCEDURE [dbo].[GetProducts]
(
  @CatalogName nvarchar(85),
  @Language nvarchar(85) = 'en-US',
  @ProductID nvarchar(255)
)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(4000)
  SET @sql = 'SELECT
      *
    FROM [dbo].[' + @CatalogName + '_Products] AS [Products]
    INNER JOIN [dbo].[' + @CatalogName + '_Products_' + @Language + '] AS [ProductsLang] ON
      [Products].[ProductID] = [ProductsLang].[ProductID]
    IF(@ProductID IS NOT NULL)
      SET @sql = @sql + ' WHERE [Products].[ProductID] = @ProductID '

  EXEC sp_executesql @sql,
    N'@ProductID nvarchar(85)',
    @ProductID
END

Now we are able to call this procedure for any client and as bonus get language related data for products. EXEC [dbo].[GetProducts] ‘Client1′, ‘en-US’; will return all products for ‘Client1′.

We can specify product ID if we want to receive only one product. Product ID condition will be added to where.

IF(@ProductID IS NOT NULL)
  SET @sql = @sql + ' WHERE [Products].[ProductID] = @ProductID '

At the end of stored procedure we will execute generated SQL.

EXEC sp_executesql @sql,
  N'@ProductID nvarchar(85)',
  @ProductID

First parameter of sp_executesql is a Unicode string that contains a Transact-SQL statement or batch. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed at this point. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

Second parameter is list of strings that contains the definitions of all parameters that have been embedded in first parameter. Every parameter must be defined in @params.

Third parameter are values for the parameters that is defined in the second parameter.

OUT or OUTPUT indicates that the parameter is an output parameter. By this, you can get last inserted sequence number for example.

Database Database, MSSQL 2005, MSSQL 2008, SQL, Stored procedures