I created this script for testing SQL Commands & Queries with SQLite.ahk (
http://www.autohotkey.com/forum/topic12752.html).
On opening, the only commands it sends to SQLite are to Startup SQLite and to Open the Database.
The first time the CREATE TABLE command is executed, the table created is set as the variable TableName. If you're creating multiple tables, you'll want to create your main table first.
If "SELECT" is present in your Query/Command, then the _SQLite_GetTable function is used, otherwise the _SQLite_Exec function is used, and then the query "SELECT * FROM TableName" is ran, to display your changes.
The ListView's column headers are dynamically added from the column names returned from the SELECT query. This could also be used in non-SQL GUIs to create dynamic ListView column headers, if needed.
Some of this code is modified from searches I've done in the forums, so I'm not claiming to have created this entirely from scratch.
Feel free to modify and redistribute.
Code:
#SingleInstance, Force
#Include, SQLite.ahk
DBFileName = TEST.DB
IfExist, %DBFileName%
{
SB_SetText("Deleting Current Database")
FileDelete, %DBFileName%
}
Gui, Add, Text, x6 y7 w100 h20 , Query/Command:
Gui, Add, Edit, x106 y7 w590 h20 vSQL,
Gui, Add, Button, x706 y7 w80 h20 gRunQC, Run
Gui, Add, GroupBox, x6 y37 w780 h330 , Results
Gui, Add, ListView, x16 y57 w760 h300 vResultsLV,
Gui, Add, StatusBar,,
Gui, Show, h389 w795, SQL Query/Command ListView Function GUI
GoSub, StartupOpen
Return
GuiClose:
RC := _SQLite_CloseDB($SQLITE_h_DB)
If (RC <> 0)
MsgBox, Error Closing Database: %RC%`n$SQLITE_s_ERROR
RC := _SQLite_Shutdown()
ExitApp
StartupOpen:
RC := _SQLite_Startup()
If (RC <> 0)
SB_SetText("Error Starting SQLite: " . RC)
Else
SB_SetText("SQLite Startup")
RC := _SQLite_OpenDB(DBFileName)
If (RC <> 0)
SB_SetText("Error Opening Database: " . RC)
Else
SB_SetText("Database Opened")
Return
RunQC:
GuiControlGet, SQL
If SQL is space
{
SB_SetText("No Text Entered")
Return
}
SQL := SQL . ";"
If TableName is space
{
IfInString, SQL, CREATE TABLE
{
StringSplit, TableName, SQL, %A_Space%
TableName := TableName3
}
}
IfInString, SQL, SELECT
SQLType := "Query"
Else
SQLType := "Command"
If SQLType = Query
{
SB_SetText("Running Query: " . SQL)
RC := _SQLite_GetTable(-1, SQL, Result, $iRows, $iCols)
If (RC <> 0)
SB_SetText("Error GETTABLE: " . $SQLITE_h_DB . " " . RC . " " . $SQLITE_s_ERROR)
Gui, Listview, ResultsLV
LV_Delete()
ColumnCount := LV_GetCount("Column")
Loop, %ColumnCount%
{
LV_DeleteCol(1)
}
Loop, Parse, Result, `n
{
If (A_Index = 1)
{
Loop, Parse, A_LoopField, |
LV_InsertCol(A_Index,"",A_LoopField)
}
Else
{
row := LV_Add("", "")
Loop, Parse, A_LoopField, |
{
LV_Modify(row, "Col" . A_Index, A_LoopField)
LV_ModifyCol(A_Index, "AutoHdr")
}
}
}
SB_SetText("")
}
Else
{
SB_SetText("Executing Command: " . SQL)
RC := _SQLite_Exec(-1, SQL)
If (RC <> 0)
{
SB_SetText("Error EXEC: " . RC . " | " . $SQLITE_s_ERROR)
}
SQL := "SELECT * FROM " . TableName . ";"
RC := _SQLite_GetTable(-1, SQL, Result, $iRows, $iCols)
If (RC <> 0)
SB_SetText("Error GETTABLE: " . $SQLITE_h_DB . " " . RC . " | " . $SQLITE_s_ERROR)
Gui, Listview, ResultsLV
LV_Delete()
ColumnCount := LV_GetCount("Column")
Loop, %ColumnCount%
{
LV_DeleteCol(1)
}
Loop, Parse, Result, `n
{
If (A_Index = 1)
{
Loop, Parse, A_LoopField, |
LV_InsertCol(A_Index,"",A_LoopField)
}
Else
{
row := LV_Add("", "")
Loop, Parse, A_LoopField, |
{
LV_Modify(row, "Col" . A_Index, A_LoopField)
LV_ModifyCol(A_Index, "AutoHdr")
}
}
}
SB_SetText("")
}
Return
Here is a small list of commands/queries to run (in order) to test it's functionality:
Code:
CREATE TABLE Test (ID, Name, Age)
INSERT INTO Test VALUES(1, 'Bob', 28)
INSERT INTO Test VALUES(2, 'Anna', 26)
INSERT INTO Test VALUES(3, 'George', 19)
SELECT * FROM Test WHERE Age>21
Login
Register
FAQ
Search
