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

[A_AhkUser]

Thanks Joe. Btw, I found this brief guide: it summarizes how to Read & Write UTF-8 Files while using ADO (it's in VBScript but can be easily replicable in AHK). However, the main reason why I'm intending to use ADO is to retrieve query results/recordset as strings.
I also try to add the following @ schema.ini: CharacterSet = 65000 / CharacterSet = 65001 - to no avail.

[A_AhkUser]

Ok, I looked back at it again.
At first, I thought

Code: Select all

oRecordset.Open(sCommandText, oConnection, adOpenStatic, adLockOptimistic)

might be a typo since, at first sight, it has no connection whatsoever with the rest of the code... but I actually managed to make it work... Note that I was able to do it by specifying Microsoft.Jet.OLEDB.4.0 as provider.
Here's a sample code, for the record, it might be helpful:

Code: Select all

#SingleInstance, Force
#NoEnv
#Warn


SetWorkingDir % A_ScriptDir
; ================= sample.txt
delimiter := "|"
filecontent := "C_INDEX" delimiter "C_TRANSLIT`n" ; ColNameHeader=FALSE @ schema.ini
Loop, 10
	filecontent .= a_index delimiter "zima" a_index "`n"
FileAppend % filecontent, sample.txt, UTF-8
; ================= sample.tab
delimiter := A_Tab
filecontent := "`n" ; ColNameHeader=TRUE @ schema.ini
Loop, 10
	filecontent .= a_index delimiter "зима" a_index delimiter "winter" delimiter "суровая зима — severe winter``r``nпредстоящая зима — coming winter`n"
FileAppend % filecontent, sample.tab, UTF-8
; ================= schema.ini
FileAppend,
(LTrim Join`n
	[sample.txt]
	Format=Delimited(|)
	ColNameHeader=TRUE
	CharacterSet=65001
	[sample.tab]
	Format=TabDelimited
	ColNameHeader=FALSE
	col1 = C_INDEX Integer
	col2 = C_SOURCE Char
	col3 = C_TARGET Char
	col4 = C_EXAMPLES Char
	CharacterSet=65001
), schema.ini, UTF-16
cnx := ComObjCreate("ADODB.Connection")
cnx.provider := "Microsoft.Jet.OLEDB.4.0"
; note: you can find vbs values @ "C:\Program Files (x86)\Common Files\System\ado\adovbs.inc"
adOpenStatic := 3
adLockOptimistic := 3
OnExit, handleExit
return

handleExit:
	cnx := "", rs := ""
	FileDelete, sample.txt
	FileDelete, sample.tab
	FileDelete, schema.ini
ExitApp

!i::
	cnx.Open("Data Source=" . A_ScriptDir . "`;Extended Properties=""text;""")
		rs:= ComObjCreate("ADODB.Recordset")
		SQLStatement =
		(LTrim Join`n
			SELECT C_TARGET, C_SOURCE, C_TRANSLIT
			FROM [sample.txt] alias1
			INNER JOIN [sample.tab] alias2
			ON alias1.C_INDEX = alias2.C_INDEX
		)
		rs.Open(SQLStatement, cnx, adOpenStatic, adLockOptimistic)
		MsgBox % rs.getString()
		rs.Close()
	cnx.close()
return

Thanks again for your decisive upstream investigations and work Joe .

Cheers