Jump to content

Search the Community

Showing results for tags 'sqlite'.



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
  • AutoIt

Calendars

  • Community Calendar

Found 53 results

  1. In my recent project I'm downloading a bunch of data, so I decided to store it in a SQLite database. NOTE: I'm using sqlite3_x64.dll Everything is working just fine but I'm struggling with getting the Median value. SQLite has an Average function but not a Median one. I googled but all of the provided solutions are way above my pay-grade. After some more searching I found 'extension-functions.c' on the SQLite site where Median is included. After almost an hour of struggling I was able to successfully compile it into a DLL. So I downloaded @jchd's SQLiteExtLoad.au3 as seen here: But I'm getting these errors: "Path\SQLiteExtLoad.au3"(21,40) : warning: $g_hDll_SQLite: possibly used before declaration. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "Path\SQLiteExtLoad.au3"(21,40) : error: $g_hDll_SQLite: undeclared global variable. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ So I tried, copying the function to my file and changing the DLL variable ($g_hDll_SQLite) directly to the DLL location (C:\...\.. .dll), but now this error fires If __SQLite_hChk($hConn, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE) To be honest, I don't know what to use as the $hConn - handle of connection. I would appreciate any help, be it getting the median using SQLite queries or getting the DLL extension loaded using AutoIt. Thanks, S. EDIT: well, I suspect the $hConn variable refers to the return value of the _SQLite_Open function. Well, at least now _SQLite_EnableExtensions doesn't give errors. Now I run into problems with _SQLite_LoadExtension, which gives error -1, and extended 1. Apparently the 1 constant is a generic error where other error do not apply. BTW, anybody knows whether I need to compile the extension DLL "into" x64 when I use a x64 SQLite? That might be the problem... EDIT2: I recompiled the dll and tried it using the SQLite3.exe and it works, so I'm confident the extension DLL has been created correctly
  2. I have been testing AutoIt 3.3.14.3 with SQLite, and the Help File examples. The examples that I have tested are throwing errors or not doing anything. I have sqlite3.dll, sqlite3_x64.dll, and sqlite3.exe in the directories with the testing script. I am copying the examples directly from the help file into a test script for testing with no edits. The _SQLite_GetTable2d example is returning an error ("Library used incorrectly") with each _SQLite_Exec command. The _SQLite_FastEncode example returns an empty dialog box. The _SQLite_Exec example only prints out the SQLite version, and nothing else in the SciTE console. Currently, I'm still searching for what is causing this issue. I'm on Windows 7 Enterprise 64-bit. Is anyone else having this issue? Adam
  3. Greetings, I have SQLite setup within my AutoIT program...I'm trying to accomplish what should be a relatively simple task. I want to be able to return an array of 'table' names for an established database...I believe this might be possible using the '_SQLite_SQLiteExe' command...since it seems to be able to access SQLite schemas...? The ".tables" command is one of them...if I am not mistaken that command returns a list of all table names in the active database. I am attempting the following: #include <SQLite.au3> #include <SQLite.dll.au3> Global $hDb, $sIn, $sOut ... $sIn = ".tables" & @CRLF _SQLite_SQLiteExe($hDb, $sIn, $sOut) if @error == 0 Then ;Show Table (using SQLite3.dll) Else if @error == 2 Then ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF) Else ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF) EndIf ;@error is "2"...OR NOT... EndIf ;@error is "0"...OR NOT... ... The error being thrown is "ERROR: Sqlite3.exe file not found" ... Am I required to have the Sqlite3.exe installed in my directory (i.e. @ScriptsDir)...??? I do not have it in there at present because I did not believe it was necessary with the 'include' calls to "SQLite.au3" and "SQLite.dll.au3"...any advice appreciated. Thanks in advance. Regards
  4. Good evening guys I am working on a little project, in which I have to retrieve 8000+ rows of data from a table, from a SQLite database, to populate a combobox This is what I tried 'til now, but it still takes about 12 seconds to populate the combobox. _SQLite_Exec($objDatabase, "BEGIN TRANSACTION;") If _SQLite_GetTable($objDatabase, "SELECT DISTINCT Comune FROM LISTA_COMUNI;", $arrRisultatoQuery, $intRighe, $intColonne) = $SQLITE_OK Then For $i = 2 To UBound($arrRisultatoQuery) - 1 If $i < UBound($arrRisultatoQuery) - 1 Then GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i] & "|") Else GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i]) EndIf Next _SQLite_Exec($objDatabase, "COMMIT;") Are there any other solution to retrieve 8000+ records from a SQLite database? Thank you very much
  5. Good morning community! I am working on a script which read from a text file ( .txt ) and should import all the content in a SQLite3 DB, in order to execute some queries that should be difficult to execute on a text file. So, I was looking for something very very fast, because the file could be very large ( I don't know exaclty how much can became big, but I know a lot of rows, it's a log file ... ) I found the "Import method", but I don't know If I can implement it in a query ( @jchd, it's your turn! ) Do you know some methods that I can implement in my script to have a very very fast import of thousands and thousands rows in a SQLite3 DB? Thanks a lot Francesco
  6. Good morning guys I was trying to use again SQLite in AutoIt, and I've again an issue: I can't startup SQLite... Can you please show me the way to set the enviornment for a SQLIte usage in AutoIt? Step by step, I'll follow your suggestion(s). Thanks for everyone will reply! Francesco
  7. Good evening everyone I'm working on this little project for a week, and, what I'm trying to do could be useful for many users as well... I'm trying to do a "Report Generator", which reads the data that have to report from a text file (.txt) formatted with this pattern; Data1;Data2;Data3;Data4;Data5;; YES, there are 2 semi-colon at the end of the line. In detail, Data1 is a date/time stamp with this format: YYYY/MM/DD HH:MM:SS ; When the script starts, the user is prompted to choose 2 dates which I'll call as: Report_Date_Start; Report_Date_Start. So, the report, should cover all dates between Report_Date_Start AND Report_Date_End. And, already at this point, I don't know how to do the query... How can I say to the script: SELECT * FROM (.txt) WHERE Data1 BETWEEN Report_Date_Start AND Report_Date_End; ? I thought that I could do a _DateDiff, but if the difference between the two dates is months and not days, how can I do the trick? Should I make a Switch...Case with the _DateDiff() and see then calculate all the dates between Report_Date_Start AND Report_Date_End... But then, how can I compare the dates in the file with all the dates between Report_Date_Start AND Report_Date_End? I'm going crazy, I know... I've already made a "Export Tool", which exports the content of the .txt file in a .db, managed with SQLite... I mean, there I could easily do a query like I did above the thread, but, this "export", for 1080 rows, takes 28 seconds to be done. And, 1080 rows are daily rows that are added every day in the .txt file, so, in a week, the file could be easily 7000+ rows, which means that the "export" would take 3 minutes to be done... And we can go over and over... I'll post just for be "complete" what I've done about the export, so, maybe, someone could say how to improve it in terms of efficency... Local $aContenutoFileAuditReport = "" _FileReadToArray($sFileAudit_Report, $aContenutoFileAuditReport) If(IsArray($aContenutoFileAuditReport) And Not @error) Then Local $aContenutoFileAuditReport_Splitted = "" Local $sQuery = "" Local $hInizioConteggio = TimerInit() For $i = 1 To UBound($aContenutoFileAuditReport) - 1 $aContenutoFileAuditReport_Splitted = StringSplit($aContenutoFileAuditReport[$i], ";") $sQuery = "INSERT INTO FileDB_Report(DATESTAMP, TIMESTAMP, USER_ID, OBJECT_ID, DESCRIPTION, COMMENT) " & _ "VALUES(" & _ _SQLite_FastEscape($aContenutoFileAuditReport_Splitted[0]) & "," & _ _SQLite_FastEscape($aContenutoFileAuditReport_Splitted[1]) & "," & _ _SQLite_FastEscape($aContenutoFileAuditReport_Splitted[3]) & "," & _ _SQLite_FastEscape($aContenutoFileAuditReport_Splitted[4]) & "," & _ _SQLite_FastEscape($aContenutoFileAuditReport_Splitted[5]) & "," & _ _SQLite_FastEscape($aContenutoFileAuditReport_Splitted[6]) & ");" If(_SQLite_Exec($hFileDB_Report, $sQuery) <> $SQLITE_OK) Then ConsoleWrite("Errore durante l'esecuzione della query #" & $i & @CRLF) Else ConsoleWrite("Query eseguita correttamente #" & $i & @CRLF) EndIf Next ConsoleWrite("Esportazione completata in: " & Round(TimerDiff($hInizioConteggio)/1000, 0) & " secondi") Else MsgBox($MB_ICONERROR, "Errore!", "Errore durante la lettura del file nell'array." & @CRLF & "Errore: " & @error) EndIf I know that I can't do queries from a .txt file... [19:18] I've been writing this post from 18:40 maybe... By the way, if @jchd or someone else could tell me if I can import a formatted .txt file in SQLite and then, do queries on the DB, I'd be very happy for that... About the report in PDF, I'm talking with @taietel in order to know how to create a PDF. I hope someone will help me Sorry for the "long" list of questions... Thank you for everything you've done for me I have to say that this is the community of programming language that I've loved most! By the way, I'll be back tomorrow in the morning ( ~ 9:15 a.m. Italian time ), so, excuse me if I can't answer before that time. Hope you guys have a wonderful day/night. Thanks again Francesco
  8. Hi I need help with the code bellow, my goal is to make faster sqlite queries to Sqlite because _SQLite_GetTable2d seams to be slow on big tables. Thank you : https://www.autoitscript.com/forum/topic/182469-using-controls-to-edit-cells-in-a-listview/ Well my code is faster but a I have 2 issues : 1 Memory leaks 2 Listview ( virtual) a little slow when scrolling (column headers delay ) Please help I am not a WinApi specialist. (The hugetable its actuality much bigger I'm limited by upload size) Thank You. #comments-start C++ Source of Dll #include <vector> #include <string> #include <stdio.h> #include <string.h> #include "sqlite3.h" using namespace std; extern "C" { // A function adding two integers and returning the result char * SqliteSelect(char * path ,char * sSql , char * rowsep, char * tabsep ) { std::string rez=""; bool once=true; sqlite3 *db; if ( sqlite3_open_v2(path, &db,SQLITE_OPEN_READONLY, NULL) == SQLITE_OK) { sqlite3_stmt *stmt; if (sqlite3_prepare_v2(db, sSql, -1, &stmt, 0) == SQLITE_OK) { int nCols = sqlite3_column_count(stmt); while (sqlite3_step(stmt) == SQLITE_ROW) { for (int nCol = 0; nCol < nCols; nCol++) { if (once==true) { rez+=(char *) sqlite3_column_name(stmt, nCol); if (nCol < nCols-1 ) { rez+=tabsep; } else { rez+=rowsep; once=false; // sqlite3_reset(stmt); nCol=0; } } if (once==false){ if ( sqlite3_column_text(stmt, nCol)!=nullptr) { rez+=(char *) sqlite3_column_text(stmt, nCol); } if (nCol < nCols-1 ) rez+=tabsep; } } rez+=rowsep; } } else { return (char *)sqlite3_errmsg(db); sqlite3_close(db); } sqlite3_close(db); } char *cstr = new char[rez.length() + 1]; strcpy(cstr, rez.c_str()); return cstr; } char * SqliteExec(char * path ,char * sSql ) { std::string rez="Error: "; sqlite3 *db; char *error; if ( sqlite3_open_v2(path, &db,SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK) { sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL,&error); if( sqlite3_exec(db, sSql, NULL, NULL, &error) != SQLITE_OK ) { rez+= (char *)sqlite3_errmsg(db); sqlite3_exec(db, "ROLLBACK", NULL, NULL, &error); sqlite3_close(db); } sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &error); sqlite3_close(db); if (rez=="Error: " ) { rez="OK"; } char *cstr = new char[rez.length() + 1]; strcpy(cstr, rez.c_str()); return cstr; } } } #comments-end #include <GuiListView.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <WinAPIvkeysConstants.au3> #include <WinAPIShellEx.au3> #include <GuiEdit.au3> Opt( "MustDeclareVars", 1 ) Global $hListView, $iItem = -1, $iSubItem = 0, $aRect Global $idComboOpen, $idComboClose, $bComboOpen = False, $bComboDoNotOpen = False Global $bEditEscape = True, $bEditUpDown = False, $bListboxOpen = False Global $bComboOpenOnEnter = True Global $bComboOpenOnSpace = False Global $bComboOpenOnDoubleClick = True Global $bListboxAcceptClickEnter = True Global $hEdit ;Global $Table Func __SQLite_StringToUtf8Struct($sString) Local $aResult = DllCall("kernel32.dll", "int", "WideCharToMultiByte", "uint", 65001, "dword", 0, "wstr", $sString, "int", -1, _ "ptr", 0, "int", 0, "ptr", 0, "ptr", 0) If @error Then Return SetError(1, @error, "") ; DllCall error Local $tText = DllStructCreate("char[" & $aResult[0] & "]") $aResult = DllCall("kernel32.dll", "int", "WideCharToMultiByte", "uint", 65001, "dword", 0, "wstr", $sString, "int", -1, _ "struct*", $tText, "int", $aResult[0], "ptr", 0, "ptr", 0) If @error Then Return SetError(2, @error, "") ; DllCall error Return $tText EndFunc ;==>__SQLite_StringToUtf8Struct Global $Headers ,$RowsArray Global $TabSep =@TAB Example() Func Compare_Headers($ArrayIni,$Array_) If UBound($ArrayIni)<> UBound($Array_) Then Return 1 EndIf For $R=0 to UBound($ArrayIni)-1 if $ArrayIni[$R] <> $Array_[$R] Then Return 1 EndIf Next Return 0 EndFunc #include <File.au3> Func nCheckDB( $sDBname , $SSQL , $idListView ) Local $iniH =$Headers; GUICtrlSendMsg( $idListView, $LVM_SETITEMCOUNT,0, 0) Local $RowSep =@LF Local $tFilename = __SQLite_StringToUtf8Struct($sDBname) Local $tSsql = __SQLite_StringToUtf8Struct($SSQL ) Local $hDLL = DllOpen("libSlqiteFaster.dll") Local $Arrx = DllCall($hDLL,"STR" ,"SqliteSelect", "struct*", $tFilename ,"struct*" ,$tSsql ,"struct*" ,__SQLite_StringToUtf8Struct($RowSep) ,"struct*" ,__SQLite_StringToUtf8Struct($TabSep) ) DllClose($hDLL) $RowsArray= StringSplit($Arrx[0],$RowSep) $Headers=StringSplit($RowsArray[1],$TabSep) Local $iCols = $Headers[0] Local $iRows = $RowsArray[0] If Compare_Headers( $iniH,$Headers)=1 Then While _GUICtrlListView_GetColumnCount($idListView)>0 _GUICtrlListView_DeleteColumn ( $idListView, 0 ) WEnd For $i = 1 To $iCols _GUICtrlListView_AddColumn( $idListView,$Headers[ $i ], 75 ) Next EndIf GUICtrlSendMsg( $idListView, $LVM_SETITEMCOUNT, $iRows-2, 0 ) ;$RowsArray=0 ;$Headers=0 $iRows=0 $iCols=0 $tFilename=0 $tSsql=0 EndFunc Func Example() Local $hGui = GUICreate( "LV_", @DesktopWidth-20, @DesktopHeight-40,-1,-1, $WS_SIZEBOX+$WS_MAXIMIZEBOX+ $WS_MINIMIZEBOX ) Global $Button3 = GUICtrlCreateButton("Ne3", 400, 0, 75, 25) Global $Button1 = GUICtrlCreateButton("NeSmler", 100, 0, 75, 25) Local $idListView Local $pListViewCallback Local $pEditCallback Local $pListCommand Local $pGuiCallback ;MsgBox(0,"","STOP") $idListView= GUICtrlCreateListView( "", 10, 40, @DesktopWidth-20-20, @DesktopHeight-80-100,$LVS_OWNERDATA ) ;$LVS_NOCOLUMNHEADER _GUICtrlListView_SetExtendedListViewStyle( $idListView, BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_BORDERSELECT,$LVS_EX_DOUBLEBUFFER, $LVS_EX_GRIDLINES) ) ; $LVS_EX_DOUBLEBUFFER+$LVS_EX_FULLROWSELECT + GUICtrlSetResizing($hGui, $GUI_DOCKBORDERS) $hListView = GUICtrlGetHandle( $idListView ) ; Subclass ListView to handle messages related to ComboBox ; ComboBox open and close events $idComboOpen = GUICtrlCreateDummy() $idComboClose = GUICtrlCreateDummy() GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" ) ; Subclass callback functions $pListViewCallback= DllCallbackGetPtr( DllCallbackRegister( "ListViewCallback", "lresult", "hwnd;uint;wparam;lparam;uint_ptr;dword_ptr" ) ) $pEditCallback = DllCallbackGetPtr( DllCallbackRegister( "EditCallback", "lresult", "hwnd;uint;wparam;lparam;uint_ptr;dword_ptr" ) ) $pListCommand = DllCallbackGetPtr( DllCallbackRegister( "ListCommand", "lresult", "hwnd;uint;wparam;lparam;uint_ptr;dword_ptr" ) ) $pGuiCallback = DllCallbackGetPtr( DllCallbackRegister( "GuiCallback", "lresult", "hwnd;uint;wparam;lparam;uint_ptr;dword_ptr" ) ) _WinAPI_SetWindowSubclass( $hListView, $pListViewCallback, 9998, 0 ) ; Show GUI GUISetState( @SW_SHOW ) ; Message loop While 1 Switch GUIGetMsg() Case $Button3 GuiSetState(@SW_LOCK, $hGui) nCheckDB("bigtable.sqlite" ,"SELECT * FROM hugetable;",$idListView ) GuiSetState(@SW_UNLOCK, $hGui) Case $Button1 GuiSetState(@SW_LOCK, $hGui) For $i = 0 To _GUICtrlListView_GetColumnCount($idListView)-1 _GUICtrlListView_SetColumnWidth ( $hListView, $i, $LVSCW_AUTOSIZE_USEHEADER ) ;$LVSCW_AUTOSIZE Next GuiSetState(@SW_UNLOCK, $hGui) #cs Local $S_PH = "C:\\Users\\achis\\Desktop\\PP2C 24.11.2015_OK\\Prod.sqlite" Local $SSQLi ="UPDATE sumbomLVL_Static Set part='10000611' WHERE rowid=1 ;" Local $hDLL = DllOpen("libSlqiteFaster.dll") Local $Arrx = DllCall($hDLL,"STR" ,"SqliteExec", "struct*", __SQLite_StringToUtf8Struct($S_PH) ,"struct*" ,__SQLite_StringToUtf8Struct($SSQLi) ) MsgBox ( 0,"",$Arrx[0]) DllClose($hDLL) #ce Case $idComboOpen If $bComboOpen Then ; If another ComboBox is open then delete it _WinAPI_RemoveWindowSubclass( $hEdit, $pEditCallback, 9999 ) ;_WinAPI_RemoveWindowSubclass( $hListView, $pListCommand, 9999 ) _WinAPI_RemoveWindowSubclass( $hGui, $pGuiCallback, 9999 ) _GUICtrlEdit_Destroy( $hEdit ) EndIf $hEdit = _GUICtrlEdit_Create( $hListView,_GUICtrlListView_GetItemText( $hListView, $iItem, $iSubItem ), $aRect[0], $aRect[1], $aRect[2] - $aRect[0],20,$ES_AUTOHSCROLL ) _GUICtrlEdit_SetSel( $hEdit, 0, -1 ) ; Create subclasses to handle Windows messages _WinAPI_SetWindowSubclass( $hEdit, $pEditCallback, 9999, 0 ) ; Messages from the Edit control of the ComboBox ;_WinAPI_SetWindowSubclass( $hListView, $pListCommand, 9999, 0 ) ; WM_COMMAND messages from Listbox part of ComboBox _WinAPI_SetWindowSubclass( $hGui, $pGuiCallback, 9999, 0 ) ; Handle GUI messages related to ComboBox control ; Set focus to ComboBox ; Subclasses are used only when ComboBox is open _WinAPI_SetFocus( $hEdit ) $bComboOpen = True Case $idComboClose If Not $bComboOpen Then ContinueLoop If GUICtrlRead( $idComboClose ) Then Local $c_item = StringSplit($RowsArray[$iItem+2],$TabSep)[$iSubItem+1] Local $T = StringSplit( $RowsArray[$iItem+2],$TabSep) $T[$iSubItem] =_GUICtrlEdit_GetText( $hEdit ) $RowsArray[$iItem+2]=_ArrayToString($T,$TabSep) _WinAPI_SetFocus( $hListView ) ; Set focus to ListView EndIf ; Delete ComboBox control _WinAPI_RemoveWindowSubclass( $hEdit, $pEditCallback, 9999 ) ;_WinAPI_RemoveWindowSubclass( $hListView, $pListCommand, 9999 ) _WinAPI_RemoveWindowSubclass( $hGui, $pGuiCallback, 9999 ) _GUICtrlEdit_Destroy( $hEdit ) $bComboOpen = False Case $GUI_EVENT_PRIMARYDOWN, $GUI_EVENT_SECONDARYDOWN If Not $bComboOpen Then ContinueLoop ; Clicks in Listbox part of ComboBox should not delete it If $bListboxOpen Then ContinueLoop ; Clicks in Edit part of ComboBox should not delete it Local $aPos = MouseGetWindowPos( $hListView ) If Not ( $aPos[0] > $aRect[0] And $aPos[0] < $aRect[2] And $aPos[1] > $aRect[1] And $aPos[1] < $aRect[1] + 20 ) Then GUICtrlSendToDummy( $idComboClose ) ; Delete ComboBox Local $aSize = WinGetPos( $hListView ) If $aPos[0] > 0 And $aPos[1] > 0 And $aPos[0] < $aSize[2] And $aPos[1] < $aSize[3] Then _ _WinAPI_SetFocus( $hListView ) ; Set focus to ListView if mouse click is inside ListView EndIf Case $GUI_EVENT_CLOSE Exit If Not $bEditEscape Then ExitLoop $bEditEscape = False EndSwitch WEnd ; Cleanup GUIDelete() EndFunc Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam ) Local Static $bNotXP = Not ( @OSVersion = "WIN_XP" ) Local Static $tRect = DllStructCreate( $tagRECT ) Local Static $hBrush = _WinAPI_CreateSolidBrush( 0xFFFF00 ) Local Static $tText = DllStructCreate( "wchar[50]" ) Local Static $pText = DllStructGetPtr( $tText ) Local $tNMHDR = DllStructCreate( $tagNMHDR, $lParam ) Local $hWndFrom = HWnd( DllStructGetData( $tNMHDR, "hWndFrom" ) ) Local $iCode = DllStructGetData( $tNMHDR, "Code" ) Switch $hWndFrom Case $hListView Switch $iCode Case $LVN_GETDISPINFOW Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam ) ; $LV_DISPINFO *lpdi = ($LV_DISPINFO *)$lParam; If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then Local $iIndex = DllStructGetData( $tNMLVDISPINFO, "Item" ) Local $Subindex = DllStructGetData($tNMLVDISPINFO,"SubItem") Local $sItem = StringSplit($RowsArray[$iIndex+2],$TabSep)[$Subindex+1] ; $aResult[$iIndex][DllStructGetData($tNMLVDISPINFO,"SubItem")] DllStructSetData( $tText, 1, $sItem ) DllStructSetData( $tNMLVDISPINFO, "Text", $pText ) DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) ) EndIf Case $NM_CUSTOMDRAW Local $tNMLVCustomDraw = DllStructCreate( $tagNMLVCUSTOMDRAW, $lParam ) Local $dwDrawStage = DllStructGetData( $tNMLVCustomDraw, "dwDrawStage" ) Switch $dwDrawStage ; Specifies the drawing stage ; Stage 1 Case $CDDS_PREPAINT ; Before the paint cycle begins Return $CDRF_NOTIFYITEMDRAW ; Stage 2 will be carried out ; Stage 2 Case $CDDS_ITEMPREPAINT ; Before an item is painted If Not _GUICtrlListView_GetItemState( $hListView, DllStructGetData( $tNMLVCUSTOMDRAW, "dwItemSpec" ), $LVIS_FOCUSED ) Then Return $CDRF_NEWFONT ; Default drawing of item $iItem = DllStructGetData( $tNMLVCUSTOMDRAW, "dwItemSpec" ) Return $CDRF_NOTIFYSUBITEMDRAW ; Stage 3 will be carried out ; Stage 3 Case BitOR( $CDDS_ITEMPREPAINT, $CDDS_SUBITEM ) ; Before a subitem is painted Return $CDRF_NOTIFYPOSTPAINT ; Stage 4 will be carried out ; Stage 4 Case BitOR( $CDDS_ITEMPOSTPAINT, $CDDS_SUBITEM ) ; After a subitem has been painted If DllStructGetData( $tNMLVCustomDraw, "iSubItem" ) = $iSubItem Then Local $hDC = DllStructGetData( $tNMLVCUSTOMDRAW, "hdc" ) ; Device context $aRect = _GUICtrlListView_GetSubItemRect( $hListView, $iItem, $iSubItem ) ; Subitem rectangle $aRect[2] = $aRect[0] + _GUICtrlListView_GetColumnWidth( $hListView, $iSubItem ) DllStructSetData( $tRect, "Left", $aRect[0]+4 ) DllStructSetData( $tRect, "Top", $aRect[1] ) DllStructSetData( $tRect, "Right", $aRect[2] ) DllStructSetData( $tRect, "Bottom", $aRect[3] ) _WinAPI_FillRect( $hDC, $tRect, $hBrush ) ; Fill subitem background _WinAPI_SetTextColor( $hDC, 0x000000 ) ; Set black text color DllStructSetData( $tRect, "Left", DllStructGetData( $tRect, "Left" ) + 2 ) ; Adjust rectangle If $bNotXP Then DllStructSetData( $tRect, "Top", DllStructGetData( $tRect, "Top" ) + 2 ) _WinAPI_DrawText( $hDC, StringSplit($RowsArray[$iItem+2],$TabSep)[$iSubItem+1], $tRect, $DT_WORD_ELLIPSIS ) ; Draw text EndIf Return $CDRF_NEWFONT ; $CDRF_NEWFONT must be returned after changing font or colors EndSwitch Case $NM_CLICK If Not $bComboDoNotOpen And Not $bComboOpenOnDoubleClick And $iItem > -1 And $iSubItem > -1 Then _ GUICtrlSendToDummy( $idComboOpen ) ; Send message to open ComboBox Case $NM_DBLCLK If $bComboOpenOnDoubleClick And $iItem > -1 And $iSubItem > -1 Then _ GUICtrlSendToDummy( $idComboOpen ) ; Send message to open ComboBox EndSwitch EndSwitch Return $GUI_RUNDEFMSG EndFunc ; Handle ListView messages related to ComboBox Func ListViewCallback( $hWnd, $iMsg, $wParam, $lParam, $iSubclassId, $pData ) Switch $iMsg ; Open ComboBox on Enter key Case $WM_GETDLGCODE Switch $wParam Case $VK_RETURN If Not $bComboDoNotOpen And $bComboOpenOnEnter And $iItem > -1 And $iSubItem > -1 Then _GUICtrlListView_SetItemSelected( $hListView, -1, False ) _GUICtrlListView_SetItemSelected( $hListView, $iItem ) GUICtrlSendToDummy( $idComboOpen ) EndIf EndSwitch ; Manage multiple selections ; Prevent ComboBox from opening ; Manage keyboard events Case $WM_KEYDOWN Switch $wParam Case $VK_SHIFT, $VK_CONTROL $bComboDoNotOpen = True ; Manage keyboard events Case $VK_LEFT If Not $bComboDoNotOpen And $iSubItem > 0 Then $iSubItem -= 1 _GUICtrlListView_RedrawItems( $hListView, $iItem, $iItem ) Case $VK_RIGHT If Not $bComboDoNotOpen And $iSubItem < _GUICtrlListView_GetColumnCount( $hListView ) - 1 Then $iSubItem += 1 _GUICtrlListView_RedrawItems( $hListView, $iItem, $iItem ) Case $VK_SPACE If Not $bComboDoNotOpen And $bComboOpenOnSpace And $iItem > -1 And $iSubItem > -1 Then _GUICtrlListView_SetItemSelected( $hListView, -1, False ) GUICtrlSendToDummy( $idComboOpen ) EndIf EndSwitch Case $WM_KEYUP Switch $wParam Case $VK_SHIFT, $VK_CONTROL $bComboDoNotOpen = False EndSwitch ; Left click in ListView ; Sent on single and double click ; Determines item/subitem of the cell that's clicked Case $WM_LBUTTONDOWN Local $aHit = _GUICtrlListView_SubItemHitTest( $hListView ) If $bComboOpen Then ; If another ComboBox is open then delete it _GUICtrlEDIT_Destroy( $hEdit ) GUICtrlSendToDummy( $idComboClose ) _WinAPI_SetFocus( $hListView ) EndIf If $aHit[0] > -1 And $aHit[1] > -1 Then $iItem = $aHit[0] $iSubItem = $aHit[1] _GUICtrlListView_RedrawItems( $hListView, $iItem, $iItem ) EndIf ; Delete ComboBox on right click in ListView and on left ; or right click in non-client ListView area (Scrollbars). Case $WM_RBUTTONDOWN, $WM_NCLBUTTONDOWN, $WM_NCRBUTTONDOWN _GUICtrlEDIT_Destroy( $hEdit ) GUICtrlSendToDummy( $idComboClose ) _WinAPI_SetFocus( $hListView ) EndSwitch ; Call next function in subclass chain Return DllCall( "comctl32.dll", "lresult", "DefSubclassProc", "hwnd", $hWnd, "uint", $iMsg, "wparam", $wParam, "lparam", $lParam )[0] #forceref $iSubclassId, $pData EndFunc ; Messages from the Edit control of the ComboBox Func EditCallback( $hWnd, $iMsg, $wParam, $lParam, $iSubclassId, $pData ) Switch $iMsg ; Dialog codes Case $WM_GETDLGCODE Switch $wParam Case $VK_TAB ; Close GUICtrlSendToDummy( $idComboClose, True ) Case $VK_RETURN ; Accept and close GUICtrlSendToDummy( $idComboClose, True ) Case $VK_ESCAPE ; Close GUICtrlSendToDummy( $idComboClose, True ) _WinAPI_SetFocus( $hListView ) $bEditEscape = True EndSwitch ; Double click in Edit part of ComboBox Case $WM_LBUTTONDBLCLK Local $aPos = MouseGetWindowPos( $hListView ) If $aPos[0] > $aRect[0] And $aPos[0] < $aRect[2] And $aPos[1] > $aRect[1] And $aPos[1] < $aRect[1] + 20 Then _ GUICtrlSendToDummy( $idComboClose, True ) ; Accept and close EndSwitch ; Call next function in subclass chain Return DllCall( "comctl32.dll", "lresult", "DefSubclassProc", "hwnd", $hWnd, "uint", $iMsg, "wparam", $wParam, "lparam", $lParam )[0] #forceref $iSubclassId, $pData EndFunc ; Handle GUI messages related to ComboBox control Func GuiCallback( $hWnd, $iMsg, $wParam, $lParam, $iSubclassId, $pData ) Switch $iMsg ; Delete ComboBox on left or right mouse click in non-client GUI area and on GUI deactivate Case $WM_NCLBUTTONDOWN, $WM_NCRBUTTONDOWN, $WM_ACTIVATE _GUICtrlEDIT_Destroy( $hEdit ) GUICtrlSendToDummy( $idComboClose ) EndSwitch ; Call next function in subclass chain Return DllCall( "comctl32.dll", "lresult", "DefSubclassProc", "hwnd", $hWnd, "uint", $iMsg, "wparam", $wParam, "lparam", $lParam )[0] #forceref $iSubclassId, $pData EndFunc ; Get mouse pos relative to window Func MouseGetWindowPos( $hWindow ) Local $aPos = MouseGetPos() Local $tPoint = DllStructCreate( "int X;int Y" ) DllStructSetData( $tPoint, "X", $aPos[0] ) DllStructSetData( $tPoint, "Y", $aPos[1] ) _WinAPI_ScreenToClient( $hWindow, $tPoint ) $aPos[0] = DllStructGetData( $tPoint, "X" ) $aPos[1] = DllStructGetData( $tPoint, "Y" ) Return $aPos EndFunc OnefolderWorking.zip
  9. Good morning I'd like to know if someone else here has already tried to use an AutoIt script which work on the same DB ( i.e. SQLite ) on different Computers. If yes, please answer here, because I'd like to develop it, and I don't know where to start ( i.e. , I don't know how the script would work if 2 or more users are writing/reading in the same moment... ) Thanks!
  10. romaSQL

    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
  11. Good day , I have the code below , and i would like to embed SQLite3.dll to the compiled file Is it possible ? Thanks in advance #include <SQLite.au3> #include <SQLite.dll.au3> _SQLite_Startup() If @error Then     MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!")     Exit -1 EndIf $DB = _SQLite_Open("C:\Temp\Test.db") $Action = "TestAction" $Time = @HOUR&":"&@MIN&":"&@SEC $Date = @YEAR&"-"&@MON&"-"&@MDAY $User = @UserName $Computer = @ComputerName $DC = @LogonServer If @error Then     MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Couldnt open Database")     Exit -1 EndIf _SQLite_Exec($DB,"INSERT INTO QLogs (Action,Date,Time,User,Computer,DC) " & _                               "VALUES ("& _SQLite_FastEscape($Action) & "," & _                                           _SQLite_FastEscape($Date) & "," & _                                           _SQLite_FastEscape($Time) & "," & _                                           _SQLite_FastEscape($User) & "," & _                                           _SQLite_FastEscape($Computer) & "," & _                                           _SQLite_FastEscape($DC) & ");") If @error Then     MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Couldnt insert!")     Exit -1 EndIf _SQLite_Shutdown()
  12. Hello - For a SQLite insert statement I'm passing a 2D array through two For/Next loops - code snippet: Local $sInsertInto2 = "" For $i = 0 To $iCount - 1 $sInsertInto2 &= " (" For $j = 0 To UBound($aTmp, 2) - 2 $sInsertInto2 &= "'" & StringReplace($aTmp[$i][$j], "'", "''") & "', " Next $sInsertInto2 &= "'" & StringReplace($aTmp[$i][$j], "'", "''") & "')" & ((($iCount - 1) = $i) ? (";") : ("," & @CRLF)) Next This works great but due to StringReplace() it is very slow. StringReplace() is used to "escape" single quotes (') and it is only required if a single quote actually is present... Somewhere on the forum there is a faster example by UEZ but I can't find it... Anyone any idea?
  13. Hi guys! How are you? Hope you're fine I'm trying to insert a text that contains quotes in a SQLite database, and I don't know how to do. I tried with: Local $sString = "1P6AV2104'0HA04'0AA0" StringReplace($sString, "'", "''") but I didn't managed to, obtaining this error: --> Error:    unrecognized token: "0HA04" How can I manage to solve this problem? Thanks
  14. Hi guys! How are you? Hope you're fine I'm trying to use SQLite for managing some data, and, I would like to display my "retrieved" data, but I'm trying to do a select from 2 table, and seems to not work properly... What I'd like to do is retrieve data from 2 tables and display in a listview... I tried with: Local $aRisultato, $iRighe, $iColonne, $iRVal $iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Rockwell, Magazzino_Siemens;", $aRisultato, $iRighe, $iColonne) If $iRVal = $SQLITE_OK Then ;_SQLite_Display2DResult($aRisultato) _ArrayDisplay($aRisultato) EndIf ... but it displays a single record 41 times, and it does this thing for every record in the database... What do you need to help me? Thanks EDIT: Managed with a double If and double query like this: $iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Rockwell;", $aRisultato, $iRighe, $iColonne) If $iRVal = $SQLITE_OK Then Local $aRisultatoRockwell = $aRisultato $iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Siemens;", $aRisultato, $iRighe, $iColonne) If $iRVal = $SQLITE_OK Then _ArrayConcatenate($aRisultato, $aRisultatoRockwell) Local $i, $sRiga, $s_LV_Item For $i = 1 To Ubound($aRisultato) - 1 $sRiga = $aRisultato[$i][0] & "|" & _ $aRisultato[$i][1] & "|" & _ $aRisultato[$i][2] & "|" & _ $aRisultato[$i][3] & "|" & _ $aRisultato[$i][4] & "|" & _ $aRisultato[$i][5] & "|" & _ $aRisultato[$i][6] & "|" & _ $aRisultato[$i][7] & "|" & _ $aRisultato[$i][8] & "|" & _ $aRisultato[$i][9] & "|" & _ $aRisultato[$i][10] & "|" & _ $aRisultato[$i][11] $s_LV_Item = GUICtrlCreateListViewItem($sRiga, $lv_Lista) Next And so, the listview is created If anyone has another more efficient way, I'm here Thanks guys
  15. 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.
  16. I have a script working on a windows 7, 64 bit OS that uses SQLite. I went to transfer to to my laptop so i could work on it while on the go, but it's not running on my laptop (Windows 10, 64 bit). The script / autoit is configured to compile and run as x86 for compatibility's sake, via the option during autoit setup. Here is the minimal code to reproduce my problem: #include <SQLite.au3> #include <SQLite.dll.au3> ;Without this, downloading fails HttpSetUserAgent("Mozilla/5.0 (Windows NT 6.3; rv:36.0) Gecko/20100101 Firefox/36.0") If @CPUArch = "x86" and Not FileExists("sqlite3.dll") then InetGet("https://www.autoitscript.com/autoit3/pkgmgr/sqlite/sqlite3.dll", @ScriptDir&"\sqlite3.dll") ConsoleWrite("Error = "&@error&@CRLF) EndIf If @CPUArch = "x64" and Not FileExists("sqlite3_x64.dll") then InetGet("https://www.autoitscript.com/autoit3/pkgmgr/sqlite/sqlite3_x64.dll", @ScriptDir&"\sqlite3_x64.dll") ConsoleWrite("Error = "&@error&@CRLF) EndIf _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error "&@error, "SQLite3.dll Can't be Loaded!") ConsoleWrite("https://www.autoitscript.com/autoit3/pkgmgr/sqlite/sqlite3_x64.dll"&@CRLF) ConsoleWrite(@ScriptDir&"\sqlite3_x64.dll"&@CRLF) Exit -1 EndIf Downloading works, files are downlaoded to @scriptdir properly. However no matter what I do with _SQLite_Startup() it fails with @error = 1. I've tried explicitly telling it to use both versions of the dll as well as leaving it to choose for itself and nothing works. Help?
  17. Hello. Could you help me find the answer for my issue. I'm trying to set fomatted data wich is selected from SQL to GUICtrlCreateEdit field. And the GUICtrlSetData function is inserting it in one line. But when I'm trying to do the same using MsgBox the data looks fine. I'm trying to not use _GUICtrlRichEdit UDF. Thanks in advance. Here is my test code. #include <GUIConstants.au3> #include <GUIListBox.au3> #include <SQLite.au3> #include <SQLite.dll.au3> Opt("GUIOnEventMode", 1) $hMainGUI = GUICreate("Test", 520, 240) $hListBox = _GUICtrlListBox_Create($hMainGUI, "", 10, 10, 180, 80) $hEdit = GUICtrlCreateEdit("", 10, 80, 500, 150) GUISetState(@SW_SHOW, $hMainGUI) GUIRegisterMsg($WM_COMMAND, "_WM_COMMAND") Local $hQuery, $aRow _SQLite_Startup() $hDB = _SQLite_Open('MyDB.sqlite') _SQLite_Query(-1, "SELECT ID ||"". "" || Name FROM Templates ORDER BY ID;", $hQuery) While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK _GUICtrlListBox_AddString($hListBox, $aRow[0]) WEnd GUISetOnEvent($GUI_EVENT_CLOSE, "CLOSE_Button") While 1 Sleep(100) WEnd Func _WM_COMMAND($hWnd, $iMsg, $wParam, $lParam) Local $hWndFrom, $iIDFrom, $iCode, $hWndListBox If Not IsHWnd($hListBox) Then $hWndListBox = GUICtrlGetHandle($hListBox) $hWndFrom = $lParam $iIDFrom = BitAND($wParam, 0xFFFF) ; Low Word $iCode = BitShift($wParam, 16) ; Hi Word Switch $hWndFrom Case $hListBox, $hWndListBox Switch $iCode Case $LBN_DBLCLK Select_Template(_GUICtrlListBox_GetCurSel($hListBox) + 1) Return 0 EndSwitch EndSwitch EndFunc ;==>_WM_COMMAND Func CLOSE_Button() _SQLite_Close() _SQLite_Shutdown() Exit EndFunc ;==>CLOSE_Button Func Select_Template($sListItem) _SQLite_QuerySingleRow($hDB, _ "SELECT Content " & _ "FROM Templates " & _ "WHERE ID = " & $sListItem & ";", $aRow) ;~ MsgBox(64, "Test: " & $sListItem, $aRow[0]) GUICtrlSetData($hEdit, $aRow[0]) WinSetTitle($hMainGUI, "", "Test: " & $sListItem) EndFunc ;==>Select_Template
  18. Hi, when a client sends /logout to the server i want that the server find the id of the client and set his online status to 0 Using 'default' I have no problems, but when I do this with 'blank517' gives me id 10 and then for the database remains online Database while Blank517 logout: id | username | password | permissions | online | 0 | default | pass1 | 0 | 0 | 1 | Blank517 | pass | 0 | 1 | Server recv: Func _Recv_From_Sockets_() For $0 = 1 To $max_connections $Recv = TCPRecv ($Socket_Data[$0][0],1024) If StringLeft($Recv, 1) = "/" Then If StringInStr($Recv, "logout") Then _SQLite_Query(-1, "SELECT id FROM Users WHERE username = '" & $Socket_Data[$0][1] & "' AND online = '1';", $hQuery) While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK $sMsg &= $aRow[0]; <-- $sMsg = 10 after FetchData WEnd _SQLite_Exec(-1, "UPDATE Users SET online = '0' WHERE id = '" & $sMsg & "';") For $000 = 1 To $max_connections TCPSend($Socket_Data[$000][0], $Socket_Data[$0][1] & " ha effettuato il logout") Next TCPCloseSocket($Socket_Data[$0][0]) $sMsg = Null EndIf Else _Broadcast_To_Sockets_ ($Recv) EndIf Next EndFunc *excuse me for my bad english *
  19. SQLite login

    Hi, I would like to make a login script with SQLite. The database looks like this: _SQLite_Exec(-1, "CREATE TABLE Users (id INT(8) NOT NULL, username VARCHAR(30) NOT NULL, password VARCHAR(255) NOT NULL, permission INT(8) NOT NULL, PRIMARY KEY (id)); CREATE UNIQUE INDEX 'user_name_unique' ON 'Users' ('username' );") _SQLite_Exec(-1, "INSERT INTO Users(id, username, password, permission) VALUES ('0', 'default', 'password', '0');") theoretically I should make a query in this way, right? _SQLite_Query (-1, "SELECT id FROM Users WHERE username = '" & $ Recv [1] & "' AND password = '" & $ Recv [2] & "';", $ hQuery) but then I do not know how to know if you have found the 'id'
  20. My SQLite.au3 include contains this entry on line 43 Global Const $SQLITE_READONLY = 8 ; /* Attempt to write a readonly database */ But, is not in the Help file. The right page, I think is _SQLite_Open.htm, and does not contain a reference to opening a SQLite db as ReadOnly. Should this be added to the Help File? BUT, what I really want to know is this: What is the best way to determine whether a SQLite db should be opened as ReadOnly? I have WAL files active. Can I check for WAL and if exists, force open ReadOnly? ie, if a user is already logged on, simply for other users to Read Only? Is this the right question to ask?
  21. sqlite database written in ANSI code reading?The current version is based on UTF 8 encoding to read and write。 UNICODE or ANSI transfer method
  22. Best way to store Dates in SQLite?

    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
  23. #cs ---------------------------------------------------------------------------- AutoIt Version: 3.3.14.0 Author: Skysnake Date: 2016.05.17 Script Function: Alter table drop column. By way of example the original table has these three columns: name,surname,nickname to be replaced with name,surname,number "We cannot drop a specific column in SQLite 3. See the FAQ. It is not possible to rename a column, remove a column, or add or remove constraints from a table. While you can always create a new table and then drop the older one.Dec 9, 2011" http://stackoverflow.com/questions/8442147/how-to-delete-or-add-column-in-sqlite The only ALTER TABLE functions that SQLite supports are those that can be accomplished without having to rewrite the entire table. Or, to put it another way, the only ALTER TABLE functions supported are those that can be done in O(1) time. ALTER TABLE DROP COLUMN requires writing every row of the table. D. Richard Hipp http://sqlite.1065341.n5.nabble.com/Why-can-t-SQLite-drop-columns-td64687.html Notes: Required original array size of table to be altered (zero count +1) Required the name of the column to drop Required full "create table ..." syntax for new column eg: , number text not null default 'Contact Number') #ce ---------------------------------------------------------------------------- ; Script Start - Add your code below here ; SQLite.dll version must match #include <Array.au3> #include <MsgBoxConstants.au3> #include <SQLite.au3> #include <SQLite.dll.au3> Local Const $iMax = 6 ; this is the current table column count Local $query, $aResult, $iRows, $iColumns, $ColName, $sMsg, $mySQLdb, $aLessColmns[$iMax][1] ; will require only one column Local $sNewColNames Local $sLocalSQLiteDll = "sqlite3_win32-x86-3120100.dll" ; Local $sSQliteDll = _SQLite_Startup($sLocalSQLiteDll, False, 1) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite3.dll Can't be Loaded!" & @CRLF & @CRLF & _ "Not FOUND in @SystemDir, @WindowsDir, @ScriptDir, @WorkingDir or from www.autoitscript.com") Exit -1 EndIf ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) $mySQLdb = _SQLite_Open(@ScriptDir & '\mydb.sql3') ; Without $sCallback it's a resultless statement ; ..................................................... create table tORiginal for System Info $query = "CREATE TABLE if not exists tORiginal ( " _ & "id integer primary key unique not null " _ & ", name text not null default 'First Name' " _ & ", surname text not null default 'Surname' " _ & ", nickname text not null default 'Nickname' " _ & ", RegDate text NOT NULL DEFAULT current_timestamp " _ & " ) ; " If Not _SQLite_Exec($mySQLdb, $query) = $SQLITE_OK Then _ MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg() & " * " & @ScriptLineNumber) ConsoleWrite("SQL created tORiginal " & @CRLF) ; Without $sCallback it's a resultless statement ; ..................................................... create table tORiginal for System Info $query = "Insert into tORiginal (name,surname,nickname) values ('John','Jones','Johnny') " _ & " ; " If Not _SQLite_Exec($mySQLdb, $query) = $SQLITE_OK Then _ MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg() & " * " & @ScriptLineNumber) ConsoleWrite("SQL data inserted into tORiginal " & @CRLF) $query = " SELECT * FROM tORiginal " _ & " ; " ; Query ; Query $iRval = _SQLite_GetTable2d($mySQLdb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ConsoleWrite("$SQLITE_OK at Line " & $SQLITE_OK & " " &@ScriptLineNumber & @CRLF) Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf _ArrayDisplay($aResult, "CREATEd and INSERTed" ) ; show the results of insert into SQL table ; https://www.sqlite.org/pragma.html#pragma_table_info $query = " pragma table_info(tORiginal) " _ & " ; " ; Query ; Query $iRval = _SQLite_GetTable2d($mySQLdb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ConsoleWrite("$SQLITE_OK at Line " & $SQLITE_OK & " " &@ScriptLineNumber & @CRLF) Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf _ArrayDisplay($aResult, "Pragma Result: Original Table") ; show the results of pragma ConsoleWrite("SQL PRAGMA to find all columns " & @CRLF) ; this is the AutoIt magic ; SQLite does not allow manipulation of the pragma result, but ; AutoIt can process this result as normal query result ; delete column %id% from result #cs Row|Col 0 |Col 1 |Col 2 |Col 3 |Col 4 |Col 5 [0]|cid |name |type |notnull|dflt_value |pk [1]|0 |id |INTEGER|0 | |1 [2]|1 |Name | |0 | |0 [3]|2 |Surname | |0 | |0 [4]|3 |Nickname | |0 | |0 ; >>>>>>>>> column to remove #CE ;https://www.autoitscript.com/wiki/Arrays#Comparing_Arrays Local $transfound = UBound($aResult, 1) - 1 For $i = 1 To $transfound Step +1 ConsoleWrite($aResult[$i][1] & @CRLF) ; Check that the array is big enough If UBound($aLessColmns) = $i Then ; Resize the array when $i is equal to the element count in the array to prevent subscript error ReDim $aLessColmns[$aLessColmns[0] + $iMax] EndIf If StringRegExp($aResult[$i][1], "nick") Then ;>>>>>>>>> remove the column you dont want ; it may be dangerous to use a Regex here :) ; do nothing, do not add this column name Else $aLessColmns[$i][0] = $aResult[$i][1] ; safely add data to new index element $aLessColmns[0][0] = $i ; update the index count for future reference EndIf Next ; for good measure ; Move backwards through the array deleting the blank lines For $i = UBound($aLessColmns) - 1 To 0 Step -1 If $aLessColmns[$i][0] = "" Then _ArrayDelete($aLessColmns, $i) EndIf Next $aLessColmns[0][0] = UBound($aLessColmns) - 1 ; update the index count for future reference _ArrayDisplay($aLessColmns, "Column names from tORiginal less one") ; show the new array $sNewColNames = _ArrayToString($aLessColmns, "|", 1, -1, ",") ; entire array MsgBox(0, "$sNewColNames", $sNewColNames) ConsoleWrite("New Column names " & $sNewColNames & @CRLF) ; now create a transient table, insert values, and rename _SQLite_Exec($mySQLdb, "Create table tNewColumns (" & $sNewColNames & ", number text not null default 'Contact Number') ; " & _ "Insert into tNewColumns (" & $sNewColNames & ") select " & $sNewColNames & " from tORiginal " & _ " ; ") $query = " SELECT * FROM tNewColumns " _ & " ; " ; Query ; Query $iRval = _SQLite_GetTable2d($mySQLdb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ConsoleWrite("$SQLITE_OK at Line " & $SQLITE_OK & @ScriptLineNumber & @CRLF) Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf _ArrayDisplay($aResult, "Results of new table") ; show the results of insert into SQL table ; https://www.sqlite.org/pragma.html#pragma_table_info $query = " pragma table_info(tORiginal) " _ & " ; " ; Query ; Query $iRval = _SQLite_GetTable2d($mySQLdb, $query, $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ConsoleWrite("$SQLITE_OK at Line " & $SQLITE_OK & " " &@ScriptLineNumber & @CRLF) Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) EndIf _ArrayDisplay($aResult, "Pragma Result: altered Original Table") ; show the results of pragma ConsoleWrite("SQL PRAGMA to find all columns " & @CRLF) ; now drop tORiginal ; https://www.sqlite.org/lang_altertable.html ; ALTER TABLE tNewColumns RENAME TO tORiginal ConsoleWrite("_SQLite_Shutdown " & @CRLF) _SQLite_Shutdown() FileDelete($mySQLdb) I believe it is suitable to post on this Forum, as it serves no purpose anywhere else. I have searched, and except for some ancient threads referring to this, could find nothing similar. I used SQLite 3.12.1 which can be downloaded here Precompiled Binaries for Windows. I see they are up to 3.12.2 now. Painfully obvious is of course that one must know the name of the offending column.
  24. Hi all i have this problem, I need to call sometimes the function below and i need return value outside. But when i write _Arraydisp($aResult) inside function show result, when i put outside nothing to show Func _SqliteQuery($req) _SqlStart() $iRval = _SQLite_GetTable2d (-1, $req, $aResult, $iRows, $iColumns) _SQLite_Close() _SQLite_Shutdown() EndFunc
  25. I have a table with 9 columns and at some point I add an incomplete entry, it might have 2 columns filled in, it might have all of them. When I come back to the table to extract the items I check to see if there are duplicates, pulling out the row with all the information I need but now I need to delete all those duplicates. Here's a small reproducer for what I'm trying to accomplish. I thought it would be easy with LIMIT but that gives a syntax error. #include <SQLite.au3> #include <SQLite.dll.au3> #include <Array.au3> _SQLite_Startup() Global $hSqDb = _SQLite_Open("") Global $iRows = 0 Global $iColumns = 0 Global $aResults = Null Global $iHighestRating = 0 Global $iLowestRating = 0 _SQLite_Exec($hSqDb, "CREATE TABLE IF NOT EXISTS websites (url TEXT, lastVisit TEXT, nextVisit TEXT, rating INTEGER);") _SQLite_Exec($hSqDb, "INSERT INTO websites VALUES ('https://www.google.com/', '4/10/2016.0012', '4/10/2016.1212', 8), ('https://www.google.com/', Null, Null, Null), ('https://www.bing.com', '4/09/2016.0936', '4/10/2016.0936', 7), ('https://www.yahoo.com.com', '4/05/2016.2200', '4/15/2016.4000', 4);") $aResults = _SqLite_GetAllRows($hSqDb, "websites", "url", 'https://www.google.com/') For $i = 1 to UBound($aResults) - 1 $iHighestRating = Max($iHighestRating, $aResults[$i][3]) $iLowestRating = Min($iLowestRating, $aResults[$i][3]) Next ; Limit gives syntax error _SQLite_Exec($hSqDb, "DELETE FROM websites WHERE url = 'https://www.google.com/' LIMIT " & UBound($aResults) - 2 & ";") _SQLite_Close($hSqDb) _SQLite_Shutdown() Func Min(Const $lhs, Const $rhs) Return ($lhs < $rhs ? $lhs : $rhs) EndFunc Func Max(Const $lhs, Const $rhs) Return ($lhs > $rhs ? $lhs : $rhs) EndFunc Func _SqLite_GetAllRows(ByRef $hSqLite, Const $sTable, Const $sWhereKey, $sWhereValue) Local $iRows = 0 Local $iColumns = 0 Local $aReturn = Null Local $iGetAttempts = 100 If ($sWhereValue <> "" and Not (StringIsInt($sWhereValue) or StringIsFloat($sWhereValue))) Then $sWhereValue = "'" & $sWhereValue & "'" Do _SQLite_GetTable2d($hSqLite, "SELECT * FROM " & $sTable & " WHERE " & $sWhereKey & " = " & $sWhereValue & ";", $aReturn, $iRows, $iColumns) Until (_SQLite_ErrCode() = $SQLITE_OK Or _SQLite_ErrCode() = $SQLITE_NOTFOUND or $iGetAttempts = 0) Return ($iRows > 1 ? SetExtended($iRows, $aReturn) : SetError(-1, 0, "")) EndFunc To clarify, I need to delete only the rows with the specified value and not remove the other duplicates.
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.