Front-end Tutorials

Front-end Tutorials

HTMLCSSSassJavaScriptReactJS
CMS Tutorials

CMS Tutorials

WordPress
Tutorials expand

HTML Web SQL Update

Posted in HTML Tutorial
Updated on Sep 03, 2024
By Mari Selvan
πŸ‘οΈ 37 - Views
⏳ 4 mins
πŸ’¬ 1 Comment
HTML Web SQL Update

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
Copy To Clipboard
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
Copy To Clipboard
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
Copy To Clipboard
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
Copy To Clipboard
<!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.

πŸ‘¨β€πŸ’» Join our Community:

To get interesting news and instant updates on Front-End, Back-End, CMS and other Frameworks. Please Join the Telegram Channel:

Author

author
πŸ‘‹ Hey, I'm Mari Selvan

For over eight years, I worked as a full-stack web developer. Now, I have chosen my profession as a full-time blogger at codetofun.com.

Buy me a coffee to make codetofun.com free for everyone.

Buy me a Coffee

Share Your Findings to All

Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
We make use of cookies to improve our user experience. By using this website, you agree with our Cookies Policy
AgreeCookie Policy