Microsoft KB Archive/312507

= INFO: Establishing a Relationship with the Referrer Class =

Article ID: 312507

Article Last Modified on 10/22/2002

-

APPLIES TO


 * Microsoft Commerce Server 4.0 Standard Edition
 * Microsoft Commerce Server 2002 Standard Edition
 * Microsoft Commerce Server 2000 Standard Edition

-



This article was previously published under Q312507



SUMMARY
This article provides a code sample which sets up a relationship between the Request class and the Referrer class, which causes each referrer to be logged per request.

By default, the Referrer class in the schema is not used. To use it, you have to establish a relationship with Referrer and another class.

You can also set up a relationship between the Visit class and the Referrer class so that the referrer is only recorded from the time at which the visit starts. By doing this, you catch most of the external referrers; however, you would still miss some if someone leaves the site and then quickly returns.



Sample Code
Option Explicit On Error Resume Next

Const sSQL_SERVER_NAME = &quot;MySQLServer&quot; Const sDB_NAME = &quot;MyDatabase&quot; Const sUSER_ID = &quot;sa&quot; Const sPWD = &quot;&quot;

Call Main

'//- '// Description: Main '//- Sub Main On Error Resume Next

Call AddMyRel End Sub

Sub AddMyRel

On Error Resume Next WScript.echo &quot;Adding Reldef between Request and Referrer&quot; Call AddRel(sSQL_SERVER_NAME, &quot;Request&quot;, &quot;Referrer&quot;, 194)

End Sub

Function AddRel(sSqlServerName, sChildClass, sParentClass, sRelType) On Error Resume Next

Dim rec Dim cn   Dim cmdChange Dim strURL '// Set AddColToTable to the default value AddRel = False

Set rec = CreateObject(&quot;ADODB.Record&quot;) Set cn = CreateObject(&quot;ADODB.Connection.2.5&quot;) Set cmdChange = CreateObject(&quot;ADODB.Command.2.5&quot;) If 0 <> Err.Number Then MsgBox &quot;[AddColToTable] An error occured while creating objects: &quot; & Err.Number & &quot; &quot; & Err.Description Exit Function End If

' --   ' Initial Bind ' --   strURL = &quot;URL=MSCOP://INPROCCONNECT/SERVER=&quot; & sSqlServerName & &quot;:DATABASE=&quot; & sDB_NAME & &quot;:USER=&quot; & sUSER_ID & &quot;:PASSWORD=&quot; & sPWD & &quot;:CATALOG=dwschema:fastload=true:&quot; cn.Open strURL If 0 <> Err.Number Then MsgBox &quot;[AddColToTable] cn.Open: &quot; & Err.Number & &quot; &quot; & Err.Description Exit Function Else ' --       ' Turn on Schema Change Mode ' --       Set cmdChange.ActiveConnection = cn        cmdChange.CommandText = &quot;schemamode=1&quot; cmdChange.Execute ' --       ' Add a new MEMBER ' --       Dim sText sText = &quot;Relation/Rel&quot; & CStr(sChildClass) & CStr(sParentClass) rec.Open sText, cn, 2, 8192 rec(&quot;ParentClassName&quot;) = sParentClass rec(&quot;ParentClasskey&quot;) = sParentClass & &quot;Key&quot; rec(&quot;ChildClassName&quot;) = sChildClass rec(&quot;RelType&quot;) = sRelType rec(&quot;__Commit&quot;) = 1 rec.Fields.Update rec.Close cmdChange.CommandText = &quot;CommitSchema&quot; cmdChange.Execute cmdChange.CommandText = &quot;schemamode=0&quot; 'change non-updatable mode cmdChange.Execute If 0 <> Err.Number Then MsgBox &quot;[AddRel] Execute commitschema: &quot; & Err.Number & &quot; &quot; & Err.Description Exit Function End If   End If

cn.Close Set cn = Nothing 'flush for fastload Set rec = Nothing Set cmdChange = Nothing

'// Adding a column to the table is successful AddRel = True End Function

'//- '// Purpose: Add a column (generic query string name) in the 'UriQuery' table. '//          The routine also add relationship for the new column in '//           'SVQStringName' and 'LinkSVQStringName'. '// Inputs:  sTable -> Name of the table in the data warehouse. '//          sColumnName -> Name of the column to add. '//          sColDataType -> Type of the column. '//          iIsMultiVal -> Field is single (=0) or multi-value (=1). '// Returns: True -> If adding the column to the table is successful. '//          False -> Otherwise (default). '//- Function AddColToTable(sSqlServerName, sTableName, sColumnName, sColDataType, iIsMultiVal) On Error Resume Next

Dim rec Dim cn   Dim cmdChange Dim strURL '// Set AddColToTable to the default value AddColToTable = False

Set rec = CreateObject(&quot;ADODB.Record&quot;) Set cn = CreateObject(&quot;ADODB.Connection.2.5&quot;) Set cmdChange = CreateObject(&quot;ADODB.Command.2.5&quot;) If 0 <> Err.Number Then MsgBox &quot;[AddColToTable] An error occured while creating objects: &quot; & Err.Number & &quot; &quot; & Err.Description Exit Function End If

' --   ' Initial Bind ' --   strURL = &quot;URL=MSCOP://INPROCCONNECT/SERVER=&quot; & sSqlServerName & &quot;:DATABASE=&quot; & sDB_NAME & &quot;:USER=&quot; & sUSER_ID & &quot;:PASSWORD=&quot; & sPWD & &quot;:CATALOG=dwschema:fastload=true:&quot; cn.Open strURL If 0 <> Err.Number Then MsgBox &quot;[AddColToTable] cn.Open: &quot; & Err.Number & &quot; &quot; & Err.Description Exit Function Else ' --       ' Turn on Schema Change Mode ' --       Set cmdChange.ActiveConnection = cn        cmdChange.CommandText = &quot;schemamode=1&quot; cmdChange.Execute ' --       ' Add a new MEMBER ' --       Dim sText sText = &quot;MEMBER/UriQuery/&quot; & CStr(sColumnName) rec.Open sText, cn, 2, 8192 rec(&quot;ClassDefName&quot;) = CStr(sTableName) rec(&quot;MemberDefName&quot;) = CStr(sColumnName) rec(&quot;TypeName&quot;) = CStr(sColDataType) rec(&quot;IsMultiValued&quot;) = CInt(iIsMultiVal) rec(&quot;GenerateColumnDef&quot;) = 1 rec(&quot;IsIdentityMember&quot;) = 0 rec(&quot;IsPrimaryKey&quot;) = 0 rec(&quot;IsJoinKey&quot;) = 0 rec(&quot;IsUniqueKey&quot;) = 0 rec(&quot;__Commit&quot;) = 1 rec.Fields.Update rec.Close cmdChange.CommandText = &quot;CommitSchema&quot; cmdChange.Execute cmdChange.CommandText = &quot;schemamode=0&quot; 'change non-updatable mode cmdChange.Execute If 0 <> Err.Number Then MsgBox &quot;[AddColToTable] Execute commitschema: &quot; & Err.Number & &quot; &quot; & Err.Description Exit Function End If   End If

cn.Close Set cn = Nothing 'flush for fastload Set rec = Nothing Set cmdChange = Nothing

'// Adding a column to the table is successful AddColToTable = True End Function

Keywords: kbinfo KB312507

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.