Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,974 courses, including more Developer and personalized recommendations.Start Your Free Trial Now
- View Offline
- Using the Communications API
- Understanding geolocation
- Getting and watching the current position
- Using web workers and WebSockets
- Implementing mobile web user interfaces
- Managing data in a mobile web application
- Working offline
- Using Web SQL
- Using drag-and-drop
Skill Level Intermediate
In this lesson we're going to see how to use web SQL. This is an HTM5 feature which allows you to store relations databases on your browser machine rather than (INAUDIBLE) store data back up and forth from the server every time. Now web SQL is laid on top of local storage. And every origin has it's own set of related databases. So for example as a user if you visit my domain1.com and my domain2.dot com. Each of those domains has it's own set of databases that you can access client side in your application. Now in this example, I have a simple datatbase, it has a single table called Courses.
And I can Add, Delete and View the items in that table. So first of all, I'll enter a name for a new Course, such as French. Let's say a French course might last 5 days. I click the Add Course button and it tells me one low is been inserted and also tells me the ID of that neuron. I can view that course like so it gives me ID, the name and the duration. I can add another course here. Let's say you wanted to learn Welsh. Welsh is quite a hard language.
So let's say that will take 10 days. I click Add. So that one has an idea of two. And if I click Select Courses, we now have two courses in our portfolio. Imagine that learning Welsh is actually something that you don't want to do. I'll delete that course. And then if I click Select Courses again, we're back to the one. So that's a fairly simple example. Let's have a look at the code that underpins it. So the code is in file called websql.html. You have to run this through a web server.
On my machine, I've set up IIS from Microsoft, the Microsoft web server. And I'm running this example through that web server. So the code file on your machine is like this, I will go the bottom the page first of all just to show you what the mark-up looks like. We have a text box were you can enter a description for the course so French or both. And we've another text box were you can specify the duration 5 days or 10 days. And then three buttons where we can add, delete, and select the courses, and then a message area. That's the area that's displayed in blue on the web page, it's the message area there.
We also have another area where we display the list of courses. Right, when the page is loaded, this is the code that gets executed. So I set up some global variables just to identify the various text boxes and (UNKNOWN) that we've just seen. There's a property called Open Database. Now, if that property exists on your window object, then your browser does support web SQL. Obviously, if that property doesn't exist, then your browser doesn't support web SQL.
So let's assume that our browser does support web SQL. We display a message there, that indicates that is the case. And then we open the database. Now the open database function returns a database handle. I stole that in a global variable called db. And we'll use that later on obviously. The open database function takes four parameters. The first parameter is the name of the database. So, you can call that anything you want and then the version number. You can only have one copy of the database at the same time.
It's not as if you can have three or four different versions sitting side by side. That's just that (UNKNOWN) management. Really and then have a description of the database against that's free format text. And then here as an estimate of how big you think database is going to be. This is going to be a very small database in this example. So I've created the database and then in the database I create a table first time round and a transaction. This is a common pattern. You take your database object, and you call the transaction function. The transaction function can take a function like so. This is the callback function, if you like, to the transaction to say, this is what I want to do inside the transaction.
So the callback function receives the transaction parameter. And then the transaction parameter you can execute SQL inside that transaction. So as you can see here I'm going to create a new table and the table is going to be called courses. And it'll have three columns. It'll have an ID, which is the primer key, one, two, three for example. The name of the the course which is French or Welsh. And then the duration which is an integer.
So the execute SQL function, takes the string that you want to execute. And then an array of parameters that you might want to pass into the SQL statement, if you have any placeholders for example. Now, I don't have any placeholders here, so I've just specified an empty array. So this, will create the table courses if it doesn't already exist. And then we call a select courses function. I'll have a look at that later in this demo which displays the courses first and through.
Maybe take you to the top of the code to look at the script to insert. And delete the courses. So the script starts on line 23, but we're going to look at line 33 through to 49 first of all. This is the function that gets called when you click the Add Course function. So basically inside a transaction, I provide two call backs this time. A call back to actually perform the work for the transaction, and that's this first function here. And then the second call back that displays any errors. So, the first call back function will get the name of the course invented such as French and the duration, so that's five days.
And then it will execute an SQL statement, in SQL statement will insert into the courses table specify the name and the duration. These values this is very familiar as close syntax. So these are place holders and you provide the place holders in the 2nd parameter here. In case of this is the second parameter to the execute to the execute SQL function. The first parameter is the SQL itself and the second parameter is an arbitrary array specifying the values. So this value will fill in the first placeholder and this value will specify the second placeholder.
The final parameter to the execute SQL is a callback funciton that'll be executed after the SQL is finished. This callback funtion I'll show you in a moment. First of all though, there's a possibility that things might have gone wrong. So inside here, this is the error call back for the transaction. And it displays any error message on the screen. Now if this error call back has occurred, the transaction will automatically be rolled back. So for example, here in my code which inserts a new item, if I threw an exception.
Like so. Then that would cause the callback function here to fail. And failure would be handled by this exception handler effectively, and that would automatically rollback the transaction. Now, I don't really want to do that, so let me just remove those two statements there. So, we have inserted a new row into the courses table. And then, assuming everything's gone well.
This function will be called afterwards. So that's the function we have down here. And this function receives a transaction object. This is the transaction that's currently running. And results settled correct and what's of interest to us here is that the results settled object has a rows affected property that will tell me how many rows have been affected by the insert. It also has an insert ID property, to tell me the ID of the new item that's been inserted. And often you need this. When you insert the new item, you want to get the idea of the new item, so that you can display it to the user potentially.
So, let's have a code which deletes an item from the table. Same sort of idea really. We'll start off on line 57. I will go through to line 77. We start the transaction, and that's the transaction function there. We give it two functions. First function is the callback, which will execute the code. So we get the name of the course that the users entered, such as Welsh, and then this time, we execute an sql statement to delete from the courses table.
Items based on that name. So the name here will be the name of the course, such as Welsh. And it'll display the results of that delete statement here. And that's the function we have here, and that'll tell me how many rows have been deleted by the delete statement. Right the last thing to look at is how we actually select all the items. So let me show you that here. That's on line 80. This is the function that gets called when I click the Select Courses button here. So, it starts a transaction, and inside the transaction, it just specifies a single function. There's no error callback here because we need, there's not much that can go wrong, to be honest.
So, here we exclude the next card statement to select all the rows. From the courses table, there were no place orders in the select statement, try passing an empty square brackets there. This is the call back function which will retrieve the result set from the select statement and that's this function here. So this function receives the current transaction and the result set that contains the result of the select. It has a rows property, which is effectively the results of the select statement, that will tell me how many rows were selected in the SQL select statement.
And then iterate through those rows and each row represents one of the rows in the database. And this is how you can access the data for the current row. So for the current row that would give me the ID column. And then for the current row this would give me the name column. And for the current row this would give me the duration column. And remember this is what it looks like when you display the information on the screen. So that's the end of the lesson. We've seen how you can open the database using the open database function. Goes here.
We've seen how using the database object, you can start transactions. And within a transaction, we can execute SQL statements and look at the results that it gives you back.