How to export history from databases using IDEA script?
Forums
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
Hi Jenny,
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
Hi Scott,
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
Hi Brian,
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
Hi Jennylam,
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