Making an SQL query from a C++ application (Visual Studio tutorial)

hello and welcome back to my youtube channel in this video tutorial I will show how to code a C++ algorithm in Visual Studio in order to retrieve data using through an SQL query I will show how to interface a C++ algorithm to a microsoft SQL server in order to get data so if you don't have an SQL server please watch my previous tutorial in which I show how to set up a Microsoft SQL Server and develop a database enjoy okay so for this tutorial you need first of all Visual Studio Microsoft program that can be used to write and compile C++ I go items it can be downloaded for free from the Microsoft website www.viki audio calm and it is free and it must be installed on your machine to follow this tutorial another things another thing that you need is SQL Server - which perform an SQL query in my case I showed in my previous tutorial how to setup an SQL Server and so I created a database called my DB which contains a table called people which in turn contains two records two names and two ages it it is required net this SQL server can be assessed remotely using both window out indication mode and simply an SQL Server authentication so if you don't have a server to which you can send a query just follow my previous tutorial and set up an SQL Server database okay so let's see how we can retrieve data from that database using C++ let's create a query for they're called C++ and SQL okay now go to your visual studio and create a new project place so choose the visual C++ project console application and place your project in the folder you just created disciplic let's call this application sq example okay choose console application precompiled header finish the wizard and now here is your project which is empty first of all you need to include several libraries for example your stream and then windows windows then you need to include SQL extension then you need to include SQL types and finally include simply SQL alright so since we need to print out data on the console in this C++ algorithm also use namespace STD so using namespace STD all right so if you don't know what this line does just you can look it up on the internet and find other tutorials that can be can make you more familiar with the C++ programming language okay so before editing the main function which is the function that is called when the algorithm starts I want to create a function for that displays possible errors that can occur when you use SQL functions in c++ so this function will have no output so just void and let's call this function show SQL error this function will have two inputs the first is an unsigned in care of and call it and type the second output is a constant of type SQL handle and let's call it and okay first of all I want to declare two variables that will be used in this function SQL char SQL state so this is a variable of type SQL char and is an array of characters it is called SQL state and it has a size of 100 1,024 the second variable that I want to declare is also of SQL chart type but it is called call the message okay now I want to call a function which is called SQL get the AG Rick okay what does this function do if we look it up if we google it we saw that this function basically performs a sort of diagnosis on the record that are the input of the function and put they a message and a state in in the variables SQL state and the message which in my functions are my phone in my function our SQL state and message so I want to call this function to perform a diagnosis on handle time and handle to see what if there is any error and if there is any message resulting from SQL function which affected handle type and so the input of this function are indeed the handle type handle and there are other parameters that I set to standard value so one SQL state no message the size all right so this function perform a diagnosis again on handle type handle and that put the results of the diagnosis in SQL State and message which afterwards will contain the state of the SQL handle and a message for a possible message for us explaining possible errors that occurred okay so here you see that there is an error because we used the variable of the available of type SQL char which is incompatible with other parameters in this function which are of SQL W char so in order to solve this issue just go on to your solution Explorer right click on your project properties and change the chapter set that to multibyte oh all right all right so we see make sure that you did it for the right platform and configuration so we set this for platform of 64 architecture which is that I wanted to use let's turn it to let's turn on your project to 64-bit architecture and also to release mode okay deep release and make sure again that your multibyte doctor set is the one you use in your project all right okay so now you can just print the SQL state and message on the console for the user but we want to do it only when SQL get dag rec the diagnosis function is successful so let's put this in a if statement so if this function is equal to SQL then we can we can print the result so in this case I will use the standard method C code to print out message and SQL state so SQL message message okay so here we will print the string SQL driver message and then we will print the message then in a new line we will print SQL State and print the actual SQL state then we will print on the console a dot full stop and then go to a new line okay so this function to work up this function will be used to show to perform a diagnosis on the variables handle type on handle and print an error message or any message that were information that is contained in these two handles okay now that we build we built this function we can actually type our main code which will be algorithm that retrieves data from an SQL Server okay so let's create the variable for the environment handle SQL and SQL in vironment and initialize it to know SQL and and then we need to create a variable that contains the connection handle and finally a variable that contains this statement handle all three are initialized to null then we need another variable of type SQL return which will be called the read code and another variable know we are set yes another variable type char SQL query this variable is an array of characters it's a string in which in which I want to put my SQL query my SQL query is select all the records from table people all right okay so now let's make a do cycle which will run until only once and we need a do cycle in order to break the cycle if an error occurs so the first thing we need to do is allocate the environment and this is them using the function SQL a lock handle as an input it has SQL on the environment and SQL will handle and finally appoint her to the our to our environment handle alright this is enough but we want to see if these function results in an error so let's put this again in a knife statement so if the result of this function is not success then we need to break so in this if statement this function is executed and it hallo creates the environment and put the environment handle in SQL and handle the variable I declared at the beginning on the function of the function and if the result of this function is an error so it is different than from success then we break the cycle and we don't execute any other command the second thing we need to do is to set the attributes that govern the aspects of the environment and this is done using SQL set environment this function this function has SQL environment handle as an input and then SQL attribute ODBC version okay then it requires an SQL pointer SQL the ODBC 2 3 okay so this is a function with a standard input which sets the attributes that govern the environment again I want to break the cycle if the result the results is an error so I put this function in the night I've if statement and wake if the result is not success the next thing that I need to do is allocate the connection and this is done with SQL look alakay I located the handle this is again and performed with a locate handle alright and as an input we have SQL environment handle not sorry in this case is SQL handle handle DBC the second input is SQL environment handle and finally a pointer to SQL connection connection handle again let's break the cycle if the result is not a success all right okay next we need to set the attributes of the connection and this is done using SQL set connection attributes the first input of this function is SQL connect our xq s ql connection handle the second is SQL login timeout SQL pointer five-0 all right so with this function I set the timeout for the connection again I put this in an if statement which can break the cycle if some error occurs okay then next step is to create a variable for the connection string which is SQL char let's call it R it on rate calm string it's an array of cocktails and so it is a string and I actually want to connect so in this case and in the function SQL driver connect the first input is the SQL connection handle the second is null then we need an SQL char with a declaration of a statement containing the driver of the SQL Server which I will fill afterwards SQL and TS wait home string it has this function it has a lot of standard inputs driver prompt okay so this function actually connects us to the database to actuate to the SQL Server and it has a lot of inputs this is in order to avoid a prompt for the user and here in this input we need to specify the type the port and the login for the database I want to connect to so in my case the driver is SQL Server SQL Server all right the server here is here you need to put the IP address of your server in my case the server is on my on my computer's I will just put local host next we need to specify the port then it comes to the data phase name which is as I said my DB the user ID is my ID the password is my password and this is a string that can be interpreted from SQL driver Connect so here again is the server type my case is SQL Server this is the IP address or localhost the database is on your computer this is the port the database name the database user ID and the password as I said and this is my Microsoft SQL Server as you can see my database is called my DB and the login is for my ID and has a password that you can set here my password okay so again we want to check what is the output of this function so in this case we will use a switch alright so the output can be of several cases alright so the first case is SQL success and in this case I want simply to break the switch like this which case in case the output is SQL such success with info I again break the switch case then we can have that the database contains no data so the result will be know that now that I found in this case we will show an error and we will do it by calling the function that I created a defini beginning of this tutorial so I will want to show the error with Spira askew and DBC connection and this so if there is no data in the database my function will show an error our own code in this case will be set to minus 1 and then the switch cycle will be break another possible case if is if they handle is invalid so let's queue valid and this and we want to do the same as if the database contains no data the same if a generic error is thrown you all right in all the other cases which we will simply break but we shouldn't find other cases actually okay so at this point this break commands are just break this switch but if an error occurs we want also to break the dual cycle and fortunately we put red code here if an error occurs and so after this the switch statement we need to break again if red code is minus one okay at this point we need again to allocate the statement and the in order to allocate this statement we needed to use the query SQL look it handle this case we need to allocate the honda statement statement so this function allocates this statement using the variable x SQL connection handle and it hal locates this statement in SQL statements hello again we want to check if an error occurs and so we need to put this my phone is in an if statement your success so if the output is not success then they do cycle will be break and then we the program will end the next thing we need to do is to execute the statement so we need to use SQL exec director put this statement and here we we will execute the query okay again I want to put this in an if statement to check if an error of course okay if an error of course then we need that to display the error so show SQL error so there and then break otherwise so as if the output of their function is a success then we just need to print out the data that we find with this SQL query which was define it and and the beginning of the function okay so if SQL x''k direct is success then I declare a variable char name of size 256 and the nuclear age which are the two variables that I expect to find with my SQL query in the table people so name and age the name of the true variables don't necessarily need to be the name that are in the database but is just for simplicity okay so now we need to create a while loop and the in the while loop we need to fetch the result of this statement and in order to fetch our the next row set from the data that result from the query I needed to use the common SQL fetch and provide SQL statement handle as an input and of course we will point out the data if and the result of this command is a success okay so now that we have the data Fetchit we need to retrieve the data using the command SQL get data I need to use the SQL statement handle and in SQL get data which retrieves data for a single column in the result set we first need to use the statement handle then the second role is the row that I want to get the data from then is the type of the data which I will put I will in this case I will use SQL see default so that the SQL data type will be the default one for the given variable the result will be put in name for the first row then I need the size of the variable so a size of name the last input is okay so to sum up SQL get data retrieves from column column one the name a name and it puts the result in the variable name of size of sides of name okay we expect two fields because we have two rows and the second row is actually age but otherwise days index is the same here you see we have two rows rows name and age okay now basically we were got the data from the database we just need to print the data out and we do it using C code name space age so in this with this line we just print out the data okay basically we have done the only thing that we need to do is to actually three the handles and free all the resources that we use there and disconnect from the database so we need to use SQL three handle and the first just freed a handle statement statement okay then you need to disconnect the connection when they free the finally you need to freedom by Roman sandal okay now if we run the program right now we don't see any data because the console closes immediately after performing all the I go with so in order to freeze the data freeze the console before it closes I will use get char okay so let's see if everything works by hitting just running a first build they I go with my okay it's success successful and now let's run the program okay and so as you can see we correctly retrieve the true they the true fields from the SQL Server so I use the an SQL Server database containing two names two ages and with the C++ algorithm I correctly got the data in the database okay so this of course all these commands all these functions can be embedded in more complex more complicated programs in order to automatically retrieve data from SQL databases that can be on the same machine and same computer on which the algorithm is performed or it can be also on another computer in the same network just if the database is not on your computer just make sure that the firewall setup of your computer and the server is ok and just put here the IP address of the computer in which the SQL server is located thank you for watching my tutorial and see you next time

Channel: Carlo Cappello

Views: 50,665

Rating: 4.841897 out of 5

Keywords: SQL, C++, Windows, query, application

Id: 1g_Xng_uH2w

Length: 47min 38sec (2858 seconds)

Published: Thu Nov 09 2017