Store variable content at runtime.

[lawsonium]

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.

[westconn1]

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

[lawsonium]

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.

[si_the_geek]

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.

[lawsonium]

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.

[si_the_geek]

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.

[lawsonium]

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.

[MartinLiss]

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.

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

[lawsonium]

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.

[westconn1]

you could use a timer, check if the variable changes then write it out to file or text box, whatever

[dee-u]

I briefly tested the following code and it seemed to worked, have a look!

Class1 (just a sample)

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

Sample usage

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

[westconn1]

here is a function you could add to dee-u code

Public Function GetADO_Error() As String
 
    Dim i As Integer
    Dim sQuery As String
    Dim nNativeError As Long
    Dim ADORecordset As ADODB.Recordset
 
    GetADO_Error = ""
    
    If (m_ADOConnection.Errors.Count = 0) Then Exit Function
    
    For i = 0 To m_ADOConnection.Errors.Count - 1
        If (InStr(m_ADOConnection.Errors(i).Description, "Transaction rollback") > 0) Then
            nNativeError = m_ADOConnection.Errors(i).NativeError
            Exit For
        End If
    Next i
        
    If ((nNativeError >= 10000) And (nNativeError < 20000)) Then 'Error
       GetADO_Error = "Error: " & Trim(Str(nNativeError))
       nNativeError = nNativeError - 10000
       sQuery = "Select Description from ImportErrors where ImportErrorID = " & Trim(Str(nNativeError))
    ElseIf ((nNativeError >= 1) And (nNativeError < 10000)) Then 'Warning
       sQuery = "Select Description from ImportWarnings where ImportWarningID = " & Trim(Str(nNativeError))
       GetADO_Error = "Warning: " & Trim(Str(nNativeError))
    ElseIf (nNativeError >= 20000) Then
       sQuery = "Select Description from InternalODBCErrors where NativeErrorNumber = '" & Trim(Str(nNativeError) & "'")
       GetADO_Error = Trim(Str(nNativeError))
    Else
        GetADO_Error = Err.Description
        Exit Function
    End If
    
    Set ADORecordset = m_ADOConnection.Execute(sQuery)
    GetADO_Error = GetADO_Error & " - " & ADORecordset.Fields("Description").Value
    ADORecordset.Close
    
End Function

[lawsonium]

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.

[westconn1]

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

[lawsonium]

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 :-(

[westconn1]

i think all replying here thought you wanted it to work in your compiled program

[lawsonium]

Yes, I think you are right, I just assumed I'd made it clear with:

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.

and

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.

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.).

Please, if anyone knows, this would make a huge difference to me.

Thanks,

Matt.

[si_the_geek]

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:

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

And the usage in each of your projects would be something like this:

'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

If you have more than one connection object in a project, simply repeat the usage section for each of them.


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).

[lawsonium]

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.

[si_the_geek]

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).

[lawsonium]

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.

[si_the_geek]

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.

[lawsonium]

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).

I expect it would be :-(

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 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.

Matt.

[dee-u]

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.