Jump to content

Search the Community

Showing results for tags 'sql'.



More search options

  • 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

Categories

  • Forum FAQ
  • AutoIt

Calendars

  • Community Calendar

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


Location


WWW


Interests

Found 52 results

  1. I want to present BETA Version of my ADO.au3 UDF. This is modifed version of _sql.au3 UDF. For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH This is first public release , and still is as BETA DOWNLOAD LINK (in download section): Have fun, mLipok EDIT: 2016-06-03 Below some interesting topics about databases: EDIT 2016/07/04: For more info about ADO look here: https://www.autoitscript.com/wiki/ADO
  2. Hey Guys, I've been using AutoIT for about 3 years now, lurking the forums, scouring documentation, and creating things relevant to my job function. I work entry level IT and utilizing AutoIT has earned me much respect in the workplace. I made my first application on the level of sharing with the community that has no relevance to my company other than the data that is used with it. Our tech's were using development software to make simple pre-written queries. I was made aware of the process in order to assist in automating it. We found that the software they were using was being sunset in our environment so they'd need a replacement; and upon me realizing that they only needed a simple query tool, decided to 'homegrow' a simple app and save some hefty licensing fees. I built in some versatility to the SQL database you can connect to but it is only tested working with a Sybase 15 system (note the example connection string). I'd love to hear some suggestions and critique. I'm sure there are some editing functionalities that I could implement into the query edit box that I haven't bothered looking at. (other than CTRA+A to select all) Thanks guys! #include <WindowsConstants.au3> #include <GUIConstants.au3> #include <GUIConstantsEx.au3> #include <GuiEdit.au3> #include <GuiListView.au3> #include <File.au3> #include <Crypt.au3> #include <Date.au3> $oMyError = ObjEvent("AutoIt.Error","MyErrFunc") Local $unique = "SQL-Query-Tool" ;TO ALLOW ONLY ONE INSTANCE OF THE TOOL If WinExists($unique) Then MsgBox(0,'Duplicate Process', 'This script is already running....' & @CRLF & "If the window is not visible, terminate the process from Task Manager" & @CRLF & 'This instance will terminate after clicking OK') Exit EndIf AutoItWinSetTitle($unique) Local $fResized = False, $GUIs, $temp, $Qlist[0][3], $x, $selQ[2], $guics[4] Local $conName, $conStr, $conPW, $selC, $selConn, $sqlErr Local $fh Local $readme = "You may delete this entry if desired once other ." & @CRLF & _ "Although it is prefaced with 'zz' for sorting" & @CRLF & _ @CRLF & _ "-Select a Query from the drop-down" & @CRLF & _ "-Note: changing selection will not lose" & @CRLF & _ " changes made during this session" & @CRLF & _ "-Click Run Query to produce results" & @CRLF & _ @CRLF & _ "-Select from 'Query Options' to create new," & @CRLF & _ " edit name, save, and delete queries" & @CRLF & _ @CRLF & _ "-Click 'Connection Settings' to change" & @CRLF & _ " server settings" $conName = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name") If @error = 1 Then DirCreate(@AppDataDir & "\SQL-Query-Tool\") IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name", "1", "Connection_Name_Placeholder") IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String", "1", "Connection_String_Placeholder") IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password", "1", "Connection_Password_Placeholder") EndIf $selConn = IniRead(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Selected", "Number", "Placeholder") If $selConn = "Placeholder" Then DirCreate(@AppDataDir & "\SQL-Query-Tool\") IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Selected", "Number", "1") $selConn = 1 EndIf $temp = _FileListToArray(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\") If @error = 1 Or @error = 4 Then DirCreate(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\") FileWrite(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\zz_Readme.txt", $readme) $temp = _FileListToArray(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\") EndIf QueryLoad(False) $selQ[0] = $Qlist[1][0] Local $GUI = GUICreate("Simple SQL Query Tool", 260, 320, -1, -1, BitOR($WS_SIZEBOX,$WS_MINIMIZEBOX));, @DesktopWidth-275, @DesktopHeight-500) GUIRegisterMsg($WM_SIZE, 'MY_WM_SIZE') Local $querysel = GUICtrlCreateCombo("", 5, 5, 155, 25, $CBS_DROPDOWNLIST) GUICtrlSetData($querysel, $Qlist[0][1], $Qlist[1][0]) GUICtrlSetResizing(-1, 552) Local $queryopt = GUICtrlCreateCombo("Query Options", 165, 5, 90, 20, $CBS_DROPDOWNLIST) GUICtrlSetData($queryopt, "Save to File|New|Edit Name|Delete|Open Query Folder") GUICtrlSetResizing(-1, 552) Local $tab = GUICtrlCreateTab(5, 35, 235, 200) $qtab = GUICtrlCreateTabItem(" Query ") Local $query = GuiCtrlCreateEdit("", 10, 60, 240, 170) GUICtrlSetFont(-1, 9, 0, 0, "Lucida Console") GUICtrlSetData($query, $Qlist[1][1]) $rtab = GUICtrlCreateTabItem(" Results ") Local $result = GuiCtrlCreateEdit("", 10, 80, 240, 150) GUICtrlSetFont(-1, 9, 0, 0, "Lucida Console") Local $status = GUICtrlCreateLabel("Last Ran: None", 15, 62, 500) GUICtrlSetResizing(-1, 802) GUICtrlCreateTabItem("") Local $run = GUICtrlCreateButton("Run Query", 5, 270, 130, 25) GUICtrlSetResizing(-1, 584) Local $settings = GUICtrlCreateButton("Connection Settings", 140, 270, 115, 25) GUICtrlSetResizing(-1, 584) Local $consel = GUICtrlCreateCombo("", 5, 5, 390, 25, $CBS_DROPDOWNLIST) GUICtrlSetResizing(-1, 802) Local $newc = GUICtrlCreateButton("New", 400, 5, 40, 20) GUICtrlSetResizing(-1, 802) Local $edic = GUICtrlCreateButton("Edit", 445, 5, 40, 20) GUICtrlSetResizing(-1, 802) Local $delc = GUICtrlCreateButton("Delete", 490, 5, 40, 20) GUICtrlSetResizing(-1, 802) Local $test = GUICtrlCreateButton("Test Connection", 540, 5, 95, 20) GUICtrlSetResizing(-1, 802) Local $return = GUICtrlCreateButton("Return to Query", 640, 5, 95, 20) GUICtrlSetResizing(-1, 802) Local $divider = GUICtrlCreateLabel("",-5,30,760,3,BitOR($SS_SUNKEN,$WS_BORDER)) GUICtrlSetResizing(-1, 802) Local $cnamel = GUICtrlCreateLabel("Connection Name:", 15, 45) GUICtrlSetResizing(-1, 802) Local $cname = GUICtrlCreateInput("", 110, 41, 200, 20) GUICtrlSetResizing(-1, 802) Local $cstringl = GUICtrlCreateLabel(" Connection String" & @CRLF & _ "Replace your password with *PW*. For example:" & @CRLF & _ "Provider=ASEOLEDB;User ID=-USERID-;Password=*PW*;Data Source=-SERVER-:-PORT-;Initial Catalog=-DatabaseName-" & @CRLF & _ "If you need assistance with your specific connection string try using: https://www.connectionstrings.com/", 5, 70, 740, 60) GUICtrlSetResizing(-1, 802) Local $cstring = GUICtrlCreateInput("", 10, 125, 725, 20) GUICtrlSetResizing(-1, 802) Local $pwl = GUICtrlCreateLabel("Password:", 10, 165) GUICtrlSetResizing(-1, 802) Local $pw = GUICtrlCreateInput("", 65, 161, 340, 20, BitOR($ES_PASSWORD, $ES_AUTOHSCROLL)) GUICtrlSetResizing(-1, 802) Local $savc = GUICtrlCreateButton("Save", 480, 161, 100, 20) GUICtrlSetResizing(-1, 802) Local $canc = GUICtrlCreateButton("Cancel", 600, 161, 100, 20) GUICtrlSetResizing(-1, 802) Guimod("SETTINGS", $GUI_HIDE+1) GuiMod('SETTINGS-ENTRY', $GUI_DISABLE) $selC = $selConn GuiMod('RELOAD-CON') Local $hSelAll = GUICtrlCreateDummy() Dim $AccelKeys[1][2] = [["^a", $hSelAll]] GUISetAccelerators($AccelKeys, $GUI) GUISetState(@SW_SHOW) Local $nMsg While 1 $nMsg = GUIGetMsg() Switch $nMsg ;Checks if a button has been pressed Case $querysel $selQ[1] = GUICtrlRead($querysel) If $selQ[0] <> $selQ[1] Then QuerySelect() GUICtrlSetState($qtab, $GUI_SHOW) Case $queryopt Switch GUICtrlRead($queryopt) Case "Save to File" $fh = FileOpen(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & GuiCtrlRead($querysel) & ".txt", 2) FileWrite($fh, GuiCtrlRead($query)) $Qlist[_ArraySearch($Qlist, GuiCtrlRead($querysel), 0, 0, 0, 0, 1, 0)][1] = GuiCtrlRead($query) $Qlist[_ArraySearch($Qlist, GuiCtrlRead($querysel), 0, 0, 0, 0, 1, 0)][2] = False GUICtrlSetData($qtab, " Query ") Case "New" $temp = InputBox("New Query Name","Enter a name for your new query." & @CRLF & "Please be aware only letters, numbers, underscores, and spaces are allowed." & @CRLF & "Symbols will be removed.", "", "", Default, Default, Default, Default, 0, $GUI) $temp = StringRegExpReplace($temp, "[^A-Za-z0-9 _]", "") If $temp <> "" And _ArraySearch($Qlist, $temp, 0, 0, 0, 0, 1, 0) = -1 Then FileWriteLine(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $temp & ".txt", "--First Line Placeholder") QueryLoad() GUICtrlSetData($querysel, "") GUICtrlSetData($querysel, $QList[0][1], $temp) $selQ[1] = GUICtrlRead($querysel) If $selQ[0] <> $selQ[1] Then QuerySelect() Else MsgBox(0,'Invalid Input','Invalid name entered. (Symbols and Duplicate Names are not allowed)', 0, $GUI) EndIf Case "Edit Name" $temp = InputBox("Edit Query name","Enter a new name for your query." & @CRLF & "Please be aware only letters, numbers, underscores, and spaces are allowed." & @CRLF & "Symbols will be removed.", GuiCtrlRead($querysel)) $temp = StringRegExpReplace($temp, "[^A-Za-z0-9 _]", "") If $temp <> "" And _ArraySearch($Qlist, $temp, 0, 0, 0, 0, 1, 0) = -1 Then FileMove(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & GuiCtrlRead($querysel) & ".txt", @AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $temp & ".txt") $selQ[0] = $temp QueryLoad() GUICtrlSetData($querysel, "") GUICtrlSetData($querysel, $QList[0][1], $temp) $selQ[1] = GUICtrlRead($querysel) If $selQ[0] <> $selQ[1] Then QuerySelect() Else MsgBox(0,'Invalid Input','Invalid name entered. (Symbols and Duplicate Names are not allowed)', 0, $GUI) EndIf Case "Delete" If UBound($Qlist) = 2 Then MsgBox(0,'Deletion Error','You may not delete the only saved query', 0, $GUI) Else If MsgBox(4, 'Confirm Deletion', "Are you sure you want to delete the query named: " & @CRLF & GUICtrlRead($querysel), 0, $GUI) = 6 Then FileDelete(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & GuiCtrlRead($querysel) & ".txt") QueryLoad() GUICtrlSetData($querysel, "") GUICtrlSetData($querysel, $QList[0][1], $Qlist[1][0]) $selQ[1] = GUICtrlRead($querysel) $selQ[0] = $selQ[1] GUICtrlSetData($query, $Qlist[1][1]) EndIf EndIf Case "Open Query Folder" ShellExecute(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\") EndSwitch GUICtrlSetData($queryopt, 'Query Options', 'Query Options') Case $run GuiMod("RUNNING", $GUI_DISABLE) ToolTip("Executing SQL query, please wait...") $sqlCon = ObjCreate("ADODB.Connection") $sqlCon.Mode = 16 ; shared $sqlCon.CursorLocation = 3 ; client side cursor Local $connString = StringReplace(GuiCtrlRead($cstring), "*PW*", GuiCtrlRead($pw)) $sqlCon.Open($connString) If @error Then MsgBox(0, "Fail", "Failed to connect to the database") Else $sqlCon.CommandTimeout = 60 GUICtrlSetData($result, ProcessQuery(GuiCtrlRead($query))) GUICtrlSetState($rtab , $GUI_SHOW) GuiCtrlSetData($status, "Last Ran: " & GuiCtrlRead($querysel) & " - " & StringRight(_NowCalc(), 8)) EndIf ToolTip("") GuiMod("RUNNING", $GUI_ENABLE) Case $settings ToolTip("Loading...") $selC = $selConn GuiMod('MAIN', $GUI_HIDE) GuiMod('RELOAD-CON') GuiMod('SETTINGS', $GUI_SHOW) ToolTip("") Case $consel If GUICtrlRead($consel) <> $selC Then $selC = _ArraySearch($conName, GUICtrlRead($consel), 0, 0, 0, 0, 1, 1) GUICtrlSetData($cname, $conName[$selC][1]) GUICtrlSetData($cString, $conStr[$selC][1]) GUICtrlSetData($pw, StringEncrypt(False, $conPW[$selC][1])) $selC = GUICtrlRead($consel) EndIf Case $newc GuiMod('SETTINGS-SELECT', $GUI_DISABLE) GuiMod('SETTINGS-ENTRY', $GUI_ENABLE) GuiCtrlSetData($consel, '*New-Connection-Entry*', '*New-Connection-Entry*') GuiCtrlSetData($cname, '') GuiCtrlSetData($cString, '') GuiCtrlSetData($pw, '') $selC = -1 Case $edic GuiMod('SETTINGS-SELECT', $GUI_DISABLE) GuiMod('SETTINGS-ENTRY', $GUI_ENABLE) $selC = GUICtrlRead($consel) _ArrayDisplay($conName, "2") $selC = _ArraySearch($conName, $selC, 0, 0, 0, 0, 1, 1) GuiCtrlSetData($cname, $conName[$selC][1]) $conName[$selC][1] &= "*" GuiCtrlSetData($cString, $conStr[$selC][1]) GuiCtrlSetData($pw, StringEncrypt(False, $conPW[$selC][1])) Case $delc If UBound($conName) = 2 Then MsgBox(0,'Error','You can not delete the only connection setting.', 0, $GUI) Else $selC = GuiCtrlRead($consel) If MsgBox(4, 'Confirm Deletion', 'Are you sure you want to delete the connection: ' & $selC, 0, $GUI) = 6 Then ToolTip('Loading...') $selC = _ArraySearch($conName, $selC, 0, 0, 0, 0, 1, 1) $conName[$selC][1] = '*!*BLANK*!*' $conStr[$selC][1] = '*!*BLANK*!*' $conPW[$selC][1] = '*!*BLANK*!*' IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name", $conName) IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String", $conStr) IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password", $conPW) GuiMod('RELOAD-CON') ToolTip('') EndIf EndIf Case $savc If GuiCtrlRead($cname) = "" Or _ArraySearch($conName, GuiCtrlRead($cname), 0, 0, 0, 0, 1, 1) > -1 Then MsgBox(0,'Invalid Input',"You can't have a blank connection name or two connections with the same name", 0, $GUI) Else ToolTip("Loading...") If $selC = -1 Then For $i = 1 To UBound($conName)-1 If $conName[$i][1] = '*!*BLANK*!*' Then $selC = $i $conName[$i][1] = GuiCtrlRead($cname) $conStr[$i][1] = GuiCtrlRead($cstring) $conPW[$i][1] = StringEncrypt(True, GuiCtrlRead($pw)) ExitLoop ElseIf $i = UBound($conName)-1 Then $selC = UBound($conName) _ArrayAdd($conName, UBound($conName) & "|" & GuiCtrlRead($cname)) _ArrayAdd($conStr, UBound($conStr) & "|" & GuiCtrlRead($cstring)) _ArrayAdd($conPW, UBound($conPW) & "|" & StringEncrypt(True, GuiCtrlRead($pw))) EndIf Next Else $conName[$selC][1] = GuiCtrlRead($cname) $conStr[$selC][1] = GuiCtrlRead($cstring) $conPW[$selC][1] = StringEncrypt(True, GuiCtrlRead($pw)) EndIf IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name", $conName) IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String", $conStr) IniWriteSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password", $conPW) GuiMod('SETTINGS-SELECT', $GUI_ENABLE) GuiMod('SETTINGS-ENTRY', $GUI_DISABLE) GuiMod('RELOAD-CON') ToolTip('') EndIf Case $canc ToolTip("Loading...") GuiMod('SETTINGS-SELECT', $GUI_ENABLE) GuiMod('SETTINGS-ENTRY', $GUI_DISABLE) GuiMod('RELOAD-CON') ToolTip('') Case $test $sqlCon = ObjCreate("ADODB.Connection") $sqlCon.Mode = 16 ; shared $sqlCon.CursorLocation = 3 ; client side cursor Local $connString = StringReplace(GuiCtrlRead($cstring), "*PW*", GuiCtrlRead($pw)) $sqlCon.Open($connString) If @error Then MsgBox(0, "Fail", "Failed to connect to the database") Else MsgBox(0, "Success", "Successfully connected to the database") EndIf Case $return $selConn = _ArraySearch($conName, $selC, 0, 0, 0, 0, 1, 1) IniWrite(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Selected", "Number", $selConn) GuiMod('SETTINGS', $GUI_HIDE) GuiMod('MAIN', $GUI_SHOW) Case $hSelAll _SelectAllTextInEdit() Case $GUI_EVENT_CLOSE Exit EndSwitch If $fResized Then GuiMod('RESIZE') $fResized = False EndIf If $guics[0] = True Then _Input_Check($cname) WEnd Func QuerySelect() Local $oldp = _ArraySearch($Qlist, $selQ[0], 0, 0, 0, 0, 1, 0) Local $old = GuiCtrlRead($query) If $Qlist[$oldp][1] <> $old Then $Qlist[$oldp][1] = $old $Qlist[$oldp][2] = True EndIf Local $newp = _ArraySearch($Qlist, $selQ[1], 0, 0, 0, 0, 1, 0) GUICtrlSetData($query, $Qlist[$newp][1]) $selQ[0] = $selQ[1] If $Qlist[$newp][2] = False Then GUICtrlSetData($qtab, " Query ") Else GUICtrlSetData($qtab, " Query (unsaved) ") EndIf EndFunc Func QueryLoad($reload = True) Local $qFiles = _FileListToArray(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\") Local $tQlist[UBound($qFiles)][3] Local $selT $tQlist[0][0] = $qFiles[0] $tQlist[0][1] = "" For $i = 1 To $tQlist[0][0] $tQlist[$i][0] = StringTrimRight($qFiles[$i], 4) If $reload Then $selT = _ArraySearch($Qlist, $tQlist[$i][0], 0, 0, 0, 0, 1, 0) If $selT > -1 Then $tQlist[$i][1] = $Qlist[$selT][1] $tQlist[$i][2] = $Qlist[$selT][2] Else $tQlist[$i][1] = FileRead(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $qFiles[$i]) $tQlist[$i][2] = False EndIf Else $tQlist[$i][1] = FileRead(@AppDataDir & "\SQL-Query-Tool\Saved-Queries\" & $qFiles[$i]) $tQlist[$i][2] = False EndIf $tQlist[0][1] &= StringTrimRight($qFiles[$i], 4) If $i < $tQlist[0][0] Then $tQlist[0][1] &= "|" Next $Qlist = $tQlist EndFunc Func ProcessQuery($input) Local $resultO = $sqlCon.Execute($input) If Not @error Then Local $resultT = "" Local $recordE = 0 Local $resultA[2][0] Local $i, $z While $recordE = 0 and IsObj($resultO) Redim $resultA[2][0] For $oField In $resultO.Fields Redim $resultA[2][UBound($resultA, 2)+1] $resultA[1][UBound($resultA, 2)-1] = $oField.Name Next ReDim $resultA[UBound($resultA)+1][UBound($resultA, 2)] While Not $resultO.EOF ReDim $resultA[UBound($resultA)+1][UBound($resultA, 2)] $i = 0 For $oField in $resultO.Fields $resultA[UBound($resultA)-1][$i] = StringStripWS(StringStripWS($oField.Value, 1), 2) $i += 1 Next $resultO.MoveNext WEnd For $i = 0 To UBound($resultA, 2)-1 $z = 0 For $x = 1 To UBound($resultA)-1 If StringLen($resultA[$x][$i]) > $z Then $z = StringLen($resultA[$x][$i]) Next $resultA[0][$i] = $z Next For $i = 0 To UBound($resultA, 2)-1 $z = "" For $x = 1 To $resultA[0][$i] + 4 $z &= "-" Next $resultA[2][$i] = $z Next For $x = 1 To UBound($resultA)-1 For $i = 0 To UBound($resultA, 2)-1 $resultT &= $resultA[$x][$i] For $z = StringLen($resultA[$x][$i]) To $resultA[0][$i] + 4 $resultT &= " " Next Next $resultT &= @CRLF Next $resultT &= @CRLF & "Total Lines Returned: " & UBound($resultA)-3 & @CRLF $resultT &= "__________________________________________________________" & @CRLF & @CRLF & @CRLF $resultO = $resultO.NextRecordset $recordE = @error WEnd $resultO.Close Else Local $resultT = $sqlErr EndIf Return $resultT EndFunc Func GuiMod($action, $toggle = 0) Switch $action Case 'RESIZE' If ($GUIs[2] < 266 or $GUIs[3] < 306) And $guics[0] = False Then WinMove($GUI, '', $GUIs[0], $GUIs[1], 276, 336) GUICtrlSetPos($tab, 5, 35, $GUIs[2] - 25, $GUIs[3] - 106) GUICtrlSetPos($query, 10, 60, $GUIs[2] - 36, $GUIs[3] - 136) GUICtrlSetPos($result, 10, 80, $GUIs[2] - 36, $GUIs[3] - 156) GUICtrlSetPos($result, 10, 80) Case 'MAIN' GUICtrlSetState($querysel, $toggle) GUICtrlSetState($queryopt, $toggle) GUICtrlSetState($tab, $toggle) GUICtrlSetState($query, $toggle) GUICtrlSetState($result, $toggle) GUICtrlSetState($status, $toggle) GUICtrlSetState($run, $toggle) GUICtrlSetState($settings, $toggle) Case 'SETTINGS' If $toggle = $GUI_SHOW Then GUISetStyle(BitOR($WS_MINIMIZEBOX, $WS_CAPTION, $WS_SYSMENU)) $GUIs = WinGetPos($GUI) $guics[0] = True $guics[1] = $GUIs[2] $guics[2] = $GUIs[3] $guics[3] = $GUIs[0] WinMove($GUI, '', 25, $GUIs[1], 750, 221) ElseIf $toggle = $GUI_HIDE Then GUISetStyle(BitOR($WS_MINIMIZEBOX, $WS_CAPTION, $WS_SYSMENU, $WS_SIZEBOX)) $guics[0] = False WinMove($GUI, '', $guics[3], Default, $guics[1], $guics[2]) ElseIf $toggle = $GUI_HIDE+1 Then $toggle -= 1 EndIf GUICtrlSetState($consel, $toggle) GUICtrlSetState($newc, $toggle) GUICtrlSetState($edic, $toggle) GUICtrlSetState($delc, $toggle) GUICtrlSetState($test, $toggle) GUICtrlSetState($return, $toggle) GUICtrlSetState($divider, $toggle) GUICtrlSetState($cnamel, $toggle) GUICtrlSetState($cname, $toggle) GUICtrlSetState($cstringl, $toggle) GUICtrlSetState($cstring, $toggle) GUICtrlSetState($pwl, $toggle) GUICtrlSetState($pw, $toggle) GUICtrlSetState($savc, $toggle) GUICtrlSetState($canc, $toggle) Case 'SETTINGS-SELECT' GUICtrlSetState($consel, $toggle) GUICtrlSetState($newc, $toggle) GUICtrlSetState($edic, $toggle) GUICtrlSetState($delc, $toggle) GUICtrlSetState($test, $toggle) GUICtrlSetState($return, $toggle) Case 'SETTINGS-ENTRY' GUICtrlSetState($cnamel, $toggle) GUICtrlSetState($cname, $toggle) GUICtrlSetState($cstringl, $toggle) GUICtrlSetState($cstring, $toggle) GUICtrlSetState($pwl, $toggle) GUICtrlSetState($pw, $toggle) GUICtrlSetState($savc, $toggle) GUICtrlSetState($canc, $toggle) Case 'RUNNING' GUICtrlSetState($querysel, $toggle) GUICtrlSetState($queryopt, $toggle) GUICtrlSetState($query, $toggle) GUICtrlSetState($tab, $toggle) GUICtrlSetState($result, $toggle) GUICtrlSetState($run, $toggle) GUICtrlSetState($settings, $toggle) Case 'RELOAD-CON' $conName = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Name") $conStr = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-String") $conPW = IniReadSection(@AppDataDir & "\SQL-Query-Tool\settings.ini", "Connection-Password") Local $sC = $conName _ArraySort($sC, 0, 1, 0, 1) GUICtrlSetData($consel, "") If $conName[$selC][1] <> "*!*BLANK*!*" Then GuiCtrlSetData($cname, $conName[$selC][1]) GuiCtrlSetData($cString, $conStr[$selC][1]) GuiCtrlSetData($pw, StringEncrypt(False, $conPW[$selC][1])) $selC = $conName[$selC][1] Else For $i = 1 To UBound($sC)-1 If $sC[$i][1] <> '*!*BLANK*!*' Then GuiCtrlSetData($cname, $sC[$i][1]) GuiCtrlSetData($cString, $conStr[$sC[$i][0]][1]) GuiCtrlSetData($pw, StringEncrypt(False, $conPW[$sC[$i][0]][1])) $selC = $sC[$i][1] ExitLoop EndIf Next EndIf For $i = 1 To UBound($sC)-1 If $sC[$i][1] <> '*!*BLANK*!*' Then GUICtrlSetData($consel, $sC[$i][1], $selC) Next EndSwitch EndFunc Func MY_WM_SIZE($hWnd, $Msg, $wParam, $lParam) $GUIs = WinGetPos($GUI) $fResized = True Return $GUI_RUNDEFMSG EndFunc ;==>MY_WM_SIZE Func StringEncrypt($bEncrypt, $sData, $sPassword = 'SQL') If $sData = "" Then Return '' _Crypt_Startup() ; Start the Crypt library. Local $sReturn = '' If $bEncrypt Then ; If the flag is set to True then encrypt, otherwise decrypt. $sReturn = _Crypt_EncryptData($sData, $sPassword, $CALG_RC4) Else $sReturn = BinaryToString(_Crypt_DecryptData($sData, $sPassword, $CALG_RC4)) EndIf _Crypt_Shutdown() ; Shutdown the Crypt library. Return $sReturn EndFunc ;==>StringEncrypt Func _Input_Check($hInput) Local $sText = GUICtrlRead($hInput) If StringRegExp($sText, "[^A-Za-z0-9 _]") Then GUICtrlSetData($hInput, StringRegExpReplace($sText, "[^A-Za-z0-9 _]", "")) EndFunc Func _SelectAllTextInEdit();will make select all text in any focused edit Local $theHandle = _WinAPI_GetFocus() Local $TheClass = _WinAPI_GetClassName($theHandle) If $TheClass = 'Edit' Then _GUICtrlEdit_SetSel($theHandle, 0, -1) EndFunc Func MyErrFunc() $HexNumber=hex($oMyError.number,8) $sqlErr = "We intercepted a COM Error !" & @CRLF & @CRLF & _ "err.description is: " & @TAB & $oMyError.description & @CRLF & _ "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _ "err.number is: " & @TAB & $HexNumber & @CRLF & _ "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _ "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _ "err.source is: " & @TAB & $oMyError.source & @CRLF & _ "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _ "err.helpcontext is: " & @TAB & $oMyError.helpcontext SetError(1) ; to check for after this function returns Endfunc
  3. Version 2.1.16 BETA

    3,439 downloads

    I want to present BETA Version of my ADO.au3 UDF. Support topic is here: http://www.autoitscript.com/forum/index.php?showtopic=180850 This UDF is modifed version of _sql.au3 UDF. For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH
  4. Only early days at this point, but I have been pondering such a program for a while. As good as calibre is (thank you Kovid Goyal), which is a great and wonderful ebook suite of tools and a fair database, it does have its limitations. One of which, is how it deals with multiple libraries, another is the views you get. CalibBrowser will seek to address those. What CalibBrowser is not going to be, is an editor for existing calibre libraries. That will be left up to calibre, which is very much needed still, and covers many aspects I will never look at. Unlike calibre, which is quite a complex program, CalibBrowser also seeks to be simple. It is mainly a viewer, at this point, but will later be able to create its own libraries. However, it does not and will not export them to calibre, especially as calibre employs a far different method and structure to what CalibBrowser will employ. When CalibBrowser starts, it looks for calibre executables and the main Calibre Library. Whatever isn't found, you get prompted for with a browse option. A calibre library, is a set of ebook folders (Author\Ebooks) and a database file, always named metadata.db, and which causes an issue when it comes to multiple libraries, but makes life a bit easier when reconstructing any corrupted libraries. However, there are better ways to deal with that, as my program will show. The metadata.db file is an SQL database, so I am having a learning curve right now, as I have only ever dealt with an SQL database previously, codewise, when I created my INItoSQL program some time last year, as an exercise to prove a point. At the moment, things are pretty basic, and not everything works 100%. Here is a screenshot, to give an idea of it, but keep in mind, I intend to expand the current GUI for other stuff I will be adding. Older Screenshots Gawd, I just noticed the '3|7' in the Book Input field (original screenshot). I was using that during troubleshooting for the multiple images scrolling and forgot to disable it ... not that it impacts anything. When it comes to maths, I struggle a bit, especially when tired. Right scrolling was easy, with a continuous loop, was easy to implement. Left scrolling was significantly harder for my poor brain ... until I realized I needed to see them as Min and Max. As you can see the program is usable, and all the buttons, aside from the Program Information one, work. You can even load different calibre libraries, and even reload after making changes to one with calibre. The calibre program does not need to be running, even to view an ebook in the Calibre Reader. The combo selector for a library and the ADD button are only temporarily placed where they are, until I expand the GUI. My intention at this point, is to add another five thumbnail images, directly below existing. Currently they aren't clickable, but I may add that. Here is another screenshot, of what you see when you click the larger Cover image. If you want to have a play with the program as is, then you will need to also get the 'sqlite3.dll' file from some online source. When CalibBrowser starts successfully with the selected calibre library, it copies its metadata.db file to a sub-folder of the program called 'Backups'. It also creates a sub-folder in that, based on the library name, to house it. That copied file, is the one the program uses, though it does not even edit that, and file modification is checked every time the program starts with a particular library, or when you Reload or select a library. If the original source file has been modified, then the program copy is overwritten. The Reload Database button does nothing, if there is no change detected, and reports such. Place the required 'sqlite3.dll' file in the CalibBrowser root folder. Download includes source files (sqlite3.dll excepted). Also required of course, is an install of calibre, plus some ebooks in a created library - Calibre Library is the default when you first add ebooks to calibre. The Mobile Read Forums, is a great source for all things ebook, and calibre can be found there in the E-Book Software section. CalibBrowser.zip - Upload 4 (previous downloads: 1 + 12 + 5) CalibBrowser (new).zip My apologies for the program being created in AutoIt v3.3.0.0. It is the first one I have done in a while, with an older AutoIt version. Basically my Win 7 Netbook, which has a current version of AutoIt, was busy and is always busy doing something, and not suitable for doing big projects for several reasons. My older but more powerful Win XP Laptop, has a better programming environment, better computer chair (most important for my knees etc), better external monitor (wider and newish), full size external keyboard & mouse, and a great suite of setup tools to assist me. I run several older versions of AutoIt already on my Laptop, but haven't yet determined what I need to adjust to add a current version of AutoIt to the mix. This also applies to my hugely beneficial Toolbar For Any program (one of those tools), which I constantly use with SciTE. At some point, when finished, I may update the program to the current AutoIt version ... especially as I believe I am now proficient enough to do so, having become quite familiar with it in the last year or so, making many programs with it. Enjoy!
  5. Hi Guys, Fine? I have this code and I use it to perform the query, however when I change the query to INSERT it is not working return error. #include <GUIConstants.au3> #include <MsgBoxConstants.au3> #include <Array.au3> Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") Example() Func Example()     Local $dbname = FileOpenDialog("Choose Access Database", @ScriptDir, "Access files (*.accdb)", 1)     If @error then Return SetError(@error, @extended, 0)     $adoCon = ObjCreate("ADODB.Connection")     $adoCon.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & $dbname & ";Uid=;Pwd=;")     $adoRs = ObjCreate("ADODB.Recordset")     GUICreate("listview items", 550, 250, 100, 200, -1, $WS_EX_ACCEPTFILES)     Local $idListview = GUICtrlCreateListView("Codigo    |Nome         |Valor  ", 10, 10, 520, 150) ;,$LVS_SORTDESCENDING     $queryInsert = INSERT INTO TABLENAME VALUES (''aaaaa'', ''bbbbbb'', ''cccccc'')     Local $aResult     With $adoRs         .CursorType = 2         .LockType = 3         .Open($queryInsert, $adoCon)         If @error Then             ; deal with Probable SQL error             Return SetError(1)         EndIf         If Not .EOF Then $aResult = .GetRows()         .Close()     EndWith     $adoRs = 0     _ArrayDisplay($aResult, 'UBound($aResult)=' & UBound($aResult))     For $iRow_idx = 0 To UBound($aResult) - 1         GUICtrlCreateListViewItem($aResult[$iRow_idx][0], $idListview)     Next     $adoCon.Close     GUISetState()     ; Loop until the user exits.     While 1         Switch GUIGetMsg()             Case $GUI_EVENT_CLOSE                 ExitLoop ;~             Case $idButton ;~                 MsgBox($MB_SYSTEMMODAL, "listview item", GUICtrlRead(GUICtrlRead($idListview)), 2)             Case $idListview                 MsgBox($MB_SYSTEMMODAL, "listview", "clicked=" & GUICtrlGetState($idListview), 2)         EndSwitch     WEnd EndFunc   ;==>Example ; User's COM error function. Will be called if COM error occurs Func _ErrFunc($oError)     ; Do anything here.     ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _             @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _             @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _             @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _             @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _             @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _             @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _             @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _             @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _             @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF) EndFunc   ;==>_ErrFunc Help, Please!
  6. 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!
  7. I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything. Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc
  8. ..let's say I store in a mysql column "[1,2,11,12,123]". How can I make s query to get all those rows that is 1, or is 12, etc ? I'm using mysqlnd 5.0.11-dev - 20120503 Any advise on what technique I should use is welcomed Thanks PS: ..I'm adding a comma to each end, as in [,1,2,11,12,123,] and will search with like %,12,% . Not pretty but I'm in a hurry and can't come up with a better solution =/
  9. Dear all, I already have a formula which will select doublicates, but sometimes there might be entries with additional information starting after "-", which I now want to trim away. Current formula is: Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (a.title like b.title and a.oid <> b.oid)" & $ActiveDisks_Filter & " AND (a.comment <> 'Delete' AND b.comment <> 'Delete') Order by a.title ASC, a.size DESC; testwise I want to add a simple solution like left, but afterwards I get an error but I do not know why. Select DISTINCT a.title, a.oid, a.size, a.disk from DB a, DB b where (left(a.title,15) like b.title and a.oid <> b.oid) Order by a.title ASC, a.size DESC; Error: near "(": syntax error: Finally I would like to insert intstr to search for "-" but currently I am not able to extend my current code =/ Thanks a lot for your help. EDIT: hey, I managed it, left was not working also it was somehow shown that the tool knowed the comment. I did it now with substr+instr (substr(a.title, 1, instr(a.title, ' - '))
  10. I have code that does a WMI SQL query to find all defined printers, and I want to parse the returned object in several places. However, after parsing it the first time, all other times fail to find any printer objects. Here is my test code: test() Func test() Local $oPrinters, $oPrinter, $err, $cnt, $oP, $query $query = "SELECT * FROM Win32_Printer" $oPrinters = doQuery($query) $err = @error LogMsg("+++: $err = " & $err & ", isObj($oPrinters) = " & IsObj($oPrinters)) If ($err == 0) Then LogMsg("FIRST LOOP") ; <=== FIRST LOOP $cnt = 0 $oP = $oPrinters LogMsg("+++: isObj($oP) = " & IsObj($oP)) For $oPrinter In $oP $cnt += 1 LogMsg("+++: isObj($oPrinter): " & IsObj($oPrinter) & ", $oPrinter.Name ==>" & $oPrinter.Name & "<==") Next LogMsg("+++: Found " & $cnt & " printers") LogMsg("SECOND LOOP") ; <== SECOND LOOP $cnt = 0 $oP = $oPrinters LogMsg("+++: isObj($oP) = " & IsObj($oP)) For $oPrinter In $oP $cnt += 1 LogMsg("+++: isObj($oPrinter): " & IsObj($oPrinter) & ", $oPrinter.Name ==>" & $oPrinter.Name & "<==") Next LogMsg("+++: Found " & $cnt & " printers") EndIf EndFunc ;==>test Func doQuery($sQuery, $lnum = @ScriptLineNumber) #forceref $lnum LogMsg("+++:" & $lnum & ": doQuery(" & '"' & $sQuery & '"' & ") entered") Local $oWMIService, $oResults, $errstr Local $wbemFlags = BitOR(0x20, 0x10) ; $wbemFlagReturnImmediately and wbemFlagForwardOnly $oWMIService = ObjGet("winmgmts:\\" & "localhost" & "\root\CIMV2") If (IsObj($oWMIService)) Then $oResults = $oWMIService.ExecQuery($sQuery, "WQL", $wbemFlags) If (IsObj($oResults)) Then LogMsg("+++: doQuery() returns @error = 0, Good: returning the object") Return (SetError(0, 0, $oResults)) ;;; Good: return the object Else $errstr = "" _ & "WMI Query failed." & @CRLF _ & "This is the query:" & @CRLF _ & " " & $sQuery LogMsg("+++: ====>" & $errstr & "<===") LogMsg("+++: doQuery() returns @error = 1") Return (SetError(1, 0, $errstr)) ; Error: Query faled EndIf Else $errstr = "" _ & "WMI Output" & @CRLF _ & "No WMI Objects Found for class: " & @CRLF _ & "Win32_PrinterDriver" & @CRLF _ & "using this query:" & @CRLF _ & " " & $sQuery LogMsg("+++: ====>" & $errstr & "<===") MsgBox(0, "ERROR", $errstr) ; Error: Cannot get $oWMIService object Exit (1) EndIf EndFunc ;==>doQuery Func LogMsg($msg, $lnum = @ScriptLineNumber) ConsoleWrite("+++:" & $lnum & ": " & $msg & @CRLF) EndFunc ;==>LogMsg Parsing the returned $oPrinters object shows 5 printers: +++:15: FIRST LOOP +++:18: +++: isObj($oP) = 1 +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Microsoft XPS Document Writer<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Microsoft Office Document Image Writer<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Fax<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Canon MG7100 series Printer WS<== +++:22: +++: isObj($oPrinter): 1, $oPrinter.Name ==>Canon MG6100 series Printer WS<== +++:24: +++: Found 5 printers Parsing it again, shows no printers: +++:26: SECOND LOOP +++:29: +++: isObj($oP) = 1 +++:35: +++: Found 0 printers
  11. Hello guys ! I'm stuck in something stupid (I guess), but since I've no clue on how to solve it, here I come I'm trying to collect the result of the query "select @@hostname" on MSSQL Server, but it doesn't work ... and I'm sure I'm connected to the database (I've successfully seen the database treeview). #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_UseX64=n #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #include<_sql.au3> #include<array.au3> Local $aData,$iRows,$iColumns ;Server ID and credentials for tCards Global $ServerAddressT = "10.200.88.1" Global $ServerUserNameT = "user" Global $ServerPasswordT = "password" Global $DatabaseNameT = "DataBASE" ;Connect to DB _SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error $OADODB = _SQL_Startup() If $OADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg()) If _sql_Connect(-1, $ServerAddressT, $DatabaseNameT, $ServerUserNameT, $ServerPasswordT) = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error 1", _SQL_GetErrMsg()) _SQL_Close() Exit Else If _SQL_Execute(-1,"select @@SERVERNAME") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg()) Else _ArrayDisplay(_SQL_GetTable(-1,"select @@SERVERNAME",$aData,$iRows,$iColumns)) ;~ $toto = _SQL_Execute(-1,"select @@SERVERNAME") ;~ _ArrayDisplay($toto) EndIf EndIf
  12. I have an Autoit-based client GUI that uses a single MS SQL Server database. I have no problem connecting to and executing queries against this database from my AutoIT code, with the below exception: One of the database's stored procedures I need to execute requires a parameter that is a table. I've set up the appropriate table_type on the database and everything works fine on the database itself when I execute the stored proc with a table variable for the parameter (no AutoIT involved). The problem is that I can't figure out how to pass a table parameter from AutoIT, assuming it's even possible. The code below shows the two methods I've tried, both methods return a RecordSet object that is at EOF. #include <Array.au3> Opt("MustDeclareVars", 0) $sDBSrv = "ITSQL01.domain.com" $sDBName = "INVDEV" $sADOName = "ADODB.Connection" $oSQLConn = ObjCreate($sADOName) $sConnStr = "Driver={SQL Server};Server=" & $sDBSrv & ";Database=" & $sDBName & ";Trusted_Connection=yes;" $oSQLConn.Open ($sConnStr) $rsoFacFriendly = $oSQLConn.Execute("SELECT TOP 5 FriendlyName FROM Facility") If Not $rsoFacFriendly.EOF Then $aFacFriendly = $rsoFacFriendly.GetRows() _ArrayDisplay($aFacFriendly) $rsoSPResults = $oSQLConn.Execute("EXEC spTableParameterPassTest " & $aFacFriendly) Select Case $rsoSPResults.EOF = False MsgBox(0, "sp exec1", "not end of file") $aSPResults = $rsoSPResults.GetRows() _ArrayDisplay($aSPResults) Case IsObj($rsoSPResults) = 0 MsgBox(0, "sp exec1", "rso isn't even an object") Case $rsoSPResults.EOF = True MsgBox(0, "sp exec1", "At end of file") Case Else MsgBox(0, "sp exec1", "Something else happened") EndSelect $rsoSPResults = 0 $rsoSPResults = $oSQLConn.Execute("EXEC spTableParameterPassTest " & $rsoFacFriendly) Select Case $rsoSPResults.EOF = False MsgBox(0, "sp exec2", "not end of file") $aSPResults = $rsoSPResults.GetRows() _ArrayDisplay($aSPResults) Case IsObj($rsoSPResults) = 0 MsgBox(0, "sp exec2", "rso isn't even an object") Case $rsoSPResults.EOF = True MsgBox(0, "sp exec2", "At end of file") Case Else MsgBox(0, "sp exec2", "Something else happened") EndSelect $rsoSPResults = 0 $rsoFacFriendly = 0 $oSQLConn.Close $oSQLConn = 0 Exit
  13. 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
  14. Hi, I want to click a link by the element ID through IEGetObjById. <!DOCTYPE html> <html> <body> <button type="button" id="Random-1-ID" onclick="alert('Hello world!')"></button> </body> </html> I intend to click the button with ID"Random-1-ID". But on every refresh the ID changes to next number like "Random-2-ID" "Random-3-ID" The code i which i wrote for this function is #include <IE.au3> #include <MsgBoxConstants.au3> Local $oIE = _IECreate("I:\Documents\1. Work\Automation\My codes\Collections\11. Clicking button by Value and ID\button.html") Local $oDiv = _IEGetObjById($oIE, "Random-1-ID") _IEAction($oDiv, "click") _IELoadWait($oIE) So can anyone help me to alter this code like it clicks for every ID in format "Random-%-ID"
  15. I hope my title is good enough. I'm using the ADO UDF and I have question regarding editing SQL records with this UDF. The owner of the UDF suggested an idea, but maybe there is another trix.
  16. DB1: CREATE TABLE [dbo].[Item]( [ItemID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED ( [ItemID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Account]( [Index] [int] IDENTITY(1,1) NOT NULL, [AccountID] [nchar](10) NOT NULL, [AccountName] [int] NOT NULL, [ItemList] [int] NOT NULL, ) ON [PRIMARY] GO CREATE TABLE [dbo].[Money]( [AccountID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, CONSTRAINT [PK_Money] PRIMARY KEY CLUSTERED ( [AccountID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO DB2: CREATE TABLE [dbo].[Item]( [ItemID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, [ItemName] [bigint] NOT NULL, [MoneyType] [bigint] NOT NULL, CONSTRAINT [Item] PRIMARY KEY CLUSTERED ( [ItemID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Account]( [Index] [int] IDENTITY(1,1) NOT NULL, [AccountID] [nchar](10) NOT NULL, [AccountName] [int] NOT NULL, [ItemList] [int] NOT NULL, ) ON [PRIMARY] GO CREATE TABLE [dbo].[Money]( [AccountID] [nchar](10) NOT NULL, [Money] [bigint] NOT NULL, [MoneyType] [bigint] NOT NULL, CONSTRAINT [Money] PRIMARY KEY CLUSTERED ( [AccountID] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO Compare and merge database. alter table [Item] add [ItemName] bigint not null default(0) alter table [Item] add [MoneyType] bigint not null default(0) alter table [Money] add [MoneyType] bigint not null default(0) Please help automate code AutoIt to generation new code for large sql file. Thanks
  17. Hi guys, This post was originally going to be a question on how to fix this issue but as I ended up figuring it out I thought I'd post it here for others that have the same issue. So you've downloaded and extracted the latest version of the SQLite dll files etc into the same directory as your SQLite script but it's failing at _SQLite_Startup()? What you need to do, that I couldn't see anywhere in the documentation, to fix the issue is rename the dll files from (for example) "sqlite3_301500000.dll" to "sqlite3.dll" and "sqlite3_x64_301500000.dll" to "sqlite3_x64.dll". Fixed my issues instantly! Hope it can help others too. Cheers.
  18. 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!
  19. Good morning all, I have a question regarding a situation im in... I have an array with information from sql query, how can i send this information to a listview ? $citeste_daune = "SELECT `id_dauna`,`data_incident`,`sala`,`autor`,`suma` FROM `daune` WHERE `status`=1;" $sa_citit_daune = _query($sqlinstance, $citeste_daune) Global $aresult[10001][5] = [[10000, 5]] Global $iindex = 0 With $sa_citit_daune While NOT .eof $aresult[$iindex][0] = .fields("id_dauna").value $aresult[$iindex][1] = .fields("data_incident").value $aresult[$iindex][2] = .fields("sala").value $aresult[$iindex][3] = .fields("autor").value $aresult[$iindex][4] = .fields("suma").value $iindex = $iindex + 1 .movenext WEnd EndWith ReDim $aresult[$iindex][5] $aresult[0][0] = $iindex - 1 _ArrayDisplay($aresult) GUICtrlCreateListViewItem(_ArrayToString($aresult), $lista_daune_active) This is what i see when i execure _ArrayDisplay($aresult)
  20. Hello guys, maybe some of you can help me. I manage to connect with sql database and use a variable to search for different things when i do the select from database. My problem is that i'm reading the database with an MsgBox to see the values. But sometimes for the thing i'm filtering the dabase for it shows me different values and i only one to select one of it and move forward with my script. Is there any chance to read the database...view it...and in front of each value to have a thick box to select with value i want for the line. After i select the the line value i want to stock the values from each column in different variables and then i want to send them as values to write in another database. Is this possible? Hope any of you can help me in this matter Apreciate guys.
  21. Using the MSSQL.udf I asked this question once, but haven't had a chance to reply to my old post so posting a new one instead of bouncing the old post. I'm trying to retrieve data from my database in Microsoft SQL Server 2014. When I tried the code below; empty message box return and no error approaches. $Connection = _MSSQL_Con($ServerAddress, $ServerUsername, $ServerPassword, $ServerDBName) $Testquery = _MSSQL_Query($Connection, "SELECT User_ID From Banker where Manager like '%Jason%'") msgbox(0, "Test", $Testquery) exit
  22. For the SQLite developers... New version 1.0.0.6 This is a Report Generator for SQLite Database. The script supports up to two dynamic parameters per report, see the 'SQLite Reports.ini' file for more details. Single Date, period, string or number input are possible I provide a full functional example using the Chinook Demo Database (http://chinookdatabase.codeplex.com), so everyone can test it. All required files are contained in the zip file. You can download the zipfile in the link hereunder. I created 20 different reports, from which 4 are system reports and one is not linked to any table. Features: Up to 2 dynamic Parameters via input dialog box per reportMulti-line fields will display in a separate Window when you click on the cell containing multiline data (which cannot be displayed in a listview, if you click on a normal cell, the window will disapearDouble click on a row and a new Window will display the row vertically, usefull for rows with many columns.Export to ExcelFast, even with several thousands of rows (see Track report)SQL that do not deliver data (no rows, no columns) will display a popup message instead (for example using the VACUUM command)Of course you have to know SQL but I guess that everyone who programs SQLite will, right? The SQL scripts are stored in the ini file, where 3 sections ‘Titles, SQL and Parameters’ contain all the elements required for a report. For simplicity, I decided to store the SQL script in the ini file as a single line. The drawback is that the SQL is not easy to read or to maintain, but look at the example ' Invoice with details' where I join 8 tables. It's absolutely functional. Read the [sql readme] section for more details about dynamic parameters. I think its usage is pretty obvious. Know issues: When compiled as 64bit, double click does not function in the main menu, I don't know why but I cannot get this fixed. If anyone knows, I would be pleased to get some help on this. All the other functions, including the double click in the ListView do work. The script will use SQLite3.dll or SQLite3_x64.dll if compiled as a x64 App. The script is a simplified version of my multi-platform Database Report Generator using Active-X DB connection. This SQLite report script (1600 lines) has very basic features compared to the latter (for example it fully integrates the ExcelChart UDF developed by water) but it has 13000 lines and is not provided has open source (sorry L, an early version was released on this forum in 2009 but for Oracle only) I hope this one will be useful for you, I had some fun with it, took me 3 evenings to do the job. Enjoy. GreenCan Updates 1.0.0.6: Bug fixes (thanks jpm)Better GUI windows managementExcel export optimized_COMError implementedFull package: http://users.telenet.be/GreenCan/AutoIt/SQLite_Reports_1.0.0.6.zip (If you downloaded the full package of the previous version, you don't have to download it again) SQLite Reports 1.0.0.6.au3 SQLite Reports.ini : [General] ;Database=Chinook_Sqlite.sqlite Database=Chinook_Sqlite_AutoIncrementPKs.sqlite DateFormat=YYYY-MM-DD [Titles] Report=Album Report=sqlite_sequence (shows number of records for each table) Report=Artist Report=Customer Report=All Employees Report=Employees birthday - period Report=Employees birthday - Name contains Report=Genre Report=Invoice Report=Invoice - amount between Report=Track Report=MediaType Report=Invoice with details - one invoice selection Report=Playlist Report=PlaylistTrack Report=Non-Database - Date formats examples Report=System - Tables and fields (sqlite_master) Report=System - List Tables of DataBase (sqlite_master) Report=System - SQLite version Report=System - Database cleanup (Vacuum) [SQL] Report=SELECT AlbumId, Title, ArtistId FROM Album Report=SELECT name,seq FROM sqlite_sequence Report=SELECT ArtistId, Name FROM Artist ORDER BY Name Report=SELECT CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId FROM Customer ORDER BY CustomerId Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, BirthDate, HireDate, Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, date(BirthDate) as BirthDate, date(HireDate), Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee WHERE BirthDate between '%d1%' AND '%d2%' Report=SELECT EmployeeId, LastName, FirstName, Title, ReportsTo, date(BirthDate) as BirthDate, date(HireDate), Address, City, State, Country, PostalCode, Phone, Fax, Email FROM Employee WHERE LastName || ' ' || FirstName like '%%1%%' Report=SELECT GenreId, Name FROM Genre Report=SELECT InvoiceId, CustomerId, InvoiceDate, BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total FROM Invoice Report=SELECT InvoiceId, CustomerId, Date(InvoiceDate), BillingAddress, BillingCity, BillingState, BillingCountry, BillingPostalCode, Total FROM Invoice WHERE Total between %1% AND %2% Report=SELECT TrackId, Name, AlbumId, MediaTypeId, GenreId, Composer, Milliseconds, Bytes, UnitPrice FROM Track Report=SELECT MediaTypeId, Name FROM MediaType Report=SELECT Invoice.InvoiceId, Date(Invoice.InvoiceDate) as "Invoice Date", Customer.FirstName, Customer.LastName, Invoice.BillingAddress, Invoice.BillingCity, Invoice.BillingState, Invoice.BillingCountry, Invoice.BillingPostalCode, Track.Name as "Track Name", Album.Title as "Album", MediaType.Name as "Media", Genre.Name as "Genre", Artist.Name as "Artist", Track.Composer, InvoiceLine.UnitPrice, InvoiceLine.Quantity, Invoice.Total FROM Invoice INNER JOIN InvoiceLine ON Invoice.InvoiceId = InvoiceLine.InvoiceId INNER JOIN Customer ON Invoice.CustomerId = Customer.CustomerId INNER JOIN Track ON InvoiceLine.TrackId = Track.TrackId INNER JOIN Album ON Track.AlbumId = Album.AlbumId INNER JOIN Artist ON Album.ArtistId = Artist.ArtistId INNER JOIN Genre ON Track.GenreId = Genre.GenreId INNER JOIN MediaType ON Track.MediaTypeId = MediaType.MediaTypeId WHERE Invoice.InvoiceId = %1% Report=SELECT * FROM Playlist Report=SELECT * FROM PlaylistTrack Report=SELECT date('now') as "today", date('now','start of month','+1 month','-1 day') as "last day current month", datetime(1092941466, 'unixepoch') as "unix timestamp", datetime(1092941466, 'unixepoch', 'localtime') as "unix timestamp, compensate for, local timezone", strftime('%s','now') as "current unix timestamp", julianday('now') - julianday('1776-07-04') as "days since US Independence", strftime('%s','now') - strftime('%s','2004-01-01 02:34:56') as "Time elapsed", date('now','start of year','+9 months','weekday 2') as "first Tuesday in October", (julianday('now') - 2440587.5)*86400.0 Report=select * from sqlite_master Report=SELECT name FROM sqlite_master WHERE type='table' Report=SELECT sqlite_version() as "sqlite version" --, sqlite_source_id() as "sqlite source_id" Report=VACUUM [Parameters] Report= Report= Report= Report= Report= Report= Report=Name contains|string will match any position in FirstName or LastName Report= Report= Report=Total between|For fraction use a dot (eg: 5.95),and|For fraction use a dot (eg: 5.95) Report= Report= Report=Invoice ID| Report= Report= Report= Report= Report= Report= Report=
  23. Referring to a question posted here, this is a possible SQL query that should do: supposed you have an sqlite table named 'multimedia' with a column named 'sha1_hash', and you want to see only rows with the 'sha1_hash' value that appears more than one time within the table, you could use an SQL query like this: SELECT sha1_hash, COUNT(sha1_hash) AS sha1_sum FROM multimedia GROUP BY sha1_hash HAVING sha1_sum>1 ORDER BY sha1_sum DESC; this query should show only records with the value of sha1_hash that appears more times also in other records, showing it only one time, with a column indicating how many occurrences are present, ordered from the most recurring to the least recurring one. I don't know if this query can be simplified a bit...?, .... if there is a simpler way I would like some hint, Thanks
  24. Currently I'm working on MS SQL >> PostgreSQL migration. Here are some of interesting Video tutorials about PostgreSQL
  25. Hi I ask for generic advice on what field format is best to use to store dates in a database (SQLite). if is a better choice to stored separately year, month and day into 3 separate fields or use one field for a complete date-time group? I have to store information of this kind: a person is assigned to a job for a certain day (one day only) or also a person is assigned to a job from a day to another day (a range of days) then i have to query the database for a certain day so to know who is working to a certain job in a specific day. Well, in the case of ranges of days (for example Mr. Bean works to JOB1 from May/15 to May/20) I have to store one record for each day of the range (6 record in this case) or use only one record with both dates in 2 fields of the same record? the SQL query should 'ask' who is working on a specific day (even for days in the middle of the ranges). My doubt is, what's the best way to store ranges of dates (as in the above example), so to be facilitated in the retrieval of those informations. I'm afraid I'm not been clear, even if I did my best to be, anyway .... any suggestion is welcome.. Thank You
×
×
  • Create New...