HTML Web SQL Update

Posted in HTML Tutorial

Updated on Sep 03, 2024

By Mari Selvan

👁️ 28 - Views

⏳ 4 mins

💬 1 Comment

Photo Credit to CodeToFun

🙋 Introduction

Web SQL is a web API that allows you to manage a client-side database using SQL queries. Although Web SQL is deprecated and not recommended for new projects, it is still supported by some browsers.

This guide focuses specifically on updating records in a Web SQL database, providing a clear and concise approach to modifying existing data.

❓ What Is Web SQL?

Web SQL Database is a web API for storing data in the client-side database using SQL. Although it is deprecated and not recommended for new projects, it allows developers to run SQL queries against a local database, making it possible to manage data within the browser.

🤔 Why Update Records in Web SQL?

Updating records in Web SQL is essential when you need to modify existing data based on user actions or new information. Whether it's updating user profiles, settings, or other data-driven elements, the UPDATE SQL statement is used to make these changes efficiently.

🗄️ Creating the Database and Table Structure

Before you can update records, you need to have a database and table structure in place. Here’s how you can create a simple database and table:

javascript

Copied

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

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

🔄 Executing Update Queries

The UPDATE statement is used to modify existing records in a table. You can specify which records to update by using the WHERE clause to target specific rows.

javascript

Copied

function updateRecord(id, newName, newAge) {
  db.transaction(function(tx) {
    tx.executeSql('UPDATE users SET name = ?, age = ? WHERE id = ?', [newName, newAge, id], 
      function(tx, results) {
        console.log('Record updated successfully.');
      }, 
      function(tx, error) {
        console.error('Update error:', error.message);
      });
  });
}

In this example, the updateRecord function updates the name and age fields for a user with a specific id.

🤝 Handling Update Errors

Errors can occur during the update process, such as when the record to be updated doesn't exist or if there is a SQL syntax error. Handling these errors is crucial to ensure the application behaves correctly.

javascript

Copied

tx.executeSql('UPDATE users SET name = ?, age = ? WHERE id = ?', [newName, newAge, id],
  function(tx, results) {
    if (results.rowsAffected === 0) {
      console.warn('No records were updated.');
    } else {
      console.log('Record updated successfully.');
    }
  },
  function(tx, error) {
    console.error('Update error:', error.message);
  });

This code checks if any rows were affected by the update, which helps in identifying cases where the update did not go through as expected.

🏆 Best Practices

Use Parameterized Queries: Always use placeholders (?) in your SQL queries to prevent SQL injection attacks.
Check for Affected Rows: Ensure that your update operation has actually modified the data by checking the number of rows affected.
Error Handling: Implement robust error handling to manage situations where the update fails, ensuring a smooth user experience.

📝 Example

Here’s a complete example that demonstrates creating a database, adding a record, and then updating that record:

HTML

Copied

<!DOCTYPE html>
<html>
<head>
  <title>Web SQL Update Example</title>
</head>
<body>
  <h1>Web SQL Update Example</h1>

  <button id="addRecord">Add Record</button>
  <button id="updateRecord">Update Record</button>

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

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

    document.getElementById('addRecord').addEventListener('click', () => {
      db.transaction(function(tx) {
        tx.executeSql('INSERT INTO users (id, name, age) VALUES (?, ?, ?)', [1, 'John Doe', 25]);
      });
    });

    document.getElementById('updateRecord').addEventListener('click', () => {
      updateRecord(1, 'Jane Doe', 26);
    });

    function updateRecord(id, newName, newAge) {
      db.transaction(function(tx) {
        tx.executeSql('UPDATE users SET name = ?, age = ? WHERE id = ?', [newName, newAge, id],
          function(tx, results) {
            console.log('Record updated successfully.');
          },
          function(tx, error) {
            console.error('Update error:', error.message);
          });
      });
    }
  </script>
</body>
</html>

🎉 Conclusion

Updating records in Web SQL is a straightforward process that involves executing an UPDATE SQL query with appropriate parameters.

Despite Web SQL being deprecated, understanding how to perform updates can still be useful in maintaining legacy applications. Always follow best practices like using parameterized queries and handling errors effectively to ensure data integrity and security.