I wrote a SQLite class for Xojo as a convenience for myself as I got tired of rewriting database code anytime I wanted to create a simple application in Xojo.
Xojo is an IDE and GUI designer that uses the Basic programming language. It compiles to Windows, Mac OS X, Linux, Android, iPhone, and web. For Linux you can run your application for free. A license to compile for Windows or OS X run around $99.00
I did not write this class for web, tablets, or smart phones so it is untested on those platforms. I see no reason for it not to work. Be aware that the folder save location will likely need changed for those platforms if you give it a try.
The class also utilizes prepared statements for inserts and updates. It is noted on the Xojo site that prepared statements are not compatible with Android.
The class is easy to use. Simply import into the project, add a couple sittings, and start using. You can import and review the code or make any change you think will make it better for your projects. I offer this free of charge with no licensing constraints.
I have included a working project to see how everything works at runtime. There you can review the code for each method. Learn from the code, or use it.
Download the files:
Sha256 to verify the download: a38e64f3e95cfb7409ba7a5cefcf109e28579034ba14865cb2d4fe989bae1fb7
Setup
1. Create a new Xojo project and import this class (SQLiteHelper.xojo_binary_code). File, Import.
2. Create a Constant under App. Right click App, Add To “App”, Constant.
3. Name it DBName and make it string. Add the preferred name of your database file, i.e., user.sqlite, as the default value.
4. Create a property under App. Right click App, Add To “App”, Property. Name it DB and set as SQLitedatebase.
5. Create another property under App named AppName and set as String.
6. Add an Opening EventHandler under App. Right click App, Add to “App”, Event Handler, Opening. Add the following code to the App Opening Event and adjust the SQL to your needs.
' Creates the folders and database on first run.
Me.AppName = App.ExecutableFile.Name ‘ IMPORTANT: Creates a folder with the name of your program under Documents.
' Instantiate our database helper Class.
Var mydb As New SQLiteHelper
DIM sqldict As New Dictionary
DIM retVal As Boolean
Dim sql_create_sales As String
Dim sql_create_deductions As String
Dim sql_create_options As String
sql_create_sales = "CREATE TABLE IF NOT EXISTS sales (id INTEGER PRIMARY KEY, product_sold TEXT NOT NULL, sold_date DATE NOT NULL, sold_amount DOUB NOT NULL, service_name TEXT, service_fee DOUB, shipping_cost DOUB, tax DOUB, refund TEXT, image_path TEXT, notes TEXT);"
sql_create_deductions = "CREATE TABLE IF NOT EXISTS deductions (id INTEGER PRIMARY KEY, deduction_name TEXT NOT NULL, deduction_date DATE NOT NULL, deduction_amt DOUB NOT NULL, mileage TEXT, notes TEXT);"
sql_create_options= "CREATE TABLE IF NOT EXISTS options (id INTEGER PRIMARY KEY, tax_rate TEXT, report_path TEXT);"
‘ Pass the SQL to the Class.
sqldict.value("sales")=sql_create_sales
sqldict.value("deductions")=sql_create_deductions
sqldict.value("options")=sql_create_options
' Create the database and tables. Return is a boolean, True = successful.
retVal = mydb.CreateNew(sqldict)The setup should look similar to this.
Testing
The included project includes all the testing code in the Window’s Opening Event. It will show you how to use each method in the Class. The methods are
- CreateNew(statements As Dictionary) Returns Boolean
- InsertRow(table As String, Fields() As String, Values() As Variant) Returns Boolean
- UpdateRow(table As String, Fields() As String, Values() As Variant, Where() As Pair) Returns Boolean
- DeleteRow(table As String, Where() As Pair) Returns Boolean
- SelectRows(statement As String) Returns RowSet
You do not need to write SQL statements for Inserts, Updates, and Deletes. If you need more complicated SQL, you may need to add your own method to the class to handle it. I did think about adding a bulk insert, but most event driven applications are simple enough that it is not needed.
Sha256 to verify the download: a38e64f3e95cfb7409ba7a5cefcf109e28579034ba14865cb2d4fe989bae1fb7
Tech. junkie, artist, coder, and supporter of open source.
