ADODB Proper Closing - Memory Release

[randym]

I have written an Autoit script that validates the data against a DSN prior to data entry.  The validation part works fine, but I began to notice that the RAM on the server was not being released and eventually caused the server to require a reboot. Below is the function that I have been using

expandcollapse popup

Func Validate($adoSQL)
    ; ODBC System DSN definition
    $DSN = "DSN=Charm";  database as defined in ODBC

    $adoCon = ObjCreate("ADODB.Connection")
    $adoCon.Open($DSN)
    $adoRs = ObjCreate("ADODB.Recordset") ; Create a Record Set - SELECT SQL

    $adoRs.CursorType = 0 ; Forward Pass thru recordset only
    $adoRs.LockType = 1 ; Read Only

    ; execute the SQL
    $adoRs.Open($adoSQL, $adoCon)
    $found = 0
    With $adoRs
        $dimension = .Fields.Count
        If .RecordCount Then
            While Not .EOF
                For $i = 0 To .Fields.Count - 1
                    Switch $i
                        Case 0
                            $div = .Fields($i).Value
                        Case 1
                            $cust = .Fields($i).Value
                        Case 2
                            $name = .Fields($i).Value
                            $found = 1
                    EndSwitch
                Next
                .MoveNext
            WEnd
        EndIf
    EndWith

    $adoRs.Close ; close record set
    $adoCon.Close ; close connection
    Return $found
EndFunc   ;==>Validate

Several other functions build SQL statements and call this function for various data validation and it works well in that regard.  Researching this issue, I could not help but notice the similarities in this syntax and that of VBS with one exception.  Standard procedure in VBS would be to set the database object variables to Nothing after the 'close' statements.

For example ...

Set adoRS = Nothing

Set adoCon = Nothing

The purpose of these statements in VBS, as I understand it, is to release the resources that are in use which would free up memory.  

My question is what is the correct method for releasing these resources in AutoIT scripting?

Thanks, Randy

Edited October 23, 2015 by randym

[mLipok]

I'll try to look in it later (evening).

 

[mLipok]

What server type ? Is it MS SQL - Which version ?
Why you think this is unexpected behavior ?
Do you have the same script in VBS and this Server behavior is not occur ?

Edited October 23, 2015 by mLipok

[randym]

Server Type - Windows 2003 Server

It is not MS SQL - It is Providex 

I think this is unexpected because this application has been running on this server for many years and has not had this particular issue ever.  The ODBC io service on the server (pvxiosrv.exe) that provides read only data grows in memory size as these scripts are run.  I am attempting to document the memory growth of the service when these processes are run, however it is difficult as this is a live server.  I will attempt to capture some screen shots to document this growth over the weekend.  The DSN is used constantly by Crystal reports which is the primary reporting mechanism in this application and I use the DSN through Excel quite frequently for adhoc queries and these issues do not occur. 

I have created a small test AutoIT script that simply validates a single customer and have duplicated that functionality in VBS to attempt to document the memory growth issue.  They are included in this email, but likely, you do not have a similar Providex environment for testing on your end.  

Let me know if there is anything on this end I can do to assist....

Thanks, Randy

expandcollapse popup

'VBScript ado testing
Dim DSN, adoCon, adoRS, adoSQL, myfound, i, div, cust, name, Mas_Cust

DSN = "DSN=Charm"
Mas_Cust = "010000047"

Set adoCon = CreateObject("Adodb.Connection")
adoCon.Open DSN
Set adoRS = CreateObject("Adodb.Recordset")

adoSQL = "SELECT AR_Customer.ARDivisionNo, AR_Customer.CustomerNo, AR_Customer.CustomerName FROM AR_Customer AR_Customer WHERE (AR_Customer.CustomerNo='0000047')"

adoRs.Open adoSQL, adoCon
myfound = 0

    With adoRs
        If .RecordCount Then
            While Not .EOF
                For i = 0 To .Fields.Count - 1
                    Select Case i
                        Case 0
                            div = .Fields(i).Value
                        Case 1
                            cust = .Fields(i).Value
                        Case 2
                            name = .Fields(i).Value
                            myfound = 1
                    End Select
                Next
                .MoveNext
            WEnd
        End If
    End With

    If myfound = 1 Then
        MsgBox "Found " & div & "-" & cust & "  " & name
    Else
        MsgBox "Not Found " & Mas_Cust
    End If

    adoRs.Close
    adoCon.Close
Set adoRS = Nothing
Set adoCon = Nothing

 

expandcollapse popup

;AutoIT ado testing
Global $Mas_Cust = '010000047'
Validate_Cust()

Func Validate_Cust()
    ; ODBC System DSN definition
    $DSN = "DSN=Charm";  database as defined in ODBC

    $adoCon = ObjCreate("ADODB.Connection")
    $adoCon.Open($DSN)
    $adoRs = ObjCreate("ADODB.Recordset") ; Create a Record Set - SELECT SQL

    ; create the SQL statement
    $adoSQL = "SELECT AR_Customer.ARDivisionNo, AR_Customer.CustomerNo, AR_Customer.CustomerName  FROM AR_Customer AR_Customer  WHERE (AR_Customer.CustomerNo='" & StringMid($Mas_Cust, 3) & "')"
    ;MsgBox(0, '', $adoSQL)

    $adoRs.CursorType = 0
    $adoRs.LockType = 1

    ; execute the SQL
    $adoRs.Open($adoSQL, $adoCon)
    $found = 0
    With $adoRs
        $dimension = .Fields.Count
        If .RecordCount Then
            While Not .EOF
                For $i = 0 To .Fields.Count - 1
                    Switch $i
                        Case 0
                            $div = .Fields($i).Value
                        Case 1
                            $cust = .Fields($i).Value
                        Case 2
                            $name = .Fields($i).Value
                            $found = 1
                    EndSwitch
                Next
                .MoveNext
            WEnd
        EndIf
    EndWith
    If $found = 1 Then
        MsgBox(0, 'Debug', "Found " & $div & '-' & $cust & '  ' & $name)
    Else
        MsgBox(0, 'Debug', "Not Found " & $Mas_Cust)
    EndIf
    $adoRs.Close ; close record set
    $adoCon.Close ; close connection
EndFunc   ;==>Validate_Cust

 

[mLipok]

Try something like this:

$adoRs.Close ; close record set
    $adoCon.Close ; close connection
    ; try this
    $adoRs = Null
    $adoCon = Null
EndFunc   ;==>Validate_Cust

Did you in teh mean time, change AutoIt version ? From  ? to ?
Is it 64Bit Windows () ?
Can you compile your script as 64 Bit ?
Did you recently update Providex  ?
Can you give any link to documentation for Providex  ?

Sorry but my English is not so perfect so So I could have missed something, and because of that I want to ask: 
Which process are taking so much memory ?
Are you sure this is only because of AutoIt script ?

btw. You do not must make screenshot, just try to make functions to report memory usage.

 

mLipok

[randym]

I will try the 'Null' statements.

I downloaded and installed latest AutoIT on Windows 7 Pro 64bit machine in the last 6 weeks or so... I have not looked for another one.  I did not compile as 64 bit.  No updates to Providex.  Manual for syntax at http://docs.pvxplus.com/PVXV7.pdf 

This is a server side ODBC service that the workstations connect to through their DSN ... I have attached the installation and configuration documentation in the interest of completeness.  

Since this service is server sided and all workstations connect to it from time to time, I expect it to use server resources during execution and release them upon termination.  Since the service is on the server, I do not see any way to get the client workstations to report on memory usage.  

ConfigureClientServerODBCDriver.pdf

[mLipok]

I will try the 'Null' statements.

I'll wait for your results.
From my side this is all for now.

mLipok

[randym]

mLipok - 

OK - added the Null statements to the script below...

expandcollapse popup

Global $Mas_Cust = '010000047'
Validate_Cust()

Func Validate_Cust()
    ; ODBC System DSN definition
    $DSN = "DSN=Charm";  database as defined in ODBC

    $adoCon = ObjCreate("ADODB.Connection")
    $adoCon.Open($DSN)
    $adoRs = ObjCreate("ADODB.Recordset") ; Create a Record Set - SELECT SQL

    ; create the SQL statement
    $adoSQL = "SELECT AR_Customer.ARDivisionNo, AR_Customer.CustomerNo, AR_Customer.CustomerName  FROM AR_Customer AR_Customer  WHERE (AR_Customer.CustomerNo='" & StringMid($Mas_Cust, 3) & "')"
    ;MsgBox(0, '', $adoSQL)

    $adoRs.CursorType = 2
    $adoRs.LockType = 3

    ; execute the SQL
    $adoRs.Open($adoSQL, $adoCon)
    $found = 0
    With $adoRs
        $dimension = .Fields.Count
        If .RecordCount Then
            While Not .EOF
                For $i = 0 To .Fields.Count - 1
                    Switch $i
                        Case 0
                            $div = .Fields($i).Value
                        Case 1
                            $cust = .Fields($i).Value
                        Case 2
                            $name = .Fields($i).Value
                            $found = 1
                    EndSwitch
                Next
                .MoveNext
            WEnd
        EndIf
    EndWith
    If $found = 1 Then
        MsgBox(0, 'Debug', "Found " & $div & '-' & $cust & '  ' & $name)
    Else
        MsgBox(0, 'Debug', "Not Found " & $Mas_Cust)
    EndIf
    $adoRs.Close ; close record set
    $adoCon.Close ; close connection
    $adoRs = Null
    $adoCon = Null
EndFunc   ;==>Validate_Cust

Unfortunately, my testing does show this has had any impact on the issue....

I am sending three screen shots of the memory usage of the pvxiosvr.exe service.  First is prior to any tests and with minimal users on the system and NONE of them running AutoIT in any capacity. The next screen shot is from immediately after the test VBS script has been run.  And finally, after the script listed above has been run.  Also, the memory size grows with each iteration of validation executed, so if I attempt to validate a set of data with 500 lines in it, 4 or 5 validations per line, the memory usage can easily grow into the GB range and left unchecked, will cause our server to crash.  Stopping and starting the service will release the resources, but is a band aid to the problem.

 

 

[water]

Release a COM object by assigning it any number or any text value. I'm not sure Null has the same effect. So could you please test with a number?

Local $oHTTP = ObjCreate("winhttp.winhttprequest.5.1") ; Object is created
$oHTTP = 0 ; Object is deleted

 

[mLipok]

I want to ask:
Can you open this connection only once and then proceed all this checking, I mean with out closing and opening new connection ?

and next question:

What happend when you validate 10 lines and close AutoIt compiled script ? Is this memory usage going down ?

 

[randym]

Water - I can give your technique a try and see how it goes....

mLipok - It would require some fairly significant surgery on my process to open and close only once, but it might be worthwhile.... I will test Water's suggestion first though.  As each line is processed and validated, the memory usage ramps up and once the process completes it remains where ever it is.  The usage does not go down.  Although the test script is not compiled, the full live process is compiled and I have seen no difference in this issue from compiled to not compiled.

[mLipok]

This is not so hard.

expandcollapse popup

Global $adoCon = ObjCreate("ADODB.Connection")
; ODBC System DSN definition
Global $DSN = "DSN=Charm";  database as defined in ODBC
$adoCon.Open($DSN)

Validate_Cust('010000047', False)
Validate_Cust('010000047', True)

Func Validate_Cust($Mas_Cust, $bGetRows = False)
    Local Enum _
            $eARDivisionNo, _
            $eCustomerNo, _
            $eCustomerName, _
            $eFIELDMAX
    ; create the SQL statement
    Local $adoSQL = "SELECT AR_Customer.ARDivisionNo, AR_Customer.CustomerNo, AR_Customer.CustomerName  FROM AR_Customer AR_Customer  WHERE (AR_Customer.CustomerNo='" & StringMid($Mas_Cust, 3) & "')"
    Local $adoRs = $adoCon.Execute($sQuery)
    If @error Then Return SetError(@error, @extended, 0)

    $iFields_Count = $adoRs.Fields.Count
    $iRows_Count = $adoRs.Fields.Count

    Local $iFound = 0
    Local $aResults
    If $bGetRows Then
        $aResults = $adoRs.GetRows()
        If UBound($aResults) Then
            For $iRow = 0 To UBound($aResults) - 1
                #cs ; here you must make your MAGIC ;)
                    $aResults[$iRow][$eARDivisionNo]
                    $aResults[$iRow][$eCustomerNo]
                    $aResults[$iRow][$eCustomerName]

                #ce
            Next
        EndIf
    Else
        With $adoRs
            If $iRows_Count Then
                While Not .EOF
                    For $i = 0 To $iFields_Count - 1
                        Switch $i
                            Case 0
                                $div = .Fields($i).Value
                            Case 1
                                $cust = .Fields($i).Value
                            Case 2
                                $name = .Fields($i).Value
                                $iFound = 1
                        EndSwitch
                    Next
                    .MoveNext
                WEnd
            EndIf
        EndWith

    EndIf
    If $iFound = 1 Then
        MsgBox(0, 'Debug', "Found " & $div & '-' & $cust & '  ' & $name)
    Else
        MsgBox(0, 'Debug', "Not Found " & $Mas_Cust)
    EndIf
    $adoRs.Close ; close record set
    $adoCon.Close ; close connection
    $adoRs = Null
    $adoCon = Null
EndFunc   ;==>Validate_Cust

 

Edited October 27, 2015 by mLipok

[randym]

Sorry for the confusion... I was referring to the changes to the actual live executables that are built using this function, not these little test scripts.  

As it turns out, the combination of Waters suggestion and yours has had a positive impact on this issue.  Only creating the adoCon object once and closing it once does seem to use far less resources.  Testing looks very promising.  I have just completed changing a number of executables in this way and compiling for end user use.  I will monitor things and let you know how it goes.  Thanks to both you and Water for your assistance in this matter.

[water]

Glad to be of service  

[mLipok]

I will monitor things and let you know how it goes. 

I'll be glad to be able to hear the finale of this story and get some feedback.

 

Thanks to both you and Water for your assistance in this matter.

Your are welcome.

 

mLipok

 

 

 

EDIT:
Btw.
focus on this:

$aResults = $adoRs.GetRows()
        If UBound($aResults) Then
            For $iRow = 0 To UBound($aResults) - 1
                #cs ; here you must make your MAGIC ;)
                    $aResults[$iRow][$eARDivisionNo]
                    $aResults[$iRow][$eCustomerNo]
                    $aResults[$iRow][$eCustomerName]

                #ce
            Next
        EndIf

 

as .GetRows() methode is much more faster.

 

 

Edited October 27, 2015 by mLipok

[randym]

I was not aware of that method... Thanks for the tip!

[randym]

mLipok - I have been playing with your GetRows() suggestion and I like it.  Just wanted to let you know. I haven't been able to demonstrate any speed advantages yet, (my test data is somewhat limited), but that is just icing on the cake.  I should be able to get a better idea tomorrow when this is unleashed on the troops.

As for the memory issue - at this time of day, the io service would be tying up several hundred meg of memory, typically.  Today - less than 19K   Thanks again for helping me put this to bed.

I am looking forward to my next project that will more fully exercise SQL execution and return sets of data.

 

Edited October 28, 2015 by randym

[mLipok]

I'm glad to hear that.

mLipok