Azure SQL Database Connection Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
TablePerson22
Posts: 40
Joined: 04 Mar 2021, 20:27

Azure SQL Database Connection

16 Sep 2021, 18:04

Hi Everyone,

I'm trying to connect to an Azure SQL Database.
I've been looking through other posts on this but can't see what I'm doing wrong.
I get no result / blank msgbox:

Code: Select all

#NoEnv
#SingleInstance,Force

userid := "01"
mypassword := "###"
myserver := "testcloud01.cloudapp.net"
port := "6011"
mydatabase := "ABC"


Connection_String := "Server=" myserver "," port ";Database=" mydatabase ";User Id=" userid ";Password=" myPassword ";"

sql_query := "SELECT JobNo FROM VQM_Jobs" ; VQM_Jobs = table / column = JobNo

Result := ADOSQL(Connection_String, sql_query)

msgbox, % Result

return



/*
###############################################################################################################
######                                      ADOSQL v5.04L - By [VxE]                                     ######
###############################################################################################################
	Wraps the utility of ADODB to connect to a database, submit a query, and read the resulting recordset.
	Returns the result as a new object (or array of objects, if the query has multiple statements).
	To instead have this function return a string, include a delimiter option in the connection string.
	For AHK-L (v1.1 or later).
	Freely available @ http://auto-hotkey.com/boards/viewtopic.php?p=365#p365
	IMPORTANT! Before you can use this library, you must have access to a database AND know the connection
	string to connect to your database.
	Varieties of databases will have different connection string formats, and different drivers (providers).
	Use the mighty internet to discover the connection string format and driver for your type of database.
	Example connection string for SQLServer (2005) listening on port 3456 and with a static IP:
	DRIVER={SQL SERVER};SERVER=192.168.0.12,3456;DATABASE=mydb;UID=admin;PWD=12345;APP=AHK
*/

Global ADOSQL_LastError, ADOSQL_LastQuery ; These super-globals are for debugging your SQL queries.

ADOSQL( Connection_String, Query_Statement ) {
; Uses an ADODB object to connect to a database, submit a query and read the resulting recordset.
; By default, this function returns an object. If the query generates exactly one result set, the object is
; a 2-dimensional array containing that result (the first row contains the column names). Otherwise, the
; returned object is an array of all the results. To instead have this function return a string, append either
; ";RowDelim=`n" or ";ColDelim=`t" to the connection string (substitute your preferences for "`n" and "`t").
; If there is more than one table in the output string, they are separated by 3 consecutive row-delimiters.
; ErrorLevel is set to "Error" if ADODB is not available, or the COM error code if a COM error is encountered.
; Otherwise ErrorLevel is set to zero.
	
	coer := "", txtout := 0, rd := "`n", cd := "CSV", str := Connection_String ; 'str' is shorter.
	
; Examine the connection string for output formatting options.
	If ( 9 < oTbl := 9 + InStr( ";" str, ";RowDelim=" ) )
	{
		rd := SubStr( str, oTbl, 0 - oTbl + oRow := InStr( str ";", ";", 0, oTbl ) )
		str := SubStr( str, 1, oTbl - 11 ) SubStr( str, oRow )
		txtout := 1
	}
	If ( 9 < oTbl := 9 + InStr( ";" str, ";ColDelim=" ) )
	{
		cd := SubStr( str, oTbl, 0 - oTbl + oRow := InStr( str ";", ";", 0, oTbl ) )
		str := SubStr( str, 1, oTbl - 11 ) SubStr( str, oRow )
		txtout := 1
	}
	
	ComObjError( 0 ) ; We'll manage COM errors manually.
	
; Create a connection object. > http://www.w3schools.com/ado/ado_ref_connection.asp
; If something goes wrong here, return blank and set the error message.
	If !( oCon := ComObjCreate( "ADODB.Connection" ) )
		Return "", ComObjError( 1 ), ErrorLevel := "Error"
		, ADOSQL_LastError := "Fatal Error: ADODB is not available."
	
	
	oCon.ConnectionTimeout := 9 ; Allow 9 seconds to connect to the server.
	oCon.CursorLocation := 3 ; Use a client-side cursor server.
	oCon.CommandTimeout := 1800 ; A generous 30 minute timeout on the actual SQL statement.
	oCon.Open( str ) ; open the connection.
	
; Execute the query statement and get the recordset. > http://www.w3schools.com/ado/ado_ref_recordset.asp
	If !( coer := A_LastError )
		oRec := oCon.execute( ADOSQL_LastQuery := Query_Statement )
	
	If !( coer := A_LastError ) ; The query executed OK, so examine the recordsets.
	{
		o3DA := [] ; This is a 3-dimensional array.
		While IsObject( oRec )
			If !oRec.State ; Recordset.State is zero if the recordset is closed, so we skip it.
				oRec := oRec.NextRecordset()
		Else ; A row-returning operation returns an open recordset
		{
			oFld := oRec.Fields
			o3DA.Insert( oTbl := [] )
			oTbl.Insert( oRow := [] )
			
			Loop % cols := oFld.Count ; Put the column names in the first row.
				oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Name
			
			While !oRec.EOF ; While the record pointer is not at the end of the recordset...
			{
				oTbl.Insert( oRow := [] )
				oRow.SetCapacity( cols ) ; Might improve performance on huge tables??
				Loop % cols
					oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Value	
				oRec.MoveNext() ; move the record pointer to the next row of values
			}
			
			oRec := oRec.NextRecordset() ; Get the next recordset.
		}
		
		If (txtout) ; If the user wants plaintext output, copy the results into a string
		{
			Query_Statement := "x"
			Loop % o3DA.MaxIndex()
			{
				Query_Statement .= rd rd
				oTbl := o3DA[ A_Index ]
				Loop % oTbl.MaxIndex()
				{
					oRow := oTbl[ A_Index ]
					Loop % oRow.MaxIndex()
						If ( cd = "CSV" )
						{
							str := oRow[ A_Index ]
							StringReplace, str, str, ", "", A
							If !ErrorLevel || InStr( str, "," ) || InStr( str, rd )
								str := """" str """"
							Query_Statement .= ( A_Index = 1 ? rd : "," ) str
						}
					Else
						Query_Statement .= ( A_Index = 1 ? rd : cd ) oRow[ A_Index ]
				}
			}
			Query_Statement := SubStr( Query_Statement, 2 + 3 * StrLen( rd ) )
		}
	}
	Else ; Oh NOES!! Put a description of each error in 'ADOSQL_LastError'.
	{
		oErr := oCon.Errors ; > http://www.w3schools.com/ado/ado_ref_error.asp
		Query_Statement := "x"
		Loop % oErr.Count
		{
			oFld := oErr.Item( A_Index - 1 )
			str := oFld.Description
			Query_Statement .= "`n`n" SubStr( str, 1 + InStr( str, "]", 0, 2 + InStr( str, "][", 0, 0 ) ) )
				. "`n   Number: " oFld.Number
				. ", NativeError: " oFld.NativeError
				. ", Source: " oFld.Source
				. ", SQLState: " oFld.SQLState
		}
		ADOSQL_LastError := SubStr( Query_Statement, 4 )
		Query_Statement := ""
		txtout := 1
	}
	
; Close the connection and return the result. Local objects are cleaned up as the function returns.
	oCon.Close()
	ComObjError( 1 )
	ErrorLevel := coer
	Return txtout ? Query_Statement : o3DA.MaxIndex() = 1 ? o3DA[1] : o3DA
} ; END - ADOSQL( Connection_String, Query_Statement )
swagfag
Posts: 6222
Joined: 11 Jan 2017, 17:59

Re: Azure SQL Database Connection

16 Sep 2021, 18:13

Code: Select all

Global ADOSQL_LastError, ADOSQL_LastQuery ; These super-globals are for debugging your SQL queries.
and even so ull probably have to dig into the internals of this function anyway
ostius
Posts: 5
Joined: 22 Mar 2019, 10:14

Re: Azure SQL Database Connection

16 Sep 2021, 18:29

Are you using 32 bit AutoHotkey? ADODB doesn’t work with 64 bit AHK.
swagfag
Posts: 6222
Joined: 11 Jan 2017, 17:59

Re: Azure SQL Database Connection

16 Sep 2021, 18:54

ostius wrote:
16 Sep 2021, 18:29
ADODB doesn’t work with 64 bit AHK.
says who?

doesnt work if u dont got the driver probably... so, go get it
TablePerson22
Posts: 40
Joined: 04 Mar 2021, 20:27

Re: Azure SQL Database Connection

30 Sep 2021, 14:29

Thanks for your replies.
I'm using 64 bit, but also tried in 32 bit.
I have ODBC Driver 17 for SQL Server installed and this works using python:

Code: Select all

import pyodbc 
server = 'tcp:name.cloudapp.net,6011' 
database = 'ABC-123456' 
username = 'ABC-123456-01' 
password = 'abc123123@abc' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+password)
cursor = cnxn.cursor()
cursor.execute("SELECT @@version")
for row in cursor:
  print(row)
In AHK I get the below error. It's progress, but it seems I've hit a wall for now.
I wonder if the 'Login Failed' error has something to do with an 'at sign' being in the password, and it not being escaped properly?

Code: Select all

#NoEnv
#SingleInstance,Force

myuserid := "ABC-123456-01"
mypassword := "abc123123@abc"
myserver := "tcp:name.cloudapp.net,6011"
mydatabase := "ABC-123456"
mydriver := "{ODBC Driver 17 for SQL Server}"

Connection_String := "DRIVER=" mydriver ";SERVER=" myserver ";DATABASE=" mydatabase ";UID=" userid ";PWD=" mypassword

sql_query := "SELECT @@version"

Result := ADOSQL(Connection_String, sql_query)

msgbox % Result

msgbox, % ADOSQL_LastError
msgbox, % ADOSQL_LastQuery

return

/*
###############################################################################################################
######                                      ADOSQL v5.04L - By [VxE]                                     ######
###############################################################################################################
	Wraps the utility of ADODB to connect to a database, submit a query, and read the resulting recordset.
	Returns the result as a new object (or array of objects, if the query has multiple statements).
	To instead have this function return a string, include a delimiter option in the connection string.
	For AHK-L (v1.1 or later).
	Freely available @ http://auto-hotkey.com/boards/viewtopic.php?p=365#p365
	IMPORTANT! Before you can use this library, you must have access to a database AND know the connection
	string to connect to your database.
	Varieties of databases will have different connection string formats, and different drivers (providers).
	Use the mighty internet to discover the connection string format and driver for your type of database.
	Example connection string for SQLServer (2005) listening on port 3456 and with a static IP:
	DRIVER={SQL SERVER};SERVER=192.168.0.12,3456;DATABASE=mydb;UID=admin;PWD=12345;APP=AHK
*/

Global ADOSQL_LastError, ADOSQL_LastQuery ; These super-globals are for debugging your SQL queries.

ADOSQL( Connection_String, Query_Statement ) {
; Uses an ADODB object to connect to a database, submit a query and read the resulting recordset.
; By default, this function returns an object. If the query generates exactly one result set, the object is
; a 2-dimensional array containing that result (the first row contains the column names). Otherwise, the
; returned object is an array of all the results. To instead have this function return a string, append either
; ";RowDelim=`n" or ";ColDelim=`t" to the connection string (substitute your preferences for "`n" and "`t").
; If there is more than one table in the output string, they are separated by 3 consecutive row-delimiters.
; ErrorLevel is set to "Error" if ADODB is not available, or the COM error code if a COM error is encountered.
; Otherwise ErrorLevel is set to zero.
	
	coer := "", txtout := 0, rd := "`n", cd := "CSV", str := Connection_String ; 'str' is shorter.
	
; Examine the connection string for output formatting options.
	If ( 9 < oTbl := 9 + InStr( ";" str, ";RowDelim=" ) )
	{
		rd := SubStr( str, oTbl, 0 - oTbl + oRow := InStr( str ";", ";", 0, oTbl ) )
		str := SubStr( str, 1, oTbl - 11 ) SubStr( str, oRow )
		txtout := 1
	}
	If ( 9 < oTbl := 9 + InStr( ";" str, ";ColDelim=" ) )
	{
		cd := SubStr( str, oTbl, 0 - oTbl + oRow := InStr( str ";", ";", 0, oTbl ) )
		str := SubStr( str, 1, oTbl - 11 ) SubStr( str, oRow )
		txtout := 1
	}
	
	ComObjError( 0 ) ; We'll manage COM errors manually.
	
; Create a connection object. > http://www.w3schools.com/ado/ado_ref_connection.asp
; If something goes wrong here, return blank and set the error message.
	If !( oCon := ComObjCreate( "ADODB.Connection" ) )
		Return "", ComObjError( 1 ), ErrorLevel := "Error"
		, ADOSQL_LastError := "Fatal Error: ADODB is not available."
	
	
	oCon.ConnectionTimeout := 9 ; Allow 9 seconds to connect to the server.
	oCon.CursorLocation := 3 ; Use a client-side cursor server.
	oCon.CommandTimeout := 1800 ; A generous 30 minute timeout on the actual SQL statement.
	oCon.Open( str ) ; open the connection.
	
; Execute the query statement and get the recordset. > http://www.w3schools.com/ado/ado_ref_recordset.asp
	If !( coer := A_LastError )
		oRec := oCon.execute( ADOSQL_LastQuery := Query_Statement )
	
	If !( coer := A_LastError ) ; The query executed OK, so examine the recordsets.
	{
		o3DA := [] ; This is a 3-dimensional array.
		While IsObject( oRec )
			If !oRec.State ; Recordset.State is zero if the recordset is closed, so we skip it.
				oRec := oRec.NextRecordset()
		Else ; A row-returning operation returns an open recordset
		{
			oFld := oRec.Fields
			o3DA.Insert( oTbl := [] )
			oTbl.Insert( oRow := [] )
			
			Loop % cols := oFld.Count ; Put the column names in the first row.
				oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Name
			
			While !oRec.EOF ; While the record pointer is not at the end of the recordset...
			{
				oTbl.Insert( oRow := [] )
				oRow.SetCapacity( cols ) ; Might improve performance on huge tables??
				Loop % cols
					oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Value	
				oRec.MoveNext() ; move the record pointer to the next row of values
			}
			
			oRec := oRec.NextRecordset() ; Get the next recordset.
		}
		
		If (txtout) ; If the user wants plaintext output, copy the results into a string
		{
			Query_Statement := "x"
			Loop % o3DA.MaxIndex()
			{
				Query_Statement .= rd rd
				oTbl := o3DA[ A_Index ]
				Loop % oTbl.MaxIndex()
				{
					oRow := oTbl[ A_Index ]
					Loop % oRow.MaxIndex()
						If ( cd = "CSV" )
						{
							str := oRow[ A_Index ]
							StringReplace, str, str, ", "", A
							If !ErrorLevel || InStr( str, "," ) || InStr( str, rd )
								str := """" str """"
							Query_Statement .= ( A_Index = 1 ? rd : "," ) str
						}
					Else
						Query_Statement .= ( A_Index = 1 ? rd : cd ) oRow[ A_Index ]
				}
			}
			Query_Statement := SubStr( Query_Statement, 2 + 3 * StrLen( rd ) )
		}
	}
	Else ; Oh NOES!! Put a description of each error in 'ADOSQL_LastError'.
	{
		oErr := oCon.Errors ; > http://www.w3schools.com/ado/ado_ref_error.asp
		Query_Statement := "x"
		Loop % oErr.Count
		{
			oFld := oErr.Item( A_Index - 1 )
			str := oFld.Description
			Query_Statement .= "`n`n" SubStr( str, 1 + InStr( str, "]", 0, 2 + InStr( str, "][", 0, 0 ) ) )
				. "`n   Number: " oFld.Number
				. ", NativeError: " oFld.NativeError
				. ", Source: " oFld.Source
				. ", SQLState: " oFld.SQLState
		}
		ADOSQL_LastError := SubStr( Query_Statement, 4 )
		Query_Statement := ""
		txtout := 1
	}
	
; Close the connection and return the result. Local objects are cleaned up as the function returns.
	oCon.Close()
	ComObjError( 1 )
	ErrorLevel := coer
	Return txtout ? Query_Statement : o3DA.MaxIndex() = 1 ? o3DA[1] : o3DA
} ; END - ADOSQL( Connection_String, Query_Statement )

Screenshot 2021-09-30 114635.png
Screenshot 2021-09-30 114635.png (6.79 KiB) Viewed 2269 times
Ahk_fan
Posts: 237
Joined: 31 Aug 2018, 14:34
Contact:

Re: Azure SQL Database Connection  Topic is solved

30 Sep 2021, 14:37

try?
--> myuserid := "ABC-123456-01"
myuserid != userid
Connection_String := "DRIVER=" mydriver ";SERVER=" myserver ";DATABASE=" mydatabase ";UID=" userid ";PWD=" mypassword
regards,
AHK_fan :)
https://hr-anwendungen.de
TablePerson22
Posts: 40
Joined: 04 Mar 2021, 20:27

Re: Azure SQL Database Connection

30 Sep 2021, 15:14

Oh, it's always a typo. Thanks for catching that - it works now!
swagfag
Posts: 6222
Joined: 11 Jan 2017, 17:59

Re: Azure SQL Database Connection

08 Oct 2021, 19:45

probably worth switching #Warn UseUnsetLocal/-Global on then
or just #Warn altogether
or moving to v2 lol

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: mikeyww, steve88 and 181 guests