15. Run SQL Query on Logic App | SQL Connector on Logic app

hey everyone welcome back to the channel where we are discussing logic apps in detail in today's demonstration we will discuss how do we run this SQL operation on your logic app using the SQL connectors which is a built-in connector available in logic app hi my name is Rakesh suryavanshi and you are watching blr for this demonstration we have a SQL Server created in Azure portal and in that SQL Server we have a SQL database which is a pass instance of the database server in azure now that database would have the table stored procedure and all which we'll be creating for this particular demonstration and that database instance will be used from the logic app and this is the logic app which we have created it is a type of standard logic app which will be using to Define our workflows let's first of all set up this SQL Server database instance all the database structures stored procedure and all in our SQL DB instance for this I have a SQL script which I'll be using and this is one of the very simplest script for this particular demonstration just to give you an idea how the connectivity and setup works so here in the visual studio code I have the SQL script which I'll be using for this particular demonstration in this script I am creating a item table which has four columns item id which is auto generated LS as a unique unique geoided identifier then we have a SKU which is which is representing the unique code for the item and then description and the status which is of type integer so let's first of all use this particular script to define the item table in our SQL database for that I am going to first of all connect to this equal server using the username and password which I have on this particular database server instance so I'm on to the SQL Server I'll go to the query editor on the SQL Server portal instance I'll provide the login credentials for this particular SQL Server that has logged into my database instance at the moment I do not have any table or this store procedure as you can receive from this particular blade I'll run that create table query to create the items table onto this particular database the query got successfully executed now if I refresh again then I should have the item table available as you can see that we have the item table on our database instance which is a SQL demo database instance [Music] next I'll run this insert into command to insert a default record so for this demonstration I am going to insert at this particular Escape SQ code as an item and this is a item description which is of let's say some t-shirt code okay so simply I'll overwrite this particular query I'll run the query a query got executed with one row affected as we have inserted one record now if I run the SQL SQL select a statement on this particular table then I should have the one record retrieved and you can see that we have the unique identifier generated because our ID column was Auto generated and now this is the SQ which we have inserted with this particular status value so our database is from the table point of view and from record point of view is ready what I'll do is I'll also create a store procedure which says that get item by SQ ID so this is my store procedure and the code of the store for CR is create store procedure USB get items by SQ ID SQ is going to be a parameter which will which it will accept and then it will return star from the item table where SQ ID is the given SQ ID let me execute this store procedure store procedure got successfully executed now if I refresh this then I should have a store procedure available I can try and get this particular store versus run right here in this particular query window to test it but I'm sure it will work fine okay this is the simplest one now the SQL database part is completed let's move to the our logic app portion to design our workflow so from here I'll go to the logic app and I'll go to the workflow and we'll create a very first workflow as this is a new logic app I'll add new workflow I'll call it as SQL demo [Music] I'll keep the workflow as the stateful type so that we can see the history of of the all our runs which we are going to perform I'll go to the designer of this particular workflow to design this workflow you can Define uh we need a trigger to start the workflow so this is a very fast entry point which should be a trigger so we need to define a trigger for this demonstration I am going to use the trigger as the time based regard but you can choose any trigger type first of all I'll select this trigger type as in schedule to select a time-based trigger and I'll say reference and then I'll save every 15 months because I don't want to run every 15 seconds for sure now I'll add an action tab now to select the SQL connector and if you remember if you look at carefully I'm using a built-in connector do remember to use the built-in connector as much as possible in your logic app workflow that will save the cost of your logic app execution so it is always important to select a right connector while defining your logic app I am going to use this SQL built-in connector it has number of actions available so for the demonstration of very first option we are going to use is get rows option to select all the records available in that particular table so I'll use this option to start with it as this is a very first workflow in this particular logic app and we do not have any connector setup which is a SQL type of connector setup so we need to set up the connection for the very first time I'll give it a name as SQL connection that's going to be a built-in connection or the it's not a manage connectors so it's there are three option available here is the connection string type manage identity and the active directory authentication for the demonstration I'm going to use the connection string type to get the connection string I'll go to the to get the connection string I'll go to mySQL server and that SQL Server we have the connection string I'll copy that connection string and of course this connection does not have passwords I'll use my notepad to provide the password on this particular connection string so here is the connection string that will create a connection now collection is ready now this get row step or the connector requires the table name to be provided and then the parameter needs to be provided what parameter it will be it it's asking let's we will have a look at first of all we need to provide the name of the table okay now what I'll do is I'll do so I'll Define the parameter I'll call it as a table name [Music] the name of the parameter and I'll give the name of the table as parameter name as this okay similarly we will call the stored procedure parameter [Music] and name of our store procedure is this so we'll call it as a SP get items so here we have defined two parameters one first store was here one for table name close it down here now we'll Define use this parameter which is the name of the table let's see what is the next parameter we have if you would like to use any wear condition while selecting the record then you can specify the where condition with this particular parameter let's say where SQ code is equal to this or where item status is equal to this so if you would like to use this where condition you can use it let's say in case you have so much of data and you want to filter out that data for now we just have one records I am not going to use anywhere condition for the demonstration I'll save this and will quickly run this particular demonstration whether we are able to fetch the record from the given table or not so workflow is saved successfully let me run that so our SQL query has executed our logic app workflow is successfully executed now if I open that and if we look at that we are successfully able to get the result as in one item and that's the item which we have inserted uh with that SKU code so that throws our SQL connector with Git operation remember that we haven't provided any filter condition so it's going to return all records so be careful about using this particular operation now if you go back to this particular logic app just to have a look at what configuration it has added so if I go to the connection here in this connection provider as we are using the built-in connectors and this is the connector which we have added this is the detail of the connector with all the connection string obviously this you can read it from the SQL Server now if you go to the Json View at the moment we are using the service built-in connectors that's why all our connected details are added here as in the SQL type and this is the name of the connector which we have provided the parameter value which is being added to your which means the connection string value has been added to your configuration section as an app setting and this is the provider of the connection which is a provider type is a SQL type okay this is been added when we have added to the connection now this has demonstrated successfully the very first part which is the select part or the get part now let's look at our next set of example which is of running the store procedure so I'll go to the designer I'll again use the store procedure option or same the SQL connector option to execute the store procedure or let me have a look at first of all the insert row we'll insert the record let's say we are going to use the same table name column which we have added so I'll use the table name I'll select the parameter set column what all columns you would like to set we'll go to our setup column section so we know that we have the SKU code so SQ code is something I am going to provide as unique SQ code now this is going to be a description so I'll use the description as some value of and it some value added now status next column is the status so we know that the status uh column we have so these are the three columns which requires to run the insert procedure okay once that's going to be inserted uh what we'll do is we will add this inserted record or the output of the inserted into a data operation just to see what exactly value we have so here is the input type I'll get the result or the inserted record which is the this particular step as a result into this particular step which is a compose step so now compose step will have the inserted row detail okay let's have a look at that and save this particular step and run our SQL demo so I will run that SQL demo so the run is successful but for for the very first time it has took around four minutes to uh connect maybe some some issue at the connectivity point of view so if I re-run again that should not take much time again to answer the records as you can see that next time when I run it has just taken almost two seconds so it was just an intermittent issue but let's open that and see how many records uh see how it has worked so very first step as it was previous step we are able to select all record in this case of course there were two records because now in the table we have two records so we have received two records again we have inserting a third record which is this one so we have inserted a description SQ code which is this one and the status code of course so here that's the output of the store procedure insert insert uh insert step and this output we have received here in the compose section right so we have run we have we have uh demonstrated get which is a select and then insert now have a look at the store procedure option uh now so for this I'll go to the designer I'll add a new step and I'll select the store procedure option with the same connector SQL connector execute procedure and I'll select the procedure name and I'll provide the value as the name of the SP get item variable here is the parameters we need to provide the parameters because we know that our store procedure requires the SKU code to be provided so I'll provide the SQ code and then I'll use this option so let's see are basically I'll use some random ID as key code as the random guid now let's let's use this to you ID as an SD code for example so that we have this Escape now what I'm going to do here in this case I'll use the SQ code of the previous SQ code which we have inserted so that we are selecting the record which we have inserted in the previous step so to do that I am going to use this compose output step I'll copy this and here I'll use it in the expression and output I'll use the SKU code because I know that we have this key code values available here that's it so that will run the store procedure with this parameter and the value of the parameter is going to be the value of the SQ code which we have inserted here in the inserted step okay let's save this so now we have three different steps to run into the sqls option let's have a look at this if it works fine again this has successfully completed but as we have saved for the very first time it has taken the time but as I have demonstrated in the previous case if you rerun again this won't take much of the time I can rerun again just to show you again how much time it is taking to rerun as you can see it has just completed in 346 47 milliseconds all three steps together which is a good performance I would say so let's have a look at our demonstration how it works so this is again I won't take much time here because we have already seen that this is the get row operation uh we have got all the records which which are available in this case I think there are three to four records we are inserting a new record with geoids Q code so we are not hard coded this Q code the result we are capturing it here in the compose step so we have this result now with the same record we have inserted and now we are using the store procedure which is this store procedure we are passing this key code as in parameter the same SQ code which we have inserted to get the value of the store procedure so when you run the store procedure option you get the result in this particular parameter uh do remember the difference as you can see that the parameter name is result result set so that's name of the parameter and it returns an array of array so if I open these raw output that will show you uh output of that particular SQL Server yes you can see that so result set is the field in which we have the output of your storage here which has an array and inside the array you have again an array of record because your stories is returning the array of Records that's why and inside that you have the uh so it's a multi-dimensional array basically now let's look at the next option available with this SQL connector so this is a update option we are going to have a uh to run now for this I am going to use the name of the table with the same variable which we have defined in the parameters so I'm going to use the table name and the where condition we are going to use the SKU code and then again we are going to use this option which is this one compose and then we are going to use the compose option with the SQ code value [Music] which would have the value of the escape code now what do you want to update let's update a description and I'll say that the description value description something [Music] is updated hat let's say we'll use a UTC time here just to show you and I can use this expressions in this as well that's now I'm going to update the value and after that let's have a look at the next one and the line what is available so I'll use the same connector and this time we are going to use the delete row operation I'll use the same table name custom value delete and then where condition I'll use the SKU code and then I will use the SQ code as in delete so for now I'm going to use the dummy value as the SK code because we would like to see the updates section uh so if I'm going to delete that then updates whatever updates we have made we would be able to see those updates basically so let's save this now I'm going to rerun again let's see how much time it takes this time because we added a new connectors again it has taken approximately four minutes as we have saved for the very first time and if I rerun again it would be uh it will finish very very effectively I think few milliseconds 400 milliseconds that's taken so be mindful of that basically uh first time you have to be little bit patience to uh get the updates now have a look at our store procedure uh execution we have got the store procedure execution which has executed the result back now out of that we are basically running the updates to this particular uh updates to the item which we have inserted so this is our update query we are updating on this particular column with this value this is our wear condition by the way as you can see from here and this is the update which we are making it says that something is updated around this particular date and time and after the update we are getting the output back which is this and then once we have the update run we are running the delete query at the moment it's not returning anything because the value of the where condition which we have provided which is SQ value I have provided some hard coded value which is not maintaining the condition with the item table rows and that's the reason it has returned nothing but you get an idea right if you pass a correct value then it will delete the rows as well so these are the different SQL statement which you can pretty easily around on your logic app adopters to connect to your SQL database and perform the different operation onto the tables I hope you have found this useful if it is please give it a thumbs up and do not forget to subscribe to the channel and if you do have any questions please do let me know in the comments box thanks for watching it see you in the next video

Channel: Our Cloud School

Views: 11,905

Keywords: Logic App, azure logic, logic app integration service, run sql connector in logic app, sql delete query in logic app, sql select query in logic app, sql store procedure query in logic app, sql update in logic app

Id: k5eI6VRmJNg

Length: 21min 39sec (1299 seconds)

Published: Sun Nov 13 2022