ADO.au3 UDF - BETA - Support Topic

[mLipok]

On 10/18/2020 at 9:15 PM, mLipok said:

haha...

I just bougth this book:

https://www.ebay.com/itm/302366891714

I hope soon I find time to provide new features and examples based on the knowledge which I gather thanks to this above mentioned book.

 

btw.
Today I also buy this one:

 

I hope it will be delivered to me sometime in a month, so it is possible that after the summer vacation, sometime in about half a year, I will have the opportunity to present new curiosities.

 

Edited February 15, 2021 by mLipok

[Skysnake]

@mLipok Michal I have a problem

Upgraded Autoit. Downloaded ADO zip

I can delete the offending lines, they appear to be cosmetic, but why am I getting this?

 

[Skysnake]

@mLipok 

I seems I am struggling with the download page.

There are multiple instances of everything. 

Which is the current "stable" version?

It feels as if I am going backwards here

 

[Skysnake]

@mLipok

OK. Downloaded the most resent beta and things are almost back to normal.

I am getting an error 

; my code
; SetUP internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

 

Console

error: _ADO_COMErrorHandler(): undefined function.

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

 

Note that all of this worked until I started the update 😐

 

Found this 

; SetUP internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

 

 

Edited March 22, 2021 by Skysnake

[mLipok]

@Skysnake in shortcut.... do you need my help or all ADO works on your side ?

Edited March 23, 2021 by mLipok

[Skysnake]

@mLipok I finally got it to work. May I suggest a "current recommended download" option? Or similar?  My problem was that I started at the top of the list, when I should just have gone down to the most recent.

All is well now.

Thanks for asking.

[mLipok]

This is how "Upload new version" and "Download" features works.
Nothing more.
The only things which I could do would be video tutorial on YT "How to download files from AutoIt forum Download section" 

 

Edited March 24, 2021 by mLipok

[Skysnake]

😏

[Lecdev]

Has anyone got any examples working with BLOBs ( Varbinary(max) ) on MSSQL with ADO?

I've done with SQLite which is allot easier for this specifically and now i have a few things to migrate to sql server, my brain is melting.

[mLipok]

Please create SQL statement to create Example database.

Then I provide Example how to put PDF file as a blob.

[Lecdev]

expandcollapse popup

#include <ADO.au3>

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

_BLOB_Example()



Func _BLOB_Example()
    _MSSQL_CreateDatabaseWithNameAndPath()
    _MSSQL_CreateBlobTable()
    Local $hFileOpened = FileOpen("SomePdf.pdf", 16) ; opened in  $FO_BINARY mode
    Local $bFileData = FileRead($hFileOpened) ; whole file in binary
    FileClose($hFileOpened)
    _MSSQL_InsertFileBinary($bFileData)
    $bFileData = Null
    _MSSQL_ReadFileBinary($bFileData)
    $hFileOpened = FileOpen("SomePdfNewCopy.pdf", 26) ; opened in  ($FO_BINARY, $FO_OVERWRITE, $FO_CREATEPATH) mode
    FileWrite($hFileOpened, $bFileData)
    If Not @Compiled Then ConsoleWrite("Done, Exiting in 2 Seconds")
    ;
    Sleep(2000)
    Exit
    ;
EndFunc   ;==>_BLOB_Example

Func _MSSQL_CreateDatabaseWithNameAndPath($NewDbName = "Test_db", $DirectoryPath = @ScriptDir, $Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    If $NewDbName = "" Then Return SetError(-1, -2, $ADO_RET_FAILURE)
    If $DirectoryPath = "" Then Return SetError(-2, -2, $ADO_RET_FAILURE)
    ; note *** directories must exist first, use DirGetSize() to ensure the path exists then if not create with DirCreate()
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=master;UID=' & $User & ';PWD=' & $Pwd & ';'
    Local $commandstr = "select name from master.dbo.sysdatabases where name='" & $NewDbName & "'"
    Local $CreateNew = True
    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()
    ; Open connection with $sConnectionString
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    ; check if database exists already
    Local $oRecordset_Final = _ADO_Execute($oConnection, $commandstr)
    Local $aRowsResult = _ADO_Recordset_ToArray($oRecordset_Final)
    If IsArray($aRowsResult) Then
        Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
        If IsArray($aRecordsetContent) Then
            If $aRecordsetContent[0][0] = $NewDbName Then
                $CreateNew = False ; already exists
            EndIf
        EndIf
    EndIf
    If $CreateNew Then ; doesnt exist, create it
        $commandstr = "CREATE DATABASE " & $NewDbName & " ON PRIMARY " & _
                "( NAME = " & $NewDbName & ", " & _
                "FILENAME = '" & $DirectoryPath & "\" & $NewDbName & ".mdf', " & _
                "SIZE = 2MB, MAXSIZE = 100MB) " & _
                "LOG ON (NAME = " & $NewDbName & "_log, " & _
                " FILENAME = '" & $DirectoryPath & "\" & $NewDbName & "_log.ldf', " & _
                " SIZE = 2MB, " & _
                " MAXSIZE = 100MB )"
        Local $oCommand = _ADO_Command_Create($oConnection)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
        Local $Result = _ADO_Command_Execute($oCommand, $commandstr)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
        If Not @Compiled Then ConsoleWrite("succesfully created database !" & @CRLF)
    Else
        If Not @Compiled Then ConsoleWrite("database exists already ! No need to create !" & @CRLF)
    EndIf
    ; Clean Up
    $oRecordset_Final = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc   ;==>_MSSQL_CreateDatabaseWithNameAndPath

Func _MSSQL_CreateBlobTable($DbName = "Test_db", $Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=' & $DbName & ';UID=' & $User & ';PWD=' & $Pwd & ';'
    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    Local $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    If @error >= $ADO_ERR_RECORDSETEMPTY Then
        _ADO_Execute($oConnection, "CREATE TABLE FILE_BIN_DATA (ROWID int IDENTITY(1,1) NOT NULL" & _
                ", File_Name Varchar(60) NOT NULL" & _
                ", File_Data Varbinary(max) NULL" & _
                ", CONSTRAINT [PK_FILE_BIN_DATA] PRIMARY KEY CLUSTERED " & _
                "(" & _
                "   ROWID ASC " & _
                ")) " & _
                "ON [PRIMARY]")

        If @error Then
            If Not @Compiled Then ConsoleWrite('! ---> @error=' & @error & '  @extended=' & @extended & ' : CREATE TABLE' & @CRLF)
        EndIf
        $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    EndIf
    If Not @Compiled Then ConsoleWrite(_ADO_Recordset_ToString($oRecordset_Tables))
    ; Clean Up
    $oRecordset_Tables = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc   ;==>_MSSQL_CreateBlobTable

Func _MSSQL_InsertFileBinary(ByRef $bFileBinaryIn)
    ;; do some insert command/ stream
EndFunc   ;==>_MSSQL_InsertFileBinary

Func _MSSQL_ReadFileBinary(ByRef $bFileBinaryOut)
    ; do some select command/ stream
EndFunc   ;==>_MSSQL_ReadFileBinary

 

here is some code, looking forward to some help.

[Skysnake]

Personally I do not like BLOBs, try bytea

[mLipok]

On 8/27/2021 at 10:32 PM, Lecdev said:

here is some code, looking forward to some help.

Working on..... stay tuned

 

Edited August 28, 2021 by mLipok

[mLipok]

To understand it well, I initially refactored it to my approach ( a way of writing code ).
This is not a working version of your example yet, but only an intermediate version.

expandcollapse popup

#include <FileConstants.au3>
#include <MsgBoxConstants.au3>
#include "ADO.au3"

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

_BLOB_Example()
If @error Then ConsoleWrite("! ---> @error=" & @error & "  @extended=" & @extended & _
        " : Error in example" & @CRLF)

Exit

Func _BLOB_Example()
    _MSSQL_CreateDatabaseWithNameAndPath()
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _MSSQL_CreateBlobTable()
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; read whole file in binary
    Local $hFileOpened = FileOpen("SomePdf.pdf", $FO_BINARY)
    Local $bFileData = FileRead($hFileOpened)
    FileClose($hFileOpened)

    _MSSQL_InsertFileBinary($bFileData)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $bFileData = Null

    _MSSQL_ReadFileBinary($bFileData)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; write binary content to new file
    $hFileOpened = FileOpen("SomePdfNewCopy.pdf", $FO_BINARY + $FO_OVERWRITE + $FO_CREATEPATH)
    FileWrite($hFileOpened, $bFileData)
    If Not @Compiled Then ConsoleWrite("Done, Exiting in 2 Seconds")

    ; final check - open file in associated program
    If $IDYES = MsgBox($MB_YESNO + $MB_TOPMOST + $MB_ICONQUESTION + $MB_DEFBUTTON1, "Question", _
            "Do you wan try to open saved file ?") Then
        ShellExecute("SomePdfNewCopy.pdf")
    EndIf


EndFunc   ;==>_BLOB_Example

Func _MSSQL_CreateDatabaseWithNameAndPath($NewDbName = "Test_db", $DirectoryPath = @ScriptDir, $Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    ; parameters validation
    If $NewDbName = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM1, $ADO_RET_FAILURE)
    If $DirectoryPath = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM2, $ADO_RET_FAILURE)

    If $Pwd = "" Then $Pwd = "AutoIt" ; this is my temp passwd for this example project

    ; note *** directories must exist first, use DirGetSize() to ensure the path exists then if not create with DirCreate()

    ; Create connection and open with $sConnectionString
    Local $oConnection = _ADO_Connection_Create()
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=master;UID=' & $User & ';PWD=' & $Pwd & ';'
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; check if database exists already
    Local $commandstr = _
            "SELECT" & @CRLF & _
            "   [name]" & @CRLF & _
            "FROM" & @CRLF & _
            "   [master].dbo.[sysdatabases]" & @CRLF & _
            "WHERE" & @CRLF & _
            "   [name]='" & $NewDbName & "'" & @CRLF & _
            ""
    Local $oRecordset_Final = _ADO_Execute($oConnection, $commandstr)
    Local $aRowsResult = _ADO_Recordset_ToArray($oRecordset_Final)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $CreateNew = Not ($aRecordsetContent[0][0] == $NewDbName ) ; If already exists then do not $CreateNew ... $CreateNew = False

    If Not $CreateNew Then ; if exist
        If Not @Compiled Then ConsoleWrite("database exists already ! No need to create !" & @CRLF)
    Else ; doesnt exist, create it
        $commandstr = _
                "CREATE DATABASE" & @CRLF & _
                "   " & $NewDbName & @CRLF & _
                "ON PRIMARY" & @CRLF & _
                "   (" & @CRLF & _
                "       NAME=" & $NewDbName & @CRLF & _
                "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & ".mdf'" & @CRLF & _
                "       ,SIZE=2MB" & @CRLF & _
                "       ,MAXSIZE=100MB" & @CRLF & _
                "   ) " & @CRLF & _
                "LOG ON " & @CRLF & _
                "   (" & @CRLF & _
                "       NAME=" & $NewDbName & "_log" & @CRLF & _
                "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & "_log.ldf'" & @CRLF & _
                "       ,SIZE=2MB" & @CRLF & _
                "       ,MAXSIZE=100MB" & @CRLF & _
                "   )"
        Local $oCommand = _ADO_Command_Create($oConnection)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

        Local $Result = _ADO_Command_Execute($oCommand, $commandstr)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
        #forceref $Result

        If Not @Compiled Then ConsoleWrite("succesfully created database !" & @CRLF)
    EndIf

    ; Clean Up
    $oRecordset_Final = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc   ;==>_MSSQL_CreateDatabaseWithNameAndPath

Func _MSSQL_CreateBlobTable($DbName = "Test_db", $Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    ; create connection and open with desired connection string
    Local $oConnection = _ADO_Connection_Create()
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=' & $DbName & ';UID=' & $User & ';PWD=' & $Pwd & ';'
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; check if table already exist
    Local $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    If @error >= $ADO_ERR_RECORDSETEMPTY Then
        ; if table not exist create them
        Local $s_SQLQuery_CreateTable = _
                "CREATE TABLE" & @CRLF & _
                "   FILE_BIN_DATA" & @CRLF & _
                "   (" & @CRLF & _
                "       ROWID int IDENTITY(1,1) NOT NULL" & @CRLF & _
                "       ,File_Name Varchar(60) NOT NULL" & @CRLF & _
                "       ,File_Data Varbinary(max) NULL" & @CRLF & _
                "       ,CONSTRAINT [PK_FILE_BIN_DATA] PRIMARY KEY CLUSTERED " & @CRLF & _
                "           (ROWID ASC)" & @CRLF & _
                "   ) " & @CRLF & _
                "ON [PRIMARY]"

        _ADO_Execute($oConnection, $s_SQLQuery_CreateTable)
        If @error Then
            If Not @Compiled Then ConsoleWrite('! ---> @error=' & @error & '  @extended=' & @extended & ' : CREATE TABLE' & @CRLF)
        Else
            $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
        EndIf
    EndIf

    ; show info about the table
    If Not @Compiled Then ConsoleWrite(_ADO_Recordset_ToString($oRecordset_Tables))

    ; Clean Up
    $oRecordset_Tables = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

EndFunc   ;==>_MSSQL_CreateBlobTable

Func _MSSQL_InsertFileBinary(ByRef $bFileBinaryIn)
    #forceref $bFileBinaryIn
    ;; do some insert command/ stream
EndFunc   ;==>_MSSQL_InsertFileBinary

Func _MSSQL_ReadFileBinary(ByRef $bFileBinaryOut)
    #forceref $bFileBinaryOut
    ; do some select command/ stream
EndFunc   ;==>_MSSQL_ReadFileBinary

 

Edited August 28, 2021 by mLipok

[mLipok]

as so far I get:

Quote

[Microsoft][ODBC SQL Server Driver]

The CREATE DATABASE statement failed. The primary file must be at least 5 MB to accommodate a copy of the model database.

 

so I customize a SQL query...... WIP

Edited August 29, 2021 by mLipok

[mLipok]

I hope that the following quite complementary example will prove to be helpful not only for you, but also for other's AutoIt coders.

expandcollapse popup

;~ https://www.autoitscript.com/forum/topic/180850-adoau3-udf-beta-support-topic/?do=findComment&comment=1488346

#AutoIt3Wrapper_Run_AU3Check=Y
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7

; I use AutoIt 3.3.15.4 (Beta version) because:
;   ConsoleWrite() does not change the values of @error and @extended,
;       which is beneficial in the case of easy code management and tracking @errors.
; In other way I need to use my _Log() from Errorlog.au3 UDF
; or use other techniques to properly manage @error with throwing them to console with full tracking events
#AutoIt3Wrapper_AutoIt3Dir="z:\AutoItPortable\AutoIt_3_3_15_4_beta"

#include <FileConstants.au3>
#include <MsgBoxConstants.au3>
#include "ADO.au3"

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

_Main()
Exit

Func _main()
    Local $oConnection = _MSSQL_CreateExampleConnection()
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _Example($oConnection)

    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc   ;==>_main

Func _Example(ByRef $oConnection)
    _MSSQL_CreateDatabaseWithNameAndPath($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _MSSQL_CreateBlobTable($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _BLOB_Example($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : Error occured in _BLOB_Example()" & @CRLF)

EndFunc   ;==>_Example


Func _BLOB_Example(ByRef $oConnection)
    ; read whole file in binary
    Local $hFileOpened = FileOpen(@ScriptDir & "\SomePdf.pdf", $FO_BINARY)
    Local $d_FileData_ToStore = FileRead($hFileOpened)
    FileClose($hFileOpened)

    _MSSQL_InsertFileBinary($oConnection, $d_FileData_ToStore, 'Test_db', 'Information: SomePdf.pdf')
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $d_FileData_Restored
    _MSSQL_ReadFileBinary($oConnection, $d_FileData_Restored, "Test_db")
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; write binary content to new file
    Local $hFileSave = FileOpen(@ScriptDir & "\SomePdfNewCopy.pdf", $FO_BINARY + $FO_OVERWRITE + $FO_CREATEPATH)
    FileWrite($hFileSave, $d_FileData_Restored)
    FileClose($hFileSave)

    ; final check - open file in associated program
    If $IDYES = MsgBox($MB_YESNO + $MB_TOPMOST + $MB_ICONQUESTION + $MB_DEFBUTTON1, "Question", _
            "Do you wan try to open saved file ?") Then
        ShellExecute("SomePdfNewCopy.pdf")
    EndIf

EndFunc   ;==>_BLOB_Example

Func _MSSQL_CreateDatabaseWithNameAndPath(ByRef $oConnection, $NewDbName = "Test_db", $DirectoryPath = @ScriptDir)
    ; parameters validation
    If $NewDbName = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM1, $ADO_RET_FAILURE)
    If $DirectoryPath = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM2, $ADO_RET_FAILURE)

    ; note *** directories must exist first, use DirGetSize() to ensure the path exists then if not create with DirCreate()

    ; check if database exists already
    Local $s_SQLQuery_Check_DB_Exist = _
            "SELECT" & @CRLF & _
            "   [name]" & @CRLF & _
            "FROM" & @CRLF & _
            "   [master].dbo.[sysdatabases]" & @CRLF & _
            "WHERE" & @CRLF & _
            "   [name]='" & $NewDbName & "'" & @CRLF & _
            ""
    Local $oRecordset_Final = _ADO_Execute($oConnection, $s_SQLQuery_Check_DB_Exist, False)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; checking if is any result
    Local $aRowsResult = _ADO_Recordset_ToArray($oRecordset_Final)
    #forceref $aRowsResult
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If Not @error Then
        If Not @Compiled Then ConsoleWrite("database exists already ! No need to create them" & @CRLF)
        Return
    ElseIf @error < $ADO_ERR_RECORDSETEMPTY Then
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    Else
        If Not @Compiled Then ConsoleWrite("database not exist - will be created" & @CRLF)
    EndIf

    #Region - this part is not needed
    #cs
        Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
        If @error And Not @compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
                " : " & @CRLF)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

        $CreateNew = Not ($aRecordsetContent[0][0] == $NewDbName ) ; If already exists then do not $CreateNew ... $CreateNew = False
    #ce
    #EndRegion - this part is not needed

    Local $commandstr = _
            "CREATE DATABASE" & @CRLF & _
            "   " & $NewDbName & @CRLF & _
            "ON PRIMARY" & @CRLF & _
            "   (" & @CRLF & _
            "       NAME=" & $NewDbName & @CRLF & _
            "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & ".mdf'" & @CRLF & _
            "       ,SIZE=5MB" & @CRLF & _
            "       ,MAXSIZE=100MB" & @CRLF & _
            "   ) " & @CRLF & _
            "LOG ON " & @CRLF & _
            "   (" & @CRLF & _
            "       NAME=" & $NewDbName & "_log" & @CRLF & _
            "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & "_log.ldf'" & @CRLF & _
            "       ,SIZE=5MB" & @CRLF & _
            "       ,MAXSIZE=100MB" & @CRLF & _
            "   )"
    Local $oCommand = _ADO_Command_Create($oConnection)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $Result = _ADO_Command_Execute($oCommand, $commandstr)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    #forceref $Result

    If Not @Compiled Then ConsoleWrite("succesfully created database !" & @CRLF)

    ; Clean Up
    $oRecordset_Final = Null
EndFunc   ;==>_MSSQL_CreateDatabaseWithNameAndPath

Func _MSSQL_CreateBlobTable(ByRef $oConnection, $DbName = "Test_db")

    ; check if table already exist
    Local $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    If @error >= $ADO_ERR_RECORDSETEMPTY Then
        ; if table not exist create them
        Local $s_SQLQuery_CreateTable = _
                "CREATE TABLE" & @CRLF & _
                "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
                "   (" & @CRLF & _
                "       ROWID int IDENTITY(1,1) NOT NULL" & @CRLF & _
                "       ,File_Name Varchar(60) NOT NULL" & @CRLF & _
                "       ,File_Data Varbinary(max) NULL" & @CRLF & _
                "       ,CONSTRAINT [PK_FILE_BIN_DATA] PRIMARY KEY CLUSTERED " & @CRLF & _
                "           (ROWID ASC)" & @CRLF & _
                "   ) " & @CRLF & _
                "ON [PRIMARY]"

        _ADO_Execute($oConnection, $s_SQLQuery_CreateTable)
        If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
                " : " & @CRLF)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

        ; get again info about table
        $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    EndIf

    ; show info about the table
    If Not @Compiled Then ConsoleWrite(_ADO_Recordset_ToString($oRecordset_Tables))

    ; Clean Up
    $oRecordset_Tables = Null
EndFunc   ;==>_MSSQL_CreateBlobTable

Func _MSSQL_InsertFileBinary(ByRef $oConnection, ByRef $dFileBinaryIn, $DbName = "Test_db", $s_File_Name ='')
    Local $s_SQLQuery_InsertBinaryContent = _
            "INSERT INTO" & @CRLF & _
            "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
            "       (" & @CRLF & _
            "           File_Name" & @CRLF & _
            "           ,File_Data" & @CRLF & _
            "       )" & @CRLF & _
            "OUTPUT" & @CRLF & _
            "   inserted.[ROWID]" & @CRLF & _
            "VALUES(" & @CRLF & _
            "           '" & $s_File_Name & "'" & @CRLF & _
            "           ," & $dFileBinaryIn & @CRLF & _
            "       )" & @CRLF & _
            ""
    # !!! WARNING !!! this following line has an issue - the inserted data are inserted 3 times !!!
;~  Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, True)
    # BECAUSE OF THIS ISSUE:   !!!!  use $bReturnAsArray = False .... like in following line
    Local $oRecordset = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, False)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    #forceref $oRecordset

    ; .... you can get new ROWID from :   OUTPUT inserted.[ROWID]
    ; ....
EndFunc   ;==>_MSSQL_InsertFileBinary

Func _MSSQL_ReadFileBinary(ByRef $oConnection, ByRef $dFileBinaryOut, $DbName = "Test_db")
    Local $s_SQLQuery_GetBinaryContent = _
            "SELECT" & @CRLF & _
            "   TOP 1 [File_Data]" & @CRLF & _
            "FROM" & @CRLF & _
            "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
            ""
    Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_GetBinaryContent, True)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $dFileBinaryOut = $aRecordsetContent[0][0]

EndFunc   ;==>_MSSQL_ReadFileBinary

Func _MSSQL_CreateExampleConnection($Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    If $Pwd = "" Then $Pwd = "AutoIt" ; this is my temp passwd for this example project

    ; Create connection and open with $sConnectionString
    Local $oConnection = _ADO_Connection_Create()
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=master;UID=' & $User & ';PWD=' & $Pwd & ';'
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Return $oConnection
EndFunc   ;==>_MSSQL_CreateExampleConnection

 

Feel warned about:

# !!! WARNING !!! this following line has an issue - the inserted data are inserted 3 times !!!
;~  Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, True)
    # BECAUSE OF THIS ISSUE:   !!!!  use $bReturnAsArray = False .... like in following line
    Local $oRecordset = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, False)

 

 

This is a bug that I have noticed before, so far I don't know why it occurs.

I will gladly use any help in solving it.

 

Edited August 29, 2021 by mLipok

[Lecdev]

6 hours ago, mLipok said:

I hope that the following quite complementary example will prove to be helpful not only for you, but also for other's AutoIt coders.

expandcollapse popup

;~ https://www.autoitscript.com/forum/topic/180850-adoau3-udf-beta-support-topic/?do=findComment&comment=1488346

#AutoIt3Wrapper_Run_AU3Check=Y
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7

; I use AutoIt 3.3.15.4 (Beta version) because:
;   ConsoleWrite() does not change the values of @error and @extended,
;       which is beneficial in the case of easy code management and tracking @errors.
; In other way I need to use my _Log() from Errorlog.au3 UDF
; or use other techniques to properly manage @error with throwing them to console with full tracking events
#AutoIt3Wrapper_AutoIt3Dir="z:\AutoItPortable\AutoIt_3_3_15_4_beta"

#include <FileConstants.au3>
#include <MsgBoxConstants.au3>
#include "ADO.au3"

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

_Main()
Exit

Func _main()
    Local $oConnection = _MSSQL_CreateExampleConnection()
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _Example($oConnection)

    _ADO_Connection_Close($oConnection)
    $oConnection = Null
EndFunc   ;==>_main

Func _Example(ByRef $oConnection)
    _MSSQL_CreateDatabaseWithNameAndPath($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _MSSQL_CreateBlobTable($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _BLOB_Example($oConnection)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : Error occured in _BLOB_Example()" & @CRLF)

EndFunc   ;==>_Example


Func _BLOB_Example(ByRef $oConnection)
    ; read whole file in binary
    Local $hFileOpened = FileOpen(@ScriptDir & "\SomePdf.pdf", $FO_BINARY)
    Local $d_FileData_ToStore = FileRead($hFileOpened)
    FileClose($hFileOpened)

    _MSSQL_InsertFileBinary($oConnection, $d_FileData_ToStore, 'Test_db', 'Information: SomePdf.pdf')
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $d_FileData_Restored
    _MSSQL_ReadFileBinary($oConnection, $d_FileData_Restored, "Test_db")
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; write binary content to new file
    Local $hFileSave = FileOpen(@ScriptDir & "\SomePdfNewCopy.pdf", $FO_BINARY + $FO_OVERWRITE + $FO_CREATEPATH)
    FileWrite($hFileSave, $d_FileData_Restored)
    FileClose($hFileSave)

    ; final check - open file in associated program
    If $IDYES = MsgBox($MB_YESNO + $MB_TOPMOST + $MB_ICONQUESTION + $MB_DEFBUTTON1, "Question", _
            "Do you wan try to open saved file ?") Then
        ShellExecute("SomePdfNewCopy.pdf")
    EndIf

EndFunc   ;==>_BLOB_Example

Func _MSSQL_CreateDatabaseWithNameAndPath(ByRef $oConnection, $NewDbName = "Test_db", $DirectoryPath = @ScriptDir)
    ; parameters validation
    If $NewDbName = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM1, $ADO_RET_FAILURE)
    If $DirectoryPath = "" Then Return SetError(-$ADO_ERR_INVALIDPARAMETERVALUE, -$ADO_EXT_PARAM2, $ADO_RET_FAILURE)

    ; note *** directories must exist first, use DirGetSize() to ensure the path exists then if not create with DirCreate()

    ; check if database exists already
    Local $s_SQLQuery_Check_DB_Exist = _
            "SELECT" & @CRLF & _
            "   [name]" & @CRLF & _
            "FROM" & @CRLF & _
            "   [master].dbo.[sysdatabases]" & @CRLF & _
            "WHERE" & @CRLF & _
            "   [name]='" & $NewDbName & "'" & @CRLF & _
            ""
    Local $oRecordset_Final = _ADO_Execute($oConnection, $s_SQLQuery_Check_DB_Exist, False)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; checking if is any result
    Local $aRowsResult = _ADO_Recordset_ToArray($oRecordset_Final)
    #forceref $aRowsResult
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If Not @error Then
        If Not @Compiled Then ConsoleWrite("database exists already ! No need to create them" & @CRLF)
        Return
    ElseIf @error < $ADO_ERR_RECORDSETEMPTY Then
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    Else
        If Not @Compiled Then ConsoleWrite("database not exist - will be created" & @CRLF)
    EndIf

    #Region - this part is not needed
    #cs
        Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
        If @error And Not @compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
                " : " & @CRLF)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

        $CreateNew = Not ($aRecordsetContent[0][0] == $NewDbName ) ; If already exists then do not $CreateNew ... $CreateNew = False
    #ce
    #EndRegion - this part is not needed

    Local $commandstr = _
            "CREATE DATABASE" & @CRLF & _
            "   " & $NewDbName & @CRLF & _
            "ON PRIMARY" & @CRLF & _
            "   (" & @CRLF & _
            "       NAME=" & $NewDbName & @CRLF & _
            "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & ".mdf'" & @CRLF & _
            "       ,SIZE=5MB" & @CRLF & _
            "       ,MAXSIZE=100MB" & @CRLF & _
            "   ) " & @CRLF & _
            "LOG ON " & @CRLF & _
            "   (" & @CRLF & _
            "       NAME=" & $NewDbName & "_log" & @CRLF & _
            "       ,FILENAME='" & $DirectoryPath & "\" & $NewDbName & "_log.ldf'" & @CRLF & _
            "       ,SIZE=5MB" & @CRLF & _
            "       ,MAXSIZE=100MB" & @CRLF & _
            "   )"
    Local $oCommand = _ADO_Command_Create($oConnection)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $Result = _ADO_Command_Execute($oCommand, $commandstr)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    #forceref $Result

    If Not @Compiled Then ConsoleWrite("succesfully created database !" & @CRLF)

    ; Clean Up
    $oRecordset_Final = Null
EndFunc   ;==>_MSSQL_CreateDatabaseWithNameAndPath

Func _MSSQL_CreateBlobTable(ByRef $oConnection, $DbName = "Test_db")

    ; check if table already exist
    Local $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    If @error >= $ADO_ERR_RECORDSETEMPTY Then
        ; if table not exist create them
        Local $s_SQLQuery_CreateTable = _
                "CREATE TABLE" & @CRLF & _
                "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
                "   (" & @CRLF & _
                "       ROWID int IDENTITY(1,1) NOT NULL" & @CRLF & _
                "       ,File_Name Varchar(60) NOT NULL" & @CRLF & _
                "       ,File_Data Varbinary(max) NULL" & @CRLF & _
                "       ,CONSTRAINT [PK_FILE_BIN_DATA] PRIMARY KEY CLUSTERED " & @CRLF & _
                "           (ROWID ASC)" & @CRLF & _
                "   ) " & @CRLF & _
                "ON [PRIMARY]"

        _ADO_Execute($oConnection, $s_SQLQuery_CreateTable)
        If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
                " : " & @CRLF)
        If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

        ; get again info about table
        $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, $DbName, Default, "FILE_BIN_DATA")
    EndIf

    ; show info about the table
    If Not @Compiled Then ConsoleWrite(_ADO_Recordset_ToString($oRecordset_Tables))

    ; Clean Up
    $oRecordset_Tables = Null
EndFunc   ;==>_MSSQL_CreateBlobTable

Func _MSSQL_InsertFileBinary(ByRef $oConnection, ByRef $dFileBinaryIn, $DbName = "Test_db", $s_File_Name ='')
    Local $s_SQLQuery_InsertBinaryContent = _
            "INSERT INTO" & @CRLF & _
            "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
            "       (" & @CRLF & _
            "           File_Name" & @CRLF & _
            "           ,File_Data" & @CRLF & _
            "       )" & @CRLF & _
            "OUTPUT" & @CRLF & _
            "   inserted.[ROWID]" & @CRLF & _
            "VALUES(" & @CRLF & _
            "           '" & $s_File_Name & "'" & @CRLF & _
            "           ," & $dFileBinaryIn & @CRLF & _
            "       )" & @CRLF & _
            ""
    # !!! WARNING !!! this following line has an issue - the inserted data are inserted 3 times !!!
;~  Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, True)
    # BECAUSE OF THIS ISSUE:   !!!!  use $bReturnAsArray = False .... like in following line
    Local $oRecordset = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, False)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    #forceref $oRecordset

    ; .... you can get new ROWID from :   OUTPUT inserted.[ROWID]
    ; ....
EndFunc   ;==>_MSSQL_InsertFileBinary

Func _MSSQL_ReadFileBinary(ByRef $oConnection, ByRef $dFileBinaryOut, $DbName = "Test_db")
    Local $s_SQLQuery_GetBinaryContent = _
            "SELECT" & @CRLF & _
            "   TOP 1 [File_Data]" & @CRLF & _
            "FROM" & @CRLF & _
            "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
            ""
    Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_GetBinaryContent, True)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRowsResult)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $dFileBinaryOut = $aRecordsetContent[0][0]

EndFunc   ;==>_MSSQL_ReadFileBinary

Func _MSSQL_CreateExampleConnection($Server = "localhost\SQLEXPRESS", $User = "sa", $Pwd = "")
    If $Pwd = "" Then $Pwd = "AutoIt" ; this is my temp passwd for this example project

    ; Create connection and open with $sConnectionString
    Local $oConnection = _ADO_Connection_Create()
    Local $sConnectionString = 'DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=master;UID=' & $User & ';PWD=' & $Pwd & ';'
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Return $oConnection
EndFunc   ;==>_MSSQL_CreateExampleConnection

 

Feel warned about:

# !!! WARNING !!! this following line has an issue - the inserted data are inserted 3 times !!!
;~  Local $aRowsResult = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, True)
    # BECAUSE OF THIS ISSUE:   !!!!  use $bReturnAsArray = False .... like in following line
    Local $oRecordset = _ADO_Execute($oConnection, $s_SQLQuery_InsertBinaryContent, False)

 

 

This is a bug that I have noticed before, so far I don't know why it occurs.

I will gladly use any help in solving it.

 

Thanks mLipok I will try this soon. Hopefully my original example worked well enough to start with I wrote it at 4:30am without any sleep before posting, looks like your modal db is bigger than mine, maybe this is mssql engine version? i am using mssql 2019? 2mb works for me but looks like you need 5mb.

 

For the above mentioned warning, without yet testing my best guess is that the recordset object Isn't cleaned up in the _ADO_Execute function when you use $bReturnAsArray = True and can't be cleaned up later because the object handle Isn't returned, instead the array is. I had noticed this in the UDF function so I avoided using it this way in my scripts because then I can always clean up the object. I thought it might be a risk of the recordset object being left open and ado perhaps returning the handle to the same object the next time which then maybe increments the size of the original recordset object and adds further field objects to that original. Rather than working out how to test this theory I just chose to always use $bReturnAsArray = false in my scripts, maybe you can try adding the clean up of the recordset object to the _ADO_Execute function if $bReturnAsArray is true before returning with the array and see if the problem persists?

 

Thanks

 

 

[mLipok]

13 hours ago, Lecdev said:

For the above mentioned warning, without yet testing my best guess is that the recordset object Isn't cleaned up in the _ADO_Execute function when you use $bReturnAsArray = True and can't be cleaned up later because the object handle Isn't returned, instead the array is. I had noticed this in the UDF function so I avoided using it this way in my scripts because then I can always clean up the object. I thought it might be a risk of the recordset object being left open and ado perhaps returning the handle to the same object the next time which then maybe increments the size of the original recordset object and adds further field objects to that original. Rather than working out how to test this theory I just chose to always use $bReturnAsArray = false in my scripts, maybe you can try adding the clean up of the recordset object to the _ADO_Execute function if $bReturnAsArray is true before returning with the array and see if the problem persists?

Thanks for yours opinion.

I just made some investigation.... in function:

Func _ADO_Recordset_ToArray(ByRef $oRecordset, $bFieldNamesInFirstRow = False)
    ..........
    ..........
    ..........
    $oRecordset.moveFirst()
    ..........
    ..........
    ..........
    $oRecordset.moveFirst()
    ..........
    ..........
    ..........
EndFunc

The first moveFirst() adds new record (the second one, I mean first duplicate).

The second moveFirst() adds the next new record (the third one, I mean second duplicate).

 

[mLipok]

Currently _ADO_Recordset_ToArray() looks like the following code:

expandcollapse popup

Func _ADO_Recordset_ToArray(ByRef $oRecordset, $bFieldNamesInFirstRow = False)
    Local Const $oADO_COMErrorHandler = ObjEvent("AutoIt.Error", __ADO_ComErrorHandler_WrapperFunction)
    If @error Then Return SetError($ADO_ERR_COMHANDLER, @error, $ADO_RET_FAILURE)
    #forceref $oADO_COMErrorHandler

    __ADO_Recordset_IsNotEmpty($oRecordset)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; save current Recordset rows position to $oRecordset_Bookmark
    Local $oRecordset_Bookmark = Null
    If $oRecordset.Supports($ADO_adBookmark) Then $oRecordset_Bookmark = $oRecordset.Bookmark

    $oRecordset.moveFirst()
    Local $aRecordset_GetRowsResult = $oRecordset.GetRows()
    If @error Then Return SetError($ADO_ERR_COMERROR, @error, $ADO_RET_FAILURE) ; Trap COM error, report and return

    If UBound($aRecordset_GetRowsResult) Then
        Local $aResult[0]

        ; Restore Recordset row position from stored $oRecordset_Bookmark
        If $oRecordset_Bookmark = Null Then
            $oRecordset.moveFirst()
        Else
            $oRecordset.Bookmark = $oRecordset_Bookmark
        EndIf

        Local $iColumns_count = UBound($aRecordset_GetRowsResult, $UBOUND_COLUMNS)
        Local $iRows_count = UBound($aRecordset_GetRowsResult)

        If $bFieldNamesInFirstRow Then
            ; Adjust the array to fit the column names and move all data down 1 row
            ReDim $aRecordset_GetRowsResult[$iRows_count + 1][$iColumns_count]

            ; Move all records down
            For $iRow_idx = $iRows_count To 1 Step -1
                For $y = 0 To $iColumns_count - 1
                    $aRecordset_GetRowsResult[$iRow_idx][$y] = $aRecordset_GetRowsResult[$iRow_idx - 1][$y]
                Next
            Next

            ; Add the coloumn names
            For $iCol_idx = 0 To $iColumns_count - 1 ; get the column names and put into 0 array element
                $aRecordset_GetRowsResult[0][$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
            Next
            $aResult = $aRecordset_GetRowsResult
            Return SetError($ADO_ERR_SUCCESS, $iRows_count + 1, $aResult)
        EndIf

        ReDim $aResult[$ADO_RS_ARRAY_ENUMCOUNTR]
        Local $aFiledNames_Temp[$iColumns_count]

        For $iCol_idx = 0 To $iColumns_count - 1 ; get the column names and put into 0 array element
            $aFiledNames_Temp[$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
        Next
        $aResult[$ADO_RS_ARRAY_GUID] = $ADO_RS_GUID
        $aResult[$ADO_RS_ARRAY_FIELDNAMES] = $aFiledNames_Temp
        $aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult
        Return SetError($ADO_ERR_SUCCESS, $iRows_count, $aResult)

    EndIf

    Return SetError($ADO_ERR_RECORDSETEMPTY, $ADO_EXT_DEFAULT, $ADO_RET_FAILURE)
EndFunc   ;==>_ADO_Recordset_ToArray

 

now I'm thinking about changing this function to such:

expandcollapse popup

Func _ADO_Recordset_ToArray(ByRef $oRecordset, $bFieldNamesInFirstRow = False)
    Local Const $oADO_COMErrorHandler = ObjEvent("AutoIt.Error", __ADO_ComErrorHandler_WrapperFunction)
    If @error Then Return SetError($ADO_ERR_COMHANDLER, @error, $ADO_RET_FAILURE)
    #forceref $oADO_COMErrorHandler

    __ADO_Recordset_IsNotEmpty($oRecordset)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    ; save current Recordset rows position to $oRecordset_Bookmark
    Local $oRecordset_Bookmark = Null
    If $oRecordset.Supports($ADO_adBookmark) Then $oRecordset_Bookmark = $oRecordset.Bookmark

;~  $oRecordset.moveFirst()
    Local $aRecordset_GetRowsResult = $oRecordset.GetRows()
;~  Local $aRecordset_GetRowsResult = $oRecordset.GetRows($ADO_adGetRowsRest, $ADO_adBookmarkFirst)
    If @error Then Return SetError($ADO_ERR_COMERROR, @error, $ADO_RET_FAILURE) ; Trap COM error, report and return

    If UBound($aRecordset_GetRowsResult) Then
        Local $aResult[0]

        ; Restore Recordset row position from stored $oRecordset_Bookmark
        If $oRecordset_Bookmark = Null Then
;~          $oRecordset.moveFirst()
        Else
            $oRecordset.Bookmark = $oRecordset_Bookmark
        EndIf

        Local $iColumns_count = UBound($aRecordset_GetRowsResult, $UBOUND_COLUMNS)
        Local $iRows_count = UBound($aRecordset_GetRowsResult)

        If $bFieldNamesInFirstRow Then
            ; Adjust the array to fit the column names and move all data down 1 row
            ReDim $aRecordset_GetRowsResult[$iRows_count + 1][$iColumns_count]

            ; Move all records down
            For $iRow_idx = $iRows_count To 1 Step -1
                For $y = 0 To $iColumns_count - 1
                    $aRecordset_GetRowsResult[$iRow_idx][$y] = $aRecordset_GetRowsResult[$iRow_idx - 1][$y]
                Next
            Next

            ; Add the coloumn names
            For $iCol_idx = 0 To $iColumns_count - 1 ; get the column names and put into 0 array element
                $aRecordset_GetRowsResult[0][$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
            Next
            $aResult = $aRecordset_GetRowsResult
            Return SetError($ADO_ERR_SUCCESS, $iRows_count + 1, $aResult)
        EndIf

        ReDim $aResult[$ADO_RS_ARRAY_ENUMCOUNTR]
        Local $aFiledNames_Temp[$iColumns_count]

        For $iCol_idx = 0 To $iColumns_count - 1 ; get the column names and put into 0 array element
            $aFiledNames_Temp[$iCol_idx] = $oRecordset.Fields($iCol_idx).Name
        Next
        $aResult[$ADO_RS_ARRAY_GUID] = $ADO_RS_GUID
        $aResult[$ADO_RS_ARRAY_FIELDNAMES] = $aFiledNames_Temp
        $aResult[$ADO_RS_ARRAY_RSCONTENT] = $aRecordset_GetRowsResult
        Return SetError($ADO_ERR_SUCCESS, $iRows_count, $aResult)

    EndIf

    Return SetError($ADO_ERR_RECORDSETEMPTY, $ADO_EXT_DEFAULT, $ADO_RET_FAILURE)
EndFunc   ;==>_ADO_Recordset_ToArray

 

The main differences are here:

Func _ADO_Recordset_ToArray(ByRef $oRecordset, $bFieldNamesInFirstRow = False)
    ....
    ....
    ....
;~  $oRecordset.moveFirst()
    Local $aRecordset_GetRowsResult = $oRecordset.GetRows()
;~  Local $aRecordset_GetRowsResult = $oRecordset.GetRows($ADO_adGetRowsRest, $ADO_adBookmarkFirst)
    ....
    ....
        If $oRecordset_Bookmark = Null Then
;~          $oRecordset.moveFirst()
        Else
            $oRecordset.Bookmark = $oRecordset_Bookmark
        EndIf
    ....
    ....
    ....
EndFunc   ;==>_ADO_Recordset_ToArray

The main purposes is to use:

Quote

adBookmarkFirst

1

Starts at the first record.

instead:

Quote

adBookmarkCurrent

0

Starts at the current record.

references:
https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/bookmarkenum?view=sql-server-ver15

 

Please feel free to express your views on this.

Edited August 30, 2021 by mLipok
proposals modified

[Lecdev]

 

 

I'm not so sure that removing the MoveFirst method support is correct, it shouldn't be adding records, it is supposed to throw an error if there is no data hence needing to check BOF and EOF first and shouldn't do anything more than set the internal pointer to the first already existing record in the recordset if it does contain a record. There is a truth table on msdn somewhere to show the results of the move methods in different circumstances I will try to find it.

 

also in some deeper research on recordset clean up I have found these notes mentioned in msdn;

"

If You Explicitly Open It, Explicitly Close It:
Pooling problems are frequently caused by an application that does not clean up its connections. Connections are placed in the pool at the time that the connection is closed and not before. To avoid this, always explicitly close an object you open. If you don't explicitly close it, chances are it won't ever be released to the pool.

Even if the language you use has effective and reliable garbage collection, an instance of an open ADO Connection or Recordset object going out of scope does not equate to the Close method of that object being implicitly invoked. You must close it explicitly.

Failing to close an open Connection or Recordset object is probably the single most frequent cause of connection creep and the single largest cause of incorrect diagnoses of pooling failure.

"

and details here:

https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/close-method-ado?view=sql-server-ver15

suggesting that if you are leaving open the connection and re-using you should use the close method on the recordset before nulling the object var and also that closing the connection object doesn't close the command objects either, it does however close a recordset but it is common to leave connections open throughout several queries.

 

it may be necessary to add the following for certain situations;

Func _ADO_Recordset_Close(ByRef $oRecordset)
    Local Const $oADO_COMErrorHandler = ObjEvent("AutoIt.Error", __ADO_ComErrorHandler_WrapperFunction)
    If @error Then Return SetError($ADO_ERR_COMHANDLER, @error, $ADO_RET_FAILURE)
    #forceref $oADO_COMErrorHandler

    __ADO_Recordset_IsValid($oRecordset)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $oRecordset.Close
    Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $ADO_RET_SUCCESS)
EndFunc   ;==>_ADO_Recordset_Close

Func _ADO_Command_Close(ByRef $oCommand)
    Local Const $oADO_COMErrorHandler = ObjEvent("AutoIt.Error", __ADO_ComErrorHandler_WrapperFunction)
    If @error Then Return SetError($ADO_ERR_COMHANDLER, @error, $ADO_RET_FAILURE)
    #forceref $oADO_COMErrorHandler

    __ADO_Command_IsValid($oCommand)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    $oCommand.Close
    Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $ADO_RET_SUCCESS)
EndFunc   ;==>_ADO_Command_Close

 

I also found that the execute method always returns a recordset with a forward only cursor so moveFirst likely doesn't work in the end. instead to do this you have to open your own recordset. https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/execute-method-ado-connection?view=sql-server-ver15#remarks

You could use recordset.supports(adMovePrevious) to check if the recordset is forward only or check the recordset.CurserType property which may be able to change the type but not sure if this works on a read-only Recordset once already opened.