Printable View
Hi,
I am trying to store the contents of the ADODB .CommandText property at run time every time it's value changes.
This will allow me to collect all the used SQL and make a proper impact analysis of our companies proposed system changes.
I have attempted to do this with an Add-In but I don't know enough about writing Add-Ins to pull it off.
It needs to be reusable across hundreds of programs so any ideas very welcome.
Kindest regards,
Matt.
are you doing this in vb6 or vba?
if in vb6 probably try with an activex dll
then use
WithEvents m_ADOConnection As ADODB.Connection
Hi there, thanks for the reply.
Yes I am using VB6.
I am currently trying to do it using an Add-In is that the same as an activeX dll? (Excuse my ignorance this is new ground for me).
I'll look into the WithEvents m_ADOConnection As ADODB.Connection code you suggested and see how I get on. I'll post back with my progress.
Matt.
An Addin is an extension to the VB editor - usually in the form of extra buttons on the toolbar (I've got one which automatically indents code the way I like it, and another that pastes an error handler).
An ActiveX DLL is a VB program which doesn't get run by itself (it gets used by other programs), and can only expose class modules.
Pete's idea seems to be that you make a class in it which uses WithEvents so you can detect when things happen with the connection - but I'm not sure if there events that will be useful for you.
I agree, I know what you mean.
Anyone have any ideas how I can monitor all instances of .CommandText?
What I need is kind of similar to the VB Watch Window but with the added functionality to store the changes in the variables/properties while the code is being run.
Matt.
What I would do is save the values to a text file, which you can look at afterwards, or while in break mode. I doubt you could do it using an Addin (which I think are limited to design-time only), and using a form could be awkward.
I just checked which events are available for the connection, and it seems like _ExecuteComplete could be your best bet - one of the various parameters is an ADODB.Command, which will hopefully be whichever Command object you used.
If that works, you will only need to add a small amount of code to each project - to initiate the DLL, and pass the connection to it.
ok, that sounds promising.
So in the code I need to pass each ADODB connection to the dll to be 'listened into'?
I'll do some reading up on _ExecuteComplete now and post back.
Thanks,
Matt.
I don't know if this will be useful for you but here is how you can keep track of variable changes, store and retrieve them.
Code:Option Explicit
Private mVar As Integer
Private Sub Form_Load()
MyVar = 2
' And to find out the value someplace else
MyVar = GetSetting(App.EXEName, "Saved Variables", "Var One", 0)
MsgBox MyVar
End Sub
Public Property Get MyVar() As Integer
MyVar = mVar
End Property
Public Property Let MyVar(ByVal intNew As Integer)
mVar = intNew
SaveSetting App.EXEName, "Saved Variables", "Var One", mVar
End Property
Hi Martin,
Thank you for the reply, I dno't think it is quite what I'm after because I can't easily initiate this for every project I need to test and it is also difficult to implement because I will have several different ADODB objects in each project.
Basically I need an alternate version of the Watch Window which dumps the values of variables and properties instead of displaying them on screen.
Anyone know how this is done?
Matt.
you could use a timer, check if the variable changes then write it out to file or text box, whatever
I briefly tested the following code and it seemed to worked, have a look!
Class1 (just a sample)
Sample usageCode:Option Explicit
Private WithEvents Conn As ADODB.Connection
Private Sub Class_Initialize()
Set Conn = New ADODB.Connection
Conn.ConnectionString = ConnString
Conn.Open
End Sub
Private Sub Class_Terminate()
If Not Conn Is Nothing Then
If Conn.state = adStateOpen Then
Conn.Close
End If
End If
Set Conn = Nothing
End Sub
Public Property Get AdoConnection() As ADODB.Connection
Set AdoConnection = Conn
End Property
Private Sub Conn_WillExecute(Source As String, CursorType As ADODB.CursorTypeEnum, LockType As ADODB.LockTypeEnum, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
MsgBox Source, , "Source"
End Sub
Code:Private Sub Command1_Click()
Dim adoR As ADODB.Recordset
Dim adoC As ADODB.Command
Dim x As Class1
Set x = New Class1
Set adoC = New ADODB.Command
With adoC
Set .ActiveConnection = x.AdoConnection
.CommandText = "SELECT TOP 1 * FROM tabPatientRecords"
.CommandType = adCmdText
Set adoR = .Execute
End With
End Sub
here is a function you could add to dee-u code
vb Code:
Hi,
I really appreciate everyones input to this problem, thank you.
The trouble I have is that all these solutions require changes to the actual code of the program to be tested and as I am dealing with hundreds of programs it just isn't feasible.
That's why I thought about an ActiveX Add-In or some such thing. Trouble is I have a feeling Add-In's can only function at design time (though if someone knows different please tell me).
This seems such a simple thing to want to do but yet there is nothing online about it.
I have seen software available to buy which emulates the VB Watch Window with extended functionality but even this doesn't appear to have a 'dump to file' feature.
If I could only write a simple version of the Watch Window myself I can easily include a function to dump all 'watched' variables and properties to file.
It's so frustrating.
i do not believe there is anyway to do what you want without some changes to the program, adding an object to an external activex seemed like the minimum amout of code within existing applications, to build a watch window would still have to be done within the existing program, or in an activex, then create an object of it within the program
I take your point but the Watch Window isn't part of a project is it.
The pay-for extended Watch windows are not part of a project either, they just interact with the project from the IDE.
That's what I want to achieve, I can't beleive it's that difficult :-(
i think all replying here thought you wanted it to work in your compiled program
Yes, I think you are right, I just assumed I'd made it clear with:
andQuote:
I have attempted to do this with an Add-In but I don't know enough about writing Add-Ins to pull it off.
It needs to be reusable across hundreds of programs so any ideas very welcome.
So, to clarify, I really need to be able to programatically nominate several ***.CommandText properties to ""Watch"" and while running the program (from the compiler) I need to dump the changing value of these various properties (in their various contexts.).Quote:
What I need is kind of similar to the VB Watch Window but with the added functionality to store the changes in the variables/properties while the code is being run.
Please, if anyone knows, this would make a huge difference to me.
Thanks,
Matt.
What dee-u posted is on a similar line to what I suggested before, but requires more work to integrate it.
This modification is more like what I was thinking for the class/DLL, which should monitor all command/recordset objects that use a connection object:
And the usage in each of your projects would be something like this:Code:Option Explicit
Private WithEvents Conn As ADODB.Connection
Private Sub Class_Terminate()
Set Conn = Nothing
End Sub
Public Property Set AdoConnection(AdoConnection As ADODB.Connection)
Set Conn = AdoConnection
End Property
Private Sub Conn_WillExecute(Source As String, CursorType As ADODB.CursorTypeEnum, LockType As ADODB.LockTypeEnum, Options As Long, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
'whatever logging features you want here
MsgBox Source, , "Source"
End Sub
If you have more than one connection object in a project, simply repeat the usage section for each of them.Code:'in the same declarations section as the connection (change Public to Private/Dim if apt)
Public adoLogger As YourClassName
'Just after initialising the connection object ("Set yourConnection = New ... ")
Set adoLogger = New YourClassName
Set adoLogger.AdoConnection = yourConnection
'Just before terminating the connection object ("Set yourConnection = Nothing")
Set adoLogger = Nothing
An alternative would be to create a class which is a wrapper for the connection object (so you simply need to change the declaration for your connection objects), however you would have to put a lot of effort either into the class (to wrap each function/property your programs use) or modifying the usage (admittedly that could be a find/replace, but I wouldn't be keen on doing it).
Hi,
These are all feasible solutions but they aren't quite what I'm after. Only because they would require too much fiddling with existing projects.
This is a large company with many many users and many many programs. The logistics would be too much.
To put this simply, I need the exact same functionality as the Watch Window but with the ability to dump to file the changes in variable values without breaking the code.
This is all going to be tested before the project is compiled to an exe.
I hope this makes sense and I really hope smoeone out there has the solution I need as it would make a HUGE difference to me.
Thanks, Matt.
We are aware of what you are dreaming of, but it seems that none of us have ever seen something like that, and can't think of a way to create exactly what you want.
I don't know why you think the posted example would be too much fiddling - for each project it just requires adding a class (either directly into the project, or via a DLL you Reference) and four lines of code per connection object. Due to the usage of the class, those four lines could even be reduced to two by using Dim as New (which is normally a bad idea, but might be apt here).
I accept that and I am very grateful to everyone for their input.
The reason I say it's too much fiddling is that we have about 400 individual programs, these have (and I quote a rough average here) about 10 DIFFERENT unique connections to the database, some less obviously and some many more.
All the projects are controled by eChangeMan as a source control tool which adds another level of complexity. It's possible but just a whole lot of effort.
I know I'm probably being lazy but I have a lot of other things going on too :-)
Is it not possible to write something which listens in to a compiled exe and checks for SQL calls being parsed to the ODBC driver?
Regards,
Matt.
There might be a way of writing some kind of wrapper for an ODBC Driver/OLEDB Provider, but it would not be easy at all (and it is likely to be almost undocumented).
There might be a program out there already that does it, and if memory serves ODBC itself provides a logging feature (via the control panel applet), but using it slows database interaction down dramatically.
I expect it would be :-(Quote:
Originally Posted by si_the_geek
I have tried this and yes, it slows function down way way too much but interesting ly it didn't actually catalogue the SQL strings!?!?!?!? This was a bit confusing.Quote:
Originally Posted by si_the_geek
Matt.
Another idea could be a packet sniffer that would track the sql statements being passed by your program though I am certain that would be difficult. When I had the need to use a packet sniffer I believe my sql statements are also being sniffed.