Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Using Web SQL

From: HTML5 Power Workshop

Video: Using Web SQL

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.

Using Web SQL

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.

Show transcript

This video is part of

Image for HTML5 Power Workshop
HTML5 Power Workshop

22 video lessons · 4044 viewers

Andy Olsen
Author

 

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now "Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed HTML5 Power Workshop.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.