Select Statement from SQL in Autoit

[CaptainBeardsEyesBeard]

Hi 

 

Could I get an example of a simple select statement from Windows sql? 

[SOLVE-SMART]

What do you mean by

Quote

[...] simple select statement from Windows sql [...]

?

Did you try to search the forum or even a google search for "AutoIt + SQL Server" or "AutoIt + MySql" or "AutoIt + SQLite" etc.?
Please provide more information and we can give better suggestions or even examples 🤝 .

Best regards
Sven

Edited April 4, 2023 by SOLVE-SMART

[Skeletor]

Use [DatabaseName]

SELECT *
FROM [TableName]

-- Depends on what you want to search
WHERE [Column] = "[Interger]"


--In other words:
Use AdventureWorks2018

SELECT *
FROM Employees

-- Depends on what you want to search
WHERE ID = "1234"

@CaptainBeardsEyesBeard

[CaptainBeardsEyesBeard]

27 minutes ago, SOLVE-SMART said:

What do you mean by

?

Did you try to search the forum or even a google search for "AutoIt + SQL Server" or "AutoIt + MySql" or "AutoIt + SQLite" etc.?
Please provide more information and we can give better suggestions or even examples 🤝 .

Best regards
Sven

Previously I've gone through and found some additional .au3 files none of which I was able to get to work. 

So I was just wondering if there was a latest SQL .au3 file with an example of a select statement from SQL server 

[SOLVE-SMART]

In case I understand you correct, you want to connect to a Microsoft SQL Server database?

Search here in the Files, Databases and web connections section for "sql" and you will get well prepared UDFs for MS SQL Server 😀 .

Best regards
Sven

[Skeletor]

Why not use Powershell and connect through that? 

[mLipok]

ADO.au3 UDF

[CaptainBeardsEyesBeard]

19 hours ago, mLipok said:

ADO.au3 UDF

Thanks. Could I get some help on this? 

So I want to connect to a Windows SQL server. So presumably my DSN would be something else? This page is empty on the wiki though

https://www.autoitscript.com/wiki/ADO_Example_SQL_Server

I copied this from the example and made the changes except 

1) DSN I'm not sure what this should be 

2) Port number - I'm not sure what this should be 

expandcollapse popup

#include <ADO.au3>
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

Global $sDSN = 'PostgreSQL35W'
Global $sDatabase = 'DBName'
Global $sServer = 'SERVER'
Global $sPort = ''
Global $sUser = 'user'
Global $sPassword = 'password'

; Internal ADO.au3 UDF COMError Handler
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)


    Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $sTableName = 'dbo.TestTable1'
    Local $sQUERY = 'Select * from ' & $sTableName
    Local $oRecordset = _ADO_Execute($oConnection, 'SELECT TOP (1000) [QATest1]  FROM [QATest].[dbo].[TestTable1])')

    Local $aRecordsetArray = _ADO_Recordset_ToArray($oRecordset, False)

    Local $aRecordset_inner = _ADO_RecordsetArray_GetContent($aRecordsetArray)

    Local $iColumn_count = UBound($aRecordset_inner, $UBOUND_COLUMNS)
    For $iRecord_idx = 0 To UBound($aRecordset_inner) - 1
        ConsoleWrite('==================================================================' & @CRLF)
        For $iColumn_idx = 0 To $iColumn_count - 1
            ConsoleWrite($aRecordset_inner[$iRecord_idx][$iColumn_idx] & @CRLF)
        Next
    Next

    ; CleanUp
    $oRecordset = Null
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

My current console output fails at the _ADO_ComErrorHandler_UserFunction though 

"testdelete this file.au3" (17) : ==> Unknown function name.:
_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)
_ADO_ComErrorHandler_UserFunction(^ ERROR
>Exit code: 1

 

Edited April 5, 2023 by CaptainBeardsEyesBeard

[mLipok]

2 hours ago, CaptainBeardsEyesBeard said:

Global $sDSN = 'PostgreSQL35W'

You are using PostgreSQL take a look on ADO_EXAMPLE_PostgreSQL_Skysnake.au3

and also: ADO_EXAMPLE.au3

Func _Example_PostgreSQL()
    ; http://www.tutorialspoint.com/postgresql/index.htm

;~  Local $sDriver = 'PostgreSQL ODBC Driver(ANSI)'
    Local $sDriver = 'PostgreSQL ANSI'
    Local $sDatabase = 'postgres' ; change this string to YourDatabaseName
    Local $sServer = 'localhost' ; change this string to YourServerLocation
    Local $sPort = '5432' ; change this string to If your Server use non standard PORT
    Local $sUser = 'postgres' ; change this string to YourUserName
    Local $sPassword = 'AutoIt' ; change this string to YourPassword

    #cs
        Local $sDSN = 'PostgreSQL35W'
        Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'
    #ce
    Local $sConnectionString = 'Driver={' & $sDriver & '};DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    Local $aSchema_Catalogs = _ADO_Schema_GetAllCatalogs($oConnection)
    _ADO_Recordset_Display($aSchema_Catalogs, '$aSchema_Catalogs')

    Local $oRecordset_Tables = _ADO_OpenSchema_Tables($oConnection, 'postgres')
    _ADO_Recordset_Display($oRecordset_Tables)
    If @error Then MsgBox($MB_ICONERROR, '_ADO_Recordset_Display OpenSchema_Tables', _
            '@error = ' & @error & @CRLF & '@extended = ' & @extended)

    _Example_1_RecordsetToConsole($sConnectionString, 'Select * from "SOME_TABLE"')
    _Example_2_RecordsetDisplay($sConnectionString, 'Select * from "SOME OTHER TABLE"')
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_PostgreSQL

 

2 hours ago, CaptainBeardsEyesBeard said:

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler)

_ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler_Function)

 

2 hours ago, CaptainBeardsEyesBeard said:

 Local $oRecordset = _ADO_Execute($oConnection, 'SELECT TOP (1000) [QATest1]  FROM [QATest].[dbo].[TestTable1])')

PosgreSQL do not use TOP but LIMIT at the end of query.
Btw: PostgreSQL
do not use [] notation the more shema usage like this: [QATest].[dbo]
You must create separate connection to each DB and then in select statement only use table name.

 

Edited April 5, 2023 by mLipok

[CaptainBeardsEyesBeard]

I think I'm after using SQL Server as i view the DB in SQL Management Server so the DSN would just be "SQL Server"? 

I returned to the ADO page and decided to use example2 

#include <ADO.au3>
#Tidy_Parameters=/sort_funcs /reel
#AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <AutoItConstants.au3>

Global $sDSN = 'SQL Server'
Global $sDatabase = 'db'
Global $sServer = 'server'
Global $sPort = ''
Global $sUser = 'user'
Global $sPassword = 'pw'

; Internal ADO.au3 UDF COMError Handler
_ADO_COMErrorHandler_UserFunction()

 Local $sConnectionString = 'DSN=' & $sDSN & ';DATABASE=' & $sDatabase & ';SERVER=' & $sServer & ';PORT=' & $sPort & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $oConnection = _ADO_Connection_Create()
    _ADO_Connection_OpenConString($oConnection, $sConnectionString)
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    Local $sTableName = 'QATable1'
    Local $sQUERY = 'Select * from ' & $sTableName
    Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True)

    ; CleanUp
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

    _ADO_REcordset_Display($aRecordset, $sTableName & '  - Recordset content')

The above code produces an error on the IF @error statement 

If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)
If @error Then ^ ERROR
>Exit code: 1

 

[mLipok]

you can not return from main code.... you are not in Func ... EndFunc

Edited April 6, 2023 by mLipok

[Skeletor]

You can try this, its for Windows Authentication, using @mLipok ADO 2.1.19 BETA.zip.

expandcollapse popup

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

; Define the server, database, and query
Local $sServer = "AutoItServer"
Local $sDatabase = "Users"
Local $sQuery = "SELECT * FROM Name"

; Call the _Example_5_MSSQL_WinAuth function
_Example_5_MSSQL_WinAuth($sServer, $sDatabase, $sQuery)

Func _Example_5_MSSQL_WinAuth($sServer, $sDatabase, $sQuery)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with Windows Authentication
    _ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, Default, Default, 'YourAppName', @ComputerName & '_' & 'YourProgram_UID', False)
    If @error Then
        MsgBox(16, "Error", "Failed to connect to the database. Error: " & @error)
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    EndIf

    ; Execute query and fetch results into an array
    Local $aRecordset = _ADO_Execute($oConnection, $sQuery, True)

    ; Check for query errors
    If @error Then
        MsgBox(16, "Error", "Failed to execute the query. Error: " & @error)
        _ADO_Connection_Close($oConnection)
        $oConnection = Null
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    EndIf

    ; Display the results
    If IsArray($aRecordset) Then
        _ADO_Recordset_Display($aRecordset, "Recordset content")
    Else
        MsgBox(64, "Info", "No results found.")
    EndIf

    ; Clean up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

EndFunc   ;==>_Example_5_MSSQL_WinAuth

 

Edited April 6, 2023 by Skeletor

[Skeletor]

For SQL Authentication, you can try this. Also, in case you ended up with an error like me, to make sure the appropriate OLE DB driver or ODBC driver for your version of Microsoft SQL Server is on the system where the script is running. 

expandcollapse popup

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

; Define the server, database, query, username, and password
Local $sServer = "AutoItServer"
Local $sDatabase = "Users"
Local $sQuery = "SELECT * FROM Name"
Local $sUsername = "YourUsername"
Local $sPassword = "YourPassword"

; Call the _Example_5_MSSQL_SQLAuth function
_Example_5_MSSQL_SQLAuth($sServer, $sDatabase, $sQuery, $sUsername, $sPassword)

Func _Example_5_MSSQL_SQLAuth($sServer, $sDatabase, $sQuery, $sUsername, $sPassword)

    ; Create connection object
    Local $oConnection = _ADO_Connection_Create()

    ; Open connection with SQL Server Authentication
    _ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, $sUsername, $sPassword, 'YourAppName', @ComputerName & '_' & 'YourProgram_UID', False)
    If @error Then
        MsgBox(16, "Error", "Failed to connect to the database. Error: " & @error)
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    EndIf

    ; Execute query and fetch results into an array
    Local $aRecordset = _ADO_Execute($oConnection, $sQuery, True)

    ; Check for query errors
    If @error Then
        MsgBox(16, "Error", "Failed to execute the query. Error: " & @error)
        _ADO_Connection_Close($oConnection)
        $oConnection = Null
        Return SetError(@error, @extended, $ADO_RET_FAILURE)
    EndIf

    ; Display the results
    If IsArray($aRecordset) Then
        _ADO_Recordset_Display($aRecordset, "Recordset content")
    Else
        MsgBox(64, "Info", "No results found.")
    EndIf

    ; Clean up
    _ADO_Connection_Close($oConnection)
    $oConnection = Null

EndFunc   ;==>_Example_5_MSSQL_SQLAuth

 

Edited April 6, 2023 by Skeletor

[Zedna]

Also look at my simple SQL ADO examples without ADO UDF:

 

 

Edited April 9, 2023 by Zedna

[CaptainBeardsEyesBeard]

On 4/9/2023 at 9:18 AM, Zedna said:

Also look at my simple SQL ADO examples without ADO UDF:

 

 

Thank you that first example. 

 

I take it that still relies on the ADO #include line? 

Running this I get it tell me my password is incorrect but I have tried a few times and am certain it is correct (just logged into another pc with it) 

the format of input I'm putting in is like below 

$sServer = 'server\server'
$sDatabase = 'db'
$sUID = 'domain\user'
$sPWD = 'password'

 

 

[mLipok]

You are using SQL or DOMAIN login / password

?

Edited April 11, 2023 by mLipok

[CaptainBeardsEyesBeard]

When I connect to SQL Server Management the authentication is Windows Authentication?

[mLipok]

So which connection as m string you are using ?

[mLipok]

23 hours ago, CaptainBeardsEyesBeard said:

When I connect to SQL Server Management the authentication is Windows Authentication?

I ask about the connection string becuase you want to use windows Authentication but instead you are trying to use SQL auth in Connection String.

take a look here:

https://www.connectionstrings.com/sql-server/

 

Quote

 

Standard Security

Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

  SQL Server 2019SQL Server 2017SQL Server 2016SQL Server 2014SQL Server 2012SQL Server 2008SQL Server 2005SQL Server 2000SQL Server 7.0

Trusted Connection

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

 

 

SUMMARY:

Standard Security is SQL AUTH and what you want is Windows Authentication so you should to use "Truested Connection"

 

Edited April 12, 2023 by mLipok

[mLipok]

btw.
in ADO_EXAMPLE.au3

there are:

Func _Example_MSSQL_SQLServerAuthorization()
    Local $sDriver = 'SQL Server'
    Local $sDatabase = 'YourBASENAME' ; change this string to YourDatabaseName
    Local $sServer = 'localhost\SQLExpress' ; change this string to YourServerLocation
    Local $sUser = 'sa' ; change this string to YourUserName
    Local $sPassword = 'AutoIt' ; change this string to YourPassword

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

    _Example_1_RecordsetToConsole($sConnectionString, "Select * from SOME_TABLE")
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

    _Example_2_RecordsetDisplay($sConnectionString, "Select * from SOME_TABLE")
    If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE)

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

    _Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword, "Select * from SOME_TABLE")
    Return SetError(@error, @extended)
EndFunc   ;==>_Example_MSSQL

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