Tutorials

HTML Web SQL Retrieve

Posted in HTML Tutorial

Updated on Sep 03, 2024

By Mari Selvan

👁️ 37 - Views

⏳ 4 mins

💬 1 Comment

Photo Credit to CodeToFun

🙋 Introduction

Web SQL is a web storage API that provides a way to store data in a structured, relational database using SQL queries. Although it is not a part of modern web standards and is no longer recommended for new projects, it is still supported by some browsers for legacy applications.

Retrieving data from a Web SQL database involves executing SQL SELECT queries to fetch the desired records.

❓ What Is Web SQL?

Web SQL Database is a deprecated API for storing data in a client-side database. It uses SQLite as the database engine and allows developers to execute SQL commands to create, read, update, and delete data. Although its usage has declined due to deprecation in favor of IndexedDB, understanding how to retrieve data from a Web SQL database can still be valuable for maintaining older projects.

🧠 Understanding Data Retrieval

Data retrieval in Web SQL involves executing SELECT statements to query the database. The results are returned as a ResultSet object, which contains rows of data that match the query criteria. The ResultSet can then be processed to extract and display the data as needed.

🗄️ Setting Up the Database

Before retrieving data, you must have a database and table set up. Here's a quick refresher on creating a database and table (this section assumes the database and table have already been created):

javascript

Copied

const db = openDatabase('myDatabase', '1.0', 'Test Database', 2 * 1024 * 1024);

db.transaction(function(tx) {
  tx.executeSql('CREATE TABLE IF NOT EXISTS myTable (id unique, name, age)');
});

🔄 Executing SELECT Queries

To retrieve data, you'll use the SELECT SQL command. Here’s an example of how to execute a simple SELECT query:

javascript

Copied

db.transaction(function(tx) {
  tx.executeSql('SELECT * FROM myTable', [], function(tx, results) {
    const len = results.rows.length, i;
    for (i = 0; i < len; i++) {
      console.log(results.rows.item(i).name);
    }
  }, null);
});

This example retrieves all records from myTable and logs each record's name property to the console.

✔️ Processing Query Results

The results of a SELECT query are returned as a ResultSet object, which contains a rows array-like collection. Each row can be accessed using the item() method or by indexing directly into the rows object.

javascript

Copied

db.transaction(function(tx) {
  tx.executeSql('SELECT * FROM myTable WHERE age > ?', [30], function(tx, results) {
    for (let i = 0; i < results.rows.length; i++) {
      const row = results.rows.item(i);
      console.log('ID:', row.id, 'Name:', row.name, 'Age:', row.age);
    }
  });
});

In this example, only records where age is greater than 30 are retrieved and displayed.

🤝 Handling Errors

Error handling is crucial in Web SQL to manage issues such as incorrect SQL syntax or database access failures. The executeSql method accepts an optional error callback, which you can use to log or handle errors.

javascript

Copied

db.transaction(function(tx) {
  tx.executeSql('SELECT * FROM nonExistentTable', [], null, function(tx, error) {
    console.error('Error occurred:', error.message);
  });
});

This code attempts to retrieve data from a non-existent table, and the error is caught and logged.

🏆 Best Practices

Use Indexed Queries: Index your tables on columns that are frequently queried to speed up data retrieval.
Parameterize Queries: Always use parameterized queries to prevent SQL injection attacks.
Limit and Offset: When dealing with large datasets, use LIMIT and OFFSET to paginate results and improve performance.
Handle Null Results: Ensure that your code gracefully handles situations where no results are returned.

📝 Example

Here’s a complete example that demonstrates retrieving data from a Web SQL database and displaying it on a webpage:

HTML

Copied

<!DOCTYPE html>
<html>
<head>
  <title>Web SQL Retrieve Example</title>
</head>
<body>
  <h1>Data from myTable</h1>
  <ul id="dataList"></ul>

  <script>
    const db = openDatabase('myDatabase', '1.0', 'Test Database', 2 * 1024 * 1024);

    db.transaction(function(tx) {
      tx.executeSql('SELECT * FROM myTable', [], function(tx, results) {
        const list = document.getElementById('dataList');
        for (let i = 0; i < results.rows.length; i++) {
          const row = results.rows.item(i);
          const listItem = document.createElement('li');
          listItem.textContent = `ID: ${row.id}, Name: ${row.name}, Age: ${row.age}`;
          list.appendChild(listItem);
        }
      }, function(tx, error) {
        console.error('Error retrieving data:', error.message);
      });
    });
  </script>
</body>
</html>

🎉 Conclusion

Retrieving data in Web SQL is a straightforward process involving SQL SELECT queries and processing the resulting data in your application.

While Web SQL is deprecated, understanding how to efficiently retrieve and handle data can be essential for maintaining legacy systems. By following best practices, you can ensure that your data retrieval operations are both secure and efficient.