Kennt ihr sicher: Datenbankabfragen und Ergebnismenge für nächste Abfrage nutzen (z.B. bei Filtern von Listen während der Eingabe von Begriffen)
Ich habe das mal für SQLite-DB in Verbindung mit AutoItObject realisiert. Als Bsp. ist meine PLZ-DB (habe ich selbst erstellt, könnt ihr frei nutzen) eingebunden:
Vielleicht könnt ihr es auch verwenden.
Spoiler anzeigen
C
;-- TIME_STAMP 2019-09-19 16:08:29
#include "AutoitObject.au3" ; https://www.autoitscript.com/forum/topic/110379-autoitobject-udf/
#include <Array.au3>
#include <SQLite.au3>
Opt('MustDeclareVars', 1)
_AutoItObject_StartUp()
_SQLite_Startup(@ScriptDir & "\sqlite3.dll", False, 1) ; Dll im angegebenen Pfad - hier im Skriptordner
OnAutoItExitRegister ("_OnExit_objFilter")
Global $g_hSQL
Global $g_oFilter ; DON'T USE ANY OTHER VARIABLE for the filtering object! It's required in exit function.
; assign your database and table
Global $dbPLZ = "C:\CODE\AutoIt\PLZ\plz_brd.db"
Global $sTable = 'plz'
; create the global filter object
Global $g_oFilter = _objFilter_Create($dbPLZ, $sTable)
; create a new instance for filtering
Global $oPLZ = $g_oFilter.new()
; filter for one condition in each process
; the next filter process will uses only the results from previous filtering
; filter operators: == = != <> > < >= <= !< !> LIKE IN
Global $aFilter
; you can filter one by one and show results between as array:
$aFilter = $oPLZ.filter('ort', 'in', 'Halle, Halle (Saale)').result
If IsArray($aFilter) Then _ArrayDisplay($aFilter)
$aFilter = $oPLZ.filter('plz', 'like', '0611%').result
If IsArray($aFilter) Then _ArrayDisplay($aFilter)
; ...or combine the calls
;~ $oPLZ.filter('ort', 'in', 'Halle, Halle (Saale)').filter('plz', 'like', '0611%')
; get the result as array
;~ $aFilter = $oPLZ.result
;~ If IsArray($aFilter) Then _ArrayDisplay($aFilter)
;============================== functions ==========================================================
; Opens the database and references to the named table ($g_hSQL)
; Creates temporary tables with the same structure to storing/switching results ($g_oFilter.tmptable1 /.tmptable2)
; The return must store in "$g_oFilter" for deleting the temporary tables when the script ends
Func _objFilter_Create($_dbToFilter, $_sTable)
Local $self = _AutoItObject_Create()
_AutoItObject_AddProperty($self, 'sqlcreate', $ELSCOPE_PUBLIC) ;-- the table create string
_AutoItObject_AddProperty($self, 'table', $ELSCOPE_PUBLIC) ;-- name of the referenced table
_AutoItObject_AddProperty($self, 'fields', $ELSCOPE_PUBLIC) ;-- dictionary for storing the tables fields names
_AutoItObject_AddProperty($self, 'tmptable1', $ELSCOPE_PUBLIC) ;-- temporary table for storing the filtered records
_AutoItObject_AddProperty($self, 'tmptable2', $ELSCOPE_PUBLIC) ;-- temporary table for switching the filtered records
_AutoItObject_AddMethod($self, 'new', '_objFilter_New')
_AutoItObject_AddMethod($self, 'filter', '_objFilter_Filter')
_AutoItObject_AddMethod($self, 'result', '_objFilter_Result')
$self.table = $_sTable
Local $aResult, $iRows, $iColumns
; ask for sql create string
Local $sSQL = StringFormat("SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master)" & _
"WHERE tbl_name LIKE '%s' AND type!='meta' ORDER BY type DESC, name;", $_sTable)
$g_hSQL = _SQLite_Open($_dbToFilter)
_SQLite_GetTable($g_hSQL, $sSQL, $aResult, $iRows, $iColumns)
$self.sqlcreate = $aResult[$aResult[0]]
; get the field names and store them
Local $aFields = StringRegExp($self.sqlcreate, '\[([^\]]+)\] ', 3)
$self.fields = ObjCreate('Scripting.Dictionary')
For $i = 0 To UBound($aFields) -1
$self.fields.add($aFields[$i], $i)
Next
; create temporary tables with unique name
$self.tmptable1 = __GUID()
$self.tmptable2 = __GUID()
Local $sCreateTmp = StringReplace($self.sqlcreate, 'CREATE TABLE ' & $_sTable, 'CREATE TABLE ' & $self.tmptable1)
_SQLite_Exec($g_hSQL, $sCreateTmp)
$sCreateTmp = StringReplace($self.sqlcreate, 'CREATE TABLE ' & $_sTable, 'CREATE TABLE ' & $self.tmptable2)
_SQLite_Exec($g_hSQL, $sCreateTmp)
Return $self
EndFunc
Func _OnExit_objFilter()
If $g_hSQL > 0 Then
_SQLite_Exec($g_hSQL, "DROP TABLE " & $g_oFilter.tmptable1)
_SQLite_Exec($g_hSQL, "DROP TABLE " & $g_oFilter.tmptable2)
_SQLite_Close($g_hSQL)
EndIf
_SQLite_Shutdown()
_AutoItObject_Shutdown()
Exit
EndFunc ;==>_OnExit_objAU3
;===================================================================================================
;============================== methodes ===========================================================
Func _objFilter_New($self, $_obj=Null)
If IsObj($_obj) Then
Return _AutoItObject_Create($_obj)
Else
Return _AutoItObject_Create($self)
EndIf
EndFunc
Func _objFilter_Filter($self, $_CompareField, $_CompareOp, $_CompareValue)
; check the field name
If Not $self.fields.exists($_CompareField) Then Return $self
; check for right operator
Local $sListOp = " == = != <> > < >= <= !< !> LIKE IN "
If Not StringInStr($sListOp, ' ' & $_CompareOp & ' ') Then Return $self
; from which table get results - on first call: "$self.table", otherwise "$self.tmptable1"
Local $sAskTable = __TableCountRows($self.tmptable1) = 0 ? $self.table : $self.tmptable1
; temporary the result will stored in "$self.tmptable2", so it need to be empty
__TableEmpty($self.tmptable2)
; sql-query, store result in temp table2
Local $sSQL
If $_CompareOp = 'IN' Then
Local $sIN, $aTmp = StringSplit($_CompareValue, ',')
For $i = 1 To $aTmp[0]
$sIN &= '"' & StringStripWS($aTmp[$i], 3) & '"' & ','
Next
$sSQL = StringFormat("INSERT INTO %s SELECT * FROM %s WHERE %s IN (%s);", $self.tmptable2, $sAskTable, $_CompareField, StringTrimRight($sIN, 1))
Else
$sSQL = StringFormat("INSERT INTO %s SELECT * FROM %s WHERE %s %s '%s';", $self.tmptable2, $sAskTable, $_CompareField, $_CompareOp, $_CompareValue)
EndIf
_SQLite_Exec($g_hSQL, $sSQL)
; for further use: empty .tmptable1 and store there the result
__TableEmpty($self.tmptable1)
__TableCopyAll($self.tmptable2, $self.tmptable1)
Return $self
EndFunc
Func _objFilter_Result($self)
Local $aResult, $iRows, $iColumns
Local $sSQL = StringFormat("SELECT * FROM %s;", $self.tmptable1)
_SQLite_GetTable2d($g_hSQL, $sSQL, $aResult, $iRows, $iColumns)
Return $aResult
EndFunc
;===================================================================================================
;============================== internal functions =================================================
Func __GUID()
Local $oTypeLib = ObjCreate("Scriptlet.TypeLib")
Local $GUID = 'tmp' & StringReplace(StringReplace(StringReplace($oTypeLib.GUID, '}', ''), '{', ''), '-', '_')
Return $GUID
EndFunc
Func __TableCountRows($_sTable)
Local $aResult, $iRows, $iColumns
Local $sSQL = StringFormat("SELECT COUNT(*) FROM %s", $_sTable)
_SQLite_GetTable($g_hSQL, $sSQL, $aResult, $iRows, $iColumns)
Return $aResult[$aResult[0]]
EndFunc
Func __TableCopyAll($_sTableFrom, $_sTableTo)
Local $sSQL = StringFormat("INSERT INTO %s SELECT * FROM %s;", $_sTableTo, $_sTableFrom)
_SQLite_Exec($g_hSQL, $sSQL)
EndFunc
Func __TableEmpty($_sTable)
Local $sSQL = StringFormat("DELETE FROM %s;", $_sTable)
_SQLite_Exec($g_hSQL, $sSQL)
EndFunc
;===================================================================================================
