Jump to content

Search the Community

Showing results for tags 'mysql'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • General
    • Announcements and Site News
    • Administration
  • AutoIt v3
    • AutoIt Help and Support
    • AutoIt Technical Discussion
    • AutoIt Example Scripts
  • Scripting and Development
    • Developer General Discussion
    • Language Specific Discussion
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office

Categories

  • AutoIt Team
    • Beta
    • MVP
  • AutoIt
    • Automation
    • Databases and web connections
    • Data compression
    • Encryption and hash
    • Games
    • GUI Additions
    • Hardware
    • Information gathering
    • Internet protocol suite
    • Maths
    • Media
    • PDF
    • Security
    • Social Media and other Website API
    • Windows
  • Scripting and Development
  • IT Administration
    • Operating System Deployment
    • Windows Client
    • Windows Server
    • Office

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


Member Title


Location


WWW


Interests

Found 25 results

  1. Hi there, I used MySQL UDF for MySQL connection and work normally on MySQL 5.x series but MySQL server update to 8.0.36 recently and UDF I used seems not support, even I replaced the libmysql.dll which copied from MySQL installation folder and I tried to use the MySQL related UDF from Wiki Collection ( EzMySql, MySQL (without ODBC) ... etc. ) still not supported 8.0+ either does any MySQL related UDF support MySQL 8.0+ without ODBC driver ? ( work station without ODBC driver ) thanks
  2. I am encountering a perplexing issue while running a script, and I would appreciate your insights and assistance in resolving it. Here's a brief description of the problem: When I execute the script within the AutoIt environment, it runs smoothly without any errors or problems. However, when I compile the script into an executable and attempt to run it, an error occurs stating, "Variable used without being declared." I have made diligent efforts to identify the undeclared variable, but unfortunately, I have been unsuccessful in locating it. I have reviewed EzMySqll.au3 UDF made by @Yoriz thoroughly, and declared all undeclared variables now they all appear to be appropriately declared before usage. Global $str_db_host, $str_db_user, $str_db_password, $str_database Func data_computers_insert() Local $serialNumber Local $computerName Local $submissionDate Local $sqlQuery ; Start the MySQL connection _EzMySql_Startup() ; Open the connection to the database _EzMySql_Open($str_db_host, $str_db_user, $str_db_password, $str_database, "3306") If @error Then MsgBox(16, "Error", "Failed to open the MySQL connection.") Return EndIf ; Set the values for the computer $serialNumber = RegRead("HKEY_LOCAL_MACHINE\HARDWARE\DESCRIPTION\System\BIOS", "SystemProductName") If @error Then MsgBox(16, "Error", "Failed to read the serial number from the registry.") _EzMySql_Close() _EzMySql_ShutDown() Return EndIf $computerName = @ComputerName $submissionDate = @YEAR & "-" & @MON & "-" & @MDAY ; Build the SQL query $sqlQuery = "INSERT INTO Computers (SerialNumber, ComputerName, SubmissionDate) VALUES ('" & $serialNumber & "', '" & $computerName & "', '" & $submissionDate & "')" ; Display the SQL query in the console ConsoleWrite("SQL Query: " & $sqlQuery & @CRLF) ; Execute the query _EzMySql_Exec($sqlQuery) If @error Then MsgBox(16, "Error", "Failed to execute the SQL query.") _EzMySql_Close() _EzMySql_ShutDown() Return EndIf _EzMySql_Close() _EzMySql_ShutDown() EndFunc I run this script using mentioned UDF. But I keep getting error when my program is compiled. After error msgbox there is an empty file created called libmySQL_x64.dll, and its only created there when I get an error.
  3. Hello, I am facing difficulties connecting to SQL Server using AutoIt and would greatly appreciate some help. I have already tried using the MySQL UDF (User Defined Function) and the latest ODBC connect driver from the provided link here However, I have been unable to establish a successful connection. I have come across posts about this topic, but most of them are over 10 years old, and I suspect they might be outdated. I even attempted to change the driver name in the https://www.autoitscript.com/forum/applications/core/interface/file/attachment.php?id=15889 UDF from "MySQL ODBC 3.51 Driver" to the latest driver I obtained, but I still haven't been able to connect. If anyone has any guidance, suggestions, or updated information on connecting to SQL Server using AutoIt, I would be grateful for your assistance. Thank you in advance for your help! Best regards, Here is a code im trying to connect with... Func _MySQLEnd($oConnectionObj) If IsObj($oConnectionObj) Then $oConnectionObj.close Return 1 Else SetError(1) Return 0 EndIf EndFunc ;==>_MySQLEnd Func _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver = "{MySQL ODBC 3.51 Driver}", $iPort=3306) Local $v = StringMid($sDriver, 2, StringLen($sDriver) - 2) Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $v) If @error or $val = "" Then SetError(2) Return 0 EndIf $ObjConn = ObjCreate("ADODB.Connection") $Objconn.open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";PORT="&$iPort) If @error Then SetError(1) Return 0 Else Return $ObjConn EndIf EndFunc ;==>_MySQLConnect ; Example usage Local $host = "host" Local $user = "user" Local $password = "password" Local $database = "database" Local $conn = _MySQLConnect($host, $user, $password, $database) If $conn Then MsgBox(64, "Success", "Connected to MySQL database successfully!") ; Disconnect from the MySQL server _MySQLEnd($conn) else MsgBox(64, "error ", "connection failed") EndIf
  4. Other than different methodologies, are there any differences between the two? Does one work out to be faster or more reliable than the other when deployed at scale? I'm trying out both UDFs, was curious which method is preferred by the community.
  5. MySQL UDFs using libmysql.dll functions: most functions from MySQL API all are prefixed with an underscore: _MySql... e.g.: _MySQL_Real_Query( sometimes parameters are chaged - read function descriptions in include file MySQL.au3 If you do not need the power of these UDFs and you simple want to use basic SQL commands, then have a look at not included: MySQL_Connect - This function is deprecated. Use _MySQL_Real_Connect instead. MySQL_Create_DB - This function is deprecated. Use mysql_query() to issue an SQL CREATE DATABASE statement instead. MySQL_Drop_DB - This function is deprecated. Use mysql_query() to issue an SQL DROP DATABASE statement instead. MySQL_Escape_String - You should use _mysql_real_escape_string() instead! MySQL_Kill - This function is deprecated. Use mysql_real_query() to issue an SQL KILL statement instead mysql_library_end - Called by _MySQL_EndLibrary. mysql_library_init - Called by _MySQL_InitLibrary. I included a fallback-libmysql.dll: yoou can include libMySQLDLL.au3 and set $Use_EmbeddedDLL=True when calling _MySQL_InitLibrary an example for XAMPP / cdcol is also included in ZIP. #cs ---------------------------------------------------------------------------- AutoIt Version: 3.2.8.1 (beta) Author: Prog@ndy Script Function: MySQL-Plugin Demo Script #ce ---------------------------------------------------------------------------- #include <array.au3> #include "mysql.au3" ; MYSQL starten, DLL im PATH (enthält auch @ScriptDir), sont Pfad zur DLL angeben. DLL muss libmysql.dll heißen. _MySQL_InitLibrary() If @error Then Exit MsgBox(0, '', "could nit init MySQL") MsgBox(0, "DLL Version:",_MySQL_Get_Client_Version()&@CRLF& _MySQL_Get_Client_Info()) $MysqlConn = _MySQL_Init() ;Fehler Demo: MsgBox(0,"Error-demo","Error-Demo") $connected = _MySQL_Real_Connect($MysqlConn,"localhostdfdf","droot","","cdcol") If $connected = 0 Then $errno = _MySQL_errno($MysqlConn) MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn)) If $errno = $CR_UNKNOWN_HOST Then MsgBox(0,"Error:","$CR_UNKNOWN_HOST" & @LF & $CR_UNKNOWN_HOST) Endif ; XAMPP cdcol MsgBox(0, "XAMPP-Cdcol-demo", "XAMPP-Cdcol-demo") $connected = _MySQL_Real_Connect($MysqlConn, "localhost", "root", "", "cdcol") If $connected = 0 Then Exit MsgBox(16, 'Connection Error', _MySQL_Error($MysqlConn)) $query = "SELECT * FROM cds" $mysql_bool = _MySQL_Real_Query($MysqlConn, $query) If $mysql_bool = $MYSQL_SUCCESS Then MsgBox(0, '', "Query OK") Else $errno = _MySQL_errno($MysqlConn) MsgBox(0,"Error:",$errno & @LF & _MySQL_error($MysqlConn)) EndIf $res = _MySQL_Store_Result($MysqlConn) $fields = _MySQL_Num_Fields($res) $rows = _MySQL_Num_Rows($res) MsgBox(0, "", $rows & "-" & $fields) ; Access2 1 MsgBox(0, '', "Access method 1- manual") Dim $array[$rows][$fields] For $k = 1 To $rows $mysqlrow = _MySQL_Fetch_Row($res,$fields) $lenthsStruct = _MySQL_Fetch_Lengths($res) For $i = 1 To $fields $length = DllStructGetData($lenthsStruct, 1, $i) $fieldPtr = DllStructGetData($mysqlrow, 1, $i) $data = DllStructGetData(DllStructCreate("char[" & $length & "]", $fieldPtr), 1) $array[$k - 1][$i - 1] = $data Next Next _ArrayDisplay($array) ; Access 2 MsgBox(0, '', "Access method 2 - row for row") _MySQL_Data_Seek($res, 0) ; just reset the pointer to the beginning of the result set Do $row1 = _MySQL_Fetch_Row_StringArray($res) If @error Then ExitLoop _ArrayDisplay($row1) Until @error ; Access 3 MsgBox(0, '', "Access method 3 - read whole result in 2D-Array") $array = _MySQL_Fetch_Result_StringArray($res) _ArrayDisplay($array) ; fieldinfomation MsgBox(0, '', "Access fieldinformation") Dim $arFields[$fields][3] For $i = 0 To $fields - 1 $field = _MySQL_Fetch_Field_Direct($res, $i) $arFields[$i][0] = _MySQL_Field_ReadValue($field, "name") $arFields[$i][1] = _MySQL_Field_ReadValue($field, "table") $arFields[$i][2] = _MySQL_Field_ReadValue($field, "db") Next _ArrayDisplay($arFields) ; free result _MySQL_Free_Result($res) ; Close connection _MySQL_Close($MysqlConn) ; exit MYSQL _MySQL_EndLibrary() MySQL UDf Downloads: (including x86 and x64)</array.au3>
  6. $sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1"; $rs = mysqli_query($conn, $sQueryUpdateTime); $row = mysqli_fetch_assoc($rs); the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool. $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows"; // takes 30 ms. on the indexed int $sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows"; // takes 300 ms. due to "PatientName" been a text field, even as I did index it So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast. ..tho, I find that any 2nd argument in the ORDER BY is just slow. Is there a way to query this in a way to have a faster result back ? Thanks PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE; and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).
  7. I was thinking but I don't have the experience, so you may have the experience. I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ? The DB is now in MySQL. I wanna do all this chopping and use SQLite. Thanks [solved]
  8. Here is a complete example of combining Autoit with PHP and MySQL was written by me. Include: Sign in, sign out.Create, read, update and delete data between client (AutoIt) and server (PHP/MySQL).Only allow user to log into a client at the same time. Video demo: https://www.youtube.com/watch?v=gQyfXLO0pls Screenshot (Main GUI) Usage If you want to test on your computer, you will need to create a localhost. I recommend to use WAMP or XAMPP. First, create a new database by importing from SETUP.sql file (see video for more info). Then, edit your path to SERVER in AutoIt-PHP-MySQL.au3: Global Const $SERVER = 'http://localhost/AutoIt/index.php?act='Make sure you have copied all files in the folder "PHP + MySQL" into a folder named AutoIt in your Localhost (www or htdocs directory). Yeah, now you can open the file "AutoIt-PHP-MySQL.au3" and try it yourself! Default username and password is: admin Download: Tutorial-AutoIt-PHP-MySQL-v1.0.rar[Tutorial] AutoIt - PHP - MySQL v2.0.zip (Thank @JohnOne for having suggested using zip format)Changelog ----- Hope you enjoyed it!
  9. Hi for all! After a long time I wasted to find the best way to connect to any external MySQL server, I'm ready to hire "C" or "C++" developer to use MySQL connector dll file for create a stable way to connect to a MySQL server. I already tried to use the UDF "miniSQL - A simple Standalone PDO-syntax-like MySQL-library with AutoitObject" which is working perfect (this way what I looking for), but unfortunately after 1-2 hours usage failed and exit the script. I plan to use the MySQL connector heavily and continuously on long time. The developed connector must be have a small memory footprint. My budget is moderated, I need this connector quick as possible, please contact with me in PM for more details. Thank you for all.
  10. Hi to all, I am writing a feature to an existing internal AutoIt program, based on a client's request. The requested feature would be to grab the file via a button, which reads the file's path. Below the file browsing button would be a button to upload the file into a MySQL server. Initially, my method would be to read the contents of the file provided in the filepath, then convert the contents of the file into binary data, and then store it into a column of a table as a BLOB. I am not sure whether if this is the best approach to storing text/Word/PDF files into the MySQL Server in this way, if the program used is an AutoIt program. So far, I am only able to generate Excel files, but only because there was already an existing BLOB column in an existing table. Sample code: $sTemplateName = "SearchTemplate" $sFilePath = @ScriptDir & "\temp\" & $sTemplateName & ".xls" $iFileExists = FileExists($sFilePath) ; If template file not found, create it. If (Not $iFileExists) Then generateExcelFile($sTemplateName, $sFilePath) EndIf $iFileExists = FileExists($sFilePath) If $iFileExists = 1 Then $oExcel = _ExcelBookOpen($sFilePath, 0) _ExcelWriteCell($oExcel, "Search conducted on " & _NowCalc() & " by " & $username & "", 2, 1) $counter = 0 While Not $recordSet.EOF _ExcelWriteCell($oExcel, 1 + $counter, 6 + $counter, 1) _ExcelWriteCell($oExcel, getName("User", $recordSet.Fields("UserID").value), 6 + $counter, 2) _ExcelWriteCell($oExcel, $recordSet.Fields("InternalName").value, 6 + $counter, 3) _ExcelWriteCell($oExcel, getName("Product", $recordSet.Fields("ProductID").value), 6 + $counter, 4) _ExcelWriteCell($oExcel, $recordSet.Fields("Serial").value, 6 + $counter, 5) _ExcelWriteCell($oExcel, getName("Location", $recordSet.Fields("LocationID").value), 6 + $counter, 6) _ExcelWriteCell($oExcel, $recordSet.Fields("Remarks").value, 6 + $counter, 7) _ExcelWriteCell($oExcel, getFriendlyDate($recordSet.Fields("LastModified").value, 1), 6 + $counter, 8) _ExcelWriteCell($oExcel, getFriendlyDate($recordSet.Fields("CreationDate").value, 1), 6 + $counter, 9) $recordSet.MoveNext $counter = $counter + 1 WEnd _ExcelBookSaveAs($oExcel, $exportDir & "\" & $username & "_Search_Results_" & @YEAR & @MON & @MDAY & "_" & @HOUR & @MIN & @SEC, "xls") _ExcelBookClose($oExcel, 1, 0) EndIf
  11. 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
  12. 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!
  13. 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
  14. Hello, it's me again. This time I have problem with MySQL plugin. Here is my code #include <MySQL.au3> #include <Date.au3> #include <MsgBoxConstants.au3> $User="" $Password="" $Database="" $Serwer="" ;I guess you guys know, why those variables are empty :) $Column = _NowDate() $BazaDanych = _MySQLConnect($User, $Password, $Database, $Serwer) $Variable1 = InputBox("", "Enter a value: ") $Query = "UPDATE autoit SET '" & $Column & "' = '" & $Variable1 & "'" MsgBox(0, "MySQL Query Preview", $Query) _Query($BazaDanych, $Query) _MySQLEnd($BazaDanych)
  15. 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
  16. 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!
  17. I am having trouble understanding why the following code does not work. THe way it is now, with this line commented out... ;$iRval = _EzMySql_GetTable2d($sSQL ...it works fine and populates the excel spreadsheet with the heading rows "System Impact" etc. However, if I uncomment out the line I mention above, nothing goes to the Excel spreadsheet. Where I have the messagebox reporting the value of Ubound on the array (for debugging purposes) it returns the correct value, i.e. the number of rows the sql has returned. I have also included a screen snap of the array display, showing that the array does indeed contain data. Here is the function Func _Excel_Xport() local $cntr,$wf,$tempresult,$iSval,$iRval[10][10] local $sFilePath = @TempDir & "\Temp.xls" local $oExcel = _Excel_Open() If Not _FileCreate($sFilePath) Then MsgBox(4096, "Error", " Error Creating File - " & @error & " - " & @extended) return 1 EndIf $oExcel = _Excel_BookOpen($oExcel,$sFilePath, Default, Default, True) if $oExcel = 0 Then MsgBox(0,"Error",@error) Return EndIf $oExcel = _Excel_BookAttach("Microsoft Excel - Temp.xls","Title") if $oExcel = 0 Then MsgBox(0,"Error","Unable to attach to spreadsheet") Return EndIf for $cntr = 1 to 8 if GUICtrlRead($r_[$cntr]) = 1 Then Switch $cntr Case 1 $wf="%" Case 2 $wf="%5" Case 3 $wf="1_0" Case 4 $wf=20 Case 5 $wf=30 Case 6 $wf=40 Case 7 $wf=50 Case 8 $wf=3 EndSwitch ExitLoop EndIf next $sSQL="SELECT * FROM rfc_manager.tbl_RFC WHERE RFC_Workflow_Status LIKE """ & $wf & """ AND RFC_Workflow_Status <> ""9999"";" ;$iRval = _EzMySql_GetTable2d($sSQL) $error = @error msgbox(0,"test",Ubound($iRval)) $iRval[0][0]="Test" if uBound($iRval)=1 Then MsgBox(0, $sSQL & " error", $error) ReDim $iRval[UBound($iRval)][26] ; Add a few columns to the array $iRval[0][13]="System Impact" $iRval[0][14]="Tech Est Hours" $iRval[0][15]="Tech Quoted Hours" $iRval[0][16]="Func Est Hours" $iRval[0][17]="Func Quoted Hours" $iRval[0][18]="Admin Est Hours" $iRval[0][19]="Admin Quoted Hours" $iRval[0][20]="Payment Method" $iRval[0][21]="PO Number" $iRval[0][22]="CAB Priority" $iRval[0][23]="CAB Notes" $iRval[0][24]="Vendor Notes" $iRval[0][25]="Reason Notes" _ArrayDisplay($iRval) $iSval = _Excel_RangeWrite($oExcel,$oExcel.Activesheet, $iRval) if $iSval = 0 Then msgbox(0,"Error",@error) EndFunc And here is the array displayed <a href="http://s111.photobucket.com/user/wongdai/media/array_display_zps9832c577.jpg.html" target="_blank"><img src="'>" border="0" alt=" photo array_display_zps9832c577.jpg"/></a> I hope I have explained this clearly enough
  18. So I'm trying to build a function into my script which will verify someone's copy of the program online somehow. I first looked up a MySQL UDF however I see this requires a driver to be installed, and I assume that the user themself would also have to install this driver on their own computer? That's way too much of a hassle, so i'm wondering if there is a way to have an autoit script connect to a php script in any way and use the PHP to do the MySQL verification process, then read the result back into the autoit script? I don't need a complete answer just wondering if this is possible and if so does anyone know of any examples of this? I've been searching for a while now but haven't come across anything. It should also be noted that I'm looking for this to be a completely behind the scenes type of verification, within the script itself only and not having a browser open or anything like that. Thanks. As a simple example all i need is the autoit script to connect to example.com/example.php, and this page displays a simple "yes" or "no" text on a blank page which the script can then read into a variable for use. Hope this clears things up.
  19. I was too lazy to frequent backups my database on localhost via phpMyAdmin so I wrote a very simple script to backup databases via command com. Maybe it will be useful to someone... It backs up all or selected databases into one or separate sql files, e.g: single file output: 20130406.022354_drupal,test.sql separate files output: 20130406.022354_drupal.sql 20130406.022354_test.sql Recommended php utility to import .sql files into MySql: BigDump: Staggered MySQL Dump Importer #include <Array.au3> #include <Constants.au3> ; ------------------------------------------------------------------------ ; BACKUP MYSQL DATABASES ON LOCALHOST ; ------------------------------------------------------------------------ ; Definition and meaning: ; $export_defs ..... combine two constants: $cEXPORT_DB + ($cEXPORT_TO_... or $cEXPORT_AS_...) ; e.g. [ $cEXPORT_DB_ALL_DATABASES + $cEXPORT_TO_SINGLE_FILE ] => export all dbs to one file ; $custom_dbs ...... user-created databases. Use comma as separator, e.g. "drupal, joomla" ; $export_path ..... an export destination folder ; $dbUsr ........... user login credentials, usually 'root' ; $dbPwd ........... passwords for MySQL accounts ; $dbSrv ........... MySQL server, 127.0.0.1 for localhost ; $sMySqlPath ...... full path to MySQL bin directory ; $sSytemDbs ....... list of databases created during installation MySql app ; use this constants in variable $export_defs: Const $cEXPORT_DB_SYSTEMS_ONLY = 1 ; export default databases (e.g. XAMPP default databases) Const $cEXPORT_DB_NON_SYSTEMS = 2 ; export user-created databases (e.g. all non XAMPP default dbs) Const $cEXPORT_DB_ALL_DATABASES = 4 ; export all databases Const $cEXPORT_DB_CUSTOM_DATABASES = 8 ; export selected databases (e.g. 'Drupal' database only) Const $cEXPORT_TO_SINGLE_FILE = 128 ; export databases as one .sql file Const $cEXPORT_AS_SEPARATE_FILES = 256 ; export each stored database as separate .sql file ;=== user definition ===================================================>> Local $export_defs = $cEXPORT_DB_CUSTOM_DATABASES + $cEXPORT_AS_SEPARATE_FILES ;Local $export_defs = $cEXPORT_DB_NON_SYSTEMS + $cEXPORT_TO_SINGLE_FILE Local $custom_dbs = "drupal" ; as separator use comma, e.g. "drupal, joomla" Local $export_path = "E:\Backup\FullBackup\Aplikace\MySQL" Local $dbUsr = "root" Local $dbPwd = "123456" Local $dbSrv = "127.0.0.1" Local $sMySqlPath = "C:\xampp\mysql\bin\" Local $sSytemDbs = "cdcol, information_schema, mysql, performance_schema , phpmyadmin, test, webauth" ;=== user definition ==== (Do not change anything below this line) ====<< $export_path = StringRegExpReplace($export_path, "[\\/]+\z", "") & "\" $sMySqlPath = StringRegExpReplace($sMySqlPath, "[\\/]+\z", "") & "\" Local $sMySqlExe = FileGetShortName($sMySqlPath & "mysql.exe") Local $sMySqlDmpExe = FileGetShortName($sMySqlPath & "mysqldump.exe") Local $sFormat = "%s -u %s -p%s -h%s %s -e ""show databases"" -s -N" Local $sExtCmd = StringFormat($sFormat, $sMySqlExe, $dbUsr, $dbPwd, $dbSrv) Local $aSytemDbs = StringSplit(StringStripWS($sSytemDbs, 8), ",", 2) Const $2L = @LF & @LF If FileExists($sMySqlExe) <> 1 Then MsgBox(8240, "MySql.exe not found", "The mysql.exe not found!" & $2L & _ "The path '$export_path' is probably not being set properly.") Exit EndIf ; run in cmd Local $CMD = Run(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD+$STDOUT_CHILD) ProcessWaitClose($CMD) Local $sMsg = StdoutRead($CMD) Local $sErr = StderrRead($CMD) ; if an error in mysql.exe (eg. server is not running) If StringInStr($sErr, "ERROR") <> 0 Then MsgBox(8208, "Error", $sErr) Exit EndIf If StringLen($sMsg) = 0 Then MsgBox(8208, "Error", "Failed to get databases names") Exit EndIf ; read all installed databases to $aAllDbs array Local $aAllDbs = StringSplit($sMsg, Chr(13), 2) For $i = UBound($aAllDbs) - 1 To 0 Step -1 $aAllDbs[$i] = StringStripWS($aAllDbs[$i],3) If StringLen($aAllDbs[$i]) = 0 Then _ArrayDelete($aAllDbs, $i) EndIf Next ; move requested names of databases to $aDbs array Select Case BitAND($export_defs, $cEXPORT_DB_SYSTEMS_ONLY) <> 0 $aDbs = $aSytemDbs Local $sResult = fncItemsInArray($aDbs, $aAllDbs) If @error Then MsgBox(8240, "Error", "Defined system database name '" & $sResult & "' not found!") Exit EndIf Case BitAND($export_defs, $cEXPORT_DB_NON_SYSTEMS) <> 0 $aDbs = fncArrayExclude($aAllDbs, $aSytemDbs) Case BitAND($export_defs, $cEXPORT_DB_ALL_DATABASES) <> 0 $aDbs = $aAllDbs Case BitAND($export_defs, $cEXPORT_DB_CUSTOM_DATABASES) <> 0 $aDbs = StringSplit(StringStripWS($custom_dbs, 8), ",", 2) Local $sResult = fncItemsInArray($aDbs, $aAllDbs) If @error Then MsgBox(8240, "Error", "Defined custom database name '" & $sResult & "' not found!") Exit EndIf EndSelect ; export Local $sOutFile Local $sFileFirstPart = $export_path & @YEAR & @MON & @MDAY & "." & @HOUR & @MIN & @SEC $sFormat = "%s --lock-all-tables -u %s -p%s -h%s %s > " & """" & "%s" & """" Select Case BitAND($export_defs, $cEXPORT_TO_SINGLE_FILE) <> 0 $sOutFile = FileGetShortName($sFileFirstPart & "_" & _ArrayToString($aDbs, ",") & ".sql") $sExtCmd = StringFormat($sFormat, $sMySqlDmpExe, $dbUsr, $dbPwd, $dbSrv, "-B " & _ _ArrayToString($aDbs, " "), $sOutFile) $CMD = RunWait(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) If FileExists($sOutFile) = 0 Then MsgBox(8208, "Error", "An error occurring during the export..." & $2L & "databases: " & _ _ArrayToString($aDbs, ", ") & @LF & "destination: " & $sOutFile) Exit EndIf Case BitAND($export_defs, $cEXPORT_AS_SEPARATE_FILES) <> 0 For $x = 0 To UBound($aDbs) - 1 $sOutFile = FileGetShortName($sFileFirstPart & "_" & $aDbs[$x] & ".sql") $sExtCmd = StringFormat($sFormat, $sMySqlDmpExe, $dbUsr, $dbPwd, $dbSrv, $aDbs[$x], $sOutFile) $CMD = RunWait(@ComSpec & " /c " & $sExtCmd, "", @SW_HIDE, $STDERR_CHILD + $STDOUT_CHILD) If FileExists($sOutFile) = 0 Then MsgBox(8208, "Error", "An error occurring during the export..." & $2L & "database: " & _ $aDbs[$x] & @LF & "destination: " & $sOutFile) Exit EndIf Next EndSelect ; final msg $sFormat = "%s database%s was exported:%s%s%sTo destination:%s%s" $sMsg = StringFormat($sFormat, UBound($aDbs), _iIf(UBound($aDbs) > 1, "s", ""), $2L, "- " & _ _ArrayToString($aDbs, @LF & "- "), $2L, $2L, $export_path) MsgBox(8256, "Done", $sMsg) Exit ; ------------------------------------------------------------------- ; Check if all items from $aSrc are included in $aCmp ; ------------------------------------------------------------------- Func fncItemsInArray($aSrc, $aCmp) Local $bFound, $i, $j For $i = 0 To UBound($aSrc) - 1 $bFound = False For $j = 0 To UBound($aCmp) - 1 If $aSrc[$i] = $aCmp[$j] Then $bFound = True ExitLoop EndIf Next If $bFound = False Then SetError(1) Return $aSrc[$i] EndIf Next Return 1 EndFunc ;==>> fncItemsInArray ; ------------------------------------------------------------------- ; Exclude items from array based on another array ; $iFirstIdx1: ... first index of $aAll ; $iFirstIdx2: ... first index of $aExclude ; ------------------------------------------------------------------- Func fncArrayExclude($aAll, $aExclude, $iFirstIdx1=0, $iFirstIdx2=0) Local $bFound, $i, $j, $aResult[1] For $i = $iFirstIdx1 To UBound($aAll) - 1 $bFound = False For $j = $iFirstIdx2 To UBound($aExclude) - 1 If $aAll[$i] = $aExclude[$j] Then $bFound = True ExitLoop EndIf Next If $bFound = False Then If StringLen($aResult[0]) <> 0 Then ReDim $aResult[UBound($aResult) + 1] EndIf $aResult[UBound($aResult)-1] = $aAll[$i] EndIf Next Return $aResult EndFunc ; ==>> fncArrayExclude ; ------------------------------------------------------------------- ; _Iif from MISC ; ------------------------------------------------------------------- Func _Iif($fTest, $vTrueVal, $vFalseVal) If $fTest Then Return $vTrueVal Else Return $vFalseVal EndIf EndFunc ;==>_Iif Export_MySql_Databases_v1.au3
  20. I can get most of this to work, however I am at the last few steps of finalizing my code and need some help. I have a mysql database with tons of data that is refreshed every night. The things I need help with is to get the data from mysql to the list box, seperating each name to a new line. Then what I am trying to accomplish is, I want to be able to highlight the computer name and have the buttons perform their action. Example: If I choose branch 1 from the drop down list, and it pulls all the computers (8 of them) from mysql and displays them in the list box one pc per line. Then if I click Ping it scrolls through the list and returns the output of the ping into the read-only display boxes. Then if I stop the ping and select computers 2, 4, and 7 from the list box, I want to be able to get the IP addresses of those PC's (using mysql) and send the IP to a seperate program. Lastly I would like to be able to select computers 1, 3, and 8 (if they are not reponding) and get thier IPs and their MAC addresses (from mysql) and send that data to the Wake On Lan Function. Here is my code: #include <File.au3> #include <ButtonConstants.au3> #include <EditConstants.au3> #include <Constants.au3> #include <GUIConstantsEx.au3> #include <GUIConstants.au3> #include <GUIListBox.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <Array.au3> #include <WinAPI.au3> #include <GUIComboBox.au3> #Include <String.au3> #include <mysql.au3> ; Declare a flag Global $fPing = False Global $Input1 = False Global $Input2 = False GUICreate("Am I Asleep?", 315, 850) GUISetState() ;~ IP Addresses $List1 = FileRead("c:\dhcp2.txt") ; Branch1 $List2 = FileRead("\server.test1.local\Installers\dhcp2.txt") ; Branch2 $List3 = ("") ; Branch3 $List4 = ("") ; Branch4 $List5 = ("") ; Branch5 $List6 = ("") ; Branch6 $List7 = ("") ; Branch7 $List8 = ("") ; Branch8 $List9 = ("") ; Branch9 $List10 = ("") ;Branch10 $List11 = ("") ; Branch11 $List12 = ("") ; Branch12 $List13 = ("") ; Branch13 $List14 = ("") ; Branch14 $List15 = ("") ; Branch15 $List16 = ("") ; Branch16 $List17 = ("") ; Branch17 $List18 = FileRead("C:\SC_PC.txt") ; Branch18 $List19 = ("") Branch Servers $List20 = ("") ; Core Servers Local $Combo1 = GUICtrlCreateCombo("", 10, 20, 125, 10) GUICtrlSetData(-1, "Branch1|Branch2|Branch3|Branch4|Branch5|Branch6|Branch7|Branch8|Branch9|Branch10|Branch11|Branch12|Branch13|Branch14|Branch15|Branch16|Branch17|Branch18|Branch Servers|Core Servers") ; Set Cuebanner rather than add an extra element to the combo <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< ; Get handle of combo edit $tInfo = $tagCOMBOBOXINFO _GUICtrlComboBox_GetComboBoxInfo($Combo1, $tInfo) $hComboEdit = DllStructGetData($tInfo, "hEdit") ; Write CueBanner $tText = _WinAPI_MultiByteToWideChar("Select one") _SendMessage($hComboEdit, $EM_SETCUEBANNER, False, $tText, 0, "wparam", "struct*") Local $Input1 = GUICtrlCreateList("", 150, 20, 150, 815, BitOR($ES_READONLY, $WS_BORDER, $WS_VSCROLL)) GUICtrlSetLimit(-1, 200) ; to limit horizontal scrolling Local $Input2 = GUICtrlCreateList("", 440, 20, 150, 815, BitOR($ES_READONLY, $WS_BORDER, $WS_VSCROLL)) GUICtrlSetState(-1, $GUI_HIDE) ;~ Controls Local $Button_1 = GUICtrlCreateButton("Ping", 10, 340, 115) Local $Button_2 = GUICtrlCreateButton("Wake On Lan", 10, 380, 115) Local $Button_3 = GUICtrlCreateButton("Netsupport Manager", 10, 440, 115) Local $Button_4 = GUICtrlCreateButton("Remote Desktop", 10, 475, 115) Local $Button_5 = GUICtrlCreateButton("VNC", 10, 510, 115) Local $Button_6 = GUICtrlCreateButton("Start WHD", 10, 750, 115) Local $Button_7 = GUICtrlCreateButton("Stop WHD", 10, 780, 115) Local $Display1 = GUICtrlCreateEdit("", 10, 70, 125, 30, BitOR($ES_READONLY, $ES_CENTER)) ;, 0) Local $Display2 = GUICtrlCreateEdit("", 10, 110, 125, 30, BitOR($ES_READONLY, $ES_CENTER)) ;, 0) $iIndex = 0 _GUICtrlListBox_ClickItem($Input1, $iIndex) $iCount = _GUICtrlListBox_GetCount($Input1) ; _AddHorzSep(10, 40, 290) _AddHorzSep(10, 325, 125) _AddHorzSep(10, 420, 125) ;~ _AddHorzSep(10, 690, 125) ; Get a timestamp $iBegin = TimerInit() While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE Exit Case $Button_1 Switch GUICtrlRead($Button_1) ; What does the button tell us we are going to do? Case "Ping" ; We need to ping $fPing = True ; And set the button text accordingly GUICtrlSetData($Button_1, "Stop") ; Disable the combo <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< GUICtrlSetState($Combo1, $GUI_DISABLE) Case Else ; Now we need to stop pinging $fPing = False ; And again change the button text GUICtrlSetData($Button_1, "Ping") ; Enable the combo <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< GUICtrlSetState($Combo1, $GUI_ENABLE) EndSwitch Case $Combo1 Switch GUICtrlRead($Combo1) Local $ip = mysql("PC1") Local $mac = mysql("PC1") Case "Branch1" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch1 GUICtrlSetData($Input1, "|" & $List1) ;~ GUICtrlSetData($Input2, "|" & $List21) Case "Branch2" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch2 GUICtrlSetData($Input1, "|" & $List2) ;~ GUICtrlSetData($Input2, "|" & $List22) Case "Branch3" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch3 GUICtrlSetData($Input1, "|" & $List3) ;~ GUICtrlSetData($Input2, "|" & $List23) Case "Branch4" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch4 GUICtrlSetData($Input1, "|" & $List4) ;~ GUICtrlSetData($Input2, "|" & $List24) Case "Branch5" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch5 GUICtrlSetData($Input1, "|" & $List5) ;~ GUICtrlSetData($Input2, "|" & $List25) Case "Branch6" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch6 GUICtrlSetData($Input1, "|" & $List6) ;~ GUICtrlSetData($Input2, "|" & $List26) Case "Branch7" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch7 GUICtrlSetData($Input1, "|" & $List7) ;~ GUICtrlSetData($Input2, "|" & $List27) Case "Branch8" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch8 GUICtrlSetData($Input1, "|" & $List8) ;~ GUICtrlSetData($Input2, "|" & $List28) Case "Branch9" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch9 GUICtrlSetData($Input1, "|" & $List9) ;~ GUICtrlSetData($Input2, "|" & $List29) Case "Branch10|" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch10 GUICtrlSetData($Input1, "|" & $List10) ;~ GUICtrlSetData($Input2, "|" & $List30) Case "Branch11" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch11 GUICtrlSetData($Input1, "|" & $List11) ;~ GUICtrlSetData($Input2, "|" & $List31) Case "Branch12" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch12 GUICtrlSetData($Input1, "|" & $List12) ;~ GUICtrlSetData($Input2, "|" & $List32) Case "Branch13" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch13 GUICtrlSetData($Input1, "|" & $List13) ;~ GUICtrlSetData($Input2, "|" & $List33) Case "Branch14" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch14 GUICtrlSetData($Input1, "|" & $List14) ;~ GUICtrlSetData($Input2, "|" & $List34) Case "Branch15" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch15 GUICtrlSetData($Input1, "|" & $List15) ;~ GUICtrlSetData($Input2, "|" & $List35) Case "Branch16" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch16 GUICtrlSetData($Input1, "|" & $List16) ;~ GUICtrlSetData($Input2, "|" & $List36) Case "Branch17" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch17 GUICtrlSetData($Input1, "|" & $List17) ;~ GUICtrlSetData($Input2, "|" & $List37) Case "Branch18" ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch18 GUICtrlSetData($Input1, "|" & $List18) ;~ GUICtrlSetData($Input2, "|" & $List38) Case "Branch Servers" ;~ ConsoleWrite("Hit" & @CRLF) ; <<<<<<<<<<<<< Branch Servers GUICtrlSetData($Input1, "|" & $List19) Case "Core Servers" MsgBox(0, "", GuiCtrlRead($ip[1])) EndSwitch ; Get new count <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< $iCount = _GUICtrlListBox_GetCount($Input1) Case $Button_2 WakeOnLan() Case $Button_3 If ProcessExists("PCICTLUI.EXE") Then Netsupport() Else Run("C:\Program Files (x86)\NetSupport\NetSupport Manager\PCICTLUI.EXE", "") Netsupport() EndIf Case $Button_4 RDP() Case $Button_5 VNC() Case $Button_6 WHD_Start() Case $Button_7 WHD_Stop() EndSwitch If $fPing Then ; Have we waited long enough since the last ping? If TimerDiff($iBegin) > 1000 Then ; 1 sec delay ; Click the current item _GUICtrlListBox_ClickItem($Input1, $iIndex) ; Read the current item $sItem = GUICtrlRead($Input1) $var = Ping(GUICtrlRead($Input1), 999) If $var <> 0 Then GUICtrlSetData($Display1, $sItem) ; & " - " & $var & " ms") GUICtrlSetBkColor($Display1, 0x00FF00) GUICtrlSetColor($Display1, 0x000000) GUICtrlSetData($Display2, " " & $var & " ms") GUICtrlSetBkColor($Display2, 0x00FF00) GUICtrlSetColor($Display2, 0x000000) Else GUICtrlSetData($Display1, $sItem) GUICtrlSetBkColor($Display1, 0) GUICtrlSetColor($Display1, 0xFFFFFF) GUICtrlSetData($Display2, "Request Timed Out ") GUICtrlSetBkColor($Display2, 0) GUICtrlSetColor($Display2, 0xFFFFFF) EndIf ; Reset the timestamp for the next ping $iBegin = TimerInit() ; Increase the index to select the next item $iIndex = Mod($iIndex + 1, $iCount) EndIf EndIf WEnd Func _AddHorzSep($iX, $iY, $iW) GUICtrlCreateLabel("", $iX, $iY, $iW, 1) GUICtrlSetBkColor(-1, 0x000000) EndFunc ;==>_AddHorzSep ; MySQL Function Func sql($branch) ; establish an array with too many elements Local $output[80] ; establish a counter starting at zero $count = 0 ; put query together $sql = _MySQLConnect("DHCP","Yvl1234","test","192.168.26.18") $var = _Query($sql,"SELECT * FROM Leases WHERE Branch='" & $branch & "'") With $var ;loop through the query result While NOT .EOF ;put data into array $output[$count]=.Fields("IP").value ;increment the counter $count += 1 ; end of the loop wend ; resize the array to the number of elements ReDim $output[$count] EndWith ; return the proper sized array return $output EndFunc Func WHD_Start() Run("cmd.exe") Sleep(1000) Send("cd /{Enter}") Sleep(1000) Send("cd pstools{Enter}") Sleep(1000) Send("psexec \\192.168.26.17\cmd.exe{Enter}") Sleep(5000) Send("cd /{Enter}") Sleep(1000) Send("cd Program Files\WebHelpDesk{Enter}") Sleep(1000) Send("whd_start.bat{Enter}") EndFunc Func WHD_Stop() Run("cmd.exe") Sleep(1000) Send("cd /{Enter}") Sleep(1000) Send("cd pstools{Enter}") Sleep(1000) Send("psexec \\192.168.26.17\ cmd.exe{Enter}") Sleep(5000) Send("cd /{Enter}") Sleep(1000) Send("cd Program Files\WebHelpDesk{Enter}") Sleep(1000) Send("whd_stop.bat{Enter}") EndFunc Func VNC() Run("C:\Program Files\RealVNC\VNC Viewer\vncviewer.exe") WinWaitActive("VNC Viewer") Send(GUICtrlRead($Input1)) Sleep(1000) Send("{ENTER}") EndFunc Func RDP() Run("mstsc.exe /console") WinWaitActive("Remote Desktop Connection") Send(GUICtrlRead($Input1)) Sleep(1000) Send("{ENTER}") EndFunc Func Netsupport() Local $Input3 = GUICtrlRead($Input1) Local $Input4 = StringTrimRight(GUICtrlRead($Input1), 12) WinActivate("NetSupport : ") Send("!C{Down}") Send("Q") Sleep(1000) Send("^A") Sleep(1000) ;~ sql() ;~ Send(GuiCtrlRead($fields2)) Send("{ENTER}") Sleep(1000) If WinActive("Security") Then Send("{Tab}") Sleep(1000) Send("ceeWee6o") Sleep(2000) Send("{Enter}") Sleep(1000) Else Send("!C{Down}") Send("w{Enter}") EndIf EndFunc Func WakeOnLan() ; need to fix this so that it removes all that funky stuff I need it to $IPAddress = GuiCtrlRead() ; I want to be able to have this use the IP address associated with the computer name in $Input1 $MACAddress = GUICtrlRead() ; I want to be able to have this use the MAC address associated with the computer name in $Input1 UDPStartUp() $connexion = UDPOpen($IPAddress, 7) $res = UDPSend($connexion, GenerateMagicPacket($MACAddress)) MsgBox(0, "", $res) UDPCloseSocket($connexion) UDPShutdown() EndFunc ; =================================================================== ; Functions ; =================================================================== ; This function convert a MAC Address Byte (e.g. "1f") to a char Func HexToChar($strHex) Return Chr(Dec($strHex)) EndFunc ; This function generate the "Magic Packet" Func GenerateMagicPacket($strMACAddress) $MagicPacket = "" $MACData = "" For $p = 1 To 11 Step 2 $MACData = $MACData & HexToChar(StringMid($strMACAddress, $p, 2)) Next For $p = 1 To 6 $MagicPacket = HexToChar("ff") & $MagicPacket Next For $p = 1 To 16 $MagicPacket = $MagicPacket & $MACData Next Return $MagicPacket EndFunc
  21. Hi Guys i need a little bit of help and advice on some code im trying to use to retrive data from sql 2008 r2 i can connect to my data base but cant seem to get any data back im using MYsql from cdkid and have attached the file for you guys to have a look though Please Help this is the error i keep getting C:\Program Files (x86)\AutoIt3\Include\Mysql.au3 (52) : ==> The requested action with this object has failed.: Return $oConnectionobj.execute ($sQuery) Return $oConnectionobj.execute ($sQuery)^ ERROR $sUsername = "sa" $sPassword = "####" $sDatabase = "Testing" $sServer = "(local)" $sDriver = "{SQL Server}" $iPort = 3306 $sql = _MySQLConnect($sUsername, $sPassword, $sDatabase, $sServer, $sDriver) ; $iPort) ;msgbox(0,"connected to server", $sql & ' - Error ' & @error,2) ;=> confirm connection works $result = _Query($sql,$sQuery) msgbox (0,@error,$result.field(" Name ").value,0) ;=> Value from the Sql query _MySQLEnd($sql)Mysql.au3
  22. I am running a mysql query that pulls down dates and department. I would like to output this into a excel file how ever I can't seem to get each rows to separate. I would like to have Departments in one row and Dates in another row. Thanks in advance. Example of output: 04/26/201404/26/201404/26/201404/26/201404/26/201404/26/201404/26/201404/26/201404/26/2014 Func ComputerModel() $SQLInstance5 = _MySQLConnect($UserName, $Password, $Database, $MySQLServerName) $Model = "" $Model2 = "" $NametoFind3 = GUICtrlRead($Userid) ;This pulls from the gui $RemoteFormatString4 = "SELECT * FROM accountinfo WHERE fields_15 BETWEEN '04/26/2014' AND '04/31/2014';" $SQLCode5 = StringFormat($RemoteFormatString4, $NametoFind3) $TableContents5 = _Query($SQLInstance5, $SQLCode5) With $TableContents5 While Not .EOF $Model &= .Fields("fields_15").value $Model2 &= .Fields("Department").value .MoveNext WEnd EndWith ;Write Excel Local $oExcel = _ExcelBookNew() Local $aArray[5][2] = [[$Model, 1],[$Model2, 2],["big_daddy", 3],["DaleHolm", 4],["GaryFrost", 5]] ;0-Base Array _ExcelWriteSheetFromArray($oExcel, $aArray, 1, 1, 0, 0) ;0-Base Array parameters _MySQLEnd($SQLInstance5) EndFunc ;==>ComputerModel
  23. Okay so quite a while back I thought I saw a AutoIt script that converted a MSSQL database to a MySQL database. I can’t seem to find it on the forums anywhere. Does anyone know if one exists? And if there is not an Autoit solution where I can find a good free converter. Thanks.
×
×
  • Create New...