SQLite
SQLite
Contents |
SQLite: free embedded database in TD applications 
SQLite is a small in-process library (dll) that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.
SQLite is the most widely deployed database in the world with applications on different platforms, like Android, Windows and IOS.
It is used by well known applications like Firefox, Chrome, PHP, Python, Dropbox, Television sets and set-top boxes to name a few.
SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process.
It reads and writes directly to ordinary disk files or straight to memory.
A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.
The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems.
SQLite does not need to be "installed" before it is used. There is no "setup" procedure.
There is no server process that needs to be started, stopped, or configured.
There is no need for an administrator to create a new database instance or assign access permissions to users. SQLite uses no configuration files.
Nothing needs to be done to tell the system that SQLite is running.
SQLite is not directly comparable to client/server SQL database engines such as SQLBase, MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.
Client/server SQL database engines strive to implement a shared repository of enterprise data.
They emphasize scalability, concurrency, centralization, and control.
SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.
Because SQLite is used widely, there is a huge and active userbase creating scripts and tools concerning everything related to SQLite.
A lot of information, examples and extensions can be found on the internet.
For example, there are free SQLite IDE toolsets which are very powerful and give you an easy way to create or open databases and browse and update the contents and database schemas.
SQLite and TD applications
The advantage of embedded versus server based databases in your application is that you do not need to setup a system and a server to be able to access or store data.
Databases like SQLBase need a server process running (on a remote server or locally) and need to be configured before it can be used.
For small to medium sized applications it could be overkill to have fully featured (expensive) database servers running.
You might consider SQLite for your TD application in these cases:
- You need to store data like usage history, user application settings, logging or temp data
- Have predefined data ready to be used, for instance lists of codes and descriptions, images or other text or binary data which would not be stored on a server
- Prototype, test or demo your application with a full set of demo data without having a running database server
- Instead of writing data to files, store them in a single SQLite database file wich can be queried using normal SQL
- Cache data from servers locally to improve performance. They can be kept in local database files or even better straight in memory
- Failover features. When no network is present due to failures, you can save application data to a local database and later sync them when network is online again.
- Data analysis: store business data in local database and query it using the powerful SQL language features to analyze the data
- Transfer data from one system to another. Instead of XML or CSV formats to export and import data, you might use SQLite database files which store data rationally.
(any many more)
SQLite API
SQLite can be used two ways:
- Include the C(++) or .NET sources into your projects and use SQLite features. There are many more programming languages supported.
- Execute SQLite features from a precompiled build (dll) using the standard exported functions
This article will discuss the use of the precompiled dll having SQLite features as exported functions.
From the official SQLite website you can download the binary DLL for Win32 and Win64.
It consists of just one dll file, named SQLite3.dll. This dll is the only file you need to have SQLite working from TD applications.
And it does not need any registration or configuration.
It is ridiculously small, about 900Kb, when considering the features it gives.
You have to consider that SQLite uses UTF8 by default for the contents of the database and the API functions.
SQLite offers also functions supporting UTF16.
Team Developer comes in two flavours: TD versions up to version 4.2 are ANSI based and starting from TD 5.1 it is UNICODE based.The latter is UTF16.
This means that when we use SQLite exported functions, we need to convert ANSI <-> UTF8 for older TD versions and UTF16 <-> UTF8 for newer TD versions.
Fortunately, SQLite API has extra functions to do those conversions.
TD SQLite API libraries
To use SQLite within TD, a set of TD include libraries (apl's) have been created for this article. Depending on which TD version you use (ANSI or UNICODE) you need to include the appropriate library in your project.
The TD SQLite API libraries contain global wrapper functions which makes the use of the API easier and will do the needed conversions for you.
You can still use the API directly if needed. Most SQLite external declarations are present. But the wrapper functions makes it possible to have your application compatible
with both older and newer versions.
These libraries are present:
- sqlite3.dll
This free binary file comes straight from the SQLite website download page. It is not changed in any way and is the original SQLite dll.
New versions are released regularly and can be downloaded from the official website. When a new version is released, just replace the old one with the new one.
- SQLite3_API_Base.apl
This TD library contains all SQLite constants to be used in the several exported functions.
Also, SQLite exported functions which do not need UTF8 conversions are located here. This library is used by both ANSI and UNICODE TD versions.
The contents of this library is thus shared.
- SQLite3_API_ANSI.apl
This TD library includes SQLite3_API_Base.apl and has wrapper functions to use specific UTF8 functions from ANSI TD versions.
If your project is using TD 2.1 up to TD 4.2, this library needs to be included.
- SQLite3_API_UNICODE.apl
This TD library includes SQLite3_API_Base.apl and has wrapper functions to use specific UTF8 functions from UTF16 (UNICODE) TD versions.
If your project is using TD 5.1 or up, this library needs to be included.
Do not include the ANSI library in UNICODE TD versions or the UNICODE library in ANSI TD versions!!!
SQLite API external declarations and TD wrapper functions
All SQLite exported functions have this prefix in their name: sqlite3_
(for example : sqlite3_open)
So, you will know when the function name starts with sqlite3_ it is the raw unwrapped SQLite API function.
Each sqlite3_* function has a corresponding TD wrapper function. Even when there is no conversion needed, it is present.
TD wrapper functions have this prefix in their name: SQLite_
(for example : SQLite_Open)
It is recommended to always use the TD wrapper functions in your projects. It is easier to correct API issues and extra actions when a TD wrapper function is used.
Also, when using the TD wrapper functions, your code will be consistent and are portable to newer TD versions.
SQLite constants used in API functions are declared having a prefix: SQLITE_
(for example : SQLITE_OPEN_READWRITE)
SQLite Team Developer API in your projects
First, copy the SQLite dll to your runtime folder. This would mostly be at the location your TD build executable is located.
Then include the TD API library in your project:
- For ANSI TD versions -> SQLite3_API_ANSI.apl
- For UNICODE TD versions -> SQLite3_API_UNICODE.apl
Having done this, you have to check your project still compiles. If so, then the SQLlite dll and the TD library are correct and can be used.
SQLite TD API examples
Using SQLite databases is fairly easy. The API is straightforward and resembles how we code SQL in TD.
The next paragraphs will show you small examples to give you a clear start.
Create a new database and table
A SQLite database is just a single file on disk. We can also create a database in memory, but that will be explained later.
In this example we assume there is not yet a SQLite database. So we will create one from scratch.
To create a new fresh database (file) we do this:
Set nRet = SQLite_Open( "C:\\MyFirstDatabase.db", nDBHandle )
It will create the file MyFirstDatabase.db at the path given. Be sure the location is writable.
When no such file already exists, the file will be created. When it does already exist, it will just open a connection to that database.
The function returns an error code when it fails or SQLITE_OK when succeeds.
The nDBHandle parameter will receive the database connection handle to that database.
Further database actions will need this nDBHandle.
Now the database has been created, we need a table and some columns to store data.
Using a SQL statement, we can create a new table with the column definitions within the database.
So first we code the SQL statement and put that in a string variable:
Set sSQL = "
CREATE TABLE Persons
(
id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL
);
COMMIT;"
This contains two actions: first the creation of the table and lastly doing a COMMIT.
By separating SQL statements using ; we can put multiple actions in one.
Just like normal TD SQL actions, the SQL statement needs to be prepared first. This is to 'compile' the statement so that it is ready to be used by SQLite.
To prepare the statement we do:
Set nRet = SQLite_Prepare_v2( nDBHandle, sSQL, nStmtPtr )
We pass the database connection handle we got earlier and the sSQL variable containing the SQL statement.
When the statement is correctly prepared, we get back a statement handle (nStmtPtr).
We need this statement handle in further actions.
Now SQLite is ready to execute the statement. We execute it using this:
Set nRet = SQLite_Step( nStmtPtr )
We pass the statement handle we got from the prepare to the function.
After this line of code, the database will have a table called Persons having the columns we specified.
Insert some data
Now, we want to insert a person into the table. We can do this by specifying the values for the person columns within the SQL statement like this:
Set sInsert = "
INSERT INTO persons
(
id,
first_name,
last_name
)
VALUES
(
1,
'John',
'Doe'
);
COMMIT;"
But in this example we would like to use bind variables.
SQLite supports named binds. This means that a bind name is a placeholder for data.
This is different compared to binds in Team Developer, which are placeholders for variable names.
You have to imagine that SQLite named binds are labels which SQLite recognizes and expects that data is available for that label.
First we define the statement as follows:
Set sInsert = "
INSERT INTO Persons
(
id,
first_name,
last_name
)
VALUES
(
:ID,
:FIRSTNAME,
:LASTNAME
);
COMMIT;"
The named binds begin with : and a bind name. We can use any name we like as long it is unique within the statement.
SQLite, when executing the statement, will look for data which is added for the specific bind name (label).
But first we need to prepare the statement:
Set nRet = SQLite_Prepare_v2( nDBHandle, sInsert, nStmtPtr )
Now, before we execute this, the binds need to be handled. We have to give the data to be used for each bind to SQLite.
Using TD wrapper functions this is easy.
Let's pass the needed data for each bind using the bind name.
Imagine we have 3 variables containing the data we need to insert:
Set nID = 1 Set sFirstName = "John" Set sLastName = "Doe"
To assign the values to the appropriate binds we do this:
Call SQLite_BindInt_Name( nStmtPtr, ":ID", nID ) Call SQLite_BindText_Name( nStmtPtr, ":FIRSTNAME", sFirstName ) Call SQLite_BindText_Name( nStmtPtr, ":LASTNAME", sLastName )
See that we pass in the statement handle we obtained from the call to SQLite_Prepare_v2.
The second parameter is the bind name. Be aware that you must prefix the name with : to indicate it is a named bind. Do not forget this colon sign.
After this, SQLLite has the needed data linked to the bind names and is able to execute the statement.
So now execute it:
Set nRet = SQLite_Step( nStmtPtr )
When all is ok, the database has a person inserted into table Persons.
This is enough for this example. Now we need to cleanup and close the handles.
To inform SQLite to dispose internal data and the prepared statements we have some cleanup to do:
Call SQLite_ClearBindings( nStmtPtr ) Call SQLite_Finalize( nStmtPtr )
This will cleanup the bind data and close the statement handle.
Then we want to close the database connection:
Set nRet = SQLite_Close( nDBHandle )
We pass in the database connection handle obtained earlier by SQLite_Open.
Now we have a local database named MyFirstDatabase.db having one table Persons and one row with person John Doe.
Select rows from database table
In this example we have an existing database having one table Person and multiple rows.
So the database exists as file on the local drive and contains data.
We will open the database connection, prepare a SQL statement to get all persons from the table and have the person values in TD variables.
Ok, first open the database. We use another open function which offers more options. Here we want to open the database in READ ONLY mode.
This prevents the creation of a new database when the database does not exist on the local system and prevents any changes to the data.
Set nRet = SQLite_Open_v2( "C:\\MyFirstDatabase.db", SQLITE_OPEN_READONLY, nDBHandle )
When succeeded, we get a database connection handle to be used further.
The SQL statement to select all rows is as follows:
Set sSelect = " SELECT id, first_name, last_name FROM Persons ORDER BY last_name;"
See that SQLite does not support INTO clause. So, we do not specify where the data is fetched into.
This comes later, first we must prepare the statement:
Set nRet = SQLite_Prepare_v2( nDBHandle, sSelect, nStmtPtr )
Now we need to loop though the resultset. When calling SQLite_Step, we fetch one record from the resultset.
The return value when a row is fetched is : SQLITE_ROW
In code this would look like this:
While SQLite_Step( nStmtPtr ) = SQLITE_ROW ...get the data here Set nTotalPersons = nTotalPersons + 1
It will iterate through all rows.
On each fetched row, the data for the columns has to be fetched.
SQLite holds all data per row on column basis. This means that to get the value for id, first_name and last_name we have to ask SQLite to give the values for each column.
This can be done on column position, the index, (from left to right) or based on the name of the column in the resultset.
We will use the column name in the following example.
The persons fetched from the table will be put into an array of Person objects in TD.
While SQLite_Step( nStmtPtr ) = SQLITE_ROW Set uaPerson[nTotalPersons].nId = SQLite_ColumnInt_Name( nStmtPtr, "id" ) Set uaPerson[nTotalPersons].sFirstName = SQLite_ColumnText_Name( nStmtPtr, "first_name" ) Set uaPerson[nTotalPersons].sLastName = SQLite_ColumnText_Name( nStmtPtr, "last_name" ) Set nTotalPersons = nTotalPersons + 1
Above we use the column name to locate the data and get it into the person attributes.
Another way as explained earlier is using column position (index):
While SQLite_Step( nStmtPtr ) = SQLITE_ROW Set uaPerson[nTotalPersons].nId = SQLite_ColumnInt( nStmtPtr, 0 ) Set uaPerson[nTotalPersons].sFirstName = SQLite_ColumnText( nStmtPtr, 1 ) Set uaPerson[nTotalPersons].sLastName = SQLite_ColumnText( nStmtPtr, 2 ) Set nTotalPersons = nTotalPersons + 1
(column index is zero based. So column 0 is the leftmost column in the resultset)
The second way, using column index is more performant as it does not have to search for the column name.
Depending on your liking, use either way.
And then we need to cleanup and close
Call SQLite_ClearBindings( nStmtPtr ) Call SQLite_Finalize( nStmtPtr ) Call SQLite_Close( nDBHandle )
Memory databases
SQLite also supports databases solely within memory. This does not involve a file on disk.
Those databases are very performant as it does not need disk I/O.
There is no difference in features between disk or memory databases.
Obviously, a memory database is only available when the application is running.
When the application ends (or crashes) the memory database is gone.
This feature is especially handy when you need a fast local cache which is not too big but offers all SQL features you want.
It is also possible to load an existing file database to memory or to save a memory database to file.
You have to be aware that when you close a memory database handle, the database is cleared from memory.
So a memory database connection must be kept open for the time you need it.
To create a memory database, use a specific filename in the Open function: ":memory:".
So, here an example:
Set nRet = SQLite_Open( ":memory:", nDBHandle )
You can create multiple databases by calling the function above which will give you multiple database connection handles.
All SQLite functions work the same on memory databases as on file databases. So the examples given in this article will work on both.
TD SQLite sample application
To have a running example, a small demo has been created to show the use of SQLite in TD.
It will work out-of-the-box on ANSI or UNICODE TD versions.
You can open the sample apt file in any TD version starting from TD 2.1.
Be aware that you first need to uncomment the needed ANSI or UNICODE library in the library section of the sample source.
The sample starts by creating a memory database which holds persons in a Persons table.
It also loads country information from a local file database named Countries.db. It contains all countries in the world with their codes, names and geographic locations.
To show that SQLite can handle blobs, another local file database is used containing all country flags.
The flags are as PNG and BMP format in several sizes.
The first screen of the sample application shows the list of persons in the memory database. It is empty when starting.
Using the NEW, EDIT and DELETE buttons you can manage the list of persons. Each person is added, edited or deleted using SQL statements on the memory database.
The list of countries (country combobox) is coming from the local countries database.
You are able to save the memory persons database to a file on disk and can load it. This shows the use of the backup functionality of SQLite.
The Countries button will open a new screen showing all countries with their flags in an outline listbox. You can display the different sizes of the flags.
They are loaded separately from the CountryFlags database.
As a gimmick, the buttons WIKI and MAPS will open a browser to show info about the country and display the country in Google Maps.
Finally
This article just scratched the surface of possibilities using SQLite. You can create huge databases and complex SQL statements as you are probably using on other databases like SQLBase.
It is advised to read the documentation of SQLite on the official website. It contains clear descriptions of all API functions and possible usage of the features.
Recommended is to use one of the free SQLite IDE's which are available. They offer nice tooling to view the contents of SQLite databases, inspect the schema's and offer online SQL execution.
It is easy to create new databases, design the tables and columns and insert/edit data. Importing complete CSV's as tables into SQLite or exporting them is done by just a few clicks.
Have a look at SQLiteStudio:
When you have questions, it can probably be found by searching it in Google. There are a lot of resources to query.
The provided TD libraries are not completely covering all SQLite features. Some need specific technical options of the c(++) environments like callbacks.
But the TD library will be extended in future to be mostly feature complete. When you personally get extra features working, please let me know so it can be added to the TD library toolset.
Here you can download the SQLite TD API and the sample:
Sqlite3_API.zip
SQLite .NET Functional Class
By Alfredo Monasi
The idea of this library is that you can use SQLite on your projects.
It's easy to implement and use, I only probe it with strings and numbers but shouldn't be a problem using dates.
How to use
- In your current project use tools / .Net Explorer / Choose the assembly file and pick up DB.dll
- (you also need in the same directory System.Data.SQLite.dll and System.Data.SQLite.Linq.dll)
- The proxy classes will be generated and a DB_SQLiteGupta will be able to use in your project.
Note: I had to modify the apl of the proxy class when passing two arrays, when parsing the second array
I had to reset the counter otherwise the second array would travel empty.
Functions
| ClearDB | Delete all rows of all user tables of the database. |
| ClearTable | Delete all rows of a specific table. |
| Delete | Do a delete sql sentence, need to specify a table, and the where condition. |
| ExecuteNonQuery | Execute a sentence, could be a create table, create index, alter, drop, even insert, delete or update. You need to specify the command, the second parameter return the rows affected in the operation. If successful the operation execute an autocommit else return false. |
| Execute Scalar | Return a String of a sql select command, if command return more than one row only take the first row and the first column. |
| ExecuteTransaction | Receive an Array of sql statements, if everything is correct will commit all the transaction otherwise will be rollback. |
| Insert | Receive one String (the table name) and two arrays, the first one is for the column names and
the other for the values. Internally build the insert statement and execute it. |
| mostrarError | Return the last error message. |
| SQLiteGupta_Overload1 | Specify the path and the database-name and create it or use the database-file. |
| SQLiteGupta_Overload2 | Need two arrays, the first one are parameters of connection and the second array is the value option for each parameter connection supported by SQLite3 implementation, look for documentation. I haven't test this function personally. |
| SQLiteGupta_Release | Dispose all the objects related to the class. |
| Update | Receive one String (the table name), two arrays, the first one is for the column names and the other for the values. Internally build the update statement and execute it. The last parameter is where condition of the update statement. |
| Update | Receive one String (the table name), two arrays, the first one is for the column names and the other for the values. Internally build the update statement and execute it. The next parameter is where condition of the update statement. The last parameter return the number of rows affected by the update. |
| WriteXMLResult | Receive a query statement and if it's valid write a XML in the path specify in the second parameter. |
Any bug report, doubt or problems please feel free to write me an email:
Email : Alfredo Monasi
Here to download the archive (version 1.0, TD 6.2):
SQLite_Monasi.zip
