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.