autoit sqlite 예제
#include <Array.au3>
#include <SQLite.au3>
_SQLite_Startup() ; Load the DLL
If @error Then Exit MsgBox(0, "Error", "Unable to start SQLite, Please verify your DLL")
Local $sDatabase = @ScriptDir & '\SQLiteTestDatabase.db'
Local $hDatabase = _SQLite_Open($sDatabase) ; Create the database file and get the handle for the database
_SQLite_Exec($hDatabase, 'CREATE TABLE People (first_name, last_name);') ; CREATE a TABLE with the name "People"
_SQLite_Exec($hDatabase, 'INSERT INTO People VALUES ("Timothy", "Lee");') ; INSERT "Timothy Lee" into the "People" TABLE
_SQLite_Exec($hDatabase, 'INSERT INTO People VALUES ("John", "Doe");') ; INSERT "John Doe" into the "People" TABLE
Local $aResult, $iRows, $iColumns ; $iRows and $iColuums are useless but they cannot be omitted from the function call so we declare them
_SQLite_GetTable2d($hDatabase, 'SELECT * FROM People;', $aResult, $iRows, $iColumns) ; SELECT everything FROM "People" TABLE and get the $aResult
_ArrayDisplay($aResult, "Results from the query")
_SQLite_Close($hDatabase)
_SQLite_Shutdown()
sqlite이용 하여 그룹별로 저장하고 그룹별 탭을 클릭시 해당룹의 내용을 보여주는 예제
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <SQLite.au3> ; SQLite UDF 포함 (미리 설치 필요)
#include <Array.au3>
; 데이터베이스 초기화
_SQLite_Startup()
Local $db = _SQLite_Open(":memory:") ; 메모리 기반 데이터베이스 사용
; 데이터베이스 테이블 생성
_SQLite_Exec($db, "CREATE TABLE data (key TEXT, value TEXT, group_name TEXT);")
; 예제 데이터 삽입
_SQLite_Exec($db, "INSERT INTO data VALUES ('Key1', 'Value1', 'Group1');")
_SQLite_Exec($db, "INSERT INTO data VALUES ('Key2', 'Value2', 'Group1');")
_SQLite_Exec($db, "INSERT INTO data VALUES ('Key3', 'Value3', 'Group2');")
_SQLite_Exec($db, "INSERT INTO data VALUES ('Key4', 'Value4', 'Group2');")
_SQLite_Exec($db, "INSERT INTO data VALUES ('Key5', 'Value5', 'Group3');")
; GUI 생성
Local $hGUI = GUICreate("Tab View with SQLite", 500, 400)
; Tab 컨트롤 생성
Local $hTab = GUICtrlCreateTab(10, 10, 480, 350)
; 그룹별 탭 생성
Local $aGroups
_SQLite_GetTable2d($db, "SELECT DISTINCT group_name FROM data;", $aGroups)
; 각 그룹 탭에 데이터를 표시
For $i = 1 To UBound($aGroups) - 1
Local $sGroupName = $aGroups[$i][0]
GUICtrlCreateTabItem($sGroupName)
; 그룹 내의 키-값 데이터 가져오기
Local $aData
_SQLite_GetTable2d($db, "SELECT key, value FROM data WHERE group_name = '" & $sGroupName & "';", $aData)
; 각 그룹의 데이터를 Label로 추가
Local $nY = 20
For $j = 1 To UBound($aData) - 1
Local $sText = $aData[$j][0] & ": " & $aData[$j][1]
GUICtrlCreateLabel($sText, 20, $nY, 440, 20)
$nY += 25
Next
Next
; 첫 번째 탭을 기본 탭으로 설정
GUICtrlCreateTabItem("")
; GUI 표시
GUISetState(@SW_SHOW)
; 메시지 루프
While 1
If GUIGetMsg() = $GUI_EVENT_CLOSE Then ExitLoop
WEnd
; 종료 처리
GUIDelete()
_SQLite_Close($db)
_SQLite_Shutdown()
Exit
또다른 예제
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>
#include <SQLite.au3>
#include <Array.au3>
; SQLite 초기화
_SQLite_Startup()
If @error Then
MsgBox(16, "Error", "SQLite.dll을 로드할 수 없습니다.")
Exit
EndIf
; GUI 생성
Global $hGUI = GUICreate("SQLite ListView Demo", 600, 400)
Global $hListView = _GUICtrlListView_Create($hGUI, "ID|Name|Age|City", 10, 10, 580, 300)
Global $btnInsert = GUICtrlCreateButton("Insert", 10, 320, 80, 30)
Global $btnUpdate = GUICtrlCreateButton("Update", 100, 320, 80, 30)
Global $btnDelete = GUICtrlCreateButton("Delete", 190, 320, 80, 30)
Global $inputName = GUICtrlCreateInput("", 10, 360, 120, 20)
Global $inputAge = GUICtrlCreateInput("", 140, 360, 50, 20)
Global $inputCity = GUICtrlCreateInput("", 200, 360, 120, 20)
; ListView 설정
_GUICtrlListView_SetColumnWidth($hListView, 0, 50)
_GUICtrlListView_SetColumnWidth($hListView, 1, 200)
_GUICtrlListView_SetColumnWidth($hListView, 2, 50)
_GUICtrlListView_SetColumnWidth($hListView, 3, 200)
; 데이터베이스 생성/연결
Global $hDB = _SQLite_Open("test.db")
If @error Then
MsgBox(16, "Error", "데이터베이스를 열 수 없습니다.")
Exit
EndIf
; 테이블 생성
_SQLite_Exec($hDB, "CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, city TEXT);")
; 초기 데이터 로드
LoadData()
GUISetState(@SW_SHOW)
; 메인 루프
While 1
Switch GUIGetMsg()
Case $GUI_EVENT_CLOSE
ExitLoop
Case $btnInsert
InsertData()
Case $btnUpdate
UpdateData()
Case $btnDelete
DeleteData()
EndSwitch
WEnd
; 정리
_SQLite_Close($hDB)
_SQLite_Shutdown()
GUIDelete($hGUI)
; 함수들
Func LoadData()
_GUICtrlListView_DeleteAllItems($hListView)
Local $aResult, $iRows, $iCols
_SQLite_GetTable2d($hDB, "SELECT * FROM people;", $aResult, $iRows, $iCols)
If $iRows > 0 Then
For $i = 1 To $iRows
_GUICtrlListView_AddItem($hListView, $aResult[$i][0])
_GUICtrlListView_AddSubItem($hListView, $i-1, $aResult[$i][1], 1)
_GUICtrlListView_AddSubItem($hListView, $i-1, $aResult[$i][2], 2)
_GUICtrlListView_AddSubItem($hListView, $i-1, $aResult[$i][3], 3)
Next
EndIf
EndFunc
Func InsertData()
Local $sName = GUICtrlRead($inputName)
Local $iAge = GUICtrlRead($inputAge)
Local $sCity = GUICtrlRead($inputCity)
If $sName <> "" And $iAge <> "" And $sCity <> "" Then
_SQLite_Exec($hDB, "INSERT INTO people (name, age, city) VALUES ('" & $sName & "', " & $iAge & ", '" & $sCity & "');")
GUICtrlSetData($inputName, "")
GUICtrlSetData($inputAge, "")
GUICtrlSetData($inputCity, "")
LoadData()
Else
MsgBox(48, "Warning", "모든 필드를 입력해주세요.")
EndIf
EndFunc
Func UpdateData()
Local $iSelected = _GUICtrlListView_GetSelectedIndices($hListView)
If $iSelected <> "" Then
Local $iID = _GUICtrlListView_GetItemText($hListView, $iSelected, 0)
Local $sName = GUICtrlRead($inputName)
Local $iAge = GUICtrlRead($inputAge)
Local $sCity = GUICtrlRead($inputCity)
If $sName <> "" And $iAge <> "" And $sCity <> "" Then
_SQLite_Exec($hDB, "UPDATE people SET name = '" & $sName & "', age = " & $iAge & ", city = '" & $sCity & "' WHERE id = " & $iID & ";")
GUICtrlSetData($inputName, "")
GUICtrlSetData($inputAge, "")
GUICtrlSetData($inputCity, "")
LoadData()
Else
MsgBox(48, "Warning", "모든 필드를 입력해주세요.")
EndIf
Else
MsgBox(48, "Warning", "수정할 항목을 선택해주세요.")
EndIf
EndFunc
Func DeleteData()
Local $iSelected = _GUICtrlListView_GetSelectedIndices($hListView)
If $iSelected <> "" Then
Local $iID = _GUICtrlListView_GetItemText($hListView, $iSelected, 0)
_SQLite_Exec($hDB, "DELETE FROM people WHERE id = " & $iID & ";")
LoadData()
Else
MsgBox(48, "Warning", "삭제할 항목을 선택해주세요.")
EndIf
EndFunc
