Using AutoIT to user HTML to run DB scripts

[Hawdon1992]

Hi there,

Im very new to using auto it and even writing HTML. As a training exercise I have been tasked with creating a basic HTML file which consist of a Dropdown with 3 options.. 'MACAddress', 'Payroll' and 'Surname'. 

I have written 3 queries in SQL developer which will find each piece of information I need. 1 is ran from entering a 'MACAddress' into the query, another is ran from entering the 'Payroll' into the query and the final one is ran from entering the 'Surname' into the query.

My colleague has given me the template of a recent AutoIT to work from and his function works based on the 'Payroll' only. What I need is to amend this AutoIT file so it recognises when the dropdown option and then runs one of the three scripts based on what the dropdown option was and what the user had entered.

Any ideas or suggestions welcome.. Please bare with me as im ver new to this..

Thanks

[Annatsu]

What you are looking for is 'GUICtrlRead'. Use an if to check if the value from the combo (dropdown) is MACAddress, payroll or surname.

If you give me some more information, i can help you more

[Hawdon1992]

8 hours ago, Annatsu said:

What you are looking for is 'GUICtrlRead'. Use an if to check if the value from the combo (dropdown) is MACAddress, payroll or surname.

If you give me some more information, i can help you more

Thank You - I have a HTML file which has a dropdown with three options and a text box. I want to be able to use the dropdown so then AutoIT points to a particular function and then uses the text input to run a DB SQL query. Not really sure where to incorporate it into my AU3 script. 

 

[Melba23]

Hawdon1992,

How about posting this script so we can take a look at it - as well as the HTML with which you wish it to interact. Without that anyone trying to help is very limited in what they can suggest. When you post the code please use Code tags - see here how to do it.

M23

 

[Hawdon1992]

expandcollapse popup

#include <Misc.au3>; Only used for _Iif
#include <Array.au3>
#include <File.au3>
Global $o_adoRs, $o_adoCon ; Global recordset defined to handle DB queries
Global $ascopes
_FileReadToArray("\\???\???\???.csv", $ascopes, 0, ",")
; // OPTIONS HERE //
Local $sRootDir = @ScriptDir & "\www" ; The absolute path to the root directory of the server.
Local $sIP = @IPAddress1 ; ip address as defined by AutoIt
Local $iPort = 9999 ; the listening port
Local $sServerAddress = "http://" & $sIP & ":" & $iPort & "/"
Local $iMaxUsers = 15 ; Maximum number of users who can simultaneously get/post
Local $sServerName = "Sean's Laptop (" & @OSVersion & ") AutoIt " & @AutoItVersion
; // END OF OPTIONS //

Local $aSocket[$iMaxUsers] ; Creates an array to store all the possible users
Local $sBuffer[$iMaxUsers] ; All these users have buffers when sending/receiving, so we need a place to store those

For $x = 0 To UBound($aSocket) - 1 ; Fills the entire socket array with -1 integers, so that the server knows they are empty.
    $aSocket[$x] = -1
Next

TCPStartup() ; AutoIt needs to initialize the TCP functions

$iMainSocket = TCPListen($sIP, $iPort) ;create main listening socket
If @error Then ; if you fail creating a socket, exit the application
    MsgBox(0x20, "AutoIt Webserver", "Unable to create a socket on port " & $iPort & ".") ; notifies the user that the HTTP server will not run
    Exit ; if your server is part of a GUI that has nothing to do with the server, you'll need to remove the Exit keyword and notify the user that the HTTP server will not work.
EndIf


ConsoleWrite("Server created on " & $sServerAddress & @CRLF) ; If you're in SciTE,

While 1
    $iNewSocket = TCPAccept($iMainSocket) ; Tries to accept incoming connections

    If $iNewSocket >= 0 Then ; Verifies that there actually is an incoming connection
        For $x = 0 To UBound($aSocket) - 1 ; Attempts to store the incoming connection
            If $aSocket[$x] = -1 Then
                $aSocket[$x] = $iNewSocket ;store the new socket
                ExitLoop
            EndIf
        Next
    EndIf

    For $x = 0 To UBound($aSocket) - 1 ; A big loop to receive data from everyone connected
        If $aSocket[$x] = -1 Then ContinueLoop ; if the socket is empty, it will continue to the next iteration, doing nothing
        $sNewData = TCPRecv($aSocket[$x], 1024) ; Receives a whole lot of data if possible
        If @error Then ; Client has disconnected
            $aSocket[$x] = -1 ; Socket is freed so that a new user may join
            ContinueLoop ; Go to the next iteration of the loop, not really needed but looks oh so good
        ElseIf $sNewData Then ; data received
            $sBuffer[$x] &= $sNewData ;store it in the buffer
            If StringInStr(StringStripCR($sBuffer[$x]), @LF & @LF) Then ; if the request has ended ..
                $sFirstLine = StringLeft($sBuffer[$x], StringInStr($sBuffer[$x], @LF)) ; helps to get the type of the request
                $sRequestType = StringLeft($sFirstLine, StringInStr($sFirstLine, " ") - 1) ; gets the type of the request
                If $sRequestType = "GET" Then ; user wants to download a file or whatever ..
                    $sRequest = StringTrimRight(StringTrimLeft($sFirstLine, 4), 11) ; let's see what file he actually wants
                    If StringInStr(StringReplace($sRequest, "\", "/"), "/.") Then ; Disallow any attempts to go back a folder
;~                      _HTTP_SendError($aSocket[$x]) ; sends back an error
                    Else
                        If $sRequest = "/" Then ; user has requested the root
                            $sRequest = "/index.html" ; instead of root we'll give him the index page
                        EndIf
                        $sRequest = StringReplace($sRequest, "/", "\") ; convert HTTP slashes to windows slashes, not really required because windows accepts both
                        If FileExists($sRootDir & "\" & $sRequest) Then ; makes sure the file that the user wants exists
                            $sFileType = StringRight($sRequest, 4) ; determines the file type, so that we may choose what mine type to use
                            Switch $sFileType
                                Case "html", ".htm" ; in case of normal HTML files
                                    _HTTP_SendFile($aSocket[$x], $sRootDir & $sRequest, "text/html")
                                Case ".css" ; in case of style sheets
                                    _HTTP_SendFile($aSocket[$x], $sRootDir & $sRequest, "text/css")
                                Case ".jpg", "jpeg" ; for common images
                                    _HTTP_SendFile($aSocket[$x], $sRootDir & $sRequest, "image/jpeg")
                                Case ".png" ; another common image format
                                    _HTTP_SendFile($aSocket[$x], $sRootDir & $sRequest, "image/png")
                                Case Else ; this is for .exe, .zip, or anything else that is not supported is downloaded to the client using a application/octet-stream
                                    _HTTP_SendFile($aSocket[$x], $sRootDir & $sRequest, "application/octet-stream")
                            EndSwitch
                        Else
                            _HTTP_SendFileNotFoundError($aSocket[$x]) ; File does not exist, so we'll send back an error..
                        EndIf
                    EndIf
                ElseIf $sRequestType = "POST" Then ; user has come to us with data, we need to parse that data and based on that do something special

                    $aPOST = _HTTP_GetPost($sBuffer[$x]) ; parses the post data

                    $sComment = _HTTP_Post("wintext", $aPOST) ; Like PHPs _POST, but it requires the second parameter to be the return value from _Get_Post

                    _HTTP_ConvertString($sComment) ; Needs to convert the POST HTTP string into a normal string

                    ConsoleWrite("Just received this: " & $sComment & @CRLF)
                    if $sComment<> "" then $sComment = _Whereis($sComment)
                    $data = FileRead($sRootDir & "\template.html")
                    $data = StringReplace($data, "<?au3 Replace me ?>", $sComment)

                    $h = FileOpen($sRootDir & "\index.html", 2)
                    FileWrite($h, $data)
                    FileClose($h)

                    $h = FileOpen($sRootDir & "\clean.html", 2)
                    FileWrite($h, $sComment)
                    FileClose($h)

                    _HTTP_SendFile($aSocket[$x], $sRootDir & "\index.html", "text/html") ; Sends back the new file we just created
                EndIf

                $sBuffer[$x] = "" ; clears the buffer because we just used to buffer and did some actions based on them
                $aSocket[$x] = -1 ; the socket is automatically closed so we reset the socket so that we may accept new clients

            EndIf
        EndIf
    Next

    Sleep(10)
WEnd

Func _HTTP_ConvertString(ByRef $sInput) ; converts any characters like %20 into space 8)
    $sInput = StringReplace($sInput, '+', ' ')
    StringReplace($sInput, '%', '')
    For $t = 0 To @extended
        $Find_Char = StringLeft(StringTrimLeft($sInput, StringInStr($sInput, '%')), 2)
        $sInput = StringReplace($sInput, '%' & $Find_Char, Chr(Dec($Find_Char)))
    Next
EndFunc   ;==>_HTTP_ConvertString

Func _HTTP_SendHTML($hSocket, $sHTML, $sReply = "200 OK") ; sends HTML data on X socket
    _HTTP_SendData($hSocket, Binary($sHTML), "text/html", $sReply)
EndFunc   ;==>_HTTP_SendHTML

Func _HTTP_SendFile($hSocket, $sFileLoc, $sMimeType, $sReply = "200 OK") ; Sends a file back to the client on X socket, with X mime-type
    Local $hFile, $sImgBuffer, $sPacket, $a

    ConsoleWrite("Sending " & $sFileLoc & @CRLF)

    $hFile = FileOpen($sFileLoc, 16)
    $bFileData = FileRead($hFile)
    FileClose($hFile)

    _HTTP_SendData($hSocket, $bFileData, $sMimeType, $sReply)
EndFunc   ;==>_HTTP_SendFile

Func _HTTP_SendData($hSocket, $bData, $sMimeType, $sReply = "200 OK")
    $sPacket = Binary("HTTP/1.1 " & $sReply & @CRLF & _
            "Server: " & $sServerName & @CRLF & _
            "Connection: close" & @CRLF & _
            "Content-Lenght: " & BinaryLen($bData) & @CRLF & _
            "Content-Type: " & $sMimeType & @CRLF & _
            @CRLF)
    TCPSend($hSocket, $sPacket) ; Send start of packet

    While BinaryLen($bData) ; Send data in chunks (most code by Larry)
        $a = TCPSend($hSocket, $bData) ; TCPSend returns the number of bytes sent
        $bData = BinaryMid($bData, $a + 1, BinaryLen($bData) - $a)
    WEnd

    $sPacket = Binary(@CRLF & @CRLF) ; Finish the packet
    TCPSend($hSocket, $sPacket)

    TCPCloseSocket($hSocket)
EndFunc   ;==>_HTTP_SendData

Func _HTTP_SendFileNotFoundError($hSocket) ; Sends back a basic 404 error
    Local $s404Loc = $sRootDir & "\404.html"
    If (FileExists($s404Loc)) Then
        _HTTP_SendFile($hSocket, $s404Loc, "text/html")
    Else
        _HTTP_SendHTML($hSocket, "404 Error: " & @CRLF & @CRLF & "The file you requested could not be found.")
    EndIf
EndFunc   ;==>_HTTP_SendFileNotFoundError

Func _HTTP_GetPost($s_Buffer) ; parses incoming POST data
    Local $sTempPost, $sLen, $sPostData, $sTemp

    ; Get the lenght of the data in the POST
    $sTempPost = StringTrimLeft($s_Buffer, StringInStr($s_Buffer, "Content-Length:"))
    $sLen = StringTrimLeft($sTempPost, StringInStr($sTempPost, ": "))

    ; Create the base struck
    $sPostData = StringSplit(StringRight($s_Buffer, $sLen), "&")

    Local $sReturn[$sPostData[0] + 1][2]

    For $t = 1 To $sPostData[0]
        $sTemp = StringSplit($sPostData[$t], "=")
        If $sTemp[0] >= 2 Then
            $sReturn[$t][0] = $sTemp[1]
            $sReturn[$t][1] = $sTemp[2]
        EndIf
    Next

    Return $sReturn
EndFunc   ;==>_HTTP_GetPost

Func _HTTP_Post($sName, $sArray) ; Returns a POST variable like a associative array.
    For $i = 1 To UBound($sArray) - 1
        If $sArray[$i][0] = $sName Then
            Return $sArray[$i][1]
        EndIf
    Next
    Return ""
EndFunc   ;==>_HTTP_Post

Func _Whereis($payroll)
;~ If @ScriptDir <> "\\???\???" Then
;~  ConsoleWrite("whereis must be run from server \\???\???" & @CRLF)
;~  Exit
;~ EndIf
    $sReturnString = ("-------------------------------------------------------" & "<br>")

;~ TCPStartup()
;~ $user = "???????"
;~ $user = "???????"
;~ $user = "???????"
    $user = $payroll
    $SQL = "select * from UserAccount where Name LIKE '%" & $User & "%'"
    If @error Then
        ConsoleWrite("ERROR - Failed to connect to the database" & @CRLF)
    Else
        ConsoleWrite("Success! - Connection to database successful!" & @CRLF)
    EndIf

    Local $s_Connection = "DRIVER={SQL Server};SERVER=???????;DATABASE=?????;uid=???????;pwd=???????;"
    $o_adoCon = ObjCreate("ADODB.Connection") ; 1. Create a connection object to connect to the database
    If @error Then
        ConsoleWrite("Error creating ADODB.Connection Object")
    EndIf
    $o_adoCon.connectionString = ($s_Connection)
    $o_adoRs = ObjCreate("ADODB.Recordset") ; 2. Create a recordset object in order to receive data in
    If @error Then
        ConsoleWrite("Database error 2" & "Error creating ADODB.Recordset Object")
    EndIf
    Local $s_adoSQL = $SQL
    $o_adoCon.Open
    $o_adoRs.Open($s_adoSQL, $o_adoCon)
    $o_adoRs.CursorType = 2
    $o_adoRs.LockType = 3
    If Not $o_adoRs.EOF Then
        $arr = $o_adoRs.GetRows()
        $o_adoRs.Close
        $records_number = UBound($arr)
;~ _ArrayDisplay($arr)
        For $i = 0 To UBound($arr) - 1
;~  ConsoleWrite($arr[$i][2] & " " & $arr[$i][1] & @CRLF)
            $sQuery = "select * from Table where Object=" & $arr[$i][0]
            $o_adoRs.Open($sQuery, $o_adoCon)
            If Not $o_adoRs.EOF Then
                $arr2 = $o_adoRs.GetRows()
;~  _ArrayDisplay($arr2)
                For $x = 0 To UBound($arr2) - 1
                    ConsoleWrite("Here" & @CRLF)
                    Local $sIPAddress = TCPNameToIP($arr2[$x][2])
                    $location = _Lookup($sIPAddress)
                    $sReturnString = $sReturnString & "Name:        " & $arr[$i][2] & "<br>" & "Location:   " & $location & "<br>" & "UserID:       " & $arr[$i][1] & "<br>" & "Asset:      " & $arr2[$x][2] & "<br>" & "IP Address:    " & $sIPAddress & "<br>" & "-------------------------------------------------------" & "<br>"

                Next
            EndIf
            $o_adoRs.Close
        Next
    EndIf

    Return ($sReturnString)
EndFunc   ;==>_Whereis

Func _Lookup($ip)
    $location = "unknown"
    For $i = 1 To UBound($ascopes) - 1
        If $ip > $ascopes[$i][0] And $ip < $ascopes[$i][1] Then $location = $ascopes[$i][2]
    Next
    Return $location
EndFunc   ;==>_Lookup

Show the page 

Anyhelp would be greatly appreciated.. Bit nervous about posting code online

Edited February 16, 2017 by Melba23
Made link clearer

[Hawdon1992]

Any ideas chaps?

[JLogan3o13]

@Hawdon1992 please wait 24 hours before bumping your threads. We have volunteers from all over the world answering questions on this forum; the person best suited to answer your question may not be online at the moment.

[Subz]

Difficult to really say how to do this as its unique to your system, all I can say is that the area below is what you need to be focused on.  You need to  you need to add a function to read the drop down box and then based upon the selection use a variance of the _Whereis($sComment) to point to a different database.

ElseIf $sRequestType = "POST" Then ; user has come to us with data, we need to parse that data and based on that do something special

                    $aPOST = _HTTP_GetPost($sBuffer[$x]) ; parses the post data

                    $sComment = _HTTP_Post("wintext", $aPOST) ; Like PHPs _POST, but it requires the second parameter to be the return value from _Get_Post

                    _HTTP_ConvertString($sComment) ; Needs to convert the POST HTTP string into a normal string

                    ConsoleWrite("Just received this: " & $sComment & @CRLF)
                    if $sComment<> "" then $sComment = _Whereis($sComment)
                    $data = FileRead($sRootDir & "\template.html")
                    $data = StringReplace($data, "<?au3 Replace me ?>", $sComment)

                    $h = FileOpen($sRootDir & "\index.html", 2)
                    FileWrite($h, $data)
                    FileClose($h)

                    $h = FileOpen($sRootDir & "\clean.html", 2)
                    FileWrite($h, $sComment)
                    FileClose($h)

                    _HTTP_SendFile($aSocket[$x], $sRootDir & "\index.html", "text/html") ; Sends back the new file we just created
                EndIf

                $sBuffer[$x] = "" ; clears the buffer because we just used to buffer and did some actions based on them
                $aSocket[$x] = -1 ; the socket is automatically closed so we reset the socket so that we may accept new clients

            EndIf

 

[Hawdon1992]

5 minutes ago, Subz said:

Difficult to really say how to do this as its unique to your system, all I can say is that the area below is what you need to be focused on.  You need to  you need to add a function to read the drop down box and then based upon the selection use a variance of the _Whereis($sComment) to point to a different database.

ElseIf $sRequestType = "POST" Then ; user has come to us with data, we need to parse that data and based on that do something special

                    $aPOST = _HTTP_GetPost($sBuffer[$x]) ; parses the post data

                    $sComment = _HTTP_Post("wintext", $aPOST) ; Like PHPs _POST, but it requires the second parameter to be the return value from _Get_Post

                    _HTTP_ConvertString($sComment) ; Needs to convert the POST HTTP string into a normal string

                    ConsoleWrite("Just received this: " & $sComment & @CRLF)
                    if $sComment<> "" then $sComment = _Whereis($sComment)
                    $data = FileRead($sRootDir & "\template.html")
                    $data = StringReplace($data, "<?au3 Replace me ?>", $sComment)

                    $h = FileOpen($sRootDir & "\index.html", 2)
                    FileWrite($h, $data)
                    FileClose($h)

                    $h = FileOpen($sRootDir & "\clean.html", 2)
                    FileWrite($h, $sComment)
                    FileClose($h)

                    _HTTP_SendFile($aSocket[$x], $sRootDir & "\index.html", "text/html") ; Sends back the new file we just created
                EndIf

                $sBuffer[$x] = "" ; clears the buffer because we just used to buffer and did some actions based on them
                $aSocket[$x] = -1 ; the socket is automatically closed so we reset the socket so that we may accept new clients

            EndIf

 

That's what I was thinking.. I understand that's the part of it that handles the textbox input..Ill have a look - Thank Yo uand apologies for bumping it up to soon

[Melba23]

Hawdon1992,

Please do not think we are always moaning at you, but when you reply, please use the "Reply to this topic" button at the top of the thread or the "Reply to this topic" editor at the bottom rather than the "Quote" button - responders know what they wrote and it just pads the thread unnecessarily.

M23

 

Edited February 16, 2017 by Melba23
Typo

[Hawdon1992]

Melba I almost done it again - Sorry, its a bad habit. Its just constructive feedback  I thought it took a lot more space than needed. :/

Apologies