A database is a file on the hard drive that has a structure to it so that it can hold large amounts of information and access it quickly.
SQLite is one type of database. It was written by Dwayne Richard Hipp (born 1961 in North Carolina). It was first released in August 2000. It is public domain, meaning anyone can use it free of charge. Google Chrome, Firefox, the Android operating system for smartphones, Skype, Adobe Reader and the iPhone all use SQLite. It’s just nice. And you pronounce it “S Q L Lite”, so saith Wikipedia.
Databases store information in tables. Gambas has a tableview. This, too, has rows and columns. You can think of a database table as an invisible tableview in the database file.
For example, a teaching might have a database with a Students table. In that table there is a row for every student. Looking across the row you seeStudentID, FirstName, LastName, Sex, DateOfBirth, Address, PhoneNumber. These are the fields. They are the columns.
| StudentID | FirstName | LastName | Sex | DateOfBirth | Address | PhoneNumber |
| 2019001 | Mary | Smith | F | 2008-06-23 | 21 Holly Crt, Bundaberg | 07324657 |
| 2019002 | Jim | Jones | M | 2003-02-19 | 14 Primrose St, Bundaberg | 07123456 |
| 2019003 | Lucy | Watkins | F | 2003-10-05 | 5 Flower St, Bundaberg | 07938276 |
This could be a TableView, or a Table in a Database file.
Every database table has to have aPrimary Key. Every record must have a unique value for this field: one that no one else shares. The simplest is to call itRecID and number 1, 2, 3... etc. In the table above, the primary key is going to be theStudentID and it is an integer. The first four digits are the year of enrolment. (We could have another column forYearOfEnrolment and just use a sequence number for theStudentID.)
In SQLite all data is stored as strings, even though you might specify some columns as integers, others as strings and others as dates. SQLite is very forgiving: you can put things that aren’t numbers into integer columns and so on, but try not to. Empty cells are NULL. Try to avoid those, too. When you make a new blank record, initialise values to the empty string, “”.
SQLite is a component (optional part) of Gambas. There is also a Database access component. On the Project Menu > Properties… > Components page, be sure to tickgb.db andgb.db.sqlite3. Without these components in your project you will get errors as soon as you try to run your program.
You send messages toSQLite and it sends answers back to you using a special language calledSQL (“S Q L” or “sequel”, pronounce it either way.) This means learning another language, but the simple statements that are used most frequently are not difficult to learn. They are the only ones I know, anyway.SQL was invented by Donald D. Chamberlin and Raymond F. Boyce and first appeared in 1974.SQL is so universal that everyone who writes databases knows of it. It is an international standard.SQLite is one implementation of it.
For example, you might send a message toSQLite saying
SELECT*FROMStudents
This says, “select everything from the Students table”. This gives you the whole table. Or you might only want the students who are male:
SELECT*FROMStudentsWHERESex='M'
Perhaps you want everyone, but you want the females first and the males second:
SELECT*FROMStudentsORDERBYSex
That will get the females first, because “F” comes before “M”. The females will all be in random order and likewise the males unless you write
SELECT*FROMStudentsORDERBYSexDESC,LastNameASCSELECT*FROMStudentsORDERBYSexDESC,LastNameASC
This returns a table to you with males first (alphabetically by surname) followed by females (alphabetically by surname).You might only want the students names, so you could write
SELECTFirstName,LastNameFROMStudentsORDERBYLastName
Perhaps you want only those students who were enrolled in 2019. Now, this is part of the StudentID. You want only those students whose StudentID number starts with “2019”. You use a “wildcard”. The percent (%) sign means “anything here will do”.
SELECTFirstName,LastNameFROMStudentsWHEREStudentIDLIKE'2019%'
When you send theseSELECT statements to the database, SQLite will send you back a table. Gambas calls it aRESULT.Suppose you have a database calleddb1 (as far as Gambas is concerned) and it is attached toMyStudentDatabase.sqlite which is the actual database file on your hard drive. You need a result to store the reply:
DimresasResultres=db1.exec("SELECT * FROM Students")
res has the information you asked for. You might want to print the information, or show it in a tableview, or hold it internally in arrays so you can do calculations on it. You need to cycle through the records thus:
Whileres.Available'do something with res!FirstName, res!LastName and res!DateOfBirth etcres.MoveNextWend
For displaying information in a tableview there is a special event that is triggered each time a cell has to have its contents painted on the screen. It is particularly useful if your set of records is large. The tableview does not have to hold all the information from all the records in itself. It can get the information as it needs it for when it has to be displayed. Be a little careful here: if you are depending on all the information being in the tableview, it may or it may not be all there. This is an example of using the_Data event, getting the information from the result table res when it is needed to display a particular cell in the tableview:
PublicSubTableView1_Data(RowAsInteger,ColumnAsInteger)res.MoveTo(row)IfColumn=0ThenTableView1.Data.Text=res!FirstNameElseTableView1.Data.Text=res!LastNameEndifEnd
Notice the use ofTableView1.Data.Text , which represents the text in the cell.
Notice we haveresult.MoveTo to go to a particular record,result.MoveNext if we are stepping through them one at a time, andresult.Available to check to see if there is another record toMoveNext to. Useful in setting the number of rows to have in your tableview isresult.RecordCount.
Besides accessing the information in the database, with databases you want to be able to:
All but the simplest databases have more than one table in them. Tables can be linked to each other, so records can have signposts in them to indicate lines in other tables that apply. The signpost is therecord ID or otherprimary key of a record in another table. For example, a database of political candidates might have a signpost to the party they belong to. SQL is so smart it can look up the two tables at once to provide you with the information you need, for example this ‘join’ of two tables. (Candidates are in a particular party, and it is the parties that have policies on a variety of issues.)
SELECTCandidate,PolicyOnPensionsFROMCandidates,PartiesWHERECandidate.PartyID=Parties.PartyIDANDCandidates.Electorate="Fairfax"
The next program comes fromhttps://kalaharix.wordpress.com/Gambas/creating-a-databases-and-tables-from-Gambas/ slightly rearranged. It creates a database in your home folder calledTest.sqlite, fills it with random two-digit numbers, then accesses the database to show them in a tableview.
You need a form with a tableview calledtv1. Make it long and thin, as it has 2 columns.
The code is
' Gambas class filePrivatedb1AsNewConnectionPrivatersAsResultPublicSubSetupTableView()tv1.header=GridView.Horizontaltv1.grid=Truetv1.Rows.count=0tv1.Columns.count=2tv1.Columns[0].text="RecID"tv1.Columns[1].text="Value"tv1.Columns[0].width=55tv1.Columns[1].width=55EndPublicSubCreateDatabase()db1.Type="sqlite"db1.host=User.homedb1.name=""'delete an existing test.sqliteIfExist(User.home&"/Test.sqlite")ThenKillUser.home&"/Test.sqlite"Endif'create test.sqlitedb1.Opendb1.Databases.Add("Test.sqlite")db1.CloseEndPublicSubMakeTable()DimhTableAsTabledb1.name="Test.sqlite"db1.OpenhTable=db1.Tables.Add("RandomNumbers")hTable.Fields.Add("RecID",db.Integer)hTable.Fields.Add("Value",db.Integer)hTable.PrimaryKey=["RecID"]hTable.UpdateEndPublicSubFillTable()DimiAsIntegerDimrs1AsResultdb1.Beginrs1=db1.Create("RandomNumbers")Fori=1To10000rs1!RecID=irs1!Value=Rand(10,99)rs1.UpdateNextdb1.CommitCatchdb1.RollbackMessage.Error(Error.Text)EndPublicSubReadData()'read the databaseDimSQLAsString="SELECT * FROM RandomNumbers"rs=db1.Exec(SQL)EndPublicSubForm_Open()SetupTableViewCreateDatabaseMakeTableFillTableReadDataEndPublicSubForm_Activate()'change the rowcount of the gridview from 0 to the number of records.'This triggers the data handling eventtv1.Rows.Count=rs.CountEndPublicSubtv1_Data(RowAsInteger,ColumnAsInteger)rs.moveTo(row)IfColumn=0Thentv1.Data.Text=rs!RecIDElsetv1.Data.Text=rs!Value'If Column = 0 Then tv1.Data.Text = Str(rs["RecID"]) Else tv1.Data.Text = Str(rs["Value"])'Either of these two lines will do it.EndPublicSubForm_Close()db1.CloseEnd
When you work with a database a temporary “journal” file is created. That file is incorporated into the database when it is “committed”. If you don’t want to commit, you “rollback” the database to what it was before you made these latest changes. The temporary file contains the “transaction”, meaning the latest work you have just done to change the database. That is what thedb1.Begin,db1.Commit anddb1.Rollback mean.
The above program is a good template to adapt when making a database.
This application saves records of cash spending. You can allocate each expenditure to a category. Each time you allocate to a category, totals are worked out for the categories and you can see what fraction of your spending went to each of the categories.
If you know you spent, say, €100, and you can only account for, say €85, you can distribute the remaining €15 among the categories.
Before letting loose on the code and after a look at the form we shall take a look at the process of designing such an application.
TheFile menu has itemsMenuNewDatabase, MenuOpen andMenuQuit.
The Data Menu has itemsMenuNewSpending, MenuNewCategory, MenuClearSpending, MenuClearCategories, MenuRound, MenuUnselectAll, MenuCalculate andMenuCopy.
TheHelp menu is optional.
The textbox whose name you cannot quite see above istbDistribute.
The program starts by opening the last database file that was open, or prompting to make a new one if it is the first time, or locating it if you sneakily moved it since the last time it was open. It also starts with a blank row in theSpending andCategories tableviews.
When a category is chosen for the selected spending line (click a category line anywhere except in the name column and press Enter) the category totals and percentages are recalculated.
Typing in theTarget textbox is optional. If there is a number in it, “Still to do” will be calculated.
Internally, the database has two tables called Spending and Categories. You can see two tableviews corresponding to the two database tables. These are the fields in each table:
The two primary keys areSpendingID andCatID. They number the records in sequence (1, 2, 3...)
TheSpending table’sCategory field contains a number which, when you look it up in theCategories table, gives you the category name. This is good: if you change the spelling of a category name you only have to change it once.
The user does not need to see the record IDs. They are internal to the database. They have to be unique: each record must have its own record ID. They are the primary keys of theSpending andCategories tables. They will be the very first columns in the tableviews, but they will be hidden from view (zero width). Also, in theSpending table, the user does not want to see the Category ID (the reference to one of the categories). It will be the last column in theSpending table, and also zero width. The columns start from zero, so it is column 5, just to the right of theAmount column.
Having sketched out a form design and planned the tables and fields with pencil and paper, we next think of what we want the program to do. It is good to keep in mind the things databases do:Add, Delete, Modify (as well as display the data). Here is a list. These are going to be the subs.
| Database | |
| NewDatabase | Create a new database file on disk with its two tables |
| OpenDatabase | Open the database and display what is in it on startup |
| General | |
| Calculate | Addup totals and work out percentages for each category |
| DoTotals | Grand totals for amounts in spending and categories tables |
| SetupTableViews | The right number of rows and columns and column headings |
| Spending Table | |
| NewSpending | Add a record to the Spending table |
| ShowSpending | Display what is in the Spending table in tv1, the tableview |
| TidySpendingTable | The final part of ShowSpending, really. Alternating blue lines |
| SumSpending | Part of “DoTotals”; add up all the spending totals |
| Clear a category (make it a right-click menu) | |
| Delete a record when you press DEL or BACKSPACE on a selected line | |
| Categories Table | |
| NewCategory | Add a record to the Categories table |
| ShowCategories | Display what is in the Categories table in tvCategories |
| TidyCategoriesTable | The final part of ShowCategories. Alternating blue lines. |
| SumCategories | Part of “DoTotals”; add up all the category amounts |
| Insert default categories into the categories table (a menu item) | |
| EnterOnCategoryLine | Enter on a line inserts category on the selected spending line. |
| Delete a record when you press DEL or BACKSPACE on a selected line | |
| Other Features | |
| Work out how much is left to allocate | |
| Distribute what is left among the categories | |
| A Help window | |
| Save what database we are using in Settings for next time | |
| Copy everything as text, to paste into a word processing document | |
| Round numbers to whole euros (and check totals are not out by one) | |
| A Quit menu item to close the program | |
| Useful Functions | |
| CategoryNameFromID | Given the CatID number, return the Category Name (a string) |
| Massage | Given the user’s choice of filename, remove bad characters |
Now it is time to program. Write the subs. Work out when they will be called on to do their work. Some can be consigned to menus. Some can happen when you click things. You are the one who is going to use this program: Do you want to click buttons? Do you want windows to pop up when you add a new category or a new spending transaction? Are therenice ways of doing things—intuitive ways—so things can happen naturally, as a new user might expect them to happen? We do some thinking and come up with some ideas:
Here are the names of the objects on the formFMain:
Panels: Panel1 (pink), Panel2 (blue)
Labels saying “Spending”, “Categories”, “Target:”, “= Done:”, “+ Still to do:”, “Amount:”
Labels called “LabSpendingTotal” and “LabCategoriesTotal” top right of the tableviews.
TableViews: tv1 for spending and tvCategories
TextBoxes: tbTarget, tbDone, tbToDo, tbDistribute
Button: bDistribute
File Menu: MenuNewDatabase, MenuOpen (Ctrl-O), MenuQuit (Ctrl-Q)
Data Menu: MenuNewSpending (Ctrl-N), MenuNewCategory (Ctrl-K), MenuClearSpending, MenuClearCategories, MenuDefaultCategories, MenuRound (Ctrl-R), MenuUnselectAll (Ctrl-Space), MenuCalculate (F4), MenuCopy (Ctrl-C)
Help Menu: Help and Instructions (F1) (Opens a separate form called Help. Put on it what you like.)
Category Menu (invisible, so it is not on the main menubar):MenuClearCategory (This one pops up with you right-click a category cell in the spending table.)
Here is the code. Following it is an explanation of the SQL statements.
PublicfdbAsNewConnection'finance databasePublicrsAsResult'result set after querying databasePublicSQLAsStringPublicSubForm_Open()SetUpTableViewsIfIsNull(Settings["Database/host"])ThenSelectCaseMessage.Question("Create a new data file, or open an existing one?","New...","Open...","Quit")Case1'newNewDatabaseCase2'openOpenDatabase(Null,Null)CaseElseQuitEndSelectElseOpenDatabase(Settings["Database/host"],Settings["Database/name"])EndifEndPublicSubForm_Close()fdb.Close'close connectionEndPublicSubOpenDatabase(dbHostAsString,dbNameAsString)'if these are null, ask where the database isIfNotExist(dbHost&/dbName)OrIsNull(dbHost)Then'it's not where it was last time, or path not suppliedDialog.Title="Where is the database?"Dialog.Filter=["*.db"]Dialog.Path=User.Home&/"Documents/"IfDialog.OpenFile()ThenReturn' User pressed Cancel; still can't open a databaseDimsAsString=Dialog.PathDimpAsInteger=RInStr(s,"/")'position of last slashfdb.host=Left(s,p)fdb.Name=Mid(s,p+1)Elsefdb.host=dbHostfdb.Name=dbNameEndIfTryfdb.Closefdb.type="sqlite3"Tryfdb.OpenIffdb.OpenedThenFMain.Caption=fdb.host&/fdb.NameSettings["Database/host"]=fdb.hostSettings["Database/name"]=fdb.NameElseMessage.Info("<b>Couldn't connect.</b><br><br>... please try again or create a new database.")ReturnEndifShowSpendingShowCategoriesCalculateEndPublicSubNewDatabase()Dialog.Path=User.Home&"/"'setting it to "~/" doesn't workDialog.Title="Create a New Database"IfDialog.SaveFile()ThenReturn'clicked CancelDimsAsString=Dialog.Path&".db"DimpAsInteger=RInStr(s,"/")'position of last slashDimFNameAsString=Mid(s,p+1)fdb.host=Left(s,p)fdb.Name=""'This MUST be left blank. If not, database file will not be createdfdb.Type="sqlite3"IfExist(s)ThenKills'delete existing file of that namefdb.CloseTryfdb.Open'opens a connection to the database; do this after setting properties and before creatingIfErrorThenMessage("Unable to open the database file<br><br>"&Error.Text)ReturnEndiffdb.Databases.Add(fName)'does the creatingfdb.CloseDimdbTableAsTablefdb.name=fNameTryfdb.OpenIfNotfdb.openedThenMessage("Unable to open the data file")ReturnEndifdbTable=fdb.Tables.Add("Spending")dbTable.Fields.Add("SpendingID",db.Integer)dbTable.Fields.Add("TransDate",db.String)dbTable.Fields.Add("Category",db.Integer)dbTable.Fields.Add("Comment",db.String)dbTable.Fields.Add("Amount",db.Float)dbTable.PrimaryKey=["SpendingID"]dbTable.Updaters=fdb.Create("Spending")Iffdb.ErrorThenMessage("Couldn't create the Spending table.<br><br>: "&Error.Text)ReturnEndifrs!SpendingID=1rs!TransDate=""rs!Category=0rs!Comment=""rs!Amount=0.0rs.Updatefdb.CommitIffdb.ErrorThenMessage("Couldn't save a first record in the Spending table.<br><br>: "&Error.Text)ReturnEndiffdb.Closefdb.name=fNameTryfdb.OpenIfNotfdb.openedThenMessage("Unable to open the data file")ReturnEndifdbTable=fdb.Tables.Add("Categories")dbTable.Fields.Add("CatID",db.Integer)dbTable.Fields.Add("Category",db.String)dbTable.PrimaryKey=["CatID"]dbTable.Updaters=fdb.Create("Categories")Iffdb.ErrorThenMessage("Couldn't create the Categories table.<br><br>: "&Error.Text)ReturnEndifrs!CatID=1rs!Category=""rs.Updatefdb.CommitIffdb.ErrorThenMessage("Couldn't save a first record in the Categories table.<br><br>: "&Error.Text)ReturnEndifEndPublicSubDoTotals()labCategoriesTotal.Text=SumTheCategories()labSpendingTotal.text=SumTheSpending()tbDone.Text=labSpendingTotal.TextEndPublicSubShowSpending()rs=fdb.Exec("SELECT * FROM Spending")DimL,CatIDAsIntegerDimCatNameAsStringtv1.Rows.Count=0'clearIfNotIsNull(rs)ThenWhilers.Availabletv1.Rows.Count+=1L=tv1.Rows.maxtv1[L,0].text=rs!SpendingIDtv1[L,1].Text=rs!TransDatetv1[L,2].Text=Format(rs!Amount,"0.00")CatName=rs!CategoryIfNotIsNull(CatName)ThenCatID=If(IsNull(Val(CatName)),-1,Val(CatName))IfCatID>-1Thentv1[L,3].Text=CategoryNameFromID(CatID)Endiftv1[L,4].Text=rs!Commenttv1[L,5].Text=rs!Category'Category ID in this hidden columnrs.MoveNextWendEndifIftv1.Rows.Count=0Thentv1.Rows.Count=1TidySpendingTableEndPublicSubShowCategories()rs=fdb.Exec("SELECT * FROM Categories")DimLAsIntegerDimtAsFloattvCategories.Rows.Count=0'clearIfNotIsNull(rs)ThenWhilers.AvailabletvCategories.Rows.Count+=1L=tvCategories.Rows.maxtvCategories[L,0].text=rs!CatIDtvCategories[L,3].Text=rs!Categoryrs.MoveNextWendEndifIftvCategories.Rows.Count=0ThentvCategories.Rows.Count=1TidyCategoriesTableEndPublicSubNewSpending()tv1.Rows.count=tv1.Rows.count+1tv1.MoveTo(tv1.Rows.Max,1)tv1.EditEndPublicSubNewCategory()tvCategories.Rows.count=tvCategories.Rows.count+1tvCategories.row+=1tvCategories.EditEndPublicSubtv1_Insert()NewSpendingEndPublicSubtvCategories_Insert()NewCategoryEndPublicSubtv1_Click()SelectCasetv1.ColumnCase1,2,4tv1.EditCase3IftvCategories.Rows.Count>0ThentvCategories.SetFocustvCategories.Rows[0].Selected=TrueEndifEndSelectEndPublicSubtvCategories_Click()IftvCategories.Column=3ThentvCategories.EditEndPublicSubSetUpTableViews()DimiAsIntegertv1.Columns.count=6tv1.Rows.count=1tv1.Columns[0].Width=0tv1.Columns[1].Alignment=Align.Centertv1.Columns[2].Alignment=Align.RightFori=1Totv1.Columns.Max-1tv1.Columns[i].Width=Choose(i,80,80,130,tv1.Width-tv1.ClientW-306)tv1.Columns[i].Text=Choose(i,"Date","Amount","Category","Comment")NexttvCategories.Columns.count=4tvCategories.Rows.count=1tvCategories.Columns[0].Width=0Fori=1TotvCategories.Columns.MaxtvCategories.Columns[i].Width=Choose(i,60,60,tvCategories.Width-tvCategories.ClientW-350)tvCategories.Columns[i].Text=Choose(i,"Total","%","Category")NexttvCategories.Columns[1].Alignment=Align.righttvCategories.Columns[2].Alignment=Align.Centertv1.Columns[5].Width=0EndPublicSubTidySpendingTable()ForiAsInteger=0Totv1.Rows.MaxForjAsInteger=0Totv1.Columns.MaxIfj=2Orj=3Thentv1[i,j].Padding=4IfiMod2=1Thentv1[i,j].Background=&hF0F0FFNextNextEndPublicSubTidyCategoriesTable()ForiAsInteger=0TotvCategories.Rows.MaxForjAsInteger=1TotvCategories.Columns.MaxtvCategories[i,j].Padding=4IfiMod2=1ThentvCategories[i,j].Background=&hF0F0FFNextNextEndPublicSubMassage(sAsString)AsString'Doesn't like spaces or hyphens in file names. Doesn't complain; just doesn't create the file.DimzAsStringForiAsInteger=0ToLen(s)-1IfIsLetter(s[i])OrIsDigit(s[i])Ors[i]="_"Ors[i]="."Thenz&=s[i]Elsez&="_"NextReturnzEndPublicSubtvCategories_Save(RowAsInteger,ColumnAsInteger,ValueAsString)DimRecIDAsIntegerDimOriginalValueAsString=tvCategories[Row,Column].TexttvCategories[Row,Column].Text=ValueIfIsNull(tvCategories[Row,0].Text)Then'no record ID, so we need a new recordDimResAsResultSQL="SELECT MAX(CatID) as 'TheMax' FROM Categories"Res=fdb.Exec(SQL)IfIsNull(Res!TheMax)ThenRecID=1ElseRecID=Res!TheMax+1tvCategories[Row,0].Text=RecIDSQL="INSERT INTO Categories(CatID,Category) VALUES("&RecID&",'')"fdb.Exec(SQL)Iffdb.ErrorThenMessage("Couldn't save:<br><br>"&SQL&"<br><br>"&Error.Text)Endif'update the recordRecID=tvCategories[Row,0].TextSQL="UPDATE Categories SET Category = '"&Value&"' WHERE CatID='"&RecID&"'"Tryfdb.Exec(SQL)Iffdb.ErrorThenMessage("Couldn't save:"&SQL&"<br><br>"&Error.Text)IfValue<>OriginalValueThenShowSpending'category name was changedEndPublicSubtv1_Save(RowAsInteger,ColumnAsInteger,ValueAsString)DimRecIDAsIntegerDimFieldNameAsString=Choose(Column,"TransDate","Amount","Category","Comment")IfIsNull(tv1[Row,0].Text)Then'There's no Record ID, so insert a new recordDimResAsResultSQL="SELECT MAX(SpendingID) as 'TheMax' FROM Spending"TryRes=fdb.Exec(SQL)IfIsNull(Res!TheMax)ThenRecID=1ElseRecID=Res!TheMax+1tv1[Row,0].Text=RecIDSQL="INSERT INTO Spending(SpendingID,TransDate,Amount,Category,Comment) VALUES('"&RecID&"',' ',' ',' ',' ')"Tryfdb.Exec(SQL)IfErrorThenMessage("Couldn't save: "&Error.Text)ReturnEndifEndif'update recordRecID=tv1[Row,0].TextSQL="UPDATE Spending SET "&FieldName&" = '"&Value&"' WHERE SpendingID='"&RecID&"'"Tryfdb.Exec(SQL)IfErrorThenMessage("Couldn't save:"&SQL&"<br><br>"&Error.Text)ReturnEndifIfColumn=2Thentv1[Row,Column].Text=Format(Val(Value),"###0.00")Calculate'amount has changedElsetv1[Row,Column].Text=ValueEndifCatchMessage("Couldn't save ... have you created and opened a database yet?")StopEvent'Don't go automatically to the next cell. If you do, you'll get this message twice.EndPublicSubtv1_KeyPress()SelectCaseKey.CodeCaseKey.BackSpace,Key.Del'remove recordDimRecIDAsInteger=tv1[tv1.Row,0].TextSQL="DELETE FROM Spending WHERE SpendingID='"&RecID&"'"Tryfdb.Exec(SQL)IfErrorThenMessage("Couldn't delete<br><br>"&Error.Text)Elsetv1.Rows.Remove(tv1.Row)Iftv1.Rows.Count=0Thentv1.Rows.Count=1EndifCaseKey.Enter,Key.ReturnIftvCategories.Rows.Count>0ThentvCategories.SetFocustvCategories.Rows[0].Selected=TrueEndifEndSelectEndPublicSubtvCategories_KeyPress()SelectCaseKey.CodeCaseKey.BackSpace,Key.Del'remove recordDimRecIDAsInteger=tvCategories[tvCategories.Row,0].TextSQL="DELETE FROM Categories WHERE CatID='"&RecID&"'"Tryfdb.Exec(SQL)IfErrorThenMessage("Couldn't delete<br><br>"&Error.Text)ElsetvCategories.Rows.Remove(tvCategories.Row)EndifCaseKey.Enter,Key.ReturnEnterOnCategoryLine'action on pressing EntertvCategories.UnSelectAllEndSelectEndPublicSubMenuClearSpending_Click()fdb.Exec("DELETE FROM Spending")tv1.Rows.count=1tv1.ClearEndPublicSubMenuClearCategories_Click()fdb.Exec("DELETE FROM Categories")tvCategories.Rows.count=1tvCategories.ClearEndPublicSubCategoryNameFromID(IDAsInteger)AsStringDimresAsResult=fdb.Exec("SELECT Category FROM Categories WHERE CatID="&ID)IfNotres.AvailableThenReturn"?"IfIsNull(res!Category)ThenReturn"-"Returnres!CategoryEndPublicSubEnterOnCategoryLine()'apply this category to the selected Spending lineIftv1.row<0ThenReturnIfIsNull(tv1[tv1.row,0].text)ThenMessage("Please save this spending record first by entering some other item of data; there's no record ID yet.")ReturnEndiftv1[tv1.row,3].text=tvCategories[tvCategories.row,3].TextDimCategoryIDAsString=tvCategories[tvCategories.row,0].TextDimSpendingIDAsString=tv1[tv1.row,0].texttv1[tv1.row,5].text=CategoryIDSQL="UPDATE Spending SET Category='"&CategoryID&"' WHERE SpendingID='"&SpendingID&"'"Tryfdb.Exec(SQL)IfErrorThenMessage("Couldn't save the category<br><br>"&SQL&"<br><br>"&Error.text)ReturnEndifCalculateForiAsInteger=tv1.rowTotv1.Rows.MaxIfIsNull(tv1[i,3].text)Thentv1.Rows[i].Selected=True'select the next Spending row that needs a categorytvCategories.SetFocusReturnEndifNexttv1.SetFocusEndPublicSubCalculate()Dimi,j,CategoryIDAsIntegerDimt,GrandTotalAsFloatDimresAsResultDimsAsStringFori=0TotvCategories.Rows.Max'every categoryIfIsNull(tvCategories[i,0].Text)ThenContinueCategoryID=tvCategories[i,0].TextTryRes=fdb.Exec("SELECT Total(Amount) AS TotalAmount FROM Spending WHERE Category="&CategoryID)IfErrorThenMessage("Couldn't total<br><br>"&Error.Text)ContinueEndifWhileres.Availablet=res!TotalAmountGrandTotal+=tIft=0ThentvCategories[i,1].Text=""ElsetvCategories[i,1].Text=Format(t,"##0.00")res.MoveNextWendNextIfGrandTotal=0ThenReturnFori=0TotvCategories.Rows.Maxs=tvCategories[i,1].TextIfNotIsNull(s)AndIfVal(s)>0ThentvCategories[i,2].Text=Format(100*Val(s)/GrandTotal,"##0.##")ElsetvCategories[i,2].Text=""NexttbDone.Text=Format(GrandTotal,"##0.00")labSpendingTotal.Text=tbDone.TextlabCategoriesTotal.Text=SumTheCategories()IfNotIsNull(tbTarget.text)ThentbToDo.Text=Format(Val(tbTarget.Text)-GrandTotal,"##0.00")tbDistribute.Text=tbToDo.TextEndifEndPublicSubSaveCategoriesTable()ForiAsInteger=0TotvCategories.Rows.MaxSaveCategoryLine(i)NextEndPublicSubSaveCategoryLine(iAsInteger)'i is the line numberDimRecIDAsIntegerDimt,pctAsFloatDims,CategoryNameAsStringDimresAsResultRecID=Val(tvCategories[i,0].Text)CategoryName=tvCategories[i,3].Textt=If(IsNull(tvCategories[i,1].Text),0,Val(tvCategories[i,1].Text))s=tvCategories[i,2].Textpct=If(IsNull(s),0,Val(s))IfIsNull(RecID)Then'new record neededres=fdb.Exec("SELECT Max(CatID) AS MaxCatID FROM Categories")RecID=res!MaxCatID+1SQL="INSERT INTO Categories(CatID,Category) VALUES("&RecID&","&CategoryName&")"fdb.Exec(SQL)IfErrorThenMessage("Couldn't insert a new record<br><br>"&SQL&"<br><br>"&Error.text)ReturnEndifElseSQL="UPDATE Categories SET Category='"&CategoryName&"' WHERE CatID="&RecIDTryfdb.Exec(SQL)'before checking Error, don't forget to use TRY. Otherwise Error will be set and you'll seem to have an error when you don'tIfErrorThenMessage("Couldn't update a record<br><br>"&SQL&"<br><br>"&Error.text)ReturnEndifEndifEndPublicSubSumTheCategories()AsStringDimtAsFloatDimsAsStringForiAsInteger=0TotvCategories.Rows.Maxs=tvCategories[i,1].TextIfNotIsNull(s)Thent+=Val(s)NextReturnFormat(t,"##0.00")EndPublicSubSumTheSpending()AsStringDimtAsFloatDimsAsStringForiAsInteger=0Totv1.Rows.Maxs=tv1[i,2].TextIfNotIsNull(s)Thent+=Val(s)NextReturnFormat(t,"##0.00")EndPublicSubMenuCalculate_Click()CalculateEndPublicSubtbTarget_LostFocus()IfNotIsNull(tbTarget.text)ThentbTarget.Text=Format(Val(tbTarget.Text),"##0.00")ElsetbTarget.Text=""CalculateEndPublicSubtbTarget_KeyPress()IfKey.Code=Key.EnterOrKey.Code=Key.ReturnThenFMain.SetFocusEndPublicSubbDistribute_Click()Dimt,pct,y,zAsFloatIfIsNull(tbDistribute.Text)ThenReturnDimxAsFloat=Val(tbDistribute.Text)ForiAsInteger=0TotvCategories.Rows.MaxIfIsNull(tvCategories[i,1].Text)ThenContinueIfIsNull(tvCategories[i,2].Text)ThenContinuet=Val(tvCategories[i,1].Text)pct=Val(tvCategories[i,2].Text)y=t+pct/100*xz+=y'running totalIfy=0ThentvCategories[i,1].Text=""ElsetvCategories[i,1].Text=Format(y,"##0.00")SaveCategoryLine(i)NextlabCategoriesTotal.text=Format(z,"##0.00")FMain.SetFocusEndPublicSubtbDistribute_LostFocus()'when leaving, fix the appearanceIfNotIsNull(tbDistribute.text)ThentbDistribute.Text=Format(Val(tbDistribute.Text),"##0.00")ElsetbDistribute.Text=""EndPublicSubtbDistribute_KeyPress()'enter leaves the textboxIfKey.Code=Key.EnterOrKey.Code=Key.ReturnThenFMain.SetFocusEndPublicSubMenuDefaultCategories_Click()Tryfdb.Exec("DELETE FROM Categories")'it might be already clearedtvCategories.Rows.Count=9tvCategories.ClearDimsAsStringForiAsInteger=0To8s=Choose(i+1,"Provisions","Travel","Medical","Donations","Papers etc","Clothes","Personal","Phone","Repairs")tvCategories[i,3].text=stvCategories[i,0].text=i+1SQL="INSERT INTO Categories(CatID,Category) VALUES("&Str(i+1)&",'"&s&"')"Tryfdb.Exec(SQL)IfErrorThenMessage("Couldn't insert a new record in the categories table.<br><br>"&SQL&"<br><br>"&Error.Text)NextlabCategoriesTotal.text=""EndPublicSubMenuRound_Click()DimsAsStringDimx,tAsFloatForiAsInteger=0TotvCategories.Rows.Maxs=tvCategories[i,1].TextIfIsNull(s)ThentvCategories[i,1].Text=""Elsex=Round(Val(s))t=t+xtvCategories[i,1].Text=xEndifNextlabCategoriesTotal.Text=Format(t,"##0.00")ForiAsInteger=0TotvCategories.Rows.Maxs=tvCategories[i,2].TextIfNotIsNull(s)ThentvCategories[i,2].Text=Round(Val(s))NextEndPublicSubMenuOpen_Click()OpenDatabase(Null,Null)EndPublicSubMenuNewDatabase_Click()NewDatabaseEndPublicSubMenuNewSpending_Click()NewSpendingEndPublicSubMenuNewCategory_Click()NewCategoryEndPublicSubMenuQuit_Click()QuitEndPublicSubMenuCopy_Click()Dims,zAsStringDimi,jAsIntegerFori=0Totv1.Rows.Maxs=tv1[i,1].TextForj=2To4s&=gb.Tab&tv1[i,j].TextNextz&=If(IsNull(z),"",gb.NewLine)&sNextz&=gb.NewLineFori=0TotvCategories.Rows.Maxs=tvCategories[i,1].TextForj=2To3s&=gb.Tab&tvCategories[i,j].TextNextz&=If(IsNull(z),"",gb.NewLine)&sNextz&=gb.NewLine&gb.NewLine&"Total Withdrawn: "&tbTarget.Text&gb.tab&" = Total Accounted For: "&tbDone.Text&gb.tab&" + Cash on hand: "&tbToDo.TextClipboard.Copy(z)EndPublicSubMenuShowHelp_Click()Help.ShowModalEndPublicSubMenuClearCategory_Click()DimRecIDAsInteger=tv1[tv1.row,0].Textfdb.Exec("UPDATE Spending Set Category=' ' WHERE SpendingID="&RecID)tv1[tv1.row,3].Text=""'Cat texttv1[tv1.row,5].Text=""'Cat IDCalculateEndPublicSubMenuUnselectAll_Click()tv1.Rows.UnSelectAlltvCategories.Rows.UnSelectAllEnd
Some of these statements are used as they appear. Others are a string that is built up from parts. You might seeSQL = … . Bits of the statement are SQL and the field name might be added to it in the right place and be stored in a variable, for example. Or perhaps the record ID might be in a variable called RecID. Use single quotes in the string that is sent to SQLite. Use double quotes when assembling the statement in Gambas.
| SELECT * FROM Spending | Select everything from the Spending table |
| SELECT * FROM Categories | Select everything from the Categories table |
| SELECT MAX(CatID) as 'TheMax' FROM Categories | Get the highest CatID from the Categories table and call it “TheMax”. |
| INSERT INTO Categories(CatID,Category) VALUES(123,'Entertainment') | Create a new record in the Categories table. Put123 into theCatID field andEntertainment into theCategory field. |
| UPDATE Categories SET Category = 'Entertainment' WHERE CatID='123' | TheCategories table has to be updated. In the record withCatID equal to123, putEntertainment in theCategory field. |
| SELECT MAX(SpendingID) as 'TheMax' FROM Spending | Find the biggestSpendingID in theSpending table and call it “TheMax”. |
| INSERT INTO Spending(SpendingID,TransDate,Amount,Category,Comment) VALUES('123',' ',' ',' ',' ') | Create a new record in the Spending table. SpendingID = 123 TransDate = a blank Amount = a blank Category = a blank Comment = a blank |
| UPDATE Spending SET TransDate = '4-11-2019' WHERE SpendingID='123' | Put “4-11-2019” into theTransDate field of the record in theSpending table that has aSpendingID of 123. |
| DELETE FROM Spending WHERE SpendingID='123' | Delete the record in theSpending table that has a record ID of 123. |
| DELETE FROM Categories WHERE CatID='123' | Delete the record in theCategories table that has a record ID of 123. |
| DELETE FROM Spending | Delete every record from theSpending table. All the data disappears, never to be seen again. |
| DELETE FROM Categories | Delete every record from theCategories table. All the category records, gone forever. |
| SELECT Category FROM Categories WHERE CatID=123 | Give me the name of the category that goes with theCatID record number 123. |
| UPDATE Spending SET Category='4' WHERE SpendingID='123' | Set theCategory field of record 123 of theSpending table to 4. This spending item goes into the fourth category, whatever that is. To find out what the fourth category is, look up theCategories table and find the record withCatID=4 |
| SELECT Total(Amount) AS TotalAmount FROM Spending WHERE Category='4' | Get the sum of all the numbers in the Amount fields of all the records in the Spending table that have 4 in their Category field. Simply, add up all the amounts spent in category 4. Call the answer “TotalAmount” |
| SELECT Max(CatID) AS MaxCatID FROM Categories | Get the highest CatID from the Categories table. Call itMaxCatID. |
| SQL = "INSERT INTO Categories(CatID,Category) VALUES(4,Travel)" | Create a new Categories record. Set theCatID field equal to 4 and theCategory to “Travel”. |
| UPDATE Categories SET Category='Travel' WHERE CatID=4 | Update the Categories record that has a record ID of 4. Put “Travel” into theCategory field. |
| UPDATE Spending Set Category=' ' WHERE SpendingID=123 | Put a blank into the Category field of Spending record 123 |
The statements are eitherSELECT,INSERT,DELETE orUPDATE.
The patterns are:
SELECTfieldsFROMtableSELECTfieldsFROMtableWHEREfield=somethingSELECT*FROMtableSELECTTotal(field)ASnameForItFROMtableSELECTMax(field)ASnameForItFROMtableINSERTINTOtable(fields)VALUES(values)DELETEFROMtableDELETEFROMtableWHEREfield=somethingUPDATEtableSETfield=somethingWHEREkeyfield=something
These are not the only SQL statements: there are many more. They are enough to get a working knowledge of SQL. Online help for SQLite can be found at
http://www.sqlitetutorial.net/
https://www.w3schools.com/sql/
A most important point about using the UPDATE statement:
| Be careful when updating records. If you omit the WHERE clause, ALL records will be updated! |
|---|
For example, do not write this:UPDATE Spending SET Amount=12.50 .This puts 12.50 into theAmount field ofevery record. All amounts become 12.50. You should sayUPDATE Spending SET Amount=12.50 WHERE SpendingID=42 .
| Programming Gambas from Zip | ||
| ← Modules | SQLite | Printing → |