One of the relational database management systems found in a C library is SQLite. It’s a bit different from other database management systems, for it has a ton of data types. But one of the most common questions everyone asks is how to store byte array in SQLite database Android.
You need to follow a few simple steps to store the array—first, query data from the table. Then make sure you know the data type within the table in question. And then finally call upon the data with a simple read() command within your code. Obviously, make sure the command is as per the data type.
The steps mentioned above are simple but incomplete. There are a few complicated commands that you would have to do before you get the final product you wished for. But first, let’s learn a bit about SQLite.
What is SQLite?
A database management system that doesn’t use client-server but is embedded into the end program – that’s what SQLite is. It is simple to use and makes the C library work faster and smoother.
It generally follows PostgreSQL syntax. But the typed SQL syntax is not strong enough to guarantee domain integrity. This simplicity allows one to use it by just inserting a string into the column.
Within the Android platform, the use of SQLite is quite popular. The reason for its widespread usage is its simplicity and embedded nature. Such a library makes the data storage compact and swift.
Datatypes for SQLite
The Datatypes generality used for SQLite is dynamic. Thus the datatype of a value is connected with the value itself instead of the container. Primarily static data types are used. But the SQLite dynamic type system is compatible with most database engines.
Though it works backward, it will have the same effect. The static type databases will collect the data and store it in SQLite as it would have if the system were SQL.
But the thing that makes SQLite better is its options. This dynamic type allows things to be done that are impossible in traditional databases.
Some Storage Classes
No matter what data you want to be stored, they will have one of the following four classes –
- NULL
- INTEGER
- REAL
- TEXT
- BLOB
In most cases, storage class is indistinguishable from datatype.
For instance, an INTEGER storage class stores the data as a different value on the disk. But as soon as the value is read, it’s converted to a general datatype. Thus one can use the terms datatype and storage class interchangeably.
One thing that makes SQLite a bit problematic is it doesn’t have a specific storage class for boolean, date, and time. But it can store the values as TEXT, REAL, or INTEGER.
The applications can store these data in whatever class it prefers. Moreover, these formats can be swapped from one to the other freely.
Affinity Type
Take a look at this –
The code here is an example of a rigid type for a SQL database engine. These rigid typed databases convert integers into strings and vice-versa. Here ‘456’ is converted to 456 and 123 to ‘123’.
To make sure SQLite is compatible with such database engines, the example shown above will work similarly as it would have in the SQL engine. All this is possible because SQLite supports “type affinity” on columns.
By type affinity of a column, it refers to the recommended type of data for that column. But it’s not limited to that particular type. One can easily store data of a different kind over the recommended one.
In SQLite, each column is sure to have at least one of the following affinity types –
- TEXT
- NUMERIC
- INTEGER
- REAL
- BLOB
These affinity types, in turn, will be able to store three types of data.
For instance, a TEXT affinity column can be used to store NULL, TEXT, or BLOB. But if one tries to hold a numerical value in this column, the data will give a TEXT type output instead of the expected INTEGER/NUMERIC.
To make sure the application runs smoothly, one needs to be able to determine the column first. And to do so, just look at the declared type of the column. If you are confused as to what means what, then take a look at this table.
| Declared Type | Affinity Type |
| INTINTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 | INTEGER |
| CHARACTER(20) CHARACTER(70)CHARACTER(255)VARCHAR(255) VARYING NCHAR(55) NATIVE NVARCHAR(100) TEXT CLOB | TEXT |
| BLOB | BLOB |
| REAL DOUBLE DOUBLE PRECISION FLOAT | REAL |
| NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME | NUMERIC |
The following shows how SQLite uses column affinity and converts them to the required type –
Comparison Expressions
Different database management systems are quite dependent on the expressions that you use in the code. But that is not true in the case of SQLite. All the standard sets that one uses for SQL are applicable here.
One thing that is different in SQLite from SQL is that the comparison depends on the storage class. There are a few rules that the comparison follows. They are,
- NULL storage class is considered the lowest among all storage types.
- INTEGER or REAL values are lower than TEXT or BLOB values. But if compared to another INTEGER or REAL value, then the numerical values are taken into account.
- A BLOB value is greater than TEXT. And in a comparison between two TEXT values, a collating sequence is used as the basis of comparison.
- For a comparison between two BLOB values, memcmp() is used.
Here’s an example of comparison –
Operators to be Used
In the case of numbers, all the mathematical operators are useable. They can also be used for STRING or BLOB. This is possible because the STRING or BLOB operands are automatically converted to REAL or INTEGER values.
Here is how the conversion occurs:
- BLOB → TEXT → REAL
- The BLOB value is cast to TEXT by casting the sequence of bytes as per the BLOB value.
- Then to cast the TEXT value to REAL, interpretation of the longest possible prefix of the value that relates to a real number is done.
- Any remainder or leading spaces within the TEXT value are ignored.
- If the value has no prefix, then the result of the conversion is 0.0.
- BLOB → TEXT → INTEGER
- The TEXT value found from the BLOB is interpreted, and the longest prefix of value is cast to an integer number.
- Any remainder or leading spaces are ignored.
- If there is no long prefix that interprets to an integer, then the outcome is 0.0.
- In the case where the integer is greater or lower than ±9223372036854775807, then the result of the cast is precisely that.
- For a TEXT that might give FLOAT, the exponent is ignored.
The conversion is a bit faulty. It causes loss and might also make the data irreversible. But despite the flaws, STRING or BLOB type values are converted to REAL or INTEGER all the time.
A new condition is imposed if the mathematical operators in use are %, <<, >>, &, and |. These operators only work for INTEGER type values. So if the data is in the REAL class, then it’s converted to INTEGER.
For casting the REAL numeric to INTEGER, the following steps take place,
- REAL → INTEGER
- A REAL value cast to INTEGER gives a result between the REAL value and zero.
- If the value is greater or lower than ±9223372036854775807, the result remains the greatest or lowest possible INTEGER.
Sorting
Once the query results are sorted, only then can the conversion start. Any attempt to store class conversion before the sorting results in failure. If the sort is done by ORDER, the sequence would be,
NULL → INTEGER → REAL → TEXT → BLOB
If the sort is done as per GROUP, the sequence remains the same, but INTEGER and REAL values are counted under the same storage class. In this case, no affinities are applicable.
Read and Write The Data
Now that you know about the data types and storage classes, you also understand how to sort and convert them. But we still haven’t clearly talked about the steps that will help you read, write and store the data.
The steps that you will have to undergo to save any data type are similar. For simplicities sake, let’s see the steps for BLOB value.
Let the table in question be materials table. If you are willing to follow the steps, here is the table we would use for the example.
For this example, we have to store a picture for each value in the materials table. Here are the steps you would have to follow,
- First, add a column for the required data type. In this case, the data type is BLOB.
- Next, query data from the table for the content. This will give NULL value in the picture column.
- Now prepare the picture file that you want to save and place it in a folder. For instance, place it in C:\temp, similar to this picture.
- After you prepare the picture, you will have to ready up an UPDATE statement.
- Then get the Connection object by connecting to the SQLite database.
- Once connected, create a PreparedStatement object.
- Now supply the values using the set* method with corresponding parameters of the PreparedStatement object.
- Execute UPDATE statement with executeUpdate() method.
- Make sure to use setBytes methods similar to the following code,
- Next, you have to connect to the tset.db database by the connect() method as follows,
- The following code will update the picture for the specific materials table, and it uses the updatePicture() method,
- If you want to update the picture for material id 1, the following code can be used –
- Finally, execute the program again and check the materials table. If all steps were correctly followed, it would show something as follows –
You may also have interested to know:
How to stop casting chromecast Android?
How to download fb videos in Android?
How to combine two videos on Android?
Final Words
The “how to store byte array in SQLite database android” process is not tricky if you know the steps. Though the steps may seem confusing, they are not. You just need to practice them once, and you are good to go. If we were to give a gist of the steps, it would be,
Set up the Database → Insert the Array in the Database → Retrieve the Data
The steps are simple and easy. You won’t ever be confused if you read through the steps correctly.
