Memory classes are a set of five rudimentary sorts of data provided by SQLite. BLOB lets you supply files, photos, and additional media assets in a central database using a web browser. BLOB stands for “Binary Large Object”. It is a type of database with a capacity for storing an unlimited amount of data and has been used to store all types of records or information. This article will look at the use of Blob type in our table records.
Let’s get started with this article by logging in and then opening the terminal of Ubuntu 20.04. After opening it with Ctrl+Alt+T, we need to use the “update” instruction via the “apt” package to make our system up-to-date with new functionalities. To continue this process, it may require the password of a logged-in user. Add your password and continue.
After a successful update, we need to upgrade our system using the upgrade instruction with the apt package followed by the password for the currently logged-in user.
Meanwhile, the process may require your permission to continue upgrading the system as it requires 508 MB of space on the system.
After a successful update and upgrade, we need to launch the sqlite3 shell, i.e., C-module for SQL database on our terminal shell using the “sqlite3” instruction. The SQLite database shell will be generated, and we can now add commands to it.
Before looking at the BLOB type of SQLite database, we will first look at the most common and most used types of SQLite databases. For this, we will utilize the typeof() function within the SELECT instruction and the value inserted in the function parameters. So, we have been starting our first illustration with the use of SELECT instruction for the NULL value in the typeof() function parameters. The execution of this instruction has been returning the type null for a value “Null”.
After, we used the value “50” within the parenthesis of a typeof() function to find the type of a value using the SELECT instruction. It returns the type “integer” for the value 50. After that, we tried the value “10.9” within the typeof() function. This SELECT instruction displayed that the value 10.9 is a real number. The character “A” has been used with the single and double inverted commas within the next two SELECT queries inside the typeof() function. These two SELECT instructions return that the value “A” is text.
After this, we used the number 100 within the single inverted commas to determine its type using the typeof() function. It turns out that the value 100 within the inverted commas is taken as a text type. Let’s use the BLOB type value in the typeof() function within the SELECT instruction of the SQLite database. The BLOB value must contain the character “x” before starting a single quote, followed by the four character values. We have been using three commands to use the BLOB type values within the typeof() function. We have tried the first two values with the capital “X” character and the last value with the small “x” character in the instruction. The output for the previous three commands shows the type ‘blob’ for the three values.
sqlite> SELECT typeof(50);
sqlite> SELECT typeof(10.9);
sqlite> SELECT typeof("A");
sqlite> SELECT typeof('A');
sqlite> SELECT typeof('100');
sqlite> SELECT typeof(X'3100');
sqlite> SELECT typeof(X'1000);
sqlite> SELECT typeof(x'1000');
Now, let’s see how we can add BLOB-type data within the SQLite table of Ubuntu 20.04. First, we just need a table in our database. So, we have been creating the table “Test” with the CREATE TABLE command. This table will contain two columns, i.e., ID and Types. The ID column will contain Integer type data and will be a primary key as specified.
After creating a table, we inserted a total of six records within it using the INSERT INTO instruction, followed by the table’s name. The first record contains the integer, the second contains the real value, the third and 4th contain the text type value, the 5th record contains the NULL, and the last record includes the BLOB-type value. After adding these records, we have tried to fetch all columns of a table “Test” along with the use of the “typeof()” function on the “Types” function to show the types. It turns out that the records have been displayed in the previously explained format.
sqlite> INSERT INTO TEST(Types) VALUES (10);
sqlite> INSERT INTO TEST(Types) VALUES (10.99);
sqlite> INSERT INTO TEST(Types) VALUES ('678');
sqlite> INSERT INTO TEST(Types) VALUES (99.99);
sqlite> INSERT INTO TEST(Types) VALUES (NULL);
sqlite> INSERT INTO TEST(Types) VALUES (X'9999');
sqlite> SELECT ID, Types, typeof(Types) FROM Test;
You can also organize the table according to your choice using the ORDER BY clause and the name of a column that will maintain equality using the “Types” function.
Within the previous example, we have displayed the BLOB-type value in the last part. Use the INSERT INTO instruction again to insert all the four BLOB-type values in the table “Test” using the same instruction.
sqlite> INSERT INTO TEST(Types) VALUES (X'2414');
sqlite> INSERT INTO TEST(Types) VALUES (X'0000');
sqlite> INSERT INTO TEST(Types) VALUES (X'0234');
After inserting four new records, we have been using the ID and Types column within the Test column to display all the records that contain the type ‘blob’ within them using the typeof() function within the query. It returns five records for the type BLOB, as shown below:
Using the SELECT instruction with the simple “*” character followed by the FROM clause containing the name of a table “Test”, we have listed all the 10 column values in the table.
Let’s type the values from the table where the type is ‘blob’ as per the DELETE instruction and display the rest. It shows the five records have been left.
Conclusion
This article was about using the BLOB type in the tables of SQLite. We inserted the BLOB records, displayed them using the typeof() function, and deleted them. You can use all these queries in any operating system and get the same result we presented in this article.
