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.
[Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)
Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)
Ok, I looked back at it again.
At first, I thought
Code: Select all
oRecordset.Open(sCommandText, oConnection, adOpenStatic, adLockOptimistic)
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
Cheers
Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)
Hello All,
I'm entirely new to AHK, can some one provide the sample file to connect to SQL server database
I'm entirely new to AHK, can some one provide the sample file to connect to SQL server database
Re: [Func] ADOSQL - wraps ADO for executing SQL queries (AHK-L)
Hello @skrishna , here is a sample of how i use this function to connect to a SQL Server 2008 R2 database:
Code: Select all
; STEP 1: Config Connection String:
DB_UserName := "sqlserver_username" ; Change this accordingly. Use the same username you use to connect to SQl Server management studio.
DB_Password := "sqlserver_password" ; Change this accordingly. Use the same password you use to connect to SQl Server management studio.
Data_Source := "192.168.0.19" ; Change this accordingly. The intranet IP of the server should do depending on how the server is configured in the network.
DB_Initial_Catalog := "DB_1" ; Change this accordingly. The catalog must be one of the individual catalogs displayed in SQL Server Management Studio (in example, a certain system called "DATASYS" may have its tables in a catalog called "DATASYS_DB"). You can see the available catalog names using Management Studio or some other tool (alternatively skip this config, remove it from the connection_string and then name all tables as CATALOG.DBO.Table_Name in your queries).
Connection_String := "Provider=SQLOLEDB.1;Password=" . DB_Password . ";Persist Security Info=True;User ID=" . DB_UserName . ";Initial Catalog=" . DB_Initial_Catalog . ";Data Source=" . Data_Source . ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=SOMEONES_PC;Use Encryption for Data=False;Tag with column collation when possible=False"
; STEP 2: Your actual query. Adjust Accordingly. Bellow is a simple sample of an SQL statement that retrieves server datetime and asks the server to perform a simple mathematical operation (2+2). If the connection worked, the server should return these values as a 2D table-like object accessible using the syntax TABLE[Row, Column]. Note that the actual results start on row 2 since the first row is a placeholder for the column headers.
Query =
(
Select
GetDate(),
2+2
)
RESULT := ADOSQL(Connection_String, Query) ; If the function succeeds, variable RESULT will become a 2D table-like object whose layout is TABLE[Row, Column]. Do keep in mind that the first Row is reserved for column headers though (therefore actual values start at 2,1).
msgbox % RESULT[2,1] . "`n" . RESULT[2,2] ; If the function worked, this should display a datetime string and the mathematical value "4" below it.
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 )
"What is suitable automation? Whatever saves your day for the greater matters."
Barcoder - Create QR Codes and other Barcodes using only Autohotkey !!
Archmage Gray - A fantasy shooter game fully coded in AutoHotkey
Barcoder - Create QR Codes and other Barcodes using only Autohotkey !!
Archmage Gray - A fantasy shooter game fully coded in AutoHotkey
Return to “Scripts and Functions (v1)”
Who is online
Users browsing this forum: No registered users and 188 guests