randym

ADODB Proper Closing - Memory Release

18 posts in this topic

#1 ·  Posted (edited)

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

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 by randym

And the sign said 'Long Hairded Creepy People Need Not Apply' ... So I stuffed my hair up under my hat and I went in to ask him why?

Share this post


Link to post
Share on other sites



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

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by mLipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

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

'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

 

;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

 


And the sign said 'Long Hairded Creepy People Need Not Apply' ... So I stuffed my hair up under my hat and I went in to ask him why?

Share this post


Link to post
Share on other sites

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


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

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


And the sign said 'Long Hairded Creepy People Need Not Apply' ... So I stuffed my hair up under my hat and I went in to ask him why?

Share this post


Link to post
Share on other sites

I will try the 'Null' statements.

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

mLipok


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

mLipok - 

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

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.

 

 

Screen Shot 2015-10-26 at 8.32.22 AM Before Test.png

Screen Shot 2015-10-26 at 8.33.05 AM After VBS Test.png

Screen Shot 2015-10-26 at 8.33.41 AM After AutoIT Test.png


And the sign said 'Long Hairded Creepy People Need Not Apply' ... So I stuffed my hair up under my hat and I went in to ask him why?

Share this post


Link to post
Share on other sites

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 ?

 


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

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.


And the sign said 'Long Hairded Creepy People Need Not Apply' ... So I stuffed my hair up under my hat and I went in to ask him why?

Share this post


Link to post
Share on other sites

#12 ·  Posted (edited)

This is not so hard.

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 by mLipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

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.


And the sign said 'Long Hairded Creepy People Need Not Apply' ... So I stuffed my hair up under my hat and I went in to ask him why?

Share this post


Link to post
Share on other sites

Glad to be of service :) 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#15 ·  Posted (edited)

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 by mLipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

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


And the sign said 'Long Hairded Creepy People Need Not Apply' ... So I stuffed my hair up under my hat and I went in to ask him why?

Share this post


Link to post
Share on other sites

#17 ·  Posted (edited)

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 by randym

And the sign said 'Long Hairded Creepy People Need Not Apply' ... So I stuffed my hair up under my hat and I went in to ask him why?

Share this post


Link to post
Share on other sites

I'm glad to hear that.

mLipok


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now