Creating the Database Queries

Article
09/12/2006

The heart of the Project Renamer PDS extender is the SQL query that changes the project names in the Microsoft® Office Project Server database. RenameProject calls the ProduceSQLQuery function for each project to be renamed, as described in Parsing the ProjectRename Method. All of the project renaming queries are concatenated to one string, so that a single query can update all of the project names in the XML request.

Before the extender executes the project renaming query, it also creates and executes queries to check for duplicates, user permission, and whether the project is checked out. Following are explanations of the four main SQL query functions in the Project Renamer PDS extender, in order of execution.

CheckProjIDs shows the complete function. The CheckForDuplicates, CheckForPermission and CheckForCheckedOut functions are similar.
CheckForDuplicates
CheckForPermission
CheckForCheckedOut
ProduceSQLQuery

At the conclusion of a successful call, or an error, XMLRequestEx returns the reply to the PDS; see Reply.

To debug the Project Renamer PDS extender queries and other functions, see Debugging.

CheckProjIDs

CheckProjIDs makes sure that each ProjectID in the request actually matches an existing project. If any ID does not match a project, CheckProjIDs returns False. The SQL query gets all of the project records that match the comma-separated list of values in the sIDs parameter. If the number of records does not match, CheckProjIDs returns False. Following is the complete function.

Private Function CheckProjIDs(ByVal sIDs As String, ByVal numIDs As Integer) As Boolean    
    Dim sQuery As String
    sQuery = "select PROJ_ID from MSP_WEB_PROJECTS where PROJ_ID in (" & sIDs & ")"
    
    Dim RS As ADODB.Recordset
    Dim cmd As ADODB.Command
         
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = m_oConnection
    cmd.CommandText = sQuery
    cmd.CommandType = adCmdText
    
    Set RS = New ADODB.Recordset    
    RS.CursorType = adOpenForwardOnly
    RS.LockType = adLockReadOnly
    
    Dim lCount As Long
    Set RS.Source = cmd
    RS.Open
    lCount = 0
    While RS.EOF = False
        lCount = lCount + 1
        RS.MoveNext
    Wend
    RS.Close

    ' Return true if the number of records equals the number of ProjectID values requested
    CheckProjIDs = lCount = numIDs
End Function

For example, suppose an XML request contains three Project nodes; RenameProject concatenates the ProjectID values and passes the following string in the sIDs parameter:
"5,6,7"

The resulting SQL query is:
select PROJ_ID from MSP_WEB_PROJECTS where PROJ_ID in (5,6,7)

CheckProjIDs creates an ActiveX Data Objects (ADO) recordset to count all of the records that match the list of PROJ_ID values and returns False if the number of records does not equal the number of PROJ_ID values. The recordset has a forward-only cursor for speed, because it simply has to read once through table.

CheckForDuplicates

Project Server would not work well if two projects had the same name. CheckForDuplicates creates and executes a SQL query that checks the comma-separated list of names in the sNewName parameter for any existing project name that would be duplicated. The implementation of CheckForDuplicates creates a SQL query and ADO recordset in a manner similar to CheckProjIDs; following are the other key parts of the function:

Private Function CheckForDuplicates(ByVal sNewName As String) As Boolean         
    . . .           
    Dim lCount As Long
    Set RS.Source = cmd
    RS.Open
    lCount = 0
    While RS.EOF = False
        lCount = lCount + 1
        RS.MoveNext
    Wend
    RS.Close

    ' Return True if there are no duplicates
    CheckForDuplicates = lCount = 0
End Function

For example, in the XML request with three Project nodes; RenameProject concatenates the new names and passes the following string in the sNewName parameter:
"'Name1','Name2','Name3'"

The resulting SQL query is:
select PROJ_NAME from MSP_WEB_PROJECTS where PROJ_NAME in ('Name1','Name2','Name3')

CheckForDuplicates creates an ADO recordset to count all of the duplicate project names and returns False if there are any duplicate names. The recordset has a forward-only cursor for speed, because it simply has to read once through the project names.

CheckForPermission

CheckForPermission gets the WPROJ_ID (Web ProjID) that corresponds to each ProjectID and checks if the user has read/write permission for every WPROJ_ID in the list. The implementation of CheckForPermission creates a SQL query and ADO recordset in a manner similar to CheckForDuplicates; following are the other key parts:

Private Function CheckForPermission(ByVal sIDs As String) As Boolean
    CheckForPermission = False
    . . .           
    Dim lCount As Long
    Dim lVal As Long
    . . .    
    lCount = 0
    While RS.EOF = False
        lCount = lCount + 1
        lVal = RS("WPROJ_ID").Value
        If m_oPjSvrSecurity.CheckSPObjectPermission(m_sUserGUID, lVal, _
            pjotProject, pjaidSaveProject) = 0 Then GoTo ExitFunction
        RS.MoveNext
    Wend
    
    'Return True if permission check is correct
    CheckForPermission = True
ExitFunction:
    RS.Close
End Function

For example, in the XML request with three Project nodes, RenameProject concatenates the ProjectID values and passes the following string in the sIDs parameter:
"5, 6, 7"

The resulting SQL query is:
select WPROJ_ID from MSP_WEB_PROJECTS where PROJ_ID in (5, 6, 7)

CheckForPermission calls the CheckSPObjectPermission method of the Project Server Security object, with the user GUID, WPROJ_ID, and the constants pjotProject (=1) and pjaidSaveProject (=1012) that tell CheckSPObjectPermission to check for objects of type Project where the user has SaveProject access permission. CheckForPermission exits with False if the permission check fails on any one of the WPROJ_ID values. For more information, see the CheckSPObjectPermission method in About the Project Server Security Object.

CheckForCheckedOut

If a project is checked out, it cannot be renamed. CheckForCheckedOut creates a SQL query that checks each project ID in the list. If any project in the XML request is checked out, the entire request fails. The implementation of CheckForCheckedOut is similar to CheckForDuplicates; following are the other key parts:

Private Function CheckForCheckedOut(ByVal sIDs As String) As Boolean
    CheckForCheckedOut = False
    . . .
    Dim lCount As Long
    Dim lVal As Long
    . . .
    lCount = 0
    While RS.EOF = False
        lCount = lCount + 1
        lVal = RS("PROJ_CHECKEDOUT").Value
        If lVal <> 0 Then GoTo ExitFunction
        RS.MoveNext
    Wend
    
    'Return True if permission check is correct
    CheckForCheckedOut = True
ExitFunction:
    RS.Close
End Function

As in the previous example, RenameProject passes the following string in the sIDs parameter:
"5, 6, 7"

The resulting SQL query is:
select PROJ_CHECKEDOUT from MSP_PROJECTS where PROJ_ID in (5, 6, 7)

CheckForCheckedOut checks each PROJ_ID in the ADO recordset and exits with False if any of one of those projects is checked out.

ProduceSQLQuery

ProjectRename calls ProduceSQLQuery for each project in the XML request, to create a query that renames the project. ProduceSQLQuery, in turn, calls GrabVersionQuery and SelectVersionQuery that create repetitive subqueries within the main query.

To rename a project, the SQL query has to update the project name at every place the name occurs within five tables: MSP_PROJECTS, MSP_WEB_PROJECTS, MSP_VIEW_PROJ_PROJECTS_STD, MSP_VIEW_PROJ_TASKS_STD, and MSP_WEB_ASSIGNMENTS.

Private Function ProduceSQLQuery( _
    ByVal sNewName As String, _
    ByVal nProjID As Long) _
    As String

    Dim sQuery As String
    sQuery = ""
                
     'Update MSP_PROJECTS (PROJ_NAME, PROJ_PROJECT, PROJ_PROP_TITLE, PROJ_EXT_EDITED)
     sQuery = sQuery & _
             "UPDATE MSP_PROJECTS " & _
             "SET PROJ_NAME='" & sNewName & _
                 "'+" & GrabVersionQuery("PROJ_NAME") & _
                 "," & _
                 "PROJ_PROP_TITLE='" & sNewName & _
                 "'+" & GrabVersionQuery("PROJ_PROP_TITLE") & _
                 "," & _
                 "PROJ_PROJECT='&lt;&gt;\" & sNewName & "' " & _
                 "," & _
                 "PROJ_EXT_EDITED=1 " & _
             "WHERE PROJ_ID=" & nProjID & _
                 " OR " & _
                 "PROJ_ID in " & SelectVersionQuery(nProjID) & _
              vbNewLine

    'Update MSP_WEB_PROJECTS
    sQuery = sQuery & _
             "UPDATE MSP_WEB_PROJECTS " & _
             "SET PROJ_NAME='" & sNewName & _
             "' " & _
             "WHERE PROJ_ID=" & nProjID & _
             " OR " & _
             "PROJ_ID in " & SelectVersionQuery(nProjID) & _
             vbNewLine
    
    'Update MSP_VIEW_PROJ_PROJECTS_STD
    sQuery = sQuery & _
             "UPDATE MSP_VIEW_PROJ_PROJECTS_STD " & _
             "SET ProjectTitle='" & sNewName & _
             "'+" & GrabVersionQuery("ProjectTitle") & _
             "WHERE ProjectUniqueID=" & nProjID & _
             " OR " & _
             "ProjectUniqueID in " & SelectVersionQuery(nProjID) & _
             vbNewLine
    
    'Update MSP_VIEW_PROJ_TASKS_STD
    sQuery = sQuery & _
             "UPDATE MSP_VIEW_PROJ_TASKS_STD " & _
             "SET TaskName='" & sNewName & _
             "'+" & GrabVersionQuery("TaskName") & _
             "WHERE TaskUniqueID=0" & _
             " And " & _
             "(ProjectUniqueID=" & nProjID & _
             " OR " & _
             "ProjectUniqueID in " & SelectVersionQuery(nProjID) & _
             ")" & _
             vbNewLine
    
    'Update MSP_WEB_ASSIGNMENTS
    sQuery = sQuery & _
             "UPDATE MSP_WEB_ASSIGNMENTS " & _
             "SET TASK_NAME='" & sNewName & _
             "'+" & GrabVersionQuery("TASK_NAME") & _
             "WHERE TASK_UID=0" & _
             " And " & _
             "WPROJ_ID in (" & _
                "SELECT WPROJ_ID from MSP_WEB_PROJECTS " & _
                "WHERE PROJ_ID=" & nProjID & _
                " OR " & _
                "PROJ_ID in " & SelectVersionQuery(nProjID) & _
                ")" & _
            vbNewLine
    
    ProduceSQLQuery = sQuery
End Function

Following are the key parts of a typical query that renames one project. The actual query that ProduceSQLQuery generates is more complicated, because it renames all versions of the project. In addition, the MSP_VIEW_PROJ_TASKS_STD and MSP_WEB_ASSIGNMENTS tables have a WPROJ_ID field, not PROJ_ID. The actual query uses subqueries that find records based on the correct fields.

UPDATE MSP_PROJECTS
    SET PROJ_NAME='Name1'+(SUBSTRING(PROJ_NAME,CHARINDEX('.',PROJ_NAME),LEN(PROJ_NAME))),
    PROJ_PROP_TITLE='Name1'+(SUBSTRING(PROJ_PROP_TITLE,CHARINDEX('.',PROJ_PROP_TITLE),LEN(PROJ_PROP_TITLE))),
    PROJ_PROJECT='<>\Name1' ,PROJ_EXT_EDITED=1
    WHERE PROJ_ID = 5
    
UPDATE MSP_WEB_PROJECTS
    SET PROJ_NAME='Name1'
    WHERE PROJ_ID=5
    
UPDATE MSP_VIEW_PROJ_PROJECTS_STD
    SET ProjectTitle='Name1'+(SUBSTRING(ProjectTitle,CHARINDEX('.',ProjectTitle),LEN(ProjectTitle)))
    WHERE ProjectUniqueID=5
    
UPDATE MSP_VIEW_PROJ_TASKS_STD
    SET TaskName='Name1'+(SUBSTRING(TaskName,CHARINDEX('.',TaskName),LEN(TaskName)))
    WHERE TaskUniqueID=0 And  (WPROJ_ID equivalent of PROJ_ID=5)
    
UPDATE MSP_WEB_ASSIGNMENTS
    SET TASK_NAME='Name1'+(SUBSTRING(TASK_NAME,CHARINDEX('.',TASK_NAME),LEN(TASK_NAME)))
    WHERE TASK_UID=0 And  (WPROJ_ID equivalent of PROJ_ID=5)

Note For the complete SQL query for the previous example, see the ExampleSQLQuery2RenameProject.txt file in the Project 2003: Project Renamer PDS Extender (pj11pdsxRenamer.exe) download.

GrabVersionQuery

The GrabVersionQuery function creates a SQL subquery that returns the version part of a project name.

Private Function GrabVersionQuery( _
    ByVal sColumn As String) _
    As String
    GrabVersionQuery = _
        "(SUBSTRING(" & sColumn & _
        ",CHARINDEX('.'," & sColumn & _
        "),LEN(" & sColumn & _
        ")))"
End Function

The resulting subquery looks like the following:
SUBSTRING(PROJ_NAME,CHARINDEX('.',PROJ_NAME),LEN(PROJ_NAME))

For example, if the sColumn parameter is "Proj1.Published", GrabVersionQuery returns "Published".

SelectVersionQuery

The SelectVersionQuery function works with the GrabProjectActualNameQuery function to create a SQL subquery that retrieves a PROJ_ID list of all versions of projects with the same name. For example, the query would return the PROD_ID values of Proj1.Published, Proj1.Target, Proj1.Archived, and Proj1.Draft.

Private Function SelectVersionQuery( _
    ByVal sProjIDs As String) As String
    
    SelectVersionQuery = _
        "(select PROJ_ID from MSP_PROJECTS where PROJ_ID in " & _
        "(" & sProjIDs & ")" & _
        " OR " & _
        GrabProjectActualNameQuery("PROJ_NAME") & _
        "in (select " & GrabProjectActualNameQuery("PROJ_NAME") & _
        "from MSP_PROJECTS where " & _
        "PROJ_ID in " & _
        "(" & sProjIDs & ")" & _
        "))"
End Function

Following is the GrabProjectActualNameQuery function.

Private Function GrabProjectActualNameQuery(ByVal sColumn As String) As String
    GrabProjectActualNameQuery = _
        "(SUBSTRING(" & sColumn & _
        ",0,CHARINDEX('.'," & sColumn & _
        ")))"
End Function

Following is an example subquery for a PROJ_ID of 5:

(select PROJ_ID from MSP_PROJECTS where PROJ_ID in (5) OR (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME)))in (select (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME)))from MSP_PROJECTS where PROJ_ID in (5)))

Reply

After RenameProject concatenates the queries that ProduceSQLQuery creates for each project, it finally calls a simple ExecuteSQLQuery subroutine to run the query that renames the projects specified in the XML request. If there is a failure at any step of the process, the entire request fails and the Project Renamer PDS extender returns a non-zero STATUS or HRESULT to the PDS. Following is the reply for a successful result.

<Reply>
  <HRESULT>0</HRESULT>
  <STATUS>0</STATUS>
</Reply>

For a list of error STATUS codes, see Error Codes in the Project Data Service Reference.

Debugging

To see the query produced when you send an XML request to the Project Renamer PDS extender, use Microsoft Visual Basic® 6.0 to open the ProjectRenamer.vbp file on the same computer with Project Server, set a breakpoint in one of the functions in CMain.cls, and run the project within the Visual Basic environment (press F5 to start execution of the code). You can then run test requests with the PDSTest or PDSTest.NET applications that are included in the Project Data Service (PDS) Usage and Methods Reference from the Microsoft Download Center.

When a PDS request includes a valid and complete <ProjectRename> node, execution passes to the Visual Basic environment and stops at the breakpoint. You can then use the Debug toolbar with the Step Into, Watch Window, Immediate Window, and the other debug functions in Visual Basic.

For example, if you set a breakpoint in the ProduceSQLQuery function, when you run a test request and execution stops at the breakpoint, type the following in the Immediate window and press the ENTER key (the Immediate window recognizes the question mark in the statement as a Print command):
? sQuery

Visual Basic prints the current value of the sQuery variable to the Immediate window.