[Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

Post your working scripts, libraries and tools
keiiz
Posts: 3
Joined: 24 Apr 2015, 19:20

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

09 Jan 2016, 01:55

A few more observations.. Note: I could be very wrong Iabout all of this, however, 'm just providing my own use observations in case it helps anyone..

It doesn't seem like either ErrorLevel or A_LastError are returned by ADO when errors are produced (atleast it doesn't seem like it is for me)
hence, the Error section never triggers for me
hence

Code: Select all

	If !( coer := A_LastError )
		oRec := oCon.execute( ADOSQL_LastQuery := Query_Statement)

	If !( coer := A_LastError ) ; The query executed OK, so examine the recordsets.
	{
always return true
and the corresponding else error section never fire for me:

Code: Select all

	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
	}
Checking for erros via

Code: Select all

		oErr := oCon.Errors ; > http://www.w3schools.com/ado/ado_ref_error.asp
		if(oErr)
			msgbox % "ERROR present!"
		Else
			msgBox % "No error present!"
seems to work correctly for me.... but like I said at the start.. I've mostly likely screwed something up royally... so don't hold me to anything I say!
Hopefully I haven't butchered your code too badly VxE..! But your function has helped greatly!

Cheers!
User avatar
Gio
Posts: 1010
Joined: 30 Sep 2013, 10:54
Location: Brazil

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

18 Jan 2016, 21:40

keiiz wrote:Is it possible to return the rows affected from the query?
Hello Keiiz.

You question has caught my attention a few days ago. One of my applications uses ADOSQL() and was indeed getting some false positives of data insertion in the DB.

Albeit rare, some of the queries were not registering in the DB despite the function outputing no errors. It happened around once in every 2000 submited queries, and i was unable to reproduce the behaviour for testing (though i was able to confirm 100% that it did happened).

Anyways, after digging around some i found the answer to your original question.

Yes, it is possible.

However, the docs for AutoHotkey functions state that:
Known Limitations:
ByRef is not directly supported in functions called by COM clients, or when calling COM methods. Instead, the script receives or must pass a wrapper object containing the VarType and address of the value.
Which follows that a small change to the code is indeed required to make it output the number of rows affected. One option is to change these lines in VxEs function:

Code: Select all

; 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 )
To something like that (the msgbox line in the end is just for testing, though it also explains how to dereference the value from the wrapped object):

Code: Select all

; Execute the query statement and get the recordset. > http://www.w3schools.com/ado/ado_ref_recordset.asp
	If !( coer := A_LastError )
	{
		; Code below executes the query and displays the number of affected rows.
		; As explained in the AutoHotkey Docs:
		; https://autohotkey.com/docs/commands/ComObjActive.htm#ByRefEx
		AFFECTED_ROWS := ComVar()
		AFFECTED_ROWS[] := "Warning: Could not retrieve number of affected rows!" ; This value will only be present if the function fails to retrieve the number of affected rows. Brackets are required to assign a value in this case.
		oRec := oCon.execute( ADOSQL_LastQuery := Query_Statement, AFFECTED_ROWS.ref ) ; ".ref" is required here.
		msgbox % AFFECTED_ROWS[] ; brackets are required to dereference the value. Warning: Comparisson and assignments treat two wrapper objects as unique, even if they contain the same COM object.
	}
And than append the following functions to the end of your script:

Code: Select all

ComVar(Type=0xC)
{
    static base := { __Get: "ComVarGet", __Set: "ComVarSet", __Delete: "ComVarDel" }
    ; Create an array of 1 VARIANT.  This method allows built-in code to take
    ; care of all conversions between VARIANT and AutoHotkey internal types.
    arr := ComObjArray(Type, 1)
    ; Lock the array and retrieve a pointer to the VARIANT.
    DllCall("oleaut32\SafeArrayAccessData", "ptr", ComObjValue(arr), "ptr*", arr_data)
    ; Store the array and an object which can be used to pass the VARIANT ByRef.
    return { ref: ComObject(0x4000|Type, arr_data), _: arr, base: base }
}

ComVarGet(cv, p*) { ; Called when script accesses an unknown field.
    if p.MaxIndex() = "" ; No name/parameters, i.e. cv[]
        return cv._[0]
}

ComVarSet(cv, v, p*) { ; Called when script sets an unknown field.
    if p.MaxIndex() = "" ; No name/parameters, i.e. cv[]:=v
        return cv._[0] := v
}

ComVarDel(cv) { ; Called when the object is being freed.
    ; This must be done to allow the internal array to be freed.
    DllCall("oleaut32\SafeArrayUnaccessData", "ptr", ComObjValue(cv._))
}
Return
Most of these changes follow up an example in the AutoHotkey Docs themselves, which can be found in the ComObjActive() pages of the help files.

Also note that there is a reference to version 1.1.17+ in that page (update AutoHotkey if your version is older than that).
and is this the best way to tell if inserts are successful?
Of that i can't be sure yet. But i will certainly be adding this to my code to see if i can get rid of that 1/2000 chance of false positive insertions :p

Best wishes.
theironspud

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

15 Feb 2016, 07:10

As a note, I had some difficulty in executing UPDATE queries, and had to modify the line:

While !oRec.EOF

to

While oRec.EOF = 0

For some reason, the variable was not populating on "True", so it would fill in with blanks.
kwibus
Posts: 9
Joined: 15 Feb 2016, 20:42

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

24 Feb 2016, 06:56

Hi VxE,

Trying out the code in the first post on this subject, I get an empty MsgBox.

What could be wrong?

Cheers, Ad
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

22 Mar 2016, 18:05

Hi, :)

I execute a query on SQL Server
SELECT * FROM [databasse].[dbo].[Table_1] WHERE [rank] = '1' AND [first_scan_time] > '20:18:58'
I wonder if it is possible to retrieve the number of records.

http://www.hostingpics.net/viewer.php?i ... apture.png

Thank you

EDIT:

I found objReturn.MaxIndex()
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
Marvin1023
Posts: 45
Joined: 10 Nov 2015, 12:49

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

03 Nov 2016, 15:50

Hi,

I use <CLASS_ADOSQL> with MSSQL this work.

Now, I would like use with MS Access.

This is my script:

Code: Select all

#SINGLEINSTANCE FORCE

F1::
TEST_SERVER := "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\xxxxxxx\Desktop\db\db.accdb"
TEST_DATABASE := "tsaisie"

TEST_REQUEST := "SELECT * FROM " . TEST_DATABASE . " WHERE [Date] = '02/11/2016';"
RS:=ADOSQL(TEST_SERVER, TEST_REQUEST)

MsgBox, % TEST_REQUEST ; It's OK

MsgBox, % ADOSQL_LASTQUERY ; Result empty
MsgBox, % ADOSQL_LASTERROR ; Result empty

MsgBox, % RS.MAXINDEX() ; Result empty

Return

#INCLUDE <CLASS_ADOSQL>
But its not working.

All the msgbox is empty.

I do not understand why

Thank for your help

Edit: Work with ahk 32bit.
check :=> Control Panel \ All Control Panel \ Administrative Tools \ ODBC Data Sources (32-bit) or (64-bit)
Asus Maximus VIII Extrême - DDR4 16Go - Intel Core i7 - GTX 1080
eekhelpspike
Posts: 16
Joined: 05 Jul 2015, 17:51

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

10 Aug 2017, 12:32

I'm having an issue where a column isn't returning any results. The column header is shown, but the field is blank.

Code: Select all

connection_string =
( ltrim join;
	DRIVER={SQL SERVER};SERVER=192.168.10.254;DATABASE=WDB;UID=Reporter;PWD=Blanked;APP=AHK
)

WWQueryCSHist = 
(
	SELECT 
		Xs.XBatch,
		Xs.XNo,
		Xs.AcID,
		Xs.AtID,
		Xs.OP,
		Xs.LabelName,
		CSHistErr.RCd,
		CSHistOpt.FieldCd,
		CSHistOpt.FieldVal

	FROM
		WDB.ARM.CSHist CSHist,
		WDB.ARM.CSHistErr CSHistErr,
		WDB.ARM.CSHistOpt CSHistOpt,
		WDB.X.Xs Xs
	WHERE
		Xs.XBatch = convert(varchar, DAY(getdate()), 100) AND
		CSHist.AtID = Xs.AtID AND
		CSHist.XNo = Xs.XNo AND
		CSHistErr.AtID = CSHist.AtID AND
		CSHistErr.Payor = CSHist.Payor AND
		CSHistErr.RecType = CSHist.RecType AND
		CSHistErr.XNo = CSHist.XNo AND
		CSHistErr.Submitted = CSHist.Submitted AND		
		((Xs.ElecRej=1) AND
		(Xs.OP=CSHist.insid) AND
		(CSHistErr.RCd='79' Or CSHistErr.RCd='88') AND
		(CSHistOpt.FieldCd='fq') AND
		(CSHistOpt.FieldVal Like '%17%'))
	GROUP BY
		Xs.XBatch,
		Xs.XNo,
		Xs.AcID,
		Xs.AtID,
		Xs.OP,
		Xs.LabelName,
		CSHistErr.RCd,
		CSHistOpt.FieldCd,
		CSHistOpt.FieldVal
)

CSHistString := ADOSQL(connection_string ";ColDelim=`t", WWQueryCSHist)

Msgbox, % CSHistString

ExitApp
Returns this:

Code: Select all

XBatch	XNo		AcID	AtID	OP		LabelName			RCd	FieldCd		FieldVal
10		9114111	994		98765	OPPC	JOHNSMITHHELLO 125	69 	FL	 
Same query in Excel returns this:

Code: Select all

XBatch	XNo		AcID	AtID	OP		LabelName			RCd	FieldCd		FieldVal
10		9114111	994		98765	OPPC	JOHNSMITHHELLO 125	69 	FL			NEXT AVAIL 11-AUG-17
XBatch	XNo		AcID	AtID	OP		LabelName			RCd	FieldCd		FieldVal
10		9114111	994		98765	OPPC	JOHNSMITHHELLO 125	69 	FL			786584 5412544 1.
I guess it has something to do with fieldval consisting of multiple values or lines?
User avatar
Gio
Posts: 1010
Joined: 30 Sep 2013, 10:54
Location: Brazil

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

11 Aug 2017, 10:54

Hello Eekhelpspike.

Welcome to the AutoHotkey Community forums.

I cannot reproduce the issue for testing without a prototype of the tables, but i have a strong feeling that the issue may be in this line:

Code: Select all

(CSHistOpt.FieldVal Like '%17%'))
AutoHotkey regards percent signs (%) as having a special meaning in literal strings. When you enclose something between percent signs, it tries to dereference it into a value, which may be causing the query to be sent as

Code: Select all

(CSHistOpt.FieldVal Like ''))
It also supports this conclusion the fact that the output you display for AHK lacks a value in the FieldVal column.

To solve this issue, you can use the escape character (`) to make AutoHotkey interpret the percent signs as literal text.

Code: Select all

(CSHistOpt.FieldVal Like '`%17`%'))
Reference for further reading: https://autohotkey.com/docs/commands/_EscapeChar.htm

On another note, regarding the tables in the FROM clause of your Query, it is (usually) not a good idea to specify many tables without any JOINs. By specifying the correct JOINs you will gain much more control over the results.

Best wishes.
eekhelpspike
Posts: 16
Joined: 05 Jul 2015, 17:51

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

11 Aug 2017, 12:04

Gio wrote:I cannot reproduce the issue for testing without a prototype of the tables, but i have a strong feeling that the issue may be in this line:

Code: Select all

(CSHistOpt.FieldVal Like '%17%'))
AutoHotkey regards percent signs (%) as having a special meaning in literal strings.
Oh my goodness, I am so dumb. I don't know how I could've missed that. I could've sworn I broke it down, line by line. I guess not. I literally wasted hours on that. Thank you so much!
User avatar
kczx3
Posts: 1121
Joined: 06 Oct 2015, 21:39

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

17 Aug 2017, 14:50

Can anyone confirm for me that AHK is unable to handle ADO Events? I think that is the case when looking at the Note above the events listed here - https://www.w3schools.com/asp/ado_ref_connection.asp
DiscoStu
Posts: 11
Joined: 16 Mar 2016, 20:11

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

15 Nov 2017, 20:12

I am having trouble with connecting properly. If I run the identical query directly on the server I get the correct result. But when I run it via AHK on a client I get no result, but I also get no ADOSQL errors. If I change the connection string I do get errors, which suggests that the connection string is valid. Any thoughts? Note I have changed the server and database names in the connection string.

Code: Select all

ConnectString := "Driver={SQL Server};Server=SERVERNAME\INSTANCENAME;Database=DBNAME;Uid=USERNAME;Pwd="

query_Statement := "SELECT Surname FROM PBPATMAS WHERE FileNo = 'A1234567'"

objReturn := ADOSQL(ConnectString, query_Statement)

MsgBox, Result: %objReturn%
MsgBox, Last Query: %ADOSQL_LastQuery%
MsgBox, Last Error: %ADOSQL_LastError%
User avatar
Gio
Posts: 1010
Joined: 30 Sep 2013, 10:54
Location: Brazil

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

15 Nov 2017, 20:16

Hello DiscoStu.

objReturn is a 2D table-like object, so you cannot dereference it like if it was a simple variable. Syntax goes like objReturn[Row, Column] and has to be used in an expression context.

So try this:

Code: Select all

ConnectString := "Driver={SQL Server};Server=SERVERNAME\INSTANCENAME;Database=DBNAME;Uid=USERNAME;Pwd="

query_Statement := "SELECT Surname FROM PBPATMAS WHERE FileNo = 'A1234567'"

objReturn := ADOSQL(ConnectString, query_Statement)

MsgBox % "Result: " objReturn[1,1]
MsgBox % "Result: " objReturn[2,1]
MsgBox, Last Query: %ADOSQL_LastQuery%
MsgBox, Last Error: %ADOSQL_LastError%

You can also use objReturn.MaxIndex() and objReturn[1].MaxIndex() to determine the number of rows and columns in the object.

Best wishes.
Ruevil2
Posts: 167
Joined: 14 Jul 2014, 10:39

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

31 Jan 2018, 16:00

I found a small issue that has caused some strange behavior for me lately and maybe something to look out for in the future.

Consider these two SQL queries, note the only difference being the single quotes around the cc variable.

Code: Select all

Query1 = SELECT description FROM job_costcodes WHERE job_no = '%j%' AND cost_code_no = '%cc%'

Code: Select all

Query1 = SELECT description FROM job_costcodes WHERE job_no = '%j%' AND cost_code_no = %cc%

The first query runs fine in every case, the second query runs fine as long as all retrieved records are numerical. When it encounters an ASCII char it simply ignores this COM error and causes a never ending loop inside ADOSQL and is never recorded in the errorlog.txt file.

Commenting out this line allowed me to get the error to throw.

Code: Select all

;ComObjError( 0 ) ; We'll manage COM errors manually.
Which then throws this error:

Code: Select all

---------------------------
Timecard Audit(Tabs).ahk
---------------------------
Error in #include file "Source\ADOSQL.ahk":
     0x80004005 - Unspecified error
Source:		Microsoft OLE DB Provider for SQL Server
Description:	Conversion failed when converting the varchar value 'BUDGET    ' to data type int.
HelpFile:		(null)
HelpContext:	0

Specifically: MoveNext

	Line#
	088: oTbl.Insert( oRow := [] )  
	089: oRow.SetCapacity( cols )  
	090: v1 := v
	091: Loop,cols
	092: {
	093: oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Value  
	094: }
--->	095: oRec.MoveNext()  
	096: }
	097: oRec := oRec.NextRecordset()
	098: }
	100: if (txtout)  
	101: {
	102: Query_Statement := "x"
	103: Loop,o3DA.MaxIndex()

Continue running the script?
---------------------------
Yes   No   
---------------------------

I am unsure how to fix this error properly as it seems that once a COM error has been thrown, oRec.EOF is never set to true, causing an infinite loop.

Offending snippet:

Code: Select all

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??
	v1 := v
	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.
User avatar
kczx3
Posts: 1121
Joined: 06 Oct 2015, 21:39

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

31 Jan 2018, 16:59

Don’t know if it would fix your issue but I’d recommend using expression syntax using :=
Ruevil2
Posts: 167
Joined: 14 Jul 2014, 10:39

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

31 Jan 2018, 18:36

kczx3 wrote:Don’t know if it would fix your issue but I’d recommend using expression syntax using :=
I have had quite a few issues that have caused me to have to switch between and sometimes have to combine = and := statements. Honestly I couldn't even remember the reason at the moment and is probably unnecessary. Thank you for reminding me I need to do a lot of consolidation in this regard. Unfortunately, after diving into the source code of ADOSQL.ahk it seems that there is no error catching beyond the initial connection string error catch via oCon.Errors. So it seems that all SQL syntax issues after connect will cause an infinite loop with oRec.EOF or a hard error via the elimination of the ComObJError(0) line.
just me
Posts: 7167
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

01 Feb 2018, 10:18

Hi ruevil2,

in 2013 I needed ADO for one of my projects accessing an Oracle DB. I built a class around VxE's code and it was working for me. After reading your post, I added an error check in Query() and replaced some Insert() calls by Push(), but I cannot test here. You might want to give it a try, though.

Code: Select all

; ======================================================================================================================
; Based on ADOSQL v5.04L - By [VxE]
; -> autohotkey.com/boards/viewtopic.php?f=6&t=74
;
; 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.
;
; 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 1234 and with a static IP:
; DRIVER={SQL SERVER};SERVER=192.168.0.12,1234\SQLEXPRESS;DATABASE=mydb;UID=admin;PWD=12345;APP=AHK
;
; ErrorLevel is set to "Error" if ADODB is not available, or the COM error code if a COM error is encountered.
; The properties 'LastError' and 'LastStatement' will contain detailed informations about the last COM error
; respectively the last executed SQL statement.
;
; ======================================================================================================================
Class ADODB {
   ; ===================================================================================================================
   ; ; Create a new connection object. > www.w3schools.com/asp/ado_ref_connection.asp
   ; ===================================================================================================================
   __New() {
      This.LastError := This.LastStatement := ""
      ComObjError(0) ; We'll manage COM errors manually.
      ; If something goes wrong here, return blank and set the error message.
      If !(oCon := ComObjCreate("ADODB.Connection")) {
         ComObjError(1)
         ErrorLevel := "Error"
         This.LastError := "Fatal Error: ADODB is not available."
         Return ""
      }
      oCon.ConnectionTimeout := 15 ; Allow 15 seconds to connect to the server. Default is 15.
      oCon.CursorLocation := 3     ; Use a client-side cursor server.
      oCon.CommandTimeout := 60    ; A generous 1 minute timeout on the actual SQL statement. Default is 30.
      This.Connection := oCon
      ComObjError(1)
   }
   ; ===================================================================================================================
   ; If the connection is still open, we close it.
   ; ===================================================================================================================
   __Delete() {
      If (This.Connection)
         This.Close()
   }
   ; ===================================================================================================================
   ; Connect to a database. > www.w3schools.com/asp/met_conn_open.asp
   ; ===================================================================================================================
   Open(ConnectionString) {
      This.LastError := This.LastStatement := ""
      ComObjError(0) ; We'll manage COM errors manually.
      This.Connection.Open(ConnectionString) ; Open the connection.
      If (ComError := A_LastError) {
         This.GetError()
         ComObjError(1)
         ErrorLevel := ComError
         Return ""
      }
      ComObjError(1)
      Return True
   }
   ; ===================================================================================================================
   ; Close the connection. > www.w3schools.com/asp/met_conn_close.asp
   ; ===================================================================================================================
   Close() {
      This.LastError := This.LastStatement := ""
      ComObjError(0) ; We'll manage COM errors manually.
      This.Connection.Close()
      This.Connection := ""
      ComObjError(1)
      Return True
   }
   ; ===================================================================================================================
   ; Execute a non-query statement. > www.w3schools.com/asp/met_conn_execute.asp
   ; ===================================================================================================================
   Exec(SQLStatement) {
      This.LastError := "", This.LastStatement := SQLStatement
      ComObjError(0) ; We'll manage COM errors manually.
      Dummy := This.Connection.Execute(SQLStatement)
      If (ComError := A_LastError) {
         This.GetError()
         ComObjError(1)
         ErrorLevel := ComError
         Return ""
      }
      ComObjError(1)
      Return True
   }
   ; ===================================================================================================================
   ; Execute a query statement and get the recordset. > www.w3schools.com/asp/ado_ref_recordset.asp
   ; ===================================================================================================================
   Query(QueryStatement) {
      This.LastError := "", This.LastStatement := QueryStatement
      ComObjError(0) ; We'll manage COM errors manually.
      Recordset := This.Connection.Execute(QueryStatement)
      If (ComError := A_LastError) {
         This.GetError()
         ComObjError(1)
         ErrorLevel := ComError
         Return ""
      }
      Result := {} ; This is a 3-dimensional array.
      While IsObject(Recordset) {
         If !(Recordset.BOF && Recordset.EOF) || !(Recordset.State) { ; Recordset is empty, so we skip it.
            ; A row-returning operation returns an open Recordset
            Fields := Recordset.Fields
            Columns := Fields.Count
            Result.Push(Query := [])
            Query.Push(Row := [])
            Loop % Columns ; Put the column names in the first row.
               Row[A_Index] := Fields.Item(A_Index - 1).Name
            While !(Recordset.EOF) { ; While the record pointer is not at the end of the Recordset...
               Query.Push(Row := [])
               Row.SetCapacity(Columns) ; Might improve performance on huge tables??
               Loop % Columns
                  Row[A_Index] := Fields.Item(A_Index - 1).Value
               Recordset.MoveNext() ; move the record pointer to the next row of values
               If (ComError := A_LastError) {
                  This.GetError()
                  ComObjError(1)
                  ErrorLevel := ComError
                  Return ""
               }
            }
         }
         Recordset := Recordset.NextRecordset() ; Get the next Recordset.
      }
      ComObjError(1)
      Return Result
   }
   ; ===================================================================================================================
   ; Convert a result object as returned from Query() into a string.
   ; ===================================================================================================================
   ToString(Result, ColDelim := "|", RowDelim := "`n") {
      Str := ""
      For I1, Table In Result {
         Str .= RowDelim
         For I2, Row In Table {
            Str .= RowDelim
            For I3, Column In Row
               Str .= (I3 > 1 ? "|" : "") . Column
         }
      }
      Return SubStr(Str, 3)
   }
   ; ===================================================================================================================
   ; Get the last error.
   ; ===================================================================================================================
   GetError() {
      This.LastError := LastErrors := ""
      For Item In This.Connection.Errors
         LastErrors .= "`n`n" . Item.Description . "`n"
                    .  "Error number: " . Item.Number . "`n"
                    .  "Error source: " . Item.Source . "`n"
                    .  "Native error: " . Item.NativeError . "`n"
                    .  "SQL state: "    . Item.SQLState
      This.LastError := SubStr(LastErrors, 3)
   }
}
User avatar
Gio
Posts: 1010
Joined: 30 Sep 2013, 10:54
Location: Brazil

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

01 Feb 2018, 15:24

I am unsure how to fix this error properly as it seems that once a COM error has been thrown, oRec.EOF is never set to true, causing an infinite loop.
COM errors are being managed directly in this function. So, if you spot a new error, you just have to add it as a second conditional for the while-loop. The built-in variable A_LastError can be checked for errors.

While ((!oRec.EOF) OR (A_LastError != 0x80004005))
A_AhkUser
Posts: 1093
Joined: 06 Mar 2017, 16:18
GitHub: AAhkUser
Location: France

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

14 Apr 2019, 17:01

@VxE Thanks much for this function, great work.
Joe Glines wrote:
30 Dec 2013, 09:19
I've figured out how to open UTF-8 files :) [...]
Regards,
Joe
@Joe Glines Could you please provide a link or a sample code demonstrating a way to work with UTF-8 files when using the Microsoft Text Driver - I mean, as an example, in the sample code below, the MsgBox displays лето in lieu of cyrillic chars (similarly, I get some ?????????? when using the ADOSQL function), thanks.
Btw, I thought about maybe using ADO Stream Objects for encoding issues - if anyone has an example, please do share thanks.

kczx3 wrote:
17 Aug 2017, 14:50
Can anyone confirm for me that AHK is unable to handle ADO Events? I think that is the case when looking at the Note above the events listed here - https://www.w3schools.com/asp/ado_ref_connection.asp
It seems one can use ComObjConnect - see the example below.

@justme My DB-stuff-related knowledge is nearly equal to 0 so you code helped me a lot to understand how this works, thanks.

On a side note, the doc references a getstring method, which returns the Recordset as a string while I saw none of the scripts make use of it.

Code: Select all

#NoEnv
#Warn
SendMode Input
SetWorkingDir %A_ScriptDir%
#SingleInstance force


SetWorkingDir % A_ScriptDir
IfNotExist, dictionary.tab
FileAppend,
(LTrim Join`n
лето	summer	прекрасное лето — great summer``r``nпроводить лето — to summer
зима	winter	суровая зима — severe winter``r``nпредстоящая зима — coming winter
), dictionary.tab, UTF-8
IfNotExist, schema.ini ; https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-2017
FileAppend,
(LTrim Join`n
	[dictionary.tab]
	Format=Delimited(%A_Tab%`)
	ColNameHeader=FALSE
	col1 = C_SOURCE Text
	col2 = C_TARGET Text
	col3 = C_EXAMPLES Text
), schema.ini, UTF-16
sleep, 1000
connectionString := "Provider=MSDASQL.1;Extended Properties=""DefaultDir=" . A_ScriptDir ";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=asc,csv,tab,txt;"""
; Folder with TXT, CSV, ASC, TAB files (ODBC) > https://www.vlsoftware.net/documentation/exportizer/connection-strings-examples.htm

ComObjError(0)
if !(ADODBCnx:=ComObjCreate("ADODB.Connection")) ; https://www.w3schools.com/asp/ado_ref_connection.asp
{
	MsgBox, 64,, ADODB is not available. The program will exit.
ExitApp
}
ADODBCnx.CursorLocation := 3 ; adUseClient > https://www.w3schools.com/asp/prop_conn_cursorlocation.asp
ComObjConnect(ADODBCnx, K:=new C)
ADODBCnx.open(connectionString) ; https://www.w3schools.com/asp/met_conn_open.asp
if (A_LastError) {
	MsgBox, 16,, % A_LastError
ExitApp
}
ComObjError(1)
rs := ADODBCnx.execute("SELECT C_TARGET, C_SOURCE FROM dictionary.tab") ; https://www.w3schools.com/asp/met_conn_execute.asp (returns a new Recordset object if it is a row-returning query. A closed Recordset object will be returned if it is not a row-returning query)
if (rs.state <> 0) { ;  adStateClosed > https://www.w3schools.com/asp/prop_rs_state.asp
	ComObjConnect(rs, K:=new C)
	MsgBox % string := rs.getString(2,, A_Tab, "`n") ; adClipString := 2 (https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/stringformatenum?view=sql-server-2017)
	rs.close()
	ComObjConnect(rs), rs := ""
}
ADODBCnx.close()
ComObjConnect(ADODBCnx)
OnExit, handleExit
return

Class C {
	__Call(_callee, _p*) {
	static _str := ""
		ToolTip % _str.=_callee "`n", 0, 0
	}
}

handleExit:
	ADODBCnx := ""
	FileDelete, dictionary.tab
	FileDelete, schema.ini
ExitApp
User avatar
Joe Glines
Posts: 697
Joined: 30 Sep 2013, 20:49
Facebook: https://www.facebook.com/theAutomatorGuru/
Google: https://plus.google.com/105328929654286634910
GitHub: joetazz
Location: Dallas
Contact:

Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)

15 Apr 2019, 07:41

A_AhkUser
@Joe Glines Could you please provide a link or a sample code demonstrating a way to work with UTF-8 files when using the Microsoft Text Driver - I mean, as an example, in the sample code below, the MsgBox displays лето in lieu of cyrillic chars (similarly, I get some ?????????? when using the ADOSQL function), thanks.
Here is an example

Code: Select all

#Include B:\Progs\AutoHotkey_L\AHK Work\Database\ADOSQL\ADOSQL.ahk
objStream := ComObjCreate("ADODB.Stream")
objStream.CharSet:= "utf-8"
objStream.Open
objStream.LoadFromFile("B:\Progs\AutoHotkey_L\AHK Work\Database\ADOSQL\Country Breakout\working with schema\my url2.txt")
strData := objStream.ReadText()
oRecordset.Open(sCommandText, oConnection, adOpenStatic, adLockOptimistic)
msgbox % strData

Find & Click AutoHotkey syntax writer Automate my Task :clap:
AHK Tutorials:Web Scraping | AHK Studio | Webservice APIs | Excel | Chrome | RegEx | Functions
Training: AHK Webinars Courses on AutoHotkey :ugeek:
Connect with me on LinkedIn :beer: | YouTube
How-to: Create a shortcut that automatically
logs in to any website

:thumbup: Quick Access Popup, the powerful Windows folders, apps and documents launcher!
Help support the AutoHotkey foundation

Return to “Scripts and Functions”

Who is online

Users browsing this forum: jmasiak and 27 guests