Skip to main content

Create Datasource with SQLite

Introduction SQLite

This document contains information about the SQLite database how to install and connect with OPNBI.This document has step-by-step information on how to install, connect SQLite Database with OPNBI and will show one use case example.

SQLite is a widely-used lightweight, embedded relational database management system (RDBMS). SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is largely SQL standards-compliant, and it is fully ACID compliant.

Objective

To connect SQLite Database with OPNBI.

Prerequisites

  1. SQLite Jar

  2. SQlite Database

Skill Level Required

Basic knowledge of Database.

1. Download SQLite JDBC driver:

To get the JDBC driver, Go the vendor's end and download the SQLite-connector-java.jar Driver file from here as shown in figure below:

Docusaurus Slash Introduction

After downloading the jar file, add it into the OPNBI.

Note:
  • To add jar go to the Directory where OPNBI installed OPNBI\apache-tomcat-8.5.61\webapps\OPNBI\WEB-INF\lib folder and add the .jar file here.

  • OPNBI: OPNBI is the folder name where OPNBI is installed.

  • Apache-tomcat-8.5.61: Apache Server [Apache folder is located inside the OPNBI folder, suffix version may change]

Restart the Tomcat server after adding a jar in the lib folder.

2. Download SQLite from here.

It will redirect on new page select Precompiled Binaries for Windows and click on SQLite-tools-winn32-x86-3370200.zip file to download.

Go to the folder where you have downloaded this file and extract the zip file.

When you go inside the folder you will be able to see three executable files.

Docusaurus Slash Introduction

Copy the SQLite folder and paste it into your desired location, here I am adding in C drive.

We need to set the environment variable for SQLite, for that we have to copy the path of SQLite (C:\sqlite3)

Click on Windows icon and search edit the system Environment variable >> environment variable

Select Path – System Variable >> Edit button

Docusaurus Slash Introduction

Click on New Button >> Paste the path of SQLite Folder, as shown in figure below:

Docusaurus Slash Introduction

Click on OK.

Open Command Prompt Run as Administrator and write command as shown in figure below, you will see sqlite version.

sqlite3 

Docusaurus Slash Introduction

Note:

From exit out of database wrote command .quit and you will come out from SQLite Shell.

.quit

Docusaurus Slash Introduction

To create a database because SQLite database is created locally in the File System so for that you just need to cd to the directory in which you want to create your SQLite Database.

I am creating an SQLite database on my Desktop so for that I am creating a new folder and giving the name DB.

Note:

Once you create the DB folder just open it and copy the path of the DB folder C:\Users\Desktop\DB and in command prompt write cd path for example cd C:\Users\Reema\Desktop\DBand hit enter.

cd C:\Users\Reema\Desktop\DB

Docusaurus Slash Introduction

To create a database write command below ex. Sqlite3 Datbasename.db hit enter.

sqlite3 Datbasename.db

Docusaurus Slash Introduction

To view the database which you have created write command below:

.databases 

Docusaurus Slash Introduction

For creating a Table inside mydb write a command below:

 create table Students (id INT primary key); 

Docusaurus Slash Introduction

To know more about SQLite Database operation click here.

Connecting SQLite Datasource with OPNBI

Go to the Master data section and click on Data Source, as shown in the figure below:

Docusaurus Slash Introduction

Click on Create button from the bottom horizontal menu bar from Datasource section, as shown in the figure below:

Docusaurus Slash Introduction

The Create Datasource window will open, as shown in the figure below:

Docusaurus Slash Introduction

Search for SQLite data source from Top-right search bar in Datasource section, as shown in the figure below:

Docusaurus Slash Introduction

Click on SQLite to create a data source using SQLite.

Docusaurus Slash Introduction

As you click on SQLite the create dialog box will pop-up on screen, as shown in the figure below:

Docusaurus Slash Introduction

Wait Time: Wait Time Analysis for Database Optimization. Wait time analysis helps you understand how much time each SQL statement is spending across all its executions in a given period of time.

Is JNDI: Java Naming and Directory Interface is the name of the interface in the Java programming language. It is an API( Application Program Interface) that works with servers and can fetch files from a database using naming conventions.

Class: Database class Contains methods for creating and manipulating data.

Connection URL: A database connection URL is a string that your DBMS JDBC driver uses to connect to a database.It can contain information such as where to search for the database, the name of the database to connect to, and configuration properties.

Username: Username is the name of the user that is connected to the database.

Password: You can set a password on an Access database to prevent unauthorized access to the database file.

Extra Configuration: Using Extra Configuration you can add extra fields as per ypur requirements.

Insert the following data in the Datasource connection Dialog box:

Note:

Enter Display Name: SQLite [ Add name as per your requirements]

Enter Class: org.sqlite.JDBC

Enter URL: jdbc:sqlite:database_file_path [Add Connection URL as per your connection]

(URL: jdbc:sqlite:C:\Users\Desktop\DB\database.db) Datasource

Enter Username: [Provide the username of your SQLite Datasource]

Enter Password: [Enter your respective SQLite password]

Click on Test Connection Button to Check Database Connection, as shown in figure below:

Docusaurus Slash Introduction

After Connection Successful, Click on ok and save button to close the Dialog box.

Docusaurus Slash Introduction

To create a dataset Select the SQLite DataSource and click on Create Dataset, as shown in the figure below:

Docusaurus Slash Introduction

When you click on create Dataset the following screen will be display.

Docusaurus Slash Introduction

Enter the Following details in Dataset.

Name: SQLite (Add name as per your requirements)

Source: SQLite (select the data source which you have created)

DataSource: JDBC

Docusaurus Slash Introduction

write a query to fetch data from required table.

Docusaurus Slash Introduction

Click on Preview.

Docusaurus Slash Introduction

Click on Save and Exit.