How to export history from databases using IDEA script?

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

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

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