ADO.au3 UDF - BETA - Support Topic

[Hadin]

When I compress the binary data with Base64 encoding, $FileBinary: tringLen($FileBinary)/1024/1024=106MB: The upload is successful, but the download and reading may fail. Therefore, I checked the output in _ADO_Execute and _ADO_Recordset_ToArray and determined that the problem occurred in _ADO_Recordset_ToArray, as shown in the following figure.(There's no problem with small files.)

Local $Ruturn=_ADO_Execute($SqlConnet,"select abinary,aVersion from TB_a_Test WHERE filename like '%Docx%';",True,True)

 

Error Problem Location:

Func _ADO_Recordset_ToArray(ByRef $oRecordset, $bFieldNamesInFirstRow = False)
......
  ConsoleWrite("Pass4"&@CRLF)
      $oRecordset.moveFirst()
  ConsoleWrite("Pass5"&@CRLF)
      Local $aRecordset_GetRowsResult = $oRecordset.GetRows()
  ConsoleWrite("Pass6"&@CRLF)
 ......
Endfunc

;;;;;;;;;;;;;;;Output ConsoleWrite:
Pass4
Pass5
!>11:00:58 AutoIt3.exe End.rc:-1073741819
;;;;;;;;;;;;;;;Output

 

[mLipok]

@Hadin I will do some test in the next few comming days.

[mLipok]

as so far I'm able to get this error:
 

>>> ==> ADO Error Collection: .errors.Count = 1
>>  $oError.Item(0).description is:     Za mało zasobów pamięci, aby można było zakończyć tę operację.
>>  $oError.Item(0).number is:      0x8007000E
>>  $oError.Item(0).NativeError is:     -2147024882
>>  $oError.Item(0).source is:      Provider
>>  $oError.Item(0).helpfile is:        
>>  $oError.Item(0).helpcontext is:     1240640

Quote

There is not enough memory resources to complete this operation.

EDIT:

4,1 GB RAM usage.
I have only 8GB in my Windows instalation.

 

 

Edited November 12, 2021 by mLipok

[mLipok]

As for now I did some ADO.au3 UDF fixes (WIP).

And do many test.
I'm able to fire the same error:

Quote

$oADO_Error.description is:     [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
$oADO_Error.windescription:     Wystąpił wyjątek.
$oADO_Error.number is:     80020009
$oADO_Error.lastdllerror is:     0
$oADO_Error.scriptline is:     -1
$oADO_Error.source is:     Microsoft OLE DB Provider for ODBC Drivers
$oADO_Error.helpfile is:
$oADO_Error.helpcontext is:     0
 

Here is usefull hint:
https://www.ibm.com/support/pages/dbnetlibconnectionwrite-send

and this one:
https://microsoft.public.sqlserver.programming.narkive.com/wPWG1Oqp/microsoft-odbc-sql-server-driver-dbnetlib-connectionwrite-send

looks like an not coresponding to @error which I fires on my PC as I do not use any Network devices, because I use:
 

localhost\SQLEXPRESS

So I go further... next step was to change:
DRIVER={SQL Server}

to:
DRIVER={SQL Server Native Client 11.0}

and then I get:

Quote

$oADO_Error.description is:     [Microsoft]

Shared Memory Provider: Na drugim końcu potoku nie ma żadnego procesu.
            $oADO_Error.description is:     [Microsoft] [SQL Server Native Client 11.0]Shared Memory Provider: There is no process at the other end of the pipe.
            $oADO_Error.windescription:     Wystąpił wyjątek.
            $oADO_Error.number is:     80020009
            $oADO_Error.lastdllerror is:     0
            $oADO_Error.scriptline is:     -1
            $oADO_Error.source is:     Microsoft OLE DB Provider for ODBC Drivers
            $oADO_Error.helpfile is:
            $oADO_Error.helpcontext is:     0

 

[mLipok]

@Hadin

After investigating the problem I think that the only one solution will be:
https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/appendchunk-method-ado?view=sql-server-ver15

and

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

EDIT:
https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/appendchunk-and-getchunk-methods-example-vb?view=sql-server-ver15
http://www.vb-helper.com/howto_database_picture.html
https://ftp.zx.net.nz/pub/archive/ftp.microsoft.com/MISC/KB/en-us/317/044.HTM

Edited November 12, 2021 by mLipok

[mLipok]

Something like this:

_Example()
Func _Example()
    Local $s_SQLQuery_GetBinaryContent = _
            "SELECT" & @CRLF & _
            "   TOP 1 [File_Data]" & @CRLF & _
            "FROM" & @CRLF & _
            "   [" & $DbName & "].dbo.[FILE_BIN_DATA]" & @CRLF & _
            ""

    Local $oRecordset = _ADO_Execute($oConnection, $s_SQLQuery_GetBinaryContent, False, False, $ADO_AdCmdText, $ADO_adOpenKeyset)
    If @error And Not @Compiled Then ConsoleWrite("! " & @ScriptLineNumber & " ---> @error=" & @error & " @extended=" & @extended & _
            " : " & @CRLF)

    Local $dChunk

    Local $dFileBinaryOut = StringToBinary('')
    While 1
        $dChunk = $oRecordset.Fields("File_Data").GetChunk(100*1024)
        If $dChunk = Null Then ExitLoop
        ConsoleWrite("- BinaryLen($dChunk) = " & BinaryLen($dChunk)& @CRLF)

        $dFileBinaryOut += $dChunk
    WEnd
    
    ; write binary content to new file
    Local $hFileSave = FileOpen(@ScriptDir & "\SomePdfNewCopy.pdf", $FO_BINARY + $FO_OVERWRITE + $FO_CREATEPATH)
    FileWrite($hFileSave, $dFileBinaryOut)
    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

This is just an outline of the code, a snippet taken from my example I'm working on.

[Skysnake]

On 11/12/2021 at 9:05 PM, mLipok said:

As for now I did some ADO.au3 UDF fixes (WIP).

PM me? I can test. 

[big_daddy]

I was tasked with creating a utility that would parse an Excel document and then update an existing MySQL table. I'm much more comfortable using object oriented programming and I didn't want to creating a bunch of update queries. After quite a lot of trial, error, and research, I finally found a working solution. Thanks to @jchd for the post above that pointed me in the right direction. Also, thank you to @mLipok for a great UDF. 

Note: This is snipped code and for concept purposes only.

#include "ADO.au3"

Global $sDriver = ""
Global $sServer = ""
Global $sDatabase = ""
Global $sPort = ""
Global $sUser = ""
Global $sPassword = ""

Global $sQuery = "SELECT * FROM table"
Global $sNewValue = "Some Value"

Global $sConnectionString = _ADO_ConnectionString_MySQL($sUser, $sPassword, $sDatabase, $sDriver, $sServer, $sPort)
Global $oConnection = _ADO_Connection_Create()

_ADO_Connection_OpenConString($oConnection, $sConnectionString)

; https://docs.microsoft.com/en-us/office/vba/access/concepts/miscellaneous/execute-method-ado-connection#remarks
Global $oRecordset = _ADO_Recordset_Create()

With $oRecordset
    .CursorLocation = $ADO_adUseClient
    .Properties("Update Criteria").Value = $ADO_adCriteriaKey
    .Open($sQuery, $oConnection, $ADO_adOpenDynamic, $ADO_adLockPessimistic)
    .MoveFirst()
EndWith

While Not $oRecordset.EOF()
    ; The update will fail if you attempt to update with equal values
    If StringCompare($oRecordset.Fields("Column").Value, $sNewValue) <> 0 Then _
            $oRecordset.Fields("Column").Value = $sNewValue
    $oRecordset.MoveNext()
WEnd

$oRecordset.UpdateBatch()

 

Edited January 25, 2022 by big_daddy

[FesterJester]

Hello everyone.

Looking to use AutoIT to return the results of a transact-sql (MSSQL) query.

I will be connecting to SQL Server 15.0.2080.9 and would like to use Windows Authentication if possible.

I downloaded this library and the examples, however I have been unsuccessful in connecting to the SQL server.

I made a copy of ADO_EXAMPLE.au3 and edited the examples.

In the _Example_MSSQL_SQLServerAuthorization() function, I changed:

• Database, Server, User, and Password
• I also changed the select statements to pull from tables that actually exist.

In the _Example_MSSQL_WindowsAuthorization() function, I changed:

• Database and Server
• I also changed the select statements to pull from tables that actually exist.

When I call the _Example_MSSQL_WindowsAuthorization() function, I get no output and no error that I can determine.

When I call the _Example_MSSQL_SQLServerAuthorization() function, I get

>"C:\Program Files (x86)\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Users\user\Desktop\AutoIT-SQL\ADO_EXAMPLE.au3"    
! ---> @error=4  @extended=-2147352567 : _Example_MSSQL_SQLServerAuthorization()
>Exit code: 0    Time: 20.86

I have not yet read through all 28 pages of this post to see if someone has listed the error codes or had a similar issue.

Anyone out there able to help explain to me what I am doing wrong or missing?

Any help is appreciated.

Thanks,

FJ

[mLipok]

1 hour ago, FesterJester said:

n the _Example_MSSQL_WindowsAuthorization() function, I changed:

• Database and Server
• I also changed the select statements to pull from tables that actually exist.

When I call the _Example_MSSQL_WindowsAuthorization() function, I get no output and no error that I can determine.

There is really no output ?
Show to us entire SciTE console log.

Edited March 2, 2022 by mLipok

[mLipok]

hm.... 
Add return point to the _Example_MSSQL_WindowsAuthorization()

Func _Example_MSSQL_WindowsAuthorization()
    Local $sDatabase = 'AdventureWorks2016_EXT' ; change this string to YourDatabaseName
    Local $sServer = 'localhost\SQLExpress' ; change this string to YourServerLocation

    _Example_5_MSSQL_WinAuth($sServer, $sDatabase, "Select * from person")
    Return SetError(@error, @extended)
EndFunc   ;==>_Example_MSSQL

and add error checking after example 

_Example_MSSQL_WindowsAuthorization()
If @error Then ConsoleWrite('! ---> @error=' & @error & '  @extended=' & @extended & ' : _Example_MSSQL_WindowsAuthorization()' & @CRLF)

 

Edited March 2, 2022 by mLipok

[mLipok]

Try with this modified example:

 

 

ADO_EXAMPLE.au3

Edited March 2, 2022 by mLipok

[FesterJester]

@mLipok I never noticed that the function was missing the "If @error" statement.

Now I get the same error when I execute both functions.

This is the entire output in the bottom panel of the SciTE Script Editor window

>"C:\Program Files (x86)\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\Users\user\Desktop\AutoIT-SQL\ADO_EXAMPLE.au3"    
! ---> @error=4  @extended=-2147352567 : _Example_MSSQL_SQLServerAuthorization()
! ---> @error=4  @extended=-2147352567 : _Example_MSSQL_WindowsAuthorization()
>Exit code: 0    Time: 21.36

 

[FesterJester]

I am going to make a guess and assume that @error=4  @extended=-2147352567 means authentication error.

I managed to get _Example_MSSQL_SQLServerAuthorization() working. It looks like I had the wrong format for the server location string.

I changed the server string in both example functions, but the _Example_MSSQL_WindowsAuthorization() function still has the error listed in the post above.

[FesterJester]

I have made a little progress.

I enabled the internal COMError handler and discovered a much more descriptive error.

I figured out that I needed to change $bSQLAuth to True in the _Example_5_MSSQL_WinAuth() function.

Now I can see that I am getting an authentication error.

How do I use Windows/Active Directory credentials to authenticate?

I know my credentials are valid because I can use my credentials in Microsoft SQL Server Management Studio.

Edited March 9, 2022 by FesterJester

[FesterJester]

I found a solution!

Looks like I need to add Integrated Security=SSPI to the connection string in order to authenticate to the SQL server properly.

I have cheated and added ;Integrated Security=SSPI to the end of the server name I enter for variable $sServer.

I.E.

Local $sServer = 'ServerName;Integrated Security=SSPI' ; change this string to YourServerLocation

Probably not the most elegant solution, but it is working now.

Now I just need to build a username and password prompt. 🙂

Also, has anyone noticed that ADO_EXAMPLE_MS_SQL_Giovanov.au3 is incomplete?

[mLipok]

I check with WinGrep

Integrated Security=SSPI'

And I found out that I have this fixed in some examples, which was sitting in a deep black hole, on my SSD.
This is how I realized that I not release new ADO.au3 UDF since 16 months.

Maybe it is a time to release.
But I had to review code and examples.
It will take me at least 1 week as I'm really busy in ohter projects my own, and even public like au3WebDriver.

[FesterJester]

@mLipok Not a problem. I understand that situation.

I average 60+ hours Monday - Friday in my IT job plus the 20+ hours I freelance on the weekends plus my own projects in between all of that.

(I think sleep is in there somewhere also.  lol)

I appreciate the work you have put into this so far and I thank you for having responded to my messages so far.

 

I will keep my eye on this thread for an update. 😉

[mLipok]

I spent about 50h on doing IT work and stuff on last 3 days.
I plan to have normal 8h work day in next 2 days, and non IT Saturday+Sunday

Edited March 9, 2022 by mLipok

[dsm-sas]

Hi2All,

I use this UDF. Really great. But now I have to switch between databases in one application. There is no "DISCONNECT" and if I try to make a new Connect with  "   _ADO_API_ConnectionString($sConnectionString)" it is still in the same database.

Would be nice to use more then 1 connection in one application, perhaps with to different obj's ?!