Accessing SQLite databases with FreeBASIC.
FreeBASIC includes by default a library called sqlite3.bi, that includes subroutines to access SQLite 3 databases. This tutorial shows (without giving details) sample FreeBASIC code that you can use to select data from a SQLite database, as well as to insert or update data in(to) a SQLite database. The tutorial samples have been written and tested on my Windows 10 Potato, using FreeBASIC 1.10 64-bit and SQLite 3.5 64-bit (file = sqlite-dll-win-x64-3500400.zip). It should work with newer versions of FreeBASIC and SQLite and on other recent Windows releases, too (I suppose). The tutorial pre-supposes that you have FreeBASIC installed as described in my tutorial Developing FreeBASIC applications on Windows 10.
Installing SQLite.
In fact, there is nothing to install; SQLite is distributed as a DLL, that has to be linked into your executable (SQLite is what is called an "embedded database"). You can download the SQLite files from the SQLite Downloads page. The only file required is the ZIP containing the DLL, but I suggest that you also download the archives containing the corresponding SQLite Tools and the documentation.
Concerning the tools and the documentation, unpack them into some folder; I chose C:\SQLite and C:\SQLite\doc respectively.
The archive sqlite-dll-win-x64-3500400.zip actually contains 2 files called sqlite3.dll and sqlite3.def. Normally it should work copying these files to one of the Windows system folders (C:\Windows\SysWOW64 or C:\Windows\System32). However, FreeBASIC does not find the DLL, when placed in either of these directories. The simplest way to do is probably to place the DLL and DEF files together with your FreeBASIC source file.
FreeBASIC includes a minimalist SQLite database called sqlite_test.db in the examples\database directory. It contains only 3 3-fields records, but this is enough for our simple test programs.
The screenshot below shows how I had a look at this database using the SQLite command line client sqlite3.exe (included in the "tools" archive). You find some details about this program in my tutorial Web development environment setup on MS Windows: SQLite embedded databases.
|
Installing DB Browser for SQLite.
If you intent to seriously use SQLite, you should install a GUI client. One possibility among others is the open-source application DB Browser for SQLite, that you can download as Windows installer from sqlitebrowser.org. During installation, you are told that several files, actually opened by other applications, have to be modified. This may be done either by closing and reopening these applications, or done during a reboot of the computer. Whereas the first option is set as default, I suggest to use the, certainly more secure, second one.
|
The screenshot shows the sqlite3_test.db database, opened in DB Browser for SQLite with the structure of the features table displayed.
|
Opening and closing a database.
Obvious, that you have to open the database, before you can access it. Also, you should close it before terminating the program.
"Typical" code to do this (for our sqlite3_test.db database):
#include once "sqlite3.bi"
Dim As sqlite3 Ptr db
If sqlite3_open("sqlite3_test.db", @db) <> SQLITE_OK Then
Print "Could not open database: "; sqlite3_errmsg(db)
sqlite3_close(db)
End 1
End If
--- your program logic ---
sqlite3_close(db)
Program sample 1: SQL SELECT.
The sqlite3.bi library includes subroutines that offer several ways to select data from a SQLite table. The way, used here, first prepares the SELECT statement (continuing only if the function return is SQLITE_OK). One of the arguments of the function is a variable of type sqlite3_stmt Ptr, that can be used to step through the returned dataset, row by row. As long as there is row data, the content of the different fields of the record, making up the actual row, can be retrieved by passing an index (0 for the first field, 1 for the second, etc) to a library function, specific for the field's data type (you have to use cast for text content conversion).
Here is the FreeBASIC source code of the program sample sqlite1.bas, that displays all records of the features table:
#include once "sqlite3.bi"
Const database As String = "sqlite3_test.db"
Dim As sqlite3 Ptr db
Dim As sqlite3_stmt Ptr records
Dim As String sql
Print "Test connection to SQLite database." : Print
If sqlite3_open(Database, @db) <> SQLITE_OK Then
Print "Could not open database: "; sqlite3_errmsg(db)
sqlite3_close(db)
End 1
End If
sql = "select * from features"
If sqlite3_prepare_v2(db, sql, -1, @records, 0) <> SQLITE_OK Then
Print "Error preparing statement: "; sqlite3_errmsg(db)
Else
While sqlite3_step(records) = SQLITE_ROW
Print "ID: "; sqlite3_column_int(records, 0)
Print "Title: "; *cast(zstring Ptr, sqlite3_column_text(records, 1))
Print "Description: "; *cast(zstring Ptr, sqlite3_column_text(records, 2))
Print
Wend
End If
sqlite3_finalize(records)
sqlite3_close(db)
The screenshot shows the program output.
|
Program sample 2: SQL INSERT.
Inserting a record into a SQLite table is easily done using the sqlite3_exec function. Note that with this function, the error message is returned as one of the function arguments, actually of type zstring Ptr.
Here is the FreeBASIC source code of the program sample sqlite2.bas, that inserts a fourth record into the features table.
#include once "sqlite3.bi"
Const database As String = "sqlite3_test.db"
Dim As sqlite3 Ptr db
Dim As zstring Ptr errMsg
Dim As sqlite3_stmt Ptr records
Dim As String sql
Print "Test connection to SQLite database." : Print
If sqlite3_open(Database, @db) <> SQLITE_OK Then
Print "Could not open database: "; sqlite3_errmsg(db)
sqlite3_close(db)
End 1
End If
sql = "insert into features(Id, Title, Description) " _
& "values(4, 'Command line programs and GUI applications', " _
& "'GUI application creation using the Windows API or visual designer based frameworks')"
If sqlite3_exec(db, sql, 0, 0, @errMsg) <> SQLITE_OK Then
Print "Error inserting data: "; *errMsg
sqlite3_free(errMsg)
End If
sql = "select * from features"
If sqlite3_prepare_v2(db, sql, -1, @records, 0) <> SQLITE_OK Then
Print "Error preparing statement: "; sqlite3_errmsg(db)
Else
While sqlite3_step(records) = SQLITE_ROW
Print "ID: "; sqlite3_column_int(records, 0)
Print "Title: "; *cast(zstring Ptr, sqlite3_column_text(records, 1))
Print "Description: "; *cast(zstring Ptr, sqlite3_column_text(records, 2))
Print
Wend
End If
sqlite3_finalize(records)
sqlite3_close(db)
Program output.
|
Program sample 3: SQL UPDATE.
As inserting a record into a SQLite table, updating a record in a table is done using sqlite3_exec.
The FreeBASIC program sample sqlite3.bas reads the title of the record with Id = 3 into a variable, modifies the content of this variable (replacing the substring "libs" with "libraries"), then writes the modified title back to the features table using SQL UPDATE.
#include once "sqlite3.bi"
Const database As String = "sqlite3_test.db"
Dim As sqlite3 Ptr db
Dim As zstring Ptr errMsg, oldtitle
Dim As sqlite3_stmt Ptr records
Dim As String sql, newtitle
Dim As Integer i
Print "Test connection to SQLite database." : Print
If sqlite3_open(Database, @db) <> SQLITE_OK Then
Print "Could not open database: "; sqlite3_errmsg(db)
sqlite3_close(db)
End 1
End If
sql = "select Title from features where Id = 3"
If sqlite3_prepare_v2(db, sql, -1, @records, 0) <> SQLITE_OK Then
Print "Error preparing statement: "; sqlite3_errmsg(db)
Else
If sqlite3_step(records) = SQLITE_ROW Then
oldtitle = cast(zstring Ptr, sqlite3_column_text(records, 0))
i = Instr(*oldtitle, "libs")
newtitle = Left(*oldtitle, i - 1) & "libraries"
newtitle = newtitle & Mid(*oldtitle, i + 4, Len(*oldtitle))
sql = "update features set Title = '" & newtitle & "' where Id = 3"
If sqlite3_exec(db, sql, 0, 0, @errMsg) <> SQLITE_OK Then
Print "Error updating data: "; *errMsg
sqlite3_free(errMsg)
End If
End If
End If
sql = "select * from features"
If sqlite3_prepare_v2(db, sql, -1, @records, 0) <> SQLITE_OK Then
Print "Error preparing statement: "; sqlite3_errmsg(db)
Else
While sqlite3_step(records) = SQLITE_ROW
Print "ID: "; sqlite3_column_int(records, 0)
Print "Title: "; *cast(zstring Ptr, sqlite3_column_text(records, 1))
Print "Description: "; *cast(zstring Ptr, sqlite3_column_text(records, 2))
Print
Wend
End If
sqlite3_finalize(records)
sqlite3_close(db)
Program output.
|
All this is, of course, just a rudimentary introduction to using SQLite with FreeBASIC. But nevertheless, I think that the examples shown should make it possible for you to work with data stored in a SQLite database using the FreeBASIC programming language...
If you find this text helpful, please, support me and this website by signing my guestbook.