Search the Community

Hi everyone, I am trying to make a script that runs a query and show it to me to see if everything is right and then decide if I finish it or not so I made a little script as below : #include <ADO.au3> #include <Array.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> _ADO_EVENTS_SetUp(True) _ADO_ComErrorHandler_UserFunction(_ADO_COMErrorHandler) Local $sDriver = 'SQL Server' Local $sDatabase = 'DataBase' ; change this string to YourDatabaseName Local $sServer = 'Localhost' ; change this string to YourServerLocation Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & ';PWD=' & ';' ;~ Global $Query = _ ;~ "BEGIN TRAN" & @CRLF & _ ;~ "UPDATE Table" & @CRLF & _ ;~ "SET HOUR = 4" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "SELECT * FROM Table" & @CRLF & _ ;~ "WHERE CUST = 'TEST'" & @CRLF & _ ;~ "ROLLBACK TRAN" Global $Query = _ "BEGIN TRAN" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" _Query_Display($sConnectionString, $Query) Func _Query_Display($sConnectionString, $sQUERY) ; Create connection object Local $oConnection = _ADO_Connection_Create() ; Open connection with $sConnectionString _ADO_Connection_OpenConString($oConnection, $sConnectionString) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Executing some query directly to Array of Arrays (instead to $oRecordset) Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True) ; Clean Up _ADO_Connection_Close($oConnection) $oConnection = Null ; Display Array Content with column names as headers _ADO_Recordset_Display($aRecordset, 'Query Result') EndFunc ;==> _Query_Display When I ran this script it works great, but when I run the query below : Global $Query = _ "BEGIN TRAN" & @CRLF & _ "UPDATE Table" & @CRLF & _ "SET HOUR = 4" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "SELECT * FROM Table" & @CRLF & _ "WHERE CUST = 'TEST'" & @CRLF & _ "ROLLBACK TRAN" It doesn't show anything, when I take those begin and rollback it does what it should but still not showing me anything at all, is there a way around it that you know of? Thank you.

#include <SQLite.au3> ;-- When SQLite is compiled with the JSON1 extensions it provides builtin tools ;-- for manipulating JSON data stored in the database. ;-- This is a gist showing SQLite return query as a JSON object. ;-- https://www.sqlite.org/json1.html Example() Func Example() _SQLite_Startup() ; "<your path>\sqlite3.dll", False, 1) ; https://www.autoitscript.com/autoit3/docs/libfunctions/_SQLite_Startup.htm _SQLite_Open() ; ...if you can not run this due to errors, get the latest DLL from https://www.sqlite.org/ If _SQLite_Exec(-1, "CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, full_name TEXT NOT NULL, email TEXT NOT NULL, created DATE NOT NULL );") Then Return 4 If _SQLite_Exec(-1, 'INSERT INTO users VALUES ' & _ '(1, "Bob McFett", "bmcfett@hunters.com", "32-01-01"),' & _ '(2, "Angus O''Vader","angus.o@destroyers.com", "02-03-04"),' & _ '(3, "Imperator Colin", "c@c.c", "01-01-01");') Then Return 5 ; -- Get query data as a JSON object using the ; -- json_group_object() [1] and json_object() [2] functions. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_object(" & _ " email," & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS json_result" & _ " FROM (SELECT * FROM users WHERE created > ""02-01-01"");") ; {"bmcfett@hunters.com":{"full_name":"Bob McFett","created":"32-01-01"},"angus.o@destroyers.com":{"full_name":"Angus O'Vader","created":"02-03-04"}} ; -- Get query data as a JSON object using the ; -- json_group_array() function to maintain order. _SQLite_GetTable2d_ArrayToConsole("SELECT" & _ " json_group_array(" & _ " json_object('full_name', full_name, 'created', created)" & _ " ) AS my_json_result_OrAnythingReally" & _ " FROM (SELECT * FROM users ORDER BY created);") ; [{"full_name":"Imperator Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}] ;-- Links ;-- [1] https://www.sqlite.org/json1.html#jgroupobject ;-- [2] https://www.sqlite.org/json1.html#jobj ; example found at https://gist.github.com/akehrer/481a38477dd0518ec0086ac66e38e0e2 EndFunc ;==>Example Func _SQLite_GetTable2d_ArrayToConsole($sSQL, $hDB = -1) Local $aResult, $iRows, $iColumns If _SQLite_GetTable2d($hDB, $sSQL, $aResult, $iRows, $iColumns) Then ConsoleWrite("! SQLite Error: " & _SQLite_ErrCode($hDB) & @CRLF & "! " & _SQLite_ErrMsg($hDB) & @CRLF) Else _SQLite_Display2DResult($aResult) EndIf ConsoleWrite(@CRLF) EndFunc ;==>_SQLite_GetTable2d_ArrayToConsole Based on this example, you can build your own query. The code has all the explanations. Enjoy

Hi All, Here's a really simple question. I ran the code from the helpfile under: _SQLite_Open Issue is I end up with an error message: SQLite3.dll Can't be Loaded! I placed the *.dll in the include folder, but still nothing. Where must this file be placed.

Hello, I'm having trouble getting information from Win32_Processor: https://docs.microsoft.com/en-us/windows/desktop/CIMWin32Prov/win32-processor How does this WMI Query work? Let's say I need to query VMMonitorModeExtensions, Level, ProcessorId, and ProcessorType. How would this work? Thanks.

Hello guys! I am having some difficulty in achieving a very simple task here. I have gone through the forums and tried some examples and UDFs but I can't seem to work it out. I would really appreciate if someone could help me out. Problem: Currently, I am logging the required feedback from the script into a log file in a simple way ... get the info in the var >> write it in the file But now I am in need to perform some analysis and need some of the values to go into an MSSQL table in the attached format Also, I need to be able to use Integrated Security" or "Trusted_Connection set as true or use the logged in windows credentials to connect to the server/database any help will be much appreciated!!! Thanks!

Good evening everyone I'm working on a little project of mines, and I was trying to use WMI Object. The question which I don't find an answer is: Once I do the query with WMI Object, something like "SELECT * FROM Win32_LogonSession", instead of specify the field of the collection returned, ( i.e. $colItems.Caption ), can I loop though each property and each value of the property, writing so one row of code only? Hope my question was clear enough. Thanks in advance. Best Regards.

Hey all, Has anyone created any queries for youtube? I was thinking it might be something like this but can't quite figure it out: $oIE = _IECreateEmbedded() _IENavigate($oIE,"https://www.youtube.com") Local $oForms = _IETagNameGetCollection($oIE, "form") Local $sTxt = "" For $oForm In $oForms If $oForm.id = "masthead-search" Then $oInputs = _IETagNameGetCollection($oIE, "input") For $oInput In $oInputs If $oInput.id = "masthead-search-term" Then _IEFormElementSetValue($oInput, "cards") _IEFormSubmit($oForm) EndIf Next EndIf Next

Hello! I'm fairly new to using Autoit, I like the language and simplicity, however, there is a bit of a learning curve for me. I'm stuck and need some community help! I need to manipulate a query by using GUICtrlCreateDate to select the correct date and pipe the selected date into my actual query in a specific format (yyyymmdd). Here is an example: _Flag_RecordsetDisplay($sConnectionString, "select * from trips_to_complete_20161122 where trip_type in ('P','C') and trip_status in ('S','PC','DC') and Flagged = 1") Func _Flag_RecordsetDisplay($sConnectionString, $sQUERY) ; Create connection object Local $oConnection = _ADO_Connection_Create() ; Open connection with $sConnectionString _ADO_Connection_OpenConString($oConnection, $sConnectionString) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Executing some query directly to Array of Arrays (instead to $oRecordset) Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True) ; Clean Up _ADO_Connection_Close($oConnection) $oConnection = Null ; Display Array Content with column names as headers _ADO_Recordset_Display($aRecordset, 'Recordset content') EndFunc ;==>_Flag_RecordsetDisplay The part of the query that needs modified is "trips_to_complete_20161122" I need to be able to select a date (via the gui) and that selection pipe into my query. Thanks in Advanced!

select top 0 * into #tmpdatabase from ABTABLE; select * from #tmpdatabase; BULK INSERT #tmpdatabase FROM $fileupload WITH (FIELDTERMINATOR =';',ROWTERMINATOR ='\n' ,FIRSTROW = 2); MERGE ABTABLE AS T USING #tmpdatabase AS S ON (T.A = S.A) WHEN NOT MATCHED BY TARGET THEN INSERT( [A] ,[K] ) VALUES( S.[A] ,S.[K] ) WHEN MATCHED THEN UPDATE SET T.[A] = S.[A] ,T.[K] = S.[K]; drop table #tmpdatabase; Hi, I'm trying to convert this query that I make from Toad that loads a file into a tempdatabase from .csv file based on an existing table on the sql server into autoit friendly code but don't know how to do it, been rubbing my head on this one :P, maybe need a different approach? I'm using AutoIt Version: 3.3.10.2 This doesn't seem to do anything: Local $obj_SQL_DB = _SQLConnect("db.u\SQl1", "data", 1, "At", "Pass") If @error Then Return SetError(50, @error, -1) Local $strSQL = "select top 0 * into #tmpdatabase from ABTABLE;" $obj_SQL_DB.Execute($strSQL) Any suggestions would be fantastic Nevermind i managed to do this like this instead :), mark as solved! Local $file = FileOpen("\TEST\SQL_UPD.txt",0) Local $strSQL = FileRead($file) $obj_SQL_DB.Execute($strSQL)

Hopefully someone a little more solid in SQL can give me a hand. I have a many-to-many-to-many setup. a.field1 a.field2 a.field3 x.afield1 x.bfield1 b.field1 b.field2 b.field3 y.bfield1 y.cfield1 c.field1 c.field2 c.field3 In essence, I need all a.field1 where c.field3="stringvalue". I could do it with multiple queries, and looping, but I am hoping there is a simpler, single query I can make. Thanks in advance for any help.

Hello, I,m connecting to a access mdb, it works fine except when i use a select query and the requested data does not exists, then i get an error. ; Example Local $dbName = @ScriptDir & "\test.mdb" $dbCon = ObjCreate("ADODB.Connection") ; Create DataBase connection $dbCon.Open("Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $dbName) $sQuery = "select * from sourcefiles where Text='test'" $result = $dbCon.Execute($sQuery) MsgBox(0, "", $result.Fields( "ID" ).Value)If "test" exists in column Text then i get the ID number from column ID But if it doesn't exist i get an error: MsgBox(0, "", $result.Fields( "ID" ).Value) MsgBox(0, "", $result.Fields( "ID" )^ ERROR If the value "test"does not exist i just want $result to be 0 or "" Someone an idea?

Not sure how to do this - been digging in the forums but no exp. with SQL queries. I'm sure this is probably very simple... What I'm trying to do is lookup the Win32_NetworkAdapter NetConnectionID object based on an IP address. I'm thinking the way to do that would be with two queries, first by looking up the Win32_NetworkAdapterConfiguration MACAddress from the IP and then use the MACAddress to get the Win32_NetworkAdapter NetConnectionID since both Win32_NetworkAdapterConfiguration and Win32_NetworkAdapter have the MAC. This obviously isn't right: $colItems = $objWMIService.ExecQuery('SELECT * FROM Win32_NetworkAdapterConfiguration Where $objItem.IPAddress = $IPAddress', "WQL") Anybody have some good references for structuring such queries or how to do this ? Thanks!

Hello, I can't find anything helpful in the forums about querying a Firebird database. Does anyone know if this can be done and how? Thanks

Hello, fellows. I recently gave fingerbirth to a script that reports out the current files, and users opening those files currently. Also provides a filter for strings/extensions. Suggestions and improvements are welcome. #cs ---------------------------Script Start------------------------------------- | Author: DavidLago (Hellfrost) | Script Function: Reports out a logfile containing openfiles within a (file)server, configured under an ini file #ce ---------------------------------------------------------------------------- ;---------------------------------------------------------------INCLUDES-------------------------------------------------------------------------------- #include <Process.au3> #include <GuiConstantsEx.au3> #include <WindowsConstants.au3> #include <array.au3> #include <Date.au3> ;------------------------------------------------------------EXPORT-FOLDER-------------------------------------------------------------------------------- If Not FileExists(@ScriptDir & "\Export") Then DirCreate(@ScriptDir & "\Export") EndIf ;------------------------------------------------------------ WHILE LOOP -------------------------------------------------------------------------------- Global $iNow = _NowCalc() Global $TimeOut = False While $TimeOut = False ;---------------------------------------------------------VARIABLES----------------------------------------------------------------------------------- Global $Date = @MON & "-" & @MDAY & "-" & @YEAR Global $Hour = @HOUR & "'" & @MIN & "''" Global $FullDate = $Date & "_" & $Hour Global $ReportDir = @ScriptDir & "\Export\" ;----------------------------------------------------------INI_READ----------------------------------------------------------------------------------- Global $MailFrom = IniRead("Config.ini", "Mail", "MailFrom", "Script_OpenFiles@AutoITScripting.net") Global $Mailto = IniRead("Config.ini", "Mail", "Mailto", "") Global $MailSubj = IniRead("Config.ini", "Mail", "MailSubj", "") Global $MailBody = IniRead("Config.ini", "Mail", "MailBody", "") Global $TimeFreq = IniRead("Config.ini", "Time", "Frequency", "60") Global $TimeDur = IniRead("Config.ini", "Time", "Duration", "24") Global $ReportOut = $FullDate & "_" & IniRead("Config.ini", "ReportOut", "ReportOut", "Report") Global $FilterExt = IniRead("Config.ini", "FilterExt", "FilterExt", "N") Global $FileExt = '"' & IniRead("Config.ini", "FileExt", "FileExt", "exe") & '"' Global $FormatOut = IniRead("Config.ini", "FormatOut", "FormatOut", "Table") ;-------------------------------------------------------MATH VARIABLES---------------------------------------------------------------------------------- Global $CommandCombo, $CommandFinal, $iDiff, $FreqDurMath $iTimeFreq = $TimeFreq * 60000 ; Turn to miliseconds $iTimeDur = $TimeDur ; Turn to minutes $FreqDurMath = ($TimeDur * 60) / $TimeFreq ;-------------------------------------------------------ERROR-TREATMENT--------------------------------------------------------------------------------- If $iTimeFreq < 900000 Then MsgBox(4112, "Error", 'The parameter "FREQUENCY" at the config.ini file is set to a number below 15. 15 minutes is the minumum threshold', 5) $MailBody = 'The Script "' & @ScriptName & '" is reporting the following error: The parameter "FREQUENCY" at the config.ini file is set to a number below 15. ' & @CRLF & '15 minutes is the minumum threshold' SendMail($MailBody) Exit EndIf If $FreqDurMath < 1 Then $MailBody = 'The Script "' & @ScriptName & '" The parameters "FREQUENCY" and "DURATION" at the config.ini file is set to a number which prevents it to loop more than once. Do you want to continue?' SendMail($MailBody) If MsgBox(4116, "Error", 'The parameters "FREQUENCY" and "DURATION" at the config.ini file is set to a number which prevents it to loop more than once. Do you want to continue?', 10) = 7 Then Exit EndIf ;----------------------------------------------------------COMMAND BREED I------------------------------------------------------------------------------- Global $QueryCommand = "/query " & "/FO " & $FormatOut & " /V " & "/nh " Global $FindCommand = "| find /I " & $FileExt Global $ExportCommand = " > " & '"' & $ReportDir & $ReportOut & ".log" & '"' If $FilterExt = "Y" Then $CommandCombo = $QueryCommand & $FindCommand & $ExportCommand Else $CommandCombo = $QueryCommand & $ExportCommand EndIf $CommandFinal = "openfiles " & $CommandCombo ;-------------------------------------------------------------OPTIONS----------------------------------------------------------------------------------- If $FilterExt = "Y" Then If $FileExt <> "" Then TrayTip("Message from the " & @ScriptName & " Script: ", 'The command "' & $CommandFinal & '" is running in this server. Options enabled are the following:' & @CRLF & @CRLF & "Filter: " & '"' & $FilterExt & '"' & @CRLF & "Ext/String: " & $FileExt & @CRLF, 1, 1) Else TrayTip("Message from the " & @ScriptName & " Script: ", 'The command "' & $CommandFinal & '" is running in this server.', 1, 1) EndIf EndIf ;--------MAIN COMMAND-----------------------------------################## _RunDOS($CommandFinal) ;--------MAIN COMMAND-----------------------------------################## $iDiff = _DateDiff('h', $iNow, _NowCalc()) Sleep($iTimeFreq) ; <-Frequency If $iDiff > $iTimeDur Then ; <-- Is time through? $TimeOut = True $MailBody = 'The script "' & @ScriptName & '", which was running at the server "' & @ComputerName & '", generated the requested logs successfully. Please, contact your Domain administrator to claim them' SendMail($MailBody) EndIf WEnd ;==> While from line 18 ;------------------------Function to send e-mail-------------------------------------------------------------------------------------------------------------- Func SendMail($MailBody) $objEmail = ObjCreate("CDO.Message") $objEmail.From = $MailFrom $objEmail.To = $Mailto $objEmail.Subject = $MailSubj $objEmail.Textbody = $MailBody $objEmail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 $objEmail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _ "mail.domain.net" ; <----------------------------------------- Insert your mailserver here $objEmail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 $objEmail.Configuration.Fields.Update $objEmail.Send EndFunc ;==>SendMail Contents of the config.ini file: #--------------------------------------------------------------------# # Config. ini # #--------------------------------------------------------------------# #--------------------------------------------------------------------# # OPENFILES REPORT: # Reports out a logfile containing openfiles within a server #--------------------------------------------------------------------# [Time] # What frequency? (Minutes [minimum: 15 min) / How long? (Hours [Minimum: 1H) Frequency = 15 Duration = 1 [FilterExt] # GREP Extension/String? ( Y, N) FilterExt = Y [FileExt] # What's the extension or string combo to be matched? (.exe; .doc; .csv; Solidworks; Report, etc. (default: line commented by #) FileExt = doc [ReportOut] # LogFile Sufix ReportOut = Report [FormatOut] # LogFile Format: CSV or Table. (default: table) FormatOut = CSV [Mail] MailFrom = Script_OpenFiles@AutoITScripts.com Mailto = hellfrost@hellfrost.info MailSubj = OpenFiles Alert Example MailBody = This message is an alert. It might be useful for a Domain admin someday.

Hi all, I have an array and I need to send all values trough an SQL update. I now use the following: Func _upload($index) If Not _EzMySql_Startup() Then _status(1, "Unable to Connect") EndIf If Not _EzMySql_Open("myserver", "username", "pw", "db", "port") Then _status(1, "Unable to Connect") Else For $i = 0 To UBound($csv_list) - 1 if $csv_list[$i][5] > 0 Then $query = "INSERT INTO `My_Table` (`id`,`name`, `data`,`object`, `ind`,`open`,`total`,`h1`,`h2`) VALUES ('', '" & $index & "', '" & $csv_list[$i][0] & "', '" & $csv_list[$i][1] & "', '" & $csv_list[$i][2] & "', '" & $csv_list[$i][3] & "', '" & $csv_list[$i][4] & "', '" & $csv_list[$i][5] & "', '" & $csv_list[$i][6] & "')" $res = _EzMySql_Exec($query) If @error Then MsgBox(0, "1", _EzMySql_ErrMsg()) Else EndIf EndIf Next _status(2,"Updated") _EzMySql_Close() _EzMySql_ShutDown() EndIf EndFunc ;==>_upload This take time since I have to cycle all the lines of the array (it can be 1-50,000 lines) Is there a way to send the whole array trough the query? Thanks, Marco