Sign in to follow this  
Followers 0
poila

Need help connecting to Amazon Web Services (AWS) using AutoIT MySQL UDF

3 posts in this topic

#1 ·  Posted (edited)

Hi,

I recently got a request to transfer databases from a Microsoft Server to an online Amazon Web Services (AWS) server.

I was able to export all the data from MySQL Workbench and use phpMyAdmin to import the data into that AWS instance.

The issue is that my AutoIT program uses a MySQL UDF (see: '?do=embed' frameborder='0' data-embedContent>>) and it uses ODBC connectors.

When I changed the string containing the path of the server from "localhost" to the provided IP address, it could not detect any connection, even though I could access that IP address from my Chrome browser and access phpMyAdmin.

I am also wondering and searching for how do I really use PuTTY.exe and PSftp.exe as well as the private key to connect via SSH in the background.

How do I accomplish this with AutoIT? Is this approach recommended, or is there a more elegant and secure way of connecting to AWS EC2 instance with AutoIT?

Additionally, is it necessary to tweak some settings in AWS (eg. my.conf) to enable incoming connections from specified IP addresses?

Thanks!

Edited by poila

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

(Bumping thread...)

Attempted to solve the problem of trying to connect to MySQL database in AWS via SSH, but was unsuccessful.

Read this link prior ('?do=embed' frameborder='0' data-embedContent>>) but still confused.

I've already set remote port forwarding to 3306 in /etc/my.cnf, still confused; what else is required to enable SSH connection, if used in conjunction with the MySQL UDF?

port=3306
bind-address=0.0.0.0

I could connect to the AWS instance using PSftp.exe (get from a Windows Command Prompt and open the remote working directory

(I didn't write it into AutoIT syntax though... not sure if this is needed to ensure a connection to a remote MySQL database via SSH):

psftp.exe userNameAtAWS@XX.XX.XX.XX -i privateKeyFile.ppk

AutoIT Code sample:

#include "MySQL.au3"
; This UDF is by cdkid

Global Const $serverUser = "serverUser"
Global Const $serverPass = "serverPassword"

Global $const_fileServerName = "server.ini"

; server.ini contains the text, "XX.XX.XX.XX", which is the IP address accessible by browser but not by AutoIT

Func connectToMySQL() ;     Establishes connection with mySQL Database
    Local $mySQLServerName = FileRead($const_fileServerName)
    $global_normalSQLInstance = _MySQLConnect($serverUser, $serverPass, "databaseName", $mySQLServerName)
    $global_SQLInstance = $global_normalSQLInstance
    If $global_normalSQLInstance = 0 Or $global_tutorialSQLInstance = 0 Then
        Return 0
    Else
        Return 1
    EndIf
EndFunc

Small update: Using PuTTY again, I used the command sudo iptables -L -n and found out that all incoming connections are allowed (these are the firewall rules).

So I realised that it might have to do with the security group's rules in that AWS instance. I don't have direct access to it, so I might update this post again once I got hold of the engineer with access rights to it.

Edited by poila
1 person likes this

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

(Bumping thread... again... and if it annoys you... FORGIVE ME!)

Seriously though...

Turns out that this is not so much of an AutoIT issue but more of an AWS Security Groups issue.

Simple solution is open port 3306 via AWS Console, add MySQL to list of programs with permission to allow access to port 3306, restart MySQL like this:

sudo service mysqld restart

And re-ping to the specific IP address for that AutoIT program (eg. XX.XX.XX.X).

For another sanity check, use telnet to check if the MySQL connection can be done remotely.

(Do this via Command Prompt in Windows)

telnet XX.XX.XX.X 3306 

Problem solved. Note that however, because that AutoIT program is accessing the MySQL server from AWS, the performance will be significantly hit because of longer-distance connections.

Additionally, table names in the MySQL database are CaSe-SenSiTive. This means a major refactoring of the AutoIT script if it gets unsanitary (using myTable and MyTable interchangeably... yay).

Edited by poila

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • Fhelipe
      By Fhelipe
      Nothing More
    • rynow
      By rynow
      romaSQL
      This autoIt UDF is built on the concept of Laravel Query & doctrine.
      RomaSQL provides a new, comfortable and easy to use way for SQL-queries in autoIt.
      Most of the common SQL-queries are supported already and more are coming soon.
      All of your support is much appreciated.
      Connections
      For the connection the object ADODB is used. Therefore the connection string is based on ODBC.
      You can also use OLEDB connection strings or other database connections.
      In order for this to work your add-ons have to be installed in the function: __4ern_SQL_Connection.
      I’d be very glad if you shared your modifications with me.
      Currently supported connections
      -       MySQL (odbc)
      -       Microsoft SQL Server (odbc)
      -       SQLite (odbc)
      -       Microsoft Access (odbc)
      Command reference
      $SQL_connect; establishing connection $SQL_returnType; return a Array or Dictionary ('oDict') Object (Default = Array) $SQL_setDefaultTable; Default Tablename $SQL_setDefaultKey; Default Colmn Key (Default = id) $SQL_debug; if True, show SQL Statment in Console $SQL_get $SQL_update $SQL_delete $SQL_insertInto $SQL_take $SQL_limit $SQL_table $SQL_select $SQL_distinct $SQL_where $SQL_orWhere $SQL_whereBetween $SQL_whereNotBetween $SQL_whereIn $SQL_whereNotIn $SQL_whereNull $SQL_whereNotNull $SQL_having $SQL_orHaving $SQL_havingBetween $SQL_havingNotBetween $SQL_havingIn $SQL_havingNotIn $SQL_havingNull $SQL_havingNotNull $SQL_groupBy $SQL_orderBy  
      Examples
      establishing connection
      ;-----/ ; SQLite Connection ;-----/ $SQL_setDatabase('sqlite') $SQL_connect('C:\project.db') ;-----/ ; Access Connection ; Database, User, Password ;-----/ $SQL_setDatabase('access') $SQL_connect('C:\project.mdb') ;or as Admin $SQL_connect('C:\project.mdb', '4ern', 'root') ;-----/ ; SQLServer Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('sqlserver') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'SQL Server') ;-----/ ; MySQL Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('mysql') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'MySQL ODBC 5.2 UNICODE Driver')  
      simple SQL query
      $SQL_table('albums') $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      Select
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song') ;or pass to an Array Local $aSelect = ['id', 'Name', 'Artist', 'Song'] $SQL_select($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif where
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song', 'Votes') $SQL_where('Artist', 'adele') $SQL_where('Votes', '>=' ,'9') $SQL_orWhere('Artist', '=' ,'Rag'n'Bone Man') ;or pass to an 2dArray Local $aSelect = [['Artist','adele'],['Votes', '>=' ,'9']] $SQL_where($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      If you need more examples, then tell me exactly what you need.
      I hope you like my UDF and find some use for it.
      ---
      ->DONWLOAD romaSQL
       
       
    • macran
      By macran
      I use mysql.au3 to connect mysql database .  If restart the server ,the client how to reconnect mysql?
      The function prototype is
      _MySQL_Options($MySQL_ptr, $option, $arg = 0, $type = "ptr")
      so,
      _MySQL_Options($MysqlConn,$MYSQL_OPT_RECONNECT, ?, ?)
      pls help me,thanks!
       
       
       
    • sivaramanm
      By sivaramanm
      From AutoIT script (Pretty much same syntax as VBA), Tried connecting to MySQL Server. While i am able to insert a new row successfully, unable to verify the rowcount (# of inserted row - to verify success or failure).
      Have tried two different methods -
      to use the RecordsAffected variable from Connection Execute function to use the RecordSet and retrieve the rowcount But have been missing something and none of these methods return the actual row count.
      Any help would be appreciated.!!!
      Cross-posted in http://stackoverflow.com/questions/27411599/unable-to-retrieve-inserted-row-count-in-mysql-using-ado-from-autoit
      MySQLConnect() $EVENT_TIME= "2014-12-12 12:12:12" $LSMCName='LSMC1' $NEType='MME0001' $OMTarFile='A_MME0011-60MIN-20141212-12-v.tar' $CSVFile='12-00-S1AP.csv' $KPIType='S1AP_HO' $UpdateStatus='NotUpdated' $ReTries='0' If Not (InsertFileUpdateLog($EVENT_TIME,$LSMCName,$NEType,$OMTarFile,$CSVFile,$KPIType,$UpdateStatus,$ReTries)=1) Then WriteLog("[Error] Record insertion failed for " & $EVENT_TIME & '" ' & $LSMCName & ' ' & $NEType & ' ' & $OMTarFile & ' ' & $CSVFile & ' ' & $KPIType & ' ' & ' NotUpdated 0') EndIf MysqlDisconnect() ;~ ####################### Sub Function Definitions Func MySQLConnect() Local $sDriver="MySQL ODBC 5.3 ANSI Driver" Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $sDriver) If @error or $val = "" Then SetError(2) Return -1 EndIf $constrim="DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=localhost;DATABASE=pmdemo;uid=rootuser;pwd=rootpass;" $oDBConnect = ObjCreate ("ADODB.Connection") ; <== Create SQL connection $oDBConnect.Open ($constrim) ; <== Connect with required credentials if @error Then WriteLog("[Error] Failed to connect to the database") SetError(2) Return -2 Else ;MsgBox(0, "Success!", "Connection to database successful!") Return 1 EndIf EndFunc Func MySQLDisConnect() $oDBConnect.Close ; ==> Close the database EndFunc Func InsertFileUpdateLog($EVENT_TIME,$LSMCName,$NEType,$OMTarFile,$CSVFile,$KPIType,$UpdateStatus,$ReTries) Local $RowCount = 0 Local $result = ObjCreate("ADODB.Recordset") $sQuery = "INSERT INTO 4gc_fileupdatelog (id,EVENT_TIME,LSMC,NEType,TarFile,CSVFile,KPIFile,UpdateStatus,ReTries) VALUES ('0'," & _ "'" & $EVENT_TIME & "'," & _ "'" & $LSMCName & "'," & _ "'" & $NEType & "'," & _ "'" & $OMTarFile & "'," & _ "'" & $CSVFile & "'," & _ "'" & $KPIType & "'," & _ "'" & $UpdateStatus & "'," & _ "'" & $ReTries & "'" & _ ") ON DUPLICATE KEY UPDATE ReTries=ReTries+1,UpdateStatus='" & $UpdateStatus & "';" $result = $oDBConnect.Execute($sQuery,$RowCount) If @error Then MsgBox(1,1,"Error executing query...") Return -2 EndIf ;# Method-1 : To use records affected from Execute function If $RowCount >= 1 Then MsgBox(1,1,"Success") Else MsgBox(1,1,"Failed, rowcount is:" & $RowCount ) EndIf If Not ($result.bof AND $result.eof) Then WriteLog("[Error] No Rows found") Return 0 EndIf ;# Method-2 : To use recordsset object and retrieve the rows/columns count If IsObj($result) And $result.EOF=False Then $myarray=$result.GetRows() $rows = UBound($myarray,1) $cols = UBound($myarray,2) MsgBox(1,1," rows: " & $rows & " cols: " & $cols) If ($rows = 1) Then WriteLog("[Info] Record inserted successfully") Return 1 ElseIf ($rows = 2) Then WriteLog("[Alert] Record updated successfully. affected row(s) is " & $rows) Return $rows Else > Blockquote WriteLog("[Error] Record insertion failed. affected row(s) is " & $rows) Return 0 EndIf EndIf EndFunc
    • tarretarretarre
      By tarretarretarre
      Now its my turn to give back to the community ( Better late than never :P)..
       
      First i want to thank progAndy for his amazing UDF which this idea came from

       
      And the AutoitObject Team (For making autoit fun again)
       

      I dont have so much to say more thant to let the project speak for itself, ive had this for a couple of months but it was "integrated" into my own "framework" but today I decided to release it because i have seen some people on the forum search for something like this.
      What libraries does this use and are they included?
      Connector/C 6.1.6 ( https://dev.mysql.com/downloads/connector/c/ ) And yes, they are included in the download so nothing has to be installed or anything

      What are the features:
      Prepared statements 32 and 64 bit environment Multiline prepared statements  Simplicity User-friendly PDO-like Syntax & Methods (http://php.net/pdo)  
      So whats the difference between this and x's Mysql UDF?
      When you are fetching your data from your database, you use your table-names to display them, like this:
      with $MySql Local $Vars = ["?", $lastname, "?", $age] .prepare("SELECT id, surname, lastname FROM users WHERE lastname = ? AND AGE > ?") .execute($Vars) Consolewrite(stringformat("Searchresult: %d Hits", .rowCount()) for $row in $oRows consolewrite("Surname: " & $row.surname & @crlf) consolewrite("Lastname: " & $row.lastname & @crlf) next endwith  
      Function-list (Yeah i might improve this when i have time)
      ; Every parameter is a default value of method _miniSQL_setDllDir(@ScriptDir); returns nothing Local Const $MySql = _miniSQL_LoadLibrary(); Returns object with methods ; Starts the library, connects to the database and returns the object $MySql.Startup($sHost, $sUser, $sPass = "", $sDatabase = "", $iPort = 0, $sUnix_socket = "", $iClient_Flag = 0); Returns TRUE if connection was succeded otherwise FALSE ; Shuts down the library and prevents any methods to be executed $MySql.Shutdown() ; The desired SQL query goes here (SELECT x FROM table) $MySql.prepare($sQuery); Returns nothing ; Used for multi-line prepared statements (See Example3 - newline prepared statements) $MySql.PrepareGlue($sQuery); Returns nothing ; Cleans any previous prepared statement of any kind $MySql.PrepareClean(); Returns nothing ; Executes a prepared statement of any kind, with or without passed arguments $MySql.execute($aVars = Null, $iExecuteStyle = $_miniSQL_ExecuteStoreResult); returns TRUE if success, otherwise FALSE ; Options for $iExecuteStyle: $_miniSQL_ExecuteStoreResult = 0 and $_miniSQL_ExecuteOnly = 1 ; Fetches previous executed prepared statement (If anything was stored "see Options for iExecuteStyle") $MySql.fetchAll($iFetchStyle = $_miniSQL_FetchObject); Returns (Depends on $iFetchStyle) ; Options for $iFetchStyle: $_miniSQL_FetchObject = 0 (Default), $_miniSQL_FetchSingleObject = 1, $_miniSQL_FetchArray = 2, $_miniSQL_FetchSingleValue = 3 ; Gives you the "lastinsertId" (The last id that was affected) $MySql.lastInsertId(); Returns the last affected id ; Counts the affected rows done by any MySQL operation (INSERT\SELECT\UPDATE\DELETE) $MySql.rowCount(); Returns how affected rows ; Use this if want to know why nothing is working (Can be used anywhere after $MySql.Startup()) $MySql.debug(); Returns nothing ; Retrives the last MysqlError set $MySql.SQLerror(); Returns error (If any) Here is some example code:
      #include <miniSQL\miniSQL.au3> ; Set default dir for our dlls (Only has to be done once) _miniSQL_setDllDir(@ScriptDir & "\miniSQL") ; Declared as CONST since we never want to accidentally change the variables original value Local Const $MySql = _miniSQL_LoadLibrary() ;Connect to database & Init library If Not $MySql.Startup("localhost", "user", "pass", "db", 3306) Then MsgBox(0, "Failed to start library", $MySql.debug()) Exit EndIf With $MySql .prepare("SELECT * FROM members") If Not .execute() Then MsgBox(0, "Failed to execute query", .sqlError()) Local $oRows = .fetchAll() ; Print how many rows got affected by latest query ConsoleWrite(StringFormat("Number of rows to display: %s", .rowCount()) & @CRLF) ; we use isObj to check if we got any result. If IsObj($oRows) Then For $row In $oRows ConsoleWrite(StringFormat("Id: %s", $row.id) & @CRLF) ConsoleWrite(StringFormat("Name: %s", $row.name) & @CRLF) ConsoleWrite(StringFormat("Bio: %s", $row.bio) & @CRLF) Next Else ConsoleWrite("No rows to show"&@CRLF) EndIf EndWith ; Use this in your app when you are done using the database $MySql.Shutdown() #include <miniSQL\miniSQL.au3> ; Set default dir for our dlls (Only has to be done once) _miniSQL_setDllDir(@ScriptDir & "\miniSQL") ; Declared as CONST since we never want to accidentally change the variables original value Local Const $MySql = _miniSQL_LoadLibrary() ;Connect to database & Init library If Not $MySql.Startup("localhost", "user", "pass", "db", 3306) Then MsgBox(0, "Failed to start library", $MySql.debug()) Exit EndIf With $MySql ; We use an array to make our query look nicer Local $vars = [":name", @UserName&Random(1,10,1)] ; Prepare our statement .prepare("UPDATE members SET name = :name WHERE 1") If Not .execute($vars) Then MsgBox(0, "Failed to execute query", .sqlError()) ; Print how many rows got affected by latest query ConsoleWrite(StringFormat("Example 1 rows affected: %s", .rowCount()) & @CRLF) EndWith ; We can also prepare like this With $MySql Local $vars = ["?", @UserName, "?", 1] ; Prepare our statement .prepare("UPDATE members SET name = ? WHERE id = ?") If Not .execute($vars) Then MsgBox(0, "Failed to execute query", .sqlError()) ; Print how many rows got affected by latest query ConsoleWrite(StringFormat("Example 2 rows affected: %s", .rowCount()) & @CRLF) EndWith ; Use this in your app when you are done using the database $MySql.Shutdown()  
      With $MySql ; We use an array to make our query look nicer Local $vars = ["?", 1] ;Line by line prepared statement .prepareClean(); .prepareGlue("SELECT *") .prepareGlue("FROM members") .prepareGlue("WHERE id = ?") If Not .execute($vars) Then MsgBox(0, "Failed to execute query", .sqlError()) ; Print how many rows got affected by latest query ConsoleWrite(StringFormat("Example 1 rows affected: %s", .rowCount()) & @CRLF) EndWith ; Use this in your app when you are done using the database $MySql.Shutdown() Some code from one of my applications at work using this UDF
      With $MySql .prepareClean() .prepareGlue("SELECT") .prepareGlue("cases.cases_dedu_casenumber,") .prepareGlue("cases.cases_created_by_ugid,") .prepareGlue("cases.cases_dedu_ftg,") .prepareGlue("cases.cases_date_created,") .prepareGlue("cases.cases_date_finished,") .prepareGlue("cases.cases_protocol_director,") .prepareGlue("cases.cases_finished_by_ugid,") .prepareGlue("IFNULL(uid1.names_name, 'none') as createdByFullname,") .prepareGlue("IFNULL(uid2.names_name, 'none') as finishedByFullname") .prepareGlue("FROM cases") .prepareGlue("LEFT JOIN names AS uid1") .prepareGlue("ON cases.cases_created_by_ugid = uid1.names_uid") .prepareGlue("LEFT JOIN names AS uid2") .prepareGlue("ON cases.cases_finished_by_ugid = uid2.names_uid") if $_App_Case_SearchFor Then .prepareGlue(StringFormat("WHERE cases_dedu_casenumber LIKE '%s'",$_App_Case_SearchFor)) .prepareGlue("ORDER BY cases.cases_date_created DESC") .prepareGlue("LIMIT 0, 30") if not .execute() then return __ThrowException(.sqlError()) Local $oRows = .fetchAll() EndWith  
       
      Git: https://gitlab.com/xdtarrexd/MiniSQL.git
      Download: Zip generated from Github
       
      Feel free to open your mind about this