HTML Web SQL Insert

Posted in HTML Tutorial

Updated on Sep 03, 2024

By Mari Selvan

👁️ 12 - Views

⏳ 4 mins

💬 0

Photo Credit to CodeToFun

🙋 Introduction

Web SQL Database is a web standard that provides a way to store and manage structured data in web applications using a SQL database. While Web SQL is now deprecated in favor of IndexedDB, it remains relevant for understanding data storage concepts in web development.

This page focuses specifically on inserting data into a Web SQL database.

❓ What Is Web SQL?

Web SQL Database is a deprecated web standard that allows client-side storage of structured data. It uses SQL queries to interact with the database, making it familiar to developers with SQL experience. Although no longer recommended for new projects, understanding Web SQL helps grasp fundamental data storage concepts.

⚙️ Setting Up the Database

To use Web SQL, you first need to create a database and define the schema. This involves opening a database and executing SQL commands to create tables.

javascript

Copied

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

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

🗄️ Inserting Data into the Database

Inserting data into a Web SQL database involves using the executeSql method within a transaction. You specify an SQL INSERT statement to add data to the table.

javascript

Copied

function insertData(id, name) {
  db.transaction(function (tx) {
    tx.executeSql('INSERT INTO items (id, name) VALUES (?, ?)', [id, name], function (tx, result) {
      console.log('Data inserted successfully');
    }, function (tx, error) {
      console.error('Insert error:', error.message);
    });
  });
}

🤝 Handling Success and Errors

When performing database operations, it's important to handle success and error callbacks to manage the results and troubleshoot issues.

Success Callback: Executed when the SQL query is successful. You can use this to confirm that data was inserted correctly.
Error Callback: Triggered if an error occurs during the execution of the SQL statement. Handling errors helps identify and resolve issues.

javascript

Copied

db.transaction(function (tx) {
  tx.executeSql('INSERT INTO items (id, name) VALUES (?, ?)', [1, 'Sample Item'], function (tx, result) {
    console.log('Data inserted successfully');
  }, function (tx, error) {
    console.error('Insert error:', error.message);
  });
});

🏆 Best Practices

Validation: Always validate input data before inserting it into the database to avoid SQL injection attacks and ensure data integrity.
Error Handling: Implement comprehensive error handling to catch and address any issues that arise during database operations.
Transactions: Use transactions to ensure that database operations are atomic, consistent, isolated, and durable (ACID).

📝 Example

Here’s a complete example demonstrating how to set up a Web SQL database and insert data into it:

HTML

Copied

<!DOCTYPE html>
<html>
<head>
  <title>Web SQL Insert Example</title>
</head>
<body>
  <h1>Insert Data into Web SQL</h1>
  <button id="insert">Insert Data</button>

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

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

    document.getElementById('insert').addEventListener('click', () => {
      insertData(1, 'Sample Item');
    });

    function insertData(id, name) {
      db.transaction(function (tx) {
        tx.executeSql('INSERT INTO items (id, name) VALUES (?, ?)', [id, name], function (tx, result) {
          console.log('Data inserted successfully');
        }, function (tx, error) {
          console.error('Insert error:', error.message);
        });
      });
    }
  </script>
</body>
</html>

🎉 Conclusion

Inserting data into a Web SQL database involves creating a database, defining tables, and executing SQL INSERT statements within transactions. While Web SQL is deprecated, understanding how it works can provide valuable insights into client-side data storage and management.