[ Autoit / MSSQL ] 데이터베이스를 사용하고자 할경우

; ------------------------------------------------------------------------------
;
; AutoIt Version: 3.1.1++
; Language:       English
; Description:    Functions for ODBC/SQL databases.
;
; ------------------------------------------------------------------------------

; Retrieve registerered DSN resources, default type USER and SYSTEM
Func _SQLGetDSN($s_DSNType="")
    Local Const $s_ODBCregUser   = "HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    Local Const $s_ODBCregSystem = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    Local $s_DSNList
    
    $s_DSNList=""
    if $s_DSNType <> "SYSTEM" then  $s_DSNList = _SQLGetDSNregister($s_ODBCregUser)
    if $s_DSNType <> "USER"   then  $s_DSNList = _SQLGetDSNregister($s_ODBCregSystem)
    return $s_DSNList
EndFunc

; Retrieve register entries, used by _SQLGetDSN
Func _SQLGetDSNregister($s_RegEntry)
    Local $s_Entries[10000], $s_Nm, $i
    $i = 1
    While 1
        $var = RegEnumVal($s_RegEntry, $i)
        If @error <> 0 then ExitLoop
        $s_Entries[$i] =  $var
        $i = $i + 1
    WEnd
    $s_Entries[0] = $i
    ReDim $s_Entries[$s_Entries[0]]
    return $s_Entries
EndFunc

; Open database connection, on empty DSN a ADO will ask for specification
Func _SQLOpen($s_DSN="")
    Local $o_ADOcn
; Create ADO connection
    $o_ADOcn = ObjCreate ("ADODB.Connection")
; Open ADO connection, only prompting for missing params adPromptComplete=2
;$o_ADOcn.Properties ("Prompt") = 4
    $o_ADOcn.ConnectionTimeout = 100
    $o_ADOcn.Open ($s_DSN)
    if @error then return 0
    return $o_ADOcn
EndFunc

; Close database connection
Func _SQLClose($o_ADOcn)
    $o_ADOcn.Close
EndFunc

; Send a schema request, optional pass a filter
Func _SQLschema($o_ADOcn, $i_SchemaID, $as_Filter=0)  ; 1="", $s_filter2="", $s_filter3="", $s_filter4="", $s_filter5="" )
    Local $o_ADOrs
    if not isObj($o_ADOcn) Then
        SetError(1)
    else
        if IsArray($as_Filter) Then
            $o_ADOrs = $o_ADOcn.OpenSchema ($i_SchemaID, $as_Filter)
        else
            $o_ADOrs = $o_ADOcn.OpenSchema ($i_SchemaID)
        EndIf
    EndIf
    if @error Then return 0
    return $o_ADOrs
EndFunc

; Send a query, optional pass a max number of records to retrieve
Func _SQLQuery($o_ADOcn, $s_Query, $i_MaxRecords=0 )
    ;ClipPut($s_Query)
    Local $o_ADOrs
    if not isObj($o_ADOcn) Then
        SetError(1)
    else
        $o_ADOrs = ObjCreate ("ADODB.Recordset")
        $o_ADOrs.CursorType = 0                    ; adOpenForwardOnly = 0
        $o_ADOrs.LockType   = 3                    ; adLockOptimistic  = 3
        $o_ADOrs.MaxRecords = $i_MaxRecords        ; maximum records returned by query
        $o_ADOrs.Open($s_Query, $o_ADOcn)
    Endif
    if @error Then return 0
    return $o_ADOrs
EndFunc

; --- retrieve fieldnames from the given qryid
Func _SQLGetFields($o_ADOrs, $s_Seperator="|")
    Local $i, $s_Fields
    $s_Fields=""                               ; Get information about Fields collection
    With $o_ADOrs
        For $i = 0 To .Fields.Count - 1
            $s_Fields &= .Fields($i).Name & $s_Seperator
        Next
    EndWith
    return $s_Fields
EndFunc

; --- retrieve fieldvalues and move to next row of given qryid
Func _SQLGetRow($o_ADOrs, $s_Seperator="|")
    Local $s_RowValues
    $s_RowValues = ""
    With $o_ADOrs
        If Not .EOF Then
            For $i = 0 To .Fields.Count - 1
                $s_RowValues &= .Fields($i).Value & $s_Seperator
            Next
            .MoveNext
        Else
            setError(1)
        EndIf
    EndWith
    return $s_RowValues
EndFunc

; --- retrieve Array fieldvalues and move to next row of given qryid
Func _SQLGetRowArray($o_ADOrs, $s_Seperator="|")
    Local $s_RowValues
    $s_RowValues = ""
    With $o_ADOrs
        If Not .EOF Then
            For $i = 0 To .Fields.Count - 1
                $s_RowValues &= .Fields($i).Value & $s_Seperator
            Next
            .MoveNext
        Else
            setError(1)
        EndIf
    EndWith
    $s_RowValues = StringSplit($s_RowValues,$s_Seperator)
    return $s_RowValues
EndFunc

Func _SQLCommand($o_ADOcn, $s_Query, $cmd_TimeOut=900)
    Local $o_ADOCmd, $o_ADOrs
    $o_ADOCmd = ObjCreate("ADODB.Command")
    $o_ADOCmd.ActiveConnection = $o_ADOcn
    $o_ADOCmd.CommandText = $s_Query
    $o_ADOCmd.CommandTimeOut = $cmd_TimeOut
    $o_ADOrs = $o_ADOCmd.Execute

    if @error Then return 0
    return $o_ADOrs
EndFunc