VBScrpt SQL Select query but SQL Update wont work.

[yaccoff]

EA V12 with MS Access DB.

The model has a load of URLs which have all moved and need to be updated. This ocde extracts the URLs and places them in an Excel workbook.

sub createDocsSql
   dim sSQL, f
   dim nRow, nCol, sHeader
   dim xml, xmlDoc
   
   sHeader = "EA GUID," & Chr(10) & "Process Name" & Chr(10)
   
   f =    "to.ea_guid, tp.Name AS Package, to.Name AS Document, "
   f = f & "to.Alias, to.[Version], to.Note, "
   f = f & "t_p1.[Value] AS Type, t_p2.[Value] AS Origin, t_p3.[Value] AS URL "
   
   sSQL = "SELECT " & f   
   sSQL = sSQL & "FROM  ((((( t_object to "
   sSQL = sSQL & "LEFT JOIN t_package tp ON tp.Package_ID = to.Package_ID )"
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p0 ON (t_p0.Object_ID = to.Object_ID AND t_p0.Property = 'dataObjectRef') )"
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p1 ON (t_p1.Object_ID = to.Object_ID AND t_p1.Property = 'type') )"
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p2 ON (t_p2.Object_ID = to.Object_ID AND t_p2.Property = 'origin') )"
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p3 ON (t_p3.Object_ID = to.Object_ID AND t_p3.Property = 'URL') )"   
   sSQL = sSQL & "WHERE ((to.Stereotype = 'NATS_Document') AND (t_p3.[Value] IS NOT NULL)) "
   sSQL = sSQL & "ORDER BY to.Name"
   
   ' Execute sSQL query
   xml = Repository.SQLQuery (sSQL)
   
   ' Parse the query result in a DOM tree
   set xmlDoc = CreateObject("Msxml2.DOMDocument.6.0")
   xmlDoc.validateOnParse = False
   xmlDoc.async = False
   xmlDoc.loadXML (xml)
   
   nRow = 2
   dim recordset
   set recordset = xmlDoc.selectNodes ("//EADATA//Dataset_0//Data//Row")
   if not recordset is Nothing then
      dim record
      With oWorksheet.Cells.Rows(1)
         .Font.Bold = True
         .Font.Name = "Calibra"
         .Font.Size = 11
         .Interior.Pattern = 1   ' xlSolid
         .Interior.Color = 15773696
         .HorizontalAlignment = -4108 ' xlCenter
         .VerticalAlignment = -4108 ' xlCenter
         .WrapText = False
         .Orientation = 0
         .AddIndent = False
         .IndentLevel = 0
         .ShrinkToFit = False
         .ReadingOrder = -5002 ' xlContext
         .MergeCells = False
      End With
      
      for each record In recordset
         dim xmlChilds, xmlNode
         set xmlChilds = record.childNodes
         nCol = 1
         for each xmlNode in xmlChilds
            oWorksheet.Cells (nRow, nCol).Value = xmlNode.Text
            nCol = nCol + 1
         next
         nRow = nRow + 1
      next
      
      ' Titles
      dim tFields
      tFields = split (sHeader, ",")
      for nCol = 0 to UBound (tFields)
         oWorksheet.Cells (1, nCol + 1).Value = tFields (nCol)
      next
      
      for nCol = 0 to UBound (tFields)
         oWorksheet.Columns (nCol + 1).Autofit ' Columns Autofit
      next
   end if
end sub

Having got the URLs a new column is create with the new corresponding URLs (prevURL and newURL) This info is read in and a temporary table 'sTableTest' holds both sets of data. This code TRIES to update the old URL with the new URL, but doesn't work.

sub replaceURLSql ( )
   dim sSQL, f
   dim xml

   sSQL = "UPDATE t_objectproperties "
   sSQL = sSQL & "FROM  ((((((( t_object to "
   sSQL = sSQL & "LEFT JOIN t_package tp ON tp.Package_ID = to.Package_ID ) "
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p0 ON (t_p0.Object_ID = to.Object_ID AND t_p0.Property = 'dataObjectRef') ) "
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p1 ON (t_p1.Object_ID = to.Object_ID AND t_p1.Property = 'type') ) "
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p2 ON (t_p2.Object_ID = to.Object_ID AND t_p2.Property = 'origin') ) "
   sSQL = sSQL & "INNER JOIN t_objectproperties t_p3 ON (t_p3.Object_ID = to.Object_ID AND t_p3.Property = 'URL') ) "   
   sSQL = sSQL & "INNER JOIN " & sTableTest & " ON t_p3.[Value] = " & sTableTest & ".prevURL ) "
   sSQL = sSQL & "SET t_objectproperties.[Value] = " & sTableTest & ".newURL "
   
   TRACE ( sSQL )
   
   ' Execute sSQL query
   xml = Repository.Execute (sSQL)
'   TRACE (xml)
end sub

I know the temporary table holds the right values. But why doesnt the Execute work?

[qwerty]

Because you're targeting an EAP and MSAccess is not ANSI SQL compliant.

It's also a good idea to post the error that's thrown. Look into %appdata%/...sparx.../dberror.txt too.

q.

[yaccoff]

It is what it is, in terms of MS Access, we are stuck with it.

I am not seeing any reported error, but will look into the file you mentioned. I am writing the script to help out a friend who will have to manually update over a 1000 URLs if I cant get this working. I was hopeing you would spot a issue with syntax on the update.

P.S. Does update work on the SQL Scratch Pad? Could I try a a simple update to test the basic FROM logic works?

[qwerty]

No. The scratch pad is R/O.

You could dump the SQL to text and post it as question on SO.

AFAIK Access does not like JOIN so much. Try sending it as just SELECT over the scratch pad. Probably you need to go to some WHERE/AND clause instead.

q.

[yaccoff]

Hi Querty, The solution was as follows: Using my temporary table, I called this sub multiple times. Turns out MS Access needs the very basic approach.

sub replaceNATSURLSql ( inputUrl, newURL )
   dim sSQL, f
   dim xml
   
   sSQL = "UPDATE t_objectproperties "
   sSQL = sSQL & "SET t_objectproperties.[Value] = '" & newURL & "' "
   sSQL = sSQL & "WHERE ((t_objectproperties.Property = 'URL' ) AND ( t_objectproperties.[Value] = '" & inputURL & "' )); "

   TRACE ( sSQL )
   
   ' Execute sSQL query
   xml = Repository.Execute (sSQL)
'   TRACE (xml)

[qwerty]

Yes, MSAccess is very "modest" when it comes to SQL details.

q.