How to export history from databases using IDEA script?

20 posts / 0 new
Last post
jennylam
Offline
Joined: 03/11/2017 - 21:18
How to export history from databases using IDEA script?

Hi All,
I completed a few group projects via using IDEA macro, while I need to export the history, PDF version, from databases manaually for documentation purposes.
I read Language Browser for help, and it seems no introduction on that is about history export.
How could I get the history from databases automatically rather than manaually exporting them one by one?
Seeking for your advice.
Regards
 

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Jennylam,

Unfortunately there is no easy way to do this.  IDEA didn't allow us access to reading the history through IDEAScript.  There is a way to get around this but it isn't easy.  If you look in the project folder there is a file called PorjectOverview.sdf which is a SQL Server Compact Database File, in this file there is a field that has the history, this is the history you see when you open the Project Overview.  You can access it via script but it isn't that easy.  I did do something when I was learning about it, I will see if I can find the script.

Brian

jennylam
Offline
Joined: 03/11/2017 - 21:18

Hi Brian,
Any update about accessing PorjectOverview.sdf to get history via script? :)
Thanks and regards

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Jenny,

Sorry I haven't had a chance to look at this.  It will take a bit of time to put this together as I will have to research how to access the sdf file and figure out how to make it work.  Unfortunately this is something that is IDEAScript + in that I have to access other objects.  Hopefully I will have some time in the near future to put this together.

Brian

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Jenny,

Ok, I was able to figure out the code to access the history.  Can you tell me what you are looking for.

Thanks

Brian

scotchy33
Offline
Joined: 09/05/2012 - 15:51

Hi Brian,
Sorry to hijack post, but I would be interested in the Idea code needed to connect to the sdf history file and also the code needed to query against it (i.e. SELECT * FROM ????).  I can do this in excel vba, but haven't figured out how to with IDEA.

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Scot,

I am givng some training right now so when I have a bit of free time I will put something together how you can incorporate this in your scripts.

Thanks

Brian

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Scott,

Here is some code that should get you on the right track.  The sdf database contains two tables, the Overview which I am access and the Parent.  The Parent table shows the parent child relationship between the files through the ChildGUID and ParentGUID.  This shows how to access the table and some basic SQL to read all the records into an array which can be changed to access specific information.  I have also attached the script.  I am using a program called CompactView to view the database.


Option Explicit
'create a type to hold the entire overview table from the database
Type project
	TaskName As String
	DateTime As String
	UserName As String
	IDEAScript As String
	HistoryLog As String
	DataBaseGUID As String
	TaskGUID As String
	RecordGUID As String
	AllRecordsUsed As String
	TaskType As String
	TaskStream As String
	Filename As String
	SubFolder As String
	Unsupported As String
	ProjectName As String
	Deleted As String
End Type
	
Sub Main
	Dim objConn As Object
	Dim connStr As String
	Dim rs As Object
	Dim i As Integer
	Dim ProjectInfo() As project
	Dim bFirstTime As Boolean
	
	bFirstTime = True
	
	ReDim ProjectInfo(0)
	i = 0
	
	'create the connection object to the database
	Set objConn = CreateObject("ADODB.Connection")
		'create the connection string.  The Data source has to point to the sdf file of the project you want to extract the info
		connStr = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Users\eleme\Documents\My IDEA Documents\IDEA Projects\EBP v10.2\ProjectOverview.sdf;"
		'connect to the database
		objConn.open connStr
		'use SQL to access the information.  In this instance all the fields are accessed, I used the field names instead of using SELECT *.*
		Set rs = objConn.execute("SELECT TaskName, DateTime, UserName, IDEAScript, HistoryLog, DatabaseGUID, TaskGUID, RecordGUID, AllRecordsUsed, TaskType, TaskStream, Filename, SubFolder, Unsupported, ProjectName, Deleted FROM Overview")'
			'loop through the table
			Do While Not rs.EOF
				'increment the array to hold the informaiton
				If Not bFirstTime Then
					ReDim preserve ProjectInfo(UBound(ProjectInfo) + 1)
				End If
				'populate the array with the information.
				ProjectInfo(i).TaskName = rs.Fields("TaskName")
				ProjectInfo(i).DateTime = rs.Fields("DateTime")
				ProjectInfo(i).UserName = rs.Fields("UserName")
				ProjectInfo(i).IDEAScript = rs.Fields("IDEAScript")
				ProjectInfo(i).HistoryLog = rs.Fields("HistoryLog")
				ProjectInfo(i).DataBaseGUID = rs.Fields("DatabaseGUID")
				ProjectInfo(i).TaskGUID = rs.Fields("TaskGUID")
				ProjectInfo(i).RecordGUID = rs.Fields("RecordGUID")
				ProjectInfo(i).AllRecordsUsed = rs.Fields("AllRecordsUsed")
				ProjectInfo(i).TaskType = rs.Fields("TaskType")
				ProjectInfo(i).TaskStream = rs.Fields("TaskStream")
				ProjectInfo(i).Filename = rs.Fields("Filename")
				ProjectInfo(i).SubFolder = rs.Fields("SubFolder")
				ProjectInfo(i).Unsupported = rs.Fields("Unsupported")
				ProjectInfo(i).ProjectName = rs.Fields("ProjectName")
				ProjectInfo(i).Deleted = rs.Fields("Deleted")			
				
				i = i + 1
				'move to the next record.
				rs.MoveNext
			Loop
		Set rs = Nothing
	
	Set objConn = Nothing
	MsgBox "Script Complete"

End Sub
scotchy33
Offline
Joined: 09/05/2012 - 15:51

That's awesome.  I know it's already been said, but you are the best.

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Glad to help.

jennylam
Offline
Joined: 03/11/2017 - 21:18

Hi Brian,
Thank you for sharing. I could see the history thru msgbox(ProjectInfo(i).HistoryLog).
But things I still can not figure out is how could I export the full array at one times in a PDF.  Do I have to write in txt line by line first?  Even that, the info been outputed is a clean version without any format, not like the PDF version that I export from IDEA database manually. About file convertion and format things, it will be greatly appreciated if you could give some advices.:)
Kind Regards

Pages