Book a Demo

Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Messages - yaccoff

Pages: [1] 2
1
General Board / Re: Relationship Matrix export to CSV via Script?
« on: September 20, 2019, 03:48:07 pm »
How does one obtain this Script? I can see it in the general scripting library and my company has a fully licensed copy?

2
General Board / Relationship Matrix export to CSV via Script?
« on: September 18, 2019, 07:11:51 am »
V12. MS Access DB. I can use the GUI to export to CSV a Relationship Matrix. Can I do it vis VBScript?

3
General Board / Re: VBScrpt SQL Select query but SQL Update wont work.
« on: August 19, 2019, 06:44:18 am »
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)

4
General Board / Re: VBScrpt SQL Select query but SQL Update wont work.
« on: August 17, 2019, 10:51:57 pm »
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?

5
General Board / VBScrpt SQL Select query but SQL Update wont work.
« on: August 17, 2019, 05:44:22 am »
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?

6
General Board / Re: SQL Select with Replace error
« on: August 02, 2019, 05:09:58 am »
Okay, looking at the MS Access SQL documentation it does seem sparse in terms of add on'd, is it possible to add/define your own functions? Replace?

7
General Board / Re: SQL Select with Replace error
« on: August 01, 2019, 10:15:08 pm »
Fair point. MS Access and the error is DAO.Database [3085] Undefined function 'replace' in expression

8
General Board / SQL Select with Replace error
« on: August 01, 2019, 05:22:30 am » 
Why am I getting SQL errors for the use of REPLACE in EA SQL?

sub searchURLSql ( src_ea_guid, inputUrl )
   dim sSQL, f
   dim xml, xmlDoc
   
   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, "
   f = f & "replace(t_p3.[value],'http://extranet.burts.co.uk','http://supernet.co.uk') AS NewURL "
   
   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 = 'A_Document') AND (t_p3.[Value] IS NOT NULL) AND (t_p3.[Value] = inputURL))"
   
   TRACE ( sSQL )
   
   ' Execute sSQL query
   xml = Repository.SQLQuery (sSQL)
end sub

					

				

				

				
			

		

		

			

				
				

					
9

					

						
General Board / Re: VBScript Excel formatting cells undefined Excel constant

						« on: January 28, 2018, 03:59:57 am »
					

					

					As an interim step incase there isny any !INC I replace the xlSolid enum with its actual value from

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/constants-enumeration-excel
					

				

				

				
			

		

		

			

				
				

					
10

					

						
General Board / Re: VBScript Excel formatting cells undefined Excel constant

						« on: January 28, 2018, 03:51:50 am »
					

					

					Hi there, I thought about that, but its not an Excel question. Under standard VB I wouldn't need to provide anything to access xlSolid, as it would be in the environment. So really I want to know if the EA has a !INC that would provide xlSolid, etc.
					

				

				

				
			

		

		

			

				
				

					
11

					

						
General Board / Re: MS Acess SQL Query vs EA SQL Query returns different results

						« on: January 28, 2018, 02:01:03 am »
					

					

					Query sorted, but by somebody cleverer than me.

select distinct 
   tp3.name as [Process Group]
,null as [In Project Scope (Yes/No)]
,tp2.name as [Process Name]
   ,IIF(ISNULL(po.alias),po.Name,po.alias) As [Process Activity Name]
   ,tEndObj.Name as [Standard Deliverable Name]
   , to2.name as [Responsible]
from ((((((( t_package tp 
inner join t_object po on tp.Package_ID = po.Package_ID)
LEFT join t_object to2 on po.ParentID = to2.object_ID)
INNER join t_connector tc on po.Object_ID = tc.Start_Object_ID)
left join t_package tp2 on tp2.Package_ID = tp.Parent_ID)
left join t_package tp3 on tp3.Package_ID = tp2.Parent_ID)
left join t_package tp4 on tp4.Package_ID = tp3.Parent_ID)
left join t_object tEndObj on tEndObj.Object_ID = tc.End_Object_ID)
where 
po.Object_Type in ('Activity') and tEndObj.Stereotype in ('NATS_Deliverable') and tp4.name in ('Enabling Processes', 'Operating Processes', 'Management and Support Processes')


order by tp3.name, tp2.name, IIF(ISNULL(po.alias),po.Name,po.alias)
					

				

				

				
			

		

		

			

				
				

					
12

					

						
General Board / VBScript Excel formatting cells undefined Excel constant

						« on: January 28, 2018, 01:36:12 am »
					

					

					Hi there,

Using a VBScript i am writing an SQL query of the DB and exporting the output to Excel

set oExcel = CreateObject ("Excel.Application")

With the active workbook selected I want to format the header row, but the use of xlSolid xlCentre,etc generates an error 'undefined variable' Is there something I can import to access xlSolid?

With oWorksheet.Cells.Rows(1)
         .Font.Bold = True
         .Font.Name = "Calibra"
         .Font.Size = 11
'         .Interior.Pattern = xlSolid
         .Interior.Color = 15773696
         .HorizontalAlignment = xlCenter
         .VerticalAlignment = xlCenter
         .WrapText = False
         .Orientation = 0
         .AddIndent = False
         .IndentLevel = 0
         .ShrinkToFit = False
         .ReadingOrder = xlContext
         .MergeCells = False
End With

					

				

				

				
			

		

		

			

				
				

					
13

					

						
General Board / Re: MS Acess SQL Query vs EA SQL Query returns different results

						« on: January 21, 2018, 04:52:37 am »
					

					

					Thx Geert. So with a bit of googling, I now have a SQL query that produces exactly the same result (bar 1 annoying entry), and I think its an EA activity consistency issue.

Here's the latest query: I replaced the Nz with IFF(...) 

select distinct po.ea_guid as CLASSGUID, po.Object_Type as CLASSTYPE, tEndObj.Name as [Deliverable], tEndObj.Stereotype as [StereoType], po.name as [Object Name]
, IIF(po.Alias Is Null, po.Name, po.Alias) As [Acticity Name], tp.name as 'Package Name' ,tp2.name as 'Package level -1',tp3.name as 'Package level -2',tp4.name as 'Package level -3', to2.name as [Role]
from ((((((( t_package tp 
inner join t_object po on tp.Package_ID = po.Package_ID)
inner join t_object to2 on po.ParentID = to2.object_ID)
inner join t_connector tc on po.Object_ID = tc.Start_Object_ID)
left join t_package tp2 on tp2.Package_ID = tp.Parent_ID)
left join t_package tp3 on tp3.Package_ID = tp2.Parent_ID)
left join t_package tp4 on tp4.Package_ID = tp3.Parent_ID)
left join t_object tEndObj on tEndObj.Object_ID = tc.End_Object_ID)
where
po.Object_Type in ('Activity') and tEndObj.Stereotype in ('NATS_Deliverable') and tp4.name in ('Enabling Processes', 'Operating Processes', 'Management and Support Processes')

Now thanks to your efforts, I want to incorporate this SQL with the scripted aspects of EA and automate the Excel output.
					

				

				

				
			

		

		

			

				
				

					
14

					

						
General Board / Re: MS Acess SQL Query vs EA SQL Query returns different results

						« on: January 20, 2018, 05:54:07 am »
					

					

					I will giv en the export mechanism a try. Any ideas about the use if null in an EA SQL query?
					

				

				

				
			

		

		

			

				
				

					
15

					

						
General Board / Re: MS Acess SQL Query vs EA SQL Query returns different results

						« on: January 18, 2018, 06:28:59 am »
					

					

					Apologies in advance, but my next question is related to the difference between the two SQL queries. Is there a way in an EA SQL query to test NULL as per the first Access Nz (ZZ.Alias) function and return something different? There appears to a isNull(xxxx) test, but is it possible to use it to output something other variable. I.e. ZZ.Name, when ZZ.Alias is null?
					

				

				

				
			

		

		

			Pages: [1] 2