User Module: SQL SELECT Query builder
Posted: Fri Jan 22, 2016 5:31 am
A little module to allow users to build simple SELECT queries against an SQLite database. Update 28/01/2016 thanks Amilcar
First the Module
Simple Query Builder.pbi
Now a little application to show usage.
frmMain.pb
First the Module
Simple Query Builder.pbi
Code: Select all
;{ ==Code Header Comment==============================
; Name/title: SimpleQueryBuilder.pbi
; Executable name: SimpleQueryBuilder.exe
; Version: 1.0
; Author: Collectordave
; Collaborators: Amílcar Matos Pérez
; Translation by:
; Create date: 24\Jan\2016
; Previous releases:
; This Release Date:
; Operating system: Windows [X]GUI
; Compiler version: PureBasic 5.41 (x64), PureBasic 5.4LTS(X86)
; Copyright: (C)2016
; License:
; Libraries:
; English Forum:
; French Forum:
; German Forum:
; Tested platforms: Windows
; Description: Builds an sqlite query string.
; ====================================================
;.......10........20........30........40........50........60........70........80
;}
DeclareModule QueryBuilder
Global Window_ID.l
Global OkPressed.i = #False
Global Query.s
Declare Open(MyDB.s)
Declare Event_Handler(Event)
EndDeclareModule
Module QueryBuilder
EnableExplicit
UseSQLiteDatabase()
;Constant Declarations
#Zero = 0
#InsertAtTheEnd = -1
#GetLastItem = -1
#UnknownColumnType = 0
#NumericColumnType = 1
#TextColumnType = 2
#BlobColumnType = 3
#HeaderText = -1
#FirstColumn = 0
#DefaultColumnWidth = 100
#StringEQ = 1
#StringLike = 2
#NumberEQ = 3
#NumberLT = 4
#NumberLTEQ = 5
#NumberGT = 6
#NumberGTEQ = 7
#ListViewSelected = 1
;Where Clause Gadgets
Structure UserClause
strFieldID.i
strClauseID.i
strConditionID.i
EndStructure
Global Dim WhereClause.UserClause(3)
Structure FData
Name.s
Type.i
EndStructure
Global Dim FieldData.FData(0)
Global QueryDB.l
Global Query.s
Global Flags.l
;String Gadgets
Global strQuery.i
Global strC1Condition.i
Global strC2Condition.i
Global strC3Condition.i
;Buttons
Global btnAllFields.i
Global btnBuild.i
Global btnCancel.i
Global btnNew.i
Global btnOk.i
Global btnSelectedFields.i
Global btnTest.i
Global btnC1FieldSelect.i
Global btnC1FieldClear.i
Global btnC2FieldSelect.i
Global btnC2FieldClear.i
Global btnC3FieldSelect.i
Global btnC3FieldClear.i
Global btnDeleteFieldFromSelectedFieldList.i
;Combos and Lists
Global LstResult.i
Global cmbTables.i
Global lstAvailableFields.i
Global lstSelectedFields.i
Procedure.l Open_Database(DBName.s)
; Procedure to open the requested database and return a handle to it.
; User message provided in case of failure to open.
; A handle of zero means failure to open.
Define DBHnd.l
;Open Main Database
DBHnd.l = OpenDatabase(#PB_Any, DBName.s, #Empty$, #Empty$)
If DBHnd.l = #Zero
MessageRequester("Database Error","Failed to open database!", #MB_ICONERROR)
ProcedureReturn #False
Else
ProcedureReturn DBHnd.l
EndIf
EndProcedure
Procedure.l ShowTables()
; Fills the Tables combobox with all the available tables and views in the database.
; QueryDB is a global variable with the handle to the opened database.
Define ObjType.s ; is the type of each entry on the master table.
ClearGadgetItems(cmbTables)
DatabaseQuery(QueryDB, "SELECT * FROM sqlite_master;")
FirstDatabaseRow(QueryDB)
ObjType = GetDatabaseString(QueryDB, DatabaseColumnIndex(QueryDB, "type"))
If ObjType = "table" Or ObjType = "view"
AddGadgetItem(cmbTables, #InsertAtTheEnd, GetDatabaseString(QueryDB, DatabaseColumnIndex(QueryDB, "tbl_name")))
EndIf
While NextDatabaseRow(QueryDB)
ObjType = GetDatabaseString(QueryDB, DatabaseColumnIndex(QueryDB, "type"))
If ObjType = "table" Or ObjType = "view"
AddGadgetItem(cmbTables, #InsertAtTheEnd, GetDatabaseString(QueryDB, DatabaseColumnIndex(QueryDB, "tbl_name")))
EndIf
Wend
FinishDatabaseQuery(QueryDB) ;free the query
ProcedureReturn #True
EndProcedure
Procedure.i GetColumnType(FieldNum.i)
Select DatabaseColumnType(QueryDB, FieldNum)
Case #PB_Database_Blob
ProcedureReturn #BlobColumnType
Case #PB_Database_Long, #PB_Database_Float, #PB_Database_Double, #PB_Database_Quad
ProcedureReturn #NumericColumnType
Case #PB_Database_String
ProcedureReturn #TextColumnType
Default
ProcedureReturn #UnknownColumnType
EndSelect
; Should never get here.
EndProcedure
Procedure ShowFields(TableName.s)
; Fills the AvailableFields list gadget with all the fields in the user selected table.
; Also fills the FieldData structured array.
; This procedure gets the fields data (name and type) from the table first row.
; QueryDB is a global variable with the handle to the opened database.
ClearGadgetItems(lstAvailableFields)
Define iloop.i = #Zero
Define txt.s = #Empty$
DatabaseQuery(QueryDB, "SELECT * FROM " + TableName + ";")
FirstDatabaseRow(QueryDB)
ReDim FieldData(DatabaseColumns(QueryDB))
For iloop = 0 To DatabaseColumns(QueryDB) -1
txt = DatabaseColumnName(QueryDB, iloop)
FieldData(iloop)\Name = DatabaseColumnName(QueryDB, iloop)
FieldData(iloop)\Type = GetColumnType(iloop)
AddGadgetItem(lstAvailableFields, #InsertAtTheEnd, txt)
Next iloop
FinishDatabaseQuery(QueryDB) ;free the query
ProcedureReturn #True
EndProcedure
Procedure.l LoadOperators(CmbID.i, Type.i)
; This procedure fills the Clause combobox in the Where section.
; For each item in the combobox a data item is added. Later that data item
; will be used to build the where clause of the query string.
; Although the combobox content is inserted at the end of the list, the data item
; is paired with each string item.
ClearGadgetItems(WhereClause(CmbID)\strClauseID)
Select Type
Case #NumericColumnType
AddGadgetItem (WhereClause(CmbID)\strClauseID, #InsertAtTheEnd, "=" )
SetGadgetItemData(WhereClause(CmbID)\strClauseID, 0 , #NumberEQ )
AddGadgetItem (WhereClause(CmbID)\strClauseID, #InsertAtTheEnd, "<" )
SetGadgetItemData(WhereClause(CmbID)\strClauseID, 1 , #NumberLT )
AddGadgetItem (WhereClause(CmbID)\strClauseID, #InsertAtTheEnd, "<=" )
SetGadgetItemData(WhereClause(CmbID)\strClauseID, 2 , #NumberLTEQ)
AddGadgetItem (WhereClause(CmbID)\strClauseID, #InsertAtTheEnd, ">" )
SetGadgetItemData(WhereClause(CmbID)\strClauseID, 3 , #NumberGT )
AddGadgetItem (WhereClause(CmbID)\strClauseID, #InsertAtTheEnd, ">=" )
SetGadgetItemData(WhereClause(CmbID)\strClauseID, 4 , #NumberGTEQ)
Case #TextColumnType
AddGadgetItem (WhereClause(CmbID)\strClauseID, #InsertAtTheEnd, "=" )
SetGadgetItemData(WhereClause(CmbID)\strClauseID, 0 , #StringEQ )
AddGadgetItem (WhereClause(CmbID)\strClauseID, #InsertAtTheEnd, "LIKE" )
SetGadgetItemData(WhereClause(CmbID)\strClauseID, 1 , #StringLike)
EndSelect
EndProcedure
Procedure.l Clear_Gadgets()
; Reset all form gadgets to default state
Define iloop.i = #Zero
Define ColCount.i = #Zero
SetGadgetText(strQuery, #Empty$)
ClearGadgetItems(lstAvailableFields)
ClearGadgetItems(lstSelectedFields)
SetGadgetText(WhereClause(0)\strFieldID , #Empty$)
SetGadgetText(WhereClause(1)\strFieldID , #Empty$)
SetGadgetText(WhereClause(2)\strFieldID , #Empty$)
SetGadgetText(WhereClause(0)\strConditionID, #Empty$)
SetGadgetText(WhereClause(1)\strConditionID, #Empty$)
SetGadgetText(WhereClause(2)\strConditionID, #Empty$)
DisableGadget(WhereClause(0)\strConditionID, #True)
DisableGadget(WhereClause(1)\strConditionID, #True)
DisableGadget(WhereClause(2)\strConditionID, #True)
ClearGadgetItems(WhereClause(0)\strClauseID)
ClearGadgetItems(WhereClause(1)\strClauseID)
ClearGadgetItems(WhereClause(2)\strClauseID)
DisableGadget(WhereClause(0)\strClauseID, #True)
DisableGadget(WhereClause(1)\strClauseID, #True)
DisableGadget(WhereClause(2)\strClauseID, #True)
ClearGadgetItems(lstResult)
;Count the columns in the lstResult ListIcongadget
While GetGadgetItemText(lstResult, #GetLastItem, ColCount) < > #Empty$
ColCount = ColCount + 1 ; count the column
Wend
;Remove all columns except #FirstColumn
For iloop = ColCount To #FirstColumn + 1 Step -1
RemoveGadgetColumn(lstResult, iloop)
Next iloop
SetGadgetItemText(lstResult, #HeaderText, "Results" , #FirstColumn)
ProcedureReturn #True
EndProcedure
Procedure UpDateTableData(BQuery.s)
; Procedure to update the results viewer with the execution results of the database query.
; BQuery is a placeholder for the content of the strQuery gadget.
; Query is executed and with the returning data the results viewer is filled.
; This procedure also creates the necessary columns in the results viewer.
If Trim(BQuery) = #Empty$
; just in case there is no query string.
ProcedureReturn #False
EndIf
Define iloop.i = #Zero
ClearGadgetItems(lstResult)
DatabaseQuery(QueryDB, BQuery.s)
FirstDatabaseRow(QueryDB)
; sets the first column title.
SetGadgetItemText(lstResult, #InsertAtTheEnd, DatabaseColumnName(QueryDB, #FirstColumn) , #FirstColumn)
; assign column titles and width to the Result list icon.
For iloop = 1 To DatabaseColumns(QueryDB) - 1
AddGadgetColumn(lstResult, iloop, DatabaseColumnName(QueryDB, iloop), #DefaultColumnWidth)
Next iloop
; get first full record from the data table.
Define txt.s = #Empty$
txt = GetDatabaseString(QueryDB, #FirstColumn) + #LF$
For iloop = 1 To DatabaseColumns(QueryDB) - 1
txt = txt + GetDatabaseString(QueryDB, iloop) + #LF$
Next iloop
AddGadgetItem(lstResult, #InsertAtTheEnd, txt)
While NextDatabaseRow(QueryDB)
txt = #Empty$
For iloop = 0 To DatabaseColumns(QueryDB) - 1
txt = txt + GetDatabaseString(QueryDB, iloop) + #LF$
Next iloop
AddGadgetItem(lstResult, #InsertAtTheEnd, txt)
Wend
FinishDatabaseQuery(QueryDB) ;free the query
ProcedureReturn #True
EndProcedure
Procedure.l BuildQuery()
; This procedure reads form fields and builds the query string.
; This procedure outputs the query string only to the 'strQuery' gadget.
Define FirstClause.i = #True
Define iLoop.i
If CountGadgetItems(lstSelectedFields) > #Zero
;Build Query
Query = "SELECT "
Query = Query + GetGadgetItemText(lstSelectedFields, 0)
For iloop = 1 To CountGadgetItems(lstSelectedFields) - 1
Query = Query + ", " + GetGadgetItemText(lstSelectedFields, iloop)
Next iloop
;Add table name to the query string.
Query = Query + " FROM " + GetGadgetText(cmbTables)
Define Field.s, Clause.s, Condition.s
;Add WHERE Clause
For iLoop = 0 To 2
Field = GetGadgetText(WhereClause(iLoop)\strFieldID )
Clause = GetGadgetText(WhereClause(iLoop)\strClauseID )
Condition = GetGadgetText(WhereClause(iLoop)\strConditionID)
If Field > #Empty$ And
Clause > #Empty$ And
Condition > #Empty$
If FirstClause = #True
Query = Query + " WHERE "
FirstClause = #False
Else
Query = Query + " AND "
EndIf
Select GetGadgetItemData(WhereClause(iLoop)\strClauseID, GetGadgetState(WhereClause(iLoop)\strClauseID))
Case #StringEQ
Query = Query + Field + " = '" + Condition + "' "
Case #StringLike
Query = Query + Field + " LIKE '%" + Condition + "%'"
Case #NumberEQ
Query = Query + Field + " = " + Condition
Case #NumberLT
Query = Query + Field + " < " + Condition
Case #NumberLTEQ
Query = Query + Field + " <= " + Condition
Case #NumberGT
Query = Query + Field + " > " + Condition
Case #NumberGTEQ
Query = Query + Field + " >= " + Condition
EndSelect
EndIf
Next iLoop
Query = Query + ";"
SetGadgetText(strQuery, Query)
Else
MessageRequester("Info","At least one field must be selected.", #MB_ICONWARNING)
EndIf
ProcedureReturn #True
EndProcedure
Procedure Open(MyDB.s)
; This procedure opens the user selected database, plus it presents
; the query building form on the screen.
OkPressed = #False
QueryDB = Open_Database(MyDB)
If QueryDB = #False
ProcedureReturn #False
EndIf
Flags = #PB_Window_TitleBar | #PB_Window_Tool | #PB_Window_ScreenCentered
Window_ID = OpenWindow(#PB_Any, 50, 150, 790, 400, "Simple Query Builder", Flags)
TextGadget(#PB_Any, 10, 10, 150, 20, "Select Table\View")
cmbTables = ComboBoxGadget(#PB_Any, 10, 30, 150, 20)
TextGadget(#PB_Any, 170, 10, 150, 20, "Query")
strQuery = StringGadget(#PB_Any, 170, 30, 610, 20, #Empty$)
TextGadget(#PB_Any, 10, 70, 150, 20, "Available Fields")
lstAvailableFields = ListViewGadget(#PB_Any, 10, 90, 150, 170, #PB_ListView_MultiSelect)
btnSelectedFields = ButtonGadget(#PB_Any, 170, 90, 30, 30, ">")
GadgetToolTip(btnSelectedFields, "Add Selected")
btnAllFields = ButtonGadget(#PB_Any, 170, 130, 30, 30, ">>")
GadgetToolTip(btnAllFields, "Add All")
btnDeleteFieldFromSelectedFieldList = ButtonGadget(#PB_Any, 170, 170, 30, 30, "<")
GadgetToolTip(btnDeleteFieldFromSelectedFieldList, "Delete selected item.")
TextGadget(#PB_Any, 210, 70, 150, 20, "Selected Fields")
lstSelectedFields = ListViewGadget(#PB_Any, 210, 90, 150, 170)
btnBuild = ButtonGadget(#PB_Any, 370, 230, 70, 30, "Build" )
btnTest = ButtonGadget(#PB_Any, 450, 230, 70, 30, "Test" )
btnNew = ButtonGadget(#PB_Any, 530, 230, 70, 30, "New" )
btnOk = ButtonGadget(#PB_Any, 630, 230, 70, 30, "Ok" )
btnCancel = ButtonGadget(#PB_Any, 710, 230, 70, 30, "Cancel")
GadgetToolTip(btnBuild , "Builds the query using the selected fields and conditions.")
GadgetToolTip(btnTest , "Test query after build." )
GadgetToolTip(btnNew , "Clears all the user entry fields." )
GadgetToolTip(btnOk , "Return the query." )
GadgetToolTip(btnCancel, "Close without making any changes." )
TextGadget(#PB_Any, 370, 70, 150, 20, "WHERE")
btnC1FieldClear = ButtonGadget(#PB_Any, 370, 90, 30, 20, "<")
GadgetToolTip(btnC1FieldClear, "Clear Field")
btnC1FieldSelect = ButtonGadget(#PB_Any, 410, 90, 30, 20, ">")
GadgetToolTip(btnC1FieldSelect, "Select Field")
WhereClause(0)\strFieldID = StringGadget(#PB_Any, 450, 90, 110, 20, #Empty$, #PB_String_ReadOnly)
SetGadgetColor(WhereClause(0)\strFieldID, #PB_Gadget_BackColor, #White)
GadgetToolTip (WhereClause(0)\strFieldID, "Selected Field Read Only")
WhereClause(0)\strClauseID = ComboBoxGadget(#PB_Any, 570, 90, 60, 20)
GadgetToolTip(WhereClause(0)\strClauseID, "Clause options")
DisableGadget(WhereClause(0)\strClauseID, #True)
WhereClause(0)\strConditionID = StringGadget(#PB_Any, 640, 90, 140, 20, #Empty$)
DisableGadget(WhereClause(0)\strConditionID, #True)
btnC2FieldClear = ButtonGadget(#PB_Any, 370, 120, 30, 20, "<")
GadgetToolTip(btnC2FieldClear, "Clear Field")
btnC2FieldSelect = ButtonGadget(#PB_Any, 410, 120, 30, 20, ">")
GadgetToolTip(btnC2FieldSelect, "Select Field")
WhereClause(1)\strFieldID = StringGadget(#PB_Any, 450, 120, 110, 20, #Empty$, #PB_String_ReadOnly)
SetGadgetColor(WhereClause(1)\strFieldID, #PB_Gadget_BackColor, #White)
GadgetToolTip (WhereClause(1)\strFieldID, "Selected Field Read Only")
WhereClause(1)\strClauseID = ComboBoxGadget(#PB_Any, 570, 120, 60, 20)
GadgetToolTip(WhereClause(1)\strClauseID, "Clause options")
DisableGadget(WhereClause(1)\strClauseID, #True)
WhereClause(1)\strConditionID = StringGadget(#PB_Any, 640, 120, 140, 20, #Empty$)
DisableGadget(WhereClause(1)\strConditionID, #True)
btnC3FieldClear = ButtonGadget(#PB_Any, 370, 150, 30, 20, "<")
GadgetToolTip(btnC3FieldClear, "Clear Field")
btnC3FieldSelect = ButtonGadget(#PB_Any, 410, 150, 30, 20, ">")
GadgetToolTip(btnC3FieldSelect, "Select Field")
WhereClause(2)\strFieldID = StringGadget(#PB_Any, 450, 150, 110, 20, #Empty$, #PB_String_ReadOnly)
SetGadgetColor(WhereClause(2)\strFieldID, #PB_Gadget_BackColor, #White)
GadgetToolTip (WhereClause(2)\strFieldID, "Selected Field Read Only")
WhereClause(2)\strClauseID = ComboBoxGadget(#PB_Any, 570, 150, 60, 20)
GadgetToolTip(WhereClause(2)\strClauseID, "Clause options")
DisableGadget(WhereClause(2)\strClauseID, #True)
WhereClause(2)\strConditionID = StringGadget(#PB_Any, 640, 150, 140, 20, #Empty$)
DisableGadget(WhereClause(2)\strConditionID, #True)
lstResult = ListIconGadget(#PB_Any, 0, 270, 790, 130, "Results", 100, #PB_ListIcon_GridLines)
ShowTables()
StickyWindow(Window_ID, #True)
EndProcedure
Procedure.l ClearResultsViewer()
; Procedure to clear and reset to default state the Results viewer only.
Protected ColCount.l
Protected iloop.l
ClearGadgetItems(lstResult)
While GetGadgetItemText(lstResult, #GetLastItem, ColCount) < > #Empty$
ColCount = ColCount + 1 ; count the column
Wend
For iloop = ColCount To 1 Step -1 ;Do not remove column zero.
RemoveGadgetColumn(lstResult, iloop)
Next iloop
SetGadgetItemText(lstResult, #HeaderText, "Results" , #FirstColumn)
EndProcedure
Procedure.l DeleteSelectedField()
; Procedure to delete one item at a time from the selected fields list.
; List view is not multi-select so only one item at a time can be deleted.
; Making the list view multi select might affect the Where clause procedures.
Protected Dim SelectedFields$(1) ; contains the items that will be retained.
Protected CurrentItem.l ; Loop counter
Protected SelectedItemsCount.l ; Loop controlling variable.
Protected RowCount.l ; for the array rows.
Protected CurrentRow.l ; Loop counter
SelectedItemsCount = CountGadgetItems(lstSelectedFields) - 1
If SelectedItemsCount < #Zero
; no fields inside the list view, user is playing.
ProcedureReturn #False
EndIf
ReDim SelectedFields$(SelectedItemsCount)
; Fill array with lstSelectedFields content.
RowCount = #Zero
For CurrentItem = 0 To SelectedItemsCount
If GetGadgetItemState(lstSelectedFields, CurrentItem) <> #ListViewSelected
; get only deselected items.
SelectedFields$(RowCount) = GetGadgetItemText(lstSelectedFields, CurrentItem)
RowCount = RowCount + 1
EndIf
Next CurrentItem
; clear the gadget
ClearGadgetItems(lstSelectedFields)
; Fill it anew.
For CurrentRow = 0 To RowCount - 1
AddGadgetItem(lstSelectedFields, #InsertAtTheEnd, SelectedFields$(CurrentRow))
Next CurrentRow
ProcedureReturn #True
EndProcedure
Procedure Event_Handler(Event)
Define iLoop.i
Select Event
Case #PB_Event_Gadget
Select EventGadget()
Case btnOk
If GetGadgetText(strQuery) > #Empty$
OkPressed = #True
QueryBuilder::Query = GetGadgetText(strQuery)
CloseWindow(Window_ID)
Window_ID = -1
Else
MessageRequester("Error", "No Query entered.", #MB_ICONERROR)
SetActiveGadget(strQuery)
EndIf
Case btncancel
OkPressed = #False
QueryBuilder::Query = #Empty$
CloseWindow(Window_ID)
Window_ID = -1
Case btnNew
Clear_Gadgets()
Case cmbTables
ShowFields(GetGadgetItemText(cmbTables, GetGadgetState(cmbTables)))
Case btnAllFields
If CountGadgetItems(lstAvailableFields) = #Zero
MessageRequester ("Info", "Select at least one table or view first.", #MB_ICONINFORMATION)
Else
ClearGadgetItems(lstSelectedFields)
For iLoop = 0 To CountGadgetItems(lstAvailableFields) -1
AddGadgetItem(lstSelectedFields, #InsertAtTheEnd, GetGadgetItemText(lstAvailableFields, iloop))
Next iLoop
EndIf
Case btnSelectedFields
For iLoop = 0 To CountGadgetItems(lstAvailableFields) -1
If GetGadgetItemState(lstAvailableFields, iLoop) = #ListViewSelected
AddGadgetItem(lstSelectedFields, #InsertAtTheEnd, GetGadgetItemText(lstAvailableFields, iLoop))
EndIf
Next iLoop
Case btnDeleteFieldFromSelectedFieldList
DeleteSelectedField()
Case btnC1FieldSelect
If GetGadgetText(lstSelectedFields) > #Empty$
SetGadgetText(WhereClause(0)\strFieldID, GetGadgetText(lstSelectedFields))
EndIf
For iloop = 0 To CountGadgetItems(lstAvailableFields) - 1
If Trim(GetGadgetItemText(lstAvailableFields, iloop)) = Trim(GetGadgetText(WhereClause(0)\strFieldID))
LoadOperators(0, FieldData(iloop)\Type)
SetGadgetText(WhereClause(0)\strFieldID, GetGadgetText(lstSelectedFields))
SetGadgetData(WhereClause(0)\strFieldID, FieldData(iloop)\Type)
;Now enable clause and condition screen fields.
DisableGadget(WhereClause(0)\strClauseID , #False)
DisableGadget(WhereClause(0)\strConditionID, #False)
Break
EndIf
Next iloop
Case btnC2FieldSelect
If GetGadgetText(lstSelectedFields) > #Empty$
SetGadgetText(WhereClause(1)\strFieldID, GetGadgetText(lstSelectedFields))
EndIf
For iloop = 0 To CountGadgetItems(lstAvailableFields) - 1
If Trim(GetGadgetItemText(lstAvailableFields, iloop)) = Trim(GetGadgetText(WhereClause(1)\strFieldID))
LoadOperators(1, FieldData(iloop)\Type)
;Now enable clause and condition screen fields.
DisableGadget(WhereClause(1)\strClauseID , #False)
DisableGadget(WhereClause(1)\strConditionID, #False)
Break
EndIf
Next iloop
Case btnC3FieldSelect
If GetGadgetText(lstSelectedFields) > #Empty$
SetGadgetText(WhereClause(2)\strFieldID, GetGadgetText(lstSelectedFields))
EndIf
For iloop = 0 To CountGadgetItems(lstAvailableFields) - 1
If Trim(GetGadgetItemText(lstAvailableFields, iloop)) = Trim(GetGadgetText(WhereClause(2)\strFieldID))
LoadOperators(2, FieldData(iloop)\Type)
;Now enable clause and condition screen fields.
DisableGadget(WhereClause(2)\strClauseID , #False)
DisableGadget(WhereClause(2)\strConditionID, #False)
Break
EndIf
Next iloop
Case btnBuild
BuildQuery()
Case btnTest
ClearResultsViewer()
UpDateTableData(GetGadgetText(strQuery))
Case btnC1FieldClear
SetGadgetText (WhereClause(0)\strFieldID , #Empty$)
SetGadgetText (WhereClause(0)\strConditionID, #Empty$)
DisableGadget (WhereClause(0)\strConditionID, #True )
ClearGadgetItems(WhereClause(0)\strClauseID )
DisableGadget (WhereClause(0)\strClauseID , #True )
Case btnC2FieldClear
SetGadgetText (WhereClause(1)\strFieldID , #Empty$)
SetGadgetText (WhereClause(1)\strConditionID, #Empty$)
DisableGadget (WhereClause(1)\strConditionID, #True )
ClearGadgetItems(WhereClause(1)\strClauseID )
DisableGadget (WhereClause(1)\strClauseID , #True )
Case btnC3FieldClear
SetGadgetText (WhereClause(2)\strFieldID , #Empty$)
SetGadgetText (WhereClause(2)\strConditionID, #Empty$)
DisableGadget (WhereClause(2)\strConditionID, #True )
ClearGadgetItems(WhereClause(2)\strClauseID )
DisableGadget (WhereClause(2)\strClauseID , #True )
EndSelect ;EventGadget()
EndSelect ;Event
EndProcedure
EndModule
frmMain.pb
Code: Select all
EnableExplicit
IncludeFile "Simple Query Builder.pbi"
Global frmMain.i
Global btnSelectDB, strDBName, btnBuildQuery, strQuery
Global MyDB.s,Pattern.s
Define Event.i
Procedure Event_Handler(Event)
Select Event
Case #PB_Event_CloseWindow
End
Case #PB_Event_Gadget
Select EventGadget()
Case btnSelectDB
Pattern = "Database (*.db)|*.db;|All files (*.*)|*.*"
MyDB = OpenFileRequester("Please choose Database", GetCurrentDirectory(), Pattern, 0)
If MyDB
SetGadgetText(strDBName,MyDB)
Else
SetGadgetText(strDBName,"")
EndIf
Case btnBuildQuery
If Not IsWindow(QueryBuilder::Window_ID)
If MyDB
QueryBuilder::Open(MyDB)
Else
MessageRequester("Information","No Database Selected!",#PB_MessageRequester_Ok)
SetActiveGadget(btnSelectDB)
EndIf
Else
SetActiveWindow(QueryBuilder::Window_ID)
EndIf
EndSelect
EndSelect
EndProcedure
frmMain = OpenWindow(#PB_Any, 20, 20, 600, 70, "", #PB_Window_SystemMenu)
btnSelectDB = ButtonGadget(#PB_Any, 10, 10, 100, 20, "Select Database")
strDBName = StringGadget(#PB_Any, 120, 10, 470, 20, "")
btnBuildQuery = ButtonGadget(#PB_Any, 10, 40, 100, 20, "Build Query")
strQuery = StringGadget(#PB_Any, 120, 40, 470, 20, "")
MyDB = ""
Repeat
Event = WaitWindowEvent()
Select EventWindow()
Case frmMain
Event_Handler(Event)
Case QueryBuilder::Window_ID
QueryBuilder::Event_Handler(Event)
;If User closed query builder grab query if there and display
If Not IsWindow(QueryBuilder::Window_ID)
If QueryBuilder::OkPressed = #True
SetGadgetText(strQuery,QueryBuilder::Query)
Else
SetGadgetText(strQuery,"")
EndIf
EndIf
EndSelect
ForEver