Skysnake

SQLite Alter Table Drop Column (with some AutoIt magic)

1 post in this topic

#1 ·  Posted (edited)

#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. :)

 

Edited by Skysnake
2016.06.04 Improved

Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites



Create an account or sign in to comment

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

Create an account

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


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Similar Content

    • Adrian123
      By Adrian123
      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
    • FrancescoDiMuro
      By FrancescoDiMuro
      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! 
    • rynow
      By rynow
      romaSQL
      This autoIt UDF is built on the concept of Laravel Query & doctrine.
      RomaSQL provides a new, comfortable and easy to use way for SQL-queries in autoIt.
      Most of the common SQL-queries are supported already and more are coming soon.
      All of your support is much appreciated.
      Connections
      For the connection the object ADODB is used. Therefore the connection string is based on ODBC.
      You can also use OLEDB connection strings or other database connections.
      In order for this to work your add-ons have to be installed in the function: __4ern_SQL_Connection.
      I’d be very glad if you shared your modifications with me.
      Currently supported connections
      -       MySQL (odbc)
      -       Microsoft SQL Server (odbc)
      -       SQLite (odbc)
      -       Microsoft Access (odbc)
      Command reference
      $SQL_connect; establishing connection $SQL_returnType; return a Array or Dictionary ('oDict') Object (Default = Array) $SQL_setDefaultTable; Default Tablename $SQL_setDefaultKey; Default Colmn Key (Default = id) $SQL_debug; if True, show SQL Statment in Console $SQL_get $SQL_update $SQL_delete $SQL_insertInto $SQL_take $SQL_limit $SQL_table $SQL_select $SQL_distinct $SQL_where $SQL_orWhere $SQL_whereBetween $SQL_whereNotBetween $SQL_whereIn $SQL_whereNotIn $SQL_whereNull $SQL_whereNotNull $SQL_having $SQL_orHaving $SQL_havingBetween $SQL_havingNotBetween $SQL_havingIn $SQL_havingNotIn $SQL_havingNull $SQL_havingNotNull $SQL_groupBy $SQL_orderBy  
      Examples
      establishing connection
      ;-----/ ; SQLite Connection ;-----/ $SQL_setDatabase('sqlite') $SQL_connect('C:\project.db') ;-----/ ; Access Connection ; Database, User, Password ;-----/ $SQL_setDatabase('access') $SQL_connect('C:\project.mdb') ;or as Admin $SQL_connect('C:\project.mdb', '4ern', 'root') ;-----/ ; SQLServer Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('sqlserver') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'SQL Server') ;-----/ ; MySQL Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('mysql') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'MySQL ODBC 5.2 UNICODE Driver')  
      simple SQL query
      $SQL_table('albums') $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      Select
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song') ;or pass to an Array Local $aSelect = ['id', 'Name', 'Artist', 'Song'] $SQL_select($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif where
      $SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song', 'Votes') $SQL_where('Artist', 'adele') $SQL_where('Votes', '>=' ,'9') $SQL_orWhere('Artist', '=' ,'Rag'n'Bone Man') ;or pass to an 2dArray Local $aSelect = [['Artist','adele'],['Votes', '>=' ,'9']] $SQL_where($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif  
      If you need more examples, then tell me exactly what you need.
      I hope you like my UDF and find some use for it.
      ---
      ->DONWLOAD romaSQL
       
       
    • BisherSH
      By BisherSH
      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()
    • supersonic
      By supersonic
      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?