SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL What are single row and multiple row subqueries?

WEB SQL
2022-07-10 01:45:22

WEB SQL

What is Web SQL?

In SQL we can create databases, read the data in the databases, insert the records into the databases, and delete the records from the databases. For storing the data and managing it, a web page API is used. This API (Application Programming Interface) is known as Web SQL.

Web SQL is an Application Programming Interface which helps developers in tackling some database operations from the client side.

Web SQL Database API does not come under HTML5 specification. Web SQL has a different specification.

Latest versions of Google Chrome, Opera, Safari, Android browsers accept the web SQL database.

This database is a deprecated web browser API specification for storing data in databases that can be queried using SQL variant.

Methods in Web SQL:

We have three main methods in Web SQL to access the data from the API.

openDatabase:

This function creates a new database. It  can also create the database object using the already existing database.

When we create a database, it can have four parameters:

  • Database name
  • Version number
  • Description
  • Size
  • Creation Callback

Transaction objects are obtained as result objects for callback. The result object has rows object, length. For obtaining individual rows, results.rows.item (i) should be used.

When we invoke openDatabase Method, it opens the specified database. If in case the database mentioned is absent, then this method creates a new database with the given database name.

NOTE: When the database is created, the creation callback is called internally.

Syntax:

var jtpdb = openDatabase (name of the database, version number, description,      size);

Example:

Now let’s create a database by running the below query.

var jtpdb = openDatabase (‘mydatabase’, ‘2.0’, ‘This is a company info database.’, 2*512*512);
if (! jtpdb)
{
	alert (‘Database is not created!”);
}
else
{
	var version = jtpdb. version;
}

So, this creates a database. If database is not created, then it gives an alert message.

executeSql:

This method is used to execute the SQL query. We use database.transaction () to execute a query. This function takes in, a single argument.

Example:

var db = openDatabase (‘mydatabase’, ‘2.0’, ‘Test_DB’, 2*512*512);
db.transaction (function (txc) {
	txc. executeSql (‘CRERATE TABLE IF NOT EXISTS LOBBY (ID unique, log)’);
});

This query creates a table LOBBY in ‘mydatabase’ database.

Transaction:

Performing some kind of operations on a database, is known as a transaction. Controlling the transactions is done, using rollback and commit functions.

Creating transactions:

From our database instance we can use transaction function to create transactions.

Syntax:

Mydb transaction (function (tx) {});

Mydb is an existing database and tx is a transaction that is used for upcoming operations.

Transaction will be rollbacked if any operation throws an error. These can be managed using transactions.

Steps of transaction:

We have a few steps for executing a transaction. These steps are assured to run asynchronously.

 These steps are initiated with a transaction callback, optionally a success callback, optionally an error callback, optionally a post flight operation, optionally a preflight operation along with a write/read mode or read only mode.

  1. We should open a new SQL transaction to the database. Then create a transaction object, specify the object with a mode. If the mode is read/write, the transaction should have an exclusive write lock for the entire database. If the mode selected is read-only mode, then the transaction should have a shared lock for the entire database.
  2. The user agent must wait for the appropriate lock to be available.
  3. If any error occurs, the jump over to the last step.
  4. For this instance of the transaction steps, if a prefight operation is defined then run that. If that fails, then we need to jump to the last step.
  5. If the callback raised an exception, jump to the last step.
  6. Perform the following steps for each queued up statement in the transaction with the oldest first, while there are any statements queued up in the transaction.
    • Execute the statement, in the context of the transaction.
    • Create a SQL Result Set, which represents the result of the statement.
    • Jump to the last step, if the callback has invoked an raised the exception.
    • Move to the next step, if no other issues raised.
  7. If success callback is not null, queue a task to invoke success callback.

The database task source is the task source for the above tasks.

Insert Operation:

For creating entries, we query the following statements.

var db = openDatabase (‘mydatabase’, ‘2.0’, ‘Test_DB’, 2*512*512);

var db = openDatabase (‘mydatabase’, ‘2.0’, ‘Test_DB’, 2*512*512);
jtpdb.transaction (function (tx) {
	tx.executeSq l(‘ CREATE TABLE IF NOT EXISTS CLASS (id unique, class)’);
	tx.executeSql (‘ INSERT INTO CLASS (id, class) VALUES(1, “hey”)’);
	tx.executeSql (‘ INSERT INTO CLASS (id, class) VALUES(2, “HELLO”)’);
var id = “1”
var String text = “heyy”
tx.executeSql (‘INSERT INTO CLASS (id, text) VALUES (?, ?)’, [id, text]);

So, in the last step, with the help of ‘?’ symbol we are retrieving the id and text values.

Read Operation:

By using insertion operation, we insert records into the database. So, after storing, if we want to retrive the records, we will use the read operation using callback function.

var db = openDatabase (‘mydatabase’, ‘2.0’, ‘Test_DB’, 2*512*512);
db.transaction (function (tx) {
	tx.executeSql (‘CREATE TABLE IF NOT EXISTS CLASS (id unique, class)’);
	tx.executeSq l(‘INSERT INTO CLASS (id, class) VALUES (1, “hey”)’);
  	   tx.executeSql (‘ INSERT INTO CLASS (id, class) VALUES(2, “HELLO”)’);
db.transaction ( function (tx) {
	tx.executeSql (‘SELECT * FROM CLASS’, [], function (tx, results) {
		var length = results.rows.length, i;
		message = “<p>Found rows: “ + length + “</p>”;
		document.querySelector (‘#status’). innerHTML += message;
		
		for (i = 0; I < length; i++)
		{
			alert (results.rows.item(i).class );
		}
	}, null ) ;
} ) ;

In the above query, we inserted the records with id’s 1 and 2 having text hey and hello respectively.

Next using the SQL keyword select, we are getting all the records from the CLASS database. Along with the records we are printing the length/ the number of records present in the CLASS, using rows.length function.