ADO.au3 UDF - BETA - Support Topic

[mLipok]

New version:

2019/08/11 '2.1.16 BETA'
.   Added: $oRecordset.Supports($ADO_adMovePrevious) in _ADO_Recordset_ToString() - xrxca
.   Added: __ADO_MSSQL_CONNECTION_STRING_WinAuth() - mLipok
.   Added: _ADO_GetDSNList() - mLipok
.   Changed: _ADO_RecordsetArray_GetContent() - on succes @extended = UBound($aContent) - mLipok
.
.
.   !!!!! SCRIPT BREAKING CHANGE !!!!!
.   Removed: prameter $iAlternateColors from __ADO_RecordsetArray_Display() for compability wiht current AutoIt Version - mLipok
.   Removed: prameter $iAlternateColors from _ADO_Recordset_Display() for compability wiht current AutoIt Version - mLipok

Download link

 

Edited August 11, 2019 by mLipok

[Skysnake]

Hi

What can you select on that DB that DOES work? Are you sure you are connected, with the right privileges?

 

[BigDaddyO]

@mLipok  The [ADO 2.1.16 BETA.zip] link at the bottom of the download page isn't working for me.

I also tried clicking on the [Download this File] button but it gives every version but the .16

 

I'm hoping that this udf will help me get around a TLS connection issue when using ODBC to an SQL server.

Thanks,

Mike

[mLipok]

As I understnad you try to download from here:

 

As I remember this issue (with downloading) was already notified .
This is related to your system environment or with FORUM engine.

I recomend you to try download on another browser, computer.....

 

[DonChunior]

On ‎9‎/‎9‎/‎2019 at 7:51 PM, BigDaddyO said:

@mLipok  The [ADO 2.1.16 BETA.zip] link at the bottom of the download page isn't working for me.

I also tried clicking on the [Download this File] button but it gives every version but the .16

 

I'm hoping that this udf will help me get around a TLS connection issue when using ODBC to an SQL server.

Thanks,

Mike

It seems that @mLipok has fixed the issue:
Now you're albe to download the version 2.1.16.

[BigDaddyO]

Yep, i'm able to download the .16 now, thanks for the heads up!

[colombeen]

Hi, I'm having an issue with values that are(n't) returned. for some reason I can't see some of them.

Example : 

This is the result when using powershell : 

PS C:\> $test = Invoke-Sqlcmd -ServerInstance $Server -Database $DB -Query "SELECT [Naam], [AantalGebruikers], [Suffixes], [Profielen], [Hoofdgebruikers] FROM [TestView] WHERE [Naam] = 'TestCompany'"
PS C:\> $test

Naam             : TestCompany
AantalGebruikers : 3
Suffixes         : testcompany.com,testcomp.com
Profielen        : Profile1,Profile4,Profile5,Profile17
Hoofdgebruikers  : Doe John,Doe Jane


PS C:\> $test.Suffixes
testcompany.com,testcomp.com

PS C:\> $test.Suffixes.GetType()

IsPublic IsSerial Name        BaseType
-------- -------- ----        --------
True     True     String      System.Object


PS C:\> $test.Profielen.GetType()

IsPublic IsSerial Name        BaseType
-------- -------- ----        --------
True     True     String      System.Object

When I use the ADO udf to retrieve the same info, then "Suffixes" and "Profielen" are both empty when viewing it with _ArrayDisplay or when using ConsoleWrite.

#include "ADO.au3"
#include <Array.au3>

Local $sDriver = 'SQL Server'
Local $sDatabase = 'DB'
Local $sServer = 'Server'
Local $sUser = ''
Local $sPassword = ''

_ADO_COMErrorHandler_UserFunction(_ADO_COMErrorHandler)

Local $oCon = _ADO_Connection_Create()
If IsObj($oCon) Then
    _ADO_Connection_OpenConString($oCon, 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';Trusted_Connection=Yes;')
    
    If Not @error Then
        Local $sQuery = "SELECT [Naam], [AantalGebruikers], [Suffixes], [Profielen], [Hoofdgebruikers] FROM [TestView] WHERE [Naam] = 'TestCompany'"
        Local $aResult = _ADO_Execute($oCon, $sQuery, True)
        If Not @error Then
            If IsArray($aResult) Then
                _ArrayDisplay($aResult[2], "Test Results")
            EndIf
        EndIf
    EndIf
EndIf

; $aResults[2]
; -------------
; [0] : TestCompany
; [1] : 3
; [2] : 
; [3] : 
; [4] : Doe John,Doe Jane
; -------------

Any ideas?

 

EDIT:

Strange, when I use this it does work:

#include "ADO.au3"
#include <Array.au3>

Local $sDriver = 'SQL Server'
Local $sDatabase = 'DB'
Local $sServer = 'Server'
Local $sUser = ''
Local $sPassword = ''

_ADO_COMErrorHandler_UserFunction(_ADO_COMErrorHandler)

Local $oCon = _ADO_Connection_Create()
If IsObj($oCon) Then
    _ADO_Connection_OpenConString($oCon, 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';Trusted_Connection=Yes;')
    
    If Not @error Then
        Local $sQuery = "SELECT [Naam], [AantalGebruikers], [Suffixes], [Profielen], [Hoofdgebruikers] FROM [TestView] WHERE [Naam] = 'TestCompany'"
        Local $aResult = _SQL_FetchData(_ADO_Execute($oCon, $sQuery))
        If Not @error Then
            If IsArray($aResult) Then
                _ArrayDisplay($aResult, "Test Results")
            EndIf
        EndIf
    EndIf
EndIf

Why is there a difference when using _ADO_Execute to get an array in comparison to _SQL_FetchData ??

Edited October 18, 2019 by colombeen

[crammaster]

Hi,

I had the problem that the original UDF with me could not open files of the format * .accdb (2007-2016).

The problem could be solved by adjusting the ADO.au3 file.

From:

Func _ADO_ConnectionString_Access($sFileFullPath, $sUser = Default, $sPassword = Default, $sDriver = Default)

    If $sUser = Default Then
        $sUser = ''
    Else
        $sUser = 'Uid=' & $sUser & ';'
    EndIf

    If $sPassword = Default Then
        $sPassword = ''
    Else
        $sPassword = 'PWD=' & $sPassword & ';'
    EndIf

    If $sDriver = Default Then
        If StringRight($sFileFullPath, 6) = '.accdb' Then
            $sDriver = 'Microsoft Access Driver (*.mdb, *.accdb)'
        Else
            $sDriver = 'Microsoft Access Driver (*.mdb)'
        EndIf
    EndIf
    Local $sConnectionString = 'Driver={' & $sDriver & '};Dbq="' & $sFileFullPath & '";' & $sUser & $sPassword

    If Not StringRegExp($sConnectionString, '(?i)(Microsoft Access Driver \(*.mdb\)|Microsoft Access Driver \(*.mdb, *.accdb\))', $STR_REGEXPMATCH) Then
        $sConnectionString = StringReplace($sConnectionString, ';Dbq=', ' ;Data Source=')
    EndIf

    Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $sConnectionString)
EndFunc   ;==>_ADO_ConnectionString_Access

To:

Func _ADO_ConnectionString_Access($sFileFullPath, $sUser = Default, $sPassword = Default, $sDriver = Default)

    If $sUser = Default Then
        $sUser = ''
    Else
        $sUser = 'Uid=' & $sUser & ';'
    EndIf

    If $sPassword = Default Then
        $sPassword = ''
    Else
        $sPassword = 'PWD=' & $sPassword & ';'
    EndIf

    If $sDriver = Default Then
        If StringRight($sFileFullPath, 6) = '.accdb' Then
            $sDriver = 'Microsoft.ACE.OLEDB.12.0'
        Else
            $sDriver = 'Microsoft Access Driver (*.mdb)'
        EndIf
    EndIf
    Local $sConnectionString = 'Provider=' & $sDriver & ';Dbq="' & $sFileFullPath & '";' & $sUser & $sPassword

    If Not StringRegExp($sConnectionString, '(?i)(Microsoft Access Driver \(*.mdb\)|Microsoft Access Driver \(*.mdb, *.accdb\))', $STR_REGEXPMATCH) Then
        $sConnectionString = StringReplace($sConnectionString, ';Dbq=', ' ;Data Source=')
    EndIf

    Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $sConnectionString)
EndFunc   ;==>_ADO_ConnectionString_Access

 

Edited November 12, 2019 by crammaster
Problem solved

[mLipok]

On 10/18/2019 at 9:20 AM, colombeen said:

Why is there a difference when using _ADO_Execute to get an array in comparison to _SQL_FetchData ??

check example:
 

Func _Example_1_RecordsetToConsole($sConnectionString, $sQUERY)

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

    ; Executing some query
    Local $oRecordset = _ADO_Execute($oConnection, $sQUERY)

    ; Get recordset to array of arrays (Conent and ColumnNames)
    Local $aRecordsetAsArray = _ADO_Recordset_ToArray($oRecordset, False)

    ; Get inner array - only conent of Recordset
    Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRecordsetAsArray)

focus on: "Get inner array - only conent of Recordset"

REMARK: there is typo "conent" should be "content"

 

[mLipok]

On 11/5/2019 at 8:24 AM, crammaster said:

I had the problem that the original UDF with me could not open files of the format * .accdb (2007-2016).

This was already "discussed" here:

And additionaly it requrire additional driver.
So for this reason must think about adding this to examples.
But thanks.

[BJR]

Hello,

"select * from table_name" doesn't return data but only returns number of rows; Size of returned  array is equal to number of rows returned by query but their is no data (empty cells)

"select ColumnName1, ColumnName2, ColumnName3, ColumnName4 from table_name" also doesn't return data but only returns number of rows; Size of returned  array is equal to number of rows returned by query but their is no data (empty cells)

"select ColumnName1 from table_name" only returns valid data (Only for selected column in the query)

So how to use "select * from table_name" query which will return all data from all columns ?

I tried various versions including latest but all have same results.

Database :  MS SQL Server

Edited January 31, 2020 by BJR
Edit: MS SQL Server Added

[mLipok]

Please show in examples how you use all this mentioned queries , of course using UDF functions.

 

Edited January 30, 2020 by mLipok

[BJR]

11 hours ago, mLipok said:

Please show in examples how you use all this mentioned queries , of course using UDF functions.

 

Thanks for replying.

I have attached file :

1.zip

Edited January 31, 2020 by BJR

[BJR]

On 1/30/2020 at 10:20 PM, mLipok said:

Please show in examples how you use all this mentioned queries , of course using UDF functions.

 

What happened ?

3 Downloads  &  Still No Reply 😑

[mLipok]

 

 

 

1

15 hours ago, BJR said:

What happened ?

3 Downloads  &  Still No Reply 😑

I was busy.
Just downloaded few seconds before.

.....calculating in progress.....

[mLipok]

This do not look wrong.
But your question is not well enough described, it is not clear in the light of the information provided.

Please use one of this AdventureWorks example database

https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15

and show what you are trying to achieve.

 

[BJR]

9 hours ago, mLipok said:

This do not look wrong.
But your question is not well enough described, it is not clear in the light of the information provided.

Please use one of this AdventureWorks example database

https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15

and show what you are trying to achieve.

 

Thanks for replying.

Question Simplification:

-> Some SQL Queries Work

-> Select * From Table_Name Query Doesn't Work ( Does it work for you ? )

-> I am using old SQL Server Database ( I think 2005 or previous versions )

 

OK, I will download that database ( AdventureWorks LightWeight ) & share new code/file.

But this will take time.

I will try to post the code next week.

Edited February 4, 2020 by BJR

[ahmish]

Hi!, how do i pass value from Autoit String to SQL Database?

i have checked your examples but i can't seem to find how to do it.

here's what i have done now and this is only getting values from SQL Database and passing it to Autoit. (i want the opposite)

Func _Example_MSSQL_SQLServerAuthorization()
    Local $sDriver = 'SQL Server' <----- I dont know this but i left it as is and it works
    Local $sDatabase = '######' ; change this string to YourDatabaseName
    Local $sServer = '######' ; change this string to YourServerLocation
    Local $sUser = '######' ; change this string to YourUserName
    Local $sPassword = '######' ; change this string to YourPassword

    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    _Example_1_RecordsetToConsole($sConnectionString, "Select * from TEST_TABLE")
    ;_Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE")
    ;_Example_3_ConnectionProperties($sConnectionString)

    ;_Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword, "Select * from SOME_TABLE")

EndFunc   ;==>_Example_MSSQL

 

[Skysnake]

@ahmish is your question on how to do an INSERT or UPDATE query using ADO UDF?

 

[Stuzz]

I have a script that includes ADO.au3 which I created near the end of last year.  I also have a compiled .exe version of it.  I'm attempting to modify the script now, but when I try to build it (via F7 in Scite) I receive the following error:

---------------------------
Aut2Exe Error
---------------------------
Line 2393  (File "C:\Program Files (x86)\AutoIt3\Include\ADO.au3"):

Local $sParam = ($bOnlyYMD = True) ? '$1\/$2\/$3' : '$1\/$2\/$3\ $4:$5:$6'
Local $sParam = ($bOnlyYMD = True) ^ ERROR

Error: Unable to parse line.
---------------------------
OK   
---------------------------

Hopefully a simple error on my part, but I'm not sure where to start in order to fix it.

Thanks