C++ with SQLite database demonstration

walking back coders and welcome to another edition of coding with Chopin so today I want to talk about sequel light often times in your programming whether it's for academics or for your own applications at your building you're gonna need a small database to work with and obviously Microsoft Access is not the solution my sequel is a very good solution I've used my sequel I've used a Microsoft sequel server all of those are really nice but when you just need a small database that you where you don't have to worry too much about security or you don't want to worry about configuration audits logging any of that extra stuff that that you often do need in a in an enterprise quality database then I recommend sequel Lite I used this to to do some of my research and I use it first of all my applications that I build and I find it to be just an amazing fast little database so I'm here on the sequel Lite org page and this is where you download your your your data I'm not going to read all of this but I am gonna just say a couple things about it and that's that it's self-contained server list zero configuration so those first three links up on top this is the this is the selling point right here just nice and easy just something you need to throw in and you can write sequin it so it'll work you can build triggers you can build views I mean you guys can go to the link and read all about it you can go here on the executive summary and read you know the database max database size whatever you need to know but my video is not 200 really to talk about sequel Lite but rather this video is to discuss how to install it with C++ I did another video where I where I connect it to Python and the Python process is really easy to do but the the C++ one is a little bit more challenging and just a few things that you need to know because if you're using sequel server 2019 like I am and you want the latest you want the latest code to make this thing work smoothly then that's what this video is for okay so this is the page where we talk about sequel light really what we need to go to is the download page and from the download page you're going to get a lot of information and it can be a little challenging to know where to go but this is the file you want right here the sequel light and I'm not even sure I'm pronounce that but this file right here and so we're gonna want to download this so I'm gonna go through the whole process and and show you what we need to do so from the download page go to this file click it I'm gonna go ahead and grab it and then I'll be done okay so I downloaded the file and what you're gonna get is you're gonna get this this file here and it's going to contain these these files here you'll see your source to sources and your headers now it really depends on how you configure this the method that I'm going to use we only need one file and that's this one right here the sequel Lite 3 dot C file and so we'll grab that we'll put into our code in a little bit but like I said the first thing you need to do is you need to get download download this file it'll come down as a zip just unzip it to wherever you want and we're gonna you're going to need this sequel Lite 3 dot C file and that's the only one that we're going to use in the way that we're going to set up our our setup ok so let me get some code up and then we'll we'll get some work going so what I'm going to do is I'm going to create a console app in C++ and we'll be using the sequel Lite as a back-end that's why it's a c-plus classical light demo I went ahead and just added just really one thing here I added the using namespace STD also the stdio.h and the iostream I guess I have two of those let me get rid of that as an accident ok so now we're good and what we need to do is we need to add that file the sequel I'd 3c file to our to our setup so we're going to do that right now so let's go into solution and where you want to add it is into the source files right here so go ahead and right click on source files and click Add existing item and we're gonna go into the right into here I'm actually going to go to the desktop and we're gonna go to the directory that we just downloaded and just file here we're going to grab these see white 3 dot C file this is the one you want the C source file let's go ahead and add that and here it is now okay and that's the only father we need from from the files that we downloaded now we do need to do something else we're going to do that right now we need to add a package from the new get package manager and we're going to want to install the sequel like three underscore senior scored + + by Darlington net and LM gellan so we're gonna do that right now so go up to this right here to your directory do a right click do manage you get you get packages we're gonna for browse it's the easiest way to get there and then we're going to just type in the [Music] what's equal eight three and you can see have done this before so we'll just grab it from here and what you're gonna want to do is come down to it came right up this is the one we want so this one here by darling and pina and you go ahead and download it and you can see what's happening here successfully loaded we can go to installed and here it is we have it installed so we're good to go and we can now click out of that and we're now ready so when I was trying to install sequel light to the C++ there was a lot of old code out there and old configurations we're just really wouldn't work and I'm sure there's probably 50 different ways to to make this connection but I found that the simplest way was the way that I just went through adding that package and then adding this sequel like three dots II thought of the source if you don't do this step right here the code won't work it just it become a be because people uh is this single server I'm sorry maybe because we just did a 2019 is a just not quite configured for it yet you have to have this file and it took me a while to figure that out but it does work so anyways now that we have that we can do a few other things and we're gonna want to ante include and we want that file right there and that's all you need so these two files this studio th and they see quite three ah th which it's the package that actually lets you grab this from the header but for it to run you need this source file right here also okay so now we're ready to start coding I'm going to code a little bit and then I'll explain okay so I have a bunch of code up and what I did was I created two new functions the static int create DV function taking a constant of which I'm going to call s and also study can't create table so I'm gonna create the database I'm going to create the table and another thing I did too was I created a directory in my C directory called deleteme and that's what I'm gonna put the database as you can see there's no database here yet we're gonna build that okay so we have the directory which is going to be C deleteme students so in this directory the students DB is going to be treated and I call my two functions that I that I have listed up here create DB I'm going to pass it the directory and create table and I'm going to return 0 since it's needed to me so in the create DB function we have a static int function and we are going to capture the directory so the directory that's passed we're going to be using sequel Lite 3db and we're going to we're going to use a the exit as an Ian return so right here this is going to say okay the command for super light three underscore open is going to take the the directory string so the delete many students DB and it's going to try and open that database and that's what this part right here means so if the database doesn't exist in sequel light what will happen is it will create it so obviously our database does not exist it goes there to this directory tries to open it up doesn't see it and then it creates the database okay so the next thing that we need to do after to create it is we need to create the table so I have the next function your create table I also pass it the directory string so the create table it's pretty straightforward and simple we're going to take a look right here at the DV and we're going to pass the the DB we're going to pass a sequel string so I create a sequel string here and say okay create table and I'm gonna use an if not exist you don't need this unless you're gonna be doing testing and recreating so I throw it in there just so I can run my code over and over again and it doesn't try to recreate the table once it's been created so you might want to look into a little bit of sequel if you haven't written any sequel but sequels a very simple language and literally you can you know learn it in 15 minutes the the little key things that you need to know the advanced stuff of course you just you need to get a book but it's this just basic sequel and pretty simple so a create table if not exists great so the name of the table is going to be grades and we're gonna have these column names these are call names also those attributes field names and the first film is gonna be called ID it's going to be a type a datatype integer it's gonna be the primary key and it's gonna auto increment I'd like to auto increment I want the system to go ahead and give me the numbers and I usually use those as object IDs for editing or updating later on okay the next one is gonna be name it's gonna be of data type text and it will be not being null which means it must have you and the other ones are roughly the same the same type address is going to be a char of 50 characters and grades it's gonna be a charm a single character one so that's really it there's a little bit of embedded sequel next we're going to try to we're going to to open up our database and this time it exists it'll just open it up and then what we're going to do is we're going to pass it the next command and this is a simply a callback command here and what's going to happen is we're going to use it here to say okay if this doesn't work then let's pass the error back and so we just have a little if-else statement for error handling okay so it'll open it up I'll explain the callback in a little bit later because I I still need to do some more work with that if exit not equals sick alight okay okay and then we're gonna Libby it'll create the table or it won't create the table and if it does create it'll say oh he's created successfully great and then we'll have this this catch here so that's really it it's a pretty simple straightforward thing we'll go ahead and run it here and test it okay so it came back it says table was created successfully we have we have the table and the next thing we're going to do is going to write some code to enter data into the table all right so the next thing I did is I created the static function insert data and again we're gonna make it a constant char and we're gonna use it for s and remember this is so that we can pass the directory string in the main yeah I guess it's right here in the main we're calling it right here so actually let me pull this down a little bit more okay so every other we have here we're declaring it and here we're calling it right here inside the main function we're passing at the directory right here that you can see from line 18 is being passed to the crib okay I did not do it I'm sorry what I did okay so we have our insert data and now we we have our function that we wrote down here and so it's static int insert data remember it's taken in the directory and white database our message error open up the database and now we're gonna do our insert again this is just a standard sequel statement that you can look up anywhere online or like I said it's just standard sequel really simple insert into the table named grades into these column names name L name age aderson grade what we created above in the create table these values Ellis first-name and lastname chiapas so each one of these correspond to the the field name so name corresponds to L is chopped up responds to lastname 35 corresponds to age and so forth and then we're going to do a second one and a third so we're doing three separate entries on line 83 to 85 then in lines 87 so on line 87 we are we're gonna pass at this equal white execute button function and so we're passing this information and then which is basically it's saying it's a DB were passed at the sequel string and our message error I will explain some of this in a little bit but right now I just want you to know this is the Secoya to execute and you must pass the DB the most important things here to know you must have the DB and you must have the sequel string which is this part right here okay so once that's happened it'll insert the data or and if it fails then we'll get the error message here okay so I ran it and our information is that the tables were created successfully the records were created successfully everything went well no problems no issues so the fourth function we'll be creating is I'm gonna go ahead and create the Select function next because we've created our database we've created the table we've been sorted data we now want to see that data being retrieved so I'm gonna go ahead write the Select statement and then I'll explain the code so there's a whole bunch of work that needed to be done so the selects data but I'll go ahead and explain it now the first thing we needed to do is we needed to add two functions for this to work and the first one is the reflux data which I wrote and when it it also like the other ones will have the s directory where the s I'm sorry the constant char for s which will be the directory string and then also the static int callback which is something that's sequel light requires and I'll explain what these values aren't here in a minute but we needed to add these two on top so that we can access them beneath so here are these these new functions are declared and then on the second part of the screen down here on the split screen you can see that they were created the functions in what they actually do so we'll start with the callback the callback function and retrieves the contents of the database and is used by the selected data and so I commented it here now these values here that we've been seen and some the other code they're required by sequel light to to pull the data back so I want to get enlisted from sickle lights from their websites information what they mean so the first part here this first argument is not used so we just avoid it the second one is a is called the Arg C and arc C is the first one I list it holds the number of results so the number of results that are returned from your query the next one our V holds each value in an array and then the AZ column name holds each column so your your column name your values the number of returned and then not used and those are what your your arguments mean and I listed the information right here okay so knowing that you have to call your cut you know inside your call back you have your for loop and it just iterates through the number returned and then outputs the column name and the value so that's what's going on here 19 through 21 a for-loop we have our account so RBC gives us the count and then we iterate through and return call name and the value and that's it in two different arrays pretty simple so the callback again is required in sequel Lite for with C all right so up here in the Select data now that we actually want to select our data we have our directory and we're going to again have to tell it it's going to be sequel Lite database and we're going to open up the database in line 105 then we have our select statement select everything so this is just a sequel statement that means I'm gonna select everything from the table of grades I want everything now you could put a where clause in here or an order by clause or or something if you want to be very selective may be selected a single record that's it this is this statement right here on line 107 that's where that would be done but I'm just going to pull everything that's in there now the next thing is to open the database and evaluate the sequel statements so here we have the sick light actually execute which we've we've used before and we use it in all our sequels on all of our commands to sequel light again it has the same values that we need we need to tell it that it's going to be using the database sequel light that it's going to pull these sick lights string it's going to evaluate this cigarette string we're going to use the callback function which which we needed right here and then these two these are just non values now this last these actually have meaning for example I listed them up here what they all mean so open database sequel to be evaluated callback function the first argument to callback and then the error message written here so the last null is the error message if we were using that I didn't use in the Select I'm going to use it in some of the other ones so you'll see that I think I used it up in here yeah I did so in the in the sequel open I have an error message and in in the insert function it should be one in there to insert function you know a venir message I use the error message in that one too okay so that's really it it's pretty simple I'll scroll through the code real quick from top to bottom create the database create the table insert the data you have to declare your your statements your functions I mean and then from the main I like to call everything from the main I give it to the the designation of the database remember if it doesn't find it it will create it and then I create the table then I insert the data and I commented out insert data because I it's not something you want to do twice because you don't want to have duplicates but we'll go ahead and we'll run it again so right now in my directory I don't have anything in here and if I run this it should work we're gonna go ahead and uncomment that okay and so the first thing I want to show you is that in the directory it created the table students in the in that database it created the table grades and then with our code we were able to pull out the information so it tells us Timur was created successfully records created successfully and then it retrieved them and this is the information that was it was inserted in the insert function so I'll just just set it there for a moment so here's their insight your insert function and from from lines 79 through 98 and then this is what it returns in the console okay so now we want to do some more things on the next thing I want to do is I want to do an update statement so to show you how we would edit information in one of our records and I'll write some code and then explain it okay so what I did was created the update statement and so just like I did it the other functions we have to declare it static int update data and again we're gonna pass it our directory string and that's that's done right here now the next thing is in the main I commented out the insert data because I don't want to have duplicate data I don't want to run the insert again this time we're going to do an update data so let's go ahead and take like the update function in the update function we have our usual declaring DB we are going to generate our message error and then we open up our database in line 108 and in line 110 we have our sequel string and so this time we're going to use the Seco code update table grades set the column grade equal to a where the last name is equal to Cooper so we're gonna modify 1 1 1 1 row it's important to remember that when you do updates or delete functions you must use this square Clause because if you don't it will upgrade it every record to an a and it's just you will mess up your database especially if you have like hundreds or thousands of entries it would be really challenging to undo that mistake so always try to use a condition here and let it know where you're gonna what where you're gonna do your update so in our case we're looking for Cooper and we're going to upgrade his grade from I think it's a C to an A and then you'll notice our usual sequel light 3 execute and it has the the typical arguments that we need to pass it this time we are passing it a message we will recognize the single string argument here so we pass it if it doesn't work we're gonna do a message error if it does we'll get records created successfully and return 0 good good to go okay so that's it in our main where we'll go back through creating the database which just means open because it's already traded we'll create the table but remember it already exists so in our sequel code it just won't create it it won't do recreate the table we did coming out the insert data so that way we don't have duplicate data and then this time we're going to run the update data this function you just created to upgrade this this exact row and then we're going to select our data so we can see what happens when we return all right so I ran it and this is what we get table Krita successfully records created successfully and we look down at Fred Cooper and here we have his eight so he we upgraded his grade from the C to an A and so our update statement would and that is almost it the only other thing I wanted to go over is the delete function okay so I created the delete function we have static int delete data again passing the same argument for directory and then in the main I went ahead and I didn't do it I want to put it right here [Music] okay so we have our delete data function we're going to call it we're going to pass it the directory this time we're not going to do any updates or any insert so we just want to delete the data and then see what's in the database oh and then the delete functions right here so static and delete we get our directory it's a sick like database we open it up we pass we create a single string delete from grades the table so delete everything from table grades that's what this means in sequel and we pass our usual arguments the sequel string the database sequel Lite the sequel string we have a callback function I'm not going to worry about any messages this time we're just going to return and so yet the tip wasn't really created remember it looked at it it already exists so didn't do anything and nothing was returned so that worked we can also insert I usually will run the Wow let's see I usually run the delete and the inserts together so that way but we'll doing data from the table and then insert and pop clean data each time so let's whoops and something went wrong there look at that okay so it's like do you book actually I'm calming her down upon the names so we actually don't want it this come about in there normally once you've created these II don't comment out from here you come a dealt from where you're calling them and I'll climb them inside the main so we want to leave that alone okay so this is all good now let's run that okay so you can see the object ID incremented four or five six we have the same information that's good okay so if I was to do this again the object ID should just continue to increment and it does so here's your object ID seven eight nine same information because it's just reinserting the data over well it's deleting the data and then inserting but the counter the auto increment is still keeping track and so it just keeps incrementing even though we only have the three entries in the database okay that is the the my tutorial on how to how to attach sequel light to to C++ I'll go ahead and get rid of the split screen and go over the code nice and slow and so here we have the are required includes on number eight single light three we have our functions declared on lines twelve to eighteen I'm calling everything from the main function I declare the delete like I do Claire the directory string on line 22 it's going to be a database sequel light on line 23 we pass it the directory string and all over functions in the main on lines 25 through 30 and then we have our individual functions the create function really simple if remember if it exists it just it just ignores it it just opens it so it either opens it or it creates the database create table just straight sequel on how to create the table that's it that would be a different topic we want to talk about how to write sequel I don't know why this little line cuz everything is it's not actually uh it's not actually a probably actually runs fine okay and then let's see the insert data function is here with no problems and the update function is here also with no issues and then lastly the delete function well not last and the Select data function so I'll scroll through it nice and slow again and that's it I hope you guys enjoyed that it can be challenging to do the connection but if you follow the little steps that I gave in the very beginning it works really well and it's pretty simple there are probably other ways to do it this was the simplest way that I found so if you have any comments or questions just let me know in the comments

Channel: Coding with Chapa

Views: 19,066

Rating: 4.866221 out of 5

Keywords: C++, SQLite, Functions, Database, Code, SQL Create, SQL Insert, SQL Delete, SQL Select

Id: L-hnA82JsEM

Length: 30min 26sec (1826 seconds)

Published: Thu Nov 21 2019