AHK DBA - ADO update query rows affected? Topic is solved

[kinnex]

https://github.com/IsNull/ahkDBA

Unfortunately, there are no update SQL query examples in the example file, and I didn't see any in the archived thread:
https://autohotkey.com/board/topic/7117 ... mysql-ado/

A basic example:

Code: Select all

connectionString := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . A_ScriptDir . "\test.mdb"
db := DBA.DataBaseFactory.OpenDataBase("ADO", connectionString) ; ADO
sql := "UPDATE mytable SET [MyField]='MyValue' WHERE [MyKey]=1;"
rVal := db.Query(sql)

The row gets updated correctly, but the rVal is always 0.

I'm not sure if there is something else I need to do or check for that to work. Or if it even possible to get the rowsAffected count.

[tank]

https://support.microsoft.com/en-us/kb/195048

[Blackholyman]

untested partial convert from @tank s link example

Code: Select all

adModeReadWrite = 3
adCmdText = 1

oConnection := ComObjCreate("ADODB.Connection")
oCommand := ComObjCreate("ADODB.Command")

lcConnString := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . A_ScriptDir . "\test.mdb"

lcUID := "YourUserID"
lcPWD := "YourPassword"

oConnection.ATTRIBUTES := adModeReadWrite
oConnection.OPEN(lcConnString, lcUID, lcPWD )

;* Use the command object to perform an UPDATE
;* and return the count of affected records.
strSQL := "UPDATE mytable SET [MyField]='MyValue' WHERE [MyKey]=1;"
oCommand.CommandType := adCmdText
oCommand.ActiveConnection := oConnection
oCommand.CommandText := strSQL
oCommand.Execute(liRecordsAffected)

msgbox % "Records affected: " . liRecordsAffected

[Gio]

Hello Kinnex.

if it even possible to get the rowsAffected count.

I managed to do just that a while ago, but i use a different library (ADOSQL by VxE).

Since this is also ADO, i don't see a reason for not being possible to update this one too, but some settings will probably have to be adjusted accordingly.

This is the link of the post where i explain solution for ADOSQL():
https://autohotkey.com/boards/viewtopic ... f1b#p67482

Best wishes.

[kinnex]

@ Blackholyman
I really appreciate the time spent to help me. I tested the code and get the same results as AHK DBA. The record gets changed, but liRecordsAffected is an empty string. If I initialize liRecordsAffected to 0 at the start, the msgbox displays 0 as the value has not been changed at all.

Note: the link from Gio's response explains the above behavior.

@ Gio
Thank you, I'll have to see if I can get this working with AHK DBA, or perhaps switch to ADOSQL.

[tmplinshi]

Tested and worked:

Code: Select all

FileName := A_ScriptDir "\test.mdb"

conn := ComObjCreate("ADODB.Connection")
conn.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" FileName)
sql := "UPDATE mytable SET MyField = 'MyValue' WHERE MyKey = '1'"

RecordsAffected := ComVar_Long()
conn.Execute(Sql, RecordsAffected)

MsgBox, % RecordsAffected[]
Return

ComVar_Long() {
	VarSetCapacity(var, 4, 0)
	return ComObject(0x4003, &var) ; 0x4003 = VT_BYREF|VT_I4
}

Blackholyman's method also works, but needs to use ComVar_Long() too.

Spoiler

Code: Select all

adModeReadWrite = 3
adCmdText = 1
 
oConnection := ComObjCreate("ADODB.Connection")
oCommand := ComObjCreate("ADODB.Command")
 
lcConnString := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . A_ScriptDir . "\test.mdb"
 
lcUID := "" ; YourUserID
lcPWD := "" ; YourPassword
 
oConnection.ATTRIBUTES := adModeReadWrite
oConnection.OPEN(lcConnString, lcUID, lcPWD )

liRecordsAffected := ComVar_LONG()

;* Use the command object to perform an UPDATE
;* and return the count of affected records.
strSQL := "UPDATE mytable SET [MyField]='MyValue' WHERE [MyKey]='1';"
oCommand.CommandType := adCmdText
oCommand.ActiveConnection := oConnection
oCommand.CommandText := strSQL
oCommand.Execute(liRecordsAffected)
 
msgbox % "Records affected: " . liRecordsAffected[]

ComVar_Long() {
	VarSetCapacity(var, 4, 0)
	return ComObject(0x4003, &var) ; 0x4003 = VT_BYREF|VT_I4
}

RecordsAffected
Optional. A Long variable to which the provider returns the number of records that the operation affected.

LONG * plVal; // VT_BYREF|VT_I4

---

sql := "UPDATE mytable SET [MyField]='MyValue' WHERE [MyKey]=1;"

This will causing error on my computer, I have to use '1'.

[kinnex]

Interesting, I was able to get Blackholyman's method to work fine as well using Gio's method with the ComVar functions.

I was also able to edit AHK DBA as well

By Creating: "ComVar.ahk"

Code: Select all

/*
	Functions to handle COM objects ByRef
	source: https://autohotkey.com/docs/commands/ComObjActive.htm#ByRefEx
*/
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._))
}

Modifying "DBA.ahk" to include the following lines:

Code: Select all

; functions to properly return values from COM objects
#Include <ComVar>

and finally, editing "DatabaseADO.ahk" Query function to:

Code: Select all

	/*
		Querys the DB and returns a ResultTable or true/false
	*/
	Query(sql){
		ret := false
		if(this.IsValid())
		{
			;Execute( commandtext,ra,options)
			;affectedRows := 0
			
			/*
				Note:  	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.
						
					https://autohotkey.com/docs/Functions.htm#ByRef
					https://autohotkey.com/docs/commands/ComObjActive.htm#ByRefEx
					https://autohotkey.com/boards/viewtopic.php?p=67482#p67482
			*/
			affectedRows := ComVar()													; create wrapper object
			affectedRows[] := "Warning: Could not retrieve number of affected rows!"	; default, overwritten if call returns values.
			
			rs := this._connection.Execute(sql, affectedRows.ref)
			if(IsObject(rs) && rs.State != gDBA_ADO.ObjectStateEnum.adStateClosed)
			{
				ret := this.FetchADORecordSet(rs)
				rs.Close()
			}else{
				;ret := affectedRows
				ret := affectedRows[]	; [] used to dereference value.
			}
		}
		
		return ret
	}	

That seems to work perfectly now.