Please enable JavaScript to view this site.

Waijung 2 User Guide

Navigation: ESP32 target > Block references > IOT

Sqlite3 database block

Scroll Prev Top Next More

How block appears in the Simulink model?

sqlite3_database_block_1

Input ports and output ports will automatically be added as the user configured the block.

What can be configured?

Mainly there are two modes

1.From Input Port

2.Generate Query

From Input Port

This will create one input port and allow users to add queries from that port as a char array. There will be two output ports for the block. One for the search results and another one to give the status of the SQLite block.

In the picture below it shows how the block and block mask looks like when the user selects this mode.

sqlite3_database_block_2
sqlite3_database_block_3

Generate Query

This mode is for beginners as well as experts. This mode will open a variable number of ports, depending on the content in the text area. Like in the string processing block it allows the user to add an external number or string to the query. The outputs also use a variable amount of outputs depending on how many search queries are in the text area and one status port.

Users can type the query by themselves in the text area without ticking out the easy query builder check box. If anyone ticks the checkbox it will display a section that helps users to select some general SQLite commands such as INSERT, UPDATE, DELETE and SELECT. Each command can be customized before and after adding.

*Note: Command will only append one after another when clicking the append button

Mask and block in this mode as shown below. Note that the content has queries to show how input and output ports work.

sqlite3_database_block_4
sqlite3_database_block_5

*Note: There is a checkbox to add two queries that need to be there to support ACID properties, it will be added automatically when the user ticks the checkbox. It will only display if the user selects generate query mode and easy query builder.

*Note: Please use only BEGIN TRANSACTION & END TRANSACTION to use the ACID properties

sqlite02

Configuration Parameter

Selectable Option/Value

Description

Database path

 

Enter the path for the database file in the SD card or Spiffs partition.

Output data buffer size

32--64--128--256--512--1024

The data buffer will only output the data on the selected database up to the selected buffer size.

Query mode

From Input Port--Generate Query

Mode select how the user enters data to the DB

Easy query builder

 

Beginner mode to generate simple DB commands by the user

Add queries

Create--Insert--Update--Delete--Select

Choose the query that needs to proceed

Create Table

 

Section to edit the DB create table command

Insert value

 

Section to edit the DB insert command

Update value

 

Section to edit the DB update command

Delete value

 

Section to edit the DB delete command

Search

 

Section to edit the DB search/select command

Append new query

 

Append new query to the textarea with the section that user select and edited

Support acid properties

 

To support acid properties tick this

Sample time

 

Sample time to run the block

SQL query

 

Textarea where all the DB commands are listed/add in the generate query mode.

Pointer Settings

 

Configuration of pointer settings

Demo

Example 1:

Demo file : esp32_sqlite3_demo.slx (Using SD card)

sqlite3_database_block_6

Components under triggered subsystem.

sqlite3_database_block_7

Description

This demo shows how to use the Sqlite3_database_block. Writing to the database and reading from the database file can be achieved from this block. Main components used in this example are Sqlite3_database_block, SD_card_block, String_processing_block & Print_block.

SD_card_block is required to this block to initialize the sqlite block database file location. String processing block & print block is for processing the text and output the search results from the Database file.

What should be happening?

Pulse generator will trigger the subsystem which holds the sqlite3 block. In the sqlite block there are simple sqlite commands to create database, write to the database and read from the database. There are two variables holding the data to be written into the database: the id and name. Each cycle id will count up the number starting from 1. These values will be inserted to the database.

User can check results from the serial monitor(baud rate:115200) as well as the SQLite DB Browser software(not real-time). To open the database from the DB browser user need to plug the sd card to the PC. The software can be downloaded from here.

sqlite3_database_block_8

Example 2:

Demo file : eps32_sqlite3_spiffs_demo.slx (Using SPIFFS partition)

 

sqlite3_database_block_9

Description

This demo shows how to use Sqlite3_database_block with the SPIFFS_block. Main components used in this example are Sqlite3_database_block, SPIFFS_block, String_processing_block & Print_block.

Since we use the SPIFFS partition we don't need the SD_card_block. String processing block & print block is for processing the text and outputting the search results from the database file.

What should be happening?

In this example, we set the sample time to 2sec. Thus, every 2sec number will be added 1 and sent to the sqlite block with the static string value. Sqlite block will execute 3 queries. Create database if not exist, write and read from the database. Users can check results from the serial monitor(baud rate:115200).

Note : since we use SELECT * FROM table1 ORDER BY id DESC LIMIT 1 command to search it will only show 1 value ordered by the id.

 

Copyright 2024 Aimagin Co.,Ltd. Rev.1670