I Wish I Knew How to... Program SQLite with Xojo API2 (XDL-0017)
I Wish I Knew How to... Program SQLite with Xojo API2 delves into the mystery of programming a back-end database.
The book is written as a guide and reference to Xojo programmers who program Desktop Applications in Windows and Mac. There are no plugins, dynamic link libraries (dll), COM, or Active X parts to add.
There are more than 20 chapters with over 400 pages with over 80 example programs.
Examples include opening RowSets, finding data, sorting, updating data, counting records, printing a report, deleting records, creating new records, creating reports, and more. Many screenshots have been added to show the results of the code with an index to help find topics quickly.
*This uses Xojo API2
All examples have been created with native Xojo code with Xojo 2020 r2.1. There is an older book for AP1 (Xojo 2014 – SQLite).
Specific updates to the book are to numerous to mention, and here is a list of general changes with January 2021 (2.0 Edition) that has major updates which are:
- Converted all examples to API 2 and updated code
- Datatype NULL is now called BLOB
- Update a large number of screen grabs with newer DB Browser for SQLite
- Make a new Example 3-2, create a sqlite database with Xojo code
- Make a new Example 3-3, create a sqlite database with a table and ID field
- OpenDialog is deprecated, using OpenFileDialog instead
- Value is deprecated, using Text instead
- MsgBox is deprecated, using MessageDialog.Show.
- Field is deprecated, using Column instead
- Field is deprecated, using RowSet instead
- SQLSelect is deprecated, using SelectSQL instead
- RecordSet is deprecated, using RowSet instead
- ErrorMessage is deprecated, using DatabaseException instead
- Replaced Dim (API1) to Var (API2)
- EOF is deprecated, using For Each x as DatabaseRow in RS instead and/or AfterLastRow
- LastIndex is deprecated, using LastAddedRowIndex instead
- HasHeading is deprecated, using HasHeader instead
- Cell is deprecated, CellValueAt is used instead
- DatabaseRecord is deprecated, DatabaseRow is used instead
- InsertRecord is deprecated, AddRow is used instead
- Delete is deprecated, using Remove instead
- CopyFileTo is deprecated, using CopyTo instead
- MaxHorizontalResolution is deprectated, using MaximumHorizontalResolution instead
- MaxVerticalResolution is deprecated, using MaximumVerticalResolution instead
- PageSetupDialog is deprecated, using ShowPageSetupDiaolog instead
- Date is deprecated, DateTime is used instead
- SaveAsDialog is deprecated, using SaveFileDialog instead
- CreateDatabaseFile is deprecated, using CreateDatabase instead
- SQLExecute is deprecated, using ExecuteSQL instead
- SleepCurrentThread is deprecated, using Thread.SleepCurrent instead
- Commit is deprecated, using CommitTransaction instead
- Rollback is deprecated, using RollbackTransaction instead
More than twenty chapters and over 400 pages with greater than 80 examples that both show and describe the code. This book is in PDF format with example chapter folders containing Xojo source code and running examples.
Eugene Dakin is the author of many computer programming reference materials and has many years experience creating technical programs.
The Table of Contents can be previewed at the author's website at https://scispec.ca/index.php/books
ISBN: 978-1-927924-29-7
Updates to Version 2.6 -- Oct 2023
* This is a continuation of the many updates for API2 controls with SQLite.
* Changed Example 13-1 from CreateDatabase to Connect to database
* Example 13-2 is updated to API2 with the following code and control updates: DesktopApplication, binary project, DesktopButton, DesktopButton Pressed Event, DesktopMenuBar, DesktopLabel, connect - not create database.
* Updated API2 code and controls in Example 13-3 which include: binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopButton Pressed Event, DesktopMenuBar, DesktopLabel,
* Code and controls were updated on Example 13-4 and the following was upgraded: Binary project, DesktopWindow, DesktopApplication, DesktopButton, DesktopLabel, DesktopButton Pressed Event, and DesktopMenuBar.
* Example 13-5 has been further updated to API2 controls and code: DesktopApplication, DesktopWindow, binary project, DesktopButton, DesktopLabel, DesktopButton Pressed event, and DesktopMenuBar.
* API2 code and controls have been updated in Example 14-1: DesktopApplication, DesktopWindow, binary project, DesktopButton, DesktopButton Pressed event, Desktoplabel, and DesktopMenuBar.
* Example 14-2 is updated to API2 with the following code and control updates: DesktopApplication, binary project, DesktopButton, DesktopButton Pressed Event, DesktopMenuBar, DesktopLabel, and DesktopMenuBar.
* Updating the following controls and code on Example 15-1: binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopButton Pressed event, DesktopLabels, and DesktopMenuBar.
* Example 15-2 has code and controls updated to the latest version of API2 in Xojo 2023 R2: Binary Project, DesktopApplication, DesktopWindow, DesktopMenuBar, DesktopButton, DesktopButton Pressed event, DesktopLabel, and DesktopProgressBar.
* Code and controls have been updated on Example 15-3: DesktopApplication, Binary Project, DesktopWindow, DesktopMenuBar, DesktopButton, DesktopButton Pressed and Opening Events, DesktopLabel, DesktopProgressBar.
* Updated controls and code in Example 16-1 which include the following: binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopButton Pressed Event, DesktopMenuBar, DesktopLabel, and DesktopTextBox.
* Controls and code updating occurred to Example 16-2 with the following info: binary project, DesktopApplication, DesktopWindow, DesktopButton, Desktop Pressed event, DesktopLabel, DesktopMenuBar, DesktopListbox, DesktopListbox CellTextAt, and DesktopTextField.
* Example 17-1 has been updated to API2 with the following changes: Binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopMenuBar, DesktopButton Pressed event, DesktopListbox, and DesktopListbox CellTextAt.
* Updated code and controls in Example 17-2 to the following: DesktopApplication, DesktopWindow, DesktopButton, binary project, DesktopMenuBar, DesktopButton Pressed Event, DesktopListbox, and DesktopListbox CellTextAt.
* Code and controls were updated in Example 17-3 to: Binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopMenuBar, DesktopButton Pressed event, DesktopListBox, and DesktopListBox CellTextAt.
* Example 17-4 has been updated with code and controls which include the following: DesktopApplication, binary project, DesktopWindow, DesktopButton, DesktopButton Pressed Event, DesktopMenuBar, DesktopListbox, and DesktopListBox CellTextAt command.
* API2 updates with control and code have been updated as follows To Example 17-5: binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopMenuBar, DesktopButton Pressed Event, DesktopListbox, and DesktopListBox CellTextAt command.
* More controls in Example 17-6 have been updated to the following: DesktopApplication, binary project, DesktopWindow, DesktopMenuBar, DesktopButton, DesktopButton Pressed event, DesktopListbox, and DesktopListbox CellTextAt.
* Example 18-1 controls and code has been updated: DesktopApplication, DesktopWindow, binary project, DesktopMenuBar, DesktopButton, DesktopButton Pressed event, and DesktopLabel.
* Upated code and controls to the project Example 19-1 with the following: DesktopApplication, DesktopWindow, Binary Project, DesktopMenuBar, DesktopButton, DesktopButton Pressed and Opening Events, DesktopListbox, DesktopListBox to CellTextAt, DesktopLabel, and DesktopTextField.
* Example 19-2 has been updated to the latest API2 code and controls with the following modifications: DesktopApplication, binary project, DesktopWindow, DesktopMenuBar, DesktopButton, DesktopButton Opening and Pressed event, DesktopListbox, DesktopListbox CellTextAt, DesktopLabel, DesktopTextField, and String.ReplaceAll.
* Updated code and controls to Example 20-1 which include: binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopButton Pressed Event, DestopMenuBar, DesktopListbox, and DesktopListbox CellTextAt.
* Controls and code have been updated to the latest version of 2023 R2 API2: DesktopApplication, binary project, DesktopWindow, DesktopMenuBar, DesktopButton, DesktopButton Pressed Event, DesktopListbox, and DesktopListBox CellTextAt.
* Example 21-1 API2 has been updated to the following controls and code: DesktopApplicaition, binary project, DesktopWindow, DesktopButton, DesktopButton Opening and Pressed event, DesktopMenuBar, DesktopListbox, DesktopListbox CellTextAt, and DesktopLabel.
* Updated controls and code in Example 22-1 to the following: DesktopApplication, binary project, DesktopWindow, DesktopButton, DesktopListbox, DesktopButton Pressed event, DesktopMenuBar, and DesktopListbox CellTextAt.
* Code and controls are updated in Example 22-2: DesktopApplication, DesktopWindow, binary project, DesktopMenuBar, DesktopButton, DesktopButton Opening and Pressed event, DesktopListbox, DesktopListBox CellTextAt, and DesktopLabel.
* Example 22-3 has been updated with the following API code and controls: binary project, DesktopWindow, DesktopApplication, DesktopButton, DesktopButton Pressed Event, DesktopMenuBar, DesktopLabel, and SQLitePreparedStatement ExecuteSQL command.
* Updated Example 22-4 to the latest Xojo 2023 Release 2 API2 code and controls: DesktopApplication, DesktopWindow, binary project, DesktopMenuBar, DesktopButton, DesktopButton Pressed event and Opening Event, DesktopListbox, DesktopListbox CellTextAt, DesktopLabel, and DesktopMenuBar.
* API2 code and controls have been updated in Example 25-1: binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopButton Opening and Pressed events, DesktopMenuBar, DesktopListbox, DesktopListbox CellTextAt commands, DesktopGroupBox, DesktopTextField controls, and DesktopLabel controls.
* Updated API2 controls and code in Example 26-1: DesktopApplication, DesktopWindow, Binary Project, DesktopMenuBar, DesktopButton, DesktopButton Pressed Event, DesktopLabel, and removed unused variable.
* Example 27-1 was updated to API2 with the following edits to controls and code: converted many parameters to doubles, and added Pragma unused for parameters.
Updates to Version 2.5 – September 2023
* Focus on latest updates to API2 version with 2023 R2
* Updated Desktop, BevelButton, DesktopApplication, DesktopWindow, Action to Pressed Event, DesktopLabel, changed Menubar to API2 (Example 1-1)
* Update DesktopApplication, DesktopWindow, DesktopButton, Binary Project, Button Pressed Event, MenuBar, DesktopLabel to API2 (Example3-1)
* Update DesktopApplication, DesktopWindow, DesktopButton, Binary Project, DesktopButton Pressed Event, MenuBar, DesktopLabel, to API2 (Example 3-2)
* Example 3-3, updated DesktopApplication, DesktopWindow, DesktopButton, Project file binary, MenuBar, DesktopButton, Desktopbutton Pressed event, and DesktopLabel.
* Example 4-1, changed to newer controls, DesktopApplication, DesktopWindow, DesktopButton, DesktopTextField, changed to new binary project format, DesktopButton to Pressed action event, Menubar, and DesktopLabel. Updated code in example.
* Changed following controls and new API2 compatible items, DesktopApplication, DesktopWindow, DesktopButton, DesktopTextField, DesktopLabel, new binary project, New Desktopbutton Pressed event, book code, and Menubar in Example 4-2.
* Example 4-3 updated items: DesktopApplication, DesktopWindow, DesktopButton, DesktopLabel, DesktopComboBox, new binary project format, new button Pressed event, MenuBar, and the code in book for this example.
* Modified the following controls and code: DesktopApplication, DesktopWindow, DesktopButton, binary project, DesktopButton Pressed Event, MenuBar, DesktopListBox, CellTextAt, changed code in book with Example 4-4.
* Changed code and controls in Example 4-5 to: DesktopApplication, DesktopWindow, DesktopButton, new binary project format, DesktopButton Pressed event, MenuBar, DesktopListBox, CellTextAt, and updated example book code for Example 4-5.
* Update Example 4-6 with further updates to API2 controls and code, DesktopApplication, DesktopWindow, DesktopButton, Binary Project, Pressed DesktopButton event, MenuBar, DesktopListbox, Code CellTextAt in book code.
* Changed and updated to the following API2 code and control changes: DesktopApplication, DesktopWindow, DesktopButton, binary project, Desktopbutton Pressed event, MenuBar, DesktopListbox, CellTextAt, and book code for Example 4-7.
* Example 4-8 updates to code and desktop controls are as follows: DesktopApplication, DesktopWindow, DesktopButton, binary project update, DesktopButton Pressed event, MenuBar, DesktopListbox, CellTextAt, DesktopTextField, and DesktopLabel.
* Updated Example 4-9 with the following: DesktopApplication, DesktopWindow, DesktopButton, update binary project, DesktopButton Pressed event, MenuBar, DesktopListbox, DesktopTextfield, Code CellTextAt, and DesktopLabel.
* The following items were updated: DesktopApplication, DesktopWindow, DesktopButton, DesktopListbox, DesktopTextbox, DesktopLabel, binary project updates, Implement Pressed event, CellTextAt, code, and MenuBar for Example 4-10.
* Example 4-11 has been updated with: DesktopApplication, DesktopWindow, DesktopButton, DesktopListbox, DesktopTextfield, Update binary project, Pressed event for DesktopButton, MenuBar, and CellTextAt.
* Updated Example 5-1 with the following modifications: DesktopApplication, DesktopWindow, DesktopButton, Pressed event, Opening Event, Closing Event, binary project format, MenuBar, DesktopLabel, DesktopListbox, and CellTextAt.
* Updated the following deprecated items: DesktopApplication, DesktopWindow, DesktopButton, DesktopListbox, DesktopLabel, update binary project, Pressed event, Opening event, MenuBar, and CellTextAt for Example 5-2.
* Example 5-3 has been updated to more API2 code: DesktopApplication, DesktopWindow, DesktopButton, DesktopListbox, DesktopLabel, updated binary project, DesktopButton Pressed and Opening event, MenuBar, DesktopListbox CellTextAt, and DesktopTextField.
* Updated the following code and controls in Example 5-3: binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopListbox, DesktopLabels, DesktopButton Pressed and Opening Event, DesktopListbox CellTextAt, MenuBar, DesktopTextfields, and DesktopGroupBox.
* Example 5-4 has updates which include: binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopListbox, DesktopLabels, DesktopButton Pressed and Opening Event, DesktopListbox CellTextAt, MenuBar, DesktopTextfields, and DesktopGroupBox.
* The following code and controls were updated: DesktopApplication, DesktopWindow, DesktopButton, binary project, Pressed and Opening Event, MenuBar, DesktopListbox, CellTextAt, DesktopTextField, and DesktopLabel for Example 5-5.
* Example 6-1 has the following updates: updated binary project, DesktopApplication, DesktopWindow, MenuBar, DesktopButton, Pressed and Opening event, DesktopListbox, CellTextAt, and DesktopLabel.
* Code and controls for Example 6-2 has been updated with the following items: xojo binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopListbox, DesktopGroupBox, DesktopButton Pressed and Opening event, MenuBar, DesktopListbox, CellTextAt, DesktopLabels, and DesktopTextFields.
* Further APII2 updates were created in Example 6-3 which contain the following code and controls: binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopListbox, DesktopGroupBox, DesktopLabel, DesktopTextField, DesktopButton Opening and Pressed events, MenuBar, and DesktopListbox CellTextAt code.
* Example 7-1 has had updates to code and controls which are: DesktopApplication, binary project, and MenuBar major modifications.
* Updates to code and controls for Example 8-1 include the following: DesktopApplication, DesktopWindow, binary project, DesktopListbox, DesktopButton, DesktopButton Pressed event, DesktopListbox CellTextAt code, and MenuBar.
* Control updates to Example 8-2 with controls have occurred in this project: binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopLabel, DesktopButton Pressed event, and MenuBar.
* Example 9-1 project had the following updates: DesktopApplication, binary project, DesktopWindow, MenuBar, DesktopLabel, DesktopButton, and DesktopButton Pressed event.
* Updated to API 2 with the latest code and controls, binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopLabel, DesktopButton Pressed event, and Menubar.
* Upgraded Example 9-3 to API 2 code with the following modifications: DesktopWindow, DesktopApplication, binary project, MenuBar, DesktopLabel, and DesktopButton.
* Example 9-4 updated with the following controls: DesktopApplication, MenuBar, binary project, DesktopWindow, DesktopLabel, DesktopButton, and Pressed event for DesktopPushbutton.
* Controls were updated as DesktopApplication, binary project, DesktopWindow, DesktopLabel, MenuBar, DesktopButton, DesktopButton Pressed Event for Example 9-5.
* Updated the following controls in Example 10-1 : DesktopApplication, binary project, MenuBar, DesktopWindow, DesktopButton, DesktopListbox, DesktopListbox CelltextAt command, DesktopButton Opening and Pressed events.
* Example 10-2 updates include: binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopTextField, DesktopLabel, DesktopListbox, MenuBar, DesktopListbox CellTextAt command, DesktopButton Opening and Pressed events.
* Formatting numbers in Example 11-1 example was update with the following code and controls, DesktopApplication, binary project, DesktopWindow, ManuBar, DesktopButton, DesktopButton Pressed event, DesktopListbox, DesktopListbox CellTextAt command.
* API2 code has been updated in Example 12-1 with the following code and controls: DesktopApplication, binary project, DesktopWindow, DesktopButton, DesktopButton Pressed event, MenuBar, PrinterSetup ShowPrinterDialog code, ReportLabel, and update code to new API2 code.
* Example 12-2 is updated to API2, updating DesktopApplication, DesktopWindow, DesktopButton, binary project, DesktopButton with Pressed method, MenuBar, OpenFileDialog, and MessageDialog Show command.
* New report created in Example 12-3 with updates, DesktopApplication, binary project, DesktopWindow, DesktopButton, MenuBar, and rewrote printing code.
* Example 12-4 has updates to: DesktopApplication, binary project, DesktopWindow, DesktopButton, DesktopButton Pressed Event, and DesktopMenuBar.
* API2 has been updated along with code and contains edits to the following controls and code: DesktopApplication, binary project, DesktopWindow, DesktopButton, DesktopButton Pressed event, DesktopMenuBar for Example 12-5.
* Updated code to API2 with Example 12-6, and to the following controls: Binary project, DesktopApplication, DesktopWindow, DesktopButton, DesktopButton Pressed event, MenuBar,
* Example 12-7 code to more API2 compatible controls, such as: binary project, DesktopApplication, DesktopButton, DesktopWindow, DesktopButton Pressed Event, DesktopMenuBar, and updated code in the Pressed event.
* The following updates to code and controls to API2 have occurd in Example 12-8: DesktopApplication, binary project, DesktopWindow, DesktopButton, DesktopButton Pressed event, DesktopMenuBar, and updated printing code.
* Updated code and controls on Example 12-9 which are as follows: DesktopApplication, binary project format, DesktopWindow, DesktopButton, DesktopButton Pressed Event, DesktopMenuBar, and updated printing code in the Pressed event.
* Example 12-10 has more API2 code added, and the list of changes are: binary project, DesktopApplication, DesktopWindow, DesktopMenuBar, DesktopButton, DesktopButton Pressed Event, and ran into crashes. Code is included although there are bugs. Note: Multiple crashes have occurred with workarounds – this code is for testing and not to be used in a final project.
* Updated code and controls in Example 12-11 which include the following: Binary project, DesktopApplication, DesktopMenuBar, DesktopWindow, DesktopButton, and DesktopButton Pressed event. Although the landscape command worked on Printersetup, it does not work on the final print – code is for testing and not to be used in a final project.
* Due to a large number of errors in creating reports with Desktop Reports in API2, most report examples have not been updated in the book until creating reports have stabilized.
* Example 13-1 has been updated to API2 with the following modifications: DesktopApplication, binary project, DesktopWindow, DesktopButton, DesktopButton Pressed event, DesktopMenuBar, and DesktopLabel.
Updates to Version 2.4 -- August 2023
* Added Example to place SQLite data in a Listbox (Example 30-1)
* Added an Example to rearrange the Listbox data (Example 30-2)
* Added Example to save Listbox Data to an SQLite Database (Example 30-3)
Updates to Version 2.3 -- Jan 2023
* Provided description information on an SQLite one-to-many relational database (Beginning Chapter 28)
* How to add database tables that can be linked in a relational database(Example 28-1)
* Add data to a table that can be linked in the future in a relational database (Example 28-2)
* Add linked columnar data to an original table in a relational database (Example 28-3)
* An example on how to retrieve linked data from two tables in a relational database using INNER JOIN (Example 28-4)
* How to update data in a relational database (Example 28-5)
* How to query multiple tables in a relational database using WHERE AND (Example 28-6)
Updates to Version (2.2 -- October 2022):
* Performed updates to many of the examples (40+ examples) which prevent crashing when cancelling the use of FileDialog use in programs
* Added the reference to view Xojo SQLite encrypted databases with SQLite Manager
* Added information on the description of Write Ahead Logging.
* Changed the executable of some example programs to the default MyApplication or My Application
* Fixed issue with the user pressing cancel in Example 25-1
* Added BOM issue example with Example 26-1
* Added Save picture to database example (Example 27-1)
* Added load picture from database – Example 27-2
Updates to Version 2.1 – Feb 2021
* Grammatical changes
* Formatting changes
* Repopulated Example 8-1
* Performed many updates from CStr to ToString formatting
* Changed information on DPI for MaximumHorizontalResolution and MaximumVerticalResolution
* Remove the legacy Call function when no return value is needed for a method, on many examples
* Changed OLEException (Windows Only) to DatabaseException
* Write Ahead Logging is added in Example 25-2.
* Changed IntegerValue.ToString to StringValue
* Changed DoubleValue.ToString to StringValue
* Removed extra brackets from dlg.ShowModal
* Updated Example 15-3 to include a Table and three columns to allow 100 additions. Special thanks to Enric Herrera.