Что нового

Не удаётся выполнить поиск по базе SQLite

Naisho

Знающий
Сообщения
86
Репутация
12
Собственно вопрос в корректности составления поискового запроса и, возможно, в определении типов данных.
примерчик ниже - выбираем папку, список файлов загоняется в базу (и показывается - нужно закрыть это окно), а затем пытаемся найти какой нибудь файл в базе по его имени (или части имени)

поисковый запрос
Код:
Local $s_query_string = "begin;" & "SELECT * FROM filelist WHERE Name_Long LIKE '%" & $Value & "%'" & ";commit;"

	_SQLite_Query ($db, $s_query_string, $hQuery)
	While _SQLite_FetchData($hQuery, $aRow, False , False) = $SQLITE_OK
		$Line = $aRow[0] & "|" & $aRow[1] & "|" & $aRow[2] & "|" & $aRow[3] & "|" & $aRow[4]
                ...
    WEnd


Целиком пример

Код:
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <GuiListView.au3>
#include <ComboConstants.au3>
#include <Array.au3>

Global $hQuery, $aRow, $sTimerInit, $sTimerDif, $sScanProgressCount, $sPathSizeCount, $sProgressCount, $sProgressCountSave
Local $aResult, $iRows, $iColumns, $iRval

_SQLite_Startup()
Global $db = _SQLite_Open(@ScriptDir & "\Database.db")

$select_input_folder = FileSelectFolder ( "Select Folder to Scan..." & @crlf & @crlf & "First scan with all attributes set... choose a small folder : )...", "",2 , @ScriptDir)
Local $sSqlBuild_Table
if $select_input_folder = true then
     $sTimerInit = TimerInit()
	_SQLite_Exec ($db, "BEGIN;")
	_FileListToSqlite_CreateTable($sSqlBuild_Table)
	$sFileListToSqlite_CreateTableError = @error
	_FileListToSqlite_FillTable($sSqlBuild_Table, $select_input_folder, "*")

    _SQLite_GetTable2d ($db, "SELECT * FROM filelist;", $aResult, $iRows, $iColumns)
    _ArrayDisplay($aResult)
endif
_SQLite_Exec ($db, "COMMIT;")

_SQLite_Exec($db, "vacuum;")
ConsoleWrite("Vacuum status: " & @error & ' ' & @extended & @LF)

$Form1 = GUICreate("Try", 627, 464, 192, 124)
$Input1 = GUICtrlCreateInput("", 8, 8, 121, 21)
$Search = GUICtrlCreateButton("Search", 136, 8, 75, 25)
$ListView = GUICtrlCreateListView("Path_Long|Name_Long|Extension|Time_Modified|Size", 0, 40, 626, 382)
Local $hListView = GUICtrlGetHandle($ListView)

GUISetState(@SW_SHOW)


If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit
EndIf


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Search
            $Valor = GUICtrlRead ($Input1)
            _Search($Valor)
    EndSwitch
WEnd
_SQLite_Close()
_SQLite_Shutdown()
Exit

;==============================================================================================================================
Func _FileListToSqlite_FillTable($sSqlBuild_Table, $sPath, $Extentions = "*")
	Local $pattern_FileName = '^.*\\'
	Local $pattern_FileExt = '.*\.'
	Local $pattern_FolderName = '\\[^\\]*$'
	$completeFileList = _FileSearch($sPath, $Extentions, 1)

	For $i = 1 To $completeFileList[0]
		$sSqlBuild_Query = '('
		$sSqlBuild_Query &= '"' & StringRegExpReplace($completeFileList[$i],$pattern_FolderName, '') & '"'
		$sSqlBuild_Query &= ',"' & StringRegExpReplace($completeFileList[$i],$pattern_FileName,'') & '"'
		$sSqlBuild_Query &= ',"' & StringRegExpReplace($completeFileList[$i],$pattern_FileExt, '') & '"'
		$sSqlBuild_Query &= ',"' & FileGetTime($completeFileList[$i],0,1) & '"'
		$sSqlBuild_Query &= ',"' & FileGetSize($completeFileList[$i]) & '"'
        $sSqlBuild_Query &= ');'

		_SQLite_Exec($db, 'INSERT INTO filelist ' & $sSqlBuild_Table & ' VALUES' & $sSqlBuild_Query)

	Next
    Return SetError(@error, 0, 0)
EndFunc
;==============================================================================================================================
Func _FileListToSqlite_CreateTable(ByRef $sSqlBuild_Table)
	Local $aNames
    $sTimerInit = TimerInit()

    _SQLite_FetchNames($db, $aNames)
    _SQLite_Exec($db, "DROP TABLE IF EXISTS filelist;")

    $sSqlBuild_Table = '('
    $sSqlBuild_Table &= 'Path_Long'
    $sSqlBuild_Table &= ',Name_Long'
    $sSqlBuild_Table &= ',Extension'
    $sSqlBuild_Table &= ',Time_Modified'
    $sSqlBuild_Table &= ',Size'
    $sSqlBuild_Table &= ')'

    _SQLite_Exec($db, 'CREATE TABLE filelist ' & $sSqlBuild_Table & ';')

    Return SetError(@error, 0, 0)
EndFunc
;==============================================================================================================================
Func _Search ($Value)
    _GUICtrlListView_BeginUpdate($ListView)
    _GUICtrlListView_DeleteAllItems($ListView)

    Local $hQuery, $aRow, $aNames
    Local $s_query_string = "begin;" & "SELECT * FROM filelist WHERE Name_Long LIKE '%" & $Value & "%'" & ";commit;"

	_SQLite_Query ($db, $s_query_string, $hQuery)
	While _SQLite_FetchData($hQuery, $aRow, False , False) = $SQLITE_OK
		$Line = $aRow[0] & "|" & $aRow[1] & "|" & $aRow[2] & "|" & $aRow[3] & "|" & $aRow[4]
        GuiCtrlCreateListViewItem($Line,$ListView)
    WEnd

    _SQLite_QueryFinalize($hQuery)
    _GUICtrlListView_EndUpdate($ListView)

	MsgBox(0,"Поиск выполнен","",2)
EndFunc
;==============================================================================================================================
Func _FileSearch($sPath, $sFileMask, $iFlag = 0)
	Local $sOut, $aOut, $sRead, $hDir, $sAttrib, $aMasks
	Switch $iFlag
		Case 1
			$sAttrib = ' /A-D'
		Case 2
			$sAttrib = ' /AD'
		Case Else
			$sAttrib = ' /A'
	EndSwitch
	$sOut = StringToBinary('0' & @CRLF, 2)
	$aMasks = StringSplit($sFileMask, ';')
	For $i = 1 To $aMasks[0]
		$hDir = Run(@ComSpec & ' /U /C DIR "' & $sPath & '\' & $aMasks[$i] & '" /S /B' & $sAttrib, @SystemDir, @SW_HIDE, 6)
		While ProcessExists($hDir)
			$sRead = StdoutRead($hDir, False, True)

			If @error Then
				ExitLoop
			EndIf
			If $sRead <> "" Then
				$sOut &= $sRead
			EndIf
		WEnd
	Next
	$aOut = StringRegExp(BinaryToString($sOut, 2), '[^\r\n]+', 3)

	If @error Then
		Return SetError(1)
	EndIf
	$aOut[0] = UBound($aOut) - 1
	Return $aOut
EndFunc
 

Redline

AutoIT Гуру
Сообщения
506
Репутация
375
begin и commit используются при транзакциях. При выборке они не используются.
Код:
Local $s_query_string = "SELECT * FROM filelist WHERE Name_Long LIKE '%" & $Value & "%'"
 
Верх